Hospital ER Operations Analyst
Write advanced SQL queries to uncover what's going wrong in a hospital ER.
Introduction
30 Second Summary
Every hospital ER struggles with the same problems. Long wait times, overworked doctors, and bills that don't add up. The answers are hiding in the data, if someone knows how to ask the right questions.
In this project, you will step into the role of a data analyst at City General Hospital. You'll build a 6-table ER database from scratch, load it with realistic patient data, and write progressively harder SQL queries that answer real stakeholder questions about operations, staffing, and revenue.
What You'll Build
A fully normalized hospital ER database with a suite of analytical queries that use multi-table JOINs, sub-queries, CASE expressions, self-joins, and date arithmetic to answer real business questions.
By the end of this project, you'll have:
- A 6-table hospital ER schema with patients, doctors, departments, visits, treatments, and billing data.
- A collection of 10+ analytical queries answering real stakeholder questions using JOINs, sub-queries, CASE, self-joins, and date arithmetic.
- Experience translating vague business questions into precise, efficient SQL that produces actionable findings.
- Secret Mission: Build a "30-Day Readmission Alert" query using self-joins and date arithmetic to flag patients who return within 30 days of discharge.
Are there any prerequisites?
This project assumes you have MySQL 8.0 and MySQL Workbench installed on your machine.
You should be comfortable with SELECT, WHERE, JOINs, GROUP BY, and basic sub-queries. This project stretches those skills into CASE expressions, self-joins, and date arithmetic.
Before We Start
Before We Start...
Create the Database and Schema
Before you can find out why the ER is losing money, you need a database to query. In this step, you'll build the entire hospital schema from scratch in MySQL Workbench.
The schema models the real relationships in an ER department. Patients visit doctors, doctors belong to departments, visits generate treatments, and treatments produce bills. Six tables, all connected by foreign keys.
In this step, get ready to:
- Create the hospital_er database with 6 interconnected tables.
- Verify the schema with SHOW TABLES and DESCRIBE.
- Load sample data spanning 6 months of ER operations.
Create the database and tables
The schema uses a normalized design where each table has a single responsibility. Some tables reference each other through foreign keys, which means the order you create them matters.
- In MySQL Workbench, open a new SQL tab by clicking the **Create a new SQL tab** button in the toolbar (the page icon with a "+" symbol).
- Paste the following schema script into the editor:
-- Hospital ER Operations Database
-- MySQL 8.0
DROP DATABASE IF EXISTS hospital_er;
CREATE DATABASE hospital_er;
USE hospital_er;
-- Departments table
CREATE TABLE departments (
department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(100) NOT NULL,
floor_number INT NOT NULL,
head_doctor_id INT
);
-- Doctors table
CREATE TABLE doctors (
doctor_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department_id INT NOT NULL,
years_experience INT NOT NULL,
hire_date DATE NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- Add foreign key for head_doctor after doctors table exists
ALTER TABLE departments
ADD FOREIGN KEY (head_doctor_id) REFERENCES doctors(doctor_id);
-- Patients table
CREATE TABLE patients (
patient_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
gender ENUM('M', 'F', 'Other') NOT NULL,
phone VARCHAR(20),
insurance_provider VARCHAR(100)
);
-- Visits table
CREATE TABLE visits (
visit_id INT PRIMARY KEY AUTO_INCREMENT,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
department_id INT NOT NULL,
admission_time DATETIME NOT NULL,
discharge_time DATETIME,
wait_time_minutes INT NOT NULL,
diagnosis VARCHAR(200),
severity_level ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- Treatments table
CREATE TABLE treatments (
treatment_id INT PRIMARY KEY AUTO_INCREMENT,
visit_id INT NOT NULL,
treatment_name VARCHAR(200) NOT NULL,
treatment_cost DECIMAL(10, 2) NOT NULL,
treatment_time DATETIME NOT NULL,
notes TEXT,
FOREIGN KEY (visit_id) REFERENCES visits(visit_id)
);
-- Billing table
CREATE TABLE billing (
billing_id INT PRIMARY KEY AUTO_INCREMENT,
visit_id INT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
insurance_covered DECIMAL(10, 2) DEFAULT 0.00,
patient_responsibility DECIMAL(10, 2) NOT NULL,
payment_status ENUM('Paid', 'Pending', 'Overdue', 'Insurance Processing') NOT NULL,
billing_date DATE NOT NULL,
FOREIGN KEY (visit_id) REFERENCES visits(visit_id)
);What does this schema do?
This script creates a database called hospital_er and builds 6 tables inside it:
- departments and doctors reference each other. Departments have a head doctor, and doctors belong to a department. The ALTER TABLE statement adds the circular foreign key after both tables exist.
- patients stores demographic info. The ENUM type restricts gender to only valid values.
- visits connects patients to doctors and departments, with timestamps for time-based analysis later.
- treatments and billing both reference visits, linking costs and payments back to specific patient encounters.
- Execute the script by clicking the lightning bolt icon in the toolbar (or select **Query**, then **Execute (All or Selection)** from the menu bar).
- Check the **Output** panel at the bottom of Workbench. You should see green checkmarks for each statement with no errors.
- Save the file by pressing **Cmd+S** (macOS) or **Ctrl+S** (Windows). Name it 01_create_schema.sql.
Seeing errors in the Output panel?
- If you see an error about a table already existing, make sure the DROP DATABASE IF EXISTS hospital_er; line is at the top of your script. This clears any previous attempts.
- If you see a foreign key error, check that your tables are in the exact order shown above. The departments table must be created before doctors, and patients must exist before visits.
Help me debug my schema creation errors.
Verify the schema
Running the script without errors is a good sign, but you should confirm the tables actually exist with the right structure.
- Clear your SQL editor (select all, then delete) and run this command to list all tables:
USE hospital_er;
SHOW TABLES;You should see 6 rows in the result grid: billing, departments, doctors, patients, treatments, and visits.
- Inspect the visits table structure by running:
DESCRIBE visits;You should see columns including visit_id, patient_id, doctor_id, admission_time, discharge_time, wait_time_minutes, and severity_level. The Key column shows MUL for foreign key columns.
What does MUL mean?
In MySQL's DESCRIBE output, MUL means the column is the first column in a non-unique index. Foreign key columns automatically get this index to speed up JOIN operations.
Don't see 6 tables?
- Make sure you ran the USE hospital_er; line before SHOW TABLES. Without it, MySQL looks in whatever database was previously selected.
- If you see 0 tables, the schema script may not have executed fully. Go back to the previous step and re-run the full script.
Help me figure out why my tables aren't showing up.
Load sample data
An empty schema is useless for analysis. You need realistic data to query against. This script inserts approximately 100 rows across all 6 tables, covering 6 months of fictional ER operations.
- Open a new SQL tab in MySQL Workbench (click the **Create a new SQL tab** button again).
- Paste the following data script into the editor:
USE hospital_er;
-- Insert departments
INSERT INTO departments (department_id, department_name, floor_number) VALUES
(1, 'Emergency Medicine', 1),
(2, 'Cardiology', 2),
(3, 'Orthopedics', 3),
(4, 'Neurology', 2),
(5, 'Pediatrics', 1);
-- Insert doctors
INSERT INTO doctors (doctor_id, first_name, last_name, department_id, years_experience, hire_date) VALUES
(1, 'Sarah', 'Chen', 1, 12, '2014-03-15'),
(2, 'Michael', 'Rodriguez', 1, 3, '2023-06-01'),
(3, 'James', 'Wilson', 2, 18, '2008-01-10'),
(4, 'Priya', 'Patel', 2, 4, '2022-09-01'),
(5, 'Robert', 'Kim', 3, 15, '2011-05-20'),
(6, 'Lisa', 'Thompson', 3, 2, '2024-01-15'),
(7, 'David', 'Okafor', 4, 20, '2006-07-01'),
(8, 'Emily', 'Martinez', 4, 3, '2023-04-10'),
(9, 'Ahmed', 'Hassan', 5, 14, '2012-08-01'),
(10, 'Rachel', 'Green', 5, 1, '2025-02-01');
-- Update department heads
UPDATE departments SET head_doctor_id = 1 WHERE department_id = 1;
UPDATE departments SET head_doctor_id = 3 WHERE department_id = 2;
UPDATE departments SET head_doctor_id = 5 WHERE department_id = 3;
UPDATE departments SET head_doctor_id = 7 WHERE department_id = 4;
UPDATE departments SET head_doctor_id = 9 WHERE department_id = 5;
-- Insert patients
INSERT INTO patients (patient_id, first_name, last_name, date_of_birth, gender, phone, insurance_provider) VALUES
(1, 'John', 'Smith', '1985-04-12', 'M', '555-0101', 'BlueCross'),
(2, 'Maria', 'Garcia', '1992-08-23', 'F', '555-0102', 'Aetna'),
(3, 'William', 'Johnson', '1968-11-05', 'M', '555-0103', 'United Health'),
(4, 'Emma', 'Brown', '2015-02-14', 'F', '555-0104', 'BlueCross'),
(5, 'Robert', 'Davis', '1955-07-30', 'M', '555-0105', NULL),
(6, 'Sofia', 'Martinez', '1990-12-01', 'F', '555-0106', 'Cigna'),
(7, 'James', 'Taylor', '1978-03-22', 'M', '555-0107', 'Aetna'),
(8, 'Olivia', 'Anderson', '2010-06-15', 'F', '555-0108', 'United Health'),
(9, 'Daniel', 'Thomas', '1945-09-08', 'M', '555-0109', 'Medicare'),
(10, 'Isabella', 'Jackson', '1988-01-19', 'F', '555-0110', 'BlueCross'),
(11, 'Marcus', 'White', '1972-05-28', 'M', '555-0111', 'Cigna'),
(12, 'Aisha', 'Mohammed', '1995-10-14', 'F', '555-0112', 'Aetna'),
(13, 'Kevin', 'Lee', '1960-02-08', 'M', '555-0113', NULL),
(14, 'Grace', 'Walker', '2018-11-22', 'F', '555-0114', 'United Health'),
(15, 'Thomas', 'Harris', '1983-07-03', 'M', '555-0115', 'BlueCross');
-- Insert visits (spanning Jan 2026 - June 2026)
INSERT INTO visits (visit_id, patient_id, doctor_id, department_id, admission_time, discharge_time, wait_time_minutes, diagnosis, severity_level) VALUES
(1, 1, 1, 1, '2026-01-05 08:30:00', '2026-01-05 11:45:00', 12, 'Chest Pain', 'High'),
(2, 2, 2, 1, '2026-01-05 09:15:00', '2026-01-05 12:00:00', 25, 'Sprained Ankle', 'Low'),
(3, 3, 3, 2, '2026-01-08 14:00:00', '2026-01-08 16:30:00', 8, 'Arrhythmia', 'High'),
(4, 4, 9, 5, '2026-01-10 10:00:00', '2026-01-10 11:30:00', 15, 'High Fever', 'Medium'),
(5, 5, 1, 1, '2026-01-15 22:00:00', '2026-01-16 03:00:00', 5, 'Heart Attack', 'Critical'),
(6, 6, 4, 2, '2026-01-20 11:00:00', '2026-01-20 13:00:00', 20, 'Hypertension', 'Medium'),
(7, 7, 5, 3, '2026-01-25 16:30:00', '2026-01-25 19:00:00', 35, 'Fractured Wrist', 'Medium'),
(8, 8, 10, 5, '2026-02-01 09:00:00', '2026-02-01 10:30:00', 18, 'Ear Infection', 'Low'),
(9, 9, 7, 4, '2026-02-05 13:00:00', '2026-02-05 17:00:00', 10, 'Stroke', 'Critical'),
(10, 10, 1, 1, '2026-02-10 07:30:00', '2026-02-10 09:45:00', 8, 'Allergic Reaction', 'High'),
(11, 1, 3, 2, '2026-02-12 15:00:00', '2026-02-12 17:30:00', 12, 'Chest Pain', 'High'),
(12, 11, 2, 1, '2026-02-15 20:00:00', '2026-02-15 22:30:00', 30, 'Back Pain', 'Low'),
(13, 12, 8, 4, '2026-02-20 10:30:00', '2026-02-20 13:00:00', 22, 'Migraine', 'Medium'),
(14, 13, 5, 3, '2026-02-25 14:00:00', '2026-02-25 18:00:00', 40, 'Hip Fracture', 'High'),
(15, 14, 9, 5, '2026-03-01 08:00:00', '2026-03-01 09:30:00', 10, 'Croup', 'Medium'),
(16, 15, 1, 1, '2026-03-05 23:30:00', '2026-03-06 02:00:00', 3, 'Severe Bleeding', 'Critical'),
(17, 5, 3, 2, '2026-03-10 09:00:00', '2026-03-10 12:00:00', 15, 'Heart Failure', 'Critical'),
(18, 2, 6, 3, '2026-03-15 11:00:00', '2026-03-15 13:30:00', 28, 'Dislocated Shoulder', 'Medium'),
(19, 7, 7, 4, '2026-03-20 16:00:00', '2026-03-20 18:30:00', 14, 'Seizure', 'High'),
(20, 6, 2, 1, '2026-03-25 19:00:00', '2026-03-25 21:00:00', 32, 'Food Poisoning', 'Low'),
(21, 1, 1, 1, '2026-04-01 06:00:00', '2026-04-01 09:00:00', 7, 'Chest Pain', 'High'),
(22, 3, 4, 2, '2026-04-05 10:00:00', '2026-04-05 12:30:00', 18, 'Angina', 'High'),
(23, 9, 8, 4, '2026-04-10 14:30:00', '2026-04-10 18:00:00', 25, 'TIA', 'Critical'),
(24, 11, 5, 3, '2026-04-15 08:00:00', '2026-04-15 10:00:00', 20, 'Knee Injury', 'Medium'),
(25, 4, 10, 5, '2026-04-20 09:30:00', '2026-04-20 11:00:00', 12, 'Asthma Attack', 'High'),
(26, 13, 1, 1, '2026-04-25 21:00:00', '2026-04-25 23:30:00', 6, 'Difficulty Breathing', 'High'),
(27, 15, 3, 2, '2026-05-01 12:00:00', '2026-05-01 14:30:00', 10, 'Chest Pain', 'High'),
(28, 12, 7, 4, '2026-05-05 15:00:00', '2026-05-05 17:00:00', 16, 'Vertigo', 'Low'),
(29, 10, 6, 3, '2026-05-10 09:00:00', '2026-05-10 11:30:00', 22, 'Ankle Fracture', 'Medium'),
(30, 5, 1, 1, '2026-05-15 03:00:00', '2026-05-15 07:00:00', 4, 'Cardiac Arrest', 'Critical'),
(31, 8, 9, 5, '2026-05-20 10:00:00', '2026-05-20 11:30:00', 14, 'Bronchitis', 'Medium'),
(32, 6, 4, 2, '2026-05-25 13:00:00', '2026-05-25 15:00:00', 20, 'Palpitations', 'Medium'),
(33, 7, 2, 1, '2026-06-01 17:00:00', '2026-06-01 19:00:00', 28, 'Laceration', 'Low'),
(34, 14, 10, 5, '2026-06-05 08:30:00', '2026-06-05 10:00:00', 10, 'High Fever', 'Medium'),
(35, 3, 7, 4, '2026-06-10 11:00:00', '2026-06-10 14:00:00', 12, 'Headache', 'Low'),
(36, 1, 1, 1, '2026-06-15 08:00:00', '2026-06-15 10:30:00', 9, 'Chest Pain', 'High');
-- Insert treatments
INSERT INTO treatments (treatment_id, visit_id, treatment_name, treatment_cost, treatment_time, notes) VALUES
(1, 1, 'ECG', 250.00, '2026-01-05 09:00:00', 'Normal sinus rhythm'),
(2, 1, 'Blood Panel', 180.00, '2026-01-05 09:15:00', 'Elevated troponin'),
(3, 2, 'X-Ray', 200.00, '2026-01-05 09:45:00', 'No fracture detected'),
(4, 2, 'Bandage and Ice', 50.00, '2026-01-05 10:30:00', NULL),
(5, 3, 'ECG', 250.00, '2026-01-08 14:30:00', 'Irregular rhythm detected'),
(6, 3, 'Cardioversion', 1500.00, '2026-01-08 15:00:00', 'Successful'),
(7, 4, 'Blood Panel', 180.00, '2026-01-10 10:30:00', 'Elevated WBC'),
(8, 4, 'Acetaminophen IV', 75.00, '2026-01-10 10:45:00', NULL),
(9, 5, 'ECG', 250.00, '2026-01-15 22:10:00', 'STEMI confirmed'),
(10, 5, 'Cardiac Catheterization', 5000.00, '2026-01-15 22:30:00', 'Stent placed'),
(11, 5, 'ICU Admission', 3000.00, '2026-01-16 00:00:00', 'Overnight monitoring'),
(12, 6, 'Blood Pressure Monitoring', 100.00, '2026-01-20 11:30:00', 'BP 180/110'),
(13, 6, 'Medication Adjustment', 50.00, '2026-01-20 12:00:00', 'Increased dosage'),
(14, 7, 'X-Ray', 200.00, '2026-01-25 17:00:00', 'Clean fracture'),
(15, 7, 'Cast Application', 350.00, '2026-01-25 17:30:00', NULL),
(16, 8, 'Otoscope Exam', 75.00, '2026-02-01 09:20:00', 'Infection confirmed'),
(17, 8, 'Antibiotics Prescription', 30.00, '2026-02-01 09:40:00', NULL),
(18, 9, 'CT Scan', 1200.00, '2026-02-05 13:15:00', 'Ischemic stroke confirmed'),
(19, 9, 'tPA Administration', 2500.00, '2026-02-05 13:30:00', 'Within window'),
(20, 9, 'ICU Admission', 3000.00, '2026-02-05 14:00:00', NULL),
(21, 10, 'Epinephrine', 150.00, '2026-02-10 07:40:00', 'Anaphylaxis'),
(22, 10, 'Observation', 200.00, '2026-02-10 08:00:00', '2 hour monitoring'),
(23, 11, 'ECG', 250.00, '2026-02-12 15:20:00', 'Normal'),
(24, 11, 'Stress Test', 800.00, '2026-02-12 16:00:00', 'Referred for follow-up'),
(25, 12, 'X-Ray', 200.00, '2026-02-15 20:30:00', 'No structural issues'),
(26, 12, 'Muscle Relaxant', 45.00, '2026-02-15 21:00:00', NULL),
(27, 13, 'CT Scan', 1200.00, '2026-02-20 11:00:00', 'No bleed'),
(28, 13, 'Pain Management', 100.00, '2026-02-20 11:30:00', 'Sumatriptan administered'),
(29, 14, 'X-Ray', 200.00, '2026-02-25 14:30:00', 'Displaced fracture'),
(30, 14, 'Surgery Consult', 500.00, '2026-02-25 15:00:00', 'Scheduled for OR'),
(31, 14, 'Pain Management', 150.00, '2026-02-25 14:45:00', 'Morphine'),
(32, 15, 'Nebulizer', 100.00, '2026-03-01 08:20:00', NULL),
(33, 15, 'Steroid Injection', 200.00, '2026-03-01 08:40:00', NULL),
(34, 16, 'Blood Transfusion', 1800.00, '2026-03-05 23:40:00', '2 units'),
(35, 16, 'Wound Suturing', 600.00, '2026-03-06 00:30:00', '12 stitches'),
(36, 17, 'ECG', 250.00, '2026-03-10 09:15:00', 'Reduced EF'),
(37, 17, 'Diuretic IV', 200.00, '2026-03-10 09:30:00', NULL),
(38, 17, 'Cardiology Consult', 400.00, '2026-03-10 10:00:00', NULL),
(39, 18, 'X-Ray', 200.00, '2026-03-15 11:20:00', 'Anterior dislocation'),
(40, 18, 'Reduction Procedure', 800.00, '2026-03-15 11:45:00', 'Successful'),
(41, 19, 'CT Scan', 1200.00, '2026-03-20 16:15:00', 'No lesion'),
(42, 19, 'Anti-seizure Medication', 300.00, '2026-03-20 16:45:00', NULL),
(43, 20, 'IV Fluids', 100.00, '2026-03-25 19:20:00', 'Dehydration'),
(44, 20, 'Anti-nausea Medication', 50.00, '2026-03-25 19:30:00', NULL),
(45, 21, 'ECG', 250.00, '2026-04-01 06:15:00', 'ST changes'),
(46, 21, 'Blood Panel', 180.00, '2026-04-01 06:20:00', 'Troponin borderline'),
(47, 21, 'Cardiology Consult', 400.00, '2026-04-01 07:00:00', 'Scheduled cath lab'),
(48, 22, 'ECG', 250.00, '2026-04-05 10:20:00', 'ST depression'),
(49, 22, 'Nitroglycerin', 80.00, '2026-04-05 10:30:00', 'Pain relieved'),
(50, 23, 'CT Scan', 1200.00, '2026-04-10 15:00:00', 'Small infarct'),
(51, 23, 'Anticoagulant', 350.00, '2026-04-10 15:30:00', NULL),
(52, 24, 'MRI', 1500.00, '2026-04-15 08:30:00', 'ACL tear'),
(53, 24, 'Knee Brace', 200.00, '2026-04-15 09:00:00', 'Surgery recommended'),
(54, 25, 'Nebulizer', 100.00, '2026-04-20 09:45:00', NULL),
(55, 25, 'Steroid Injection', 200.00, '2026-04-20 10:00:00', 'Rapid response'),
(56, 26, 'Chest X-Ray', 220.00, '2026-04-25 21:10:00', 'Pleural effusion'),
(57, 26, 'Oxygen Therapy', 150.00, '2026-04-25 21:20:00', NULL),
(58, 27, 'ECG', 250.00, '2026-05-01 12:15:00', 'Normal'),
(59, 27, 'Blood Panel', 180.00, '2026-05-01 12:20:00', 'Normal ranges'),
(60, 28, 'Vestibular Test', 300.00, '2026-05-05 15:20:00', 'BPPV confirmed'),
(61, 28, 'Epley Maneuver', 150.00, '2026-05-05 15:40:00', 'Successful'),
(62, 29, 'X-Ray', 200.00, '2026-05-10 09:20:00', 'Lateral malleolus fracture'),
(63, 29, 'Cast Application', 350.00, '2026-05-10 09:50:00', NULL),
(64, 30, 'Defibrillation', 2000.00, '2026-05-15 03:05:00', 'ROSC achieved'),
(65, 30, 'ICU Admission', 3000.00, '2026-05-15 03:30:00', 'Critical care'),
(66, 30, 'Cardiac Catheterization', 5000.00, '2026-05-15 04:00:00', 'Multiple stents'),
(67, 31, 'Chest X-Ray', 220.00, '2026-05-20 10:15:00', 'Bronchial inflammation'),
(68, 31, 'Antibiotics Prescription', 30.00, '2026-05-20 10:30:00', NULL),
(69, 32, 'ECG', 250.00, '2026-05-25 13:15:00', 'PACs detected'),
(70, 32, 'Holter Monitor Setup', 400.00, '2026-05-25 13:45:00', '24hr monitoring'),
(71, 33, 'Wound Cleaning', 100.00, '2026-06-01 17:15:00', 'Deep laceration'),
(72, 33, 'Suturing', 300.00, '2026-06-01 17:30:00', '8 stitches'),
(73, 34, 'Blood Panel', 180.00, '2026-06-05 08:45:00', 'Viral markers'),
(74, 34, 'Acetaminophen IV', 75.00, '2026-06-05 09:00:00', NULL),
(75, 35, 'CT Scan', 1200.00, '2026-06-10 11:20:00', 'No abnormalities'),
(76, 35, 'Pain Management', 100.00, '2026-06-10 11:45:00', NULL),
(77, 36, 'ECG', 250.00, '2026-06-15 08:15:00', 'Unchanged from prior'),
(78, 36, 'Blood Panel', 180.00, '2026-06-15 08:20:00', 'Normal troponin'),
(79, 36, 'Discharge with Follow-up', 50.00, '2026-06-15 09:30:00', 'Cardiology referral');
-- Insert billing
INSERT INTO billing (billing_id, visit_id, total_amount, insurance_covered, patient_responsibility, payment_status, billing_date) VALUES
(1, 1, 430.00, 344.00, 86.00, 'Paid', '2026-01-06'),
(2, 2, 250.00, 200.00, 50.00, 'Paid', '2026-01-06'),
(3, 3, 1750.00, 1400.00, 350.00, 'Paid', '2026-01-09'),
(4, 4, 255.00, 204.00, 51.00, 'Paid', '2026-01-11'),
(5, 5, 8250.00, 6600.00, 1650.00, 'Insurance Processing', '2026-01-17'),
(6, 6, 150.00, 120.00, 30.00, 'Paid', '2026-01-21'),
(7, 7, 550.00, 440.00, 110.00, 'Paid', '2026-01-26'),
(8, 8, 105.00, 84.00, 21.00, 'Paid', '2026-02-02'),
(9, 9, 6700.00, 5360.00, 1340.00, 'Insurance Processing', '2026-02-06'),
(10, 10, 350.00, 280.00, 70.00, 'Paid', '2026-02-11'),
(11, 11, 1050.00, 840.00, 210.00, 'Pending', '2026-02-13'),
(12, 12, 245.00, 196.00, 49.00, 'Paid', '2026-02-16'),
(13, 13, 1300.00, 1040.00, 260.00, 'Paid', '2026-02-21'),
(14, 14, 850.00, 0.00, 850.00, 'Overdue', '2026-02-26'),
(15, 15, 300.00, 240.00, 60.00, 'Paid', '2026-03-02'),
(16, 16, 2400.00, 1920.00, 480.00, 'Paid', '2026-03-07'),
(17, 17, 850.00, 680.00, 170.00, 'Pending', '2026-03-11'),
(18, 18, 1000.00, 800.00, 200.00, 'Paid', '2026-03-16'),
(19, 19, 1500.00, 1200.00, 300.00, 'Paid', '2026-03-21'),
(20, 20, 150.00, 120.00, 30.00, 'Paid', '2026-03-26'),
(21, 21, 830.00, 664.00, 166.00, 'Pending', '2026-04-02'),
(22, 22, 330.00, 264.00, 66.00, 'Paid', '2026-04-06'),
(23, 23, 1550.00, 1240.00, 310.00, 'Insurance Processing', '2026-04-11'),
(24, 24, 1700.00, 1360.00, 340.00, 'Paid', '2026-04-16'),
(25, 25, 300.00, 240.00, 60.00, 'Paid', '2026-04-21'),
(26, 26, 370.00, 0.00, 370.00, 'Overdue', '2026-04-26'),
(27, 27, 430.00, 344.00, 86.00, 'Paid', '2026-05-02'),
(28, 28, 450.00, 360.00, 90.00, 'Paid', '2026-05-06'),
(29, 29, 550.00, 440.00, 110.00, 'Paid', '2026-05-11'),
(30, 30, 10000.00, 8000.00, 2000.00, 'Insurance Processing', '2026-05-16'),
(31, 31, 250.00, 200.00, 50.00, 'Paid', '2026-05-21'),
(32, 32, 650.00, 520.00, 130.00, 'Pending', '2026-05-26'),
(33, 33, 400.00, 320.00, 80.00, 'Paid', '2026-06-02'),
(34, 34, 255.00, 204.00, 51.00, 'Paid', '2026-06-06'),
(35, 35, 1300.00, 1040.00, 260.00, 'Pending', '2026-06-11'),
(36, 36, 480.00, 384.00, 96.00, 'Paid', '2026-06-16');What data did you just load?
- 5 departments (Emergency Medicine, Cardiology, Orthopedics, Neurology, Pediatrics).
- 10 doctors with varying experience levels (1 to 20 years).
- 15 patients spanning ages from young children to elderly.
- 36 visits from January to June 2026, with different severity levels and diagnoses.
- 79 treatments ranging from basic ($30 antibiotics) to expensive ($5000 cardiac catheterization).
- 36 billing records with a mix of Paid, Pending, Overdue, and Insurance Processing statuses.
Notice that some patients have multiple visits (repeat ER visitors), and some visits have multiple treatments. These patterns will be important when you write analytical queries in later steps.
- Execute the script by clicking the lightning bolt icon.
- Check the **Output** panel. You should see green checkmarks for every INSERT and UPDATE statement.
- Save this file as 02_insert_data.sql by pressing **Cmd+S** (macOS) or **Ctrl+S** (Windows).
- Confirm the data loaded correctly by running this quick count:
SELECT
'departments' AS table_name, COUNT(*) AS row_count FROM departments
UNION ALL SELECT
'doctors', COUNT(*) FROM doctors
UNION ALL SELECT
'patients', COUNT(*) FROM patients
UNION ALL SELECT
'visits', COUNT(*) FROM visits
UNION ALL SELECT
'treatments', COUNT(*) FROM treatments
UNION ALL SELECT
'billing', COUNT(*) FROM billing;You should see 6 rows in the result grid: departments (5), doctors (10), patients (15), visits (36), treatments (79), billing (36).
Seeing fewer rows than expected?
- If you see 0 rows across all tables, you may have run the INSERT script against the wrong database. Make sure USE hospital_er; is the first line.
- If some tables have fewer rows, scroll through the Output panel for any red error icons. A common cause is a foreign key violation where a referenced ID does not exist yet.
- If you need to start fresh, re-run 01_create_schema.sql first (it drops and recreates everything), then run 02_insert_data.sql again.
Help me debug my data loading errors.
✔️ Awesome, I've got everything!
Great. Make sure you have saved both 01_create_schema.sql and 02_insert_data.sql.
ⓧ I'd like to double check the full code
Here is the full 01_create_schema.sql:
-- Hospital ER Operations Database
-- MySQL 8.0
DROP DATABASE IF EXISTS hospital_er;
CREATE DATABASE hospital_er;
USE hospital_er;
-- Departments table
CREATE TABLE departments (
department_id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(100) NOT NULL,
floor_number INT NOT NULL,
head_doctor_id INT
);
-- Doctors table
CREATE TABLE doctors (
doctor_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department_id INT NOT NULL,
years_experience INT NOT NULL,
hire_date DATE NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- Add foreign key for head_doctor after doctors table exists
ALTER TABLE departments
ADD FOREIGN KEY (head_doctor_id) REFERENCES doctors(doctor_id);
-- Patients table
CREATE TABLE patients (
patient_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
gender ENUM('M', 'F', 'Other') NOT NULL,
phone VARCHAR(20),
insurance_provider VARCHAR(100)
);
-- Visits table
CREATE TABLE visits (
visit_id INT PRIMARY KEY AUTO_INCREMENT,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
department_id INT NOT NULL,
admission_time DATETIME NOT NULL,
discharge_time DATETIME,
wait_time_minutes INT NOT NULL,
diagnosis VARCHAR(200),
severity_level ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- Treatments table
CREATE TABLE treatments (
treatment_id INT PRIMARY KEY AUTO_INCREMENT,
visit_id INT NOT NULL,
treatment_name VARCHAR(200) NOT NULL,
treatment_cost DECIMAL(10, 2) NOT NULL,
treatment_time DATETIME NOT NULL,
notes TEXT,
FOREIGN KEY (visit_id) REFERENCES visits(visit_id)
);
-- Billing table
CREATE TABLE billing (
billing_id INT PRIMARY KEY AUTO_INCREMENT,
visit_id INT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
insurance_covered DECIMAL(10, 2) DEFAULT 0.00,
patient_responsibility DECIMAL(10, 2) NOT NULL,
payment_status ENUM('Paid', 'Pending', 'Overdue', 'Insurance Processing') NOT NULL,
billing_date DATE NOT NULL,
FOREIGN KEY (visit_id) REFERENCES visits(visit_id)
);And here is the full 02_insert_data.sql:
USE hospital_er;
-- Insert departments
INSERT INTO departments (department_id, department_name, floor_number) VALUES
(1, 'Emergency Medicine', 1),
(2, 'Cardiology', 2),
(3, 'Orthopedics', 3),
(4, 'Neurology', 2),
(5, 'Pediatrics', 1);
-- Insert doctors
INSERT INTO doctors (doctor_id, first_name, last_name, department_id, years_experience, hire_date) VALUES
(1, 'Sarah', 'Chen', 1, 12, '2014-03-15'),
(2, 'Michael', 'Rodriguez', 1, 3, '2023-06-01'),
(3, 'James', 'Wilson', 2, 18, '2008-01-10'),
(4, 'Priya', 'Patel', 2, 4, '2022-09-01'),
(5, 'Robert', 'Kim', 3, 15, '2011-05-20'),
(6, 'Lisa', 'Thompson', 3, 2, '2024-01-15'),
(7, 'David', 'Okafor', 4, 20, '2006-07-01'),
(8, 'Emily', 'Martinez', 4, 3, '2023-04-10'),
(9, 'Ahmed', 'Hassan', 5, 14, '2012-08-01'),
(10, 'Rachel', 'Green', 5, 1, '2025-02-01');
-- Update department heads
UPDATE departments SET head_doctor_id = 1 WHERE department_id = 1;
UPDATE departments SET head_doctor_id = 3 WHERE department_id = 2;
UPDATE departments SET head_doctor_id = 5 WHERE department_id = 3;
UPDATE departments SET head_doctor_id = 7 WHERE department_id = 4;
UPDATE departments SET head_doctor_id = 9 WHERE department_id = 5;
-- Insert patients
INSERT INTO patients (patient_id, first_name, last_name, date_of_birth, gender, phone, insurance_provider) VALUES
(1, 'John', 'Smith', '1985-04-12', 'M', '555-0101', 'BlueCross'),
(2, 'Maria', 'Garcia', '1992-08-23', 'F', '555-0102', 'Aetna'),
(3, 'William', 'Johnson', '1968-11-05', 'M', '555-0103', 'United Health'),
(4, 'Emma', 'Brown', '2015-02-14', 'F', '555-0104', 'BlueCross'),
(5, 'Robert', 'Davis', '1955-07-30', 'M', '555-0105', NULL),
(6, 'Sofia', 'Martinez', '1990-12-01', 'F', '555-0106', 'Cigna'),
(7, 'James', 'Taylor', '1978-03-22', 'M', '555-0107', 'Aetna'),
(8, 'Olivia', 'Anderson', '2010-06-15', 'F', '555-0108', 'United Health'),
(9, 'Daniel', 'Thomas', '1945-09-08', 'M', '555-0109', 'Medicare'),
(10, 'Isabella', 'Jackson', '1988-01-19', 'F', '555-0110', 'BlueCross'),
(11, 'Marcus', 'White', '1972-05-28', 'M', '555-0111', 'Cigna'),
(12, 'Aisha', 'Mohammed', '1995-10-14', 'F', '555-0112', 'Aetna'),
(13, 'Kevin', 'Lee', '1960-02-08', 'M', '555-0113', NULL),
(14, 'Grace', 'Walker', '2018-11-22', 'F', '555-0114', 'United Health'),
(15, 'Thomas', 'Harris', '1983-07-03', 'M', '555-0115', 'BlueCross');
-- Insert visits (spanning Jan 2026 - June 2026)
INSERT INTO visits (visit_id, patient_id, doctor_id, department_id, admission_time, discharge_time, wait_time_minutes, diagnosis, severity_level) VALUES
(1, 1, 1, 1, '2026-01-05 08:30:00', '2026-01-05 11:45:00', 12, 'Chest Pain', 'High'),
(2, 2, 2, 1, '2026-01-05 09:15:00', '2026-01-05 12:00:00', 25, 'Sprained Ankle', 'Low'),
(3, 3, 3, 2, '2026-01-08 14:00:00', '2026-01-08 16:30:00', 8, 'Arrhythmia', 'High'),
(4, 4, 9, 5, '2026-01-10 10:00:00', '2026-01-10 11:30:00', 15, 'High Fever', 'Medium'),
(5, 5, 1, 1, '2026-01-15 22:00:00', '2026-01-16 03:00:00', 5, 'Heart Attack', 'Critical'),
(6, 6, 4, 2, '2026-01-20 11:00:00', '2026-01-20 13:00:00', 20, 'Hypertension', 'Medium'),
(7, 7, 5, 3, '2026-01-25 16:30:00', '2026-01-25 19:00:00', 35, 'Fractured Wrist', 'Medium'),
(8, 8, 10, 5, '2026-02-01 09:00:00', '2026-02-01 10:30:00', 18, 'Ear Infection', 'Low'),
(9, 9, 7, 4, '2026-02-05 13:00:00', '2026-02-05 17:00:00', 10, 'Stroke', 'Critical'),
(10, 10, 1, 1, '2026-02-10 07:30:00', '2026-02-10 09:45:00', 8, 'Allergic Reaction', 'High'),
(11, 1, 3, 2, '2026-02-12 15:00:00', '2026-02-12 17:30:00', 12, 'Chest Pain', 'High'),
(12, 11, 2, 1, '2026-02-15 20:00:00', '2026-02-15 22:30:00', 30, 'Back Pain', 'Low'),
(13, 12, 8, 4, '2026-02-20 10:30:00', '2026-02-20 13:00:00', 22, 'Migraine', 'Medium'),
(14, 13, 5, 3, '2026-02-25 14:00:00', '2026-02-25 18:00:00', 40, 'Hip Fracture', 'High'),
(15, 14, 9, 5, '2026-03-01 08:00:00', '2026-03-01 09:30:00', 10, 'Croup', 'Medium'),
(16, 15, 1, 1, '2026-03-05 23:30:00', '2026-03-06 02:00:00', 3, 'Severe Bleeding', 'Critical'),
(17, 5, 3, 2, '2026-03-10 09:00:00', '2026-03-10 12:00:00', 15, 'Heart Failure', 'Critical'),
(18, 2, 6, 3, '2026-03-15 11:00:00', '2026-03-15 13:30:00', 28, 'Dislocated Shoulder', 'Medium'),
(19, 7, 7, 4, '2026-03-20 16:00:00', '2026-03-20 18:30:00', 14, 'Seizure', 'High'),
(20, 6, 2, 1, '2026-03-25 19:00:00', '2026-03-25 21:00:00', 32, 'Food Poisoning', 'Low'),
(21, 1, 1, 1, '2026-04-01 06:00:00', '2026-04-01 09:00:00', 7, 'Chest Pain', 'High'),
(22, 3, 4, 2, '2026-04-05 10:00:00', '2026-04-05 12:30:00', 18, 'Angina', 'High'),
(23, 9, 8, 4, '2026-04-10 14:30:00', '2026-04-10 18:00:00', 25, 'TIA', 'Critical'),
(24, 11, 5, 3, '2026-04-15 08:00:00', '2026-04-15 10:00:00', 20, 'Knee Injury', 'Medium'),
(25, 4, 10, 5, '2026-04-20 09:30:00', '2026-04-20 11:00:00', 12, 'Asthma Attack', 'High'),
(26, 13, 1, 1, '2026-04-25 21:00:00', '2026-04-25 23:30:00', 6, 'Difficulty Breathing', 'High'),
(27, 15, 3, 2, '2026-05-01 12:00:00', '2026-05-01 14:30:00', 10, 'Chest Pain', 'High'),
(28, 12, 7, 4, '2026-05-05 15:00:00', '2026-05-05 17:00:00', 16, 'Vertigo', 'Low'),
(29, 10, 6, 3, '2026-05-10 09:00:00', '2026-05-10 11:30:00', 22, 'Ankle Fracture', 'Medium'),
(30, 5, 1, 1, '2026-05-15 03:00:00', '2026-05-15 07:00:00', 4, 'Cardiac Arrest', 'Critical'),
(31, 8, 9, 5, '2026-05-20 10:00:00', '2026-05-20 11:30:00', 14, 'Bronchitis', 'Medium'),
(32, 6, 4, 2, '2026-05-25 13:00:00', '2026-05-25 15:00:00', 20, 'Palpitations', 'Medium'),
(33, 7, 2, 1, '2026-06-01 17:00:00', '2026-06-01 19:00:00', 28, 'Laceration', 'Low'),
(34, 14, 10, 5, '2026-06-05 08:30:00', '2026-06-05 10:00:00', 10, 'High Fever', 'Medium'),
(35, 3, 7, 4, '2026-06-10 11:00:00', '2026-06-10 14:00:00', 12, 'Headache', 'Low'),
(36, 1, 1, 1, '2026-06-15 08:00:00', '2026-06-15 10:30:00', 9, 'Chest Pain', 'High');
-- Insert treatments
INSERT INTO treatments (treatment_id, visit_id, treatment_name, treatment_cost, treatment_time, notes) VALUES
(1, 1, 'ECG', 250.00, '2026-01-05 09:00:00', 'Normal sinus rhythm'),
(2, 1, 'Blood Panel', 180.00, '2026-01-05 09:15:00', 'Elevated troponin'),
(3, 2, 'X-Ray', 200.00, '2026-01-05 09:45:00', 'No fracture detected'),
(4, 2, 'Bandage and Ice', 50.00, '2026-01-05 10:30:00', NULL),
(5, 3, 'ECG', 250.00, '2026-01-08 14:30:00', 'Irregular rhythm detected'),
(6, 3, 'Cardioversion', 1500.00, '2026-01-08 15:00:00', 'Successful'),
(7, 4, 'Blood Panel', 180.00, '2026-01-10 10:30:00', 'Elevated WBC'),
(8, 4, 'Acetaminophen IV', 75.00, '2026-01-10 10:45:00', NULL),
(9, 5, 'ECG', 250.00, '2026-01-15 22:10:00', 'STEMI confirmed'),
(10, 5, 'Cardiac Catheterization', 5000.00, '2026-01-15 22:30:00', 'Stent placed'),
(11, 5, 'ICU Admission', 3000.00, '2026-01-16 00:00:00', 'Overnight monitoring'),
(12, 6, 'Blood Pressure Monitoring', 100.00, '2026-01-20 11:30:00', 'BP 180/110'),
(13, 6, 'Medication Adjustment', 50.00, '2026-01-20 12:00:00', 'Increased dosage'),
(14, 7, 'X-Ray', 200.00, '2026-01-25 17:00:00', 'Clean fracture'),
(15, 7, 'Cast Application', 350.00, '2026-01-25 17:30:00', NULL),
(16, 8, 'Otoscope Exam', 75.00, '2026-02-01 09:20:00', 'Infection confirmed'),
(17, 8, 'Antibiotics Prescription', 30.00, '2026-02-01 09:40:00', NULL),
(18, 9, 'CT Scan', 1200.00, '2026-02-05 13:15:00', 'Ischemic stroke confirmed'),
(19, 9, 'tPA Administration', 2500.00, '2026-02-05 13:30:00', 'Within window'),
(20, 9, 'ICU Admission', 3000.00, '2026-02-05 14:00:00', NULL),
(21, 10, 'Epinephrine', 150.00, '2026-02-10 07:40:00', 'Anaphylaxis'),
(22, 10, 'Observation', 200.00, '2026-02-10 08:00:00', '2 hour monitoring'),
(23, 11, 'ECG', 250.00, '2026-02-12 15:20:00', 'Normal'),
(24, 11, 'Stress Test', 800.00, '2026-02-12 16:00:00', 'Referred for follow-up'),
(25, 12, 'X-Ray', 200.00, '2026-02-15 20:30:00', 'No structural issues'),
(26, 12, 'Muscle Relaxant', 45.00, '2026-02-15 21:00:00', NULL),
(27, 13, 'CT Scan', 1200.00, '2026-02-20 11:00:00', 'No bleed'),
(28, 13, 'Pain Management', 100.00, '2026-02-20 11:30:00', 'Sumatriptan administered'),
(29, 14, 'X-Ray', 200.00, '2026-02-25 14:30:00', 'Displaced fracture'),
(30, 14, 'Surgery Consult', 500.00, '2026-02-25 15:00:00', 'Scheduled for OR'),
(31, 14, 'Pain Management', 150.00, '2026-02-25 14:45:00', 'Morphine'),
(32, 15, 'Nebulizer', 100.00, '2026-03-01 08:20:00', NULL),
(33, 15, 'Steroid Injection', 200.00, '2026-03-01 08:40:00', NULL),
(34, 16, 'Blood Transfusion', 1800.00, '2026-03-05 23:40:00', '2 units'),
(35, 16, 'Wound Suturing', 600.00, '2026-03-06 00:30:00', '12 stitches'),
(36, 17, 'ECG', 250.00, '2026-03-10 09:15:00', 'Reduced EF'),
(37, 17, 'Diuretic IV', 200.00, '2026-03-10 09:30:00', NULL),
(38, 17, 'Cardiology Consult', 400.00, '2026-03-10 10:00:00', NULL),
(39, 18, 'X-Ray', 200.00, '2026-03-15 11:20:00', 'Anterior dislocation'),
(40, 18, 'Reduction Procedure', 800.00, '2026-03-15 11:45:00', 'Successful'),
(41, 19, 'CT Scan', 1200.00, '2026-03-20 16:15:00', 'No lesion'),
(42, 19, 'Anti-seizure Medication', 300.00, '2026-03-20 16:45:00', NULL),
(43, 20, 'IV Fluids', 100.00, '2026-03-25 19:20:00', 'Dehydration'),
(44, 20, 'Anti-nausea Medication', 50.00, '2026-03-25 19:30:00', NULL),
(45, 21, 'ECG', 250.00, '2026-04-01 06:15:00', 'ST changes'),
(46, 21, 'Blood Panel', 180.00, '2026-04-01 06:20:00', 'Troponin borderline'),
(47, 21, 'Cardiology Consult', 400.00, '2026-04-01 07:00:00', 'Scheduled cath lab'),
(48, 22, 'ECG', 250.00, '2026-04-05 10:20:00', 'ST depression'),
(49, 22, 'Nitroglycerin', 80.00, '2026-04-05 10:30:00', 'Pain relieved'),
(50, 23, 'CT Scan', 1200.00, '2026-04-10 15:00:00', 'Small infarct'),
(51, 23, 'Anticoagulant', 350.00, '2026-04-10 15:30:00', NULL),
(52, 24, 'MRI', 1500.00, '2026-04-15 08:30:00', 'ACL tear'),
(53, 24, 'Knee Brace', 200.00, '2026-04-15 09:00:00', 'Surgery recommended'),
(54, 25, 'Nebulizer', 100.00, '2026-04-20 09:45:00', NULL),
(55, 25, 'Steroid Injection', 200.00, '2026-04-20 10:00:00', 'Rapid response'),
(56, 26, 'Chest X-Ray', 220.00, '2026-04-25 21:10:00', 'Pleural effusion'),
(57, 26, 'Oxygen Therapy', 150.00, '2026-04-25 21:20:00', NULL),
(58, 27, 'ECG', 250.00, '2026-05-01 12:15:00', 'Normal'),
(59, 27, 'Blood Panel', 180.00, '2026-05-01 12:20:00', 'Normal ranges'),
(60, 28, 'Vestibular Test', 300.00, '2026-05-05 15:20:00', 'BPPV confirmed'),
(61, 28, 'Epley Maneuver', 150.00, '2026-05-05 15:40:00', 'Successful'),
(62, 29, 'X-Ray', 200.00, '2026-05-10 09:20:00', 'Lateral malleolus fracture'),
(63, 29, 'Cast Application', 350.00, '2026-05-10 09:50:00', NULL),
(64, 30, 'Defibrillation', 2000.00, '2026-05-15 03:05:00', 'ROSC achieved'),
(65, 30, 'ICU Admission', 3000.00, '2026-05-15 03:30:00', 'Critical care'),
(66, 30, 'Cardiac Catheterization', 5000.00, '2026-05-15 04:00:00', 'Multiple stents'),
(67, 31, 'Chest X-Ray', 220.00, '2026-05-20 10:15:00', 'Bronchial inflammation'),
(68, 31, 'Antibiotics Prescription', 30.00, '2026-05-20 10:30:00', NULL),
(69, 32, 'ECG', 250.00, '2026-05-25 13:15:00', 'PACs detected'),
(70, 32, 'Holter Monitor Setup', 400.00, '2026-05-25 13:45:00', '24hr monitoring'),
(71, 33, 'Wound Cleaning', 100.00, '2026-06-01 17:15:00', 'Deep laceration'),
(72, 33, 'Suturing', 300.00, '2026-06-01 17:30:00', '8 stitches'),
(73, 34, 'Blood Panel', 180.00, '2026-06-05 08:45:00', 'Viral markers'),
(74, 34, 'Acetaminophen IV', 75.00, '2026-06-05 09:00:00', NULL),
(75, 35, 'CT Scan', 1200.00, '2026-06-10 11:20:00', 'No abnormalities'),
(76, 35, 'Pain Management', 100.00, '2026-06-10 11:45:00', NULL),
(77, 36, 'ECG', 250.00, '2026-06-15 08:15:00', 'Unchanged from prior'),
(78, 36, 'Blood Panel', 180.00, '2026-06-15 08:20:00', 'Normal troponin'),
(79, 36, 'Discharge with Follow-up', 50.00, '2026-06-15 09:30:00', 'Cardiology referral');
-- Insert billing
INSERT INTO billing (billing_id, visit_id, total_amount, insurance_covered, patient_responsibility, payment_status, billing_date) VALUES
(1, 1, 430.00, 344.00, 86.00, 'Paid', '2026-01-06'),
(2, 2, 250.00, 200.00, 50.00, 'Paid', '2026-01-06'),
(3, 3, 1750.00, 1400.00, 350.00, 'Paid', '2026-01-09'),
(4, 4, 255.00, 204.00, 51.00, 'Paid', '2026-01-11'),
(5, 5, 8250.00, 6600.00, 1650.00, 'Insurance Processing', '2026-01-17'),
(6, 6, 150.00, 120.00, 30.00, 'Paid', '2026-01-21'),
(7, 7, 550.00, 440.00, 110.00, 'Paid', '2026-01-26'),
(8, 8, 105.00, 84.00, 21.00, 'Paid', '2026-02-02'),
(9, 9, 6700.00, 5360.00, 1340.00, 'Insurance Processing', '2026-02-06'),
(10, 10, 350.00, 280.00, 70.00, 'Paid', '2026-02-11'),
(11, 11, 1050.00, 840.00, 210.00, 'Pending', '2026-02-13'),
(12, 12, 245.00, 196.00, 49.00, 'Paid', '2026-02-16'),
(13, 13, 1300.00, 1040.00, 260.00, 'Paid', '2026-02-21'),
(14, 14, 850.00, 0.00, 850.00, 'Overdue', '2026-02-26'),
(15, 15, 300.00, 240.00, 60.00, 'Paid', '2026-03-02'),
(16, 16, 2400.00, 1920.00, 480.00, 'Paid', '2026-03-07'),
(17, 17, 850.00, 680.00, 170.00, 'Pending', '2026-03-11'),
(18, 18, 1000.00, 800.00, 200.00, 'Paid', '2026-03-16'),
(19, 19, 1500.00, 1200.00, 300.00, 'Paid', '2026-03-21'),
(20, 20, 150.00, 120.00, 30.00, 'Paid', '2026-03-26'),
(21, 21, 830.00, 664.00, 166.00, 'Pending', '2026-04-02'),
(22, 22, 330.00, 264.00, 66.00, 'Paid', '2026-04-06'),
(23, 23, 1550.00, 1240.00, 310.00, 'Insurance Processing', '2026-04-11'),
(24, 24, 1700.00, 1360.00, 340.00, 'Paid', '2026-04-16'),
(25, 25, 300.00, 240.00, 60.00, 'Paid', '2026-04-21'),
(26, 26, 370.00, 0.00, 370.00, 'Overdue', '2026-04-26'),
(27, 27, 430.00, 344.00, 86.00, 'Paid', '2026-05-02'),
(28, 28, 450.00, 360.00, 90.00, 'Paid', '2026-05-06'),
(29, 29, 550.00, 440.00, 110.00, 'Paid', '2026-05-11'),
(30, 30, 10000.00, 8000.00, 2000.00, 'Insurance Processing', '2026-05-16'),
(31, 31, 250.00, 200.00, 50.00, 'Paid', '2026-05-21'),
(32, 32, 650.00, 520.00, 130.00, 'Pending', '2026-05-26'),
(33, 33, 400.00, 320.00, 80.00, 'Paid', '2026-06-02'),
(34, 34, 255.00, 204.00, 51.00, 'Paid', '2026-06-06'),
(35, 35, 1300.00, 1040.00, 260.00, 'Pending', '2026-06-11'),
(36, 36, 480.00, 384.00, 96.00, 'Paid', '2026-06-16');Your database is built and loaded with 6 months of ER data. Next up, you'll start querying this data to answer real questions about patient flow, doctor workload, and revenue.
Write Foundation Queries
Your database is built and loaded with 6 months of ER data. Now the real work begins.
Leadership wants answers: How busy is the ER? Which doctors are overworked? Where is the money going? In this step, you'll write foundation queries that explore patient volume, doctor workloads, and revenue across departments.
In this step, get ready to:
- Query patient demographics and visit volume.
- Analyze doctor workloads using JOINs and HAVING.
- Build a revenue overview across departments and billing.
Query patient demographics and visit volume
The first questions any analyst answers are volume questions. How many people came in, when did they come, and who are they?
- In MySQL Workbench, open a new query tab by clicking File in the menu bar, then New Query Tab.
- Start your foundation queries file by adding this header and first query:
USE hospital_er;
-- ============================================
-- STEP 2: FOUNDATION QUERIES
-- ============================================
-- Q1: How many patients visited per month?
SELECT
MONTH(admission_time) AS visit_month,
YEAR(admission_time) AS visit_year,
COUNT(*) AS total_visits
FROM visits
GROUP BY YEAR(admission_time), MONTH(admission_time)
ORDER BY visit_year, visit_month;What does this query do?
MONTH(admission_time) extracts the month number (1-12) from each visit's admission timestamp. YEAR(admission_time) does the same for the year.
By grouping on both year and month, you get a clean monthly breakdown of ER traffic. The ORDER BY ensures results display chronologically.
- Select the entire query (from SELECT through the semicolon) and execute it by clicking the lightning bolt icon in the toolbar.
- You should see 6 rows in the Result Grid, one for each month from January to June 2026.
Seeing an error about unknown column?
Make sure you included the USE hospital_er; line at the top and executed it first. Without it, MySQL does not know which database to query.
Also check that column names match exactly: admission_time (not admit_time or admission_date).
Help me debug my monthly visits query
- Add the age distribution query below Q1 in the same file:
-- Q2: Age distribution of ER patients
SELECT
CASE
WHEN TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) < 18 THEN 'Under 18'
WHEN TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) BETWEEN 18 AND 35 THEN '18-35'
WHEN TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) BETWEEN 36 AND 55 THEN '36-55'
WHEN TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) BETWEEN 56 AND 70 THEN '56-70'
ELSE '71+'
END AS age_group,
COUNT(DISTINCT p.patient_id) AS patient_count
FROM patients p
JOIN visits v ON p.patient_id = v.patient_id
GROUP BY age_group
ORDER BY patient_count DESC;What does this query do?
TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) calculates each patient's age by finding the difference in years between their birth date and today.
The CASE expression groups ages into human-readable buckets. COUNT(DISTINCT p.patient_id) counts unique patients so repeat visitors are not double-counted.
What is TIMESTAMPDIFF?
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) returns the difference between two datetime values in the unit you specify. It subtracts expr1 from expr2.
You can use units like SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, or YEAR. You will use MINUTE later to measure ER stay durations.
- Select Q2 and execute it. You should see age groups listed with their patient counts, ordered from largest to smallest group.
Getting a CASE syntax error?
Make sure every WHEN has a matching THEN, and the entire block ends with END (not END CASE). In SELECT queries, CASE terminates with just END.
Help me fix my CASE expression
- Add the department volume query below Q2:
-- Q3: Which departments handle the most visits?
SELECT
d.department_name,
COUNT(v.visit_id) AS total_visits
FROM departments d
JOIN visits v ON d.department_id = v.department_id
GROUP BY d.department_name
ORDER BY total_visits DESC;What does this query do?
This JOIN connects the departments table to visits using their shared department_id column. This lets you count visits per department while displaying the department name instead of just an ID number.
- Select Q3 and execute it. You should see 5 rows showing each department and its visit count, with Emergency Medicine at the top.
Seeing 0 results?
Check that you are joining on the correct column: d.department_id = v.department_id. If you accidentally join on a different column, the result set will be empty or incorrect.
Help me debug my JOIN query
Analyze doctor workload
Knowing total visit volume is useful, but leadership also wants to know which individual doctors are handling the most patients. Overworked doctors lead to longer wait times and burnout.
- Add the doctor workload query below Q3 in your file:
-- Q4: Which doctors are seeing more than 5 patients total?
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
d.department_name,
COUNT(v.visit_id) AS total_visits
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
JOIN departments d ON doc.department_id = d.department_id
GROUP BY doc.doctor_id, doctor_name, d.department_name
HAVING COUNT(v.visit_id) > 5
ORDER BY total_visits DESC;What does this query do?
This query joins three tables: doctors to visits (to count each doctor's patients) and departments (to display the department name alongside each doctor).
The HAVING clause filters AFTER grouping. Unlike WHERE (which filters individual rows before aggregation), HAVING filters entire groups based on aggregate values like COUNT().
- Select Q4 and execute it. You should see only the doctors who have handled more than 5 visits in the dataset.
Getting an error about non-aggregated columns?
MySQL requires that every column in your SELECT that is not inside an aggregate function (like COUNT or AVG) must also appear in the GROUP BY clause. Make sure your GROUP BY includes doc.doctor_id, doctor_name, d.department_name.
Help me fix my GROUP BY clause
- Add the average visit duration query below Q4:
-- Q5: Average visit duration per doctor (in minutes)
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time)), 0) AS avg_duration_minutes
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
WHERE v.discharge_time IS NOT NULL
GROUP BY doc.doctor_id, doctor_name
ORDER BY avg_duration_minutes DESC;What does this query do?
TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time) calculates how many minutes each patient spent in the ER. Wrapping it in AVG() gives each doctor's average, and ROUND(..., 0) removes decimal places for readability.
The WHERE v.discharge_time IS NOT NULL filter is critical. Without it, any patient still admitted would produce a NULL duration that breaks the average calculation.
- Select Q5 and execute it. You should see each doctor's name and their average ER stay duration in minutes, sorted from longest to shortest.
Seeing NULL in avg_duration_minutes?
If you forgot the WHERE v.discharge_time IS NOT NULL clause, rows with NULL discharge times will produce NULL when passed to TIMESTAMPDIFF. Add the WHERE clause before your GROUP BY.
Help me fix my TIMESTAMPDIFF query
Build a revenue overview
Patient volume and doctor workload tell half the story. The other half is money. Leadership needs to know which departments generate the most revenue and how much of that revenue is actually being collected.
- Add the revenue by department query below Q5:
-- Q6: Total revenue by department
SELECT
d.department_name,
SUM(b.total_amount) AS total_revenue,
COUNT(v.visit_id) AS total_visits,
ROUND(SUM(b.total_amount) / COUNT(v.visit_id), 2) AS revenue_per_visit
FROM departments d
JOIN visits v ON d.department_id = v.department_id
JOIN billing b ON v.visit_id = b.visit_id
GROUP BY d.department_name
ORDER BY total_revenue DESC;What does this query do?
This query chains two JOINs: departments connects to visits (which department handled the visit), then visits connects to billing (how much was billed for that visit).
The revenue_per_visit calculated column divides total revenue by visit count. This reveals whether a department is high-revenue because it sees many patients or because each visit is expensive.
- Select Q6 and execute it. You should see 5 rows with each department's total revenue, visit count, and per-visit average.
Missing departments in results?
An INNER JOIN only returns rows where matches exist in both tables. If a department had no visits (or visits had no billing records), it would be excluded. In this dataset all departments have visits and billing, so you should see all 5.
Check that both JOIN conditions use the correct columns: d.department_id = v.department_id and v.visit_id = b.visit_id.
Help me debug my multi-table JOIN
- Add the payment status breakdown query below Q6:
-- Q7: Percentage of bills unpaid or overdue
SELECT
payment_status,
COUNT(*) AS bill_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM billing), 1) AS percentage
FROM billing
GROUP BY payment_status
ORDER BY bill_count DESC;What does this query do?
The (SELECT COUNT(*) FROM billing) is a scalar sub-query. It runs independently and returns a single value (the total number of bills). MySQL plugs that value into the outer query's arithmetic to calculate each status as a percentage of the whole.
Multiplying by 100.0 (not 100) forces decimal division. Without the decimal, MySQL would perform integer division and round down to 0 for small groups.
- Select Q7 and execute it. You should see 4 rows: Paid, Pending, Insurance Processing, and Overdue, each with a count and percentage.
- Save this file as 03_foundation_queries.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows) and choosing the same folder as your other SQL files.
Percentage column showing 0 for every row?
This happens when you multiply by 100 (integer) instead of 100.0 (decimal). Integer division in MySQL truncates the result. Change to COUNT(*) * 100.0 / ... to force decimal arithmetic.
Help me fix my percentage calculation
✔️ Awesome, I've got everything!
Great. Make sure you have saved 03_foundation_queries.sql with all 7 queries.
ⓧ I'd like to double check the full code
Here is the complete 03_foundation_queries.sql file. Compare it against yours:
USE hospital_er;
-- ============================================
-- STEP 2: FOUNDATION QUERIES
-- ============================================
-- Q1: How many patients visited per month?
SELECT
MONTH(admission_time) AS visit_month,
YEAR(admission_time) AS visit_year,
COUNT(*) AS total_visits
FROM visits
GROUP BY YEAR(admission_time), MONTH(admission_time)
ORDER BY visit_year, visit_month;
-- Q2: Age distribution of ER patients
SELECT
CASE
WHEN TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) < 18 THEN 'Under 18'
WHEN TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) BETWEEN 18 AND 35 THEN '18-35'
WHEN TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) BETWEEN 36 AND 55 THEN '36-55'
WHEN TIMESTAMPDIFF(YEAR, p.date_of_birth, CURDATE()) BETWEEN 56 AND 70 THEN '56-70'
ELSE '71+'
END AS age_group,
COUNT(DISTINCT p.patient_id) AS patient_count
FROM patients p
JOIN visits v ON p.patient_id = v.patient_id
GROUP BY age_group
ORDER BY patient_count DESC;
-- Q3: Which departments handle the most visits?
SELECT
d.department_name,
COUNT(v.visit_id) AS total_visits
FROM departments d
JOIN visits v ON d.department_id = v.department_id
GROUP BY d.department_name
ORDER BY total_visits DESC;
-- Q4: Which doctors are seeing more than 5 patients total?
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
d.department_name,
COUNT(v.visit_id) AS total_visits
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
JOIN departments d ON doc.department_id = d.department_id
GROUP BY doc.doctor_id, doctor_name, d.department_name
HAVING COUNT(v.visit_id) > 5
ORDER BY total_visits DESC;
-- Q5: Average visit duration per doctor (in minutes)
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time)), 0) AS avg_duration_minutes
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
WHERE v.discharge_time IS NOT NULL
GROUP BY doc.doctor_id, doctor_name
ORDER BY avg_duration_minutes DESC;
-- Q6: Total revenue by department
SELECT
d.department_name,
SUM(b.total_amount) AS total_revenue,
COUNT(v.visit_id) AS total_visits,
ROUND(SUM(b.total_amount) / COUNT(v.visit_id), 2) AS revenue_per_visit
FROM departments d
JOIN visits v ON d.department_id = v.department_id
JOIN billing b ON v.visit_id = b.visit_id
GROUP BY d.department_name
ORDER BY total_revenue DESC;
-- Q7: Percentage of bills unpaid or overdue
SELECT
payment_status,
COUNT(*) AS bill_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM billing), 1) AS percentage
FROM billing
GROUP BY payment_status
ORDER BY bill_count DESC;You now have a solid baseline picture of the ER's operations. Next up, you'll use CASE expressions and date arithmetic to dig deeper into patient severity patterns and time-based trends.
Write Intermediate Queries with CASE and Date Arithmetic
Your foundation queries revealed the raw numbers. You know visit counts, department volumes, and revenue totals. But leadership doesn't want raw numbers. They want answers like "how bad is the wait time problem?" and "which shift is the bottleneck?"
To answer those questions, you need to transform continuous data into meaningful categories. The CASE expression lets you create custom buckets from raw values. Combined with TIMESTAMPDIFF for time calculations, you can turn timestamps into actionable insights about shift performance and treatment efficiency.
In this step, get ready to:
- Classify visits into severity buckets using CASE expressions.
- Analyze ER stay durations by shift using TIMESTAMPDIFF and CASE.
- Compare treatment costs against outcomes using a derived subquery.
Classify visits by wait time severity
The ER already records a severity_level column, but that was assigned by triage nurses at arrival. Leadership wants an independent classification based purely on how long patients actually waited. This tells them whether the triage system is working.
- In MySQL Workbench, create a new SQL tab by clicking the new query tab button (the "+" icon above the SQL editor).
- Start building your intermediate queries file by pasting this header and first query:
USE hospital_er;
-- Q1: Patient severity classification by wait time
SELECT
CASE
WHEN wait_time_minutes BETWEEN 0 AND 5 THEN 'Critical (0-5 min)'
WHEN wait_time_minutes BETWEEN 6 AND 15 THEN 'Urgent (6-15 min)'
WHEN wait_time_minutes BETWEEN 16 AND 30 THEN 'Standard (16-30 min)'
ELSE 'Low Priority (31+ min)'
END AS wait_category,
COUNT(*) AS visit_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM visits), 1) AS percentage
FROM visits
GROUP BY wait_category
ORDER BY MIN(wait_time_minutes);What does this query do?
The CASE expression evaluates wait_time_minutes for each row and assigns it to a named bucket. It works like an if/else chain. MySQL checks each WHEN condition in order and returns the first match.
The percentage column uses a scalar subquery (SELECT COUNT(*) FROM visits) to divide each bucket's count by the total number of visits. The ORDER BY MIN(wait_time_minutes) sorts results by the lowest wait time in each bucket, putting Critical first.
What is a CASE expression?
A CASE expression in SQL transforms values inline. The syntax is CASE WHEN condition THEN result ... ELSE result END. It returns a single value per row, which you can alias and use in GROUP BY just like any other column.
- Click the lightning bolt icon (or press Cmd+Shift+Enter on macOS or Ctrl+Shift+Enter on Windows) to execute all statements.
- You should see a result grid with 4 rows, one per wait category, each showing a visit count and percentage.
Seeing an error about wait_category in GROUP BY?
Make sure your CASE expression ends with END AS wait_category. Missing the END keyword is the most common CASE syntax mistake.
Help me debug my CASE expression query
Analyze ER stay durations by shift
Knowing the average ER stay is useful, but it hides a critical pattern. Stays might be longer at night when fewer staff are on duty. The TIMESTAMPDIFF function calculates the exact difference between two timestamps in any unit you choose (minutes, hours, days).
- Add the following query below Q1 in your 04_intermediate_queries.sql tab:
-- Q2: Average ER stay by shift
SELECT
CASE
WHEN HOUR(admission_time) BETWEEN 6 AND 13 THEN 'Morning (6AM-2PM)'
WHEN HOUR(admission_time) BETWEEN 14 AND 21 THEN 'Afternoon (2PM-10PM)'
ELSE 'Night (10PM-6AM)'
END AS shift,
COUNT(*) AS visit_count,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, admission_time, discharge_time)), 0) AS avg_stay_minutes
FROM visits
WHERE discharge_time IS NOT NULL
GROUP BY shift
ORDER BY avg_stay_minutes DESC;What does this query do?
HOUR(admission_time) extracts just the hour (0-23) from the admission timestamp. The CASE expression then labels each visit with a shift name based on when the patient arrived.
TIMESTAMPDIFF(MINUTE, admission_time, discharge_time) returns the number of minutes between admission and discharge for each visit. Wrapping it in AVG() gives the average stay per shift. The WHERE discharge_time IS NOT NULL filter excludes patients still in the ER (no discharge yet).
What is TIMESTAMPDIFF?
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) returns datetime_expr2 minus datetime_expr1 in the specified unit. Legal units include MINUTE, HOUR, DAY, MONTH, and YEAR. It handles all the edge cases of crossing midnight, month boundaries, and leap years for you.
- Highlight Q2 (from the -- comment through the semicolon) and click the lightning bolt icon to execute just this query.
- You should see 3 rows in the result grid. Each row shows a shift name, the number of visits during that shift, and the average stay in minutes.
Getting NULL values in avg_stay_minutes?
Check that your WHERE discharge_time IS NOT NULL clause is present. Without it, rows with NULL discharge times produce NULL in the TIMESTAMPDIFF calculation, which can affect the average.
Help me fix NULL values in my TIMESTAMPDIFF query
Compare treatment costs against stay duration
Leadership wants to know whether expensive treatments actually get patients out faster. This requires combining data from the visits and treatments tables, computing per-visit averages, and then grouping those averages into cost categories. A derived subquery (a SELECT inside the FROM clause) is the cleanest way to handle this two-layer aggregation.
- Add the following query below Q2 in your file:
-- Q3: Cost vs outcome
SELECT
CASE
WHEN avg_cost < 300 THEN 'Low Cost (<$300)'
WHEN avg_cost BETWEEN 300 AND 1000 THEN 'Medium Cost ($300-$1000)'
ELSE 'High Cost (>$1000)'
END AS cost_category,
COUNT(*) AS visit_count,
ROUND(AVG(avg_cost), 2) AS avg_treatment_cost,
ROUND(AVG(stay_minutes), 0) AS avg_stay_minutes
FROM (
SELECT
v.visit_id,
AVG(t.treatment_cost) AS avg_cost,
TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time) AS stay_minutes
FROM visits v
JOIN treatments t ON v.visit_id = t.visit_id
WHERE v.discharge_time IS NOT NULL
GROUP BY v.visit_id, v.admission_time, v.discharge_time
) AS visit_costs
GROUP BY cost_category
ORDER BY AVG(avg_cost);What does this query do?
The inner subquery (aliased as visit_costs) calculates two values per visit: the average treatment cost and the total stay in minutes. It joins visits to treatments and groups by visit_id so each row represents one visit.
The outer query then treats that result set as a table. It uses CASE to bucket each visit into Low, Medium, or High cost categories, and reports the average cost and average stay within each bucket. This two-layer approach is necessary because you cannot nest aggregate functions directly.
Why use a derived subquery here?
You need to aggregate twice: first to get per-visit averages (because one visit has multiple treatments), then to average those per-visit values across cost categories. SQL doesn't allow AVG(AVG(...)) directly. The derived subquery solves this by computing the first aggregation, then handing a flat result set to the outer query for the second.
- Highlight Q3 and execute it.
- You should see 3 rows showing Low Cost, Medium Cost, and High Cost categories with their visit counts, average treatment costs, and average stay durations.
- Save this file as 04_intermediate_queries.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).
Getting "Every derived table must have its own alias"?
MySQL requires every subquery in a FROM clause to have an alias. Check that you have ) AS visit_costs at the end of the inner subquery's closing parenthesis.
Help me fix my derived subquery alias error
✔️ Awesome, I've got everything!
Great. Double check you've saved 04_intermediate_queries.sql with all three queries.
ⓧ I'd like to double check the full code
Here is the complete 04_intermediate_queries.sql file for comparison:
USE hospital_er;
-- Q1: Patient severity classification by wait time
SELECT
CASE
WHEN wait_time_minutes BETWEEN 0 AND 5 THEN 'Critical (0-5 min)'
WHEN wait_time_minutes BETWEEN 6 AND 15 THEN 'Urgent (6-15 min)'
WHEN wait_time_minutes BETWEEN 16 AND 30 THEN 'Standard (16-30 min)'
ELSE 'Low Priority (31+ min)'
END AS wait_category,
COUNT(*) AS visit_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM visits), 1) AS percentage
FROM visits
GROUP BY wait_category
ORDER BY MIN(wait_time_minutes);
-- Q2: Average ER stay by shift
SELECT
CASE
WHEN HOUR(admission_time) BETWEEN 6 AND 13 THEN 'Morning (6AM-2PM)'
WHEN HOUR(admission_time) BETWEEN 14 AND 21 THEN 'Afternoon (2PM-10PM)'
ELSE 'Night (10PM-6AM)'
END AS shift,
COUNT(*) AS visit_count,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, admission_time, discharge_time)), 0) AS avg_stay_minutes
FROM visits
WHERE discharge_time IS NOT NULL
GROUP BY shift
ORDER BY avg_stay_minutes DESC;
-- Q3: Cost vs outcome
SELECT
CASE
WHEN avg_cost < 300 THEN 'Low Cost (<$300)'
WHEN avg_cost BETWEEN 300 AND 1000 THEN 'Medium Cost ($300-$1000)'
ELSE 'High Cost (>$1000)'
END AS cost_category,
COUNT(*) AS visit_count,
ROUND(AVG(avg_cost), 2) AS avg_treatment_cost,
ROUND(AVG(stay_minutes), 0) AS avg_stay_minutes
FROM (
SELECT
v.visit_id,
AVG(t.treatment_cost) AS avg_cost,
TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time) AS stay_minutes
FROM visits v
JOIN treatments t ON v.visit_id = t.visit_id
WHERE v.discharge_time IS NOT NULL
GROUP BY v.visit_id, v.admission_time, v.discharge_time
) AS visit_costs
GROUP BY cost_category
ORDER BY AVG(avg_cost);You can now transform raw data into meaningful categories and compute time differences across timestamps. Next up, you'll use sub-queries and self-joins to compare individual performance against group averages.
Write Advanced Queries with Sub-queries and Self-Joins
Your CASE expressions and date arithmetic from the last step gave you powerful tools for classifying and measuring data. Now you are ready to ask questions that compare individual performance against group benchmarks.
Sub-queries let you embed one query inside another to compute dynamic thresholds. Self-joins let you compare rows within the same table to each other. Together, these techniques answer the comparative questions that stakeholders care about most.
In this step, get ready to:
- Write sub-queries that compare individual doctors to hospital-wide averages.
- Use correlated sub-queries to benchmark each doctor against their own department.
- Build a self-join that pairs junior doctors with senior mentors.
Find above-average performers with sub-queries
Leadership wants to know which doctors move patients through the ER faster than average. A hard-coded threshold would go stale the moment data changes. A sub-query calculates the hospital-wide average on the fly, so your comparison is always current.
- In MySQL Workbench, open a new query tab by clicking the New Query Tab button (the "+" icon above the SQL Editor area).
- Paste the following query into the new tab:
USE hospital_er;
-- ============================================
-- STEP 4: ADVANCED QUERIES - SUB-QUERIES & SELF-JOINS
-- ============================================
-- Q1: Doctors with average visit duration below hospital-wide average
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
d.department_name,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time)), 0) AS avg_duration
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
JOIN departments d ON doc.department_id = d.department_id
WHERE v.discharge_time IS NOT NULL
GROUP BY doc.doctor_id, doctor_name, d.department_name
HAVING AVG(TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time)) < (
SELECT AVG(TIMESTAMPDIFF(MINUTE, admission_time, discharge_time))
FROM visits
WHERE discharge_time IS NOT NULL
);What does this query do?
The outer query groups visits by doctor and calculates each doctor's average visit duration using TIMESTAMPDIFF.
The HAVING clause contains a scalar sub-query that independently calculates the hospital-wide average. MySQL runs that inner query once, then uses the result to filter each doctor's row. Only doctors whose average is below the hospital-wide number survive the filter.
- Click the lightning bolt icon (Execute) to run the query.
- You should see a results grid listing doctors whose average visit duration is shorter than the hospital-wide average. Each row shows the doctor name, department, and their average duration in minutes.
Seeing an error about unknown column?
- Make sure the sub-query inside the HAVING clause has its own WHERE discharge_time IS NOT NULL filter. Without it, NULL discharge times break the average calculation.
- Check that every opening parenthesis has a matching closing parenthesis. The sub-query needs one pair around it.
Why isn't my sub-query returning results in MySQL Workbench?
Write correlated sub-queries for comparative analysis
A regular sub-query runs once and returns a single value. A correlated sub-query runs once per row in the outer query, using data from the current row to filter its own results. This lets you ask per-row questions like "how does this doctor compare to their own department?"
- Add the following query below Q1 in your SQL Editor tab:
-- Q2: For each doctor, compare their avg treatment cost to department average
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
d.department_name,
ROUND(AVG(t.treatment_cost), 2) AS doctor_avg_cost,
(
SELECT ROUND(AVG(t2.treatment_cost), 2)
FROM treatments t2
JOIN visits v2 ON t2.visit_id = v2.visit_id
WHERE v2.department_id = doc.department_id
) AS dept_avg_cost,
CASE
WHEN AVG(t.treatment_cost) > (
SELECT AVG(t2.treatment_cost)
FROM treatments t2
JOIN visits v2 ON t2.visit_id = v2.visit_id
WHERE v2.department_id = doc.department_id
) THEN 'Above Average'
ELSE 'Below Average'
END AS comparison
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
JOIN treatments t ON v.visit_id = t.visit_id
JOIN departments d ON doc.department_id = d.department_id
GROUP BY doc.doctor_id, doc.first_name, doc.last_name, doc.department_id, d.department_name;What makes this a correlated sub-query?
The sub-query references doc.department_id from the outer query. This means MySQL re-runs the sub-query for each doctor, scoping the department average to that doctor's own department.
The CASE expression at the end labels each doctor as "Above Average" or "Below Average" relative to their department peers. This gives leadership an instant visual flag.
- Highlight just Q2 and click Execute (lightning bolt).
- You should see each doctor listed with their personal average cost, their department's average cost, and a comparison label.
Query runs but all doctors show the same dept_avg_cost?
- Check that the sub-query uses v2.department_id = doc.department_id to correlate. If you accidentally hard-coded a department number, every row gets the same value.
- Make sure doc.department_id is included in the GROUP BY clause. MySQL needs it there to resolve the reference inside the sub-query.
Help me debug my correlated sub-query for department cost comparison.
Now add a query that finds frequent visitors. This uses a nested sub-query to calculate the average number of visits per patient, then filters for patients above that threshold.
- Add Q3 below Q2 in the same file:
-- Q3: Patients who have visited more times than the average patient
SELECT
CONCAT(p.first_name, ' ', p.last_name) AS patient_name,
COUNT(v.visit_id) AS visit_count
FROM patients p
JOIN visits v ON p.patient_id = v.patient_id
GROUP BY p.patient_id, patient_name
HAVING COUNT(v.visit_id) > (
SELECT AVG(visit_count) FROM (
SELECT COUNT(*) AS visit_count
FROM visits
GROUP BY patient_id
) AS avg_visits
);Why is the sub-query nested two levels deep?
MySQL cannot use AVG(COUNT(*)) directly. You first need a derived table that counts visits per patient, then wrap that in AVG(). The inner-most query groups by patient and counts. The middle layer averages those counts. The outer query uses that average as its filter.
The alias avg_visits on the derived table is required by MySQL. Every derived table must have an alias even if you never reference it by name.
- Highlight Q3 and click Execute.
- You should see a list of patients who visited more than the average number of times. These are your frequent flyers whose outcomes and costs deserve closer inspection.
Getting "Every derived table must have its own alias"?
- Check that the innermost sub-query ends with ) AS avg_visits. If you omit the alias after the closing parenthesis, MySQL cannot reference the derived table.
Help me fix a derived table alias error in my nested sub-query.
Use a self-join to match junior and senior doctors
A self-join joins a table to itself using different aliases. This is useful when you need to compare rows within the same table. Here, you will pair each junior doctor with a senior mentor in the same department.
- Add Q4 below Q3 in your file:
-- Q4: Self-join - Pair junior doctors with senior mentors in same department
SELECT
CONCAT(junior.first_name, ' ', junior.last_name) AS junior_doctor,
junior.years_experience AS junior_exp,
CONCAT(senior.first_name, ' ', senior.last_name) AS senior_mentor,
senior.years_experience AS senior_exp,
d.department_name
FROM doctors junior
JOIN doctors senior
ON junior.department_id = senior.department_id
AND junior.doctor_id != senior.doctor_id
JOIN departments d ON junior.department_id = d.department_id
WHERE junior.years_experience < 5
AND senior.years_experience >= 10
ORDER BY d.department_name, junior.last_name;How does the self-join work?
- The doctors table is referenced twice with different aliases: junior and senior. MySQL treats them as two separate tables for the purpose of the join.
- The condition junior.department_id = senior.department_id ensures pairs are in the same department.
- The condition junior.doctor_id != senior.doctor_id prevents a doctor from being paired with themselves.
- The WHERE clause filters so that only doctors with less than 5 years experience appear as juniors, and only those with 10+ years appear as seniors.
- Highlight Q4 and click Execute.
- You should see pairings like Michael Rodriguez matched with Sarah Chen in Emergency Medicine, and Lisa Thompson matched with Robert Kim in Orthopedics.
Getting duplicate or zero rows?
- If you see zero rows, double-check the experience thresholds. The junior filter is < 5 and the senior filter is >= 10. Using <= 5 or > 10 changes which doctors qualify.
- If you see doctors paired with themselves, make sure you have junior.doctor_id != senior.doctor_id in the JOIN condition.
Help me debug my self-join on the doctors table.
- Save the file as 05_advanced_queries.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).
✔️ Awesome, I've got everything!
Great. Make sure all four queries are saved in the file.
ⓧ I'd like to double check the full code
Here is the complete 05_advanced_queries.sql file:
USE hospital_er;
-- ============================================
-- STEP 4: ADVANCED QUERIES - SUB-QUERIES & SELF-JOINS
-- ============================================
-- Q1: Doctors with average visit duration below hospital-wide average
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
d.department_name,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time)), 0) AS avg_duration
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
JOIN departments d ON doc.department_id = d.department_id
WHERE v.discharge_time IS NOT NULL
GROUP BY doc.doctor_id, doctor_name, d.department_name
HAVING AVG(TIMESTAMPDIFF(MINUTE, v.admission_time, v.discharge_time)) < (
SELECT AVG(TIMESTAMPDIFF(MINUTE, admission_time, discharge_time))
FROM visits
WHERE discharge_time IS NOT NULL
);
-- Q2: For each doctor, compare their avg treatment cost to department average
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
d.department_name,
ROUND(AVG(t.treatment_cost), 2) AS doctor_avg_cost,
(
SELECT ROUND(AVG(t2.treatment_cost), 2)
FROM treatments t2
JOIN visits v2 ON t2.visit_id = v2.visit_id
WHERE v2.department_id = doc.department_id
) AS dept_avg_cost,
CASE
WHEN AVG(t.treatment_cost) > (
SELECT AVG(t2.treatment_cost)
FROM treatments t2
JOIN visits v2 ON t2.visit_id = v2.visit_id
WHERE v2.department_id = doc.department_id
) THEN 'Above Average'
ELSE 'Below Average'
END AS comparison
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
JOIN treatments t ON v.visit_id = t.visit_id
JOIN departments d ON doc.department_id = d.department_id
GROUP BY doc.doctor_id, doc.first_name, doc.last_name, doc.department_id, d.department_name;
-- Q3: Patients who have visited more times than the average patient
SELECT
CONCAT(p.first_name, ' ', p.last_name) AS patient_name,
COUNT(v.visit_id) AS visit_count
FROM patients p
JOIN visits v ON p.patient_id = v.patient_id
GROUP BY p.patient_id, patient_name
HAVING COUNT(v.visit_id) > (
SELECT AVG(visit_count) FROM (
SELECT COUNT(*) AS visit_count
FROM visits
GROUP BY patient_id
) AS avg_visits
);
-- Q4: Self-join - Pair junior doctors with senior mentors in same department
SELECT
CONCAT(junior.first_name, ' ', junior.last_name) AS junior_doctor,
junior.years_experience AS junior_exp,
CONCAT(senior.first_name, ' ', senior.last_name) AS senior_mentor,
senior.years_experience AS senior_exp,
d.department_name
FROM doctors junior
JOIN doctors senior
ON junior.department_id = senior.department_id
AND junior.doctor_id != senior.doctor_id
JOIN departments d ON junior.department_id = d.department_id
WHERE junior.years_experience < 5
AND senior.years_experience >= 10
ORDER BY d.department_name, junior.last_name;You now have queries that dynamically benchmark individuals against groups and compare rows within the same table. In the next step, you will combine everything into stakeholder-ready queries that flag problems and quantify them.
Build the Stakeholder Report
In the last step, you built advanced queries using sub-queries, correlated sub-queries, and self-joins. You can now compare individual performance against group averages and match rows within the same table.
Now it's time to combine everything into queries that answer the questions leadership actually cares about. This is where SQL moves from a technical exercise to a business tool. Each query in this step synthesizes multiple techniques into a single, actionable finding.
In this step, get ready to:
- Write a "Problem Doctors" query that flags underperformers using dual HAVING sub-queries.
- Write a "Money Leak" query that identifies billing inflation using derived sub-queries and CASE.
- Document your findings with SQL comments and a summary metrics dashboard.
Write the "Problem Doctors" query
Hospital leadership wants to know which doctors might need workflow support. The signal is a combination of two factors: their patients wait longer than the department average, AND they see fewer patients than the hospital average.
- Create a new file called 06_stakeholder_report.sql in the SQL Editor by clicking the new file icon in the toolbar.
- Paste the following code to start the file and write the first stakeholder query:
USE hospital_er;
-- ============================================
-- STEP 5: STAKEHOLDER REPORT
-- ============================================
-- FINDING: "Problem Doctors" - underperformers with high wait times and low volume
-- Recommendation: These doctors may need additional training or workflow optimization
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
d.department_name,
COUNT(v.visit_id) AS total_visits,
ROUND(AVG(v.wait_time_minutes), 1) AS avg_wait_time
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
JOIN departments d ON doc.department_id = d.department_id
GROUP BY doc.doctor_id, doctor_name, d.department_name, doc.department_id
HAVING AVG(v.wait_time_minutes) > (
SELECT AVG(v2.wait_time_minutes)
FROM visits v2
WHERE v2.department_id = doc.department_id
)
AND COUNT(v.visit_id) < (
SELECT AVG(doc_visits) FROM (
SELECT COUNT(*) AS doc_visits
FROM visits
GROUP BY doctor_id
) AS avg_doc_volume
)
ORDER BY avg_wait_time DESC;What does this query do?
This query uses two sub-queries inside the HAVING clause. Each one filters from a different angle:
- The first sub-query (SELECT AVG(v2.wait_time_minutes) ... WHERE v2.department_id = doc.department_id) calculates the average wait time for each doctor's own department. This is a correlated sub-query because it references the outer query's doc.department_id.
- The second sub-query calculates the average number of visits per doctor across the entire hospital. It uses a derived table to first count visits per doctor, then averages those counts.
- Combined with AND, these two conditions only return doctors who are BOTH slow (above-average wait) AND low-volume (below-average visits). Either condition alone could be misleading.
- Execute the query by clicking the lightning bolt icon or pressing Ctrl+Shift+Enter (Windows) or Cmd+Shift+Enter (macOS).
- Check your Results Grid. You should see a short list of doctors with their department, total visits, and average wait time.
Getting an "Unknown column" error?
- Make sure doc.department_id is included in the GROUP BY clause. Without it, MySQL cannot resolve the correlated sub-query reference.
- Check that both sub-queries inside HAVING are connected by AND, not OR. A missing AND causes a syntax error.
- Confirm you have USE hospital_er; at the top of your file.
Help me debug my Problem Doctors query
Write the "Money Leak" query
The finance team suspects that some diagnoses are being billed far above their actual treatment cost. They also want to know which diagnoses have unpaid bills piling up. This query surfaces both problems.
- Add the following query below your Problem Doctors query in 06_stakeholder_report.sql:
-- FINDING: "Money Leak" - diagnoses with billing inflation or high unpaid rates
-- Recommendation: Review billing practices for flagged diagnoses
SELECT
v.diagnosis,
COUNT(v.visit_id) AS total_cases,
ROUND(AVG(b.total_amount), 2) AS avg_billed,
ROUND(AVG(total_treatment_cost), 2) AS avg_treatment_cost,
ROUND(AVG(b.total_amount) - AVG(total_treatment_cost), 2) AS avg_markup,
ROUND(
SUM(CASE WHEN b.payment_status IN ('Overdue', 'Pending') THEN 1 ELSE 0 END) * 100.0
/ COUNT(*), 1
) AS pct_unpaid
FROM visits v
JOIN billing b ON v.visit_id = b.visit_id
JOIN (
SELECT visit_id, SUM(treatment_cost) AS total_treatment_cost
FROM treatments
GROUP BY visit_id
) t ON v.visit_id = t.visit_id
GROUP BY v.diagnosis
HAVING COUNT(v.visit_id) >= 2
ORDER BY avg_markup DESC;What does this query do?
This query joins three sources and uses a derived sub-query as one of them:
- The derived sub-query (SELECT visit_id, SUM(treatment_cost) ... GROUP BY visit_id) pre-calculates the total treatment cost per visit. This avoids double-counting when a visit has multiple treatments.
- The CASE expression inside SUM counts how many bills are Overdue or Pending, then divides by total count to get a percentage.
- The avg_markup column shows the gap between what was billed and what treatments actually cost. A high markup signals potential billing inflation.
- HAVING COUNT >= 2 filters out one-off diagnoses where a single data point would be misleading.
- Highlight just the Money Leak query and execute it.
- Check your Results Grid. You should see diagnoses ordered by markup amount, with columns for avg_billed, avg_treatment_cost, avg_markup, and pct_unpaid.
Seeing NULL values in avg_treatment_cost?
- Make sure the derived sub-query alias is t and that you join it with ON v.visit_id = t.visit_id. A mismatched alias causes the join to silently return NULLs.
- Confirm that the derived sub-query uses SUM(treatment_cost) not AVG(treatment_cost). You want the total cost per visit, not the average across individual treatments.
Help me debug my Money Leak query
Document your findings with a summary dashboard
Real analysts don't just hand over raw query output. They add context: what did you find, and what do you recommend? The SQL comments you've already written above each query serve this purpose.
The final piece is a summary dashboard that gives leadership a snapshot of key hospital metrics in one result set.
- Add the following summary metrics query at the bottom of 06_stakeholder_report.sql:
-- FINDING: Summary of key metrics
-- Recommendation: Focus on night shift staffing and readmission prevention
SELECT
'Total Visits' AS metric, CAST(COUNT(*) AS CHAR) AS value FROM visits
UNION ALL
SELECT
'Avg Wait Time (min)', CAST(ROUND(AVG(wait_time_minutes), 1) AS CHAR) FROM visits
UNION ALL
SELECT
'Avg Stay (min)', CAST(ROUND(AVG(TIMESTAMPDIFF(MINUTE, admission_time, discharge_time)), 0) AS CHAR)
FROM visits WHERE discharge_time IS NOT NULL
UNION ALL
SELECT
'Total Revenue', CONCAT('$', FORMAT(SUM(total_amount), 2)) FROM billing
UNION ALL
SELECT
'Unpaid Bills %', CONCAT(ROUND(SUM(CASE WHEN payment_status IN ('Overdue', 'Pending') THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1), '%') FROM billing;What does this query do?
This query uses UNION ALL to stack multiple single-row aggregates into one vertical result set. Each SELECT produces one metric row.
- CAST and CONCAT convert numeric results to strings so all rows share the same column types (required by UNION ALL).
- FORMAT adds thousand-separators to the revenue total for readability.
- The result looks like a mini-dashboard: one column for the metric name, one for its value.
Why document findings as SQL comments?
In professional analytics work, your SQL files are often the deliverable. Leadership won't run the queries themselves. They read the comments to understand your findings and recommendations, then verify by looking at the result sets you share.
The pattern is: FINDING (what the data shows) followed by Recommendation (what to do about it). This turns a technical query into an actionable insight.
- Save 06_stakeholder_report.sql.
- Highlight the summary metrics query and execute it. You should see a two-column result with 5 rows: Total Visits, Avg Wait Time (min), Avg Stay (min), Total Revenue, and Unpaid Bills %.
Seeing a "column count doesn't match" error?
- Every SELECT in a UNION ALL must return the same number of columns. Make sure each one returns exactly two columns: the metric name (string) and the value (string).
- If you see type mismatch warnings, check that all values are wrapped in CAST or CONCAT to produce string output.
Help me fix my UNION ALL summary query
✔️ Awesome, I've got everything!
Great. Double check you've saved 06_stakeholder_report.sql.
ⓧ I'd like to double check the full code
USE hospital_er;
-- ============================================
-- STEP 5: STAKEHOLDER REPORT
-- ============================================
-- FINDING: "Problem Doctors" - underperformers with high wait times and low volume
-- Recommendation: These doctors may need additional training or workflow optimization
SELECT
CONCAT(doc.first_name, ' ', doc.last_name) AS doctor_name,
d.department_name,
COUNT(v.visit_id) AS total_visits,
ROUND(AVG(v.wait_time_minutes), 1) AS avg_wait_time
FROM doctors doc
JOIN visits v ON doc.doctor_id = v.doctor_id
JOIN departments d ON doc.department_id = d.department_id
GROUP BY doc.doctor_id, doctor_name, d.department_name, doc.department_id
HAVING AVG(v.wait_time_minutes) > (
SELECT AVG(v2.wait_time_minutes)
FROM visits v2
WHERE v2.department_id = doc.department_id
)
AND COUNT(v.visit_id) < (
SELECT AVG(doc_visits) FROM (
SELECT COUNT(*) AS doc_visits
FROM visits
GROUP BY doctor_id
) AS avg_doc_volume
)
ORDER BY avg_wait_time DESC;
-- FINDING: "Money Leak" - diagnoses with billing inflation or high unpaid rates
-- Recommendation: Review billing practices for flagged diagnoses
SELECT
v.diagnosis,
COUNT(v.visit_id) AS total_cases,
ROUND(AVG(b.total_amount), 2) AS avg_billed,
ROUND(AVG(total_treatment_cost), 2) AS avg_treatment_cost,
ROUND(AVG(b.total_amount) - AVG(total_treatment_cost), 2) AS avg_markup,
ROUND(
SUM(CASE WHEN b.payment_status IN ('Overdue', 'Pending') THEN 1 ELSE 0 END) * 100.0
/ COUNT(*), 1
) AS pct_unpaid
FROM visits v
JOIN billing b ON v.visit_id = b.visit_id
JOIN (
SELECT visit_id, SUM(treatment_cost) AS total_treatment_cost
FROM treatments
GROUP BY visit_id
) t ON v.visit_id = t.visit_id
GROUP BY v.diagnosis
HAVING COUNT(v.visit_id) >= 2
ORDER BY avg_markup DESC;
-- FINDING: Summary of key metrics
-- Recommendation: Focus on night shift staffing and readmission prevention
SELECT
'Total Visits' AS metric, CAST(COUNT(*) AS CHAR) AS value FROM visits
UNION ALL
SELECT
'Avg Wait Time (min)', CAST(ROUND(AVG(wait_time_minutes), 1) AS CHAR) FROM visits
UNION ALL
SELECT
'Avg Stay (min)', CAST(ROUND(AVG(TIMESTAMPDIFF(MINUTE, admission_time, discharge_time)), 0) AS CHAR)
FROM visits WHERE discharge_time IS NOT NULL
UNION ALL
SELECT
'Total Revenue', CONCAT('$', FORMAT(SUM(total_amount), 2)) FROM billing
UNION ALL
SELECT
'Unpaid Bills %', CONCAT(ROUND(SUM(CASE WHEN payment_status IN ('Overdue', 'Pending') THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1), '%') FROM billing;Secret mission
30-Day Readmission Alert
Hospital readmissions within 30 days are a red flag for treatment failure or complications. Can you build a query that catches these patterns before they become a crisis?
Clean Up Your Resources
Clean Up Your Resources
This project runs entirely locally on your MySQL server, so there are no ongoing costs. Decide whether to keep your database for future practice, or delete it to free up disk space.
Resources you used:
- The hospital_er database on your local MySQL server (6 tables, ~100 rows of sample data).
- 7 SQL script files (01_create_schema.sql through 07_readmission_alert.sql).
Keep everything running
No action needed. Choose this if you want to keep practicing queries or extending the database with new tables and data.
- Your hospital_er database stays on your local MySQL server and takes up minimal disk space.
- Your SQL script files remain wherever you saved them on your computer.
Pause - I'll come back to this later
Since MySQL runs locally, there is nothing to pause. The database only uses resources when you actively run queries against it.
- Close MySQL Workbench when you are done working.
- Your database and SQL files will be exactly as you left them when you come back.
Delete - I don't want to use this again
Remove all project resources and start fresh. This permanently deletes the database and all its data.
- Drop the database by running the following command in MySQL Workbench's SQL Editor:
DROP DATABASE hospital_er;- Delete your SQL script files. On macOS or Linux, open your terminal and navigate to the folder where you saved them, then run:
rm 01_create_schema.sql 02_insert_data.sql 03_foundation_queries.sql 04_intermediate_queries.sql 05_advanced_queries.sql 06_stakeholder_report.sql 07_readmission_alert.sql- On Windows, open PowerShell, navigate to the folder where you saved them, then run:
Remove-Item 01_create_schema.sql, 02_insert_data.sql, 03_foundation_queries.sql, 04_intermediate_queries.sql, 05_advanced_queries.sql, 06_stakeholder_report.sql, 07_readmission_alert.sqlNice Work!
Nice Work!
You just built a full hospital ER analytics database and used it to answer the kind of messy, real-world questions that leadership actually asks. That is exactly the skill that makes a data analyst valuable in any industry.
Here's what you learned:
- Built a 6-table normalized database with foreign key relationships and loaded realistic sample data spanning 6 months of ER operations.
- Wrote analytical queries using JOINs, sub-queries, CASE expressions, self-joins, and date arithmetic to translate vague business questions into precise SQL.
- Combined all techniques into stakeholder-ready queries that identify underperforming doctors, flag billing inflation, and summarize key hospital metrics.
- Secret Mission: Built a 30-day readmission alert system using self-joins, DATEDIFF, and CASE to flag patients returning within 30 days of discharge.
Ready to quiz yourself?