Week 2

Day 8

More Sophisticated Queries with SQL

You already have seen how the SELECT statement can retrieve records from a single table, but retrieving records is only one of the many features of this versatile statement. This lesson examines the more advanced features of the SELECT statement: the GROUP BY clause, the HAVING clause, and the join operation. You'll see how powerful the SELECT statement truly is.

The syntax of the SELECT statement is fairly difficult to decipher, and you can't really understand the use of its clauses just by studying the syntax diagrams shown in the Oracle documentation. Therefore, you should look at the many examples in this lesson. I'll show you what works and what doesn't.

Built-In Group Functions

The first subject for discussion is the Oracle built-in functions that operate on groups of rows. Be warned: This topic is a good example of SQL's quirky

characteristics. Even though these functions are group functions, they do not require the use of a GROUP BY clause.

• COUNT counts the number of rows that Oracle retrieves based on the supplied criteria.
• MAX and MIN return the minimum and maximum values for the specified column.
• AVG and SUM compute a column's average and total value.
• STDDEV and VARIANCE compute a column's standard deviation and variance.

Each of these functions returns a single value. A few detailed examples follow.

The COUNT Function

The COUNT function comes in two flavors: COUNT(*), which counts all the rows in a table that satisfy any specified criteria, and COUNT(column-name), which counts all the rows in a table that have a non-null value for column-name and satisfy any specified criteria.

To demonstrate the use of COUNT(*), Listing 8.1 illustrates how you would count the number of courses offered by the Biology department.

Listing 8.1. A SELECT statement with a WHERE clause.

SQL> select count(*)
  2  from Course
   3  where
  4  Department_ID = `BIO';
 COUNT(*)
---------
        3

If you want to count the number of students who have fax numbers, supply Fax as an argument to the COUNT function as shown in Listing 8.2.

Listing 8.2. Using the COUNT function to determine the number of rows in a table.

SQL> select count(*)
     2  from Student;

 COUNT(*)
---------
       31

SQL> select count(Fax)
  2  from Student;
COUNT(FAX)
----------
        3

You should be aware that the COUNT function returns a single row, even if the count is zero.

Obtaining Maximum and Minimum Values

You can combine the MAX and MIN functions in a single SELECT statement. Listing 8.3 demonstrates the use of these functions. The first query applies the functions to a numeric column—Additional_Fees. The second query applies the MAX and MIN functions to a character column—Instructor_ID.

Listing 8.3. The MAX and MIN functions used with different column datatypes.

SQL> select max(Additional_Fees), min(Additional_Fees)
    2  from Course;
MAX(ADDITIONAL_FEES) MIN(ADDITIONAL_FEES)
-------------------- --------------------
                 750                    0
SQL> select max(Instructor_ID), min(Instructor_ID)
  2  from Instructor;

MAX(INSTRUCTOR_ID)   MIN(INSTRUCTOR_ID)
-------------------- --------------------
Y561                 A612

Using AVG and SUM

AVG and SUM work in the same way as MIN and MAX. Listing 8.4 provides an example. A query of the Course table retrieves the average and total of the additional fees charged for a course.

Listing 8.4. Using the AVG and SUM functions.

SQL> select avg(Additional_Fees), sum(Additional_Fees)
      2  from Course;

AVG(ADDITIONAL_FEES) SUM(ADDITIONAL_FEES)
-------------------- --------------------
           46.171875               1477.5

Note that the AVG function does not use null values in its calculation.

Combining Group Functions with Other Columns

You cannot combine group functions and columns in the select list of a SELECT statement without using a GROUP BY clause. Listing 8.5 provides an example of a query in which two items compose the select list—Department_ID and sum(Additional_Fees). However, Oracle returns an error. To understand why, it helps to think in these terms: A group function returns a single row, but Department_ID returns as many rows as exist in the table. It simply doesn't make sense to return both these values at the same time; instead, Oracle returns an error message. However, by using a GROUP BY clause, you can combine group functions and columns in the select list. The next few sections look further at the use of the GROUP BY clause.

Listing 8.5. Incorrect syntax in a SELECT statement.

SQL> select Department_ID, sum(Additional_Fees)
     2  from Course;
select Department_ID, sum(Additional_Fees)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

Looking for Distinct Rows

The SELECT statement has an optional keyword that we haven't discussed yet: DISTINCT. This keyword follows SELECT and instructs Oracle to return only rows that have distinct values for the specified columns. As an example, Listing 8.6 uses a query to obtain a list of the cities in which students live.

Listing 8.6. Using a query to retrieve a set of distinct values.

SQL> select distinct City
  2  from Student;

CITY
------------------------
DOVER
SPRINGFIELD

The DISTINCT option is very useful for finding a column's set of values. It offers a method that you can use for quickly determining how values are clustered in a table. If you don't specify

Page 195

the DISTINCT option, Oracle retrieves all rows that satisfy the criteria in the WHERE clause. By default, Oracle uses the ALL option to retrieve all rows. Specifically, the following SELECT statements are equivalent:

select  City  from  Student;

and

select  all  City  from  Student;

Grouping Rows

The GROUP BY clause is another section of the SELECT statement. This optional clause tells Oracle to group rows based on the distinct values that exist for the specified columns. In addition, the HAVING clause can be used in conjunction with the GROUP BY clause to further restrict the retrieved rows.

Even though this topic is best explained by example, here's a quick look at the syntax first.

SELECT select-list
FROM table-list
[WHERE
condition [AND | OR]
...
condition]
[GROUP BY column1, column2, ..., columnN]
[HAVING condition]
[ORDER BY column1, column2, ...]

The variables are defined as follows:

select-list is a set of columns and expressions from the tables listed in table-list.

table-list is the tables from which rows are retrieved.

condition is a valid Oracle SQL condition.

column1 through columnN are columns contained in table-list.