Consider the following relational schema: Students (rollno: integer, name:…
2025
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?
- A.
SELECT S.name
FROM Students S
WHERE EXISTS (SELECT * FROM Enrolled E
WHERE E.courseno = 1470
AND E.rollno = S.rollno); - B.
SELECT S.name
FROM Students S
WHERE SIZEOF (SELECT * FROM Enrolled E
WHERE E.courseno = 1470
AND E.rollno = S.rollno) > 0; - 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;
Attempted by 113 students.
Show answer & explanation
Correct answer: A, C, D
Answer: the following queries retrieve the student names enrolled in course number 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 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;
Preferred explicit join form (clear and safe): SELECT S.name FROM Students S JOIN Enrolled E ON S.rollno = E.rollno WHERE E.courseno = 1470;
Explanation:
The EXISTS correlated subquery works because it checks for the existence of at least one Enrolled row with the same roll number and the given courseno; it returns true as soon as a match is found.
The COUNT(*) correlated subquery is also valid: it counts matching Enrolled rows for each student, and the outer WHERE selects students with a count greater than zero. It produces the same result but may be less efficient because it computes counts rather than stopping at the first match.
The NATURAL JOIN version returns the same students because NATURAL JOIN matches on the common rollno column. It is syntactically valid, but NATURAL JOIN can be fragile if schemas change; using an explicit JOIN ... ON is clearer and safer.
The form using SIZEOF is not standard SQL. Use COUNT(*) or EXISTS instead.
A video solution is available for this question — log in and enroll to watch it.