GATE CS 2025 Set 2 | Question 54

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;

Answer

(A), (C), (D)

Explanation

(A) For each student, the correlated EXISTS subquery is true exactly when an Enrolled row has that student's roll number and course 1470. Correct.
(B) SIZEOF is not an SQL operator or aggregate function, so this is not a valid query.
(C) The correlated COUNT(*) counts the student's enrolments in course 1470, and requiring it to be greater than 0 is the same test as (A). Correct.
(D) rollno is the only attribute common to Students and Enrolled, so the natural join pairs each student with their enrolments and the filter keeps course 1470. Because (rollno, courseno) is the key of Enrolled, each such student appears once. Correct.

Answer: A, C, D