Practice Exercises

Time to practice. Below are exercises using these tables. Do not just read them. Open a SQL editor and write the queries yourself. Struggling is learning.

Table Schema for Exercises

Exercises

1. List all orders with the customer's full name and email (INNER JOIN)
2. Find all customers who have never placed an order (LEFT JOIN + IS NULL)
3. Show each order with its items and product names (3-table JOIN)
4. Find products that have never been ordered (LEFT JOIN or NOT EXISTS)
5. Find customers whose average order value is above the overall average (subquery)
6. Show each employee with their manager's name (self-join)
7. Find orders where the stored total does not match the sum of line items (JOIN + GROUP BY + HAVING)
8. Find orders that have a payment but the payment amount differs from the order total (data mismatch)
9. List all customers with their total number of orders and total amount spent (LEFT JOIN + aggregation — include customers with 0 orders)
10. Find duplicate orders: same customer, same amount, same date (self-join)

Challenge Exercise

Write a single query that shows: customer name, total orders, total amount spent, most recent order date, and their most expensive order amount. Include customers who have never ordered (show 0 for all counts). This combines LEFT JOIN, GROUP BY, and aggregate functions.

-- Hint: Start with this structure
SELECT
  c.first_name,
  c.last_name,
  COUNT(o.order_id) AS total_orders,
  COALESCE(SUM(o.total_amount), 0) AS total_spent,
  MAX(o.order_date) AS last_order_date,
  COALESCE(MAX(o.total_amount), 0) AS biggest_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;