Question Details

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?

Options

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;

Show Answer
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;

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.

Transparent Access on Mindyard

Built for Learners & Educators Alike

Completely free for students. Educators & coaching institutes get 2 months free Pro trial to create tests.

FREE FOR STUDENTS

Practice & Learn Free for Students

Access unlimited practice questions, detailed step-by-step solutions, and exam study materials — completely free for students.

  • Unlimited mock tests & question bank practice
  • Full access to curated study library & formula notes
  • 100% free for students • No credit card required
2 MONTHS FREE TRIAL

Educators & Coaching Institutes

Create and conduct professional online assessments with AI question generation, tab proctoring, and comprehensive batch management.

  • AI question uploader & rapid test generator
  • Anti-cheating tab proctoring & performance reports
  • Full Pro access free for 60 days • No upfront commitment
Discover more resources

You may also like

Mock Tests

View All
  • GATE
  • beginner
  • 3 hours
  • civil engineering

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.

  • GATE
  • beginner
  • 3 hours
  • electronics and communication engineering

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.

Ask AI Tutor
5 left
Q1 View Question & Options
AI Tutor is solving this question...
Reading question context & options...
Ask your own doubt or choose a quick prompt: