GATE CS 2025 Set 2 Q.54: SQL Queries and Integrity Constraints

Consider the following relational schema:

Students (rollno: integer, name: string, age: integer, cgpa: real)
Courses (courseno: integer, cname: string, credits: integer)
Enrolled (rollno: integer, courseno: integer, grade: string)

Which of the following options is/are correct SQL query/queries to retrieve the names of the students enrolled in course number (i.e., courseno) 1470?

SELECT S.name
FROM Students S
WHERE EXISTS (SELECT * FROM Enrolled E
              WHERE E.courseno = 1470
                      AND E.rollno = S.rollno);

SELECT S.name
FROM Students S
WHERE SIZEOF (SELECT * FROM Enrolled E
              WHERE E.courseno = 1470
                      AND E.rollno = S.rollno) > 0;

SELECT S.name
FROM Students S
WHERE 0 < (SELECT COUNT(*)
            FROM Enrolled E
            WHERE E.courseno = 1470
                      AND E.rollno = S.rollno);

SELECT S.name
FROM Students S NATURAL JOIN Enrolled E
WHERE E.courseno = 1470;

Official answer and solution

Official answer: A, C and D
From: Official GATE 2025 answer key

Solution

Check SQL syntax and semantics for EXISTS, COUNT(*), NATURAL JOIN, and the non-existent SIZEOF keyword.

(A) is CORRECT: Standard correlated subquery using EXISTS to check whether the student's rollno appears in Enrolled with courseno = 1470.
(B) is INCORRECT: SIZEOF is not a valid SQL keyword/operator.
(C) is CORRECT: Standard correlated subquery checking that the count of matching enrollment rows for S.rollno and courseno = 1470 is greater than 0.
(D) is CORRECT: Students and Enrolled share only the attribute rollno, so NATURAL JOIN performs an equijoin on S.rollno = E.rollno, and WHERE E.courseno = 1470 filters for students enrolled in course 1470.

Final answer

Options (A), (C), (D)

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2025 Computer Science and Information Technology - Session 2 (CS2)
Question: 54 of 65
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source