Solutions Chapter 5: Aggregate Functions Test Your Thinking

1. List the order in which the WHERE, GROUP BY, and HAVING clauses are executed by the database in the following SQL statement. SELECT section_id, COUNT(*), final_grade FROM enrollment WHERE TRUNC(enroll_date) > TO_DATE('2/16/1999', 'MM/DD/YYYY') GROUP BY section_id, final_grade HAVING COUNT(*) > 5 Solution: First the WHERE clause is executed, then the GROUP BY, and lastly the HAVING clause is applied.

2. Display a count of all the different course costs in the COURSE table.

   Solution:

   SELECT cost, COUNT(*)
     FROM course
    GROUP BY cost
    COST  COUNT(*)
   --------- ---------
        1095         3
        1195        25
        1595         1
                     1K
   
   4 rows selected.
   

   Note the NULL value in the result set, and notice the difference if you write the SQL statement using COUNT(cost)instead.

   SELECT cost, COUNT(cost)
      FROM course
     GROUP BY cost
    COST COUNT(COST)
   --------- -----------
        1095           3
        1195          25
        1595           1
                       0
   
   4 rows selected.
   

3. Determine the number of students living in zip code 10025.

   Solution:

   SELECT COUNT(*)
     FROM student
    WHERE zip = '10025'
    COUNT(*)
   ---------
           3
   
   1 row selected.
   

4. Show all the different companies for which students work. Display only companies where more than four students are employed.

   Solution:

   SELECT employer, COUNT(*)
     FROM student
    GROUP BY employer
   HAVING COUNT(*) > 4
   EMPLOYER              COUNT(*)
   -------------------- ---------
   Amer.Legal Systems          10
   Crane Co.                    6
   Electronic Engineers        15
   New York Pop                 8
   
   4 rows selected.
   

5. List how many sections each instructor teaches.

   Solution:

   SELECT instructor_id, COUNT(*)
     FROM section
    GROUP BY instructor_id
   INSTRUCTOR_ID  COUNT(*)
   ------------- ---------
             101         9
             102        10
             103        10
             104        10
             105        10
             106        10
             107        10
             108         9
   
   8 rows selected.
   

6. Formulate the question for the following statement:

   SELECT COUNT(*), start_date_time, location
     FROM section
    GROUP BY start_date_time, location
   HAVING COUNT(*) > 1
   

   Solution:

   List the date, time, and location of sections that meet at the same time, date, and location.

   COUNT(*) START_DAT LOCATION
   --------- --------- --------
           2 09-APR-99 L214
           2 16-APR-99 L509
   
   2 rows selected.
   

7. Determine the highest grade achieved for the midterm within each section.

   Solution:

   SELECT section_id, MAX(numeric_grade)
     FROM grade
    WHERE grade_type_code = 'MT'
    GROUP BY section_id
   SECTION_ID MAX(NUMERIC_GRADE)
   ---------- ------------------
           80                 76
           81                 88
   ...
          154                 92
          156                 99
   
   56 rows selected.
   

8. A table called CUSTOMER_ORDER contains 5,993 rows with a total order amount of $10,993,333.98. A total of 4,500 customers placed these orders. Given the following scenario, how many row(s) do you think the following query returns?

   SELECT SUM(total_order_amount)
     FROM customer_order
   

   Solution: Aggregate functions always return a single row. The result of the SUM function will return 10,993.333.98.

Select Another Chapter

1. SQL and Data
2. SQL: The Basics
3. Character and Number Functions
4. Date, Conversion, and Miscellaneous Functions
5. Aggregate Functions, GROUP BY, and HAVING
6. Equijoins
7. Subqueries
8. Set Operators
9. Complex Joins
10. Insert, Update, and Delete
11. Create, Alter, and Drop Tables
12. Views, Indexes, and Sequences
13. The Data Dictionary and Dynamic SQL Scripts
14. Security
15. Advanced SQL Queries
16. SQL Optimization