Set up a practice database

What you will be able to do

Get the same database on your computer as in the browser, and run queries that you save in files. Saved queries can be run again, compared and reviewed. A query that you typed once in a box cannot.

NotebookSee each setup step in a notebookColab · Kaggle · GitHub

The notebook does the steps of get_data.py one at a time: download, checksum, build and read-only connection. Then it runs get_data.py, the reset and q.py, and has the module practice with its checks. It needs nothing on your computer. To make the project on your own computer, follow the steps on this page in a terminal.

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 50 minutes

What you need to know

• Primary keys, foreign keys and why the export has no constraints, from Relational concepts: Keys and Why the export has no constraints.
• The grain of each table, from Dataset anatomy.
• Folders, paths and the home folder ~, from Files and terminal: Folders and paths. Commands with arguments, from Commands and arguments.

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

• A terminal. To open one, see Files and terminal: Get a terminal.
• Python 3.12 or later. To install it, see Install and verify Python: Step 1. To check the version, see Step 2: check which Python runs.
• A code editor, for example VS Code, from Step 3: get a code editor.
• An internet connection. Everything is free, and you need no account.
• No virtual environment is needed in this module. The two scripts use only the Python standard library. You make the virtual environment in Module 4, when you first need pandas.
• This lesson makes the folder ~/projects/ticket-data. If a step goes wrong, you can start again: reset the database, or delete the folder and follow Build the database again.

Tested on macOS with Python 3.14.6 (SQLite 3.50.4 inside Python). The Windows and Linux steps are not tested. The browser boxes use SQLite 3.49.1. The notebook was tested in Jupyter.

Two routes to one database

Up to now, every query ran in your browser. The box loads the file larkfield.sqlite and runs SQLite in the page. That is quick for short questions. Real data work also needs files: a query saved in queries/, which you run again next week, or a Python script that reads the database. From Module 3, you write such scripts.

So you build the same database on your computer. Two small scripts do the work:

Script	What it does
get_data.py	Downloads the three CSV files of the export into data/raw/, checks that each file is correct, and loads them into larkfield.db
q.py	Runs one SQL query, from a .sql file or typed in quotes, and prints the result as a table

The browser file and your larkfield.db hold the same data. A query gives the same numbers on both routes.

At the end of this lesson, your project folder looks like this. The course adds more folders later.

Folder tree

projects
└── ticket-data
    ├── get_data.py         downloaded
    ├── q.py                downloaded
    ├── larkfield.db        built by get_data.py
    ├── data
    │   └── raw             customers.csv, tickets.csv, outcomes.csv
    └── queries             your .sql files: 01-first-look.sql, ...

Which Python command to type

The command that starts Python depends on your system:

• macOS and Linux: python3
• Windows: python

The rest of this course writes python. On macOS and Linux, type python3 until you make the virtual environment in Module 4. Inside an active virtual environment, python works on every system.

Build the database

1. Open a terminal.

2. Make the project folder, and go into it:

   macOS

   mkdir -p ~/projects/ticket-data
   cd ~/projects/ticket-data

   Windows

   mkdir ~\projects\ticket-data
   cd ~\projects\ticket-data

   If the folder already exists, mkdir shows an error. You can ignore it.

   Linux

   mkdir -p ~/projects/ticket-data
   cd ~/projects/ticket-data

3. Download the two scripts:

   macOS

   curl -o get_data.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/get_data.py
   curl -o q.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/q.py

   Windows

   curl.exe -o get_data.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/get_data.py
   curl.exe -o q.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/q.py

   Type curl.exe, not curl. In Windows PowerShell, curl is a different command.

   Linux

   curl -o get_data.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/get_data.py
   curl -o q.py https://raw.githubusercontent.com/Nextia-Intelligence/nextia-learning-labs/main/C04/q.py

   If the output is curl: command not found, run sudo apt install curl (or sudo dnf install curl) first.

   If you cannot use curl, open each link in your browser, and save the page with the same file name in the project folder.

4. Make the folder for your queries:

   mkdir queries

5. Build the database:

   macOS

   python3 get_data.py

   Windows

   python get_data.py

   Linux

   python3 get_data.py

   Output

   Downloading customers.csv ...
   Downloading tickets.csv ...
   Downloading outcomes.csv ...
   customers    240 rows
   tickets     1417 rows
   outcomes    1874 rows
   Built larkfield.db. Check: customers 240, tickets 1417, outcomes 1874.

   The script compares each row count with the line Check:. They must be the same.

6. Check the database with a first query. Type python instead of python3 on Windows:

   python3 q.py "SELECT COUNT(*) FROM tickets"

   Output

   COUNT(*)
   --------
   1417
   
   1 row

If you see 1417, the setup works. This is the same number as in the browser.

The script checks each downloaded file with a checksum. A checksum is a long code calculated from the bytes of a file. If one byte is different, the code is different. If a file is damaged, get_data.py stops with ... has the wrong checksum and tells you what to do. If the download fails, it stops with Could not download .... Check your internet connection, and run it again. The notebook runs each step of get_data.py in a separate cell, so you can see what the script does.

Run a saved query

Typing queries in quotes works for one line. For anything longer, save the query in a file. The course numbers the files, so they stay in the order that you wrote them.

1. Open the ticket-data folder in VS Code (File › Open Folder).

2. In the queries folder, make a new file, 01-first-look.sql.

3. Type this query in the file, and save it:

   queries/01-first-look.sql

   -- The first five tickets in the export
   SELECT ticket_id, customer_id, created_at, team
   FROM tickets
   LIMIT 5;

4. In the terminal, in the project folder, run the file:

   python3 q.py queries/01-first-look.sql

Output

ticket_id  customer_id  created_at           team
---------  -----------  -------------------  --------
T-10001    C-0228       2026-01-05 07:30:24  returns
T-10002    C-0207       2026-01-05 09:09:23  delivery
T-10003    C-0119       2026-01-05 13:22:27  delivery
T-10004    C-0048       2026-01-05 16:14:56  returns
T-10005    C-0039       2026-01-05 17:31:57  returns

5 rows

A line that starts with -- is a comment. SQL does not run it. Use a comment to say what the query answers.

The query names its columns instead of SELECT *. A saved query must give the same columns next month, even if the export adds a column. From Module 2, every saved query names its columns.

q.py prints at most 20 rows, then the total. For example, python3 q.py "SELECT ticket_id FROM tickets" ends with 1417 rows (showing 20; add --all to see every row). Add --all at the end of the command to print every row.

The database is protected, and you can reset it

q.py opens the database read-only: it can read, but it cannot change anything. Try to delete all tickets:

python3 q.py "DELETE FROM tickets"

Output

SQL error: attempt to write a readonly database

Nothing changed. This protects the data from a mistake in a query.

Other tools can change the file. If you are not sure that larkfield.db is still the original, build it again from the CSV files. This is the reset:

python3 get_data.py --reset

Output

Deleted larkfield.db.
customers    240 rows
tickets     1417 rows
outcomes    1874 rows
Built larkfield.db. Check: customers 240, tickets 1417, outcomes 1874.

The CSV files are already in data/raw/ and their checksums are correct, so the script does not download them again. If you run get_data.py without --reset while larkfield.db exists, it stops and tells you to use --reset. It never replaces the database by accident.

Other ways to open the database

q.py is all you need in this course. Two other tools are optional:

• The Python SQLite shell. Run python3 -m sqlite3 larkfield.db. You get the prompt sqlite>, where you type SQL statements that end with ;. Type .quit to leave. This shell can change the database. If you change it by mistake, reset it.
• DB Browser for SQLite is a free program with windows and tables to click. It is useful to look at data, but the course does not need it.

SQLite and PostgreSQL

SQLite is the database of this course, because it is one file and needs no server. Many companies use PostgreSQL, a database server. Most SQL in this course works in both. These are the differences that you meet:

Topic	SQLite	PostgreSQL
Types	Flexible: a column of type INTEGER can still store text	Strict: a wrong type is an error
Dates and times	No date type. Dates are text, and date() and strftime() work on that text	Types date and timestamp, with functions such as date_trunc and EXTRACT
Join text with ||	Yes	Yes, the same
Match text without case: ILIKE	No	Yes
LIMIT	Yes	Yes, the same
True and false	1 and 0	A BOOLEAN type with true and false
Foreign key checks	Only after PRAGMA foreign_keys = ON;	Always
Parameters in Python	? or :name (module sqlite3)	%s or %(name)s (module psycopg)

You do not install PostgreSQL in this course. Where a later query is different in PostgreSQL, the lesson adds a short note.

Common mistake

larkfield.db is not in this folder

Symptom: you run a query, and you see this message:

larkfield.db is not in this folder. Run the script from your project folder, or build the database first: python get_data.py

Cause: q.py looks for larkfield.db in the working directory, the folder that the terminal is in. If you run python3 ticket-data/q.py from the projects folder, or from a new terminal in your home folder, the file is not there.

Fix:

1. Run pwd to see the working directory.
2. Go to the project folder: cd ~/projects/ticket-data.
3. Run ls (or dir on Windows). You must see larkfield.db. If it is missing, run python3 get_data.py.
4. Run the query again.

In AI work: scripts that use relative paths depend on the working directory. Always run the course scripts from the project folder. A script that finds no file and stops is good. A script that quietly makes a new, empty database is much worse.

Check your understanding

You ran a DELETE in the Python SQLite shell, and now a count of tickets gives 0. How do you get the original data back, and why does it work?

Show the answer

Run python get_data.py --reset (or python3 on macOS and Linux). It deletes larkfield.db and builds it again from the three CSV files in data/raw/. The CSV files are the export. The database is only a copy built from them, so you can always build it again. The check line must show customers 240, tickets 1417, outcomes 1874.

Check your understanding

Why does the course save queries in numbered .sql files instead of only typing them?

Show the answer

A saved query can be run again on the same snapshot and must give the same result. Another person can read it, review it and run it. The file name and the comment say what the query answers. A query typed once is lost, and nobody can check how a number was made.

Module practice

Worked example: read the schema in SQL

Mei wants to see the schema of tickets without opening get_data.py. SQLite describes each table with pragma_table_info. You use it like a table: one row for each column.

Run SQL in your browser

Try it: the columns of tickets

Run the query. You should see 11 rows, one for each column, from ticket_id to closed_at. Look at the column pk.

SQL query (you can change it)SELECT cid, name, type, pk FROM pragma_table_info('tickets');

Mei saves the same query as queries/02-schema.sql and runs it on her computer:

python q.py queries/02-schema.sql

cid  name                 type     pk
---  -------------------  -------  --
0    ticket_id            TEXT     0
1    customer_id          TEXT     0
2    created_at           TEXT     0
3    channel              TEXT     0
4    team                 TEXT     0
5    priority             INTEGER  0
6    order_value          REAL     0
7    word_count           INTEGER  0
8    first_reply_minutes  INTEGER  0
9    priority_now         INTEGER  0
10   closed_at            TEXT     0

11 rows

cid is the position of the column, from 0. pk is 1 for a column in a PRIMARY KEY constraint, and 0 otherwise. Every value is 0: the table has no primary key constraint. This confirms what you found in the last lesson: you must check the keys with queries.

Then she counts the rows of all three tables in one query. UNION ALL puts the results of several queries under each other, as one table.

queries/03-counts.sql

-- Rows in each table of the export
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'tickets', COUNT(*) FROM tickets
UNION ALL
SELECT 'outcomes', COUNT(*) FROM outcomes;

Output

table_name  row_count
----------  ---------
customers   240
tickets     1417
outcomes    1874

3 rows

The counts are the same as the Check: line of get_data.py. So the load is complete.

Your task: the other two tables

On your computer, in the project folder:

1. Save a query queries/04-schema-outcomes.sql that shows the name and the type of each column of outcomes. Run it with q.py.
2. Do the same for customers in queries/05-schema-customers.sql.
3. Run queries/03-counts.sql.
4. In a file notes.md in the project folder, write one line for each table: its name, its row count, its grain and its primary key.

Check your first query in the box too:

Run SQL in your browser

Your turn: the schema of outcomes

Your result must have two columns, the name and the type, and one row for each column of outcomes.

SQL query (you can change it)-- Show the name and the type of each column of outcomes

Show the model answer

queries/04-schema-outcomes.sql

-- The columns of the outcomes table, with their types
SELECT name, type
FROM pragma_table_info('outcomes');

The result has 6 rows: outcome_id TEXT, ticket_id TEXT, recorded_at TEXT, status TEXT, resolution_code TEXT and csat INTEGER. For customers, the result has 4 rows: customer_id, segment, region and joined_on, all TEXT.

notes.md

# Larkfield export of 2026-07-01 06:00

- customers: 240 rows. One row = one customer. Primary key: customer_id (checked: unique, no NULL).
- tickets: 1417 rows, 1403 tickets. One row = one ticket. Primary key: ticket_id, but 14 IDs appear twice (exact duplicates).
- outcomes: 1874 rows. One row = one event in a ticket's life. Primary key: outcome_id (checked: unique).

Module check

Check your understanding

Retrieval: what are a primary key and a foreign key, and which one can repeat?

Show the answer

A primary key identifies one row of its table: it is unique and never NULL, for example customers.customer_id. A foreign key is a column that points to the primary key of another table, for example tickets.customer_id. The foreign key can repeat, because one customer has many tickets. It can also be NULL when a row may have no match, as for a guest ticket.

Check your understanding

Predict: the help desk logs one more event for ticket T-10014 (it is reopened again). Which of these numbers change: the rows in outcomes, the different ticket_id values in outcomes, the rows in tickets?

Show the answer

Only the rows in outcomes change, from 1,874 to 1,875. T-10014 already has events, so the number of different ticket_id values in outcomes stays the same. The tickets table does not change: the grain of tickets is one ticket, and no new ticket was made. One-to-many means that the “many” side grows, and the “one” side does not.

Check your understanding

Completion check: for each table, name its grain and the keys that connect it to the others. Say where each connection is not safe in this export, with the numbers.

Show the answer

Table	Grain (one row is)	Primary key	Connects through
customers	one customer	customer_id: 240 rows, 240 different values, no NULL. Safe.	tickets.customer_id points here
tickets	one ticket	ticket_id: 1,417 rows but 1,403 different values. 14 tickets appear twice as exact duplicates. Not safe until the duplicates are removed.	customer_id → customers.customer_id: 12 rows are NULL (guests) and 9 rows point to deleted customers (C-0241 to C-0244)
outcomes	one event in a ticket’s life	outcome_id: 1,874 rows, 1,874 different values. Unique.	ticket_id → tickets.ticket_id: one-to-many, and 4 events point to tickets that are not in the export (T-9990 to T-9993)

A connection is safe when the key on the “one” side is unique. You must also know what happens to the rows on the “many” side that match nothing. Here, tickets.ticket_id is not unique yet, and both foreign keys have rows that match nothing. Module 2 shows what these problems do to a join.

Recap

In this module, you learned to read a dataset before you query it:

• Each table has rows and columns, and each column has a type. NULL means “no value”. The schema describes the tables; only the data shows what they actually hold.
• The grain says what one row means: one customer, one ticket, or one event in a ticket’s life. Say it before you count.
• Primary keys identify rows, and foreign keys connect tables in one-to-many relationships. The export has no constraints, so you check the keys yourself.
• The export of 2026-07-01 06:00 has 240 customers, 1,417 ticket rows (1,403 tickets) and 1,874 outcome events. It has 14 duplicated tickets, 9 tickets of deleted customers, 12 guest tickets and 4 events without a ticket.
• You have the same database in the browser and in ~/projects/ticket-data/larkfield.db. You run saved queries with python q.py, and you reset with python get_data.py --reset.

Next, you ask the database real questions: select columns, filter rows and sort them, and you meet the first quiet mistakes in filters.

Next: Select and filter