SQL Exercise: Names of all patients who had at least 2 appointments

Last update on December 20 2024 11:41:49 (UTC/GMT +8 hours)

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".

 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

    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

 employeeid |      name       |  position  | registered |    ssn
------------+-----------------+------------+------------+-----------
        101 | Carla Espinosa  | Head Nurse | t          | 111111110
        102 | Laverne Roberts | Nurse      | t          | 222222220
        103 | Paul Flowers    | Nurse      | f          | 333333330

 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:

1. Joins:

• 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).

2. Filter Registered Nurses:

• 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).

3. JSubquery for Appointment Count:

• 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.

4. Ordering:

• The result is ordered by the patient's name for better readability.

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.