SQL Exercise: Patients and the room number where they treated

SQL hospital Database: Exercise-15 with Solution

15. From the following tables, write a SQL query to find the names of the patients and the room number where they need to be treated. Return patient name as "Patient", examination room as "Room No.", and starting date time as Date "Date and Time of appointment".

    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

Sample Solution:

-- SELECTing patient name, examination room, and date/time of appointment
SELECT p.name AS "Patient",
       a.examinationroom AS "Room No.",
       a.start_dt_time AS "Date and Time of appointment"
-- FROM patient table aliased as p
FROM patient p
-- INNER JOIN with appointment table aliased as a based on patient's SSN
JOIN appointment a ON p.ssn = a.patient;

Sample Output:

      Patient      | Room No. | Date and Time of appointment
-------------------+----------+------------------------------
 John Smith        | A        | 2008-04-24 10:00:00
 Grace Ritchie     | B        | 2008-04-24 10:00:00
 John Smith        | A        | 2008-04-25 10:00:00
 Dennis Doe        | B        | 2008-04-25 10:00:00
 Dennis Doe        | C        | 2008-04-26 10:00:00
 Random J. Patient | C        | 2008-04-26 11:00:00
 John Smith        | C        | 2008-04-26 12:00:00
 Dennis Doe        | A        | 2008-04-27 10:00:00
 Grace Ritchie     | B        | 2008-04-27 10:00:00
(9 rows)

Explanation:

The given query in SQL that returns information about patients and their appointments, including the patient's name, the examination room number, and the date and time of the appointment from two tables "patient" and "appointment".

The query joins the 'patient' and 'appointment' tables based on the ssn and patient columns.

Alternative Solution:

Using Implicit INNER JOIN:

-- SELECTing patient name, examination room, and date/time of appointment
SELECT p.name AS "Patient",
       a.examinationroom AS "Room No.",
       a.start_dt_time AS "Date and Time of appointment"
-- FROM patient table aliased as p, appointment table aliased as a
FROM patient p, appointment a
-- WHERE clause specifies the join condition based on patient's SSN
WHERE p.ssn = a.patient;

Explanation:

This solution uses the traditional comma-separated table list with join conditions specified in the WHERE clause, creating an implicit INNER JOIN between the patient and appointment tables.

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: Count unique patients who came to examination room C.
Next SQL Exercise: Nurses and the room where they assist the physicians.