Bright Tutorials Bright Tutorials
CBSE Class 12 Computer Science Question 71 of 91

Grouping Records, Joins in SQL — Question 6

Back to all questions
6
Question

Question 6

Table EXAM_RESULTS

STU IDFNAMELNAMEEXAM IDEXAM_SCORE
10LAURALYNCH190
10LAURALYNCH285
11GRACEBROWN178
11GRACEBROWN272
12JAYJACKSON195
12JAYJACKSON292
13WILLIAMBISHOP170
13WILLIAMBISHOP2100
14CHARLESPRADA285

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;
Answer 
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.