Table EXAM_RESULTS

STU ID	FNAME	LNAME	EXAM ID	EXAM_SCORE
10	LAURA	LYNCH	1	90
10	LAURA	LYNCH	2	85
11	GRACE	BROWN	1	78
11	GRACE	BROWN	2	72
12	JAY	JACKSON	1	95
12	JAY	JACKSON	2	92
13	WILLIAM	BISHOP	1	70
13	WILLIAM	BISHOP	2	100
14	CHARLES	PRADA	2	85

What SQL statement do we use to find the average exam score for EXAM_ID = 1?

1. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS;
2. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1;
3. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;
4. SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1;

SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;

Output

+-----------------+
| AVG(EXAM_SCORE) |
+-----------------+
|         83.2500 |
+-----------------+

Explanation

1. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS; — This statement calculates the average exam score across all exam IDs in the EXAM_RESULTS table.
2. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1; — This statement is incorrect because the WHERE clause should come before the GROUP BY clause. Additionally, grouping by EXAM_ID and then trying to filter by EXAM_ID = 1 within the GROUP BY clause will result in an error because grouping should be done before filtering.
3. SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1; — This query groups the exam results by EXAM_ID and then calculates the average exam score for each group. The HAVING clause filters the groups and returns only those where the EXAM_ID is equal to 1, giving us the average exam score for the exam with EXAM_ID equal to 1.
4. SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1; — This statement calculates the count of exam scores for EXAM_ID = 1, but it doesn't calculate the average score.