Q1. Find books that are priced higher than the average price of all books.
SQL Query:
SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books);Explanation:
- Inner query calculates average price of books
- Outer query selects books with price greater than average
- This is a scalar subquery (returns one value)
Q2. Find customers who have placed at least one order.
SQL Query:
SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);Explanation:
- Inner query gets customer_ids from orders
- Outer query finds matching customers
- Uses IN operator with subquery
Q3. Find books that have never been ordered.
SQL Query:
SELECT title FROM books WHERE book_id NOT IN (SELECT book_id FROM order_items);Explanation:
- Inner query gets all ordered book_ids
- Outer query selects books not present in that list
- Demonstrates NOT IN subquery
Q4. Find customers who have placed orders worth more than the average order amount.
SQL Query:
SELECT DISTINCT c.name
FROM customers c
WHERE c.customer_id IN (
SELECT o.customer_id
FROM orders o
WHERE o.order_id IN (
SELECT oi.order_id
FROM order_items oi
GROUP BY oi.order_id
HAVING SUM(oi.quantity * oi.unit_price) >
(SELECT AVG(quantity * unit_price) FROM order_items)
)
);Explanation:
- Innermost query calculates average order item value
- Middle query calculates total order value per order
- Outer query finds customers with high-value orders
- Example of multi-level nested subqueries
Q5. Find the most expensive book(s).
SQL Query:
SELECT title, price FROM books WHERE price = (SELECT MAX(price) FROM books);Explanation:
- Inner query finds maximum price
- Outer query returns book(s) with that price
- Useful for top-N type queries
Q6. Find customers who have never placed any orders.
SQL Query:
SELECT name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);Explanation:
- Inner query gets customers who have orders
- Outer query excludes them
- Shows negative condition using subquery
Practice Questions
Q1. Find the books whose price is less than the average price of books written by the same author.
Q2. Find customers who have placed more orders than the average number of orders placed by all customers.
Q3. Find the book(s) that have been ordered the maximum number of times (based on total quantity sold).
Q4. Find customers who have ordered at least one book whose price is higher than the average price of all books.
Q5. Find orders where the total order amount is greater than the average total order amount.
Q6. Find books that are ordered in all orders (i.e., books present in every order).
Q7. Find customers who have ordered only those books whose price is above 500.
Q8. Find the second highest priced book using a subquery.
Q9. Find customers whose total purchase amount is equal to the maximum purchase amount among all customers.
Q10. Find books that have been ordered by customers who have never ordered any other book.
