Asking a database questions

The one idea#

SQL is not programming. It is a way of writing down a question about rows.

Every query is the same sentence with the blanks filled in:

Show me [these columns] from [this table], where [this is true], grouped by [this], sorted by [this].

You do not tell the database how to find the rows. You describe the result you want, and it works out how. That is why it is learnable in an afternoon while most languages are not.

Set yourself up first#

You cannot learn this by reading, and you do not need permission or a server to practise.

DB Browser for SQLite is a free, open-source desktop program that opens a SQLite database file and runs queries locally. SQLite keeps a whole database in one ordinary file, so there is nothing to break. Download it from sqlitebrowser.org, create a database, and use Import to load a CSV as a table. Ten minutes and you have something real to query — ideally your own data, which teaches faster than a sample database about a fictional record shop.

The questions, and the SQL that answers them#

Two tables throughout: orders (with order_id, customer_id, order_date, amount, status) and customers (with customer_id, name, city, signup_date).

"Show me the recent orders"#

SELECT order_id, order_date, amount
FROM orders
ORDER BY order_date DESC
LIMIT 20;

SELECT names the columns. FROM names the table. ORDER BY ... DESC sorts newest first. LIMIT 20 stops at twenty rows — always put a LIMIT on your first look at an unfamiliar table, because you do not yet know if it holds two million rows.

"Only the large completed orders from this year"#

SELECT order_id, order_date, amount
FROM orders
WHERE amount > 50000
  AND status = 'completed'
  AND order_date >= '2026-01-01'
ORDER BY amount DESC;

WHERE filters rows. Text goes in single quotes, numbers do not. Note = for equality, not ==. Useful variants:

WHERE city IN ('Mumbai', 'Pune', 'Nashik')
WHERE name LIKE 'A%'
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31'
WHERE status <> 'cancelled'

% in LIKE means "any characters". <> means not equal.

"How much did we sell, by city?"#

This is the PivotTable question, written down.

SELECT city, SUM(amount) AS total_revenue, COUNT(*) AS order_count
FROM orders
GROUP BY city
ORDER BY total_revenue DESC;

GROUP BY city collapses all rows for a city into one, and the aggregate functions — SUM, COUNT, AVG, MIN, MAX — describe each group. AS names the output column so the result is readable.

The rule people trip on: every column in SELECT must either be in the GROUP BY or be inside an aggregate function. Asking for order_id alongside a SUM makes no sense — there are 400 order IDs in that group and only room for one value.

"Only the cities that did more than 10 lakh"#

SELECT city, SUM(amount) AS total_revenue
FROM orders
GROUP BY city
HAVING SUM(amount) > 1000000
ORDER BY total_revenue DESC;

WHERE filters rows before grouping. HAVING filters groups after. If your condition is about an individual row (a date, a status), it goes in WHERE. If it is about the aggregate (a total, a count), it goes in HAVING. Putting it in the wrong one is either an error or a quietly different answer.

"I need the customer's city, but it lives in the other table"#

This is the spreadsheet lookup, done properly.

SELECT c.name, c.city, o.order_date, o.amount
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.amount > 50000;

JOIN ... ON says how the two tables connect: rows match where the customer IDs are equal. The aliases o and c save typing and make it clear which table each column came from.

Now combine it with grouping — revenue by city, across both tables:

SELECT c.city, SUM(o.amount) AS revenue, COUNT(*) AS orders
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY c.city
ORDER BY revenue DESC;

That query is the entire two-file join from the spreadsheet lesson, in six lines, and it will run on a million rows.

"Which customers have never ordered?"#

A plain JOIN keeps only rows that matched. A LEFT JOIN keeps every row from the left table and fills in NULL where nothing matched — so the non-matches become findable.

SELECT c.name, c.city
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This is one of the most useful queries in business: find the things that are missing. Dormant customers, products never sold, invoices never paid.

"Group them into bands"#

SELECT
  CASE
    WHEN amount >= 100000 THEN 'Large'
    WHEN amount >= 25000  THEN 'Medium'
    ELSE 'Small'
  END AS order_size,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
GROUP BY order_size
ORDER BY revenue DESC;

CASE is IF with more branches. Conditions are checked top to bottom and the first match wins, which is why the order matters — put the largest threshold first or everything falls into the first bucket.

What NULL does to your results#

NULL is not zero and not an empty string. It means unknown, and unknown behaves strangely on purpose.

The dangerous pair is the first two. WHERE status <> 'cancelled' will silently drop every row where status is NULL, so your "all non-cancelled orders" quietly excludes rows that were never given a status. If you meant to include them:

WHERE status <> 'cancelled' OR status IS NULL

And that AVG row is the one that changes conclusions. If half your discount column is NULL because nobody recorded it, AVG(discount) reports the average of the recorded half and presents it as the average discount. Replace an unknown with a stated assumption when you mean to:

SELECT AVG(COALESCE(discount, 0)) AS avg_discount FROM orders;

COALESCE returns the first non-null argument. Use it deliberately — the choice between "unknown" and "zero" is the same judgement as the blanks in the cleaning lesson, and it belongs in your write-up.

SELECT c.name, SUM(o.amount) AS revenue
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY revenue DESC
LIMIT 5;

SELECT c.city, AVG(o.amount) AS avg_order
FROM customers AS c
JOIN orders AS o ON c.city = o.city
GROUP BY c.city;

Act as an experienced practitioner who is good at teaching. I have just learned writing SQL queries with JOIN, GROUP BY and NULL handling. Assume I am intelligent but relatively new to this — treat me as intermediate level.

Work through this in order, and wait for my reply at each step:

1. Ask me 5 questions that test whether I actually understood writing SQL queries with JOIN, GROUP BY and NULL handling. Do not reveal the answers yet.
2. After I answer, tell me which parts I got right, which I got wrong, and which I only half-understand. Explain only what I misunderstood — do not re-teach what I already know.
3. Give me one practical challenge based on something I could genuinely encounter at work or in daily life. Do not solve it for me.
4. Evaluate my solution the way an experienced person would judge it, including what a professional would have done differently.
5. Tell me what to learn next, and why that comes next.
6. Give me trustworthy sources for deeper study — prefer official documentation, primary research or standards bodies over blogs and videos.

Rules for you: no buzzwords. No motivational filler. Say "I'm not certain" when you are not certain, and tell me which parts of your answer I should verify myself. Clearly separate facts from your recommendations and your opinions.

I want to become independently capable at querying a database with SQL to answer business questions — not permanently dependent on AI, tutorials or step-by-step guides.

Design a progression for me with five stages: Beginner, Guided practice, Independent practice, Real-world application, Professional level.

For each stage tell me:
- what I must know
- what I must be able to do without help
- the mistakes people make at this stage
- one practical challenge
- one real project that would prove I reached this stage
- one way I can test myself honestly

Then tell me the signals that I am ready to move to the next stage, and the signals that I have skipped ahead too early.

Keep the theory to the minimum I actually need. Focus on ability I can transfer to situations you and I have not discussed.