Q50.Marks: +2.0UGC NET Paper 2: Computer Science 26th Nov 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);
1.A and B only✓ Correct
2.C only
3.A only
4.B only
Solution
SELECT coursename, count(*) 'total' from STUDENT natural join COURSE group by coursename;
The above statement will first do a natural join between STUDENT and COURSE on the basis of common courseno.
Then it will group the result by coursename.
Finally, it will display the result - coursename and count(*). count(*) will count the number of rows having a particular coursename.
Finally, in the result, while displaying, count(*) column heading will become "total".
SELECT C.coursename, count(*) 'total' from STUDENT S, COURSE C where S.courseno = C.courseno group by coursename;
The above statement will first find the STUDENT S and COURSE C where courseno between them is same.
It will then group the result by coursename.
Finally, it will display the result - for every coursename C, it will display coursename C and the number of rows having the coursename as C.
Then, count(*) will be displayed as "total".
SELECT coursename, count(*) 'total' from COURSE C where courseno in (SELECT courseno from STUDENT);
It will give wrong result because, the inner query will have duplicate courseno in its final result and hence, using that, outer query will also generate incorrect number of rows.