📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 12 of 12

Time to build what a manager actually asks for: “Give me the half-year sales summary.” Every query below uses something from earlier lessons. Try each yourself first; the exercise file shows the expected numbers.

1. Total delivered sales

SELECT SUM(o.qty * p.price) AS sales
FROM orders o JOIN products p ON p.product_id = o.product_id
WHERE o.status = 'Delivered';

2. GST collected

SELECT ROUND(SUM(o.qty * p.price * p.gst_rate)) AS gst
FROM orders o JOIN products p ON p.product_id = o.product_id
WHERE o.status = 'Delivered';

3. Top customer and best month

SELECT c.name, SUM(o.qty * p.price) AS sales
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
              JOIN products p  ON p.product_id = o.product_id
WHERE o.status = 'Delivered'
GROUP BY c.customer_id ORDER BY sales DESC LIMIT 1;

SELECT strftime('%Y-%m', o.order_date) AS month, SUM(o.qty * p.price) AS sales
FROM orders o JOIN products p ON p.product_id = o.product_id
WHERE o.status = 'Delivered'
GROUP BY month ORDER BY sales DESC LIMIT 1;

4. Cancellation rate

SELECT ROUND(100.0 * SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_pct
FROM orders;

Note 100.0: without the decimal, integer division returns 0.

5. Region × category grid

SELECT c.region,
  SUM(CASE WHEN p.category = 'Computers'   THEN o.qty * p.price ELSE 0 END) AS computers,
  SUM(CASE WHEN p.category = 'Peripherals' THEN o.qty * p.price ELSE 0 END) AS peripherals,
  SUM(CASE WHEN p.category NOT IN ('Computers','Peripherals') THEN o.qty * p.price ELSE 0 END) AS other
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products  p ON p.product_id  = o.product_id
WHERE o.status = 'Delivered'
GROUP BY c.region ORDER BY c.region;

6. Make it reusable

Create the order_lines view from Lesson 11 and rewrite each query against it; they become two or three lines each. Save all queries in one sales_report.sql file with comments.

7. Get it into Excel

• In DB Browser: run a query, then Export to CSV from the results, and open in Excel.
• For live connections to SQL Server, MySQL or PostgreSQL: Excel Data › Get Data › From Database, paste the SQL, and Refresh pulls new numbers.
• Then build the chart and pivot from the Excel course’s final project on top.

You’ve finished the course

You can now select, filter, sort, aggregate, group, join, nest, label with CASE, handle dates, change data safely and build views. Next steps: window functions (RANK, running totals with OVER), indexes and query plans, and connecting SQL to Python (coming in the Python course).

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.