Sub-query in a WHERE clause with IN
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which WHERE clause uses a sub-query to find students who have at least one result?
2. What does the sub-query (SELECT studentId FROM results) return to the outer query?
3. Which keyword tests whether a value is found in the list returned by a sub-query?
4. You want students who have NO rows in the results table. Which is correct?
5. Which sub-query is valid inside IN to find products that have been ordered?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1WHERE studentId IN (SELECT studentId FROM results) | AKeeps only the students whose studentId appears in the results table | |
| 2WHERE studentId NOT IN (SELECT studentId FROM orders) | BSELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results WHERE mark > 80); | |
| 3A sub-query is a SELECT written inside brackets within the WHERE clause of another query | CKeeps only the students who have never placed an order | |
| 4A sub-query can do the same job as a join: finding students linked to rows in another table | DThe sub-query is missing its brackets. It must be written as IN (SELECT studentId FROM results). | |
| 5SELECT firstName FROM students WHERE studentId IN SELECT studentId FROM results; | EThe sub-query returns the wrong column. IN compares studentId values, so the sub-query must select studentId, not firstName (which is not even in results). | |
| 6SELECT firstName FROM students WHERE studentId IN (SELECT firstName FROM results); | FSELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM orders); |
C
Definitions
IN (SELECT studentId FROM results)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName FROM students
WHERE studentId __
(SELECT studentId FROM results);
2.
SELECT firstName FROM students
WHERE studentId IN
(______ studentId FROM results);
3.
SELECT name FROM products
WHERE productId ___ IN
(SELECT productId FROM orders);
4.
SELECT firstName FROM students
WHERE studentId IN
(SELECT _________ FROM results);
5.
SELECT name FROM subjects
WHERE subjectId IN
(SELECT subjectId FROM _______);
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT firstName students
B
WHERE studentId IN (
C
SELECT studentId
D
FROM results
E
);
Question 2
A
SELECT name FROM products
B
WHERE productId = (
C
SELECT productId
D
FROM orders
E
);
Question 3
A
SELECT firstName
B
FROM students
C
WHERE studentId NOT (
D
SELECT studentId
E
FROM orders);
Question 4
A
SELECT firstName, house
B
FROM students
C
WHERE firstName IN (
D
SELECT studentId
E
FROM results
F
WHERE mark > 90);
Question 5
A
SELECT name FROM product
B
WHERE productId IN (
C
SELECT productId
D
FROM orders
E
WHERE quantity > 2);
Answer Key
Sub-query in a WHERE clause with IN
Section A: Quick Questions
Q1
A — WHERE studentId IN (SELECT studentId FROM results)
Q2
A — A list of studentId values
Q3
B — IN
Q4
A — WHERE studentId NOT IN (SELECT studentId FROM results)
Q5
B — WHERE productId IN (SELECT productId FROM orders)
Section B: Match the Pairs
Q1
WHERE studentId IN (SELECT studentId FROM results) → Keeps only the students whose studentId appears in the results table
Q2
WHERE studentId NOT IN (SELECT studentId FROM orders) → Keeps only the students who have never placed an order
Q3
A sub-query is a SELECT written inside brackets within the WHERE clause of another query → SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM orders);
Q4
A sub-query can do the same job as a join: finding students linked to rows in another table → SELECT firstName FROM students WHERE studentId IN (SELECT studentId FROM results WHERE mark > 80);
Q5
SELECT firstName FROM students WHERE studentId IN SELECT studentId FROM results; → The sub-query is missing its brackets. It must be written as IN (SELECT studentId FROM results).
Q6
SELECT firstName FROM students WHERE studentId IN (SELECT firstName FROM results); → The sub-query returns the wrong column. IN compares studentId values, so the sub-query must select studentId, not firstName (which is not even in results).
Section C: Definitions
Q1
IN (SELECT studentId FROM results) — A sub-query is a SELECT inside brackets in a WHERE clause. It returns a list of values, and IN keeps only the rows whose value is in that list. NOT IN keeps the rows that are not in it.Section D: Fill in the Blanks
Q1
SELECT firstName FROM students
WHERE studentId IN
(SELECT studentId FROM results);Q2
SELECT firstName FROM students
WHERE studentId IN
(SELECT studentId FROM results);Q3
SELECT name FROM products
WHERE productId NOT IN
(SELECT productId FROM orders);Q4
SELECT firstName FROM students
WHERE studentId IN
(SELECT studentId FROM results);Q5
SELECT name FROM subjects
WHERE subjectId IN
(SELECT subjectId FROM results);Section E: Spot the Error
Q1
A — The FROM keyword is missing. The outer query must name its table with FROM, so it should be SELECT firstName FROM students.
Q2
B — The sub-query returns a list of productId values. The = operator only compares to one value, so it should be WHERE productId IN (.
Q3
C — NOT must be followed by IN to test against the list from the sub-query. It should be WHERE studentId NOT IN (.
Q4
C — The sub-query returns studentId values, so the outer column must be studentId too. Comparing firstName with a list of IDs is wrong. It should be WHERE studentId IN (.
Q5
A — There is no table called product. The table is products, so it should be SELECT name FROM products.