You've now seen both simple and correlated subqueries. Simple subqueries run once, while correlated subqueries run for each row of the outer query. Let's practice identifying and building them.

Engagement Message

Ready to tackle some advanced subquery problems?

Type

Swipe Left or Right

Practice Question

Let's practice identifying the type of subquery. Swipe left for simple subqueries and right for correlated subqueries.

Labels

• Left Label: Simple Subquery
• Right Label: Correlated Subquery

Left Label Items

• ... WHERE price > (SELECT AVG(price) FROM products)
• ... WHERE customer_id IN (SELECT id FROM gold_members)
• ... FROM (SELECT category, COUNT(*) FROM products GROUP BY category)

Right Label Items

• ... FROM orders o WHERE order_date = (SELECT MAX(d) FROM orders d WHERE d.customer_id = o.customer_id)
• ... FROM employees e WHERE salary > (SELECT AVG(s) FROM employees s WHERE s.dept = e.dept)

Type

Fill in the Blanks

Markdown With Blanks

Fill in the blanks to find all products that have been ordered at least once. This query uses a correlated subquery with EXISTS to check for a match in the order_items table for each product.

Suggested Answers

• EXISTS
• p.product_id
• oi.product_id