Given the following STUDENT‐COURSE scheme: STUDENT (Rollno, Name, courseno)…
2021
Given the following STUDENT‐COURSE scheme: STUDENT (Rollno, Name, courseno) COURSE (courseno, coursename, capacity), where Rollno is the primary key of relation STUDENT and courseno is the primary key of relation COURSE. Attribute coursename of COURSE takes unique values only. Which of the following query(ies) will find total number of students enrolled in each course, along with its coursename.
A. SELECT coursename, count(*) 'total' from STUDENT natural join COURSE group by coursename;
B. SELECT C.coursename, count(*) 'total' from STUDENT S, COURSE C where S.courseno=C.courseno group by coursename;
C. SELECT coursename, count(*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);
- A.
A and B only
- B.
C only
- C.
A only
- D.
B only
Attempted by 265 students.
Show answer & explanation
Correct answer: A
Answer: The queries that join STUDENT and COURSE and group by coursename correctly return the number of students per course.
Query using NATURAL JOIN: SELECT coursename, count(*) AS total FROM STUDENT NATURAL JOIN COURSE GROUP BY coursename; This joins on courseno and counts the joined student rows per course.
Query using explicit join: SELECT C.coursename, count(*) AS total FROM STUDENT S, COURSE C WHERE S.courseno = C.courseno GROUP BY coursename; This is equivalent to the NATURAL JOIN version and also counts students per course.
The attempted COURSE-only query is incorrect: SELECT coursename, count(*) AS total FROM COURSE C WHERE courseno IN (SELECT courseno FROM STUDENT); Without GROUP BY this is not a per-course aggregation and will not return the student count per course. Even with GROUP BY it would not correctly count students unless joined or using a correlated subquery.
Note on courses with zero students: If you need to include courses that currently have no students (show zero), use a LEFT JOIN and count the student rows, for example:
SELECT C.coursename, COUNT(S.Rollno) AS total FROM COURSE C LEFT JOIN STUDENT S ON C.courseno = S.courseno GROUP BY C.coursename;
Therefore the correct choice is the combination of the two join-and-group queries; the third query is not correct for counting students per course.
A video solution is available for this question — log in and enroll to watch it.