SQL Exercise: Make a report of specified queries

SQL hospital Database: Exercise-30 with Solution

30. From the following tables, write a SQL query to get
a) name of the patient,
b) name of the physician who is treating him or her,
c) name of the nurse who is attending him or her,
d) which treatement is going on to the patient,
e) the date of release,
f) in which room the patient has admitted and which floor and block the room belongs to respectively.

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

 nurse | blockfloor | blockcode |     oncallstart     |      oncallend
-------+------------+-----------+---------------------+---------------------
   101 |          1 |         1 | 2008-11-04 11:00:00 | 2008-11-04 19:00:00
   101 |          1 |         2 | 2008-11-04 11:00:00 | 2008-11-04 19:00:00
   102 |          1 |         3 | 2008-11-04 11:00:00 | 2008-11-04 19:00:00
   103 |          1 |         1 | 2008-11-04 19:00:00 | 2008-11-05 03:00:00
   103 |          1 |         2 | 2008-11-04 19:00:00 | 2008-11-05 03:00:00
   103 |          1 |         3 | 2008-11-04 19:00:00 | 2008-11-05 03:00:00

Sample Solution:

SELECT p.name AS "Patient",
       y.name AS "Physician",
       n.name AS "Nurse",
       s.end_time AS "Date of release",
       pr.name as "Treatement going on",
       r.roomnumber AS "Room",
       r.blockfloor AS "Floor",
       r.blockcode AS "Block"
FROM undergoes u
JOIN patient p ON u.patient=p.ssn
JOIN physician y ON u.physician=y.employeeid
LEFT JOIN nurse n ON u.assistingnurse=n.employeeid
JOIN stay s ON u.patient=s.patient
JOIN room r ON s.room=r.roomnumber
JOIN procedure pr on u.procedure=pr.code;

Sample Output:

  Patient   |    Physician     |      Nurse      |   Date of release   | Room | Floor | Block
------------+------------------+-----------------+---------------------+------+-------+-------
 John Smith | Christopher Turk | Carla Espinosa  | 2008-05-02 00:00:00 |  111 |     1 |     2
 John Smith | John Wen         | Carla Espinosa  | 2008-05-03 00:00:00 |  111 |     1 |     2
 Dennis Doe | Christopher Turk | Laverne Roberts | 2008-05-07 00:00:00 |  112 |     1 |     2
 Dennis Doe | Todd Quinlan     |                 | 2008-05-09 00:00:00 |  112 |     1 |     2
 John Smith | John Wen         | Carla Espinosa  | 2008-05-10 00:00:00 |  112 |     1 |     2
 Dennis Doe | Christopher Turk | Paul Flowers    | 2008-05-13 00:00:00 |  112 |     1 |     2
(6 rows)

Explanation:

The said query in SQL that retrieves information such as the patient name, physician name, nurse name (if available), date of release, treatment going on, room number, floor, and block.

The JOIN keyword is used to join tables based on a common column.

The 'undergoes' and the 'patient' tables are joined based on the common columns patient and ssn, the 'physican' and 'undergoes' tables are joined based on the common columns employeeid and physician.

The LEFT JOIN keyword is used to retrieve information from the nurse table, but also include records where there is no match in the nurse table.

The 'undergoes' and the 'stay' tables are joined based on the common columns patient, the 'stay' and 'room' tables are joined based on the common column room and roomnumber, and the 'undergoes' and the 'procedure' tables are joined based on the common columns procedure and code.

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: Find the nurses and the block where they are booked.
Next SQL Exercise: Physicians, a medical procedure without certification.