MySQL views, indexes and EXPLAIN: reusable queries and faster lookups

Answers — Views, indexes and EXPLAIN

1. Easy. Create the completed_order_totals view (order_id, customer_id, order_date, total for completed orders) and list it. Target: 6 rows; totals 300, 1300, 1400, 550, 1300, 1300

   CREATE VIEW completed_order_totals AS SELECT o.order_id, o.customer_id, o.order_date, SUM(i.quantity * i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.order_id, o.customer_id, o.order_date; SELECT * FROM completed_order_totals ORDER BY order_id;

2. Easy. Using the view, list completed orders worth more than 1000. Target: 4 rows: 102, 103, 106, 108

   CREATE VIEW completed_order_totals AS SELECT o.order_id, o.customer_id, o.order_date, SUM(i.quantity * i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.order_id, o.customer_id, o.order_date; SELECT order_id, total FROM completed_order_totals WHERE total > 1000 ORDER BY order_id;

3. Medium. Create a second view customer_revenue on top of completed_order_totals (customer_name, orders, revenue) and list it, highest revenue first. Target: 3 rows: Bilal 2600.00, Dev 1850.00, Asha 1700.00

   CREATE VIEW completed_order_totals AS SELECT o.order_id, o.customer_id, o.order_date, SUM(i.quantity * i.unit_price) AS total FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY o.order_id, o.customer_id, o.order_date; CREATE VIEW customer_revenue AS SELECT c.customer_name, COUNT(*) AS orders, SUM(t.total) AS revenue FROM completed_order_totals t JOIN customers c ON c.customer_id = t.customer_id GROUP BY c.customer_id, c.customer_name; SELECT * FROM customer_revenue ORDER BY revenue DESC;

4. Medium. Create a view of Pune customers, then replace it with CREATE OR REPLACE VIEW so it shows Mumbai customers, and query it. Target: 2 rows: Bilal, Esha

   CREATE VIEW city_customers AS SELECT customer_id, customer_name FROM customers WHERE city = 'Pune'; CREATE OR REPLACE VIEW city_customers AS SELECT customer_id, customer_name FROM customers WHERE city = 'Mumbai'; SELECT * FROM city_customers ORDER BY customer_id;

5. Medium. Create an index on orders(order_date) and list the index names on orders. Target: PRIMARY, customer_id and idx_orders_order_date

   CREATE INDEX idx_orders_order_date ON orders (order_date); SHOW INDEX FROM orders;

6. Hard. Create a view unsold_products listing products that never appear in a completed order, and query it. Target: 1 row: Planner

   CREATE VIEW unsold_products AS SELECT p.product_id, p.product_name FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items i JOIN orders o ON o.order_id = i.order_id WHERE i.product_id = p.product_id AND o.status = 'completed'); SELECT * FROM unsold_products;

7. Hard. Large dataset: count customer 42's orders by status. Target: Up to 3 rows (completed / cancelled / pending) whose counts add up to customer 42's order count

   SELECT status, COUNT(*) AS orders FROM orders WHERE customer_id = 42 GROUP BY status ORDER BY status;

8. Hard. Large dataset: add a composite index for status + order_date, then count completed orders in March 2026. Target: 1 row: the number of completed March 2026 orders (same answer with or without the index)

   CREATE INDEX idx_orders_status_date ON orders (status, order_date); SELECT COUNT(*) AS completed_march FROM orders WHERE status = 'completed' AND order_date >= '2026-03-01' AND order_date < '2026-04-01';

9. Professional. Create a monthly_revenue view (month, revenue for completed orders) and query it with a running total. Target: 3 rows: 2026-01 1600 / 1600, 2026-02 1950 / 3550, 2026-03 2600 / 6150

   CREATE VIEW monthly_revenue AS SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY month; SELECT month, revenue, SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM monthly_revenue ORDER BY month;

10. Hard. Audit for orphan order items and inspect a date-filter plan. Target: Zero orphan rows. Record EXPLAIN's key, rows and type; no timing/speedup claim.

    SELECT COUNT(*) AS orphans FROM order_items i LEFT JOIN orders o ON o.order_id=i.order_id WHERE o.order_id IS NULL;
    EXPLAIN SELECT order_id FROM orders WHERE order_date >= '2026-02-01' AND order_date < '2026-03-01';
    -- Candidate for a larger workload: CREATE INDEX idx_orders_date ON orders(order_date);
    -- Inspect SHOW INDEX FROM orders before adding an index; DDL is not rolled back.

11. Hard. Data-quality check: find duplicate customer emails (excluding NULL). Target: 0 rows — UNIQUE prevents duplicates

    SELECT email, COUNT(*) AS n FROM customers WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1;

12. Hard. Data-quality check: find order lines whose quantity or unit_price is zero or negative. Target: 0 rows

    SELECT * FROM order_items WHERE quantity <= 0 OR unit_price <= 0;

13. Hard. Data-quality check: find orders with no items at all. Target: 0 rows

    SELECT o.order_id FROM orders o LEFT JOIN order_items i ON i.order_id = o.order_id WHERE i.order_id IS NULL;

14. Hard. Run EXPLAIN on a lookup by customer_id and record type, key and rows. Target: Usually type ref on the customer_id index; a tiny table may show ALL. No speed claim.

    EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

15. Professional. One-result data-quality report: orphan items, orders without items, negative prices, duplicate emails — each with its count. Target: 4 rows, every count 0

    SELECT 'orphan_items' AS check_name, COUNT(*) AS problems FROM order_items i LEFT JOIN orders o ON o.order_id = i.order_id WHERE o.order_id IS NULL UNION ALL SELECT 'orders_without_items', COUNT(*) FROM orders o LEFT JOIN order_items i ON i.order_id = o.order_id WHERE i.order_id IS NULL UNION ALL SELECT 'negative_prices', COUNT(*) FROM products WHERE price < 0 UNION ALL SELECT 'duplicate_emails', COUNT(*) FROM (SELECT email FROM customers WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1) d;