## 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)
GROUP BY section_id
---------- ------------------
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.