SQL Exercise: Patients with an appointment on the given date

SQL hospital Database: Exercise-17 with Solution

17. From the following tables, write a SQL query to locate the patients who attended the appointment on the 25th of April at 10 a.m. Return Name of the patient, Name of the Nurse assisting the physician, Physician Name as "Name of the physician", examination room as "Room No.", schedule date and approximate time to meet the physician.

    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

 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

 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:

-- SELECTing patient, nurse, physician, examination room, and start date/time based on appointment time
SELECT t.name AS "Name of the patient",
       n.name AS "Name of the Nurse assisting the physician",
       p.name AS "Name of the physician",
       a.examinationroom AS "Room No.",
       a.start_dt_time
-- FROM patient table aliased as t
FROM patient t
-- INNER JOIN with appointment table aliased as a based on patient's SSN
JOIN appointment a ON a.patient = t.ssn
-- INNER JOIN with nurse table aliased as n based on preparation nurse's employeeid
JOIN nurse n ON a.prepnurse = n.employeeid
-- INNER JOIN with physician table aliased as p based on physician's employeeid
JOIN physician p ON a.physician = p.employeeid
-- WHERE clause filters appointments for the specified start date/time
WHERE a.start_dt_time = '2008-04-25 10:00:00';

Sample Output:

 Name of the patient | Name of the Nurse assisting the physician | Name of the physician | Room No. |    start_dt_time
---------------------+-------------------------------------------+-----------------------+----------+---------------------
 John Smith          | Laverne Roberts                           | John Dorian           | A        | 2008-04-25 10:00:00
 Dennis Doe          | Paul Flowers                              | Percival Cox          | B        | 2008-04-25 10:00:00
(2 rows)

Explanation:

The said query in SQL that returns information about the patient, nurse, physician, examination room number, and start date and time for a specific appointment that took place on April 25, 2008, at 10:00 AM.

The query join the 'patient', 'appointment', 'nurse', and 'physician' tables, based on their common columns.

The 'appointment' and 'patient' tables linked based on the patient and ssn columns, the 'appointment' and 'nurse' tables based on the prepnurse and employeeid columns, and the 'appointment' and 'physician' table based on the physician and employeeid columns.

The query filters the results using a WHERE clause to retrieve only the appointment that took place on April 25, 2008, at 10:00 AM.

Alternative Solution:

Using Subquery in WHERE Clause:

-- SELECTing patient, nurse, physician, examination room, and start date/time based on appointment time using a subquery
SELECT t.name AS "Name of the patient",
       n.name AS "Name of the Nurse assisting the physician",
       p.name AS "Name of the physician",
       a.examinationroom AS "Room No.",
       a.start_dt_time
-- FROM patient table aliased as t
FROM patient t
-- JOIN with appointment table aliased as a based on patient's SSN
JOIN appointment a ON a.patient = t.ssn
-- JOIN with nurse table aliased as n based on preparation nurse's employeeid
JOIN nurse n ON a.prepnurse = n.employeeid
-- JOIN with physician table aliased as p based on physician's employeeid
JOIN physician p ON a.physician = p.employeeid
-- WHERE clause with a subquery to filter appointments for the specified start date/time
WHERE a.start_dt_time = '2008-04-25 10:00:00' AND EXISTS (
    SELECT 1
    FROM appointment a_sub
    WHERE a_sub.patient = t.ssn
      AND a_sub.start_dt_time = '2008-04-25 10:00:00'
);

Explanation:

This solution uses a subquery in the WHERE clause to filter appointments for the specified start date/time.

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: Nurses and the room where they assist the physicians.
Next SQL Exercise: Patients and their physicians who do not need a nurse.