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