Week 01

SELECT, WHERE, ORDER BY & LIMIT

Day 11.5 to 2 hrs

From a statement to a query

A single-table question is usually straightforward until you check the details: should cancelled orders count, do ties need a second sort key, and should a missing city be included?

Start with the required result, then work backwards to the filters and sorting. We will use an order log to practise that. If you already know SELECT and WHERE, skim the first example and spend more time on NULLs, mixed conditions and top-N queries.

The examples use SQLite. In an assessment, check the selected database and the output requirements before submitting; syntax, formatting and judging rules can differ between platforms.

A query you can build on

Use this orders table throughout the lesson:

order_id	customer	city	amount	status	order_date
1001	Rahul	Hyderabad	2499	DELIVERED	2026-07-03
1002	Sneha	Pune	899	SHIPPED	2026-07-05
1003	Arjun	Hyderabad	1500	CANCELLED	2026-07-09
1004	Meera	Delhi	4750	DELIVERED	2026-07-11
1005	Rahul	Hyderabad	350	PENDING	2026-07-14
1006	Kiran	Pune	1500	SHIPPED	2026-07-18
1007	Divya	NULL	6200	DELIVERED	2026-07-21
1008	Farhan	Delhi	1200	SHIPPED	2026-07-25

Divya's city is NULL, meaning it was not recorded. That is different from an empty string and matters when we filter the city column.

Filter and sort in one query

Find delivered orders worth at least 2000, highest amount first:

SQL

SELECT order_id, customer, amount
FROM orders
WHERE status = 'DELIVERED' AND amount >= 2000
ORDER BY amount DESC;

order_id	customer	amount
1007	Divya	6200
1004	Meera	4750
1001	Rahul	2499

The query applies four decisions:

FROM orders identifies the source table.
WHERE status = 'DELIVERED' AND amount >= 2000 keeps only lines where both conditions hold, because AND means both must be true.
SELECT order_id, customer, amount copies those three columns.
ORDER BY amount DESC prints the biggest amount first.

All delivered orders in this sample happen to exceed 2000. Keep the amount condition anyway: the query must satisfy the statement on other data too.

Divya is included because neither condition checks her city.

Remove repeated rows with DISTINCT

SELECT DISTINCT removes duplicate rows from the result. Hyderabad appears on three orders, but a list of cities should show it once:

SQL

SELECT DISTINCT city
FROM orders
ORDER BY city;

city
NULL
Delhi
Hyderabad
Pune

Two details decide most answers. First, DISTINCT looks at the whole selected row, not one column: SELECT DISTINCT customer, city keeps Rahul, Hyderabad once but would keep Rahul twice if he had ordered from two cities. Second, all the NULLs count as one value, so Divya's missing city shows up as a single empty row, and SQLite sorts it first.

When a statement says "unique", "different" or "without repeats", it is asking for DISTINCT.

Use this logical order to reason about the query:

FROM table
WHERE keeps rows
SELECT picks columns
ORDER BY sorts
LIMIT cuts

WHERE runs before SELECT, so you can filter on status and never print it. A SELECT alias can be used in ORDER BY to name a computed output column.

The Pattern

Before writing a query, settle these five things.

Read the required output block first. Write the columns down in the exact order and with the exact names asked for.
Separate the row filters from the output instructions. Use AND when conditions must hold together, OR for alternatives, and brackets when the two are mixed.
Ask what the row order must be. If the statement names an order, write ORDER BY with a tie break. For a top-N result, make ties deterministic using the stated rule or a suitable unique key.
Only then check whether you need DISTINCT, arithmetic, ROUND, PRINTF or LIMIT.
Run the sample, then check boundary values, ties and missing values against the statement.

A worked example. "The 3 biggest orders that were not cancelled, of 1000 and above."

Two new pieces here. <> means "not equal to", and LIMIT 3 keeps only the first 3 rows after sorting, which works when the question wants exactly N rows. Including all ties needs a different approach.

SQL

SELECT customer, city, amount
FROM orders
WHERE status <> 'CANCELLED' AND amount >= 1000
ORDER BY amount DESC, order_id ASC
LIMIT 3;

Dry run, one line per step:

Step	What it does here	Rows alive
FROM orders	reads the table	8
WHERE status <> 'CANCELLED'	drops order 1003	7
WHERE amount >= 1000	drops 1002 (899) and 1005 (350)	5
GROUP BY	not used today	5
HAVING	not used today	5
SELECT customer, city, amount	keeps 3 of the 6 columns	5
ORDER BY amount DESC, order_id	6200, 4750, 2499, 1500, 1200	5
LIMIT 3	cuts the tail	3

Output:

customer	city	amount
Divya	NULL	6200
Meera	Delhi	4750
Rahul	Hyderabad	2499

Five rows reached the sort, and only the top three were printed.

The second sort key, order_id ASC, is a tie break. If two rows have the same amount, the database is free to print them in any order unless you say what to do next. Nothing is tied in the top three here, but the habit is what saves you on the hidden cases.

Check the output format. A result displayed as pipe-separated text can look like this:

Text

customer|city|amount
Divya||6200
Meera|Delhi|4750
Rahul|Hyderabad|2499

Here NULL is displayed as an empty field, so Divya's line has two pipes together. SELECT * would return six columns instead of the three requested. Leaving out ORDER BY is a separate problem: LIMIT 3 could pick a different set, such as:

Text

customer|city|amount
Rahul|Hyderabad|2499
Meera|Delhi|4750
Kiran|Pune|1500

Those are not the three largest qualifying orders. Sorting must happen before limiting.

Tip
Tip: Check the requested columns, aliases and order before submitting. These are part of the result, not just presentation.

Check what you learned

The statement says "list distinct city values from orders, alphabetically, including NULL". Which query matches?

Query Templates

SQL

-- 1. The workhorse: named columns, ANDed filters, explicit order with a tie break.
SELECT order_id, customer, amount
FROM orders
WHERE status = 'SHIPPED'
  AND amount >= 1000
ORDER BY amount DESC, order_id ASC;

-- 2. Set membership and ranges instead of a long chain of ORs.
--    IN checks if a value is one of a list. BETWEEN checks a range, both ends included.
SELECT order_id, city, order_date
FROM orders
WHERE city IN ('Delhi', 'Pune')
  AND order_date BETWEEN '2026-07-05' AND '2026-07-20'
  AND status IS NOT NULL;

-- 3. A computed column, renamed with AS so the header matches the statement.
SELECT order_id,
       amount,
       PRINTF('%.2f', amount * 0.02) AS coins_earned
FROM orders
WHERE status = 'DELIVERED'
ORDER BY order_id;

-- 4. Top N, then reprinted in a different order. Very common in OAs.
SELECT order_id, customer, amount
FROM (
    SELECT order_id, customer, amount
    FROM orders
    WHERE status <> 'CANCELLED'
    ORDER BY amount DESC, order_id ASC
    LIMIT 3
) AS t
ORDER BY order_id ASC;

Template 1 returns two rows, 1006 Kiran 1500 and 1008 Farhan 1200. Template 3 returns 49.98, 95.00 and 124.00 in the coins column, and AS is what puts coins_earned on the header line the judge reads.

Template 4 returns 1001 Rahul 2499, then 1004 Meera 4750, then 1007 Divya 6200. The query inside the brackets decides who wins, the query outside decides how they are printed. A query written inside another query like this is a subquery, and putting one in the FROM clause is the standard trick when the ranking order and the printing order differ.

Variations

Variation 1 - Pattern match with LIKE

LIKE compares text against a pattern instead of an exact value. % stands for any number of characters, _ for exactly one.

So LIKE 'R%' is names starting with R, LIKE '_iran' is a five letter name ending in iran, and NOT LIKE '%a%' is names with no a anywhere.

SQL

SELECT order_id, customer
FROM orders
WHERE customer LIKE 'r%'
ORDER BY order_id;

order_id	customer
1001	Rahul
1005	Rahul

The pattern was lowercase 'r%' and capital R Rahul still matched, twice, once per order.

SQLite's default LIKE is case insensitive for ASCII letters. MySQL behaviour depends on collation; PostgreSQL provides ILIKE for case-insensitive matching. For these English names, UPPER(city) LIKE '%PUN%' makes the intention explicit.

Variation 2 - LIMIT, OFFSET and paging

filter rows
ORDER BY the ranking key
LIMIT n
wrap in a subquery
ORDER BY the printing key

OFFSET skips rows before LIMIT starts counting, so LIMIT 3 OFFSET 3 returns page 2 of a 3 per page list:

SQL

SELECT order_id, amount
FROM orders
ORDER BY amount DESC
LIMIT 3 OFFSET 3;

order_id	amount
1003	1500
1006	1500
1008	1200

Rows 4, 5 and 6 of the sorted list.

This output is not safe, even though it looks fine. Orders 1003 and 1006 are both 1500 and nothing says which comes first, so their relative order can change. They happen to fall on the same page here, but a tie across a page boundary can cause repeats or omissions between page requests. Add , order_id ASC for a stable order. LIMIT with no ORDER BY at all is worse, the database may hand you any 3 rows.

Variation 3 - The Nth highest distinct value

"Find the second highest salary" is one of the most asked SQL questions, in OAs and in interviews. It is a sorting question with one trap: duplicates. Take this employees table:

id	name	salary
1	Asha	90000
2	Vikram	75000
3	Neha	90000
4	Ravi	60000
5	Kavya	75000

The obvious answer skips one row and takes the next:

SQL

SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

It prints 90000, because Asha and Neha both earn 90000 and the second row of the sorted list is Neha's copy of the top salary. "Second highest" means the second highest different value, so remove the repeats first:

SQL

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

salary
75000

For the Nth highest, skip N minus 1 values: the third highest is LIMIT 1 OFFSET 2 and prints 60000.

One more requirement shows up often: if there is no such value (every employee earns the same, or there is only one), return a single row containing NULL, not an empty result. Put the query in brackets inside a SELECT. A query in brackets that finds nothing gives NULL:

SQL

SELECT (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
) AS second_highest_salary;

Queries inside queries get a full lesson on Day 6; this one shape is worth learning now, because the question comes up so often.

Check what you learned

SELECT customer FROM orders WHERE city NOT IN ('Delhi', NULL); returns zero rows on the orders table. Why?

Common Mistakes

Missing a requested sort. Translate "highest first" or "alphabetically" into an explicit ORDER BY. The order you see in a small sample is not guaranteed.
Leaving ties unresolved. Use the secondary key given in the statement. For a deterministic top-N result, use a unique key if no other tie rule is specified.
Returning extra columns or different headers. Select exactly the requested columns. Use aliases such as order_id AS ID when the output asks for them.
Confusing rounding with formatting. ROUND(x, 2) rounds a number; SQLite's PRINTF('%.2f', x) returns text with exactly two decimal places. Choose based on the output requirements.
Comparing with NULL using =. WHERE city = NULL returns no rows at all, not even Divya's, because any comparison with NULL is unknown. Write WHERE city IS NULL, and IS NOT NULL for the opposite.
Dropping unknown cities unintentionally. city <> 'Delhi' excludes NULL cities too. Use (city <> 'Delhi' OR city IS NULL) if unknown cities should be included.
Mixing AND and OR without brackets. city = 'Pune' OR city = 'Delhi' AND amount > 5000 includes every Pune order. Use (city = 'Pune' OR city = 'Delhi') AND amount > 5000 when the amount rule applies to both cities.

IMP
Before submitting: Check ties, NULLs, duplicate values and an empty result. A sample can confirm the expected shape without covering every condition.

Interview Notes

Where does WHERE run in the pipeline? Logically, WHERE follows FROM and precedes SELECT and ORDER BY. A filter can therefore use source columns that do not appear in the output.

Can you use a SELECT alias inside WHERE? SQLite allows some alias references in WHERE as an extension. Do not rely on this across databases: repeat the expression or put the computed column in a subquery.

What is the difference between WHERE and HAVING? WHERE filters individual rows; HAVING filters groups after aggregation. Day 2 uses both in the same query.

Is BETWEEN inclusive? Yes, on both ends. It also works on text and on dates written as YYYY-MM-DD, because that format sorts chronologically as plain text. Timestamp ranges need more care; an exclusive upper boundary avoids cutting off the last day.

IN versus a chain of OR? IN is a compact way to write equality checks joined by OR. Negating either form needs care with NULL: NOT IN cannot return TRUE if its list contains NULL.

How do NULLs sort? Defaults differ across databases. If NULL placement matters, say it explicitly: SQLite, PostgreSQL and Oracle accept ORDER BY city NULLS LAST.

How would you take the 3 biggest orders but print them by order_id? Put ORDER BY amount DESC LIMIT 3 in a subquery in the FROM clause, then ORDER BY order_id outside. The inner query picks the winners, the outer one decides the printing order.

Is LIKE case sensitive? It depends on the database and collation. SQLite's default LIKE ignores case for ASCII letters. For these English values, UPPER(col) LIKE '%PUN%' expresses a case-insensitive match.

Quick Test

Check what you learned

Which comes first logically, SELECT or WHERE?

SQL

SELECT amount * 0.02 AS coins FROM orders WHERE amount > 1000 ORDER BY coins DESC;

Week 01

SELECT, WHERE, ORDER BY & LIMIT

Day 11.5 to 2 hrs

From a statement to a query

A single-table question is usually straightforward until you check the details: should cancelled orders count, do ties need a second sort key, and should a missing city be included?

The examples use SQLite. In an assessment, check the selected database and the output requirements before submitting; syntax, formatting and judging rules can differ between platforms.

A query you can build on

Use this orders table throughout the lesson:

order_id	customer	city	amount	status	order_date
1001	Rahul	Hyderabad	2499	DELIVERED	2026-07-03
1002	Sneha	Pune	899	SHIPPED	2026-07-05
1003	Arjun	Hyderabad	1500	CANCELLED	2026-07-09
1004	Meera	Delhi	4750	DELIVERED	2026-07-11
1005	Rahul	Hyderabad	350	PENDING	2026-07-14
1006	Kiran	Pune	1500	SHIPPED	2026-07-18
1007	Divya	NULL	6200	DELIVERED	2026-07-21
1008	Farhan	Delhi	1200	SHIPPED	2026-07-25

Divya's city is NULL, meaning it was not recorded. That is different from an empty string and matters when we filter the city column.

Filter and sort in one query

Find delivered orders worth at least 2000, highest amount first:

SQL

SELECT order_id, customer, amount
FROM orders
WHERE status = 'DELIVERED' AND amount >= 2000
ORDER BY amount DESC;

order_id	customer	amount
1007	Divya	6200
1004	Meera	4750
1001	Rahul	2499

The query applies four decisions:

FROM orders identifies the source table.
WHERE status = 'DELIVERED' AND amount >= 2000 keeps only lines where both conditions hold, because AND means both must be true.
SELECT order_id, customer, amount copies those three columns.
ORDER BY amount DESC prints the biggest amount first.

All delivered orders in this sample happen to exceed 2000. Keep the amount condition anyway: the query must satisfy the statement on other data too.

Divya is included because neither condition checks her city.

Remove repeated rows with DISTINCT

SELECT DISTINCT removes duplicate rows from the result. Hyderabad appears on three orders, but a list of cities should show it once:

SQL

SELECT DISTINCT city
FROM orders
ORDER BY city;

city
NULL
Delhi
Hyderabad
Pune

When a statement says "unique", "different" or "without repeats", it is asking for DISTINCT.

Use this logical order to reason about the query:

FROM table
WHERE keeps rows
SELECT picks columns
ORDER BY sorts
LIMIT cuts

WHERE runs before SELECT, so you can filter on status and never print it. A SELECT alias can be used in ORDER BY to name a computed output column.

The Pattern

Before writing a query, settle these five things.

Read the required output block first. Write the columns down in the exact order and with the exact names asked for.
Separate the row filters from the output instructions. Use AND when conditions must hold together, OR for alternatives, and brackets when the two are mixed.
Ask what the row order must be. If the statement names an order, write ORDER BY with a tie break. For a top-N result, make ties deterministic using the stated rule or a suitable unique key.
Only then check whether you need DISTINCT, arithmetic, ROUND, PRINTF or LIMIT.
Run the sample, then check boundary values, ties and missing values against the statement.

A worked example. "The 3 biggest orders that were not cancelled, of 1000 and above."

SQL

SELECT customer, city, amount
FROM orders
WHERE status <> 'CANCELLED' AND amount >= 1000
ORDER BY amount DESC, order_id ASC
LIMIT 3;

Dry run, one line per step:

Step	What it does here	Rows alive
FROM orders	reads the table	8
WHERE status <> 'CANCELLED'	drops order 1003	7
WHERE amount >= 1000	drops 1002 (899) and 1005 (350)	5
GROUP BY	not used today	5
HAVING	not used today	5
SELECT customer, city, amount	keeps 3 of the 6 columns	5
ORDER BY amount DESC, order_id	6200, 4750, 2499, 1500, 1200	5
LIMIT 3	cuts the tail	3

Output:

customer	city	amount
Divya	NULL	6200
Meera	Delhi	4750
Rahul	Hyderabad	2499

Five rows reached the sort, and only the top three were printed.

Check the output format. A result displayed as pipe-separated text can look like this:

Text

customer|city|amount
Divya||6200
Meera|Delhi|4750
Rahul|Hyderabad|2499

Text

customer|city|amount
Rahul|Hyderabad|2499
Meera|Delhi|4750
Kiran|Pune|1500

Those are not the three largest qualifying orders. Sorting must happen before limiting.

Tip
Tip: Check the requested columns, aliases and order before submitting. These are part of the result, not just presentation.

Check what you learned

The statement says "list distinct city values from orders, alphabetically, including NULL". Which query matches?

Query Templates

SQL

-- 1. The workhorse: named columns, ANDed filters, explicit order with a tie break.
SELECT order_id, customer, amount
FROM orders
WHERE status = 'SHIPPED'
  AND amount >= 1000
ORDER BY amount DESC, order_id ASC;

-- 2. Set membership and ranges instead of a long chain of ORs.
--    IN checks if a value is one of a list. BETWEEN checks a range, both ends included.
SELECT order_id, city, order_date
FROM orders
WHERE city IN ('Delhi', 'Pune')
  AND order_date BETWEEN '2026-07-05' AND '2026-07-20'
  AND status IS NOT NULL;

-- 3. A computed column, renamed with AS so the header matches the statement.
SELECT order_id,
       amount,
       PRINTF('%.2f', amount * 0.02) AS coins_earned
FROM orders
WHERE status = 'DELIVERED'
ORDER BY order_id;

-- 4. Top N, then reprinted in a different order. Very common in OAs.
SELECT order_id, customer, amount
FROM (
    SELECT order_id, customer, amount
    FROM orders
    WHERE status <> 'CANCELLED'
    ORDER BY amount DESC, order_id ASC
    LIMIT 3
) AS t
ORDER BY order_id ASC;

Variations

Variation 1 - Pattern match with LIKE

LIKE compares text against a pattern instead of an exact value. % stands for any number of characters, _ for exactly one.

So LIKE 'R%' is names starting with R, LIKE '_iran' is a five letter name ending in iran, and NOT LIKE '%a%' is names with no a anywhere.

SQL

SELECT order_id, customer
FROM orders
WHERE customer LIKE 'r%'
ORDER BY order_id;

order_id	customer
1001	Rahul
1005	Rahul

The pattern was lowercase 'r%' and capital R Rahul still matched, twice, once per order.

Variation 2 - LIMIT, OFFSET and paging

filter rows
ORDER BY the ranking key
LIMIT n
wrap in a subquery
ORDER BY the printing key

OFFSET skips rows before LIMIT starts counting, so LIMIT 3 OFFSET 3 returns page 2 of a 3 per page list:

SQL

SELECT order_id, amount
FROM orders
ORDER BY amount DESC
LIMIT 3 OFFSET 3;

order_id	amount
1003	1500
1006	1500
1008	1200

Rows 4, 5 and 6 of the sorted list.

Variation 3 - The Nth highest distinct value

"Find the second highest salary" is one of the most asked SQL questions, in OAs and in interviews. It is a sorting question with one trap: duplicates. Take this employees table:

id	name	salary
1	Asha	90000
2	Vikram	75000
3	Neha	90000
4	Ravi	60000
5	Kavya	75000

The obvious answer skips one row and takes the next:

SQL

SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

SQL

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

salary
75000

For the Nth highest, skip N minus 1 values: the third highest is LIMIT 1 OFFSET 2 and prints 60000.

SQL

SELECT (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1
) AS second_highest_salary;

Queries inside queries get a full lesson on Day 6; this one shape is worth learning now, because the question comes up so often.

Check what you learned

SELECT customer FROM orders WHERE city NOT IN ('Delhi', NULL); returns zero rows on the orders table. Why?

Common Mistakes

Missing a requested sort. Translate "highest first" or "alphabetically" into an explicit ORDER BY. The order you see in a small sample is not guaranteed.
Leaving ties unresolved. Use the secondary key given in the statement. For a deterministic top-N result, use a unique key if no other tie rule is specified.
Returning extra columns or different headers. Select exactly the requested columns. Use aliases such as order_id AS ID when the output asks for them.
Confusing rounding with formatting. ROUND(x, 2) rounds a number; SQLite's PRINTF('%.2f', x) returns text with exactly two decimal places. Choose based on the output requirements.
Comparing with NULL using =. WHERE city = NULL returns no rows at all, not even Divya's, because any comparison with NULL is unknown. Write WHERE city IS NULL, and IS NOT NULL for the opposite.
Dropping unknown cities unintentionally. city <> 'Delhi' excludes NULL cities too. Use (city <> 'Delhi' OR city IS NULL) if unknown cities should be included.
Mixing AND and OR without brackets. city = 'Pune' OR city = 'Delhi' AND amount > 5000 includes every Pune order. Use (city = 'Pune' OR city = 'Delhi') AND amount > 5000 when the amount rule applies to both cities.

IMP
Before submitting: Check ties, NULLs, duplicate values and an empty result. A sample can confirm the expected shape without covering every condition.

Interview Notes

Where does WHERE run in the pipeline? Logically, WHERE follows FROM and precedes SELECT and ORDER BY. A filter can therefore use source columns that do not appear in the output.

What is the difference between WHERE and HAVING? WHERE filters individual rows; HAVING filters groups after aggregation. Day 2 uses both in the same query.

IN versus a chain of OR? IN is a compact way to write equality checks joined by OR. Negating either form needs care with NULL: NOT IN cannot return TRUE if its list contains NULL.

How do NULLs sort? Defaults differ across databases. If NULL placement matters, say it explicitly: SQLite, PostgreSQL and Oracle accept ORDER BY city NULLS LAST.

Quick Test

Check what you learned

Which comes first logically, SELECT or WHERE?

SQL

SELECT amount * 0.02 AS coins FROM orders WHERE amount > 1000 ORDER BY coins DESC;