SQL Projects
Projects combine design, DDL, data loading, querying, reporting, and optimization into a complete deliverable.
Concept
Projects combine design, DDL, data loading, querying, reporting, and optimization into a complete deliverable. Each project below includes requirements, schema, relationships, sample data, SQL queries, reports, and advanced challenges. Schemas use PostgreSQL syntax; adapt identity columns and date functions for other DBMSs (Module 47).
Why It Matters
A portfolio of working database projects demonstrates practical skill far better than isolated queries. Put each project in a Git repository with a schema.sql, seed.sql, queries.sql, and a README explaining design decisions.
Syntax
Suggested project structure:
project-name/
├── README.md -- requirements, ER diagram, design decisions
├── 01_schema.sql -- CREATE TABLE, constraints
├── 02_indexes.sql -- indexes
├── 03_seed.sql -- sample data
├── 04_views.sql -- reporting views
├── 05_queries.sql -- business queries and reports
└── 06_challenges.sql -- advanced tasks (procedures, triggers, optimization)Project 1 — Student Management Database
Store students, departments, courses, instructors, semesters, enrollments, and grades.
Each student belongs to one department; each course is offered by one department.
A course can be offered in many semesters, each offering taught by one instructor with a seat limit.
Students enroll in course offerings; a student cannot enroll twice in the same offering.
Grades are recorded per enrollment as letter grades mapped to grade points.
Reports: transcripts, SGPA/CGPA, course pass rates, instructor load, seat availability.
Sample data SQL queries Reports (expected output for Q2 and Q4)
Q2 — Ravi: (10×4 + 8×4 + 6×3) / 11 = 90 / 11 = 8.18; Meena: (8×4 + 0×4) / 8 = 4.00; Farhan: 10.00.
Advanced challenges Write a trigger or procedure that rejects an enrollment when the offering is full (handle concurrency with SELECT... FOR UPDATE on the offering).
Add prerequisites (sp_course_prerequisites) and a query that lists students eligible for CS102.
Compute CGPA across semesters and rank students within each department.
Create a materialized view for department-level academic statistics.
Implement grade-change auditing with a trigger.
departments 1──N students
departments 1──N courses
departments 1──N instructors
courses 1──N course_offerings N──1 semesters
instructors 1──N course_offerings
students M──N course_offerings (via enrollments)
grade_scale 1──N enrollments (letter grade lookup)CREATE TABLE sp_departments (
dept_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code VARCHAR(10) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL
);
CREATE TABLE sp_students (
student_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
roll_no VARCHAR(20) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(120) NOT NULL UNIQUE,
dept_id INT NOT NULL REFERENCES sp_departments(dept_id),
admit_year SMALLINT NOT NULL CHECK (admit_year BETWEEN 2000 AND 2100)
);
CREATE TABLE sp_instructors (
instructor_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
dept_id INT NOT NULL REFERENCES sp_departments(dept_id)
);
CREATE TABLE sp_courses (
course_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code VARCHAR(10) NOT NULL UNIQUE,
title VARCHAR(100) NOT NULL,
credits SMALLINT NOT NULL CHECK (credits BETWEEN 1 AND 6),
dept_id INT NOT NULL REFERENCES sp_departments(dept_id)
);
CREATE TABLE sp_semesters (
semester_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
label VARCHAR(10) NOT NULL UNIQUE, --
…INSERT INTO sp_departments (code, name) VALUES ('CSE', 'Computer Science'), ('ECE', 'Electronics');
INSERT INTO sp_students (roll_no, full_name, email, dept_id, admit_year) VALUES
('CSE-001', 'Ravi Kumar', '[email protected]', 1, 2025),
('CSE-002', 'Meena Iyer', '[email protected]', 1, 2025),
('ECE-001', 'Farhan Ali', '[email protected]', 2, 2025);
INSERT INTO sp_instructors (full_name, dept_id) VALUES ('Dr. Rao', 1), ('Dr. Sen', 2);
INSERT INTO sp_courses (code, title, credits, dept_id) VALUES
('CS101', 'Databases', 4, 1), ('CS102', 'Data Structures', 4, 1), ('EC101', 'Digital Electronics', 3, 2);
INSERT INTO sp_semesters (label, start_date, end_date) VALUES ('2026-S1', DATE '2026-01-05', DATE '2026-05-15');
INSERT INTO sp_course_offerings (course_id, semester_id, instructor_id, seat_limit) VALUES
(1, 1, 1, 60), (2, 1, 1, 60), (3, 1, 2, 40);
INSERT INTO sp_grade_scale VALUES ('A', 10, TRUE), ('B', 8, TRUE), ('C', 6, TRUE), ('D', 4, TRUE), ('F', 0, FALSE);
INSERT INTO sp_enrollments (student_id, offering_id, letter) VALUES
(1, 1, 'A'), (1, 2, 'B'), (1, 3, 'C'),
(2, 1, 'B'), (2, 2, 'F'),
(3, 3, 'A');-- Q1. Transcript of a student
SELECT s.roll_no, sem.label, c.code, c.title, c.credits, e.letter, g.grade_point
FROM sp_enrollments e
JOIN sp_students s ON s.student_id = e.student_id
JOIN sp_course_offerings o ON o.offering_id = e.offering_id
JOIN sp_courses c ON c.course_id = o.course_id
JOIN sp_semesters sem ON sem.semester_id = o.semester_id
LEFT JOIN sp_grade_scale g ON g.letter = e.letter
WHERE s.roll_no = 'CSE-001'
ORDER BY sem.start_date, c.code;
-- Q2. SGPA per student per semester (credit-weighted)
SELECT s.roll_no, sem.label,
ROUND(SUM(g.grade_point * c.credits) / SUM(c.credits), 2) AS sgpa,
SUM(c.credits) FILTER (WHERE g.is_pass) AS credits_earned
FROM sp_enrollments e
JOIN sp_students s ON s.student_id = e.student_id
JOIN sp_course_offerings o ON o.offering_id = e.offering_id
JOIN sp_courses c ON c.course_id = o.course_id
JOIN sp_semesters sem ON sem.semester_id = o.semester_id
JOIN sp_grade_scale g ON g.letter = e.letter
GROUP BY s.roll_no, sem.label
ORDER BY s.roll_no;
-- Q3. Seat availability per offering
SELECT c.code, sem.label, o.seat_limit, COUNT(e.student_id) AS enrolled,
o.seat_limit
…Project 2 — Employee Management System
Employees, departments, job positions, managers (self-reference), salary history, attendance, leave, and performance reviews.
Salary changes must keep history with effective dates.
Reports: org chart, payroll by department, attendance summary, leave balances, attrition, top performers.
Sample data SQL queries Reports (expected output for Q2 and Q3, as of 2026-09-29)
Q3 (Neha: 5 annual days + 2 sick days; Omar's request is pending and not counted):
(Q5 returns one row per salary-history row; add DISTINCT or wrap it to get one row per employee.) Advanced challenges
Prevent overlapping leave requests for the same employee (trigger, or PostgreSQL exclusion constraint on a daterange).
Calculate working hours per week from attendance and flag employees exceeding 48 hours.
Write a stored procedure give_raise(emp_id, percent) that inserts a new salary-history row effective the first day of next month and validates limits.
Compute annual attrition rate = leavers / average headcount per department.
Add row-level security so managers see only their subtree of employees.
departments 1──N employees N──1 positions
employees 1──N employees (manager_id)
employees 1──N salary_history
employees 1──N attendance
employees 1──N leave_requests N──1 leave_types
employees 1──N performance_reviews (as employee and as reviewer)CREATE TABLE ems_departments (dept_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE);
CREATE TABLE ems_positions (position_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title VARCHAR(60) NOT NULL UNIQUE, grade CHAR(2) NOT NULL);
CREATE TABLE ems_employees (
emp_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
emp_code VARCHAR(10) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(120) NOT NULL UNIQUE,
dept_id INT NOT NULL REFERENCES ems_departments,
position_id INT NOT NULL REFERENCES ems_positions,
manager_id INT REFERENCES ems_employees(emp_id),
hire_date DATE NOT NULL,
exit_date DATE,
CHECK (exit_date IS NULL OR exit_date >= hire_date),
CHECK (manager_id <> emp_id)
);
CREATE TABLE ems_salary_history (
emp_id INT NOT NULL REFERENCES ems_employees,
effective_from DATE NOT NULL,
monthly_salary DECIMAL(12,2) NOT NULL CHECK (monthly_salary > 0),
reason VARCHAR(30) NOT NULL,
PRIMARY KEY (emp_id, effective_from)
);
CREATE TABLE ems_attendance (
emp_id INT
…INSERT INTO ems_departments (name) VALUES ('Engineering'), ('Finance');
INSERT INTO ems_positions (title, grade) VALUES ('Director', 'D1'), ('Lead Engineer', 'L3'), ('Engineer', 'L1'), ('Accountant', 'L2');
INSERT INTO ems_employees (emp_code, full_name, email, dept_id, position_id, manager_id, hire_date) VALUES
('E001', 'Asha Rao', '[email protected]', 1, 1, NULL, DATE '2016-01-04'),
('E002', 'Vikram Shah', '[email protected]', 1, 2, 1, DATE '2019-03-11'),
('E003', 'Neha Iyer', '[email protected]', 1, 3, 2, DATE '2022-07-01'),
('E004', 'Omar Khan', '[email protected]', 2, 4, 1, DATE '2021-02-15');
INSERT INTO ems_salary_history VALUES
(1, DATE '2016-01-04', 300000, 'HIRE'), (1, DATE '2025-04-01', 350000, 'APPRAISAL'),
(2, DATE '2019-03-11', 120000, 'HIRE'), (2, DATE '2025-04-01', 150000, 'PROMOTION'),
(3, DATE '2022-07-01', 70000, 'HIRE'), (3, DATE '2025-04-01', 80000, 'APPRAISAL'),
(4, DATE '2021-02-15', 90000, 'HIRE');
INSERT INTO ems_leave_types VALUES ('ANNUAL', 20), ('SICK', 10);
INSERT INTO ems_leave_requests (emp_id, leave_type, start_date, end_date, status) VALUES
(3, 'ANNUAL', DATE '2026-02-02', DATE '2026-02-06', 'APPROVED'),
(3, 'SICK', DATE '2026-03-10', D
…-- Q1. Current salary (salary effective on a given date)
CREATE VIEW ems_v_current_salary AS
SELECT emp_id, monthly_salary
FROM (SELECT sh.*, ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_from DESC) AS rn
FROM ems_salary_history sh
WHERE effective_from <= CURRENT_DATE) t
WHERE rn = 1;
-- Q2. Monthly payroll per department
SELECT d.name, COUNT(*) AS headcount, SUM(cs.monthly_salary) AS monthly_payroll
FROM ems_employees e
JOIN ems_departments d ON d.dept_id = e.dept_id
JOIN ems_v_current_salary cs ON cs.emp_id = e.emp_id
WHERE e.exit_date IS NULL
GROUP BY d.name
ORDER BY monthly_payroll DESC;
-- Q3. Leave balance per employee per leave type for 2026
SELECT e.full_name, lt.leave_type, lt.annual_quota,
COALESCE(SUM(lr.end_date - lr.start_date + 1), 0) AS used,
lt.annual_quota - COALESCE(SUM(lr.end_date - lr.start_date + 1), 0) AS balance
FROM ems_employees e
CROSS JOIN ems_leave_types lt
LEFT JOIN ems_leave_requests lr
ON lr.emp_id = e.emp_id AND lr.leave_type = lt.leave_type
AND lr.status = 'APPROVED' AND lr.start_date >= DATE '2026-01-01' AND lr.start_date < DATE '2027-01-01'
GROUP BY e.emp_id, e.full_name, lt.leave_type, lt
…Project 3 — E-Commerce Database
Customers with multiple addresses; products with categories (hierarchical) and inventory; carts; orders with items, payments, and shipments; product reviews; coupons.
Prices on order items must be captured at purchase time.
An order has one payment attempt history and one or more shipments.
Reports: sales by category, top customers, conversion funnel, repeat purchase, average order value, inventory alerts, review ratings.
Sample data SQL queries Reports (expected output)
Q2: 3 orders, total 89800 → avg_order_value 29933.33; 2 customers → orders_per_customer 1.50.
Q4 returns no rows (all orders fully paid). Q5 returns Desk Lamp (5).
Advanced challenges Write a checkout procedure that validates stock with row locks, creates the order and items, applies a coupon, and decrements stock atomically.
Build a conversion funnel from an ecp_events table (view → add_to_cart → checkout → paid) with counts per step.
Monthly cohort retention report (Module 43, Problem 50) for this schema.
"Customers who bought X also bought Y" recommendations with a self join on order items.
Partition ecp_orders by month and compare query plans for a date-range report.
customers 1──N addresses
customers 1──N orders 1──N order_items N──1 products N──1 categories (self-referencing parent)
orders 1──N payments
orders 1──N shipments
customers 1──N reviews N──1 products
coupons 1──N ordersCREATE TABLE ecp_categories (category_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(60) NOT NULL, parent_id INT REFERENCES ecp_categories);
CREATE TABLE ecp_customers (customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(120) NOT NULL UNIQUE, full_name VARCHAR(100) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE ecp_addresses (address_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES ecp_customers,
line1 VARCHAR(200) NOT NULL, city VARCHAR(60) NOT NULL,
postal_code VARCHAR(10) NOT NULL, country CHAR(2) NOT NULL,
is_default BOOLEAN NOT NULL DEFAULT FALSE);
CREATE UNIQUE INDEX ux_ecp_one_default_address ON ecp_addresses (customer_id) WHERE is_default;
CREATE TABLE ecp_products (product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku VARCHAR(30) NOT NULL UNIQUE, name VARCHAR(200) NOT NULL,
category_id INT NOT NULL REFERE
…INSERT INTO ecp_categories (name, parent_id) VALUES ('Electronics', NULL), ('Computers', 1), ('Audio', 1), ('Home', NULL);
INSERT INTO ecp_customers (email, full_name) VALUES ('[email protected]', 'Ananya Gupta'), ('[email protected]', 'Ben Carter');
INSERT INTO ecp_addresses (customer_id, line1, city, postal_code, country, is_default) VALUES
(1, '12 MG Road', 'Mumbai', '400001', 'IN', TRUE), (2, '5 Baker St', 'London', 'NW16XE', 'GB', TRUE);
INSERT INTO ecp_products (sku, name, category_id, list_price, stock) VALUES
('LAP-15', '15-inch Laptop', 2, 75000, 10), ('HP-01', 'Headphones', 3, 4000, 50), ('LAMP-1', 'Desk Lamp', 4, 1500, 5);
INSERT INTO ecp_orders (customer_id, shipping_address_id, ordered_at, status) VALUES
(1, 1, TIMESTAMP '2026-03-01 10:00', 'DELIVERED'),
(2, 2, TIMESTAMP '2026-03-05 15:30', 'SHIPPED'),
(1, 1, TIMESTAMP '2026-03-20 09:15', 'PAID');
INSERT INTO ecp_order_items VALUES (1, 1, 1, 75000), (1, 2, 1, 3800), (2, 2, 2, 4000), (3, 3, 2, 1500);
INSERT INTO ecp_payments (order_id, amount, method, status, provider_ref, paid_at) VALUES
(1, 78800, 'CARD', 'SUCCESS', 'PAY-1001', TIMESTAMP '2026-03-01 10:01'),
(2, 8000, 'UPI', 'SUCCESS', 'PAY-1002', TIMESTAMP '2026-03-
…-- Q1. Revenue by top-level category (roll up child categories with a recursive CTE)
WITH RECURSIVE cat_root AS (
SELECT category_id, category_id AS root_id FROM ecp_categories WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, r.root_id FROM ecp_categories c JOIN cat_root r ON c.parent_id = r.category_id
)
SELECT root.name AS top_category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM ecp_order_items oi
JOIN ecp_orders o ON o.order_id = oi.order_id AND o.status NOT IN ('CANCELLED', 'RETURNED')
JOIN ecp_products p ON p.product_id = oi.product_id
JOIN cat_root cr ON cr.category_id = p.category_id
JOIN ecp_categories root ON root.category_id = cr.root_id
GROUP BY root.name
ORDER BY revenue DESC;
-- Q2. Average order value and orders per customer
SELECT COUNT(DISTINCT o.order_id) AS orders,
ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value,
ROUND(1.0 * COUNT(DISTINCT o.order_id) / COUNT(DISTINCT o.customer_id), 2) AS orders_per_customer
FROM ecp_orders o
JOIN ecp_order_items oi ON oi.order_id = o.order_id
WHERE o.status NOT IN ('CANCELLED', 'RETURNED');
-- Q3. Product rating summary
SELECT p.nam
…Project 4 — Banking Database
Customers (with KYC status), branches, accounts (savings/current/fixed deposit), joint account holders, transactions, transfers, and loans with EMI schedules.
Balances must never go negative for savings/current accounts; every transfer must be atomic and logged.
Reports: account statements with running balances, daily branch totals, suspicious activity, loan overdue report, interest calculation.
Sample data SQL queries Reports (expected output) After the transfer, Q2 for account 1:
(transfer time) Account balances: SB-1001 = 35000.00, SB-2001 = 35000.00. Q3 and Q4 return no rows.
Advanced challenges Generate the EMI schedule for a loan with a recursive CTE using EMI = P·r·(1+r)^n / ((1+r)^n − 1).
Month-end interest posting for savings accounts on the minimum balance between the 10th and last day of the month.
Detect accounts receiving more than 10 incoming transfers from distinct accounts within one hour.
Demonstrate a lost update with two concurrent sessions and prevent it with the version column.
Design roles: teller (deposits/withdrawals via procedures only), auditor (read-only), and branch manager (own branch via row-level security).
branches 1──N accounts
customers M──N accounts (via account_holders, with holder role)
accounts 1──N ledger_entries (double-entry style: each transfer = 1 debit + 1 credit)
transfers 1──2 ledger_entries
customers 1──N loans 1──N loan_installmentsCREATE TABLE bnk_branches (branch_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
ifsc CHAR(11) NOT NULL UNIQUE, city VARCHAR(50) NOT NULL);
CREATE TABLE bnk_customers (customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
kyc_status VARCHAR(10) NOT NULL CHECK (kyc_status IN ('PENDING','VERIFIED','REJECTED')));
CREATE TABLE bnk_accounts (account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
account_no VARCHAR(20) NOT NULL UNIQUE,
branch_id INT NOT NULL REFERENCES bnk_branches,
account_type VARCHAR(10) NOT NULL CHECK (account_type IN ('SAVINGS','CURRENT','FD')),
balance DECIMAL(15,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE',
opened_on DATE NOT NULL,
version INT NOT NULL DEFAULT 0); -- optimistic locking
CREATE TABLE bnk_account_holders (account_id BIGINT REFERENCES bnk_accounts, customer_id BIGINT REFERENCE
…INSERT INTO bnk_branches (ifsc, city) VALUES ('EHBK0000001', 'Mumbai'), ('EHBK0000002', 'Pune');
INSERT INTO bnk_customers (full_name, kyc_status) VALUES ('Ananya Gupta', 'VERIFIED'), ('Ben Carter', 'VERIFIED');
INSERT INTO bnk_accounts (account_no, branch_id, account_type, balance, opened_on) VALUES
('SB-1001', 1, 'SAVINGS', 0, DATE '2026-01-01'), ('SB-2001', 2, 'SAVINGS', 0, DATE '2026-01-01');
INSERT INTO bnk_account_holders VALUES (1, 1, 'PRIMARY'), (2, 2, 'PRIMARY');
-- Opening deposits (ledger + balance updated together)
INSERT INTO bnk_ledger_entries (account_id, entry_type, amount, description, posted_at) VALUES
(1, 'CREDIT', 50000, 'Cash deposit', TIMESTAMP '2026-01-02 10:00'),
(2, 'CREDIT', 20000, 'Cash deposit', TIMESTAMP '2026-01-02 11:00');
UPDATE bnk_accounts SET balance = 50000 WHERE account_id = 1;
UPDATE bnk_accounts SET balance = 20000 WHERE account_id = 2;-- Q1. Atomic, idempotent transfer (PostgreSQL procedure)
CREATE OR REPLACE PROCEDURE bnk_transfer(p_from BIGINT, p_to BIGINT, p_amount NUMERIC, p_key VARCHAR)
LANGUAGE plpgsql AS $$
DECLARE v_transfer_id BIGINT;
BEGIN
INSERT INTO bnk_transfers (from_account, to_account, amount, idempotency_key)
VALUES (p_from, p_to, p_amount, p_key)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING transfer_id INTO v_transfer_id;
IF v_transfer_id IS NULL THEN
RAISE NOTICE 'Duplicate request %, ignored', p_key;
RETURN;
END IF;
-- Lock both accounts in a consistent order to avoid deadlocks
PERFORM 1 FROM bnk_accounts WHERE account_id IN (p_from, p_to) ORDER BY account_id FOR UPDATE;
UPDATE bnk_accounts SET balance = balance - p_amount, version = version + 1
WHERE account_id = p_from AND status = 'ACTIVE' AND balance >= p_amount;
IF NOT FOUND THEN
RAISE EXCEPTION 'Insufficient funds or inactive account %', p_from;
END IF;
UPDATE bnk_accounts SET balance = balance + p_amount, version = version + 1
WHERE account_id = p_to AND status = 'ACTIVE';
IF NOT FOUND THEN
RAISE EXCEPTION 'Target account % not active'
…Project 5 — Hospital Database
Patients, doctors with specialties, departments, appointments, admissions to wards/beds, diagnoses (ICD-style codes), prescriptions, lab tests and results, and billing.
A bed can hold only one patient at a time; a doctor cannot have two appointments in the same slot.
Sensitive data (diagnoses, lab results) must be restricted by role.
Reports: bed occupancy, average length of stay, doctor workload, readmissions, abnormal lab results, outstanding bills.
Sample data SQL queries Reports (expected output)
Q3 (filtered): MRN-001 readmitted on 2026-03-20, 15 days after discharge.
Q5: MRN-002, invoice 2, outstanding 12000.00.
Advanced challenges Procedure transfer_bed(admission_id, new_bed_id) that closes the old stay and opens a new one atomically (split admissions into stays).
Row-level security: doctors see only their own patients' diagnoses.
Monthly doctor workload report (appointments done, admissions attended, no-show rate).
Age-group distribution of patients with a primary diagnosis per ICD chapter.
Audit trigger on lab result changes recording the user and old/new values.
departments 1──N doctors; departments 1──N wards 1──N beds
patients 1──N appointments N──1 doctors
patients 1──N admissions N──1 beds
admissions 1──N diagnoses N──1 icd_codes
appointments/admissions 1──N prescriptions
patients 1──N lab_orders 1──N lab_results N──1 lab_tests
patients 1──N invoices 1──N invoice_linesCREATE TABLE hsp_departments (dept_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(60) NOT NULL UNIQUE);
CREATE TABLE hsp_doctors (doctor_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, full_name VARCHAR(100) NOT NULL,
specialty VARCHAR(50) NOT NULL, dept_id INT NOT NULL REFERENCES hsp_departments);
CREATE TABLE hsp_patients (patient_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, mrn VARCHAR(20) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL, date_of_birth DATE NOT NULL,
sex CHAR(1) CHECK (sex IN ('F','M','X')), phone VARCHAR(20));
CREATE TABLE hsp_wards (ward_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(30) NOT NULL UNIQUE,
dept_id INT NOT NULL REFERENCES hsp_departments);
CREATE TABLE hsp_beds (bed_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ward_id INT NOT NULL REFERENCES hsp_wards,
bed_no VARCHAR(10) NOT NULL, UNIQUE (ward_id, bed_no));
CREATE TABLE hsp_appointments (appointment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
patient_id BIGINT NOT NULL REFERENCES hsp_patients
…INSERT INTO hsp_departments (name) VALUES ('Cardiology'), ('General Medicine');
INSERT INTO hsp_doctors (full_name, specialty, dept_id) VALUES ('Dr. Mehta', 'Cardiologist', 1), ('Dr. Nair', 'Physician', 2);
INSERT INTO hsp_patients (mrn, full_name, date_of_birth, sex) VALUES
('MRN-001', 'Ramesh Gupta', DATE '1960-05-10', 'M'),
('MRN-002', 'Lata Singh', DATE '1985-11-23', 'F'),
('MRN-003', 'Arun Das', DATE '1972-02-02', 'M');
INSERT INTO hsp_wards (name, dept_id) VALUES ('CCU', 1), ('Ward-A', 2);
INSERT INTO hsp_beds (ward_id, bed_no) VALUES (1, 'C1'), (1, 'C2'), (2, 'A1'), (2, 'A2');
INSERT INTO hsp_admissions (patient_id, bed_id, attending_doctor_id, admitted_at, discharged_at) VALUES
(1, 1, 1, TIMESTAMP '2026-03-01 09:00', TIMESTAMP '2026-03-05 12:00'),
(1, 2, 1, TIMESTAMP '2026-03-20 18:00', NULL),
(2, 3, 2, TIMESTAMP '2026-03-10 08:00', TIMESTAMP '2026-03-12 10:00'),
(3, 4, 2, TIMESTAMP '2026-03-22 14:00', NULL);
INSERT INTO hsp_lab_tests VALUES ('HB', 'Hemoglobin', 'g/dL', 12.0, 17.5), ('GLU', 'Fasting glucose', 'mg/dL', 70, 100);
INSERT INTO hsp_lab_results (patient_id, test_code, value, collected_at) VALUES
(1, 'GLU', 145, TIMESTAMP '2026-03-02 07:00'), (1, 'HB', 13.1,
…-- Q1. Current bed occupancy per ward
SELECT w.name AS ward, COUNT(b.bed_id) AS beds, COUNT(a.admission_id) AS occupied,
ROUND(100.0 * COUNT(a.admission_id) / COUNT(b.bed_id), 1) AS occupancy_pct
FROM hsp_wards w
JOIN hsp_beds b ON b.ward_id = w.ward_id
LEFT JOIN hsp_admissions a ON a.bed_id = b.bed_id AND a.discharged_at IS NULL
GROUP BY w.ward_id, w.name
ORDER BY w.name;
-- Q2. Length of stay for discharged admissions
SELECT p.mrn, a.admitted_at, a.discharged_at,
ROUND(EXTRACT(EPOCH FROM (a.discharged_at - a.admitted_at)) / 86400, 1) AS los_days
FROM hsp_admissions a JOIN hsp_patients p ON p.patient_id = a.patient_id
WHERE a.discharged_at IS NOT NULL
ORDER BY a.admitted_at;
-- Q3. Readmission within 30 days
SELECT p.mrn, a.admitted_at,
LAG(a.discharged_at) OVER (PARTITION BY a.patient_id ORDER BY a.admitted_at) AS prev_discharge
FROM hsp_admissions a JOIN hsp_patients p ON p.patient_id = a.patient_id;
-- wrap and filter: admitted_at <= prev_discharge + INTERVAL '30 days'
-- Q4. Abnormal lab results
SELECT p.mrn, t.name, r.value, t.unit,
CASE WHEN r.value < t.ref_low THEN 'LOW' WHEN r.value > t.ref_high THEN 'HIGH' END AS flag
FROM hsp_lab_results r
…Project 6 — Inventory Management
Items, suppliers, warehouses, bin locations, purchase orders, goods receipts, sales issues, transfers between warehouses, and stock adjustments.
Stock is derived from an immutable movement ledger; a snapshot table may cache current stock.
Valuation using weighted average cost.
Reports: stock on hand, reorder list, stock valuation, slow/dead stock, supplier lead time, inventory turnover.
Sample data SQL queries Reports (expected output)
Q2 (BOLT-M8 400 ≤ 500 → reorder; RELAY-12V 80 > 50 → no; FUSE 500 > 100 → no):
Q4: PO 1 received after 5 days (promised 5); PO 2 first receipt after 7 days (promised max 14).
Advanced challenges Prevent issues that would make stock negative (trigger or procedure with row locking on a stock snapshot table).
FIFO valuation using window functions over receipts and cumulative issues.
Inventory turnover = cost of goods issued / average inventory value per month.
ABC classification of items by annual consumption value (Pareto with NTILE or cumulative percentage).
A daily snapshot table populated by a scheduled job and a query comparing snapshot vs ledger.
suppliers 1──N purchase_orders 1──N po_lines N──1 items
warehouses 1──N stock_movements N──1 items
po_lines 1──N stock_movements (receipts reference the PO line)
items N──M suppliers (via supplier_items with price and lead time)CREATE TABLE inv2_items (item_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sku VARCHAR(30) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL, uom VARCHAR(10) NOT NULL DEFAULT 'EA',
reorder_point INT NOT NULL DEFAULT 0, reorder_qty INT NOT NULL DEFAULT 0);
CREATE TABLE inv2_suppliers (supplier_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE);
CREATE TABLE inv2_supplier_items (supplier_id INT REFERENCES inv2_suppliers, item_id INT REFERENCES inv2_items,
unit_cost DECIMAL(10,2) NOT NULL, lead_time_days SMALLINT NOT NULL,
PRIMARY KEY (supplier_id, item_id));
CREATE TABLE inv2_warehouses (warehouse_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, code VARCHAR(10) NOT NULL UNIQUE);
CREATE TABLE inv2_purchase_orders (po_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
supplier_id INT NOT NULL REFERENCES inv2_suppliers,
ordered_on DATE NOT NULL, status VARCHAR(10) NOT NULL DEFAULT 'OPEN');
CREATE TABLE inv2_po_lines (po_id INT REFERENCES inv2_purchase_orders, line_no SMALL
…INSERT INTO inv2_items (sku, name, reorder_point, reorder_qty) VALUES
('BOLT-M8', 'M8 bolt', 500, 2000), ('RELAY-12V', '12V automotive relay', 50, 200), ('FUSE-10A', '10A blade fuse', 100, 500);
INSERT INTO inv2_suppliers (name) VALUES ('Fasteners Ltd'), ('ElectroParts');
INSERT INTO inv2_supplier_items VALUES (1, 1, 2.50, 5), (2, 2, 85.00, 14), (2, 3, 4.00, 7);
INSERT INTO inv2_warehouses (code) VALUES ('WH-PUN'), ('WH-CHN');
INSERT INTO inv2_purchase_orders (supplier_id, ordered_on, status) VALUES (1, DATE '2026-01-02', 'CLOSED'), (2, DATE '2026-01-05', 'CLOSED');
INSERT INTO inv2_po_lines VALUES (1, 1, 1, 2000, 2.50), (2, 1, 2, 200, 85.00), (2, 2, 3, 500, 4.00);
INSERT INTO inv2_stock_movements (item_id, warehouse_id, movement_type, qty, unit_cost, po_id, po_line_no, moved_at) VALUES
(1, 1, 'RECEIPT', 2000, 2.50, 1, 1, TIMESTAMP '2026-01-07 10:00'),
(2, 1, 'RECEIPT', 200, 85.00, 2, 1, TIMESTAMP '2026-01-19 11:00'),
(3, 1, 'RECEIPT', 500, 4.00, 2, 2, TIMESTAMP '2026-01-12 09:00'),
(1, 1, 'ISSUE', -1600, NULL, NULL, NULL, TIMESTAMP '2026-02-10 15:00'),
(2, 1, 'TRANSFER_OUT', -50, NULL, NULL, NULL, TIMESTAMP '2026-02-15 08:00'),
(2, 2, 'TRANSFER_IN', 50, NULL, NULL, NULL, T
…-- Q1. Stock on hand by item and warehouse
SELECT i.sku, w.code, SUM(m.qty) AS on_hand
FROM inv2_stock_movements m
JOIN inv2_items i ON i.item_id = m.item_id
JOIN inv2_warehouses w ON w.warehouse_id = m.warehouse_id
GROUP BY i.sku, w.code
ORDER BY i.sku, w.code;
-- Q2. Reorder list (total across warehouses) with preferred (cheapest) supplier
WITH stock AS (SELECT item_id, SUM(qty) AS on_hand FROM inv2_stock_movements GROUP BY item_id),
best AS (SELECT si.*, ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY unit_cost) AS rn FROM inv2_supplier_items si)
SELECT i.sku, COALESCE(s.on_hand, 0) AS on_hand, i.reorder_point, i.reorder_qty, sup.name AS supplier, b.lead_time_days
FROM inv2_items i
LEFT JOIN stock s ON s.item_id = i.item_id
JOIN best b ON b.item_id = i.item_id AND b.rn = 1
JOIN inv2_suppliers sup ON sup.supplier_id = b.supplier_id
WHERE COALESCE(s.on_hand, 0) <= i.reorder_point;
-- Q3. Stock valuation at weighted average receipt cost
WITH avg_cost AS (
SELECT item_id, SUM(qty * unit_cost) / SUM(qty) AS wac
FROM inv2_stock_movements WHERE movement_type = 'RECEIPT' GROUP BY item_id
),
stock AS (SELECT item_id, SUM(qty) AS on_hand FROM inv2_stock_movements GROUP BY
…Project 7 — Automotive Diagnostics Database
Extend Module 36 with workshop diagnostic sessions: a technician connects a diagnostic tester to a vehicle, reads DTCs from ECUs together with freeze-frame data (snapshot of signal values when the DTC was set), performs repairs, clears codes, and records the outcome.
Track which diagnostic tester software version was used.
Track repair actions and replaced parts; link them to DTC occurrences.
Reports: most common DTCs by model, first-time-fix rate, repeat repairs within 30 days, average session duration, freeze-frame analysis (e.g., coolant temperature when misfire occurred), DTCs per 1000 km.
Schema (reuses vehicle, ecu, dtc_catalog from Module 36) Sample data SQL queries Reports (expected output) Cylinder 1 misfire detected Door ajar switch circuit
Q2 (P0301 on vehicle 4 returned 12 days after the first session — the spark-plug repair did not fix it):
Q6: status byte 47 = 0b00101111 → testFailed true, confirmed true; 9 = 0b00001001 → testFailed true, confirmed true.
Advanced challenges Link sessions to the software installed on each ECU at session time (point-in-time join from Module 36) and compute misfire rates per ECM software version.
Compute DTC occurrences per 1000 km using odometer readings between sessions.
Build a view v_open_issues of vehicles whose last session outcome was NOT_FIXED or PARTS_ORDERED.
Store freeze frames as JSONB instead of name/value rows; rewrite Q4 and compare design trade-offs.
Partition sessions by year and create a materialized view for the monthly warranty dashboard.
vehicle 1──N diag_session N──1 workshop
technician 1──N diag_session
diag_session 1──N session_dtc N──1 dtc_catalog (DTC read in a session, per ECU)
session_dtc 1──N freeze_frame (signal name/value pairs)
diag_session 1──N repair_action N──1 repair_operation
repair_action 1──N replaced_partCREATE TABLE ad_workshops (workshop_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, city VARCHAR(50) NOT NULL);
CREATE TABLE ad_technicians (technician_id INT PRIMARY KEY, full_name VARCHAR(100) NOT NULL,
workshop_id INT NOT NULL REFERENCES ad_workshops);
CREATE TABLE ad_sessions (
session_id INT PRIMARY KEY,
vehicle_id INT NOT NULL REFERENCES vehicle(vehicle_id),
technician_id INT NOT NULL REFERENCES ad_technicians,
tester_sw VARCHAR(20) NOT NULL,
odometer_km INT NOT NULL CHECK (odometer_km >= 0),
started_at TIMESTAMP NOT NULL,
ended_at TIMESTAMP,
outcome VARCHAR(12) CHECK (outcome IN ('FIXED','NOT_FIXED','PARTS_ORDERED')),
CHECK (ended_at IS NULL OR ended_at > started_at)
);
CREATE TABLE ad_session_dtcs (
session_dtc_id INT PRIMARY KEY,
session_id INT NOT NULL REFERENCES ad_sessions,
ecu_id INT NOT NULL REFERENCES ecu(ecu_id),
dtc_code VARCHAR(8) NOT NULL REFERENCES dtc_catalog(dtc_code),
dtc_status_byte SMALLINT NOT NULL, -- raw UDS status byte (0..255)
cleared BOOLEAN NOT NULL DEFAULT FALSE,
UNIQUE (session_id, ecu
…INSERT INTO ad_workshops VALUES (1, 'EH Service Pune', 'Pune');
INSERT INTO ad_technicians VALUES (1, 'Sanjay Patil', 1), (2, 'Kavya Rao', 1);
INSERT INTO ad_repair_operations VALUES
('OP-IGN-COIL', 'Replace ignition coil cylinder 1', 1.0),
('OP-SPARK', 'Replace spark plugs', 0.8),
('OP-DOOR-SW', 'Replace door ajar switch', 0.5);
INSERT INTO ad_sessions VALUES
(1, 4, 1, 'TESTER-4.2', 18250, TIMESTAMP '2026-03-06 09:00', TIMESTAMP '2026-03-06 10:30', 'NOT_FIXED'),
(2, 4, 2, 'TESTER-4.2', 18410, TIMESTAMP '2026-03-18 14:00', TIMESTAMP '2026-03-18 15:15', 'FIXED'),
(3, 3, 1, 'TESTER-4.3', 9120, TIMESTAMP '2026-03-02 08:00', TIMESTAMP '2026-03-02 09:00', 'FIXED');
INSERT INTO ad_session_dtcs VALUES
(1, 1, 10, 'P0301', 47, FALSE),
(2, 1, 11, 'B1234', 9, TRUE),
(3, 2, 10, 'P0301', 47, TRUE),
(4, 3, 7, 'P0301', 9, TRUE);
INSERT INTO ad_freeze_frames VALUES
(1, 'EngineRPM', 820, 'rpm'), (1, 'CoolantTemp', 38, 'degC'),
(3, 'EngineRPM', 790, 'rpm'), (3, 'CoolantTemp', 41, 'degC'),
(4, 'EngineRPM', 2400, 'rpm'), (4, 'CoolantTemp', 92, 'degC');
INSERT INTO ad_repair_actions VALUES
(1, 1, 1, 'OP-SPARK', 0.8),
(2, 1, 2, 'OP-DOOR-SW', 0.5),
(3, 2, 3, 'OP-IGN-COIL', 1.2),
(4, 3, 4, 'OP-IGN
…-- Q1. Most common DTCs by model (sessions in which they were read)
SELECT v.model, sd.dtc_code, c.description, COUNT(*) AS reads
FROM ad_session_dtcs sd
JOIN ad_sessions s ON s.session_id = sd.session_id
JOIN vehicle v ON v.vehicle_id = s.vehicle_id
JOIN dtc_catalog c ON c.dtc_code = sd.dtc_code
GROUP BY v.model, sd.dtc_code, c.description
ORDER BY v.model, reads DESC;
-- Q2. Repeat repair: same vehicle and DTC seen again within 30 days of an earlier session
SELECT v.vin, sd.dtc_code, s.started_at,
prev.started_at AS previous_session
FROM ad_session_dtcs sd
JOIN ad_sessions s ON s.session_id = sd.session_id
JOIN vehicle v ON v.vehicle_id = s.vehicle_id
JOIN LATERAL (
SELECT s2.started_at
FROM ad_session_dtcs sd2 JOIN ad_sessions s2 ON s2.session_id = sd2.session_id
WHERE s2.vehicle_id = s.vehicle_id AND sd2.dtc_code = sd.dtc_code
AND s2.started_at < s.started_at
AND s2.started_at >= s.started_at - INTERVAL '30 days'
ORDER BY s2.started_at DESC
LIMIT 1
) prev ON TRUE;
-- Q3. First-time-fix rate per technician
SELECT t.full_name, COUNT(*) AS sessions,
ROUND(100.0 * COUNT(*) FILTER (WHERE s.outcome = 'FIXED') / COUNT(*), 1) A
…Expected Result
Each project yields a working database with at least 5 business queries and verified expected outputs, plus a list of advanced tasks for extension.
Common Mistakes
Starting with queries before designing relationships and constraints.
No sample data covering edge cases (NULLs, zero rows, ties, cancellations).
Storing derived state (stock, balance) without a ledger to reconcile against.
Forgetting indexes on foreign keys and time columns.
Best Practices
Version-control every script and make them re-runnable (DROP ... IF EXISTS in dev only).
Include a verification query for every report with a hand-computed expected result.
Document design decisions and trade-offs in the README.
Interview Relevance
Projects give you concrete stories for behavioral and system-design interviews: "Tell me about a schema you designed" or "How did you prevent double-spending/double-booking?".
Practice Questions
Draw the ER diagram for each project in crow's-foot notation.
For Project 4, explain why the ledger table is the source of truth.
For Project 6, explain the trade-off between a ledger and a stock snapshot table.
For Project 7, propose three indexes for a fleet-wide DTC dashboard and justify them.