SQL basics: SELECT, WHERE, ORDER BY and LIMIT

Almost every SQL round starts with a small question. For example: "Give me the three biggest orders of last month." It looks easy. The interviewer wants to see if you can read a table, pick the right rows, put them in order, and avoid the small traps.

A weak answer takes every column, forgets the sort order, and does not test the answer on a few rows. A strong answer names the columns it needs, says what happens when two rows tie, and checks the result by hand.

At SDE-1 level you should write these queries without help. At SDE-2 level you should also spot the traps in dates, in text and in division before the interviewer points at them.

Think of a clerk who keeps a register. You hand the clerk a note: "Show me the delivered orders over Rs 300, biggest first." The clerk copies the right lines onto a fresh sheet and gives it to you. The register does not change.

SQL is the language for such notes. A query is one note. It asks a table a question, and the answer is a new table. All the queries in this lesson only read data.

This module uses one small made-up food app, called Tiffin Town. Every table is shown in full where it is first used.

Tables, rows and types

You know from lessons 33 and 34 that a table has columns (the facts) and rows (one thing each). A primary key is a column that is different in every row. Here is how Tiffin Town creates its customers table.

CREATE TABLE customers (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  city      TEXT NOT NULL,
  joined_on TEXT NOT NULL
);

Each line gives a column, its type and a rule. INTEGER is a whole number and TEXT is text. NOT NULL means every row must have a value in that column.

SQLite, the database we use, has few types: whole numbers, decimal numbers, text and raw bytes. It has no date type. A date is stored as text such as '2026-03-31', or '2026-03-31 18:00:00' with a time. This works because the year comes first. Text in this form sorts in the same order as the dates.

The orders table has seven columns: id, customer_id, restaurant_id, amount (rupees), status (placed, delivered or cancelled), coupon_off and ordered_at (text). In this lesson we use id, restaurant_id, amount, status and ordered_at.

SELECT and FROM: choose columns

A query is made of clauses, small parts that each start with a keyword. SELECT lists the columns you want. FROM names the table.

SELECT * FROM customers;

id  name    city       joined_on
--  ------  ---------  ----------
 1  Asha    Delhi      2026-01-05
 2  Ravi    Mumbai     2026-01-12
 3  Meena   Pune       2026-02-03
 4  Karan   Delhi      2026-02-14
 5  Sana    Mumbai     2026-02-20
 6  Vikram  Bengaluru  2026-03-02
 7  Priya   Pune       2026-03-09
 8  Dev     Delhi      2026-03-15
 9  Neha    Bengaluru  2026-03-20

The star * means "all columns". Use it to look at a table. Do not use it in real code. A column added later changes your result, the program that reads it may break, and you move data you do not need. Name the columns instead.

The star also shows the other two tables of this lesson. First restaurants, then orders. The table orders has 24 rows, so we show it in two parts.

SELECT * FROM restaurants;

id  name          city       cuisine
--  ------------  ---------  -------------
 1  Spice Route   Delhi      North Indian
 2  Roti Ghar     Delhi      North Indian
 3  Chaat Corner  Delhi      Street food
 4  Bombay Bites  Mumbai     Street food
 5  Thali Tales   Mumbai     Maharashtrian
 6  Dosa Corner   Bengaluru  South Indian
 7  Misal Mane    Pune       Maharashtrian

SELECT * FROM orders WHERE id <= 12;

id  customer_id  restaurant_id  amount  status     coupon_off  ordered_at
--  -----------  -------------  ------  ---------  ----------  -------------------
 1            1              1     440  delivered        NULL  2026-03-02 13:10:00
 2            2              4     200  delivered        NULL  2026-03-03 20:15:00
 3            3              7     360  delivered          50  2026-03-05 19:40:00
 4            1              2     440  delivered        NULL  2026-03-07 21:05:00
 5            4              1     600  cancelled        NULL  2026-03-08 20:30:00
 6            5              5     340  delivered        NULL  2026-03-10 12:45:00
 7            2              4     400  delivered         100  2026-03-12 20:50:00
 8         NULL              3     210  delivered        NULL  2026-03-14 19:20:00
 9            3              6     300  delivered        NULL  2026-03-15 08:55:00
10            1              1     640  delivered          50  2026-03-18 20:10:00
11            4              3     170  delivered        NULL  2026-03-20 13:30:00
12            5              5     250  cancelled        NULL  2026-03-22 19:05:00

SELECT * FROM orders WHERE id > 12;

id  customer_id  restaurant_id  amount  status     coupon_off  ordered_at
--  -----------  -------------  ------  ---------  ----------  -------------------
13            3              7     360  delivered        NULL  2026-03-25 20:40:00
14            5              5     340  delivered        NULL  2026-03-31 18:00:00
15            2              4     240  delivered        NULL  2026-04-02 20:05:00
16            1              1     400  delivered        NULL  2026-04-05 21:20:00
17            6              6     340  delivered        NULL  2026-04-09 09:15:00
18            3              7     330  delivered          50  2026-04-12 19:55:00
19            7              5     250  cancelled        NULL  2026-04-18 13:00:00
20            4              2     600  delivered        NULL  2026-04-21 20:35:00
21            1              2     360  delivered        NULL  2026-05-03 21:00:00
22            6              6     280  delivered        NULL  2026-05-09 20:20:00
23            7              4     260  delivered        NULL  2026-05-14 19:30:00
24            2              4     150  placed           NULL  2026-05-20 13:15:00

A cell that shows NULL has no value. This lesson does not use the columns customer_id and coupon_off.

SELECT name, cuisine FROM restaurants;

name          cuisine
------------  -------------
Spice Route   North Indian
Roti Ghar     North Indian
Chaat Corner  Street food
Bombay Bites  Street food
Thali Tales   Maharashtrian
Dosa Corner   South Indian
Misal Mane    Maharashtrian

After SELECT you can write an expression: a small calculation that gives one value. You can give any column or expression a new name in the result with AS. That name is an alias.

SELECT name AS restaurant, UPPER(city) AS town FROM restaurants;

restaurant    town
------------  ---------
Spice Route   DELHI
Roti Ghar     DELHI
Chaat Corner  DELHI
Bombay Bites  MUMBAI
Thali Tales   MUMBAI
Dosa Corner   BENGALURU
Misal Mane    PUNE

WHERE: keep some rows

WHERE keeps only the rows for which a test is true. The comparisons are =, <> (not equal), <, >, <= and >=. Text goes in single quotes. You combine tests with AND, OR and NOT.

SELECT name, city FROM customers WHERE city = 'Delhi' AND joined_on >= '2026-02-01';

name   city
-----  -----
Karan  Delhi
Dev    Delhi

The trap: AND comes before OR. The database groups AND first, like multiplication before addition. We want Maharashtrian restaurants in Mumbai or Pune. Look at this query.

SELECT name, city, cuisine FROM restaurants
WHERE city = 'Mumbai' OR city = 'Pune' AND cuisine = 'Maharashtrian';

name          city    cuisine
------------  ------  -------------
Bombay Bites  Mumbai  Street food
Thali Tales   Mumbai  Maharashtrian
Misal Mane    Pune    Maharashtrian

It returned Bombay Bites, a street food place. The database read it as: Mumbai, or (Pune and Maharashtrian). Brackets fix it.

SELECT name, city, cuisine FROM restaurants
WHERE (city = 'Mumbai' OR city = 'Pune') AND cuisine = 'Maharashtrian';

name         city    cuisine
-----------  ------  -------------
Thali Tales  Mumbai  Maharashtrian
Misal Mane   Pune    Maharashtrian

More tests for WHERE:

• IN (a, b) is a short way to write several OR tests on one column.
• BETWEEN a AND b includes both ends: a and b are in.
• LIKE matches a pattern. % stands for any number of characters, including none. _ stands for exactly one character.

SELECT id, amount FROM orders WHERE amount BETWEEN 300 AND 330 AND status IN ('delivered', 'placed');

id  amount
--  ------
 9     300
18     330

Orders with amounts 300 and 330 both appear. Now a pattern test. Names with a as the second letter:

SELECT name FROM customers WHERE name LIKE '_a%';

name
-----
Ravi
Karan
Sana

Text dates and the end-of-day trap. A date with a time is longer text than a date without one. Text compares letter by letter, and a shorter text is smaller than a longer text that starts the same way. So '2026-03-31 18:00:00' is bigger than '2026-03-31'. This query looks for orders on 30 and 31 March.

SELECT id, ordered_at FROM orders WHERE ordered_at BETWEEN '2026-03-30' AND '2026-03-31';

id  ordered_at
--  ----------
(no rows)

No rows, but order 14 was placed at 6 pm on 31 March. The safe form is: from the first moment, up to but not including the next day.

SELECT id, ordered_at FROM orders WHERE ordered_at >= '2026-03-30' AND ordered_at < '2026-04-01';

id  ordered_at
--  -------------------
14  2026-03-31 18:00:00

Case. In SQLite, = on text is case-sensitive. LIKE ignores case for plain English letters. You can also add COLLATE NOCASE after a comparison to ignore case. A collation is the rule for comparing text.

SELECT name FROM customers WHERE city = 'delhi';

name
----
(no rows)

SELECT name FROM customers WHERE city = 'delhi' COLLATE NOCASE;

name
-----
Asha
Karan
Dev

DISTINCT: remove repeated rows

DISTINCT removes rows that are exactly the same as another row in the result. It looks at the whole row, not one column.

SELECT DISTINCT city FROM customers;

city
---------
Delhi
Mumbai
Pune
Bengaluru

SELECT DISTINCT city, cuisine FROM restaurants;

city       cuisine
---------  -------------
Delhi      North Indian
Delhi      Street food
Mumbai     Street food
Mumbai     Maharashtrian
Bengaluru  South Indian
Pune       Maharashtrian

The second query keeps Delhi twice, because the pairs (Delhi, North Indian) and (Delhi, Street food) are different rows.

ORDER BY and LIMIT

A database does not promise any order for the rows of a result. Some results above look sorted by id, but that is only what the database just did. To get an order, say it with ORDER BY. The default is smallest first (ASC). Write DESC for biggest first. You can sort by several columns. The second column breaks ties in the first.

SELECT name, city FROM customers ORDER BY city, name DESC;

name    city
------  ---------
Vikram  Bengaluru
Neha    Bengaluru
Karan   Delhi
Dev     Delhi
Asha    Delhi
Sana    Mumbai
Ravi    Mumbai
Priya   Pune
Meena   Pune

You can sort by an alias, because ORDER BY runs after SELECT has made it. Here the alias letters counts the letters of a name.

SELECT name, LENGTH(name) AS letters FROM customers ORDER BY letters DESC, name LIMIT 3;

name    letters
------  -------
Vikram        6
Karan         5
Meena         5

LIMIT n keeps only the first n rows. OFFSET m skips m rows first. Together they give pages: with 3 rows per page, page 2 is LIMIT 3 OFFSET 3. Always write ORDER BY with them. Without it, "the first 3 rows" has no meaning. Add a tie-break column, such as id, so that a tie always gives the same rows.

SELECT id, name FROM customers ORDER BY name LIMIT 3 OFFSET 3;

id  name
--  -----
 3  Meena
 9  Neha
 7  Priya

Numbers and text functions

The signs are +, -, *, / and % (the remainder). Warning: when both numbers are whole, / drops the decimal part. Make one number decimal to keep it, for example 100.0 * a / b. ROUND(x, 2) rounds to 2 decimals, ABS removes the minus sign, and CAST(x AS INTEGER) changes the type.

SELECT 7 / 2 AS whole, 7 / 2.0 AS decimal, 7 % 2 AS remainder, ROUND(10 / 3.0, 2) AS rounded, CAST('42' AS INTEGER) + 1 AS cast_plus_one;

whole  decimal  remainder  rounded  cast_plus_one
-----  -------  ---------  -------  -------------
    3      3.5          1     3.33             43

Functions for text:

• || joins text. LOWER and UPPER change case. TRIM removes spaces at both ends. LENGTH counts characters.
• SUBSTR(text, start, count) cuts a piece. The first character is number 1, not 0.
• INSTR(text, small) gives the place where small starts, or 0 if it is not there.
• REPLACE(text, old, new) swaps every old for new.

SELECT name || ' (' || city || ')' AS label, LENGTH(name) AS letters, SUBSTR(name, 1, 3) AS first3, INSTR(name, 'a') AS first_a
FROM customers WHERE id <= 3;

label          letters  first3  first_a
-------------  -------  ------  -------
Asha (Delhi)         4  Ash           4
Ravi (Mumbai)        4  Rav           2
Meena (Pune)         5  Mee           5

How the database reads a query

You write SELECT first, but the database works in this order:

1. FROM: find the table.
2. WHERE: keep the rows that pass.
3. SELECT: make the columns.
4. ORDER BY: sort.
5. LIMIT: cut.

This is why ORDER BY can use an alias and WHERE should not. The standard rule says WHERE runs before SELECT makes the alias, so WHERE cannot use it. SQLite lets this pass, as the next query shows. Postgres and MySQL give an error (column "fee" does not exist in Postgres, Unknown column 'fee' in 'where clause' in MySQL). Never rely on it. Write the full expression in WHERE.

SELECT id, amount * 5 / 100 AS fee FROM orders WHERE fee > 25;

id  fee
--  ---
 5   30
10   32
20   30

Build in small steps

Do not write the whole query at once. Start with SELECT * FROM table, run it, and add one thing at a time. When a query fails, read the message. The words say what is wrong.

SELECT name, citty FROM customers;

Error: no such column: citty

SELECT name FORM customers;

Error: near "customers": syntax error

The first message names a column that does not exist. The second points to the word where the grammar broke. Check the spelling first.

Style. Write keywords in capitals. Put each clause on its own line when the query is long. End with a semicolon. Readable queries are easier to check, and the interviewer reads them too.

How Tradeoff runs your query. The Tiffin Town tables are not loaded in the runner. Every practice problem brings its own tables, and the lessons show the Tiffin Town rows so that you can read the results. To try these queries yourself, type the rows into any SQLite tool. The runner accepts one query that reads data. A query that changes data is refused. It shows at most 100 rows and stops after 5 seconds. Run uses the example rows you see. Submit also tests your query on hidden rows with the same columns. So a query must be right for other data too. A query that only copies the example answer fails.

In Postgres and MySQL

• Text compare: Postgres = and LIKE are case-sensitive (use ILIKE to ignore case). MySQL ignores case for both by default.
• Division: in Postgres, whole numbers divide like SQLite (7 / 2 is 3). In MySQL, / gives 3.5 and DIV gives 3.
• Joining text: || works in Postgres. In MySQL, use CONCAT(a, b), because || means OR there by default.
• Dates are a real type in both, but BETWEEN on a timestamp has the same end-of-day trap.

The question: "Delivered orders over Rs 300 in March. Biggest first. Only the top 3."

Here are the March orders. We build the answer in four runs.

Run 1: March only. We use a start and an end that is not included.

SELECT id, restaurant_id, amount, status, ordered_at
FROM orders
WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01';

id  restaurant_id  amount  status     ordered_at
--  -------------  ------  ---------  -------------------
 1              1     440  delivered  2026-03-02 13:10:00
 2              4     200  delivered  2026-03-03 20:15:00
 3              7     360  delivered  2026-03-05 19:40:00
 4              2     440  delivered  2026-03-07 21:05:00
 5              1     600  cancelled  2026-03-08 20:30:00
 6              5     340  delivered  2026-03-10 12:45:00
 7              4     400  delivered  2026-03-12 20:50:00
 8              3     210  delivered  2026-03-14 19:20:00
 9              6     300  delivered  2026-03-15 08:55:00
10              1     640  delivered  2026-03-18 20:10:00
11              3     170  delivered  2026-03-20 13:30:00
12              5     250  cancelled  2026-03-22 19:05:00
13              7     360  delivered  2026-03-25 20:40:00
14              5     340  delivered  2026-03-31 18:00:00

The result has 14 orders. The last one, order 14, is at 6 pm on 31 March. This is the answer to the predict box. With BETWEEN '2026-03-01' AND '2026-03-31' it would be missing, as we saw above. Text '2026-03-31 18:00:00' is bigger than '2026-03-31'.

Run 2: add the other two rules.

SELECT id, restaurant_id, amount, ordered_at
FROM orders
WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01'
  AND status = 'delivered'
  AND amount > 300;

id  restaurant_id  amount  ordered_at
--  -------------  ------  -------------------
 1              1     440  2026-03-02 13:10:00
 3              7     360  2026-03-05 19:40:00
 4              2     440  2026-03-07 21:05:00
 6              5     340  2026-03-10 12:45:00
 7              4     400  2026-03-12 20:50:00
10              1     640  2026-03-18 20:10:00
13              7     360  2026-03-25 20:40:00
14              5     340  2026-03-31 18:00:00

Now 8 rows are left. Amount 300 of order 9 is not over 300, so it is out. The cancelled orders are out.

Run 3: sort. The largest amount comes first. Two orders have amount 440, so we add id to decide their place.

SELECT id, restaurant_id, amount, ordered_at
FROM orders
WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01'
  AND status = 'delivered'
  AND amount > 300
ORDER BY amount DESC, id;

id  restaurant_id  amount  ordered_at
--  -------------  ------  -------------------
10              1     640  2026-03-18 20:10:00
 1              1     440  2026-03-02 13:10:00
 4              2     440  2026-03-07 21:05:00
 7              4     400  2026-03-12 20:50:00
 3              7     360  2026-03-05 19:40:00
13              7     360  2026-03-25 20:40:00
 6              5     340  2026-03-10 12:45:00
14              5     340  2026-03-31 18:00:00

Run 4: cut.

SELECT id, restaurant_id, amount, ordered_at
FROM orders
WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01'
  AND status = 'delivered'
  AND amount > 300
ORDER BY amount DESC, id
LIMIT 3;

id  restaurant_id  amount  ordered_at
--  -------------  ------  -------------------
10              1     640  2026-03-18 20:10:00
 1              1     440  2026-03-02 13:10:00
 4              2     440  2026-03-07 21:05:00

The answer is orders 10, 1 and 4. If we had left out id, orders 1 and 4 could change places, because the database is free to order a tie as it likes.

Check by hand. The delivered March orders over Rs 300 have amounts 440, 360, 440, 340, 400, 640, 360 and 340. The top three are 640, 440 and 440. This agrees with the result.

• It solves: a clear question on one table, with only the rows and columns you need.
• It costs: the traps. Each one gives a wrong answer without any error message. The database only does what you wrote.
• I would change it when: the query becomes too long to check by hand. Then I build it in smaller steps and run each step.
• Check any query on a few rows you can count by hand, and say your tie rule out loud.

"A result without ORDER BY has the order I see." The order is not promised. It can change when the data or the plan changes. Say the order you want with ORDER BY.

"BETWEEN '2026-03-01' AND '2026-03-31' covers all of March." It stops at the first moment of 31 March. Any order with a time on that day is missing, as order 14 shows. Use >= first day and < first day of the next month.

"SELECT * is the safe choice." It moves extra data. Its result changes when someone adds a column. Name the columns you need.

1. Which runs first: WHERE or SELECT? Which runs last: ORDER BY or LIMIT?
2. A page shows 20 orders. You want page 6, sorted by id. Which LIMIT and OFFSET do you write?
3. A teammate wants cancelled or placed orders above Rs 200 and writes this: WHERE status = 'cancelled' OR status = 'placed' AND amount > 200. What is wrong?