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);

1. AA and B only
2. BC only
3. CA only
4. DB only

Pick an option to check yourself, or open the answer below.

Show answer & explanation

Correct answer: A. A and B only

Explanation

A (natural join) and B (explicit join on courseno) both group by coursename and count. C has no GROUP BY, so it returns one aggregated row (and is invalid with coursename).