SQL SELECT Statement, Part one



LU05 – SQL SELECT Statement Aggregates and Sub-Queries / SQL ViewsContents TOC \o "1-3" \h \z \u LU04 – SQL SELECT Statement Aggregates and Sub-Queries / SQL Views PAGEREF _Toc253596060 \h 1Learning Objectives PAGEREF _Toc253596061 \h 1SQL Goals PAGEREF _Toc253596062 \h 2Part 1: SELECT Aggregates PAGEREF _Toc253596063 \h 2Recall: SQL Aggregate functions PAGEREF _Toc253596064 \h 3Part2: Sub-Selects: Queries within Queries PAGEREF _Toc253596065 \h 5Derived Queries PAGEREF _Toc253596066 \h 6Part 3: External Model 101 - SQL Views PAGEREF _Toc253596067 \h 7References PAGEREF _Toc253596068 \h 7Learning ObjectivesLast week we were introduced to the SQL SELECT statement. This week we’ll take the covers off and dive really deep into the SELECT statement. We explore some additional SQL capabilities; aggregate (summarize) data, and write sub queries. f you look at our methodology below you can see that we are still in the implementation phase of the SDLC. Upon completion of this learning unit you should be able to:Compare, contrast scalar and aggregate functions.Describe and use various ways to join tables.Solve problems using aggregate functions and pare join and sub queries.Understand how to use special constructs on the SELECT statement.SQL GoalsOur SQL goals for this learning unit will be to:Understand aggregates and using the GROUP BY and HAVING clauses on the SELECT statement.Understand sub-SELECTS and sub-queriesPart 1: SELECT AggregatesAggregates allow you to group and/or summarize rows of data. The syntax diagram for the basic select statement is as follows:SELECT {colname [, ..n] | * } FROM tablename [WHERE pre-group-by-condition][GROUP BY colname [DESC] [, ..n]][HAVING post-group-by-condition][ORDER BY colname [DESC] [, ..n]]Some notes on the select statement:Every column included in the select statement must either appear in GROUP BY or use an aggregate function.The HAVING clause is like the WHERE clause, only it applied to the output after the GROUP BY clause, as opposed to WHERE clause. When using aggregate functions, the HAVING is optional but the GROUP BY is not.Recall: SQL Aggregate functionsCount(column)Count the values of columnsum(column)Total the values in columnmin(column)Retrieve the smallest value in columnmax(column)Retrieve the largest value in columnavg(column)Retrieve average of columnExample 1: Some basic aggregates in action:Example 2: Illustrating the need for group by over non-aggregates (Incorrect)Example 3: Illustrating the need for group by over non-aggregates (Correct)Example 4: Illustrating the use of HAVING. Notice the AVG(employee_hourlywage) is used in the HAVING clause and not the alias name of average_hourlywage. That is because column aliasing occurs last in the SELECT process:Part2: Sub-Selects: Queries within QueriesSub queries are The easiest way to explain why you’d need a sub-query is to give you an example. Its easy to write a SELECT statement to output all employees making more than a specific value, such as $50,000:But what about showing all employees making more than the average? We learned in the pervious section, we can use aggregates to determine the average salary:Okay, but how to we display all employees making more than the average, and what does this have to do with sub-selects? Consider the following:And viola! You have your answer to both questions! You can place a sub select anywhere SQL is normally looking for a column name or expression. You can also use the IN or NOT IN keywords retrieve data based on the condition of data in another table. For example, this query displays all employees who work in a store with zip code 12345: Consider the following example. Fudgemart employees who are ‘Department Managers’ are salaried, and thus get paid for no more than 40 hours, regardless of how many they work. So, you might need SQL like this to update their timecards to 40 hours based on their job title: It should be noted this can also be done using a join:Why use the sub-select query when the name data can be express with a join? That is a matter of personal preference and readability, but not all queries can be expressed with joins (the first example is a good example of this) so sub-selects do have their place. BTW: Most DBMS’s are good at optimizing SQL queries so that both queries, which in effect accomplish the same thing, perform in the same manner.Derived QueriesA derived query is a type of sub-select which contains an SQL SELECT statement in the FROM clause of a main SELECT statement. In the example below, the SELECT statement in parenthesis is executed first and then the output is aliased as the table fmap. This aliased table is then used as part of the main query, which displays the product_id and product_name. While the example you see could be implemented without a derived query, there are several examples of queries where this is the only way to accomplish the task. How do you know? Well in my experience when I can’t get the results I want through a join or a sub-select in the where clause.Part 3: External Model 101 - SQL ViewsPart of building a database involves creating SQL Select statements to display output matching the end-user’s requirements. Queries such as “Total quantity on hand by region”, and “Employees, departments, and managers” are easy to understand in concept, but can be quite difficult for the end-user to implement in SQL because of the inherent complexity of the SELECT statement. If we could abstract the complexity of these SQL statements into their own virtual tables it would be easier for the end user to digest that complexity. This is the fundamental purpose of the SQL View.An SQL View is a type of meta-data. It allows you to store an SQL SELECT statement under an object name, which in turn can be queried as a table. Since views are meta-data, they reside in the DML family of SQL statements, and have corresponding CREATE VIEW, ALTER VIEW and DROP VIEW statements. Here’s the syntax of the CREATE VIEW statement: CREATE VIEW viewname AS select-query-statementOnce you’ve created your view you can access it just like it was a normal table. In addition you can update, delete, and insert data into a view as long the SELECT statement defined in the view does not contain aggregates, joins, and calculated columns or violate any table constraints. The benefit of the View to the external model cannot be overlooked; views are the vehicle a database designer uses to abstract the complexities of the internal model from the users of the database.Example: Creating a viewReferencesRob, Ch 6 Bordodli Ch 5,6,7Whitehorn, Ch 28Microsoft SQL 2005 Books onlineHome of SQL Cookbook and Recipies ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download