Answer a distributor's real questions from its sales tables

The situation

A fast-moving goods distributor in Faisalabad supplies shops across Punjab. Its director keeps asking questions the current spreadsheet cannot answer: which towns are growing, which products are slipping, which shops have stopped ordering. The data lives in four tables and nobody has written a query against them.

Build the queries that answer the director's real questions, prove each number, and leave queries the next analyst can read.

What you'll be able to show

• Write SELECT and WHERE knowing the order the database runs clauses
• Join tables without silently losing or multiplying rows
• Answer real questions with GROUP BY and HAVING and check the totals
• Structure a hard query as readable CTE steps and format it to last

What you need

• SQLite (DB Browser for SQLite), MySQL or PostgreSQL; any one is fine
• Four tables you create: shops, products, orders, order_lines, with invented but realistic data

Tasks

1. 1Build the data

   Create shops (id, name, town), products (id, name, category, price), orders (id, shop_id, order_date) and order_lines (order_id, product_id, quantity). Insert at least 50 orders across three months and six towns.
   A correct result: Row counts for each table and a note of the date range and towns covered.

2. 2The right clause

   Write one query that returns the ten most recent orders from Gujranwala, newest first. Then say which clause each condition belonged in (WHERE vs HAVING) and why the LIMIT runs last.
   A correct result: The query, its result, and two sentences on clause order.

3. 3Join without losing rows

   List every shop with its number of orders, including shops that have ordered nothing. Record the row count with an INNER JOIN and with a LEFT JOIN, and explain the difference.
   A correct result: Both counts, the correct query (LEFT), and the names of the shops the INNER dropped.

4. 4Answer the director

   Answer three real questions with GROUP BY: revenue by town this quarter, the five products by quantity sold, and shops whose total orders are below a threshold (HAVING). Check one total against a plain SUM.
   A correct result: Three result sets and the reconciliation of one total.

5. 5Break it into steps

   Find each town's best-selling product. Write it as named CTEs (for example town_product_qty, ranked) rather than nested subqueries.
   A correct result: A CTE query a colleague can read top to bottom, with no step named t1.

6. 6Make it last

   Format the final three queries: one clause per line, uppercase keywords, indented joins. Replace any hardcoded date with a clear parameter or a comment, and add one comment explaining a business rule.
   A correct result: The formatted queries with the date handled and one business-rule comment.

What to hand in

One .sql file with the table creation, the data, every query in order and comments, plus a one-page answer to the director in plain words with the numbers.

How it is marked