SQL with Real-World Databases
This module applies everything so far to ten realistic domains.
Concept
This module applies everything so far to ten realistic domains. Each example shows a compact but realistic schema and the queries that such a system actually runs. The schemas use PostgreSQL syntax (identity columns, TIMESTAMP, DATE - DATE); the notes in Module 47 show how to adapt them.
Why It Matters
Interviews and real projects rarely say "write a LEFT JOIN". They say "find patients who missed follow-ups" or "which books are overdue". Recognizing the SQL pattern inside a business question is the key practical skill.
Syntax
The patterns reused throughout: joins across 1:N and M:N relationships, anti-joins, GROUP BY with HAVING, window functions for rankings and latest records, date arithmetic, and conditional aggregation.
Example
35.1 Employee Management 35.2 Student Management 35.6 Automotive (Dealer Service) (The full engineering-level automotive diagnostics example is in Module 36.) 35.9 Ticket Booking 35.10 Engineering Project Management
CREATE TABLE em_departments (
dept_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE em_employees (
emp_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
dept_id INT REFERENCES em_departments(dept_id),
manager_id INT REFERENCES em_employees(emp_id),
hire_date DATE NOT NULL,
exit_date DATE,
CHECK (exit_date IS NULL OR exit_date >= hire_date)
);
CREATE TABLE em_salary_history (
emp_id INT NOT NULL REFERENCES em_employees(emp_id),
effective_from DATE NOT NULL,
salary DECIMAL(12,2) NOT NULL CHECK (salary > 0),
PRIMARY KEY (emp_id, effective_from)
);
CREATE TABLE em_leave_requests (
leave_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
emp_id INT NOT NULL REFERENCES em_employees(emp_id),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
status VARCHAR(10) NOT NULL CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED')),
CHECK (end_date >= start_date)
);-- Current salary of each active employee (latest effective record)
SELECT e.emp_id, e.full_name, s.salary
FROM em_employees e
JOIN em_salary_history s ON s.emp_id = e.emp_id
WHERE e.exit_date IS NULL
AND s.effective_from = (SELECT MAX(s2.effective_from)
FROM em_salary_history s2
WHERE s2.emp_id = e.emp_id AND s2.effective_from <= CURRENT_DATE);
-- Headcount and attrition per department in 2026
SELECT d.dept_name,
COUNT(*) FILTER (WHERE e.exit_date IS NULL) AS active,
COUNT(*) FILTER (WHERE e.exit_date >= DATE '2026-01-01'
AND e.exit_date < DATE '2027-01-01') AS left_in_2026
FROM em_departments d
LEFT JOIN em_employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;
-- Approved leave days per employee in 2026 (inclusive day count)
SELECT e.full_name, SUM(l.end_date - l.start_date + 1) AS leave_days
FROM em_leave_requests l
JOIN em_employees e ON e.emp_id = l.emp_id
WHERE l.status = 'APPROVED' AND l.start_date >= DATE '2026-01-01'
GROUP BY e.full_name
ORDER BY leave_days DESC;CREATE TABLE sm_students (
student_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
roll_no VARCHAR(20) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL,
program VARCHAR(50) NOT NULL,
admit_year SMALLINT NOT NULL
);
CREATE TABLE sm_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)
);
CREATE TABLE sm_enrollments (
student_id INT REFERENCES sm_students(student_id),
course_id INT REFERENCES sm_courses(course_id),
semester VARCHAR(7) NOT NULL, -- e.g. '2026-S1'
grade_point DECIMAL(3,1) CHECK (grade_point BETWEEN 0 AND 10),
PRIMARY KEY (student_id, course_id, semester)
);-- Credit-weighted GPA per student per semester
SELECT s.roll_no, e.semester,
ROUND(SUM(e.grade_point * c.credits) / SUM(c.credits), 2) AS sgpa
FROM sm_enrollments e
JOIN sm_students s ON s.student_id = e.student_id
JOIN sm_courses c ON c.course_id = e.course_id
WHERE e.grade_point IS NOT NULL
GROUP BY s.roll_no, e.semester;
-- Top 3 students per program by cumulative GPA
WITH cgpa AS (
SELECT s.student_id, s.full_name, s.program,
SUM(e.grade_point * c.credits) / SUM(c.credits) AS cgpa
FROM sm_students s
JOIN sm_enrollments e ON e.student_id = s.student_id
JOIN sm_courses c ON c.course_id = e.course_id
WHERE e.grade_point IS NOT NULL
GROUP BY s.student_id, s.full_name, s.program
)
SELECT program, full_name, ROUND(cgpa, 2) AS cgpa
FROM (SELECT cgpa.*, DENSE_RANK() OVER (PARTITION BY program ORDER BY cgpa DESC) AS rnk FROM cgpa) r
WHERE rnk <= 3
ORDER BY program, rnk;
-- Courses with pass rate below 70% (pass = grade_point >= 4)
SELECT c.code, c.title,
ROUND(100.0 * SUM(CASE WHEN e.grade_point >= 4 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pass_rate
FROM sm_courses c
JOIN sm_enrollments e ON e.course_id = c.course_id
WHERE e.grade_
…CREATE TABLE ec_customers (customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(120) NOT NULL UNIQUE, created_at TIMESTAMP NOT NULL);
CREATE TABLE ec_products (product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku VARCHAR(30) NOT NULL UNIQUE, name VARCHAR(200) NOT NULL,
category VARCHAR(50) NOT NULL, price DECIMAL(10,2) NOT NULL);
CREATE TABLE ec_orders (order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES ec_customers,
ordered_at TIMESTAMP NOT NULL,
status VARCHAR(15) NOT NULL);
CREATE TABLE ec_order_items (order_id BIGINT REFERENCES ec_orders, product_id BIGINT REFERENCES ec_products,
quantity INT NOT NULL CHECK (quantity > 0), unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id));
CREATE TABLE ec_carts (customer_id BIGINT REFERENCES ec_customers, product_id BIGINT REFERENCES ec_products,
added_at TIMESTAMP NOT NULL, PRIMARY KEY (customer
…-- Best-selling products in the last 30 days
SELECT p.sku, p.name, SUM(oi.quantity) AS units, SUM(oi.quantity * oi.unit_price) AS revenue
FROM ec_order_items oi
JOIN ec_orders o ON o.order_id = oi.order_id
JOIN ec_products p ON p.product_id = oi.product_id
WHERE o.ordered_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
AND o.status <> 'CANCELLED'
GROUP BY p.sku, p.name
ORDER BY revenue DESC
FETCH FIRST 10 ROWS ONLY;
-- Abandoned carts: items in cart for more than 24 hours with no order since they were added
SELECT c.email, p.name, ca.added_at
FROM ec_carts ca
JOIN ec_customers c ON c.customer_id = ca.customer_id
JOIN ec_products p ON p.product_id = ca.product_id
WHERE ca.added_at < CURRENT_TIMESTAMP - INTERVAL '24 hours'
AND NOT EXISTS (SELECT 1 FROM ec_orders o
WHERE o.customer_id = ca.customer_id AND o.ordered_at > ca.added_at);
-- Repeat-purchase rate: share of customers with 2+ orders
SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE n >= 2) / COUNT(*), 1) AS repeat_rate_pct
FROM (SELECT customer_id, COUNT(*) AS n FROM ec_orders WHERE status <> 'CANCELLED' GROUP BY customer_id) t;
-- Frequently bought together (product pairs in the same order)
SELECT a.produ
…CREATE TABLE bk_customers (customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL, kyc_status VARCHAR(10) NOT NULL);
CREATE TABLE bk_accounts (account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
account_no VARCHAR(20) NOT NULL UNIQUE,
customer_id BIGINT NOT NULL REFERENCES bk_customers,
account_type VARCHAR(10) NOT NULL CHECK (account_type IN ('SAVINGS','CURRENT')),
balance DECIMAL(15,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
opened_on DATE NOT NULL, status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE');
CREATE TABLE bk_transactions (txn_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
account_id BIGINT NOT NULL REFERENCES bk_accounts,
txn_time TIMESTAMP NOT NULL,
txn_type VARCHAR(6) NOT NULL CHECK (txn_type IN ('CREDIT','DEBIT')),
amount DECIMAL(15,2) NOT NULL CHECK (amount > 0),
channel VARCHAR(10) NOT NULL);
CREATE INDEX ix_bk_tx
…-- Account statement with running balance (opening balance assumed 0 at account opening)
SELECT t.txn_time, t.txn_type, t.amount,
SUM(CASE WHEN t.txn_type = 'CREDIT' THEN t.amount ELSE -t.amount END)
OVER (ORDER BY t.txn_time, t.txn_id ROWS UNBOUNDED PRECEDING) AS running_balance
FROM bk_transactions t
WHERE t.account_id = 1001
ORDER BY t.txn_time, t.txn_id;
-- Reconciliation: accounts whose stored balance differs from the transaction ledger
SELECT a.account_no, a.balance,
COALESCE(SUM(CASE WHEN t.txn_type = 'CREDIT' THEN t.amount ELSE -t.amount END), 0) AS ledger_balance
FROM bk_accounts a
LEFT JOIN bk_transactions t ON t.account_id = a.account_id
GROUP BY a.account_id, a.account_no, a.balance
HAVING a.balance <> COALESCE(SUM(CASE WHEN t.txn_type = 'CREDIT' THEN t.amount ELSE -t.amount END), 0);
-- Suspicious activity: more than 5 debits totalling over 100000 within any single day
SELECT account_id, CAST(txn_time AS DATE) AS txn_day, COUNT(*) AS debits, SUM(amount) AS total
FROM bk_transactions
WHERE txn_type = 'DEBIT'
GROUP BY account_id, CAST(txn_time AS DATE)
HAVING COUNT(*) > 5 AND SUM(amount) > 100000;
-- Dormant accounts: active accounts with no t
…CREATE TABLE hp_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, blood_group VARCHAR(3));
CREATE TABLE hp_doctors (doctor_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL, specialty VARCHAR(50) NOT NULL);
CREATE TABLE hp_appointments (appointment_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
patient_id BIGINT NOT NULL REFERENCES hp_patients,
doctor_id INT NOT NULL REFERENCES hp_doctors,
scheduled_at TIMESTAMP NOT NULL,
status VARCHAR(12) NOT NULL CHECK (status IN ('SCHEDULED','COMPLETED','NO_SHOW','CANCELLED')),
UNIQUE (doctor_id, scheduled_at));
CREATE TABLE hp_admissions (admission_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
patient_id BIGINT NOT NULL REFERENCES hp_patients,
ward VARCHAR(20) NOT NULL, admitted_at TIMESTAMP NOT NULL,
…-- Current bed occupancy per ward
SELECT ward, COUNT(*) AS current_patients
FROM hp_admissions
WHERE discharged_at IS NULL
GROUP BY ward;
-- Average length of stay (days) per ward for discharges in 2026
SELECT ward,
ROUND(AVG(EXTRACT(EPOCH FROM (discharged_at - admitted_at)) / 86400)::NUMERIC, 1) AS avg_los_days
FROM hp_admissions
WHERE discharged_at >= TIMESTAMP '2026-01-01'
GROUP BY ward;
-- 30-day readmissions: admissions within 30 days of the same patient's previous discharge
WITH a AS (
SELECT patient_id, admission_id, admitted_at,
LAG(discharged_at) OVER (PARTITION BY patient_id ORDER BY admitted_at) AS prev_discharge
FROM hp_admissions
)
SELECT p.mrn, a.admission_id, a.admitted_at, a.prev_discharge
FROM a JOIN hp_patients p ON p.patient_id = a.patient_id
WHERE a.prev_discharge IS NOT NULL
AND a.admitted_at <= a.prev_discharge + INTERVAL '30 days';
-- No-show rate per doctor
SELECT d.full_name, d.specialty,
ROUND(100.0 * COUNT(*) FILTER (WHERE a.status = 'NO_SHOW') / NULLIF(COUNT(*), 0), 1) AS no_show_pct
FROM hp_doctors d
JOIN hp_appointments a ON a.doctor_id = d.doctor_id
WHERE a.status IN ('COMPLETED', 'NO_SHOW')
GROUP BY d.doctor_id,
…Expected Result
Each query returns the business answer named in its comment. For example, the library "available copies" query returns one row per title with total_copies and available (copies without an open loan).
Common Mistakes
Computing balances from a stored column without reconciling against the ledger.
Counting rows after a LEFT JOIN with COUNT(*) instead of COUNT(child.pk).
Using BETWEEN on timestamps for daily/monthly reports.
Forgetting cancelled/voided records in revenue.
Double-booking prevention implemented only in application code.
Best Practices
Model events as immutable ledgers (transactions, stock movements) and derive balances.
Use partial/filtered unique indexes for "only one active X" rules.
Keep history (salary, owner, price) with effective dates.
Write reusable views for common business definitions.
Interview Relevance
High: interviewers often present one of these domains and ask for 3–5 queries. Practice explaining the grain of each table and the pattern used.
Practice Questions
Hospital: list doctors with no appointments next week.
Library: find members who have borrowed more than 10 books this year.
Banking: compute the month-end balance of each account.
Inventory: find warehouses where any item has negative on-hand stock (data error).
Project management: list tasks whose actual hours exceed the estimate by more than 20%.