Your first SQL query

Before you start

• Level: 1 (beginner)
• Time: about 2 hours
• Builds on: 03 Tables, rows and grain (rows, columns, keys) and 01 Run your first lesson (running a command in a terminal)

The problem

It is Monday 26 October 2026. Neha, who leads business sales at Tiffinbell (a fictional food delivery app), thinks offices are ordering big group lunches through the normal app. If so, she wants to call them and offer a proper office lunch plan. She asks for the ten biggest delivered orders of last week, Monday 19 to Sunday 25 October, with the city of each, by lunchtime. The data is one table of 284 orders over two weeks. You will write your first SQL queries to answer her, and check them with a test.

In plain words

Think of a shop's order book with one line per order. To answer Neha by hand you would: open the book (FROM), cross out the lines you do not want, such as cancelled orders and other weeks (WHERE), copy only the columns she asked for (SELECT), sort what is left from biggest to smallest (ORDER BY), and keep the top ten (LIMIT). A SQL query is that list of instructions, written in a fixed order.

Some words:

• SQL (often said "sequel"): Structured Query Language, the language for asking questions of tables in a database.
• Database: a program that stores tables and answers SQL. This course uses SQLite, a small database that comes with Python, so there is nothing to install.
• Query: one SQL question. It returns a table of results.
• Clause: one part of a query, such as WHERE ....
• Condition: a test that is true or false for each row, such as amount >= 1000.

What you will build from scratch

• A first look at a table with SELECT *, ORDER BY and LIMIT.
• The top ten delivered orders of one week, with a tie-breaker.
• A filter that combines AND and OR correctly.
• The habit of running any query against a lesson's CSV files with tools/query.py.

The data in data/orders.csv is synthetic, built by make_data.py with a fixed random seed. Amounts are in rupees.

The idea

The shape of a query

SELECT order_id, city, amount        -- which columns to show
FROM orders                          -- which table
WHERE status = 'delivered'           -- which rows to keep
ORDER BY amount DESC                 -- how to sort them
LIMIT 10;                            -- how many to show

Text after -- is a comment: a note for people that the database ignores. Text values go in single quotes, 'delivered'. Numbers do not. The ; ends the query.

The order the database works in

You write SELECT first, but the database does it third. This logical order explains many errors:

flowchart LR
    F["1 FROM<br/>open the table"] --> W["2 WHERE<br/>keep rows that pass"]
    W --> S["3 SELECT<br/>pick the columns"]
    S --> O["4 ORDER BY<br/>sort"]
    O --> L["5 LIMIT<br/>keep the first n"]

So LIMIT 10 keeps the first ten rows after sorting. Without ORDER BY, "the first ten" means whatever order the database happens to read, which is not "the biggest".

Comparisons, AND, OR

AND is worked out before OR, the way multiplication comes before addition in 2 + 3 x 4. So

city = 'Pune' OR city = 'Nagpur' AND amount >= 1000

means "every Pune order, plus Nagpur orders of 1000 or more". If you want both cities to need 1000 or more, add brackets: (city = 'Pune' OR city = 'Nagpur') AND amount >= 1000.

Dates here are text in the form YYYY-MM-DD, and that form sorts in date order, so order_date >= '2026-10-19' AND order_date <= '2026-10-25' selects the week.

Walk through

How to run any SQL on a lesson's data

The course has one helper for this, tools/query.py. From the top folder of the course (the one with README.md and tools/ in it), type:

python3 tools/query.py <lesson-folder> "<SQL>"

It loads every CSV in the lesson's data/ folder into a temporary SQLite database, one table per file (data/orders.csv becomes the table orders), runs your SQL and prints the result as a table with a row count. Empty cells become NULL (lesson 05 explains NULL). Numbers are stored as numbers, so 2400.00 in the file prints as 2400.0. Nothing is saved and the CSV files are never changed. With no SQL it lists the tables and their columns:

python3 tools/query.py phases/00-start-here/04-your-first-sql-query

orders (284 rows): order_id, order_date, city, status, amount

Put the SQL in double quotes and the text values inside it in single quotes. That works in macOS, Linux and Windows PowerShell terminals. Every query below was run this way.

Step 1. Look before you filter

SELECT * FROM orders ORDER BY order_id LIMIT 5

order_id | order_date | city   | status    | amount
---------+------------+--------+-----------+-------
   80001 | 2026-10-12 | Nashik | delivered | 1200.0
   80002 | 2026-10-12 | Nagpur | delivered | 3600.0
   80003 | 2026-10-12 | Nashik | delivered | 461.85
   80004 | 2026-10-12 | Pune   | delivered | 801.51
   80005 | 2026-10-12 | Pune   | delivered | 854.77
(5 rows)

* means every column. Now you know the columns, what a date looks like, and that status holds words such as delivered.

Step 2. Keep last week's delivered orders, biggest first

SELECT order_id, order_date, city, amount
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2026-10-19'
  AND order_date <= '2026-10-25'
ORDER BY amount DESC
LIMIT 12

order_id | order_date | city   | amount
---------+------------+--------+-------
   80246 | 2026-10-24 | Nagpur | 3600.0
   80219 | 2026-10-22 | Pune   | 3000.0
   80162 | 2026-10-19 | Nashik | 2400.0
   80232 | 2026-10-23 | Pune   | 2400.0
   80215 | 2026-10-22 | Nagpur | 1800.0
   80229 | 2026-10-23 | Mumbai | 1800.0
   80264 | 2026-10-25 | Nagpur | 1800.0
   80241 | 2026-10-24 | Pune   | 1500.0
   80167 | 2026-10-20 | Nashik | 1200.0
   80174 | 2026-10-20 | Pune   | 1200.0
   80272 | 2026-10-25 | Pune   | 1200.0
   80253 | 2026-10-24 | Nagpur | 948.62
(12 rows)

• WHERE has three conditions joined by AND, so a row must pass all three.
• DESC means descending, biggest first. ASC (the default) is smallest first.
• LIMIT 12 shows two more than Neha asked for, on purpose, so you can see what is just below the line.

Places 9, 10 and 11 all have 1200.0. LIMIT 10 must cut that tie somewhere, and with ORDER BY amount alone the database is free to cut it differently from one run, or one database, to the next.

Step 3. Break the tie on purpose

SELECT order_id, order_date, city, amount
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2026-10-19'
  AND order_date <= '2026-10-25'
ORDER BY amount DESC, order_id
LIMIT 10

order_id | order_date | city   | amount
---------+------------+--------+-------
   80246 | 2026-10-24 | Nagpur | 3600.0
   80219 | 2026-10-22 | Pune   | 3000.0
   80162 | 2026-10-19 | Nashik | 2400.0
   80232 | 2026-10-23 | Pune   | 2400.0
   80215 | 2026-10-22 | Nagpur | 1800.0
   80229 | 2026-10-23 | Mumbai | 1800.0
   80264 | 2026-10-25 | Nagpur | 1800.0
   80241 | 2026-10-24 | Pune   | 1500.0
   80167 | 2026-10-20 | Nashik | 1200.0
   80174 | 2026-10-20 | Pune   | 1200.0
(10 rows)

ORDER BY amount DESC, order_id sorts by amount, and among equal amounts by order id (smallest first). Now the answer is the same every time. Order 80272 (also 1200.0) is the one left out, and Neha should be told about it.

Step 4. Two cities, done right

Neha asks a follow-up: which of last week's delivered orders of 1000 or more came from Pune or Nagpur?

SELECT order_id, city, amount
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2026-10-19'
  AND order_date <= '2026-10-25'
  AND amount >= 1000
  AND city IN ('Pune', 'Nagpur')
ORDER BY order_id

order_id | city   | amount
---------+--------+-------
   80174 | Pune   | 1200.0
   80215 | Nagpur | 1800.0
   80219 | Pune   | 3000.0
   80232 | Pune   | 2400.0
   80241 | Pune   | 1500.0
   80246 | Nagpur | 3600.0
   80264 | Nagpur | 1800.0
   80272 | Pune   | 1200.0
(8 rows)

Common mistakes

OR without brackets. Write the two cities with OR and no brackets, and count the rows with COUNT(*) (it counts the rows that pass the WHERE; lesson 05 says more):

SELECT COUNT(*) AS orders
FROM orders
WHERE city = 'Pune' OR city = 'Nagpur'
  AND amount >= 1000
  AND status = 'delivered'
  AND order_date >= '2026-10-19'
  AND order_date <= '2026-10-25'

orders
------
    76
(1 row)

76 orders instead of 8. Because AND binds first, this reads "every Pune order of any size, any status, any week, plus big delivered Nagpur orders of last week". The query runs without any error. Only the number tells you.

Forgetting a filter. Drop status = 'delivered' and a cancelled order slips into the top five:

SELECT order_id, city, status, amount
FROM orders
WHERE order_date >= '2026-10-19' AND order_date <= '2026-10-25'
ORDER BY amount DESC, order_id
LIMIT 5

order_id | city   | status    | amount
---------+--------+-----------+-------
   80246 | Nagpur | delivered | 3600.0
   80219 | Pune   | delivered | 3000.0
   80162 | Nashik | delivered | 2400.0
   80232 | Pune   | delivered | 2400.0
   80189 | Nagpur | cancelled | 1800.0
(5 rows)

Neha would call an office about an order it cancelled.

Numbers stored as text. Some tools load every CSV column as text. Text sorts letter by letter, so "9" beats "3" and 948.62 ranks above 3600. You can see the effect by turning the amount into text on purpose with CAST(amount AS TEXT):

SELECT order_id, amount
FROM orders
WHERE status = 'delivered' AND order_date >= '2026-10-19' AND order_date <= '2026-10-25'
ORDER BY CAST(amount AS TEXT) DESC
LIMIT 3

order_id | amount
---------+-------
   80203 |  96.27
   80253 | 948.62
   80169 | 941.28
(3 rows)

A Rs 96.27 order comes first, because the text "96.27" starts with a 9. None of these is in the real top ten. If a "top" list looks odd, check the column's type. tools/query.py and the tests store numbers as numbers, so this does not happen in the course.

LIMIT without ORDER BY. LIMIT 10 on its own returns ten rows in whatever order the table is stored, not the ten biggest. Always sort before you limit.

SQLite and other databases. The SQL in this lesson works almost everywhere. Two differences to know: SQL Server writes SELECT TOP 10 ... instead of LIMIT 10, and Oracle writes FETCH FIRST 10 ROWS ONLY. Most other databases have a real date type: PostgreSQL, MySQL and Oracle accept a date written DATE '2026-10-19', while SQL Server takes the plain text '2026-10-19'. SQLite has no date type and keeps dates as text, which works because of the YYYY-MM-DD form.

Your turn

Open exercise.sql. Write the three queries under their -- query: markers: first_look, top_ten_last_week and big_orders_pune_nagpur. Try each with tools/query.py first, then run, from this folder:

python3 test_lesson.py

The test runs your SQL on the lesson data, on a second dataset with different orders, and on small hand-made tables: an empty table, the first and last day of the week, a cancelled order, and eleven orders with the same amount.

Stretch: write a query for the five smallest delivered orders of last week. Do any have an amount of 0?

Check yourself

1. In which order does the database run SELECT, FROM, WHERE, ORDER BY and LIMIT?
2. What does WHERE a OR b AND c mean, and how do you write "(a or b) and c"?
3. Why add order_id to ORDER BY amount DESC when you only care about amount?
4. Your "top 10 by revenue" puts 950 above 3,600. What is the likely cause?

What to tell the stakeholder

"Last week's ten biggest delivered orders range from Rs 1,200 to Rs 3,600: four from Pune, three from Nagpur, two from Nashik and one from Mumbai. One more Pune order, 80272, also came to Rs 1,200 and ties for tenth place. One Nagpur order of Rs 1,800 was cancelled and is not in the list."

In real job postings

SQL is the tool analyst postings name most often. In the Real Analyst Jobs data drop of 25 October 2026, 1,477 of the 2,056 US postings that list a tool ask for SQL (71.8%), and 634 of 900 in India (70.4%). Browse them at SQL data analyst jobs.

Next

05 NULLs, CASE and COALESCE