1.4 Coding Practice: A Tiny Travel Database

1.4 Coding Practice: A Tiny Travel Database#

In this follow-along, you will:

  • inspect a small database table,

  • predict what a SQL query will return,

  • run the query,

  • modify it, and

  • explain what changed.

The goal is to connect the Week 1 ideas about databases, tables, rows, columns, and SQL to a visible result you can explore right away.

How to use this notebook#

  1. Download this file as ipynb

  2. Open this file in Google Colab (upload to https://colab.research.google.com/) or VS Code.

  3. Choose a Python kernel.

  4. Run each code cell from top to bottom, or use Run All.

  5. No extra files, installs, or database setup are required.

This activity is self-contained and works from a clean checkout or a downloaded notebook.

1. Load the data#

We will use SQLite in memory and create a tiny travel bookings dataset directly in this notebook. This keeps the activity self-contained and portable.

Instructions: Run the cell below to create the bookings table and load the sample rows.

Expected output: a short message telling you the dataset loaded successfully.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

rows = [
    (1, "Ana Torres", "Chicago", 2, "confirmed"),
    (2, "Derrick Hall", "Seattle", 4, "confirmed"),
    (3, "Mei Chen", "Denver", 1, "pending"),
    (4, "Riley Brooks", "Austin", 3, "cancelled"),
    (5, "Sofia Patel", "Boston", 2, "confirmed"),
    (6, "Noah Kim", "New Orleans", 5, "pending"),
]

cur.execute(
    """
    CREATE TABLE bookings (
        booking_id INTEGER,
        traveler_name TEXT,
        destination TEXT,
        nights INTEGER,
        status TEXT
    )
    """
)

cur.executemany("INSERT INTO bookings VALUES (?, ?, ?, ?, ?)", rows)
conn.commit()

print(f"Loaded {len(rows)} rows from the in-memory dataset")

Loaded 6 rows from the in-memory dataset

2. Inspect the table structure#

A schema tells us what columns a table has and what type of data each column stores.

Instructions: Run the cell below to print the columns and their data types.

print("Columns in bookings:")
for column in cur.execute("PRAGMA table_info(bookings)").fetchall():
    _, name, dtype, _, _, _ = column
    print(f"- {name} ({dtype})")

Columns in bookings:
- booking_id (INTEGER)
- traveler_name (TEXT)
- destination (TEXT)
- nights (INTEGER)
- status (TEXT)

3. Observe the full table#

Predict: What do you think SELECT * will show?

Then run the query and compare your prediction.

Expected output: a table with six rows, showing the booking_id, traveler name, destination, length of stay, and booking status.

def show_query(sql_text):
    result = cur.execute(sql_text)
    headers = [description[0] for description in result.description]
    rows = result.fetchall()

    print(" | ".join(headers))
    print("-" * (len(" | ".join(headers))))
    for row in rows:
        print(" | ".join(str(value) for value in row))
    print(f"\n{len(rows)} row(s) returned")


show_query("SELECT * FROM bookings;")

booking_id | traveler_name | destination | nights | status
----------------------------------------------------------
1 | Ana Torres | Chicago | 2 | confirmed
2 | Derrick Hall | Seattle | 4 | confirmed
3 | Mei Chen | Denver | 1 | pending
4 | Riley Brooks | Austin | 3 | cancelled
5 | Sofia Patel | Boston | 2 | confirmed
6 | Noah Kim | New Orleans | 5 | pending

6 row(s) returned

4. Select only the columns you need#

Now try a smaller query that shows just the traveler and destination.

Your turn: change the order of the columns and rerun the cell.

Expected output: a two-column table listing traveler names and destinations.

show_query("SELECT traveler_name, destination FROM bookings;")

traveler_name | destination
---------------------------
Ana Torres | Chicago
Derrick Hall | Seattle
Mei Chen | Denver
Riley Brooks | Austin
Sofia Patel | Boston
Noah Kim | New Orleans

6 row(s) returned

5. Filter the rows with WHERE#

The next query shows only confirmed bookings.

Predict: What will change if you replace confirmed with pending?

Expected output: only the rows where status is confirmed are displayed.

show_query(
    "SELECT traveler_name, destination, status FROM bookings WHERE status = 'confirmed';"
)

traveler_name | destination | status
------------------------------------
Ana Torres | Chicago | confirmed
Derrick Hall | Seattle | confirmed
Sofia Patel | Boston | confirmed

3 row(s) returned

6. What you should notice#

  • A database stores related data in a structured way.

  • A table stores rows and columns.

  • SQL lets you inspect that data.

  • SELECT chooses the data you want.

  • WHERE filters the rows you want.

If you can read the query and explain the result, you have the core Week 1 idea.

This notebook is designed to be run from top to bottom without any hidden setup steps.

conn.close()
print("Connection closed")

Connection closed
Contents