w3resource

SQL exercises on hospital Database: Basic, Subqueries, and Joins

SQL [39 exercises with solution]

[An editor is available at the bottom of the page to write and execute the scripts.]

Sample Database: hospital

1. From the following table, write a SQL query to find those nurses who are yet to be registered. Return all the fields of nurse table.  Go to the editor

Sample table: nurse


Sample Output:

  employeeid |     name     | position | registered |    ssn
 ------------+--------------+----------+------------+-----------
         103 | Paul Flowers | Nurse    | f          | 333333330
 (1 row)

Click me to see the solution

2. From the following table, write a SQL query to find the nurse who is the head of their department. Return Nurse Name as "name", Position as "Position".  Go to the editor

Sample table: nurse


Sample Output:

      Name      |  Position
----------------+------------
 Carla Espinosa | Head Nurse
(1 row)

Click me to see the solution

3. From the following tables, write a SQL query to find those physicians who are the head of the department. Return Department name as "Department" and Physician name as "Physician".  Go to the editor

Sample table: physician


Sample table: department


Sample Output:

    Department    |  Physician
------------------+--------------
 General Medicine | Percival Cox
 Surgery          | John Wen
 Psychiatry       | Molly Clock
(3 rows)

Click me to see the solution

4. From the following table, write a SQL query to count the number of patients who booked an appointment with at least one Physician. Return count as "Number of patients taken at least one appointment".  Go to the editor

Sample table: appointment


Sample Output:

 No. of patients taken at least one appointment
------------------------------------------------
                                              4
(1 row)

Click me to see the solution

5. From the following table, write a SQL query to find the floor and block where the room number 212 belongs. Return block floor as "Floor" and block code as "Block".  Go to the editor

Sample table: room


Sample Output:

 Floor | Block
-------+-------
     2 |     2
(1 row)

Click me to see the solution

6. From the following table, write a SQL query to count the number available rooms. Return count as "Number of available rooms".  Go to the editor

Sample table: room


Sample Output:

 Number of available rooms
---------------------------
                        29
(1 row)

Click me to see the solution

7. From the following table, write a SQL query to count the number of unavailable rooms. Return count as "Number of unavailable rooms".  Go to the editor

Sample table: room


Sample Output:

Number of unavailable rooms
---------------------------
                         7
(1 row)

Click me to see the solution

8. From the following tables, write a SQL query to find the physician and the departments they are affiliated with. Return Physician name as "Physician", and department name as "Department".  Go to the editor

Sample table: physician


Sample table: department


Sample table: affiliated_with


Sample Output:

     Physician     |    Department
-------------------+------------------
 John Dorian       | General Medicine
 Elliot Reid       | General Medicine
 Christopher Turk  | General Medicine
 Christopher Turk  | Surgery
 ....
 

Click me to see the solution

9. From the following tables, write a SQL query to find those physicians who have trained for special treatment. Return Physician name as "Physician", treatment procedure name as "Treatment".  Go to the editor

Sample table: physician


Sample table: procedure


Sample table: trained_in


Sample Output:

    Physician     |           Treatement
------------------+--------------------------------
 Christopher Turk | Reverse Rhinopodoplasty
 Christopher Turk | Obtuse Pyloric Recombobulation
 Christopher Turk | Obfuscated Dermogastrotomy
 Christopher Turk | Reversible Pancreomyoplasty
 .....
 

Click me to see the solution

10. From the following tables, write a SQL query to find those physicians who are yet to be affiliated. Return Physician name as "Physician", Position, and department as "Department".  Go to the editor

Sample table: physician


Sample table: affiliated_with


Sample table: department


Sample Output:

    Physician     |           position           |    Department
------------------+------------------------------+------------------
 Christopher Turk | Surgical Attending Physician | General Medicine
 John Wen         | Surgical Attending Physician | General Medicine
(2 rows)

Click me to see the solution

11. From the following tables, write a SQL query to find those physicians who are not a specialized physician. Return Physician name as "Physician", position as "Designation".  Go to the editor

Sample table: physician


Sample Output:

     Physician     |        Designation
-------------------+----------------------------
 John Dorian       | Staff Internist
 Elliot Reid       | Attending Physician
 Percival Cox      | Senior Attending Physician
 Bob Kelso         | Head Chief of Medicine
 Keith Dudemeister | MD Resident
 Molly Clock       | Attending Psychiatrist
(6 rows)

Click me to see the solution

12. From the following tables, write a SQL query to find the patients with their physicians by whom they got their preliminary treatment. Return Patient name as "Patient", address as "Address" and Physician name as "Physician".  Go to the editor

Sample table: patient


Sample table: physician


Sample Output:

      Patient      |      Address       |    Physician
-------------------+--------------------+------------------
 John Smith        | 42 Foobar Lane     | John Dorian
 Grace Ritchie     | 37 Snafu Drive     | Elliot Reid
 Random J. Patient | 101 Omgbbq Street  | Elliot Reid
 Dennis Doe        | 1100 Foobaz Avenue | Christopher Turk
(4 rows)

Click me to see the solution

13. From the following tables, write a SQL query to find the patients and the number of physicians they have taken appointment. Return Patient name as "Patient", number of Physicians as "Appointment for No. of Physicians".  Go to the editor

Sample table: appointment


Sample table: patient


Sample Output:

      Patient      | Appointment for No. of Physicians
-------------------+-----------------------------------
 Grace Ritchie     |                                 2
 John Smith        |                                 3
 Dennis Doe        |                                 3
 Random J. Patient |                                 1
(4 rows)

Click me to see the solution

14. From the following table, write a SQL query to count number of unique patients who got an appointment for examination room ‘C’. Return unique patients as “No. of patients got appointment for room C”.  Go to the editor

Sample table: appointment


Sample Output:

No. of patients got appointment for room C 
--------------------------------------------
                                          3
(1 row)

Click me to see the solution

15. From the following tables, write a SQL query to find the name of the patients and the number of the room where they have to go for their treatment. Return patient name as “Patient”, examination room as "Room No.”, and starting date time as Date "Date and Time of appointment".  Go to the editor

Sample table: patient


Sample table: appointment


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
 .....
 

Click me to see the solution

16. From the following tables, write a SQL query to find the name of the nurses and the room scheduled, where they will assist the physicians. Return Nurse Name as “Name of the Nurse” and examination room as “Room No.”.  Go to the editor

Sample table: nurse


Sample table: appointment


Sample Output:

 Name of the Nurse | Room No.
-------------------+----------
 Carla Espinosa    | A
 Carla Espinosa    | B
 Laverne Roberts   | A
 Paul Flowers      | B
 .....
 

Click me to see the solution

17. From the following tables, write a SQL query to find those patients who taken the appointment on the 25th of April at 10 am. 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.  Go to the editor

Sample table: patient


Sample table: appointment


Sample table: nurse


Sample table: physician


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)

Click me to see the solution

18. From the following tables, write a SQL query to find those patients and their physicians who do not require any assistance of a nurse. 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.".  Go to the editor

Sample table: patient


Sample table: appointment


Sample table: physician


Sample Output:

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

Click me to see the solution

19. From the following tables, write a SQL query to find the patients and their treating physicians and medication. Return Patient name as "Patient", Physician name as "Physician", Medication name as "Medication".  Go to the editor

Sample table: patient


Sample table: prescribes


Sample table: physician


Sample table: medication


Sample Output:

  Patient   |  Physician  |  Medication
------------+-------------+--------------
 John Smith | John Dorian | Procrastin-X
 Dennis Doe | Molly Clock | Thesisin
 Dennis Doe | Molly Clock | Thesisin
(3 rows)

Click me to see the solution

20. From the following tables, write a SQL query to find those patients who have taken an advanced appointment. Return Patient name as "Patient", Physician name as "Physician" and Medication name as "Medication".  Go to the editor

Sample table: patient


Sample table: prescribes


Sample table: physician


Sample table: medication


Sample Output:

  Patient   |  Physician  |  Medication
------------+-------------+--------------
 John Smith | John Dorian | Procrastin-X
 Dennis Doe | Molly Clock | Thesisin
(2 rows)

Click me to see the solution

21. From the following tables, write a SQL query to find those patients who did not take any appointment. Return Patient name as "Patient", Physician name as "Physician" and Medication name as "Medication".  Go to the editor

Sample table: patient


Sample table: prescribes


Sample table: physician


Sample table: medication


Sample Output:

  Patient   |  Physician  | Medication
------------+-------------+------------
 Dennis Doe | Molly Clock | Thesisin
(1 row)

Click me to see the solution

22. From the following table, write a SQL query to count the number of available rooms in each block. Sort the result-set on ID of the block. Return ID of the block as "Block", count number of available rooms as "Number of available rooms".  Go to the editor

Sample table: room


Sample Output:

 Block | Number of available rooms
-------+---------------------------
     1 |                         9
     2 |                        10
     3 |                        10
(3 rows)

Click me to see the solution

23. From the following table, write a SQL query to count the number of available rooms in each floor. Sort the result-set on block floor. Return floor ID as "Floor" and count the number of available rooms as "Number of available rooms".  Go to the editor

Sample table: room


Sample Output:

 Floor | Number of available rooms
-------+---------------------------
     1 |                         8
     2 |                         7
     3 |                         7
     4 |                         7
(4 rows)

Click me to see the solution

24. From the following table, write a SQL query to count the number of available rooms for each floor in each block. Sort the result-set on floor ID, ID of the block. Return the floor ID as "Floor", ID of the block as "Block", and number of available rooms as "Number of available rooms".  Go to the editor

Sample table: room


Sample Output:

 Floor | Block | Number of available rooms
-------+-------+---------------------------
     1 |     1 |                         3
     1 |     2 |                         2
     1 |     3 |                         3
     2 |     1 |                         2
	 .....
	 

Click me to see the solution

25. From the following tables, write a SQL query to count the number of unavailable rooms for each block in each floor. Sort the result-set on block floor, block code. Return the floor ID as "Floor", block ID as "Block", and number of unavailable as "Number of unavailable rooms".  Go to the editor

Sample table: room


Sample Output:

 Floor | Block | Number of unavailable rooms
-------+-------+---------------------------
     1 |     2 |                         1
     2 |     1 |                         1
     2 |     2 |                         1
     3 |     1 |                         1
     3 |     3 |                         1
     4 |     1 |                         1
     4 |     3 |                         1
(7 rows)

Click me to see the solution

26. From the following table, write a SQL query to find the floor where the maximum numbers of rooms are available. Return floor ID as "Floor", count "Number of available rooms".  Go to the editor

Sample table: room


Sample Output:

 Floor | Number of available rooms
-------+-----------------------
     1 |                     8
(1 row)

Click me to see the solution

27. From the following table, write a SQL query to find the floor where the minimum numbers of rooms are available. Return floor ID as “Floor”, Number of available rooms.  Go to the editor

Sample table: room


Sample Output:

 Floor | No of available rooms
-------+-----------------------
     3 |                     7
     4 |                     7
     2 |                     7
(3 rows)

Click me to see the solution

28. From the following tables, write a SQL query to find the name of the patients, their block, floor, and room number where they admitted.  Go to the editor

Sample table: stay


Sample table: patient


Sample table: room


Sample Output:

      Patient      | Room | Floor | Block
-------------------+------+-------+-------
 John Smith        |  111 |     1 |     2
 Random J. Patient |  123 |     1 |     3
 Dennis Doe        |  112 |     1 |     2
(3 rows)

Click me to see the solution

29. From the following tables, write a SQL query to find the nurses and the block where they are booked for attending the patients on call. Return Nurse Name as “Nurse”, Block code as "Block".   Go to the editor

Sample table: nurse


Sample table: on_call


Sample Output:

      Nurse      | Block
-----------------+-------
 Carla Espinosa  |     1
 Carla Espinosa  |     2
 Laverne Roberts |     3
 Paul Flowers    |     1
 Paul Flowers    |     2
 Paul Flowers    |     3
(6 rows)

Click me to see the 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.  Go to the editor

Sample table: undergoes


Sample table: patient


Sample table: physician


Sample table: nurse


Sample table: stay


Sample table: room


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)

Click me to see the solution

31. From the following tables, write a SQL query to find all those physicians who performed a medical procedure, but they are not certified to perform. Return Physician name as “Physician”.  Go to the editor

Sample table: physician


Sample table: undergoes


Sample table: trained_in


Sample Output:

    Physician
------------------
 Christopher Turk
(1 row)

Click me to see the solution

32. From the following tables, write a SQL query to find all the physicians, their procedure, date when the procedure was carried out and name of the patient on which procedure have been carried out but those physicians are not certified for that procedure. Return Physician Name as "Physician", Procedure Name as "Procedure", date, and Patient. Name as "Patient".  Go to the editor

Sample table: physician


Sample table: undergoes


Sample table: patient


Sample table: procedure


Sample Output:

    Physician     |       Procedure       |        date         |  Patient
------------------+-----------------------+---------------------+------------
 Christopher Turk | Complete Walletectomy | 2008-05-13 00:00:00 | Dennis Doe
(1 row)	

Click me to see the solution

33. From the following table, write a SQL query to find all those physicians who completed a medical procedure with certification after the date of expiration of their certificate. Return Physician Name as "Physician", Position as "Position".  Go to the editor

Sample table: physician


Sample table: undergoes


Sample table: trained_in


Sample Output:

  Physician   |           Position
--------------+------------------------------
 Todd Quinlan | Surgical Attending Physician
(1 row)

Click me to see the solution

34. From the following table, write a SQL query to find all those physicians who completed a medical procedure with certification after the date of expiration of their certificate. Return Physician Name as “Physician”, Position as "Position", Procedure Name as “Procedure”, Date of Procedure as “Date of Procedure”, Patient Name as “Patient”, and expiry date of certification as “Expiry Date of Certificate”.   Go to the editor

Sample table: physician


Sample table: undergoes


Sample table: patient


Sample table: procedure


Sample table: trained_in


Sample Output:

  Physician   |           Position           |         Procedure          |  Date of Procedure  |  Patient   | Expiry Date of Certificate
--------------+------------------------------+----------------------------+---------------------+------------+-------------------------
 Todd Quinlan | Surgical Attending Physician | Obfuscated Dermogastrotomy | 2008-05-09 00:00:00 | Dennis Doe | 2007-12-31
(1 row) 

Click me to see the solution

35. From the following table, write a SQL query to find those nurses who have ever been on call for room 122. Return name of the nurses.   Go to the editor

Sample table: nurse


Sample table: on_call


Sample table: room


Sample Output:

      name
-----------------
 Laverne Roberts
 Paul Flowers
(2 rows)	

Click me to see the solution

36. From the following table, write a SQL query to find those patients who have been prescribed by some medication by his/her physician who has carried out primary care. Return Patient name as “Patient”, and Physician Name as “Physician”.  Go to the editor

Sample table: patient


Sample table: prescribes


Sample table: physician


Sample Output:

   Ptient   |  Physician
------------+-------------
 John Smith | John Dorian
(1 row) 

Click me to see the solution

37. From the following table, write a SQL query to find those patients who have been undergone a procedure costing more than $5,000 and the name of that physician who has carried out primary care. Return name of the patient as “Patient”, name of the physician as “Primary Physician”, and cost for the procedure as “Procedure Cost”.   Go to the editor

Sample table: patient


Sample table: undergoes


Sample table: physician


Sample table: procedure


Sample Output:

   Patient   | Primary Physician | Procedure Cost
------------+-------------------+----------------
 John Smith | John Dorian       |           5600
 Dennis Doe | Christopher Turk  |          10000
(2 rows)

Click me to see the solution

38. From the following table, write a SQL query to find those patients who had at least two appointments where the nurse who prepped the appointment was a registered nurse and the physician who has carried out primary care. Return Patient name as “Patient”, Physician name as “Primary Physician”, and Nurse Name as “Nurse”.   Go to the editor

Sample table: appointment


Sample table: patient


Sample table: nurse


Sample table: physician


Sample Output:

    Patient    | Primary Physician |      Nurse
---------------+-------------------+-----------------
 Dennis Doe    | Christopher Turk  | Laverne Roberts
 Grace Ritchie | Elliot Reid       | Carla Espinosa
 Grace Ritchie | Elliot Reid       | Carla Espinosa
 John Smith    | John Dorian       | Carla Espinosa
 John Smith    | John Dorian       | Laverne Roberts
(5 rows)

Click me to see the solution

39. From the following table, write a SQL query to find those patients whose primary care a physician who is not the head of any department takes. Return Patient name as “Patient”, Physician Name as “Primary care Physician”.   Go to the editor

Sample table: patient


Sample table: department


Sample table: physician


Sample Output:

      Patient      | Primary care Physician
-------------------+------------------------
 John Smith        | John Dorian
 Grace Ritchie     | Elliot Reid
 Random J. Patient | Elliot Reid
 Dennis Doe        | Christopher Turk
(4 rows)	 

Click me to see the solution

Practice Online


More to Come !

E R Diagram of Hospital Database:

E R Diagram: SQL Hospital Database.

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.