Solution:Step 1: Identify teacher_id for 'Smith'The innermost subquery gets teacher_id where teacher_name is 'Smith'.Step 2: Find class_id(s) taught by that teacherThe middle subquery selects class_id from Classes where teacher_id matches.Step 3: Select student names in those classesThe outer query

[Solved] Given tables:Students(student_id, name, class_id)Classes(class_id, teacher_id)Teachers(teacher_id, teacher_name)Write a query to find names of students taught by teacher 'Smith'.... | SQL

SQL - Subqueries

Given tables:
Students(student_id, name, class_id)
Classes(class_id, teacher_id)
Teachers(teacher_id, teacher_name)
Write a query to find names of students taught by teacher 'Smith'. Which query is correct?

ASELECT name FROM Students WHERE class_id IN (SELECT class_id FROM Classes WHERE teacher_id IN (SELECT teacher_id FROM Teachers WHERE teacher_name = 'Smith'));

BSELECT name FROM Students WHERE class_id = (SELECT class_id FROM Classes WHERE teacher_id = (SELECT teacher_id FROM Teachers WHERE teacher_name = 'Smith'));

CSELECT name FROM Students WHERE class_id IN (SELECT teacher_id FROM Teachers WHERE teacher_name = 'Smith');

DSELECT name FROM Students WHERE student_id IN (SELECT student_id FROM Teachers WHERE teacher_name = 'Smith');

Step-by-Step Solution

Solution:

Step 1: Identify teacher_id for 'Smith'
The innermost subquery gets teacher_id where teacher_name is 'Smith'.
Step 2: Find class_id(s) taught by that teacher
The middle subquery selects class_id from Classes where teacher_id matches.
Step 3: Select student names in those classes
The outer query selects student names where class_id is in the list from step 2.
Final Answer:
SELECT name FROM Students WHERE class_id IN (SELECT class_id FROM Classes WHERE teacher_id IN (SELECT teacher_id FROM Teachers WHERE teacher_name = 'Smith')); -> Option A
Quick Check:
Nested IN subqueries find matching students [OK]

Quick Trick: Use nested IN subqueries to link related tables [OK]

Common Mistakes:

MISTAKES

Using = instead of IN for multiple matches
Comparing unrelated columns
Selecting wrong columns in subqueries

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Given tables:

Step 1: Identify teacher_id for 'Smith'

Step 2: Find class_id(s) taught by that teacher

Step 3: Select student names in those classes

Final Answer:

Quick Check:

Want More Practice?