Skip to content
Related Articles

Related Articles

Improve Article

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

  • Last Updated : 07 Jan, 2020

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

Attention reader! Don’t stop learning now.  Practice GATE exam well before the actual exam with the subject-wise and overall quizzes available in GATE Test Series Course.

Learn all GATE CS concepts with Free Live Classes on our youtube channel.

My Personal Notes arrow_drop_up
Recommended Articles
Page :