ASAmol Shukla

16. Real life: hospital records

Step 1 of 4 · Lesson

Lesson 16 of 21

Real life database practice: hospital records (50 SQL problems)

Practise 50 SQL problems on a 2,000-appointment hospital database — filtering, joins, grouping and window functions, checked in your browser.

200 min 3 min read 50 problems · 0 solved City Hospital (2,000 appointments)

In short

What does the hospital practice database contain?

Three related tables: 600 patients, 60 doctors in six departments, and 2,000 appointments that link a patient to a doctor with a date, status, diagnosis, amount and rating. Every practice problem runs on it in the browser, with the database already selected.

By the end of this lesson you can

  • Filter and sort 2,000 appointments without scrolling the grid
  • Join appointments to patients and doctors without double counting
  • Report per department and per month with GROUP BY and HAVING
  • Use window functions for rankings, running totals and month-over-month change

The database you are practising on

City Hospital is a fictional multi-speciality hospital. Patients register once, doctors sit in six departments, and every visit becomes one appointment that is completed, cancelled or missed. The appointments table holds 2,000 rows across two years, so the answers are too big to eyeball — you have to let SQL count for you.

The whole set is fictional but shaped like the real thing: keys, foreign keys, dates, statuses and 2,000 rows in the busiest table. You do not import anything to start — practice mode loads the same rows into your browser. When you want the same data in MySQL Workbench, download the script (3 tables, 20 columns) and run it there.

What one row means

Read the grain before you write a join. Joining two tables without knowing what one row of each means is the single most common cause of doubled totals.

Table One row is Columns you will use
patients one registered patient patient_id, patient_name, gender, age, city, phone, registered_on, blood_group
doctors one doctor doctor_id, doctor_name, department, city, fee, joined_on
appointments one visit to one doctor appointment_id, patient_id, doctor_id, appointment_date, status, diagnosis, amount, rating

Your database is already selected

Every problem below runs against this database only. The code arena preselects it and does not show a database dropdown, so you never see MySQL's ERROR 1046: No database selected here.

You write one query, press Check answer, and it is compared row by row with the reference answer — same rows, same columns, any valid query wins. Use Hint first, Compare with the reference answer once you have tried, and Reset database if you changed rows while experimenting.

Common mistakes

Wrong

WHERE COUNT(*) > 5

Fix

Move the condition to HAVING COUNT(*) > 5.

Why: WHERE runs before grouping, so an aggregate is not allowed there (MySQL error 1111).

Wrong

JOIN doctors without ON, then SUM(amount)

Fix

Always join with ON a.doctor_id = d.doctor_id and check COUNT(*) before trusting SUM.

Why: A missing join condition pairs every visit with every doctor and multiplies revenue.

Wrong

Assuming a NULL rating is a zero rating

Fix

Filter to completed rows, or use COUNT(rating) to see how many ratings the average is based on.

Why: Only completed visits are rated; AVG and COUNT ignore NULL correctly, but AVG(rating) over all rows is not an average of visits.

Check yourself

  1. Can you say in one sentence what one row of patients means?
  2. Can you write a query that returns exactly one number from a 2,000-row table without scrolling the result?
  3. Can you explain why two of your joins could double a total, and how you avoided it?

Answer out loud or in your notebook. If one is hard, reread that section before practising.

Frequently asked questions

Do I need to install MySQL and import this data?

No. The code arena loads the hospital database in your browser and preselects it for every problem. The .sql download is there for when you want to run the same queries in Workbench.

Are the answers checked automatically?

Yes. Your query and the reference answer run on fresh copies of the same 2,000 rows and the results are compared row by row, so any correct query is accepted.

Is the hospital data real?

No. It is fictional, generated from a fixed seed so every learner sees exactly the same rows and the same answers.

Quick quiz — 3 questions

Quick Check: Real life: hospital records

1 / 3
Why does filtering `status = 'completed'` happen before the SUM?

Answers — Real life: hospital records

  1. Easy. List every doctor with their name and department. Target: 60 rows · doctor_name, department · (Dr Ira Rao, Dermatology); (Dr Gurleen Reddy, Orthopaedics) …

    SELECT doctor_name, department FROM doctors ORDER BY doctor_id;

  2. Easy. List the 20 oldest patients. Target: 20 rows · patient_id, patient_name, age · (50, Kavya Khan, 88); (119, Riya Nair, 88) …

    SELECT patient_id, patient_name, age FROM patients ORDER BY age DESC, patient_id LIMIT 20;

  3. Easy. Show the 10 most recent appointments. Target: 10 rows · appointment_id, appointment_date, status · (760, 2025-12-29, completed); (333, 2025-12-29, completed) …

    SELECT appointment_id, appointment_date, status FROM appointments ORDER BY appointment_date DESC, appointment_id DESC LIMIT 10;

  4. Easy. List female patients living in Pune. Target: 31 rows · patient_id, patient_name, age · (46, Zara Verma, 61); (53, Anaya Reddy, 43) …

    SELECT patient_id, patient_name, age FROM patients WHERE gender = 'Female' AND city = 'Pune' ORDER BY patient_id;

  5. Easy. List doctors who charge more than 1,500. Target: 25 rows · doctor_id, doctor_name, fee · (17, Dr Aarav Gupta, 2500); (24, Dr Diya Rao, 2500) …

    SELECT doctor_id, doctor_name, fee FROM doctors WHERE fee > 1500 ORDER BY fee DESC, doctor_id;

  6. Easy. Show the first 20 cancelled appointments. Target: 20 rows · appointment_id, patient_id, appointment_date · (16, 438, 2025-01-08); (19, 94, 2024-06-12) …

    SELECT appointment_id, patient_id, appointment_date FROM appointments WHERE status = 'cancelled' ORDER BY appointment_id LIMIT 20;

  7. Easy. List patients who registered during 2025. Target: 20 rows · patient_id, patient_name, registered_on · (1, Saanvi Rao, 2025-06-02); (4, Rahul Bose, 2025-03-28) …

    SELECT patient_id, patient_name, registered_on FROM patients WHERE registered_on >= '2025-01-01' AND registered_on < '2026-01-01' ORDER BY patient_id LIMIT 20;

  8. Easy. List each department once. Target: 6 rows · department · (Cardiology); (Dermatology) …

    SELECT DISTINCT department FROM doctors ORDER BY department;

  9. Easy. Show appointments that were rated 5. Target: 20 rows · appointment_id, doctor_id, rating · (7, 52, 5); (13, 9, 5) …

    SELECT appointment_id, doctor_id, rating FROM appointments WHERE rating = 5 ORDER BY appointment_id LIMIT 20;

  10. Easy. Count all patients. Target: 1 row · total_patients · (600)

    SELECT COUNT(*) AS total_patients FROM patients;

  11. Medium. List patients aged 30 to 40 who live in Mumbai. Target: 5 rows · patient_id, patient_name, age · (6, Arjun Bose, 35); (95, Aditi Nair, 38) …

    SELECT patient_id, patient_name, age FROM patients WHERE age BETWEEN 30 AND 40 AND city = 'Mumbai' ORDER BY patient_id LIMIT 20;

  12. Medium. List Cardiology and Neurology doctors, most expensive first. Target: 13 rows · doctor_name, department, fee · (Dr Harpreet Kaur, Neurology, 2300); (Dr Aman Bose, Neurology, 2200) …

    SELECT doctor_name, department, fee FROM doctors WHERE department IN ('Cardiology', 'Neurology') ORDER BY fee DESC, doctor_id;

  13. Medium. List patients whose first name starts with A. Target: 20 rows · patient_id, patient_name · (6, Arjun Bose); (11, Arjun Rao) …

    SELECT patient_id, patient_name FROM patients WHERE patient_name LIKE 'A%' ORDER BY patient_id LIMIT 20;

  14. Medium. List patients from Pune, Mumbai or Delhi. Target: 20 rows · patient_id, patient_name, city · (1, Saanvi Rao, Mumbai); (3, Harpreet Nair, Mumbai) …

    SELECT patient_id, patient_name, city FROM patients WHERE city IN ('Pune', 'Mumbai', 'Delhi') ORDER BY patient_id LIMIT 20;

  15. Medium. List appointments in January 2025. Target: 83 rows · appointment_id, appointment_date · (120, 2025-01-01); (638, 2025-01-01) …

    SELECT appointment_id, appointment_date FROM appointments WHERE appointment_date >= '2025-01-01' AND appointment_date < '2025-02-01' ORDER BY appointment_date, appointment_id;

  16. Medium. Show each patient's name in capitals next to their city. Target: 20 rows · patient, city · (SAANVI RAO, Mumbai); (SIMRAN GILL, Ahmedabad) …

    SELECT UPPER(patient_name) AS patient, city FROM patients ORDER BY patient_id LIMIT 20;

  17. Medium. Show each appointment with its year, for the first 20 rows. Target: 20 rows · appointment_id, appointment_date, appointment_year · (1, 2025-01-08, 2025); (2, 2025-06-22, 2025) …

    SELECT appointment_id, appointment_date, YEAR(appointment_date) AS appointment_year FROM appointments ORDER BY appointment_id LIMIT 20;

  18. Medium. Label every patient as 'Child', 'Adult' or 'Senior'. Target: 20 rows · patient_id, patient_name, age, age_band · (1, Saanvi Rao, 23, Adult); (2, Simran Gill, 7, Child) …

    SELECT patient_id, patient_name, age, CASE WHEN age < 18 THEN 'Child' WHEN age < 60 THEN 'Adult' ELSE 'Senior' END AS age_band FROM patients ORDER BY patient_id LIMIT 20;

  19. Medium. List appointments that have no diagnosis. Target: 20 rows · appointment_id, status, diagnosis · (16, cancelled, NULL); (19, cancelled, NULL) …

    SELECT appointment_id, status, diagnosis FROM appointments WHERE diagnosis IS NULL ORDER BY appointment_id LIMIT 20;

  20. Medium. List doctors who joined before 2015. Target: 25 rows · doctor_name, joined_on · (Dr Zara Gupta, 2010-06-04); (Dr Aarav Gupta, 2010-06-12) …

    SELECT doctor_name, joined_on FROM doctors WHERE joined_on < '2015-01-01' ORDER BY joined_on, doctor_id;

  21. Medium. Show each patient's name and how many characters it has. Target: 20 rows · patient_name, name_length · (Saanvi Rao, 10); (Simran Gill, 11) …

    SELECT patient_name, LENGTH(patient_name) AS name_length FROM patients ORDER BY patient_id LIMIT 20;

  22. Medium. Count how many different cities patients come from. Target: 1 row · cities · (10)

    SELECT COUNT(DISTINCT city) AS cities FROM patients;

  23. Hard. Count appointments by status. Target: 3 rows · status, appointments · (cancelled, 242); (completed, 1606) …

    SELECT status, COUNT(*) AS appointments FROM appointments GROUP BY status ORDER BY status;

  24. Hard. Show the 10 doctors with the most appointments. Target: 10 rows · doctor_id, doctor_name, appointments · (7, Dr Nisha Mehta, 49); (26, Dr Nikhil Patel, 44) …

    SELECT d.doctor_id, d.doctor_name, COUNT(*) AS appointments FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id GROUP BY d.doctor_id, d.doctor_name ORDER BY appointments DESC, d.doctor_id LIMIT 10;

  25. Hard. Show completed revenue per department. Target: 6 rows · department, revenue · (Orthopaedics, 749300); (Dermatology, 463000) …

    SELECT d.department, SUM(a.amount) AS revenue FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id WHERE a.status = 'completed' GROUP BY d.department ORDER BY revenue DESC, d.department;

  26. Hard. Show the average consultation fee per department. Target: 6 rows · department, avg_fee · (Orthopaedics, 1583.33); (Pediatrics, 1522.22) …

    SELECT department, ROUND(AVG(fee), 2) AS avg_fee FROM doctors GROUP BY department ORDER BY avg_fee DESC, department;

  27. Hard. Show departments with more than 300 appointments. Target: 2 rows · department, appointments · (Orthopaedics, 589); (Dermatology, 416)

    SELECT d.department, COUNT(*) AS appointments FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id GROUP BY d.department HAVING COUNT(*) > 300 ORDER BY appointments DESC, d.department;

  28. Hard. Show the 5 cities with the most patients. Target: 5 rows · city, patients · (Bengaluru, 77); (Mumbai, 65) …

    SELECT city, COUNT(*) AS patients FROM patients GROUP BY city ORDER BY patients DESC, city LIMIT 5;

  29. Hard. Show the 10 best-rated doctors who have at least 20 rated appointments. Target: 10 rows · doctor_id, doctor_name, avg_rating, ratings · (52, Dr Rohan Gill, 4.45, 29); (56, Dr Neel Bose, 4.35, 26) …

    SELECT d.doctor_id, d.doctor_name, ROUND(AVG(a.rating), 2) AS avg_rating, COUNT(a.rating) AS ratings FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id GROUP BY d.doctor_id, d.doctor_name HAVING COUNT(a.rating) >= 20 ORDER BY avg_rating DESC, d.doctor_id LIMIT 10;

  30. Hard. Show completed appointments and revenue per month of 2025. Target: 12 rows · month, appointments, revenue · (2025-01, 69, 99700); (2025-02, 48, 65200) …

    SELECT DATE_FORMAT(a.appointment_date, '%Y-%m') AS month, COUNT(*) AS appointments, SUM(a.amount) AS revenue FROM appointments a WHERE a.status = 'completed' AND a.appointment_date >= '2025-01-01' AND a.appointment_date < '2026-01-01' GROUP BY month ORDER BY month;

  31. Hard. Count patients by blood group, rarest last. Target: 6 rows · blood_group, patients · (B+, 115); (AB+, 110) …

    SELECT blood_group, COUNT(*) AS patients FROM patients GROUP BY blood_group ORDER BY patients DESC, blood_group;

  32. Hard. Show total appointment amount by status. Target: 3 rows · status, total_amount · (cancelled, 0); (completed, 2306400) …

    SELECT status, SUM(amount) AS total_amount FROM appointments GROUP BY status ORDER BY status;

  33. Hard. Show each department's no-show percentage, worst first. Target: 6 rows · department, no_show_pct · (Neurology, 11.9); (Orthopaedics, 7.6) …

    SELECT d.department, ROUND(100 * SUM(CASE WHEN a.status = 'no_show' THEN 1 ELSE 0 END) / COUNT(*), 1) AS no_show_pct FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id GROUP BY d.department ORDER BY no_show_pct DESC, d.department;

  34. Hard. List doctors who have never had an appointment. Target: 0 rows · doctor_id, doctor_name, department · 

    SELECT d.doctor_id, d.doctor_name, d.department FROM doctors d LEFT JOIN appointments a ON a.doctor_id = d.doctor_id WHERE a.appointment_id IS NULL ORDER BY d.doctor_id;

  35. Hard. Show patients with more than 5 completed appointments. Target: 20 rows · patient_id, patient_name, completed_visits · (126, Ishaan Joshi, 7); (244, Rohan Bhat, 7) …

    SELECT p.patient_id, p.patient_name, COUNT(*) AS completed_visits FROM patients p JOIN appointments a ON a.patient_id = p.patient_id WHERE a.status = 'completed' GROUP BY p.patient_id, p.patient_name HAVING COUNT(*) > 5 ORDER BY completed_visits DESC, p.patient_id LIMIT 20;

  36. Hard. Show departments where the average completed bill is above 1,000. Target: 6 rows · department, avg_bill · (Orthopaedics, 1597.65); (Pediatrics, 1492.31) …

    SELECT d.department, ROUND(AVG(a.amount), 2) AS avg_bill FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id WHERE a.status = 'completed' GROUP BY d.department HAVING AVG(a.amount) > 1000 ORDER BY avg_bill DESC, d.department;

  37. Professional. Show patients who saw more than 3 different doctors. Target: 20 rows · patient_id, patient_name, doctors_seen · (258, Aman Khan, 7); (272, Simran Mehta, 7) …

    SELECT p.patient_id, p.patient_name, COUNT(DISTINCT a.doctor_id) AS doctors_seen FROM patients p JOIN appointments a ON a.patient_id = p.patient_id WHERE a.status = 'completed' GROUP BY p.patient_id, p.patient_name HAVING COUNT(DISTINCT a.doctor_id) > 3 ORDER BY doctors_seen DESC, p.patient_id LIMIT 20;

  38. Professional. Show the top 5 doctors by completed revenue. Target: 5 rows · doctor_id, doctor_name, department, revenue · (17, Dr Aarav Gupta, Orthopaedics, 80000); (41, Dr Vihaan Iyer, Dermatology, 77500) …

    SELECT d.doctor_id, d.doctor_name, d.department, SUM(a.amount) AS revenue FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id WHERE a.status = 'completed' GROUP BY d.doctor_id, d.doctor_name, d.department ORDER BY revenue DESC, d.doctor_id LIMIT 5;

  39. Professional. Show each patient's most recent appointment date. Target: 20 rows · patient_id, patient_name, last_visit · (24, Diya Bose, 2025-12-29); (100, Saanvi Singh, 2025-12-29) …

    SELECT p.patient_id, p.patient_name, MAX(a.appointment_date) AS last_visit FROM patients p JOIN appointments a ON a.patient_id = p.patient_id GROUP BY p.patient_id, p.patient_name ORDER BY last_visit DESC, p.patient_id LIMIT 20;

  40. Professional. Rank doctors by completed revenue with RANK(). Target: 10 rows · doctor_name, revenue, revenue_rank · (Dr Aarav Gupta, 80000, 1); (Dr Vihaan Iyer, 77500, 2) …

    SELECT d.doctor_name, SUM(a.amount) AS revenue, RANK() OVER (ORDER BY SUM(a.amount) DESC) AS revenue_rank FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id WHERE a.status = 'completed' GROUP BY d.doctor_id, d.doctor_name ORDER BY revenue_rank, d.doctor_id LIMIT 10;

  41. Professional. Show monthly completed revenue for 2025 with a running total. Target: 12 rows · month, revenue, running_total · (2025-01, 99700, 99700); (2025-02, 65200, 164900) …

    WITH m AS (SELECT DATE_FORMAT(appointment_date, '%Y-%m') AS month, SUM(amount) AS revenue FROM appointments WHERE status = 'completed' AND appointment_date >= '2025-01-01' AND appointment_date < '2026-01-01' GROUP BY month) SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_total FROM m ORDER BY month;

  42. Professional. Show each month of 2025 next to the previous month's revenue and the change. Target: 12 rows · month, revenue, prev_revenue, change · (2025-01, 99700, NULL, NULL); (2025-02, 65200, 99700, -34500) …

    WITH m AS (SELECT DATE_FORMAT(appointment_date, '%Y-%m') AS month, SUM(amount) AS revenue FROM appointments WHERE status = 'completed' AND appointment_date >= '2025-01-01' AND appointment_date < '2026-01-01' GROUP BY month) SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change FROM m ORDER BY month;

  43. Professional. Show each department's share of completed revenue as a percentage. Target: 6 rows · department, revenue, pct_of_total · (Orthopaedics, 749300, 32.5); (Dermatology, 463000, 20.1) …

    SELECT d.department, SUM(a.amount) AS revenue, ROUND(100 * SUM(a.amount) / (SELECT SUM(amount) FROM appointments WHERE status = 'completed'), 1) AS pct_of_total FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id WHERE a.status = 'completed' GROUP BY d.department ORDER BY revenue DESC, d.department;

  44. Professional. List patients who never had a completed appointment. Target: 20 rows · patient_id, patient_name · (1, Saanvi Rao); (5, Rahul Joshi) …

    SELECT p.patient_id, p.patient_name FROM patients p WHERE NOT EXISTS (SELECT 1 FROM appointments a WHERE a.patient_id = p.patient_id AND a.status = 'completed') ORDER BY p.patient_id LIMIT 20;

  45. Professional. Compare average spend of patients with 3+ visits to the average of everyone. Target: 1 row · avg_spend_all, avg_spend_frequent · (4178.26, 5711.15)

    WITH per_patient AS (SELECT patient_id, COUNT(*) AS visits, SUM(amount) AS spend FROM appointments WHERE status = 'completed' GROUP BY patient_id) SELECT ROUND(AVG(spend), 2) AS avg_spend_all, ROUND((SELECT AVG(spend) FROM per_patient WHERE visits >= 3), 2) AS avg_spend_frequent FROM per_patient;

  46. Professional. List doctors who charge more than their own department's average fee. Target: 29 rows · doctor_name, department, fee · (Dr Neel Sharma, Cardiology, 1600); (Dr Vihaan Nair, Cardiology, 1400) …

    SELECT d.doctor_name, d.department, d.fee FROM doctors d WHERE d.fee > (SELECT AVG(x.fee) FROM doctors x WHERE x.department = d.department) ORDER BY d.department, d.fee DESC, d.doctor_id;

  47. Professional. Show the first appointment of every patient (one row each). Target: 20 rows · patient_id, appointment_id, appointment_date, status · (2, 74, 2024-04-27, no_show); (3, 1466, 2024-02-03, completed) …

    WITH ranked AS (SELECT a.patient_id, a.appointment_id, a.appointment_date, a.status, ROW_NUMBER() OVER (PARTITION BY a.patient_id ORDER BY a.appointment_date, a.appointment_id) AS rn FROM appointments a) SELECT patient_id, appointment_id, appointment_date, status FROM ranked WHERE rn = 1 ORDER BY patient_id LIMIT 20;

  48. Professional. Show patients with at least 2 no-shows, most first. Target: 16 rows · patient_id, patient_name, no_shows · (327, Varun Joshi, 3); (6, Arjun Bose, 2) …

    SELECT p.patient_id, p.patient_name, COUNT(*) AS no_shows FROM patients p JOIN appointments a ON a.patient_id = p.patient_id WHERE a.status = 'no_show' GROUP BY p.patient_id, p.patient_name HAVING COUNT(*) >= 2 ORDER BY no_shows DESC, p.patient_id LIMIT 20;

  49. Professional. Build a city dashboard: completed appointments, revenue and average rating. Target: 10 rows · city, appointments, revenue, avg_rating · (Bengaluru, 200, 290200, 3.99); (Ludhiana, 194, 282800, 4.05) …

    SELECT p.city, COUNT(*) AS appointments, SUM(a.amount) AS revenue, ROUND(AVG(a.rating), 2) AS avg_rating FROM patients p JOIN appointments a ON a.patient_id = p.patient_id WHERE a.status = 'completed' GROUP BY p.city ORDER BY revenue DESC, p.city;

  50. Professional. Show each department's busiest month in 2025. Target: 6 rows · department, month, appointments · (Cardiology, 2025-05, 10); (Dermatology, 2025-09, 27) …

    WITH m AS (SELECT d.department, DATE_FORMAT(a.appointment_date, '%Y-%m') AS month, COUNT(*) AS appointments FROM doctors d JOIN appointments a ON a.doctor_id = d.doctor_id WHERE a.appointment_date >= '2025-01-01' AND a.appointment_date < '2026-01-01' GROUP BY d.department, month), r AS (SELECT department, month, appointments, ROW_NUMBER() OVER (PARTITION BY department ORDER BY appointments DESC, month) AS rn FROM m) SELECT department, month, appointments FROM r WHERE rn = 1 ORDER BY department;

Up next · Lesson 17

Real life: retail bank

200 min · 50 practice problems

Start next lesson
Capstone: audited retail report Real life: retail bank