Subquery challenge
- SQL course
- Intermediate
- Lesson 114/115
- 6 min
Write a complex condition with a correlated subquery.
Subquery challenge
In this challenge you will find products above average with a correlated subquery.
SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);This query products above their category average.
name is selected from products p. WHERE keeps only the rows where price > (SELECT AVG(price) FROM products WHERE category = … holds.
Practice Subquery challenge
Find products above their category average with a correlated subquery.
Starter code
-- subquery challenge
SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category =Sample solution
SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);Expected output
name Laptop Telefon
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does a correlated subquery use?
- A column from the outer query Correct answer
- Only constants
- GROUP BY
- LIMIT
Explanation: A correlated subquery uses an outer query column.
2 Add p.category
Question
WHERE category = ____
Answer
WHERE category = p.categoryExplanation: p.category is the right answer.