GATE 2025 CS (CS2) – 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?
Practise this question in The GATE Grind →
Show answer and explanation
Correct answer: (A) SELECT S.name FROM Students S WHERE EXISTS (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno);; (C) SELECT S.name FROM Students S WHERE 0 < (SELECT COUNT(*) FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno);; (D) SELECT S.name FROM Students S NATURAL JOIN Enrolled E WHERE E.courseno = 1470;
Explanation
The task is to return the names of students enrolled in course 1470.
- **(A)** `EXISTS (SELECT * FROM Enrolled ... courseno=1470 AND rollno=S.rollno)`: correlated subquery that is true exactly for students enrolled in 1470. Correct.
- **(B)** `SIZEOF(...)` is not an SQL operator, so this is a syntax error. Incorrect.
- **(C)** `0 < (SELECT COUNT(*) ...)` with the same correlation: true for enrolled students. Correct.
- **(D)** `Students S NATURAL JOIN Enrolled E WHERE E.courseno = 1470`: the natural join uses the common column `rollno` (the only one in common), so it returns students enrolled in 1470. Correct.
Answer: A, C, D.
More Relational Model: Relational Algebra, Tuple Calculus, SQL questions
- GATE 2024 CS (CS1) Q35
- GATE 2024 CS (CS2) Q45
- GATE 2023 CS Q16
- GATE 2023 CS Q61
- GATE 2022 CS Q25
- GATE 2022 CS Q56
All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →