Chapter 23 Exercises: Database Basics

These exercises use the Acme Corp inventory database created by sqlite_demo.py. Run that script first to generate acme_inventory.db, then connect to it for the exercises below.

Each tier builds on the previous. Work through them in order unless your instructor specifies otherwise.

Tier 1 — Foundations (Connect, Create, Insert)

Exercise 1.1 — First Connection

Write a Python script that: 1. Connects to acme_inventory.db using sqlite3 2. Prints the number of rows in the products table 3. Prints the number of rows in the customers table 4. Closes the connection

Expected output format:

Products: 18
Customers: 8

Exercise 1.2 — Create a Suppliers Table

Add a suppliers table to acme_inventory.db with the following columns: - id — integer, primary key, autoincrement - name — text, not null, unique - contact_email — text, not null - phone — text - city — text - state — text

Then insert three rows of sample data for fictional suppliers. Verify by printing all rows with SELECT * FROM suppliers.

Exercise 1.3 — Safe Insert Practice

Write a function add_product(conn, sku, name, category, unit_price, quantity) that: 1. Uses a parameterized INSERT to add a product 2. Returns the new product's auto-generated ID (cursor.lastrowid) 3. Raises a descriptive error if the SKU already exists 4. Includes a docstring explaining the parameters

Test it by inserting one new product and printing the returned ID.

Exercise 1.4 — Row Factory Exploration

Connect to the database without setting row_factory. Then connect again with conn.row_factory = sqlite3.Row.

Run the same query on both connections:

SELECT id, sku, name FROM products LIMIT 3

Print the results from each connection. Observe and comment in your script on the difference between accessing columns by index vs. by name.

Exercise 1.5 — fetchone, fetchall, fetchmany

Write a script that demonstrates all three fetch methods on the products table: 1. Use fetchone() to get the most expensive product 2. Use fetchall() to get all products in the "Supplies" category 3. Use fetchmany(3) to get the first 3 products ordered alphabetically by name

Print a label before each result so the output is clear.

Tier 2 — Core SQL

Exercise 2.1 — WHERE and ORDER BY

Write SQL queries (executed through Python) to answer each question. Print the results using pd.read_sql().

a. Which products have a unit price greater than $50? b. Which products are in the "Electronics" category, sorted by price descending? c. Which products have fewer than 20 units in stock? d. Which products have a name that starts with "Widget"? (Use LIKE)

Exercise 2.2 — Aggregate Functions

Write queries to answer:

a. What is the average unit price across all products? b. What is the total number of units across all products (sum of quantity)? c. What is the most expensive product? Show SKU, name, and price. d. How many products are there in each category? (Use GROUP BY) e. What is the total inventory value (unit_price × quantity) per category, sorted highest to lowest?

Exercise 2.3 — HAVING Clause

Write a query that shows only product categories where: - There are at least 3 products in the category, AND - The average unit price is above $20

Show category, product count, and average price.

Exercise 2.4 — INNER JOIN

Write a query that returns: - Customer name - Order ID - Order date - Order status - Order total

For all orders placed in the last 30 days. Sort by order date descending.

Hint: Use strftime('%Y-%m-%d', order_date) >= date('now', '-30 days') in SQLite.

Exercise 2.5 — LEFT JOIN and Missing Data

Write a query to find all customers who have not placed any orders. Show customer name, email, city, and state.

Use a LEFT JOIN with a WHERE orders.id IS NULL condition.

Exercise 2.6 — Three-Table Join

Write a query that returns one row per order line, showing: - Customer name - Order date - Product name - Quantity ordered - Unit price at time of order - Line total (quantity × unit_price)

Sort by customer name, then order date.

Tier 3 — Data Modification and Transactions

Exercise 3.1 — UPDATE with Verification

Write a function update_price(conn, sku, new_price) that: 1. Updates the unit price for the given SKU 2. Verifies the update was successful using cursor.rowcount 3. Returns True if the update succeeded, False if the SKU was not found 4. Uses a parameterized query

Test it with a valid SKU and an invalid SKU ("SKU-NOTEXIST").

Exercise 3.2 — Transaction Rollback

Simulate a transaction that should fail. Write code that: 1. Begins a transaction 2. Updates the quantity for two products (reduce by 5 each) 3. Attempts to insert an order line referencing a product_id that does not exist 4. Handles the exception by rolling back the transaction 5. Verifies the product quantities were NOT changed (the rollback worked)

Exercise 3.3 — Bulk Insert with executemany

Write a function that reads a CSV file with columns sku, name, category, unit_price, quantity and inserts all rows into the products table using executemany().

Create a small test CSV file (5 rows) and run the function. Use INSERT OR IGNORE so duplicate SKUs are skipped. Print how many rows were inserted vs. how many were skipped.

Exercise 3.4 — DELETE with Confirmation

Write a function delete_product(conn, sku) that: 1. Checks if the product has any associated order lines before deleting 2. If it does, raises an error with a message explaining why it cannot be deleted 3. If it does not, deletes the product and returns True 4. Prompts for confirmation before deleting (use input())

Tier 4 — SQLAlchemy ORM

Exercise 4.1 — Define a Model

Using SQLAlchemy, define a Supplier model that maps to a suppliers table with: - id (Integer, primary key, autoincrement) - name (String, not null, unique) - contact_email (String, not null) - phone (String, optional) - city (String, optional) - products (relationship to Product — one supplier has many products)

You will need to add a supplier_id foreign key column to the Product model.

Create the tables, insert two suppliers, and query all suppliers using the ORM.

Exercise 4.2 — ORM CRUD

Using the SQLAlchemy models from sqlalchemy_demo.py:

a. Add a new product using session.add() and commit it. b. Load that product by its SKU using session.query().filter().first(). c. Update its quantity and unit_price. Commit. d. Verify the changes by querying again. e. Delete the product using session.delete(). Commit. f. Verify it is gone.

Exercise 4.3 — ORM Relationships

Using the Order and Customer models: 1. Create a new customer 2. Create two orders for that customer using the customer relationship attribute (not customer_id) 3. For each order, add at least two OrderLine objects 4. Commit everything 5. Load the customer back and navigate to their orders and line items using Python attribute access (customer.orders, order.order_lines, line.product)

Exercise 4.4 — ORM vs Raw SQL Comparison

Write the same query two ways:

Query: Total revenue per customer (sum of order totals for completed orders), top 5.

Version A: Using SQLAlchemy ORM query with session.query(), func.sum(), and group_by(). Version B: Using pd.read_sql() with a raw SQL string.

Compare the two approaches: which is more readable? Which was easier to write? Add a comment block in your script sharing your perspective.

Tier 5 — Real-World Challenges

Exercise 5.1 — Data Integrity Audit

Write a comprehensive data integrity check function that queries acme_inventory.db and reports: 1. Products with negative quantity 2. Orders with a total_amount of 0 but that have order lines 3. Orders where the stored total_amount does not match the sum of the line items (within $0.01) 4. Customers with no contact information (no email AND no phone)

Output a report as a pandas DataFrame with columns: issue_type, affected_id, description, detail.

Exercise 5.2 — Inventory Turnover Report

Write a query and Python function that calculates inventory turnover for each product category:

Inventory Turnover = Cost of Goods Sold / Average Inventory Value

For simplicity, use: - "Cost of Goods Sold" = sum of (quantity × unit_price) across all completed order lines - "Average Inventory Value" = current (quantity × unit_price)

Products with higher turnover are selling quickly relative to inventory held.

Export the result to both a printed table and a CSV file named inventory_turnover.csv.

Exercise 5.3 — Database Migration Script

Write a script that migrates the Acme Corp SQLite database to a new schema version. The migration should:

  1. Add a discount_percent column to products (default 0.0)
  2. Add a loyalty_tier column to customers (TEXT, default 'standard')
  3. Calculate loyalty tier based on total order value: - 'standard' — less than $500 total - 'silver' — $500 to $2,000 - 'gold' — more than $2,000
  4. Update all existing customers with their calculated tier

Wrap the entire migration in a transaction. Add a schema_version table that records each migration that has been applied, with a timestamp.

Exercise 5.4 — Python CLI for the Database

Build a command-line interface for the Acme Corp inventory database using Python's argparse module:

python inventory_cli.py list-products [--category CATEGORY] [--low-stock]
python inventory_cli.py add-product --sku SKU --name NAME --category CAT --price PRICE --qty QTY
python inventory_cli.py search SEARCH_TERM
python inventory_cli.py report --type {inventory|customers|revenue}

Requirements: - All database operations must use parameterized queries - list-products --low-stock shows only products at or below reorder point - search TERM searches product names and SKUs (use LIKE) - report --type inventory exports a CSV summary

Exercise 5.5 — PostgreSQL Connection Swap (Conceptual)

This exercise does not require a running PostgreSQL server. Instead:

  1. Take your sqlite_demo.py script and create a copy called postgres_ready.py
  2. Refactor it to use SQLAlchemy instead of sqlite3 directly
  3. Use python-dotenv to load the connection string from a .env file
  4. Add a --db command-line argument that accepts sqlite or postgres
  5. When --db sqlite is passed, use sqlite:///acme_inventory.db
  6. When --db postgres is passed, build the connection string from .env variables

Write a short comment block explaining what additional steps (driver installation, server setup, credential management) would be needed to actually connect to PostgreSQL.

Bonus Challenge: Build Something Real

Choose one of the following and build it from scratch:

Option A — Personal Finance Tracker Tables: accounts, transactions, categories, budgets. Write queries for: monthly spending by category, accounts over budget, net worth calculation.

Option B — Event Management System Tables: events, attendees, registrations, venues. Write queries for: upcoming events, sold-out events, attendee lists, revenue per event.

Option C — Job Application Tracker Tables: companies, applications, contacts, interviews, notes. Write queries for: applications by status, response rate by source, interview success rate.

For your chosen option, include: schema design document (comments in code), all CRUD operations, at least 5 analytical queries, and a data export function.