Web based School
- Chapter 5 -
Clauses in SQL
Objectives
Today's topic is clauses--not the kind that distribute presents during the holidays, but the ones you use with a SELECT statement. By the end of the Chapter you will understand and be able to use the following clauses:
- WHERE
- STARTING WITH
- ORDER BY
- GROUP BY
- HAVING
To get a feel for where these functions fit in, examine the general syntax for a SELECT statement:
SYNTAX:
SELECT [DISTINCT | ALL] { *
| { [schema.]{table | view | snapshot}.*
| expr } [ [AS] c_alias ]
[, { [schema.]{table | view | snapshot}.*
| expr } [ [AS] c_alias ] ] ... }
FROM [schema.]{table | view | snapshot}[@dblink] [t_alias]
[, [schema.]{table | view | snapshot}[@dblink] [t_alias] ] ...
[WHERE condition ]
[GROUP BY expr [, expr] ... [HAVING condition] ]
[{UNION | UNION ALL | INTERSECT | MINUS} SELECT command ]
[ORDER BY {expr|position} [ASC | DESC]
[, {expr|position} [ASC | DESC]] ...]
NOTE: In my experience with SQL, the ANSI standard is really more of an ANSI "suggestion." The preceding syntax will generally work with any SQL engine, but you may find some slight variations.
NOTE: You haven't yet had to deal with a complicated syntax diagram. Because many people find syntax diagrams more puzzling than illuminating when learning something new, this guide has used simple examples to illustrate particular points. However, we are now at the point where a syntax diagram can help tie the familiar concepts to today's new material.
Don't worry about the exact syntax--it varies slightly from implementation to implementation anyway. Instead, focus on the relationships. At the top of this statement is SELECT, which you have used many times in the last few days. SELECT is followed by FROM, which should appear with every SELECT statement you typed. (You learn a new use for FROM tomorrow.) WHERE, GROUP BY, HAVING, and ORDER BY all follow. (The other clauses in the diagram--UNION, UNION ALL, INTERSECT, and MINUS--were covered in Chapter 3, "Expressions, Conditions, and Operators.") Each clause plays an important part in selecting and manipulating data.
NOTE: We have used two implementations of SQL to prepare today's examples. One implementation has an SQL> prompt and line numbers (Personal Oracle7), and the other (Borland's ISQL) does not. You will also notice that the output displays vary slightly, depending on the implementation.
The WHERE Clause
Using just SELECT and FROM, you are limited to returning every row in a table. For example, using these two key words on the CHECKS table, you get all seven rows:
INPUT:
SQL> SELECT * 2 FROM CHECKS;
OUTPUT:
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
7 rows selected.
With WHERE in your vocabulary, you can be more selective. To find all the checks you wrote with a value of more than 100 dollars, write this:
INPUT:
SQL> SELECT * 2 FROM CHECKS 3 WHERE AMOUNT > 100;
The WHERE clause returns the four instances in the table that meet the required condition:
OUTPUT:
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.32 Cellular Phone
5 Joes Stale $ Dent 150 Groceries
WHERE can also solve other popular puzzles. Given the following table of names and locations, you can ask that popular question, Where's Waldo?
INPUT:
SQL> SELECT * 2 FROM PUZZLE;
OUTPUT:
NAME LOCATION -------------- -------------- TYLER BACKYARD MAJOR KITCHEN SPEEDY LIVING ROOM WALDO GARAGE LADDIE UTILITY CLOSET ARNOLD TV ROOM
6 rows selected.
INPUT:
SQL> SELECT LOCATION AS "WHERE'S WALDO?" 2 FROM PUZZLE 3 WHERE NAME = 'WALDO';
OUTPUT:
WHERE'S WALDO? -------------- GARAGE
Sorry, we couldn't resist. We promise no more corny queries. (We're saving those for that SQL bathroom humor guide everyone's been wanting.) Nevertheless, this query shows that the column used in the condition of the WHERE statement does not have to be mentioned in the SELECT clause. In this example you selected the location column but used WHERE on the name, which is perfectly legal. Also notice the AS on the SELECT line. AS is an optional assignment operator, assigning the alias WHERE'S WALDO? to LOCATION. You might never see the AS again, because it involves extra typing. In most implementations of SQL you can type
INPUT:
SQL> SELECT LOCATION "WHERE'S WALDO?" 2 FROM PUZZLE 3 WHERE NAME ='WALDO';
and get the same result as the previous query without using AS:
OUTPUT:
WHERE'S WALDO? -------------- GARAGE
After SELECT and FROM, WHERE is the third most frequently used SQL term.
The STARTING WITH Clause
STARTING WITH is an addition to the WHERE clause that works exactly like LIKE(<exp>%). Compare the results of the following query:
INPUT:
SELECT PAYEE, AMOUNT, REMARKS
FROM CHECKS
WHERE PAYEE LIKE('Ca%');
OUTPUT:
PAYEE AMOUNT REMARKS ==================== =============== ============== Cash 25 Wild Night Out Cash 60 Trip to Boston Cash 34 Trip to Dayton
with the results from this query:
INPUT:
SELECT PAYEE, AMOUNT, REMARKS
FROM CHECKS
WHERE PAYEE STARTING WITH('Ca');
OUTPUT:
PAYEE AMOUNT REMARKS ==================== =============== ============== Cash 25 Wild Night Out Cash 60 Trip to Boston Cash 34 Trip to Dayton
The results are identical. You can even use them together, as shown here:
INPUT:
SELECT PAYEE, AMOUNT, REMARKS
FROM CHECKS
WHERE PAYEE STARTING WITH('Ca')
OR
REMARKS LIKE 'G%';
OUTPUT:
PAYEE AMOUNT REMARKS ==================== =============== =============== Local Utilities 98 Gas Joes Stale $ Dent 150 Groceries Cash 25 Wild Night Out Joans Gas 25.1 Gas Cash 60 Trip to Boston Cash 34 Trip to Dayton Joans Gas 15.75 Gas
WARNING: STARTING WITH is a common feature of many implementations of SQL. Check your implementation before you grow fond of it.
Order from Chaos: The ORDER BY Clause
From time to time you will want to present the results of your query in some kind of order. As you know, however, SELECT FROM gives you a listing, and unless you have defined a primary key (see Chapter 10, "Creating Views and Indexes"), your query comes out in the order the rows were entered. Consider a beefed-up CHECKS table:
INPUT:
SQL> SELECT * FROM CHECKS;
OUTPUT:
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
9 Abes Cleaners 24.35 X-Tra Starch
20 Abes Cleaners 10.5 All Dry Clean
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
11 rows selected.
ANALYSIS:
You're going to have to trust me on this one, but the order of the output is exactly the same order as the order in which the data was entered. After you read Chapter 8, "Manipulating Data," and know how to use INSERT to create tables, you can test how data is ordered by default on your own.
The ORDER BY clause gives you a way of ordering your results. For example, to order the preceding listing by check number, you would use the following ORDER BY clause:
INPUT:
SQL> SELECT * 2 FROM CHECKS 3 ORDER BY CHECK#;
OUTPUT:
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
8 Cash 60 Trip to Boston
9 Abes Cleaners 24.35 X-Tra Starch
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
20 Abes Cleaners 10.5 All Dry Clean
21 Cash 34 Trip to Dayton
11 rows selected.
Now the data is ordered the way you want it, not the way in which it was entered. As the following example shows, ORDER requires BY; BY is not optional.
INPUT/OUTPUT:
SQL> SELECT * FROM CHECKS ORDER CHECK#;
SELECT * FROM CHECKS ORDER CHECK#
*
ERROR at line 1:
ORA-00924: missing BY keyword
What if you want to list the data in reverse order, with the highest number or letter first? You're in luck! The following query generates a list of PAYEEs that stars at the end of the alphabet:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM CHECKS
3 ORDER BY PAYEE DESC;
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
2 Reading R.R. 245.34 Train to Chicago
1 Ma Bell 150 Have sons next time
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
17 Joans Gas 25.1 Gas
16 Cash 25 Wild Night Out
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
9 Abes Cleaners 24.35 X-Tra Starch
20 Abes Cleaners 10.5 All Dry Clean
11 rows selected.
ANALYSIS:
The DESC at the end of the ORDER BY clause orders the list in descending order instead of the default (ascending) order. The rarely used, optional keyword ASC appears in the following statement:
INPUT:
SQL> SELECT PAYEE, AMOUNT 2 FROM CHECKS 3 ORDER BY CHECK# ASC;
OUTPUT:
PAYEE AMOUNT -------------------- --------- Ma Bell 150 Reading R.R. 245.34 Ma Bell 200.32 Local Utilities 98 Joes Stale $ Dent 150 Cash 60 Abes Cleaners 24.35 Cash 25 Joans Gas 25.1 Abes Cleaners 10.5 Cash 34 11 rows selected.
ANALYSIS:
The ordering in this list is identical to the ordering of the list at the beginning of the section (without ASC) because ASC is the default. This query also shows that the expression used after the ORDER BY clause does not have to be in the SELECT statement. Although you selected only PAYEE and AMOUNT, you were still able to order the list by CHECK#.
You can also use ORDER BY on more than one field. To order CHECKS by PAYEE and REMARKS, you would query as follows:
INPUT:
SQL> SELECT * 2 FROM CHECKS 3 ORDER BY PAYEE, REMARKS;
OUTPUT:
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
20 Abes Cleaners 10.5 All Dry Clean
9 Abes Cleaners 24.35 X-Tra Starch
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
5 Joes Stale $ Dent 150 Groceries
4 Local Utilities 98 Gas
3 Ma Bell 200.32 Cellular Phone
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
ANALYSIS:
Notice the entries for Cash in the PAYEE column. In the previous ORDER BY, the CHECK#s were in the order 16, 21, 8. Adding the field REMARKS to the ORDER BY clause puts the entries in alphabetical order according to REMARKS. Does the order of multiple columns in the ORDER BY clause make a difference? Try the same query again but reverse PAYEE and REMARKS:
INPUT:
SQL> SELECT * 2 FROM CHECKS 3 ORDER BY REMARKS, PAYEE;
OUTPUT:
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- --------------------
20 Abes Cleaners 10.5 All Dry Clean
3 Ma Bell 200.32 Cellular Phone
17 Joans Gas 25.1 Gas
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
16 Cash 25 Wild Night Out
9 Abes Cleaners 24.35 X-Tra Starch
11 rows selected.
ANALYSIS:
As you probably guessed, the results are completely different. Here's how to list one column in alphabetical order and list the second column in reverse alphabetical order:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM CHECKS
3 ORDER BY PAYEE ASC, REMARKS DESC;
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
9 Abes Cleaners 24.35 X-Tra Starch
20 Abes Cleaners 10.5 All Dry Clean
16 Cash 25 Wild Night Out
21 Cash 34 Trip to Dayton
8 Cash 60 Trip to Boston
17 Joans Gas 25.1 Gas
5 Joes Stale $ Dent 150 Groceries
4 Local Utilities 98 Gas
1 Ma Bell 150 Have sons next time
3 Ma Bell 200.32 Cellular Phone
2 Reading R.R. 245.34 Train to Chicago
11 rows selected.
ANALYSIS:
In this example PAYEE is sorted alphabetically, and REMARKS appears in descending order. Note how the remarks in the three checks with a PAYEE of Cash are sorted.
TIP: If you know that a column you want to order your results by is the first column in a table, then you can type ORDER BY 1 in place of spelling out the column name. See the following example.
INPUT/OUTPUT:
SQL> SELECT *
2 FROM CHECKS
3 ORDER BY 1;
CHECK# PAYEE AMOUNT REMARKS
-------- -------------------- -------- ------------------
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.32 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
8 Cash 60 Trip to Boston
9 Abes Cleaners 24.35 X-Tra Starch
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
20 Abes Cleaners 10.5 All Dry Clean
21 Cash 34 Trip to Dayton
11 rows selected.
ANALYSIS:
This result is identical to the result produced by the SELECT statement that you used earlier today:
SELECT * FROM CHECKS ORDER BY CHECK#;
The GROUP BY Clause
On Chapter 3 you learned how to use aggregate functions (COUNT, SUM, AVG, MIN, and MAX). If you wanted to find the total amount of money spent from the slightly changed CHECKS table, you would type:
INPUT:
SELECT * FROM CHECKS;
Here's the modified table:
OUTPUT:
CHECKNUM PAYEE AMOUNT REMARKS
======== =========== =============== ======================
1 Ma Bell 150 Have sons next time
2 Reading R.R. 245.34 Train to Chicago
3 Ma Bell 200.33 Cellular Phone
4 Local Utilities 98 Gas
5 Joes Stale $ Dent 150 Groceries
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
9 Abes Cleaners 24.35 X-Tra Starch
20 Abes Cleaners 10.5 All Dry Clean
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
30 Local Utilities 87.5 Water
31 Local Utilities 34 Sewer
25 Joans Gas 15.75 Gas
Then you would type:
INPUT/OUTPUT:
SELECT SUM(AMOUNT)
FROM CHECKS;
SUM
===============
1159.87
ANALYSIS:
This statement returns the sum of the column AMOUNT. What if you wanted to find out how much you have spent on each PAYEE? SQL helps you with the GROUP BY clause. To find out whom you have paid and how much, you would query like this:
INPUT/OUTPUT:
SELECT PAYEE, SUM(AMOUNT) FROM CHECKS GROUP BY PAYEE; PAYEE SUM ==================== =============== Abes Cleaners 34.849998 Cash 119 Joans Gas 40.849998 Joes Stale $ Dent 150 Local Utilities 219.5 Ma Bell 350.33002 Reading R.R. 245.34
ANALYSIS:
The SELECT clause has a normal column selection, PAYEE, followed by the aggregate function SUM(AMOUNT). If you had tried this query with only the FROM CHECKS that follows, here's what you would see:
INPUT/OUTPUT:
SELECT PAYEE, SUM(AMOUNT) FROM CHECKS; Dynamic SQL Error -SQL error code = -104 -invalid column reference
ANALYSIS:
SQL is complaining about the combination of the normal column and the aggregate function. This condition requires the GROUP BY clause. GROUP BY runs the aggregate function described in the SELECT statement for each grouping of the column that follows the GROUP BY clause. The table CHECKS returned 14 rows when queried with SELECT * FROM CHECKS. The query on the same table, SELECT PAYEE, SUM(AMOUNT) FROM CHECKS GROUP BY PAYEE, took the 14 rows in the table and made seven groupings, returning the SUM of each grouping.
Suppose you wanted to know how much you gave to whom with how many checks. Can you use more than one aggregate function?
INPUT/OUTPUT:
SELECT PAYEE, SUM(AMOUNT), COUNT(PAYEE) FROM CHECKS GROUP BY PAYEE; PAYEE SUM COUNT ==================== =============== =========== Abes Cleaners 34.849998 2 Cash 119 3 Joans Gas 40.849998 2 Joes Stale $ Dent 150 1 Local Utilities 219.5 3 Ma Bell 350.33002 2 Reading R.R. 245.34 1
ANALYSIS:
This SQL is becoming increasingly useful! In the preceding example, you were able to perform group functions on unique groups using the GROUP BY clause. Also notice that the results were ordered by payee. GROUP BY also acts like the ORDER BY clause. What would happen if you tried to group by more than one column? Try this:
INPUT/OUTPUT:
SELECT PAYEE, SUM(AMOUNT), COUNT(PAYEE) FROM CHECKS GROUP BY PAYEE, REMARKS; PAYEE SUM COUNT ==================== =============== =========== Abes Cleaners 10.5 1 Abes Cleaners 24.35 1 Cash 60 1 Cash 34 1 Cash 25 1 Joans Gas 40.849998 2 Joes Stale $ Dent 150 1 Local Utilities 98 1 Local Utilities 34 1 Local Utilities 87.5 1 Ma Bell 200.33 1 Ma Bell 150 1 Reading R.R. 245.34 1
ANALYSIS:
The output has gone from 7 groupings of 14 rows to 13 groupings. What is different about the one grouping with more than one check associated with it? Look at the entries for Joans Gas:
INPUT/OUTPUT:
SELECT PAYEE, REMARKS FROM CHECKS WHERE PAYEE = 'Joans Gas'; PAYEE REMARKS ==================== ==================== Joans Gas Gas Joans Gas Gas
ANALYSIS:
You see that the combination of PAYEE and REMARKS creates identical entities, which SQL groups together into one line with the GROUP BY clause. The other rows produce unique combinations of PAYEE and REMARKS and are assigned their own unique groupings.
The next example finds the largest and smallest amounts, grouped by REMARKS:
INPUT/OUTPUT:
SELECT MIN(AMOUNT), MAX(AMOUNT)
FROM CHECKS
GROUP BY REMARKS;
MIN MAX
=============== ===============
245.34 245.34
10.5 10.5
200.33 200.33
15.75 98
150 150
150 150
34 34
60 60
34 34
87.5 87.5
25 25
24.35 24.35
Here's what will happen if you try to include in the select statement a column that has several different values within the group formed by GROUP BY:
INPUT/OUTPUT:
SELECT PAYEE, MAX(AMOUNT), MIN(AMOUNT) FROM CHECKS GROUP BY REMARKS; Dynamic SQL Error -SQL error code = -104 -invalid column reference
ANALYSIS:
This query tries to group CHECKS by REMARK. When the query finds two records with the same REMARK but different PAYEEs, such as the rows that have GAS as a REMARK but have PAYEEs of LOCAL UTILITIES and JOANS GAS, it throws an error.
The rule is, Don't use the SELECT statement on columns that have multiple values for the GROUP BY clause column. The reverse is not true. You can use GROUP BY on columns not mentioned in the SELECT statement. For example:
INPUT/OUTPUT:
SELECT PAYEE, COUNT(AMOUNT) FROM CHECKS GROUP BY PAYEE, AMOUNT; PAYEE COUNT ==================== =========== Abes Cleaners 1 Abes Cleaners 1 Cash 1 Cash 1 Cash 1 Joans Gas 1 Joans Gas 1 Joes Stale $ Dent 1 Local Utilities 1 Local Utilities 1 Local Utilities 1 Ma Bell 1 Ma Bell 1 Reading R.R. 1
ANALYSIS:
This silly query shows how many checks you had written for identical amounts to the same PAYEE. Its real purpose is to show that you can use AMOUNT in the GROUP BY clause, even though it is not mentioned in the SELECT clause. Try moving AMOUNT out of the GROUP BY clause and into the SELECT clause, like this:
INPUT/OUTPUT:
SELECT PAYEE, AMOUNT, COUNT(AMOUNT) FROM CHECKS GROUP BY PAYEE; Dynamic SQL Error -SQL error code = -104 -invalid column reference
ANALYSIS:
SQL cannot run the query, which makes sense if you play the part of SQL for a moment. Say you had to group the following lines:
INPUT/OUTPUT:
SELECT PAYEE, AMOUNT, REMARKS FROM CHECKS WHERE PAYEE ='Cash'; PAYEE AMOUNT REMARKS ==================== =============== =============== Cash 25 Wild Night Out Cash 60 Trip to Boston Cash 34 Trip to Dayton
If the user asked you to output all three columns and group by PAYEE only, where would you put the unique remarks? Remember you have only one row per group when you use GROUP BY. SQL can't do two things at once, so it complains: Error #31: Can't do two things at once.
The HAVING Clause
How can you qualify the data used in your GROUP BY clause? Use the table ORGCHART and try this:
INPUT:
SELECT * FROM ORGCHART;
OUTPUT:
NAME TEAM SALARY SICKLEAVE ANNUALLEAVE =============== ======== =========== =========== =========== ADAMS RESEARCH 34000.00 34 12 WILKES MARKETING 31000.00 40 9 STOKES MARKETING 36000.00 20 19 MEZA COLLECTIONS 40000.00 30 27 MERRICK RESEARCH 45000.00 20 17 RICHARDSON MARKETING 42000.00 25 18 FURY COLLECTIONS 35000.00 22 14 PRECOURT PR 37500.00 24 24
If you wanted to group the output into divisions and show the average salary in each division, you would type:
INPUT/OUTPUT:
SELECT TEAM, AVG(SALARY) FROM ORGCHART GROUP BY TEAM; TEAM AVG =============== =========== COLLECTIONS 37500.00 MARKETING 36333.33 PR 37500.00 RESEARCH 39500.00
The following statement qualifies this query to return only those departments with average salaries under 38000:
INPUT/OUTPUT:
SELECT TEAM, AVG(SALARY) FROM ORGCHART WHERE AVG(SALARY) < 38000 GROUP BY TEAM; Dynamic SQL Error -SQL error code = -104 -Invalid aggregate reference
ANALYSIS:
This error occurred because WHERE does not work with aggregate functions. To make this query work, you need something new: the HAVING clause. If you type the following query, you get what you ask for:
INPUT/OUTPUT:
SELECT TEAM, AVG(SALARY) FROM ORGCHART GROUP BY TEAM HAVING AVG(SALARY) < 38000;
TEAM AVG =============== =========== COLLECTIONS 37500.00 MARKETING 36333.33 PR 37500.00
ANALYSIS:
HAVING enables you to use aggregate functions in a comparison statement, providing for aggregate functions what WHERE provides for individual rows. Does HAVING work with nonaggregate expressions? Try this:
INPUT/OUTPUT:
SELECT TEAM, AVG(SALARY) FROM ORGCHART GROUP BY TEAM HAVING SALARY < 38000; TEAM AVG =============== =========== PR 37500.00
ANALYSIS:
Why is this result different from the last query? The HAVING AVG(SALARY) < 38000 clause evaluated each grouping and returned only those with an average salary of under 38000, just what you expected. HAVING SALARY < 38000, on the other hand, had a different outcome. Take on the role of the SQL engine again. If the user asks you to evaluate and return groups of divisions where SALARY < 38000, you would examine each group and reject those where an individual SALARY is greater than 38000. In each division except PR, you would find at least one salary greater than 38000:
INPUT/OUTPUT:
SELECT NAME, TEAM, SALARY FROM ORGCHART ORDER BY TEAM; NAME TEAM SALARY =============== =============== =========== FURY COLLECTIONS 35000.00 MEZA COLLECTIONS 40000.00 WILKES MARKETING 31000.00 STOKES MARKETING 36000.00 RICHARDSON MARKETING 42000.00 PRECOURT PR 37500.00 ADAMS RESEARCH 34000.00 MERRICK RESEARCH 45000.00
ANALYSIS:
Therefore, you would reject all other groups except PR. What you really asked was Select all groups where no individual makes more than 38000. Don't you just hate it when the computer does exactly what you tell it to?
WARNING: Some implementations of SQL return an error if you use anything other than an aggregate function in a HAVING clause. Don't bet the farm on using the previous example until you check the implementation of the particular SQL you use.
Can you use more than one condition in your HAVING clause? Try this:
INPUT:
SELECT TEAM, AVG(SICKLEAVE),AVG(ANNUALLEAVE) FROM ORGCHART GROUP BY TEAM HAVING AVG(SICKLEAVE)>25 AND AVG(ANNUALLEAVE)<20;
ANALYSIS:
The following table is grouped by TEAM. It shows all the teams with SICKLEAVE averages above 25 days and ANNUALLEAVE averages below 20 days.
OUTPUT:
TEAM AVG AVG =============== =========== =========== MARKETING 28 15 RESEARCH 27 15
You can also use an aggregate function in the HAVING clause that was not in the SELECT statement. For example:
INPUT/OUTPUT:
SELECT TEAM, AVG(SICKLEAVE),AVG(ANNUALLEAVE) FROM ORGCHART GROUP BY TEAM HAVING COUNT(TEAM) > 1; TEAM AVG AVG =============== =========== =========== COLLECTIONS 26 21 MARKETING 28 15 RESEARCH 27 15
ANALYSIS:
This query returns the number of TEAMs with more than one member. COUNT(TEAM) is not used in the SELECT statement but still functions as expected in the HAVING clause.
The other logical operators all work well within the HAVING clause. Consider this:
INPUT/OUTPUT:
SELECT TEAM,MIN(SALARY),MAX(SALARY) FROM ORGCHART GROUP BY TEAM HAVING AVG(SALARY) > 37000 OR MIN(SALARY) > 32000; TEAM MIN MAX =============== =========== =========== COLLECTIONS 35000.00 40000.00 PR 37500.00 37500.00 RESEARCH 34000.00 45000.00
The operator IN also works in a HAVING clause, as demonstrated here:
INPUT/OUTPUT:
SELECT TEAM,AVG(SALARY)
FROM ORGCHART
GROUP BY TEAM
HAVING TEAM IN ('PR','RESEARCH');
TEAM AVG
=============== ===========
PR 37500.00
RESEARCH 39500.00
Combining Clauses
Nothing exists in a vacuum, so this section takes you through some composite examples that demonstrate how combinations of clauses perform together.
Example 5.1
Find all the checks written for Cash and Gas in the CHECKS table and order them by REMARKS.
INPUT:
SELECT PAYEE, REMARKS FROM CHECKS WHERE PAYEE = 'Cash' OR REMARKS LIKE'Ga%' ORDER BY REMARKS;
OUTPUT:
PAYEE REMARKS ==================== ==================== Joans Gas Gas Joans Gas Gas Local Utilities Gas Cash Trip to Boston Cash Trip to Dayton Cash Wild Night Out
ANALYSIS:
Note the use of LIKE to find the REMARKS that started with Ga. With the use of OR, data was returned if the WHERE clause met either one of the two conditions.
What if you asked for the same information and group it by PAYEE? The query would look something like this:
INPUT:
SELECT PAYEE, REMARKS FROM CHECKS WHERE PAYEE = 'Cash' OR REMARKS LIKE'Ga%' GROUP BY PAYEE ORDER BY REMARKS;
ANALYSIS:
This query would not work because the SQL engine would not know what to do with the remarks. Remember that whatever columns you put in the SELECT clause must also be in the GROUP BY clause--unless you don't specify any columns in the SELECT clause.
Example 5.2
Using the table ORGCHART, find the salary of everyone with less than 25 days of sick leave. Order the results by NAME.
INPUT:
SELECT NAME, SALARY FROM ORGCHART WHERE SICKLEAVE < 25 ORDER BY NAME;
OUTPUT:
NAME SALARY =============== =========== FURY 35000.00 MERRICK 45000.00 PRECOURT 37500.00 STOKES 36000.00
ANALYSIS:
This query is straightforward and enables you to use your new-found skills with WHERE and ORDER BY.
Example 5.3
Again, using ORGCHART, display TEAM, AVG(SALARY), AVG(SICKLEAVE), and AVG(ANNUALLEAVE) on each team:
INPUT:
SELECT TEAM, AVG(SALARY), AVG(SICKLEAVE), AVG(ANNUALLEAVE) FROM ORGCHART GROUP BY TEAM;
OUTPUT:
TEAM AVG AVG AVG =============== =========== =========== =========== COLLECTIONS 37500.00 26 21 MARKETING 36333.33 28 15 PR 37500.00 24 24 RESEARCH 39500.00 26 15
An interesting variation on this query follows. See if you can figure out what happened:
INPUT/OUTPUT:
SELECT TEAM, AVG(SALARY), AVG(SICKLEAVE), AVG(ANNUALLEAVE) FROM ORGCHART GROUP BY TEAM ORDER BY NAME; TEAM AVG AVG AVG =============== =========== =========== =========== RESEARCH 39500.00 27 15 COLLECTIONS 37500.00 26 21 PR 37500.00 24 24
MARKETING 36333.33 28 15
A simpler query using ORDER BY might offer a clue:
INPUT/OUTPUT:
SELECT NAME, TEAM FROM ORGCHART ORDER BY NAME, TEAM; NAME TEAM =============== =========== ADAMS RESEARCH FURY COLLECTIONS MERRICK RESEARCH MEZA COLLECTIONS PRECOURT PR RICHARDSON MARKETING STOKES MARKETING WILKES MARKETING
ANALYSIS:
When the SQL engine got around to ordering the results of the query, it used the NAME column (remember, it is perfectly legal to use a column not specified in the SELECT statement), ignored duplicate TEAM entries, and came up with the order RESEARCH, COLLECTIONS, PR, and MARKETING. Including TEAM in the ORDER BY clause is unnecessary, because you have unique values in the NAME column. You can get the same result by typing this statement:
INPUT/OUTPUT:
SELECT NAME, TEAM FROM ORGCHART ORDER BY NAME; NAME TEAM =============== ============ ADAMS RESEARCH FURY COLLECTIONS MERRICK RESEARCH MEZA COLLECTIONS PRECOURT PR RICHARDSON MARKETING STOKES MARKETING WILKES MARKETING
While you are looking at variations, don't forget you can also reverse the order:
INPUT/OUTPUT:
SELECT NAME, TEAM FROM ORGCHART ORDER BY NAME DESC; NAME TEAM =============== ============ WILKES MARKETING STOKES MARKETING RICHARDSON MARKETING PRECOURT PR MEZA COLLECTIONS MERRICK RESEARCH FURY COLLECTIONS ADAMS RESEARCH
Example 5.4: The Big Finale
Is it possible to use everything you have learned in one query? It is, but the results will be convoluted because in many ways you are working with apples and oranges--or aggregates and nonaggregates. For example, WHERE and ORDER BY are usually found in queries that act on single rows, such as this:
INPUT/OUTPUT:
SELECT * FROM ORGCHART ORDER BY NAME DESC;
NAME TEAM SALARY SICKLEAVE ANNUALLEAVE =============== ======== =========== =========== =========== WILKES MARKETING 31000.00 40 9 STOKES MARKETING 36000.00 20 19 RICHARDSON MARKETING 42000.00 25 18 PRECOURT PR 37500.00 24 24 MEZA COLLECTIONS 40000.00 30 27 MERRICK RESEARCH 45000.00 20 17 FURY COLLECTIONS 35000.00 22 14 ADAMS RESEARCH 34000.00 34 12
GROUP BY and HAVING are normally seen in the company of aggregates:
INPUT/OUTPUT:
SELECT PAYEE, SUM(AMOUNT) TOTAL, COUNT(PAYEE) NUMBER_WRITTEN FROM CHECKS GROUP BY PAYEE HAVING SUM(AMOUNT) > 50; PAYEE TOTAL NUMBER_WRITTEN ==================== =============== ============== Cash 119 3 Joes Stale $ Dent 150 1 Local Utilities 219.5 3 Ma Bell 350.33002 2 Reading R.R. 245.34 1
You have seen that combining these two groups of clauses can have unexpected results, including the following:
INPUT:
SELECT PAYEE, SUM(AMOUNT) TOTAL, COUNT(PAYEE) NUMBER_WRITTEN FROM CHECKS WHERE AMOUNT >= 100 GROUP BY PAYEE HAVING SUM(AMOUNT) > 50;
OUTPUT:
PAYEE TOTAL NUMBER_WRITTEN ==================== =============== ============== Joes Stale $ Dent 150 1 Ma Bell 350.33002 2 Reading R.R. 245.34 1
Compare these two result sets and examine the raw data:
INPUT/OUTPUT:
SELECT PAYEE, AMOUNT FROM CHECKS ORDER BY PAYEE; PAYEE AMOUNT ==================== =============== Abes Cleaners 10.5 Abes Cleaners 24.35 Cash 25 Cash 34 Cash 60 Joans Gas 15.75 Joans Gas 25.1 Joes Stale $ Dent 150 Local Utilities 34 Local Utilities 87.5 Local Utilities 98 Ma Bell 150 Ma Bell 200.33 Reading R.R. 245.34
ANALYSIS:
You see how the WHERE clause filtered out all the checks less than 100 dollars before the GROUP BY was performed on the query. We are not trying to tell you not to mix these groups--you may have a requirement that this sort of construction will meet. However, you should not casually mix aggregate and nonaggregate functions. The previous examples have been tables with only a handful of rows. (Otherwise, you would need a cart to carry this guide.) In the real world you will be working with thousands and thousands (or billions and billions) of rows, and the subtle changes caused by mixing these clauses might not be so apparent.
Summary
ToChapter you learned all the clauses you need to exploit the power of a SELECT statement. Remember to be careful what you ask for because you just might get it. Your basic SQL education is complete. You already know enough to work effectively with single tables. Tomorrow (Chapter 6, "Joining Tables") you will have the opportunity to work with multiple tables.
Q&A
- Q I thought we covered some of these functions earlier this week? If so, why
are we covering them again?
A We did indeed cover WHERE on Chapter 3. You needed a knowledge of WHERE to understand how certain operators worked. WHERE appears again toChapter because it is a clause, and today's topic is clauses.
Workshop
The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."
Quiz
- 1. Which clause works just like LIKE(<exp>%)?
2. What is the function of the GROUP BY clause, and what other clause does it act like?
3. Will this SELECT work?
INPUT:
SQL> SELECT NAME, AVG(SALARY), DEPARTMENT
FROM PAY_TBL
WHERE DEPARTMENT = 'ACCOUNTING'
ORDER BY NAME
GROUP BY DEPARTMENT, SALARY;
- 4. When using the HAVING clause, do you always have to use a
GROUP BY also?
5. Can you use ORDER BY on a column that is not one of the columns in the SELECT statement?
Exercises
- 1. Using the ORGCHART table from the preceding examples, find
out how many people on each team have 30 or more days of sick leave.
2. Using the CHECKS table, write a SELECT that will return the following:
OUTPUT:
CHECK#_____PAYEE_______AMOUNT 1 MA BELL 150
Popular Tutorials
-
MS Access
1109
-
C++
1222
-
HTML
584
-
JavaScript
616
-
Vbscript
873
-
Oracle
473
-
VC++
875
-
SQL
2959
-
XML
514
-
Java
814
-
Perl
455
-
Linux
451
