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)
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)
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)
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)
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)
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)
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)
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 ....
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 .....
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)
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)
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)
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)
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)
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 .....
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 .....
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)
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)
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)
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)
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)
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)
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)
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
.....
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
Practice Online
More to Come !
E R Diagram of Hospital Database:
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework