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?
Solution :
The correct options are:
1. SELECT S.name FROM Students S WHERE EXISTS (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno);
2. SELECT S.name FROM Students S WHERE 0 < (SELECT COUNT(*) FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno);
3. SELECT S.name FROM Students S NATURAL JOIN Enrolled E WHERE E.courseno = 1470;
Let us analyze the query requirements and evaluate each option step-by-step to understand why they are correct or incorrect.
The objective is to retrieve the names of students (S.name) who are enrolled in the course with courseno = 1470.
Analysis of Option 1 (Correct):
SELECT S.name
FROM Students S
WHERE EXISTS (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno);
This query uses a correlated subquery with the EXISTS operator. For each student S in the Students table, the subquery checks if there exists at least one row in the Enrolled table (E) where the course number is 1470 and the student's roll number matches S.rollno. If the subquery returns at least one row, EXISTS evaluates to true, and the student's name is selected. This accurately retrieves the names of students enrolled in course 1470.
Analysis of Option 2 (Incorrect):
SELECT S.name
FROM Students S
WHERE SIZEOF (SELECT * FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno) > 0;
SQL does not have a SIZEOF operator to check the cardinality of a subquery result set. Therefore, this query is syntactically invalid in standard SQL.
Analysis of Option 3 (Correct):
SELECT S.name
FROM Students S
WHERE 0 < (SELECT COUNT(*) FROM Enrolled E WHERE E.courseno = 1470 AND E.rollno = S.rollno);
This query uses a correlated subquery with the aggregation function COUNT(*). For each student S, it counts the number of enrollment records in course 1470. If this count is strictly greater than 0, it means the student is enrolled in course 1470, and their name is included in the output. This is a semantically correct SQL query that produces the desired result.
Analysis of Option 4 (Correct):
SELECT S.name
FROM Students S NATURAL JOIN Enrolled E
WHERE E.courseno = 1470;
A NATURAL JOIN automatically joins two tables on all columns that share the same name. Looking at the schemas:
• Students (rollno: integer, name: string, age: integer, cgpa: real)
• Enrolled (rollno: integer, courseno: integer, grade: string)
The only shared attribute name is rollno. Thus, Students NATURAL JOIN Enrolled joins the tables on the equality condition S.rollno = E.rollno. The WHERE clause filters the joined records for E.courseno = 1470, and the SELECT clause extracts the names of these students. This is a direct and standard way to solve the query.
Completely free for students. Educators & coaching institutes get 2 months free Pro trial to create tests.
Access unlimited practice questions, detailed step-by-step solutions, and exam study materials — completely free for students.
Create and conduct professional online assessments with AI question generation, tab proctoring, and comprehensive batch management.
Prepare for GATE 2026 Civil Engineering with the latest question papers, previous year papers, detailed solutions, syllabus, exam pattern, preparation tips, mock tests, and subject-wise study material. Download GATE 2026 CE question papers with solutions in PDF format and boost your preparation for top engineering institutes and PSU recruitment.
Prepare effectively for GATE 2026 Electronics & Communication Engineering (EC) with this comprehensive Question Paper with Solutions. This resource includes detailed explanations, concept-wise solutions, previous year paper analysis, and exam-oriented practice to help aspirants strengthen core subjects like Electronic Devices, Analog Circuits, Digital Circuits, Signals & Systems, Network Theory, Control Systems, Electromagnetics, Communications, and more. Perfect for revision, self-assessment, and boosting your GATE 2026 EC preparation strategy.
Copyright © 2026 Combe Labs Private Limited. All Rights Reserved.