SQL Exercise: Patients and their physicians who do not need a nurse

Last update on December 20 2024 12:01:01 (UTC/GMT +8 hours)

18. From the following tables, write a SQL query to identify those patients and their physicians who do not require any nursing assistance. Return Name of the patient as "Name of the patient", Name of the Physician as "Name of the physician" and examination room as "Room No.".

    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           |    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 name, physician name, and examination room where preparation nurse is NULL
SELECT t.name AS "Name of the patient",
       p.name AS "Name of the physician",
       a.examinationroom AS "Room No."
-- 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 physician table aliased as p based on physician's employeeid
JOIN physician p ON a.physician = p.employeeid
-- WHERE clause filters appointments where preparation nurse is NULL
WHERE a.prepnurse IS NULL;

Sample Output:

 Name of the patient | Name of the physician | Room No.
---------------------+-----------------------+----------
 John Smith          | Christopher Turk      | C
 Dennis Doe          | Percival Cox          | C
(2 rows)

Explanation:

The said query in SQL that returns information about appointments where no preparatory nurse was assigned, including the patient's name, the physician's name, and the examination room number.

The JOIN keyword performs a join between the 'patient', 'appointment', and 'physician' tables based on their relational columns.

The 'appointment' and the 'patient' tables are joins based on the patient and ssn columns, and the 'appointment' and 'physician' tables are joins based on the the physician and employeeid columns.

The WHERE clause filters the results by appointments where no preparatory nurse was assigned. This is determined by checking whether the preparatory nurse identifier in the 'appointment' table is NULL.

Alternative Solution:

Using Implicit INNER JOIN:

-- SELECTing patient name, physician name, and examination room where preparation nurse is NULL
SELECT t.name AS "Name of the patient",
       p.name AS "Name of the physician",
       a.examinationroom AS "Room No."
-- FROM patient table aliased as t
FROM patient t
-- LEFT JOIN with appointment table aliased as a based on patient's SSN
LEFT JOIN appointment a ON a.patient = t.ssn
-- LEFT JOIN with physician table aliased as p based on physician's employeeid
JOIN physician p ON a.physician = p.employeeid
-- WHERE clause filters appointments where preparation nurse is NULL
WHERE a.prepnurse IS NULL;

Explanation:

This solution uses a LEFT JOIN with IS NULL to find appointments where the preparation nurse is NULL. The LEFT JOIN ensures that all rows from the patient table are included, and the WHERE clause filters out appointments where the preparation nurse is not NULL.

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 with an appointment on the given date.
Next SQL Exercise: Names of patients, their doctors, and medications.