SQL Subquery Questions with Answers

Single Row Subquery

Q1: Find the customer(s) who placed an order equal to the highest order amount.
SELECT name FROM customers

WHERE customer_id IN (

SELECT customer_id FROM orders

WHERE amount = (SELECT MAX(amount) FROM orders)

);

Q2: Get the email of the customer who made the earliest order.
SELECT email FROM customers

WHERE customer_id = (

SELECT customer_id FROM orders

ORDER BY order_date ASC

LIMIT 1

);

Q3: List customers from the same country as the customer named 'Alice Smith'.
SELECT name FROM customers

WHERE country = (

SELECT country FROM customers

WHERE name = 'Alice Smith'

);

Q4: Find orders that match the average order amount.

SELECT * FROM orders

WHERE amount = (SELECT AVG(amount) FROM orders);

Q5: Get the name of the customer who placed the last order.
SELECT name FROM customers

WHERE customer_id = (

SELECT customer_id FROM orders

ORDER BY order_date DESC

LIMIT 1

);
Multiple Row Subquery
Q1: List names of customers who have placed any order.
SELECT name FROM customers

WHERE customer_id IN (

SELECT DISTINCT customer_id FROM orders

);

Q2: Find customers from countries that have at least one order placed.
SELECT DISTINCT country FROM customers

WHERE country IN (

SELECT country FROM customers

WHERE customer_id IN (SELECT customer_id FROM orders)

);

Q3: List customers who made orders of amount greater than 100.
SELECT name FROM customers

WHERE customer_id IN (

SELECT customer_id FROM orders

WHERE amount > 100

);

Q4: Get the customer IDs who made orders on February 2025.
SELECT customer_id FROM orders

WHERE order_date IN (

SELECT order_date FROM orders

WHERE order_date BETWEEN '2025-02-01' AND '2025-02-28'

);

Q5: Find all order amounts made by customers from India.

SELECT amount FROM orders

WHERE customer_id IN (

SELECT customer_id FROM customers

WHERE country = 'India'

);

Correlated Subquery
Q1: Find customers who placed more than one order.
SELECT name FROM customers c

WHERE 1 < (

SELECT COUNT(*) FROM orders o

WHERE o.customer_id = c.customer_id

);

Q2: Get orders where the amount is greater than the average amount of that customer's orders.
SELECT * FROM orders o1

WHERE amount > (

SELECT AVG(amount) FROM orders o2

WHERE o2.customer_id = o1.customer_id

);

Q3: List customers who placed an order with the maximum amount they ever spent.
SELECT name FROM customers c

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.customer_id = c.customer_id

AND amount = (

SELECT MAX(amount) FROM orders o2

WHERE o2.customer_id = c.customer_id

);

Q4: Find orders that are the first order for each customer.
SELECT * FROM orders o1

WHERE order_date = (

SELECT MIN(order_date) FROM orders o2

WHERE o2.customer_id = o1.customer_id

);

Q5: List customers who placed exactly two orders.

SELECT name FROM customers c

WHERE 2 = (

SELECT COUNT(*) FROM orders o

 WHERE o.customer_id = c.customer_id

);

Non-Correlated Subquery
Q1: List all customers who are not from the same country as 'Bob Johnson'.
SELECT name FROM customers

WHERE country != (

SELECT country FROM customers WHERE name = 'Bob Johnson'

);

Q2: Get the customers who placed orders greater than the average order amount.
SELECT name FROM customers

WHERE customer_id IN (

SELECT customer_id FROM orders

WHERE amount > (SELECT AVG(amount) FROM orders)

);

Q3: Get the highest order amount.

SELECT MAX(amount) AS highest_order FROM orders;

Q4: Show the total number of customers who made at least one order.
SELECT COUNT(*) FROM customers

WHERE customer_id IN (

SELECT DISTINCT customer_id FROM orders

);

Q5: Get all orders made by customers from 'UK'.

SELECT * FROM orders

WHERE customer_id IN (

SELECT customer_id FROM customers WHERE country = 'UK'

);

Nested Subquery
Q1: Get customers who made orders greater than the average of the highest order per customer.
SELECT name FROM customers

WHERE customer_id IN (
 SELECT customer_id FROM orders

WHERE amount > (

SELECT AVG(max_amount) FROM (

SELECT MAX(amount) AS max_amount

FROM orders

GROUP BY customer_id

) AS max_per_customer

);

Q2: List customers whose minimum order is greater than 50.

SELECT name FROM customers

WHERE customer_id IN (

SELECT customer_id FROM (

SELECT customer_id, MIN(amount) as min_order

FROM orders

GROUP BY customer_id

HAVING min_order > 50

) AS min_orders

);

Q3: Find customers who made more orders than the average order count per customer.
SELECT name FROM customers

WHERE customer_id IN (

SELECT customer_id FROM (

SELECT customer_id, COUNT(*) AS order_count

FROM orders

GROUP BY customer_id

HAVING COUNT(*) > (

SELECT AVG(order_num) FROM (

SELECT COUNT(*) AS order_num

FROM orders

GROUP BY customer_id

) AS counts

)
 ) AS more_orders

);

Q4: List all orders with amount greater than the minimum order made by customers from 'Canada'.
SELECT * FROM orders

WHERE amount > (

SELECT MIN(amount) FROM orders

WHERE customer_id IN (

SELECT customer_id FROM customers WHERE country = 'Canada'

);

Q5: Get customers whose max order is greater than the overall average order.
SELECT name FROM customers

WHERE customer_id IN (

SELECT customer_id FROM (

SELECT customer_id, MAX(amount) AS max_order

FROM orders

GROUP BY customer_id

) AS maxes

WHERE max_order > (SELECT AVG(amount) FROM orders)

);