Practise subqueries, IN and EXISTS, ANY and ALL, CASE expressions, UNION and UNION ALL, and common table expressions (CTEs).
Review first: Subqueries, CASE, UNION, UNION ALL, EXISTS, IN, ANY / ALL, Common Table Expressions
products
order_items
store_customers
online_customers
Return the name and price of the most expensive product, using a subquery (not ORDER BY ... LIMIT).
Compare price with (SELECT MAX(price) FROM products).
SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);name | price
-------+------
Laptop | 55000Unlike LIMIT 1, this returns every product that shares the top price if there is a tie.
Return the name, category and price of products that cost more than the average price of their own category.
Use a correlated subquery that calculates the average for the outer row's category.
SELECT p.name, p.category, p.price
FROM products p
WHERE p.price > (
SELECT AVG(p2.price)
FROM products p2
WHERE p2.category = p.category
)
ORDER BY p.category;name | category | price
-----------+-------------+------
Laptop | Electronics | 55000
Desk Chair | Furniture | 7000The Electronics average is 23166.67 (only the Laptop is above it) and the Furniture average is 5750 (the Desk Chair is above it). Stationery has one product, which equals its own average, so it is not more than the average.
Return the names of products that appear in at least one order, sorted by name. Use IN with a subquery.
WHERE product_id IN (SELECT product_id FROM order_items).
SELECT name
FROM products
WHERE product_id IN (SELECT product_id FROM order_items)
ORDER BY name;name
----------
Headphones
Laptop
Monitor
NotebookHeadphones and Notebook were ordered twice, but each product appears only once, because the query reads products. A JOIN would repeat them unless you added DISTINCT.
Return the names of products that have never been ordered. Use NOT EXISTS.
For each product, check whether a matching order_items row exists.
SELECT p.name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM order_items oi
WHERE oi.product_id = p.product_id
)
ORDER BY p.name;name
----------
Bookshelf
Desk ChairPrefer NOT EXISTS over NOT IN here. If the subquery of a NOT IN ever returns a NULL, NOT IN returns no rows at all; NOT EXISTS does not have that trap.
Return the name and price of products that cost more than every Furniture product. Use ALL.
price > ALL (subquery) is true when the price is greater than every value the subquery returns.
SELECT name, price
FROM products
WHERE price > ALL (
SELECT price FROM products WHERE category = 'Furniture'
)
ORDER BY price DESC;name | price
--------+------
Laptop | 55000
Monitor | 12000The most expensive Furniture item costs 7000, so > ALL (...) is the same as > (SELECT MAX(price) ...). That MAX form also works in SQLite, which does not support ANY and ALL.
Return each product's name, price, and a band column:
Premium for 10000 and aboveStandard for 1000 to 9999Budget for anything cheaperSort by price, highest first.
CASE checks its WHEN conditions from top to bottom and uses the first one that is true.
SELECT name, price,
CASE
WHEN price >= 10000 THEN 'Premium'
WHEN price >= 1000 THEN 'Standard'
ELSE 'Budget'
END AS band
FROM products
ORDER BY price DESC;name | price | band
-----------+-------+---------
Laptop | 55000 | Premium
Monitor | 12000 | Premium
Desk Chair | 7000 | Standard
Bookshelf | 4500 | Standard
Headphones | 2500 | Standard
Notebook | 60 | BudgetFor each category, return the number of products as products and how many of them cost 5000 or more as over_5000. Sort by category.
Put a CASE inside SUM, returning 1 for rows that match and 0 for the rest.
SELECT category,
COUNT(*) AS products,
SUM(CASE WHEN price >= 5000 THEN 1 ELSE 0 END) AS over_5000
FROM products
GROUP BY category
ORDER BY category;category | products | over_5000
------------+----------+----------
Electronics | 3 | 2
Furniture | 2 | 1
Stationery | 1 | 0This "conditional aggregation" pattern lets one query produce several counts side by side, which is how most pivot-style reports are written.
Return a single list of customer names from both customer tables, with no duplicates, sorted by name.
UNION combines the results and removes duplicate rows.
SELECT name FROM store_customers
UNION
SELECT name FROM online_customers
ORDER BY name;name
-----
Asha
John
Kiran
Meera
RaviThe ORDER BY at the end sorts the combined result, not just the second query.
Using UNION ALL, find the customers who appear in both lists. Return their name and the number of lists they appear in as lists.
UNION ALL keeps duplicates. Wrap it in a subquery, group by name, and keep the names that appear more than once.
SELECT name, COUNT(*) AS lists
FROM (
SELECT name FROM store_customers
UNION ALL
SELECT name FROM online_customers
) AS all_names
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY name;name | lists
-----+------
Asha | 2
Ravi | 2Many databases also support INTERSECT (SELECT name FROM store_customers INTERSECT SELECT name FROM online_customers), which answers "in both" directly. This version also tells you how many lists a name is in.
Using a CTE called product_revenue, calculate each ordered product's revenue (price * quantity, summed over its orders). Then return the products whose revenue is above 5000, highest first.
Put the join and GROUP BY inside WITH product_revenue AS (...), then filter in the main query.
WITH product_revenue AS (
SELECT p.name, SUM(p.price * oi.quantity) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
)
SELECT name, revenue
FROM product_revenue
WHERE revenue > 5000
ORDER BY revenue DESC;name | revenue
-----------+--------
Laptop | 55000
Monitor | 12000
Headphones | 7500Headphones were ordered 2 + 1 = 3 times at 2500, so their revenue is 7500. Notebooks sold 15 units at 60, only 900, so they are filtered out.