SQL Hospital Database - Exercises, Practice, Solution
SQL [39 exercises with solution]
[An editor is available at the bottom of the page to write and execute the scripts. Go to the editor]
Sample Database: hospital
1. From the following table, write a SQL query to find out which nurses have not yet been registered. Return all the fields of nurse table.
Sample table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
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 identify the nurses in charge of each department. Return nursename as “name”, Position as “Position”.
Sample table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample Output:
Name | Position
----------------+------------
Carla Espinosa | Head Nurse
(1 row)
3. From the following tables, write a SQL query to identify the physicians who are the department heads. Return Department name as “Department” and Physician name as “Physician”.
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: department
departmentid | name | head
--------------+------------------+------
1 | General Medicine | 4
2 | Surgery | 7
3 | Psychiatry | 9
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 scheduled an appointment with at least one physician. Return count as "Number of patients taken at least one appointment".
Sample table: appointment
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 Output:
No. of patients taken at least one appointment
------------------------------------------------
4
(1 row)
5. From the following table, write a SQL query to locate the floor and block where room number 212 is located. Return block floor as "Floor" and block code as "Block".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
Sample Output:
Number of unavailable rooms
---------------------------
7
(1 row)
8. From the following tables, write a SQL query to identify the physician and the department with which he or she is affiliated. Return Physician name as "Physician", and department name as "Department".
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: department
departmentid | name | head
--------------+------------------+------
1 | General Medicine | 4
2 | Surgery | 7
3 | Psychiatry | 9
Sample table: affiliated_with
physician | department | primaryaffiliation
-----------+------------+--------------------
1 | 1 | t
2 | 1 | t
3 | 1 | f
3 | 2 | t
4 | 1 | t
5 | 1 | t
6 | 2 | t
7 | 1 | f
7 | 2 | t
8 | 1 | t
9 | 3 | t
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 received special training. Return Physician name as “Physician”, treatment procedure name as "Treatment".
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: procedure
code | name | cost
------+--------------------------------+-------
1 | Reverse Rhinopodoplasty | 1500
2 | Obtuse Pyloric Recombobulation | 3750
3 | Folded Demiophtalmectomy | 4500
4 | Complete Walletectomy | 10000
5 | Obfuscated Dermogastrotomy | 4899
6 | Reversible Pancreomyoplasty | 5600
7 | Follicular Demiectomy | 25
Sample table: trained_in
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
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".
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: affiliated_with
physician | department | primaryaffiliation
-----------+------------+--------------------
1 | 1 | t
2 | 1 | t
3 | 1 | f
3 | 2 | t
4 | 1 | t
5 | 1 | t
6 | 2 | t
7 | 1 | f
7 | 2 | t
8 | 1 | t
9 | 3 | t
Sample table: department
departmentid | name | head
--------------+------------------+------
1 | General Medicine | 4
2 | Surgery | 7
3 | Psychiatry | 9
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 identify physicians who are not specialists. Return Physician name as "Physician", position as "Designation".
Sample table: trained_in
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
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 received preliminary treatment. Return Patient name as "Patient", address as "Address" and Physician name as "Physician".
Sample table: patient
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
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
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 identify the patients and the number of physicians with whom they have scheduled appointments. Return Patient name as "Patient", number of Physicians as "Appointment for No. of Physicians".
Sample table: appointment
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 table: patient
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
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 tables, write a SQL query to count the number of unique patients who have been scheduled for examination room 'C'. Return unique patients as "No. of patients got appointment for room C".
Sample table: appointment
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 Output:
No. of patients got appointment for room C
--------------------------------------------
3
(1 row)
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".
Sample table: patient
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
Sample table: appointment
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 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 identify the nurses and the room in which they will assist the physicians. Return Nurse Name as "Name of the Nurse" and examination room as "Room No.".
Sample table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample table: appointment
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 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 locate the patients who attended the appointment on the 25th of April at 10 a.m. 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.
Sample table: patient
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
Sample table: appointment
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 table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
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 identify those patients and their physicians who do not require any nursing assistance. 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.".
Sample table: patient
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
Sample table: appointment
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 table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
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 locate the patients' treating physicians and medications. Return Patient name as "Patient", Physician name as "Physician", Medication name as "Medication".
Sample table: patient
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
Sample table: prescribes
physician | patient | medication | date | appointment | dose
-----------+-----------+------------+---------------------+-------------+------
1 | 100000001 | 1 | 2008-04-24 10:47:00 | 13216584 | 5
9 | 100000004 | 2 | 2008-04-27 10:53:00 | 86213939 | 10
9 | 100000004 | 2 | 2008-04-30 16:53:00 | | 5
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: medication
code | name | brand | description
------+--------------+-----------------------+-------------
1 | Procrastin-X | X | N/A
2 | Thesisin | Foo Labs | N/A
3 | Awakin | Bar Laboratories | N/A
4 | Crescavitin | Baz Industries | N/A
5 | Melioraurin | Snafu Pharmaceuticals | N/A
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 identify patients who have made an advanced appointment. Return Patient name as "Patient", Physician name as "Physician" and Medication name as "Medication".
Sample table: patient
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
Sample table: prescribes
physician | patient | medication | date | appointment | dose
-----------+-----------+------------+---------------------+-------------+------
1 | 100000001 | 1 | 2008-04-24 10:47:00 | 13216584 | 5
9 | 100000004 | 2 | 2008-04-27 10:53:00 | 86213939 | 10
9 | 100000004 | 2 | 2008-04-30 16:53:00 | | 5
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: medication
code | name | brand | description
------+--------------+-----------------------+-------------
1 | Procrastin-X | X | N/A
2 | Thesisin | Foo Labs | N/A
3 | Awakin | Bar Laboratories | N/A
4 | Crescavitin | Baz Industries | N/A
5 | Melioraurin | Snafu Pharmaceuticals | N/A
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 schedule an appointment. Return Patient name as "Patient", Physician name as "Physician" and Medication name as "Medication".
Sample table: patient
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
Sample table: prescribes
physician | patient | medication | date | appointment | dose
-----------+-----------+------------+---------------------+-------------+------
1 | 100000001 | 1 | 2008-04-24 10:47:00 | 13216584 | 5
9 | 100000004 | 2 | 2008-04-27 10:53:00 | 86213939 | 10
9 | 100000004 | 2 | 2008-04-30 16:53:00 | | 5
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: medication
code | name | brand | description
------+--------------+-----------------------+-------------
1 | Procrastin-X | X | N/A
2 | Thesisin | Foo Labs | N/A
3 | Awakin | Bar Laboratories | N/A
4 | Crescavitin | Baz Industries | N/A
5 | Melioraurin | Snafu Pharmaceuticals | N/A
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".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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 rooms that are unavailable in each block and on 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".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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 tables, write a SQL query to find the floor where the maximum number of rooms are available. Return floor ID as "Floor", count "Number of available rooms".
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
Sample Output:
Floor | Number of available rooms
-------+-----------------------
1 | 8
(1 row)
27. From the following tables, write a SQL query to locate the floor with the minimum number of available rooms. Return floor ID as "Floor", Number of available rooms.
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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.
Sample table: staystayid | patient | room | start_time | end_time --------+-----------+------+---------------------+--------------------- 3215 | 100000001 | 111 | 2008-05-01 00:00:00 | 2008-05-04 00:00:00 3216 | 100000003 | 123 | 2008-05-03 00:00:00 | 2008-05-14 00:00:00 3217 | 100000004 | 112 | 2008-05-02 00:00:00 | 2008-05-03 00:00:00Sample table: patient
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
Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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 locate the nurses and the block where they are scheduled to attend the on-call patients.Return Nurse Name as "Nurse", Block code as "Block".
Sample table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample table: on_call
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 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.
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
Sample table: patient
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
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample table: stay
stayid | patient | room | start_time | end_time --------+-----------+------+---------------------+--------------------- 3215 | 100000001 | 111 | 2008-05-01 00:00:00 | 2008-05-04 00:00:00 3216 | 100000003 | 123 | 2008-05-03 00:00:00 | 2008-05-14 00:00:00 3217 | 100000004 | 112 | 2008-05-02 00:00:00 | 2008-05-03 00:00:00Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
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 physicians who have performed a medical procedure but are not certified to do so. Return Physician name as "Physician".
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: undergoes
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
Sample table: trained_in
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
Sample Output:
Physician
------------------
Christopher Turk
(1 row)
32. From the following tables, write a SQL query to find all physicians, their procedures, the date when the procedure was performed, and the name of the patient on whom the procedure was performed, but the physicians are not certified to perform that procedure. Return Physician Name as "Physician", Procedure Name as "Procedure", date, and Patient. Name as "Patient".
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: undergoes
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
Sample table: patient
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
Sample table: procedure
code | name | cost
------+--------------------------------+-------
1 | Reverse Rhinopodoplasty | 1500
2 | Obtuse Pyloric Recombobulation | 3750
3 | Folded Demiophtalmectomy | 4500
4 | Complete Walletectomy | 10000
5 | Obfuscated Dermogastrotomy | 4899
6 | Reversible Pancreomyoplasty | 5600
7 | Follicular Demiectomy | 25
Sample table: trained_in
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
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 physicians who completed a medical procedure with certification after the expiration date of their license. Return Physician Name as "Physician", Position as "Position".
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: undergoes
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
Sample table: trained_in
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
Sample Output:
Physician | Position --------------+------------------------------ Todd Quinlan | Surgical Attending Physician (1 row)
34. From the following table, write a SQL query to find all physicians who have completed medical procedures with certification after their certificates expired. 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".
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: undergoes
patient | procedure | stay | date | physician | assistingnurse -----------+-----------+------+---------------------+-----------+---------------- 100000001 | 6 | 3215 | 2008-05-02 00:00:00 | 3 | 101 100000001 | 2 | 3215 | 2008-05-03 00:00:00 | 7 | 101 100000004 | 1 | 3217 | 2008-05-07 00:00:00 | 3 | 102 100000004 | 5 | 3217 | 2008-05-09 00:00:00 | 6 | 100000001 | 7 | 3217 | 2008-05-10 00:00:00 | 7 | 101 100000004 | 4 | 3217 | 2008-05-13 00:00:00 | 3 | 103Sample table: patient
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
Sample table: procedure
code | name | cost
------+--------------------------------+-------
1 | Reverse Rhinopodoplasty | 1500
2 | Obtuse Pyloric Recombobulation | 3750
3 | Folded Demiophtalmectomy | 4500
4 | Complete Walletectomy | 10000
5 | Obfuscated Dermogastrotomy | 4899
6 | Reversible Pancreomyoplasty | 5600
7 | Follicular Demiectomy | 25
Sample table: trained_in
physician | treatment | certificationdate | certificationexpires
-----------+-----------+-------------------+----------------------
3 | 1 | 2008-01-01 | 2008-12-31
3 | 2 | 2008-01-01 | 2008-12-31
3 | 5 | 2008-01-01 | 2008-12-31
3 | 6 | 2008-01-01 | 2008-12-31
3 | 7 | 2008-01-01 | 2008-12-31
6 | 2 | 2008-01-01 | 2008-12-31
6 | 5 | 2007-01-01 | 2007-12-31
6 | 6 | 2008-01-01 | 2008-12-31
7 | 1 | 2008-01-01 | 2008-12-31
7 | 2 | 2008-01-01 | 2008-12-31
7 | 3 | 2008-01-01 | 2008-12-31
7 | 4 | 2008-01-01 | 2008-12-31
7 | 5 | 2008-01-01 | 2008-12-31
7 | 6 | 2008-01-01 | 2008-12-31
7 | 7 | 2008-01-01 | 2008-12-31
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 out, which nurses have been on call for room 122 in the past. Return name of the nurses.
Sample table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample table: on_call
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:00Sample table: room
roomnumber | roomtype | blockfloor | blockcode | unavailable
-----------+----------+------------+-----------+-------------
101 | Single | 1 | 1 | f
102 | Single | 1 | 1 | f
103 | Single | 1 | 1 | f
111 | Single | 1 | 2 | f
112 | Single | 1 | 2 | t
113 | Single | 1 | 2 | f
121 | Single | 1 | 3 | f
122 | Single | 1 | 3 | f
123 | Single | 1 | 3 | f
201 | Single | 2 | 1 | t
202 | Single | 2 | 1 | f
203 | Single | 2 | 1 | f
211 | Single | 2 | 2 | f
212 | Single | 2 | 2 | f
213 | Single | 2 | 2 | t
221 | Single | 2 | 3 | f
222 | Single | 2 | 3 | f
223 | Single | 2 | 3 | f
301 | Single | 3 | 1 | f
302 | Single | 3 | 1 | t
303 | Single | 3 | 1 | f
311 | Single | 3 | 2 | f
312 | Single | 3 | 2 | f
313 | Single | 3 | 2 | f
321 | Single | 3 | 3 | t
322 | Single | 3 | 3 | f
323 | Single | 3 | 3 | f
401 | Single | 4 | 1 | f
402 | Single | 4 | 1 | t
403 | Single | 4 | 1 | f
411 | Single | 4 | 2 | f
412 | Single | 4 | 2 | f
413 | Single | 4 | 2 | f
421 | Single | 4 | 3 | t
422 | Single | 4 | 3 | f
423 | Single | 4 | 3 | f
Sample Output:
name
-----------------
Laverne Roberts
Paul Flowers
(2 rows)
36. From the following table, write a SQL query to determine which patients have been prescribed medication by their primary care physician. Return Patient name as "Patient", and Physician Name as "Physician".
Sample table: patient
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
Sample table: prescribes
physician | patient | medication | date | appointment | dose
-----------+-----------+------------+---------------------+-------------+------
1 | 100000001 | 1 | 2008-04-24 10:47:00 | 13216584 | 5
9 | 100000004 | 2 | 2008-04-27 10:53:00 | 86213939 | 10
9 | 100000004 | 2 | 2008-04-30 16:53:00 | | 5
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
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 undergone a procedure costing more than $5,000, as well as the name of the physician who has provided primary care, should be identified. Return name of the patient as "Patient", name of the physician as "Primary Physician", and cost for the procedure as "Procedure Cost".
Sample table: patient
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
Sample table: undergoes
patient | procedure | stay | date | physician | assistingnurse -----------+-----------+------+---------------------+-----------+---------------- 100000001 | 6 | 3215 | 2008-05-02 00:00:00 | 3 | 101 100000001 | 2 | 3215 | 2008-05-03 00:00:00 | 7 | 101 100000004 | 1 | 3217 | 2008-05-07 00:00:00 | 3 | 102 100000004 | 5 | 3217 | 2008-05-09 00:00:00 | 6 | 100000001 | 7 | 3217 | 2008-05-10 00:00:00 | 7 | 101 100000004 | 4 | 3217 | 2008-05-13 00:00:00 | 3 | 103Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
Sample table: procedure
code | name | cost
------+--------------------------------+-------
1 | Reverse Rhinopodoplasty | 1500
2 | Obtuse Pyloric Recombobulation | 3750
3 | Folded Demiophtalmectomy | 4500
4 | Complete Walletectomy | 10000
5 | Obfuscated Dermogastrotomy | 4899
6 | Reversible Pancreomyoplasty | 5600
7 | Follicular Demiectomy | 25
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 with at least two appointments in which the nurse who prepared the appointment was a registered nurse and the physician who provided primary care should be identified. Return Patient name as "Patient", Physician name as "Primary Physician", and Nurse Name as "Nurse".
Sample table: appointment
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 table: patient
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
Sample table: nurse
employeeid | name | position | registered | ssn
------------+-----------------+------------+------------+-----------
101 | Carla Espinosa | Head Nurse | t | 111111110
102 | Laverne Roberts | Nurse | t | 222222220
103 | Paul Flowers | Nurse | f | 333333330
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
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 identify those patients whose primary care is provided by a physician who is not the head of any department. Return Patient name as "Patient", Physician Name as "Primary care Physician".
Sample table: patient
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
Sample table: department
departmentid | name | head
--------------+------------------+------
1 | General Medicine | 4
2 | Surgery | 7
3 | Psychiatry | 9
Sample table: physician
employeeid | name | position | ssn
------------+-------------------+------------------------------+-----------
1 | John Dorian | Staff Internist | 111111111
2 | Elliot Reid | Attending Physician | 222222222
3 | Christopher Turk | Surgical Attending Physician | 333333333
4 | Percival Cox | Senior Attending Physician | 444444444
5 | Bob Kelso | Head Chief of Medicine | 555555555
6 | Todd Quinlan | Surgical Attending Physician | 666666666
7 | John Wen | Surgical Attending Physician | 777777777
8 | Keith Dudemeister | MD Resident | 888888888
9 | Molly Clock | Attending Psychiatrist | 999999999
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.