w3resource
SQL exercises

SQL exercises on hospital Database: Make a report of specified queries

SQL hospital Database: Exercise-30 with Solution

30. Write a query in SQL to make a report which will show - 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.

Sample table: undergoes


Sample table: patient


Sample table: physician


Sample table: nurse


Sample table: stay


Sample table: room


Sample Solution:

SELECT p.name AS "Patient",
       y.name AS "Physician",
       n.name AS "Nurse",
       u.date AS "Date of release",
       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.stay=s.stayid
JOIN room r ON s.room=r.roomnumber;

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)

Practice Online


E R Diagram of Hospital Database:

E R Diagram: SQL Hospital Database.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming