Some Interesting Verbs and Adverbs You Should Know

In the preceding pages I've used some SQL statements without really explaining them. I guessed that you could figure out the purpose and syntax from the context and examples. However, here's a little more information on the GROUP BY (and HAVING ), ORDER BY , and WHERE clauses.

GROUP BY

Here's the important thing to remember: When you want to combine a group function (such as COUNT , AVG , or SUM ) with an individual function such as SELECT L_NAME (with L_NAME being an individual item), you have to use the GROUP BY statement. Otherwise, get ready for "ORA-xxx" errors that say some-thing about "not a group function."

And here's one more important syntax item:

  SELECT TEST_ID, SCORE, COUNT(TEST_ID) from TEST_HISTORY  GROUP    BY  TEST_ID, SCORE   

(1) You must match your SELECT and GROUP BY items.

(2) You have two SELECT items, so you must have two GROUP BY items. Also whatever you put in the COUNT statement must also appear in your SELECT statement.

How do we use this feature? Suppose you have to produce a list of student scores by test, but you do not want to repeat duplicate scores. Running the preceding SELECT command will produce the following results:

TEST_ID	SCORE [4]	COUNT(TEST_ID) [3]
1	75	1
1	80	2
1	90	1
1	98	1
2	22	1
2	32	1
2	50	1
2	100	2
3	87	1
3	88	1
3	100	2
4	72	1
4	90	1
4	92	1
. . . and so on

^[4] the SCORE column shows the actual scores, all arranged in test ID order.

^[3] Notice that the COUNT column shows duplicate scores for a test, and

Now suppose you wanted to list only the failing scores. Now you use the HAVING command, like this:

 SQL> SELECT TEST_ID, SCORE, COUNT(TEST_ID) from TEST_HISTORY GROUP BY TEST_ID,      score  HAVING  score < 70;  

(1) Notice the use of HAVING.

Here are the results:

Test_ID	Score	COUNT(Test_ID)
2	22	1
2	32	1
2	50	1

Remember that HAVING is a group function and works only when you're using the GROUP BY command! Here's the error you get if you go after some-thing with the HAVING statement that is not in your GROUP BY clause:

 1 SELECT TEST_ID, SCORE, COUNT(TEST_ID) from TEST_HISTORY  GROUP BY  TEST_ID   2* HAVING score < 70 HAVING score < 70       *  ERROR at line 2:    ORA-00979: not a GROUP BY expression  

(2) Since SCORE is not in the GROUP BY clause, you get this error.

ORDER BY

Now let's add the ORDER BY function. As you've seen, the GROUP BY clause is used to group things together. It does not change the way items sort . Suppose you now needed to be able to list the scores in descending order by test ID. Here's where you add the ORDER BY statement, and notice that you can have more than one item in it:

 SQL> SELECT TEST_ID, SCORE, COUNT(TEST_ID) from TEST_HISTORY GROUP BY            TEST_ID, SCORE    HAVING score > 70    ORDER BY TEST_ID, SCORE 

Here are the results: ^[3]

^[3] There's a lot going on here. First of all, we want only scores greater than 70 ”hence the HAVING statement. Then, to show the scores in ascending order within TEST_ID, we added the ORDER BY phrase. And don't forget, we're using a COUNT function, so we get a total for any duplicate scores.

You're seeing a pretty powerful set of functions here that will help you look really good to your users whenever they ask you for information like this!

TEST_ID	SCORE	COUNT(TEST_ID)
1	75	1
1	80	2
1	90	1
1	98	1
2	100	2
3	87	1
3	88	1
3	98	1
3	100	2
4	72	1
4	90	1
4	92	1
4	100	2
5	79	1
5	91	1
...and so on

WHERE

Finally, we have the WHERE clause. Think of WHERE as a gate or filter. It qualifies the rows that are returned, as in these examples: WHERE SCORE > 70 or WHERE TEST_ID = 1 . One of the most important uses of the WHERE clause is to prevent what is called a Cartesian product , which is just a way of saying that you can end up with huge results if you join tables and do not use a WHERE clause. For example, if we wrote a SELECT statement that selected all rows from STUDENTS and all rows from TEST_ID, and there were 500 rows in the STUDENTS table and 50 rows in the TEST_ID table, we would end up with 25,000 rows of rather useless data. (For you skeptics, go ahead and run Select * from students, test_id , and you will see that you will get back not the sum of the rows in both tables, but the result of multiplying the numbers of both tables! Do it now and get it out of your system. Do not do it when your systems are in production, or you'll bring them to their knees.) But beware; it happens. So use the WHERE clause whenever possible to prevent such situations, as well as to produce meaningful results.

You can also use various operators with the WHERE clause, such as:

• WHERE SCORE BETWEEN 70 AND 100;

• WHERE SCORE = 90 OR SCORE = 80 OR SCORE = 70;

• WHERE EXISTS (SELECT SCORE FROM TEST_HISTORY WHERE SCORE >99);

• WHERE SCORE IN (99,98,97,96);