Select and filter

What you will be able to do

Get exactly the rows and columns that a question needs from a table. Then check that a filter did not drop rows that you wanted to keep. A filter that drops rows gives no error, so you learn to count.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions. Its first cell downloads the practice data and builds the database, so it works without the local project from Module 1. Read the explanations here, and run the code there.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 45 minutes

What you need to know

• The three tables, customers, tickets and outcomes, and their grain, from Dataset anatomy.
• That tickets has 1,417 rows but 1,403 distinct tickets, from Relational concepts.

What you need on your computer (only for the local route; the notebook and the boxes on this page need none of it)

• The folder ~/projects/ticket-data with larkfield.db and q.py. You made it in Set up a practice database: Build the database.
• If the folder or the database is missing or changed, follow that section again, or reset the database with python3 get_data.py --reset.
• A terminal open in that folder. Type python3 on macOS and Linux and python on Windows, as explained in Which Python command to type.
• The folder queries/ for your saved queries, as in Run a saved query. The queries of this module are numbered from 21-. This lesson adds 21- and 22-.

Tested on macOS with Python 3.14.6 (SQLite 3.50.4). The browser boxes use SQLite 3.49.1. The notebook was tested in Jupyter.

Larkfield is a fictional online shop for home and garden products. Its customers contact support, and each contact is a ticket. Mei, a data engineer, prepares the ticket data for a model that predicts escalations. In this module she answers questions from the support team with SQL. The data is made up for this course; no real people are in it.

A query on six rows

A query is a question that you send to a database, written in SQL. The database answers with a table of rows. Start with a table small enough to see completely: six tickets.

ticket_id	channel	team	priority
T-1	email	delivery	1
T-2	chat	returns	3
T-3	Email	delivery	2
T-4	NULL	payment	1
T-5	phone	delivery	3
T-6	email	account	2

NULL means “no value is stored here”. For T-4, nobody recorded the channel. NULL is not the text 'NULL' and not an empty text ''. It is the absence of a value.

A query has parts called clauses. Each clause starts with a keyword:

SELECT ticket_id, channel     -- which columns to show
FROM tickets                  -- which table to read
WHERE priority = 1            -- which rows to keep
ORDER BY ticket_id            -- the order of the rows
LIMIT 10;                     -- the most rows to show

Only SELECT is required; FROM is required when you read a table. The clauses must come in this order. The database works through them in a different order. It reads the table (FROM) and keeps the rows that pass the test (WHERE). Then it picks the columns (SELECT), sorts (ORDER BY) and cuts the list (LIMIT). Text after -- is a comment, which SQL ignores.

Run SQL in your browser

Try it: six tickets

This box has only the six tickets above. Before you press Run, predict how many rows the query returns. Then run it. You should see 2 rows: T-1 and T-4. Change priority = 1 to team = 'delivery' and run it again: 3 rows.

SQL query (you can change it)SELECT ticket_id, channel FROM tickets WHERE priority = 1;

What WHERE keeps

WHERE tests each row. It keeps a row only when the test is true. Here are two tests on the six tickets:

ticket_id	channel	channel = 'email'	channel <> 'email'
T-1	email	true: kept	false: dropped
T-2	chat	false: dropped	true: kept
T-3	Email	false: dropped	true: kept
T-4	NULL	unknown: dropped	unknown: dropped
T-5	phone	false: dropped	true: kept
T-6	email	true: kept	false: dropped
Rows kept		2	3

Two things in this table are surprises:

• T-3 is not an email ticket for channel = 'email'. Text comparison is exact: 'Email' with a capital E is a different value. A person reads both as “email”; SQL does not.
• T-4 is in neither result. 2 + 3 = 5, not 6. Any comparison with NULL, such as NULL = 'email' or NULL <> 'email', gives unknown, not true or false. WHERE keeps only true, so the row disappears from both. You get no error and no warning.

To test for NULL, use IS NULL or IS NOT NULL. channel = NULL is also unknown for every row, so it returns no rows at all.

Run SQL in your browser

Try it: comparisons with NULL

This query reads no table; it only calculates three tests. SQLite shows true as 1 and false as 0. You should see NULL, NULL and 1: only IS NULL gives a real answer.

SQL query (you can change it)SELECT NULL = NULL AS equals_null, NULL <> 'email' AS not_email, NULL IS NULL AS is_null;

The tools of a filter

These are the parts that you use in WHERE. Each example runs on the real tickets table, which has 1,417 rows.

Part	Meaning	Example	Rows
=, <>	equal, not equal (!= also works)	team = 'payment'	241
<, <=, >, >=	compare numbers, or text in alphabetical order	word_count > 1000	1
AND	both tests are true	priority = 1 AND team = 'delivery'	49
OR	at least one test is true	team = 'payment' OR team = 'returns'	568
NOT	the opposite of a test	NOT team = 'account'	1,229
IN (…)	equal to one of the values in a list	channel IN ('chat', 'phone')	598
LIKE	matches a pattern: % is any text, _ is one character	channel LIKE 'web%'	205
IS NULL, IS NOT NULL	no value, a value	channel IS NULL	38

Text values go in single quotes: 'payment'. Numbers do not: priority = 1. Column names have no quotes.

LIKE 'web%' finds 205 rows: 200 rows of web_form and 5 rows of webform. A pattern is a good way to find spellings that you did not know about.

AND before OR

SQL does AND before OR, as arithmetic does × before +. team = 'payment' OR team = 'returns' AND priority = 1 means “all payment tickets, plus urgent returns tickets”: 280 rows. If you mean “urgent tickets of the two teams”, write brackets: (team = 'payment' OR team = 'returns') AND priority = 1 gives 98 rows. team IN ('payment', 'returns') AND priority = 1 also gives 98 and is easier to read.

In PostgreSQL: LIKE is case-sensitive, so channel LIKE 'email' finds only email. SQLite’s LIKE ignores the case of the letters A to Z, so it also finds Email and EMAIL. PostgreSQL has ILIKE for a search that ignores case. Do not depend on either behaviour: write the rule that you mean, as the worked example shows.

Worked example: how many email tickets?

Grace, the support lead, asks Mei: “How many tickets came in by email? I want to plan the email team.” Mei writes the obvious query first, and counts the rows. COUNT(*) counts rows; the next lesson explains it in detail.

SELECT COUNT(*) AS email_rows
FROM tickets
WHERE channel = 'email';

The answer is 541 rows. Before she sends it, Mei checks which values the column really contains. SELECT DISTINCT returns each different value once:

queries/21-channel-values.sql

-- Every spelling of channel in the tickets table.
SELECT DISTINCT channel
FROM tickets
ORDER BY channel;

Output of python3 q.py queries/21-channel-values.sql

channel
--------
NULL
 chat
EMAIL
Email
chat
email
phone
web_form
webform

9 rows

There are 9 values for 5 real channels. chat has a space at the start. Email and EMAIL are email with capitals. webform is web_form without the underscore. And NULL means “not recorded”. This is a messy category: one meaning, several spellings. Look at the order too: SQLite sorts NULL first, then by character code, so a space comes before capitals, and capitals come before small letters.

For a report, Mei compares a standard form of the text. TRIM removes spaces at the start and the end, and LOWER changes capitals to small letters:

SELECT COUNT(*) AS email_rows
FROM tickets
WHERE LOWER(TRIM(channel)) = 'email';

The answer is 568 rows: 541 + 20 (Email) + 7 (EMAIL). The first query missed 27 rows, about 5% of the email work.

Then Grace asks a second question: “And how many came in by another channel?” Mei predicts 1,417 − 541 = 876 rows, and checks:

Query	Rows
WHERE channel = 'email'	541
WHERE channel <> 'email'	838
WHERE channel IS NULL	38
Total	1,417

The two “opposite” filters together cover only 1,379 rows. The 38 rows with no channel are in neither, exactly as T-4 was. Mei sends Grace both numbers and one sentence: “38 tickets have no recorded channel; I count them separately.”

Two notes about these numbers:

• They count rows, not tickets. The table has 14 duplicate rows, as you found in Module 1. Module 2 counts distinct tickets in the next lesson.
• Mei does not change the data. LOWER(TRIM(channel)) changes only what this one query compares. Module 4 cleans the column itself, with a record of every change.

Columns, aliases and sorting

SELECT * returns every column. It is useful to look at a table once. In a saved query, name the columns that you need:

• The query says what it uses. A reader does not need to look up the table.
• The result does not change when the export changes. The next help-desk export renames channel to contact_channel. A query that names channel stops with an error: no such column: channel. A query with SELECT * runs, and gives you a different table without a word.

An alias gives a column in the result a new name, with AS: word_count AS words. An alias is useful for a calculated column, such as LOWER(TRIM(channel)) AS channel_for_report. It changes only the result, not the table.

ORDER BY sorts the rows: ASC (the default) from small to large, DESC from large to small. Without ORDER BY, the order of the rows is not defined: it can change between runs or between database versions. So LIMIT without ORDER BY gives “some 10 rows”, not “the first 10”.

Dates in this database are text in the form YYYY-MM-DD HH:MM:SS. Text in this form sorts in time order, so ORDER BY created_at DESC puts the newest ticket first.

Run SQL in your browser

Try it: the newest urgent delivery tickets

Grace wants to read the newest urgent delivery tickets. Predict: will the first row be from June 2026? Run the query. You should see 10 rows; the first is T-11384, created on 2026-06-29. Then remove the two WHERE lines and run it again. The first row is now T-10147, created in 2036. Sorting shows the extremes of a column, so it is a quick way to find impossible values.

SQL query (you can change it)SELECT ticket_id, created_at, LOWER(TRIM(channel)) AS channel_for_report, word_count AS words FROM tickets WHERE priority = 1 AND team = 'delivery' ORDER BY created_at DESC LIMIT 10;

Guided practice: save the queries

Run the same queries on your computer, and save them in your project. From now on, each saved query is a file in queries/.

1. Open a terminal in ~/projects/ticket-data.

2. Make the file queries/21-channel-values.sql with the query from the worked example.

3. Run it:

   macOS

   python3 q.py queries/21-channel-values.sql

   Windows

   python q.py queries/21-channel-values.sql

   Check: the output has 9 rows, and the first is NULL.

4. Make the file queries/22-urgent-delivery.sql with the query from the box above.

5. Run it:

   macOS

   python3 q.py queries/22-urgent-delivery.sql

   Windows

   python q.py queries/22-urgent-delivery.sql

   Check: the output has 10 rows, and the first line is T-11384 2026-06-29 11:40:07 chat 84.

6. Change LIMIT 10 to LIMIT 3, and run the file again. Check: you see 3 rows, T-11384, T-11361 and T-11301.

7. Change LIMIT 3 back to LIMIT 10. Save the file.

Show the full output of step 5

Output of python3 q.py queries/22-urgent-delivery.sql

ticket_id  created_at           channel_for_report  words
---------  -------------------  ------------------  -----
T-11384    2026-06-29 11:40:07  chat                84
T-11361    2026-06-26 11:16:44  phone               175
T-11301    2026-06-19 10:40:56  email               63
T-11278    2026-06-17 11:50:29  chat                60
T-11200    2026-06-06 07:04:17  email               40
T-11198    2026-06-05 19:31:52  email               98
T-11180    2026-06-03 15:21:56  chat                153
T-11066    2026-05-20 11:44:46  chat                56
T-11059    2026-05-19 15:08:42  email               127
T-11054    2026-05-19 10:13:05  email               177

10 rows

If you see SQL error: no such column, check the spelling of each column name. If you see larkfield.db is not in this folder, your terminal is not in ~/projects/ticket-data.

Run SQL in your browser

Your turn: urgent tickets with no channel

Grace wants to know which urgent tickets have no recorded channel. The query in the box has a mistake, so it returns no rows. Find the mistake and fix it. When it is correct, you see 5 rows, the first is T-10133, and the check passes.

SQL query (you can change it)-- Urgent tickets (priority 1) with no recorded channel. -- Show ticket_id, channel and team, sorted by ticket_id. SELECT ticket_id, channel, team FROM tickets WHERE priority = 1 AND channel = NULL ORDER BY ticket_id;

Show the example answer

SELECT ticket_id, channel, team
FROM tickets
WHERE priority = 1
  AND channel IS NULL
ORDER BY ticket_id;

channel = NULL is unknown for every row, so WHERE keeps none. channel IS NULL is true for the 38 rows with no channel. Five of them have priority 1: T-10133, T-10160, T-10229, T-10374 and T-10437.

Independent variation: everything except chat

Omar, the product manager, asks: “How many tickets did not come in by chat? Include the tickets with no recorded channel, and count chat with a space as chat.”

Run SQL in your browser

Your turn: not chat

The starting query gives a number, but it is wrong in two ways. Change only the WHERE clause. Before you run your answer, write down a prediction: the table has 1,417 rows, and 421 of them are chat in some spelling.

SQL query (you can change it)-- Count the rows that did not come in by chat. -- Count ' chat' as chat. Count rows with no channel as "not chat". SELECT COUNT(*) AS not_chat_rows FROM tickets WHERE channel <> 'chat';

Show the example answer

SELECT COUNT(*) AS not_chat_rows
FROM tickets
WHERE LOWER(TRIM(channel)) <> 'chat'
   OR channel IS NULL;

The answer is 996 rows: 1,417 − 421 = 996. The starting query gives 966: it counts the 8 rows of chat as “not chat” (+8), and it drops the 38 rows with no channel (−38). Without the OR channel IS NULL line, the corrected comparison gives 958, still 38 short. Always check a “not” filter like this: the rows that pass plus the rows that fail must equal the whole table.

Common mistake

A “not email” filter loses the rows with no channel

Symptom: Tomás builds a training table of “all tickets that did not come by email”, to test if the model works the same for other channels. He uses WHERE channel <> 'email' and gets 838 rows. Later, Priya counts by hand: 1,417 rows minus 541 email rows is 876. Where are the other 38?

Cause: for the 38 rows where channel is NULL, channel <> 'email' is unknown, not true. WHERE drops them. SQL gives no error and no warning. The same filter also kept the 27 rows of Email and EMAIL, which are email tickets.

Diagnose: count both sides of the filter and the NULLs, and add them up: 541 + 838 + 38 = 1,417. If the sum of “yes” and “no” is smaller than the table, NULL rows are missing.

Fix: decide what the NULL rows mean, and write it in the query. Here, “no channel” is not email, so Tomás keeps them: WHERE LOWER(TRIM(channel)) <> 'email' OR channel IS NULL gives 849 rows (1,417 − 568).

Consequence in AI work: the model never sees a ticket with no channel during training. In production, such tickets still arrive, and the model’s score on them is unknown. A filter that silently removes a group is one way that training data stops matching real data.

Check your understanding

A table has 100 rows. WHERE status = 'open' returns 60 rows, and the opposite filter, WHERE NOT status = 'open', returns 30 rows. Explain the missing 10 rows, and write a query that returns them.

Show the answer

The 10 rows have NULL in status. Any comparison with NULL is unknown, and WHERE keeps only rows where the test is true, so these rows are in neither result. To find them: SELECT * FROM the_table WHERE status IS NULL; (or name the columns). It should return 10 rows, and 60 + 30 + 10 = 100.

Check your understanding

Mei's query WHERE channel = 'email' returns 541 rows, and WHERE LOWER(TRIM(channel)) = 'email' returns 568. What does the difference of 27 rows tell you about the data, and why does Mei not fix the data in this query?

Show the answer

27 rows store email with capitals: 20 Email and 7 EMAIL. The column is a messy category: one meaning with several spellings. The second query compares a standard form of the text for this one report, and does not change the table. Changes to the data belong in a cleaning step that keeps the original values and counts each change (Module 4). Then everyone uses the same corrected data and can check it.

Check your understanding

Why should a saved query name its columns instead of using SELECT *? Give one reason that matters when the next export arrives.

Show the answer

A saved query with named columns says what it uses, and it fails clearly when the source changes. In the next Larkfield export, channel is renamed contact_channel. A query that names channel stops with no such column: channel, so you notice at once. A query with SELECT * runs and returns a different set of columns, and the problem shows up later, in a report or a model.

Recap

• SELECT picks columns, FROM names the table, WHERE keeps the rows where a test is true, ORDER BY sorts and LIMIT cuts the list. Without ORDER BY, the row order is not defined.
• Name the columns in a saved query. Use AS for clear names in the result.
• NULL means “no value”. Any comparison with NULL is unknown, so WHERE drops the row. Test for it with IS NULL.
• Text comparison is exact. 'Email' is not 'email'. Look at the distinct values of a category before you filter on it. For a report, compare LOWER(TRIM(column)); clean the data itself in Module 4.
• Check a filter by counting: the rows that pass, the rows that fail and the NULL rows must add up to the table.

Next, you count and summarise many rows at once, and see how NULL values and wrong values change a total or an average.

Next: Aggregate correctly