GATE | Sudo GATE 2020 Mock II (10 January 2019) | Question 19

Consider the following relational schema:

STUDENT(SNO, SNAME, DEPT)
ENROLL(CNO, SNO, GRADE)
COURSE(CNO, DEPT)
PREREQ(CNO, PNO) 

Keys are underlined. The column ENROLL(SNO) is a foreign key referencing STUDENT(SNO). All the occurrences of the columns CNO and PNO, except for the one in COURSE, are foreign keys referencing COURSE(CNO).

SELECT E1.CNO, S.SNAME
FROM ENROLL E1, STUDENT S
WHERE E1.SNO = S.SNO
      AND NOT EXISTS ( SELECT * FROM ENROLL E2
                       WHERE E2.CNO = E1.CNO
                            AND E2.GRADE > E1.GRADE )

What is the output of the Query ?
(A) For every course, return the names of the highest-scoring students.
(B) For every student, return the names of the courses.
(C) For every course, return the names of the students who never score highest marks.
(D) None of these.


Answer: (A)

Explanation: Given SQL query,



SELECT E1.CNO, S.SNAME
FROM ENROLL E1, STUDENT S
WHERE E1.SNO = S.SNO
      AND NOT EXISTS ( SELECT * FROM ENROLL E2
                       WHERE E2.CNO = E1.CNO
                            AND E2.GRADE > E1.GRADE )

It returns the names of the highest-scoring students, for every course.

Option (A) is correct.


Quiz of this Question



My Personal Notes arrow_drop_up
Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.