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)
```

Click me to see the solution

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
----------------+------------
(1 row)
```

Click me to see the solution

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)
```

Click me to see the solution

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)
```

Click me to see the solution

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)
```

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

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)
```

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

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)
```

Click me to see the solution

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

Click me to see the solution

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

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

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)
```

Click me to see the solution

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)
```

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 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)
```

Click me to see the solution

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)
```

Click me to see the solution

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)
```

Click me to see the solution

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

Click me to see the solution

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

Click me to see the solution

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)
```

Click me to see the solution

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)
```

Click me to see the solution

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)
```

Click me to see the solution

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)
```

Click me to see the solution

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)
```

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

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)
```

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

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)
```

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

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

Click me to see the solution

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)
```

Click me to see the solution

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)
```

Click me to see the solution

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)
```

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.

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:00
```
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: 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)
```

Click me to see the solution

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)
```

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.

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: 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:00
```
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   |    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 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)
```

Click me to see the solution

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)
```

Click me to see the solution

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)
```

Click me to see the solution

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 |            103
```
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   |           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 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:00
```
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:

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

Click me to see the solution

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)
```

Click me to see the solution

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 |            103
```
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 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 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)
```

Click me to see the solution

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)
```

Click me to see the solution

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.

﻿