Skip to content

Beginner project · Small

Corner shop sales report

You write one SQL script that creates a tiny shop database, fills it with rows you invent, and then answers a fixed list of business questions about it. Every query has to be right against data you can add up on paper, which is the whole trick: you know the answers before you write the SQL. By the end you have done a full pass over SELECT, WHERE, GROUP BY, ORDER BY and a join that has to include rows with nothing on the other side.

Languages
SQL
Size
Small: one sitting for most learners
Where to build it
Runs as-is in the SQL playground, which is real SQLite starting from an empty database on every run, so keep the CREATE TABLE and INSERT statements at the top of the same script. Open the SQL playground →

What you will practise

  • designing two related tables with a foreign key
  • GROUP BY with aggregates, and the difference from WHERE
  • an outer join to find rows with no match
  • sorting and limiting to answer top-N questions
  • money as whole cents, and integer division traps
  • checking a query against an answer you worked out by hand

Requirements

The project is done when every one of these is true.

  • The script is self-contained: every CREATE TABLE and INSERT comes before the first query, because each run starts from a brand-new empty database and nothing survives between runs.
  • There are two tables: products, with an id, a name, a category and a price in whole cents, and sales, with an id, a product id, a date and a quantity.
  • The products table has at least six rows across at least three categories, and at least one product that has no sales rows at all.
  • The sales table has at least ten rows spread over at least three different dates, including two sales of the same product on the same date.
  • The script answers, as separate labelled queries: total revenue; revenue per category, highest first; products that have never sold; the single best date by revenue; the average quantity per sale; and the top three products by revenue.
  • Revenue is always computed as price times quantity, never as price alone, and the never-sold query uses a LEFT JOIN rather than a NOT IN list.
  • Every query has a comment above it stating the question in plain English and the answer you expect from your own data.
  • Column names in the output are readable, using AS where the raw expression would be ugly.

Milestones

A sensible order to build it in, so something works at every step.

  1. Create the two tables and nothing else

    Write both CREATE TABLE statements with sensible types and a foreign key, run the script, and confirm it completes without error before any data exists.

  2. Insert data you can add up by hand

    Keep the numbers small and round so you can compute the right answers on paper, and deliberately leave one product with no sales.

  3. Write the single-number queries

    Answer total revenue and average quantity per sale first, because they have one row of output and are the easiest to verify.

  4. Group by category and by date

    Add the revenue-per-category and best-date queries, ordering by the aggregate and limiting where the question asks for one row.

  5. Find the products that never sold

    Join products to sales keeping every product, then keep only the rows where the sales side came back empty.

  6. Add the top three and the expected-answer comments

    Finish with the top-three query, then go back over every query and write the answer you expect above it, so a future change that breaks one is obvious.

Hints

Open one only when you are stuck. Each gives a little more away.

Show hint 1

The database is empty at the start of every run, so a query that worked yesterday will fail today unless the CREATE and INSERT statements are in the same script above it. Treat the whole file as one program.

Show hint 2

WHERE filters rows before grouping and HAVING filters groups after. If your filter mentions an aggregate, it belongs in HAVING.

Show hint 3

A plain join can only return products that appear in sales, so it can never show you the ones that never sold. Keep every row from the left-hand table and look for the empty right-hand side.

Show hint 4

Dividing one integer by another truncates in SQLite, so a sum of quantities divided by a count of sales silently loses the fraction. AVG() returns a floating-point value, which is usually what the question means.

Show hint 5

Prices in cents keep every total exact. If you want currency in the output, divide by 100.0 only in the final SELECT, never in the middle of a sum.

Show hint 6

Each statement that returns rows prints as its own table, so you can leave a scratch SELECT * in place while you work and delete it at the end.

How to test it

Run these checks yourself, or turn them into automated tests once you know how.

  • Insert only two products, one at 250 cents and one at 400 cents, and three sales: quantity 2 of the first, quantity 1 of the first, quantity 3 of the second. Total revenue must be 1950 cents, because 3 times 250 is 750 and 3 times 400 is 1200.
  • With those same three sales, the average quantity per sale must come out at exactly 2, because 2 plus 1 plus 3 is 6 and 6 divided by 3 is 2. Note that this case cannot tell real division from integer division, because both give 2; the next one can.
  • Change the second product's quantity from 3 to 4 so the quantities are 2, 1 and 4. The average must now come out as a fraction, printed as a long run of threes after 2.3, because AVG() returns a floating-point value. If it prints exactly 2, you have integer division somewhere.
  • Add a third product with no sales rows. The never-sold query must return exactly one row, that product, and the revenue total must not change.
  • Sell the never-sold product once, re-run, and confirm the never-sold query now returns zero rows.
  • Make two sales of different products on the same date and confirm the best-date query adds them together rather than picking one.
  • Add up the revenue-per-category rows by hand and confirm they equal the single total revenue figure exactly.
  • Give two products identical revenue and check what your top-three query does with the tie, then add an ORDER BY tiebreaker so the output is the same on every run.

Stretch goals

  • Add a customers table and answer which customer spent the most, still working in whole cents.
  • Use a common table expression to compute revenue per product once and reuse it for both the category totals and the top three.
  • Add a running total of revenue by date with a window function, and check it against the plain total.
  • Add a returns table with negative quantities and decide, in a comment, how each query should treat them.
  • Write a query that finds categories whose total revenue is above the average category revenue.

Learn what you need

All projects · Roadmaps · Practice problems