Hospital Management Database SQL JOIN Practice with 30 Challenges

Theory and isolated examples are essential, but true JOIN mastery comes from applying every JOIN type — INNER, LEFT, RIGHT, SELF, and multi-table combinations — against a single, coherent, realistic database schema that mirrors what you would actually encounter in a job or interview setting. This lesson provides a complete hospital management database schema and 30 progressively challenging JOIN exercises built directly on top of it, functioning as both a mini-project and a comprehensive JOIN practice set.

Hospital management systems are an excellent practice domain because they naturally involve nearly every relationship pattern covered in this module: one-to-many relationships (one doctor treats many patients), many-to-many relationships (many patients can have many appointments across many doctors), and even self-referencing hierarchies (a doctor supervising other doctors), making this schema uniquely suited to reinforce the entire JOIN module in one connected project.

Key Definitions

• Schema: The overall structure of a database, including its tables, columns, and the relationships between them.
• One-to-many relationship: A relationship where one row in a table can relate to many rows in another table, such as one doctor treating many patients.
• Many-to-many relationship: A relationship where many rows in one table can relate to many rows in another, typically implemented using a junction table, such as patients and doctors connected through an appointments table.
• Mini-project: A focused, self-contained practical exercise designed to apply multiple concepts together against a single realistic scenario.

What You'll Learn

• Design and populate a realistic multi-table hospital management database schema.
• Apply INNER JOIN, LEFT JOIN, SELF JOIN, and multi-table joins to real, interconnected healthcare data.
• Practice combining JOIN with aggregate functions to answer hospital operations questions.
• Solve 30 progressively difficult challenges ranging from beginner to advanced JOIN scenarios.
• Build confidence applying JOIN concepts to an unfamiliar schema, a key interview and real-job skill.

Detailed Explanation

This practice project uses five interconnected tables: doctors (doctor_id, doctor_name, specialization, supervisor_id), patients (patient_id, patient_name, date_of_birth, city), appointments (appointment_id, patient_id, doctor_id, appointment_date, status), treatments (treatment_id, appointment_id, treatment_name, cost), and departments (department_id, department_name, doctor_id as head_of_department). Notice that doctors.supervisor_id is a self-referencing foreign key, ideal for practicing SELF JOIN, while appointments acts as a junction table connecting patients and doctors in a many-to-many relationship.

The 30 challenges below are organized into three difficulty tiers of ten questions each. Beginner challenges (1-10) focus on straightforward two-table INNER and LEFT JOINs, such as listing every patient's appointment history. Intermediate challenges (11-20) require multi-table joins and aggregate functions, such as calculating total treatment cost per doctor. Advanced challenges (21-30) combine SELF JOIN, Non-Equi JOIN concepts, and multi-condition joins, such as finding doctors who supervise more than two other doctors, or identifying appointment scheduling conflicts.

For each challenge, learners should first attempt to write the query independently, then compare against the provided example solution pattern, focusing not just on getting a correct answer but on understanding why that particular JOIN type and structure was the right choice for that specific question.

Visual Summary

Draw an entity-relationship diagram with five boxes: 'doctors' (with a curved self-referencing arrow from supervisor_id back to doctor_id), 'departments' (linked to doctors via head_of_department), 'patients', 'appointments' (in the center, connected to both patients and doctors, acting as the junction table), and 'treatments' (connected to appointments). Label each connecting line with the relevant foreign key.

Quick Reference

Tier	Challenge Range	Focus	Example Challenge
Beginner	1–10	Two-table INNER/LEFT JOIN	List every patient with their appointment dates
Intermediate	11–20	Multi-table joins + aggregates	Total treatment cost per doctor specialization
Advanced	21–30	SELF JOIN, complex conditions	Doctors supervising more than 2 other doctors

SQL Example

-- Full schema setup for the hospital management practice project
CREATE TABLE doctors (
  doctor_id      INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name    VARCHAR(100) NOT NULL,
  specialization VARCHAR(100),
  supervisor_id  INT,
  FOREIGN KEY (supervisor_id) REFERENCES doctors(doctor_id)
);

CREATE TABLE patients (
  patient_id     INT PRIMARY KEY AUTO_INCREMENT,
  patient_name   VARCHAR(100) NOT NULL,
  date_of_birth  DATE,
  city           VARCHAR(100)
);

CREATE TABLE appointments (
  appointment_id   INT PRIMARY KEY AUTO_INCREMENT,
  patient_id       INT,
  doctor_id        INT,
  appointment_date DATE,
  status           VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

CREATE TABLE treatments (
  treatment_id     INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id   INT,
  treatment_name   VARCHAR(150),
  cost             DECIMAL(10,2),
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

-- CHALLENGE 1 (Beginner): List every patient with their appointment dates
SELECT p.patient_name, a.appointment_date
FROM patients p
JOIN appointments a ON p.patient_id = a.patient_id;

-- CHALLENGE 11 (Intermediate): Total treatment cost per doctor
SELECT d.doctor_name, SUM(t.cost) AS total_treatment_cost
FROM doctors d
JOIN appointments a ON d.doctor_id = a.doctor_id
JOIN treatments t ON a.appointment_id = t.appointment_id
GROUP BY d.doctor_name;

-- CHALLENGE 21 (Advanced): Doctors supervising more than 2 other doctors
SELECT s.doctor_name AS supervisor, COUNT(d.doctor_id) AS supervised_count
FROM doctors d
JOIN doctors s ON d.supervisor_id = s.doctor_id
GROUP BY s.doctor_name
HAVING COUNT(d.doctor_id) > 2;

-- CHALLENGE 30 (Advanced): Patients who have never had an appointment
SELECT p.patient_name
FROM patients p
LEFT JOIN appointments a ON p.patient_id = a.patient_id
WHERE a.appointment_id IS NULL;

The schema models a realistic hospital system with self-referencing doctor supervision, a patient-doctor many-to-many relationship through appointments, and a downstream treatments table. Challenge 1 demonstrates a basic INNER JOIN, Challenge 11 layers a multi-table join with SUM and GROUP BY to compute per-doctor treatment costs, Challenge 21 applies SELF JOIN combined with GROUP BY and HAVING to find doctors supervising more than two others, and Challenge 30 uses the LEFT JOIN anti-join pattern to find patients with zero appointments — together spanning the full range of concepts covered across this module.

Real-World Examples

• Real hospital management systems use nearly this exact schema structure to power patient portals, doctor scheduling dashboards, and billing systems.
• Healthcare analytics teams run queries structurally identical to these challenges to measure doctor workload, patient no-show rates, and treatment cost trends.
• Hospital administrators use self-join-based supervisor reports to manage staffing hierarchies and department oversight structures.
• Insurance companies processing hospital billing claims rely on multi-table joins across appointments, treatments, and patients to validate and process claims accurately.
• This schema pattern (entities connected through a junction table with a self-referencing hierarchy) recurs across countless real-world domains beyond healthcare, including education, logistics, and corporate management systems.

Common Mistakes to Avoid

• Attempting to find patients without appointments using INNER JOIN, which would exclude exactly the rows the question is asking for.
• Forgetting to alias the doctors table twice when writing the SELF JOIN for the supervisor relationship.
• Miscounting treatment costs by joining tables in the wrong order or forgetting to include the treatments table entirely.
• Using COUNT(*) instead of COUNT(DISTINCT ...) in scenarios where the join has multiplied rows, leading to inflated counts.
• Not testing each challenge query against edge cases, such as a doctor with zero supervised doctors or a patient with zero appointments.

Interview Questions

Q1. Why is a hospital management schema a good practice ground for SQL JOIN concepts?

It naturally contains one-to-many relationships (a doctor with many appointments), many-to-many relationships through a junction table (patients and doctors connected via appointments), and a self-referencing hierarchy (doctors supervising other doctors), covering nearly every JOIN pattern in a single coherent schema.

Q2. How would you find doctors who have never had any appointments in this schema?

Use a LEFT JOIN from doctors to appointments on doctor_id, then filter with WHERE appointments.appointment_id IS NULL, which isolates doctors with no matching appointment rows — the standard anti-join pattern.

Q3. How would you calculate the total treatment revenue generated by each doctor?

Join doctors to appointments and then to treatments, group the combined result by doctor_name, and apply SUM on the treatment cost column to get one total revenue figure per doctor.

Q4. How would you find doctors who supervise more than a certain number of other doctors?

Perform a SELF JOIN on the doctors table matching supervisor_id to doctor_id, group the result by the supervisor's name, use COUNT to tally supervised doctors per group, and apply a HAVING clause to filter for counts exceeding the desired threshold.

Practice MCQs

1. In this hospital schema, which table acts as the many-to-many junction between patients and doctors?

1. treatments
2. departments
3. appointments
4. doctors

Answer: C. appointments

Explanation: appointments connects patients and doctors, since one patient can have many appointments with many different doctors, and one doctor can have many appointments with many different patients.

2. Which JOIN type is required to model the doctors.supervisor_id relationship?

1. CROSS JOIN
2. SELF JOIN
3. NATURAL JOIN
4. Non-Equi JOIN

Answer: B. SELF JOIN

Explanation: supervisor_id references another row's doctor_id within the same doctors table, which is precisely the self-referencing relationship SELF JOIN is designed to handle.

3. To find patients who have never had an appointment, which pattern is used?

1. INNER JOIN with GROUP BY
2. LEFT JOIN combined with WHERE appointments.appointment_id IS NULL
3. CROSS JOIN with DISTINCT
4. RIGHT JOIN with ORDER BY

Answer: B. LEFT JOIN combined with WHERE appointments.appointment_id IS NULL

Explanation: This is the standard anti-join pattern: LEFT JOIN preserves all patients, and filtering for a NULL appointment column isolates those with no matching appointment.

4. To calculate total treatment cost per doctor, how many tables must be joined?

1. One
2. Two
3. Three (doctors, appointments, treatments)
4. Five

Answer: C. Three (doctors, appointments, treatments)

Explanation: Treatment cost is stored in the treatments table, which links to appointments, which in turn links to doctors — requiring all three tables to be joined together to connect a doctor to their treatment costs.

5. Which SQL clause is needed to filter for doctors supervising more than 2 other doctors after a SELF JOIN and GROUP BY?

1. WHERE
2. HAVING
3. ON
4. ORDER BY

Answer: B. HAVING

Explanation: Since the condition (supervised count greater than 2) depends on an aggregated COUNT value, HAVING is required rather than WHERE, which cannot filter on aggregate results.

Quick Revision Points

• This hospital schema exercises INNER JOIN, LEFT JOIN, SELF JOIN, multi-table joins, and JOIN with aggregate functions together.
• appointments functions as the many-to-many junction table connecting patients and doctors.
• doctors.supervisor_id is a self-referencing foreign key, the classic real-world SELF JOIN scenario beyond the employee-manager example.
• The anti-join pattern (LEFT JOIN + WHERE ... IS NULL) is directly reusable here to find patients or doctors with no matching appointments.

Conclusion

• Practicing JOIN concepts against one coherent, realistic schema builds far deeper understanding than isolated two-table examples.
• Hospital management systems naturally exercise nearly every JOIN pattern covered in this module in a single connected project.
• Working through progressively harder challenges (beginner to advanced) mirrors how real interviews and job tasks escalate in complexity.
• This project can serve as a portfolio piece demonstrating comprehensive, applied SQL JOIN skills to potential employers.

This mini-project applies every JOIN concept covered in this module — INNER JOIN, LEFT JOIN, SELF JOIN, multi-table joins, and JOIN with aggregate functions — to a single, realistic hospital management database schema involving doctors, patients, appointments, treatments, and departments. Through 30 progressively challenging exercises organized into beginner, intermediate, and advanced tiers, learners move from simple two-table joins to complex multi-condition, self-referencing queries, building the kind of practical, applied SQL fluency that real interviews and real jobs actually demand.