Grouping Records, Joins in SQL — Question 7

SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;

+---------+------------------------+
| EXAM_ID | COUNT(DISTINCT STU_ID) |
+---------+------------------------+
|       1 |                      4 |
|       2 |                      5 |
+---------+------------------------+

1. SELECT COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — It groups the EXAM_RESULTS table by EXAM_ID and uses the COUNT(DISTINCT STU_ID) function to count the number of distinct student IDs for each exam. However, the result set does not include EXAM_ID.
2. SELECT EXAM_ID, MAX(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — This query groups the results by EXAM_ID and then selects the maximum STU_ID for each exam. However, this doesn't provide the count of students who took each exam, as it gives the maximum student ID instead of counting the distinct student IDs.
3. SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — It groups the EXAM_RESULTS table by EXAM_ID and uses the COUNT(DISTINCT STU_ID) function to count the number of distinct student IDs for each exam. The result set includes the EXAM_ID and the count of students who took each exam.
4. SELECT EXAM_ID, MIN(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — This query groups the results by EXAM_ID and selects the minimum STU_ID for each exam. It does not provide information about the number of students who took each exam.