SQL Exercise: Names of all patients who had at least 2 appointments
38. From the following table, write a SQL query to find those patients with at least two appointments in which the nurse who prepared the appointment was a registered nurse and the physician who provided primary care should be identified. Return Patient name as "Patient", Physician name as "Primary Physician", and Nurse Name as "Nurse".
Sample table: appointment
appointmentid | patient | prepnurse | physician | start_dt_time | end_dt_time | examinationroom
---------------+-----------+-----------+-----------+---------------------+---------------------+-----------------
13216584 | 100000001 | 101 | 1 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | A
26548913 | 100000002 | 101 | 2 | 2008-04-24 10:00:00 | 2008-04-24 11:00:00 | B
36549879 | 100000001 | 102 | 1 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | A
46846589 | 100000004 | 103 | 4 | 2008-04-25 10:00:00 | 2008-04-25 11:00:00 | B
59871321 | 100000004 | | 4 | 2008-04-26 10:00:00 | 2008-04-26 11:00:00 | C
69879231 | 100000003 | 103 | 2 | 2008-04-26 11:00:00 | 2008-04-26 12:00:00 | C
76983231 | 100000001 | | 3 | 2008-04-26 12:00:00 | 2008-04-26 13:00:00 | C
86213939 | 100000004 | 102 | 9 | 2008-04-27 10:00:00 | 2008-04-21 11:00:00 | A
93216548 | 100000002 | 101 | 2 | 2008-04-27 10:00:00 | 2008-04-27 11:00:00 | B
Sample table: patient
ssn | name | address | phone | insuranceid | pcp
-----------+-------------------+--------------------+----------+-------------+-----
100000001 | John Smith | 42 Foobar Lane | 555-0256 | 68476213 | 1
100000002 | Grace Ritchie | 37 Snafu Drive | 555-0512 | 36546321 | 2
100000003 | Random J. Patient | 101 Omgbbq Street | 555-1204 | 65465421 | 2
100000004 | Dennis Doe | 1100 Foobaz Avenue | 555-2048 | 68421879 | 3
Sample table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample Solution:
SELECT pt.name AS "Patient", -- Select the patient's name and label it as "Patient"
p.name AS "Primary Physician", -- Select the primary physician's name and label it as "Primary Physician"
n.name AS "Nurse" -- Select the nurse's name and label it as "Nurse"
FROM appointment a -- Main table containing appointment details
JOIN patient pt ON a.patient = pt.ssn -- Join with patient table to get patient details
JOIN nurse n ON a.prepnurse = n.employeeid -- Join with nurse table to get nurse details
JOIN physician p ON pt.pcp = p.employeeid -- Join with physician table to get the primary care physician details
WHERE n.registered = 't' -- Ensure the nurse is registered ('t' means true in this dataset)
AND a.patient IN ( -- Subquery to filter patients who meet the criteria of having at least two qualifying appointments
SELECT patient -- Select patients from the appointment table
FROM appointment a1 -- Subquery alias for the appointment table
JOIN nurse n1 ON a1.prepnurse = n1.employeeid -- Join nurse table to check for registered nurses
WHERE n1.registered = 't' -- Condition to include only appointments with registered nurses
GROUP BY a1.patient -- Group by patient to count the number of their appointments
HAVING COUNT(*) >= 2 -- Ensure the patient has at least two such appointments
)
ORDER BY pt.name; -- Order the result by patient's name for better readability
Sample Output:
Patient | Primary Physician | Nurse
---------------+-------------------+-----------------
John Smith | John Dorian | Carla Espinosa
John Smith | John Dorian | Laverne Roberts
Dennis Doe | Christopher Turk | Laverne Roberts
Dennis Doe | Christopher Turk | Paul Flowers
Explanation:
- appointment table is joined with:
- patient to fetch the patient details (pt.name).
- nurse to fetch the nurse details (n.name).
- physician to fetch the primary care physician (p.name).
- The condition n.registered = 't' ensures that the nurse preparing the appointment is registered.
- This is consistent with the nurse table data structure, where registered is a character column ('t' for true).
- The subquery filters patients with at least two appointments where a registered nurse was involved.
- It uses GROUP BY on the patient field and applies HAVING COUNT(*) >= 2.
- The result is ordered by the patient's name for better readability.
1. Joins:
2. Filter Registered Nurses:
3. JSubquery for Appointment Count:
4. Ordering:
Expected Output Based on the data:
- Patient 100000001 (John Smith) has three appointments:
- Two are prepared by registered nurses (employeeid 101 and 102).
- Patient 100000004 (Dennis Doe) has two appointments:
- Both are prepared by registered nurses (employeeid 102 and 103).
Practice Online
E R Diagram of Hospital Database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Patients who have had a procedure costing over $5,000.
Next SQL Exercise: Providers of primary care who are not department heads.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.