MySQL Projects
Four projects that exercise the full course.
Concept
Four projects that exercise the full course. Each lists requirements, schema, relationships, sample data, queries, reports, and advanced challenges. Build each in its own database with scripts in version control (01_schema.sql, 02_seed.sql, 03_queries.sql, 04_routines.sql, README.md).
Why It Matters
Projects turn isolated knowledge into working systems and give you concrete examples for interviews.
Project 1 — Library Management System
Requirements: books with multiple authors, physical copies with barcodes, members, loans with due dates, one open loan per copy, fines for overdue returns, reports on overdue loans, popular books, and member activity.
Relationships: books M:N authors (book_authors); books 1:N copies; members 1:N loans; copies 1:N loans.
Queries and reports (as of 2026-09-30):
Advanced challenges: a return_book(barcode) procedure that closes the loan and records a fine; a full-text search on titles; an event that emails (via an outbox table) reminders two days before due dates; member borrowing statistics with window functions.
CREATE DATABASE lib CHARACTER SET utf8mb4;
USE lib;
CREATE TABLE books (book_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, isbn CHAR(13) NOT NULL UNIQUE, title VARCHAR(200) NOT NULL);
CREATE TABLE authors (author_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE book_authors (book_id INT UNSIGNED, author_id INT UNSIGNED, PRIMARY KEY (book_id, author_id),
FOREIGN KEY (book_id) REFERENCES books (book_id), FOREIGN KEY (author_id) REFERENCES authors (author_id));
CREATE TABLE copies (copy_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, book_id INT UNSIGNED NOT NULL, barcode VARCHAR(20) NOT NULL UNIQUE,
FOREIGN KEY (book_id) REFERENCES books (book_id));
CREATE TABLE members (member_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(120) UNIQUE);
CREATE TABLE loans (
loan_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
copy_id INT UNSIGNED NOT NULL,
member_id INT UNSIGNED NOT NULL,
loaned_on DATE NOT NULL,
due_on DATE NOT NULL,
returned_on DATE NULL,
open_copy_id INT UNSIGNED GENERATED ALWAYS AS (IF(returned_on IS NULL, copy_id, NULL)) VIRTUAL,
UNIQUE KEY uq_one_open_
…-- Overdue loans with fine of 5 per day
SELECT m.name, b.title, l.due_on,
DATEDIFF('2026-09-30', l.due_on) AS days_late,
DATEDIFF('2026-09-30', l.due_on) * 5 AS fine
FROM loans l
JOIN copies c ON c.copy_id = l.copy_id
JOIN books b ON b.book_id = c.book_id
JOIN members m ON m.member_id = l.member_id
WHERE l.returned_on IS NULL AND l.due_on < '2026-09-30';
-- Ravi | Automotive Embedded Systems | 2026-09-15 | 15 | 75
-- Availability per title
SELECT b.title, COUNT(c.copy_id) AS copies, COUNT(c.copy_id) - COUNT(l.loan_id) AS available
FROM books b
JOIN copies c ON c.book_id = b.book_id
LEFT JOIN loans l ON l.copy_id = c.copy_id AND l.returned_on IS NULL
GROUP BY b.book_id;
-- Automotive Embedded Systems: 2 copies, 0 available; Practical SQL: 1 copy, 1 available
-- Books with author lists
SELECT b.title, GROUP_CONCAT(a.name ORDER BY a.name SEPARATOR ', ') AS authors
FROM books b JOIN book_authors ba ON ba.book_id = b.book_id JOIN authors a ON a.author_id = ba.author_id
GROUP BY b.book_id;
-- Trying to lend copy 1 again while it is out:
INSERT INTO loans (copy_id, member_id, loaned_on, due_on) VALUES (1, 2, '2026-09-30', '2026-10-14');
-- ERROR 1062: Duplicate en
…Project 2 — E-Commerce Order and Inventory System
Requirements: customers, addresses, hierarchical categories, products with JSON attributes, inventory per warehouse, orders with items and payments, idempotent payment webhooks, reports on revenue, top products, low stock, and customer cohorts.
Advanced challenges: a checkout procedure with FOR UPDATE on inventory rows in a consistent order; a multi-valued JSON index on product tags; monthly cohort retention; partitioning orders by year; replicas for reporting queries.
CREATE DATABASE shop CHARACTER SET utf8mb4;
USE shop;
CREATE TABLE categories (category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL,
parent_id INT UNSIGNED NULL, FOREIGN KEY (parent_id) REFERENCES categories (category_id));
CREATE TABLE products (product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(30) NOT NULL UNIQUE,
name VARCHAR(200) NOT NULL, category_id INT UNSIGNED NOT NULL, price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
attrs JSON NULL, FOREIGN KEY (category_id) REFERENCES categories (category_id));
CREATE TABLE warehouses (warehouse_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(10) NOT NULL UNIQUE);
CREATE TABLE inventory (product_id INT UNSIGNED, warehouse_id SMALLINT UNSIGNED, on_hand INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (product_id, warehouse_id),
FOREIGN KEY (product_id) REFERENCES products (product_id), FOREIGN KEY (warehouse_id) REFERENCES warehouses (warehouse_id));
CREATE TABLE customers (customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL UNIQUE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE orders (order_id BIGINT UNSIGNED AU
…-- Idempotent webhook insert: a retried notification does not create a second payment
INSERT INTO payments (order_id, provider_ref, amount, status, paid_at)
VALUES (1, 'PAY-1001', 78800, 'SUCCESS', '2026-03-01 10:01')
ON DUPLICATE KEY UPDATE payment_id = payment_id; -- no-op on duplicate
-- Revenue by top-level category (recursive CTE over the category tree)
WITH RECURSIVE tree AS (
SELECT category_id, category_id AS root_id FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, t.root_id FROM categories c JOIN tree t ON c.parent_id = t.category_id
)
SELECT r.name AS top_category, SUM(oi.qty * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id AND o.status <> 'CANCELLED'
JOIN products p ON p.product_id = oi.product_id
JOIN tree t ON t.category_id = p.category_id
JOIN categories r ON r.category_id = t.root_id
GROUP BY r.name;
-- Electronics | 86800.00
-- Stock across warehouses and products with 16 GB RAM
SELECT p.sku, SUM(i.on_hand) AS total_stock
FROM products p JOIN inventory i ON i.product_id = p.product_id
WHERE p.attrs->>'$.ram_gb' = '16'
GROUP BY p.sku;
-- LAP-15 | 7
-- Reserve stock ato
…Project 3 — IoT / Vehicle Telemetry Store
Requirements: devices (gateways in vehicles) send sensor readings every second; keep 90 days of raw data, hourly aggregates forever; fast queries for "last value per sensor", hourly charts, and threshold alerts.
Advanced challenges: an event that rotates monthly partitions and drops those older than 90 days; gaps-and-islands to find continuous over-temperature periods; ingest 1 million rows with LOAD DATA and compare with batched inserts; a read replica for dashboards.
CREATE DATABASE telemetry CHARACTER SET utf8mb4;
USE telemetry;
CREATE TABLE devices (device_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, vin CHAR(17) NOT NULL UNIQUE,
firmware VARCHAR(20) NOT NULL, last_seen_at DATETIME(3) NULL);
CREATE TABLE sensors (sensor_id SMALLINT UNSIGNED PRIMARY KEY, name VARCHAR(40) NOT NULL UNIQUE, unit VARCHAR(10) NOT NULL,
alert_max DOUBLE NULL);
CREATE TABLE readings (
device_id INT UNSIGNED NOT NULL,
sensor_id SMALLINT UNSIGNED NOT NULL,
ts DATETIME(3) NOT NULL,
value DOUBLE NOT NULL,
PRIMARY KEY (device_id, sensor_id, ts)
)
PARTITION BY RANGE COLUMNS (ts) (
PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE readings_hourly (
device_id INT UNSIGNED NOT NULL, sensor_id SMALLINT UNSIGNED NOT NULL, hour_start DATETIME NOT NULL,
n INT UNSIGNED NOT NULL, min_v DOUBLE NOT NULL, avg_v DOUBLE NOT NULL, max_v DOUBLE NOT NULL,
PRIMARY KEY (device_id, sensor_id, hour_start)
);
INSERT INTO devices (vin, firmware) VALUES ('EHBAUREV250000001', 'GW-3.1.0');
IN
…-- Last value per sensor for a device (uses the PK order; LATERAL + LIMIT 1)
SELECT s.name, lv.ts, lv.value, s.unit
FROM sensors s
JOIN LATERAL (
SELECT r.ts, r.value FROM readings r
WHERE r.device_id = 1 AND r.sensor_id = s.sensor_id
ORDER BY r.ts DESC LIMIT 1
) AS lv ON TRUE;
-- coolant_temp | 2026-09-29 11:05:00.000 | 95 | degC
-- pack_voltage | 2026-09-29 11:05:00.000 | 396.1 | V
-- Hourly rollup (run by an event every hour; idempotent via upsert)
-- (Row aliases like "AS new" are allowed only with VALUES/SET; for INSERT ... SELECT, alias a derived table.)
INSERT INTO readings_hourly (device_id, sensor_id, hour_start, n, min_v, avg_v, max_v)
SELECT * FROM (
SELECT device_id, sensor_id, DATE_FORMAT(ts, '%Y-%m-%d %H:00:00') AS hour_start,
COUNT(*) AS n, MIN(value) AS min_v, AVG(value) AS avg_v, MAX(value) AS max_v
FROM readings
WHERE ts >= '2026-09-29 10:00:00' AND ts < '2026-09-29 11:00:00'
GROUP BY device_id, sensor_id, DATE_FORMAT(ts, '%Y-%m-%d %H:00:00')
) AS new
ON DUPLICATE KEY UPDATE n = new.n, min_v = new.min_v, avg_v = new.avg_v, max_v = new.max_v;
-- coolant_temp 10:00 hour: n=3, min 88, avg 95.23…, max 107.2; pack_voltage: n=1
…Project 4 — Automotive Test Management Database
Requirements: requirements, test cases linked to requirements (M:N), test benches, test campaigns per software release, test executions with results and logs (JSON), defects linked to failed executions, and traceability reports (requirement coverage, pass rate per release, open defects per ECU).
Advanced challenges: a trigger that creates a defect stub automatically when an ASIL C/D test fails; a procedure that clones a campaign's test plan to a new release; role-based access (testers insert executions, managers read reports); a JSON schema CHECK for details.
CREATE DATABASE testmgmt CHARACTER SET utf8mb4;
USE testmgmt;
CREATE TABLE requirements (req_id VARCHAR(20) PRIMARY KEY, title VARCHAR(200) NOT NULL, ecu_code VARCHAR(10) NOT NULL,
asil ENUM('QM', 'A', 'B', 'C', 'D') NOT NULL);
CREATE TABLE test_cases (tc_id VARCHAR(20) PRIMARY KEY, title VARCHAR(200) NOT NULL, level ENUM('HIL','VEHICLE') NOT NULL);
CREATE TABLE tc_requirements (tc_id VARCHAR(20), req_id VARCHAR(20), PRIMARY KEY (tc_id, req_id), KEY ix_tcr_req (req_id),
FOREIGN KEY (tc_id) REFERENCES test_cases (tc_id), FOREIGN KEY (req_id) REFERENCES requirements (req_id));
CREATE TABLE campaigns (campaign_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, release_label VARCHAR(20) NOT NULL,
started_on DATE NOT NULL);
CREATE TABLE executions (exec_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, campaign_id INT UNSIGNED NOT NULL,
tc_id VARCHAR(20) NOT NULL, bench VARCHAR(20) NOT NULL, executed_at DATETIME NOT NULL,
result ENUM('PASS','FAIL','BLOCKED') NOT NULL, details JSON NULL,
KEY ix_exec_campaign_tc (campaign_id, tc_id, executed_at),
FOREIGN KEY (campaign_id) REFERENCES campaigns (campaign_id), FOREIGN KEY (tc_id) REFERENCES test_cases (tc_id));
CREATE TABL
…-- Requirement coverage: requirements without any linked test case
SELECT r.req_id, r.title, r.asil
FROM requirements r
WHERE NOT EXISTS (SELECT 1 FROM tc_requirements t WHERE t.req_id = r.req_id);
-- REQ-BMS-002 | Report SOC with 1% resolution | QM
-- Latest result per test case per campaign (re-tests supersede earlier runs)
WITH latest AS (
SELECT e.*, ROW_NUMBER() OVER (PARTITION BY campaign_id, tc_id ORDER BY executed_at DESC) AS rn
FROM executions e
)
SELECT c.release_label, l.tc_id, l.result, l.details->>'$.peak_torque_nm' AS peak_torque, l.details->>'$.max_cell_v' AS max_cell_v
FROM latest l JOIN campaigns c ON c.campaign_id = l.campaign_id
WHERE l.rn = 1
ORDER BY c.release_label, l.tc_id;+---------------+------------+--------+-------------+------------+
| release_label | tc_id | result | peak_torque | max_cell_v |
+---------------+------------+--------+-------------+------------+
| R2026.1 | TC-BMS-010 | PASS | NULL | 4.21 |
| R2026.1 | TC-VCU-002 | PASS | 248 | NULL |
| R2026.2 | TC-BMS-010 | FAIL | NULL | 4.31 |
+---------------+------------+--------+-------------+------------+-- Requirement status per release (traceability): FAIL if any linked test's latest result failed
WITH latest AS (
SELECT campaign_id, tc_id, result,
ROW_NUMBER() OVER (PARTITION BY campaign_id, tc_id ORDER BY executed_at DESC) AS rn
FROM executions
)
SELECT c.release_label, tr.req_id,
CASE WHEN SUM(l.result = 'FAIL') > 0 THEN 'FAIL'
WHEN SUM(l.result = 'PASS') = COUNT(*) THEN 'PASS'
ELSE 'INCOMPLETE' END AS req_status
FROM latest l
JOIN campaigns c ON c.campaign_id = l.campaign_id
JOIN tc_requirements tr ON tr.tc_id = l.tc_id
WHERE l.rn = 1
GROUP BY c.release_label, tr.req_id
ORDER BY c.release_label, tr.req_id;
-- R2026.1 REQ-BMS-001 PASS | R2026.1 REQ-VCU-010 PASS | R2026.2 REQ-BMS-001 FAIL
-- Open defects per ECU
SELECT r.ecu_code, COUNT(DISTINCT d.defect_id) AS open_defects
FROM defects d
JOIN executions e ON e.exec_id = d.exec_id
JOIN tc_requirements tr ON tr.tc_id = e.tc_id
JOIN requirements r ON r.req_id = tr.req_id
WHERE d.status = 'OPEN'
GROUP BY r.ecu_code;
-- BMS | 1Expected Result
Four working databases with verified query outputs, stored programs, and documented design decisions.
Common Mistakes
Skipping constraints to "move faster".
Not testing with edge cases (returned loans, retried webhooks, re-tests).
No indexes on time-series access paths.
Best Practices
Write expected outputs next to each query in your repository and re-check after changes.
Automate schema creation and seeding so the project can be rebuilt in seconds.
Interview Relevance
Projects provide concrete answers to "Tell me about a database you designed", including trade-offs (generated-column uniqueness, idempotency keys, partitioning).
Practice Questions
Add a reservations table to Project 1 and prevent reserving an available copy.
Add a returns flow to Project 2 that restocks inventory atomically.
Add daily aggregates to Project 3 built from hourly aggregates.
Add requirement-coverage percentages per ASIL level to Project 4.