Table of Contents

• 1  Wikibooks SQL Exercise: The Hospital
• 2  Load the libraries
• 3  Load the Database and Tables
• 4  Exercise Questions
  • 4.1  Obtain the names of all physicians that have performed a medical procedure they have never been certified to perform.
  • 4.2  Same as the previous query, but include the following information in the results: Physician name, name of procedure, date when the procedure was carried out, name of the patient the procedure was carried out on.
  • 4.3  Obtain the names of all physicians that have performed a medical procedure that they are certified to perform, but such that the procedure was done at a date (Undergoes.Date) after the physician's certification expired (Trained_In.CertificationExpires).
  • 4.4  Same as the previous query, but include the following information in the results: Physician name, name of procedure, date when the procedure was carried out, name of the patient the procedure was carried out on, and date when the certification expired.
  • 4.5  Obtain the information for appointments where a patient met with a physician other than his/her primary care physician. Show the following information: Patient name, physician name, nurse name (if any), start and end time of appointment, examination room, and the name of the patient's primary care physician.
  • 4.6  The Patient field in Undergoes is redundant, since we can obtain it from the Stay table. There are no constraints in force to prevent inconsistencies between these two tables. More specifically, the Undergoes table may include a row where the patient ID does not match the one we would obtain from the Stay table through the Undergoes.Stay foreign key. Select all rows from Undergoes that exhibit this inconsistency.
  • 4.7  Obtain the names of all the nurses who have ever been on call for room 123.
  • 4.8  The hospital has several examination rooms where appointments take place. Obtain the number of appointments that have taken place in each examination room.
  • 4.9  Obtain the names of all patients (also include, for each patient, the name of the patient's primary care physician), such that \emph{all} the following are true:
• 5  Time Taken

Wikibooks SQL Exercise: The Hospital

• https://en.wikibooks.org/wiki/SQL_Exercises
• https://en.wikibooks.org/wiki/SQL_Exercises/The_Hospital

Load the libraries

import time
import os
import json
time_start_notebook = time.time()

import numpy as np
import pandas as pd

# Jupyter notebook settings for pandas
pd.set_option('display.max_columns', 200)
pd.set_option('display.max_rows', 100)
pd.set_option('display.max_colwidth', 100)

from datetime import datetime
from dateutil.relativedelta import relativedelta
# last_year = datetime.now() - relativedelta(years=1)

from sqlalchemy import create_engine

Load the Database and Tables

# configs
data = json.load(open(os.path.expanduser('~/.config/postgres/credentials.json')))
pw,port = data['password'], data['port'] # port is 5432
dbname = 'wbhospital' # open pdadmin4 and look if this database exists.

%load_ext sql
%sql postgres://postgres:$pw@localhost:$port/$dbname

%%sql
select table_schema,table_name
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'
order by table_schema, table_name

 * postgres://postgres:***@localhost:5432/wbhospital
15 rows affected.

table_schema	table_name
public	affiliated_with
public	appointment
public	block
public	department
public	medication
public	nurse
public	on_call
public	patient
public	physician
public	prescribes
public	procedure
public	room
public	stay
public	trained_in
public	undergoes

tables = _

print(type(tables))

df_tables = pd.DataFrame(tables, columns=['table_schema','table_name'])

<class 'sql.run.ResultSet'>

df_tables.columns

Index(['table_schema', 'table_name'], dtype='object')

x = %sql select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'appointment';
dfx = x.DataFrame()
dfx

 * postgres://postgres:***@localhost:5432/wbhospital
7 rows affected.

	column_name	data_type	character_maximum_length
0	appointmentid	integer	None
1	patient	integer	None
2	prepnurse	integer	None
3	physician	integer	None
4	start	date	None
5	End	date	None
6	examinationroom	text	None

'**' + dfx['column_name'] + '**'

# I don't see any whitespaces around the column names of this table.

0      **appointmentid**
1            **patient**
2          **prepnurse**
3          **physician**
4              **start**
5                **End**
6    **examinationroom**
Name: column_name, dtype: object

Exercise Questions

Obtain the names of all physicians that have performed a medical procedure they have never been certified to perform.

%%sql

SELECT Name
FROM Physician
WHERE EmployeeID IN
    (SELECT Physician
     FROM Undergoes U
     WHERE NOT EXISTS
         (SELECT *
          FROM Trained_In
          WHERE Treatment = PROCEDURE
            AND Physician = U.Physician ) );

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

name
Christopher Turk

%%sql

SELECT P.Name
FROM Physician AS P,

  (SELECT Physician,
          PROCEDURE
   FROM Undergoes
   EXCEPT SELECT Physician,
                 Treatment
   FROM Trained_in) AS Pe
WHERE P.EmployeeID=Pe.Physician;

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

name
Christopher Turk

%%sql
SELECT Name
FROM Physician
WHERE EmployeeID IN
    (SELECT Undergoes.Physician
     FROM Undergoes
     LEFT JOIN Trained_In ON Undergoes.Physician=Trained_In.Physician
     AND Undergoes.Procedure=Trained_In.Treatment
     WHERE Treatment IS NULL );

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

name
Christopher Turk

Same as the previous query, but include the following information in the results: Physician name, name of procedure, date when the procedure was carried out, name of the patient the procedure was carried out on.

%%sql

SELECT P.Name AS Physician,
       Pr.Name AS PROCEDURE,
       U.Date,
       Pt.Name AS Patient
FROM Physician P,
     Undergoes U,
     Patient Pt,
     PROCEDURE Pr
WHERE U.Patient = Pt.SSN
  AND U.Procedure = Pr.Code
  AND U.Physician = P.EmployeeID
  AND NOT EXISTS
    (SELECT *
     FROM Trained_In T
     WHERE T.Treatment = U.Procedure
       AND T.Physician = U.Physician );

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

physician	procedure	date	patient
Christopher Turk	Complete Walletectomy	2008-05-13	Dennis Doe

%%sql

SELECT P.Name,
       Pr.Name,
       U.Date,
       Pt.Name
FROM Physician AS P,
     PROCEDURE AS Pr,
                  Undergoes AS U,
                  Patient AS Pt,

  (SELECT Physician,
          PROCEDURE
   FROM Undergoes
   EXCEPT SELECT Physician,
                 Treatment
   FROM Trained_in) AS Pe
WHERE P.EmployeeID=Pe.Physician
  AND Pe.Procedure=Pr.Code
  AND Pe.Physician=U.Physician
  AND Pe.Procedure=U.Procedure
  AND U.Patient=Pt.SSN;

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

name	name_1	date	name_2
Christopher Turk	Complete Walletectomy	2008-05-13	Dennis Doe

Obtain the names of all physicians that have performed a medical procedure that they are certified to perform, but such that the procedure was done at a date (Undergoes.Date) after the physician's certification expired (Trained_In.CertificationExpires).

%%sql
SELECT Name
FROM Physician
WHERE EmployeeID IN
    (SELECT Physician
     FROM Undergoes U
     WHERE Date >
         (SELECT CertificationExpires
          FROM Trained_In T
          WHERE T.Physician = U.Physician
            AND T.Treatment = U.Procedure ) );

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

name
Todd Quinlan

%%sql

SELECT P.Name
FROM Physician AS P,
     Trained_In T,
     Undergoes AS U
WHERE T.Physician=U.Physician
  AND T.Treatment=U.Procedure
  AND U.Date>T.CertificationExpires
  AND P.EmployeeID=U.Physician;

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

name
Todd Quinlan

Same as the previous query, but include the following information in the results: Physician name, name of procedure, date when the procedure was carried out, name of the patient the procedure was carried out on, and date when the certification expired.

%%sql

SELECT P.Name AS Physician,
       Pr.Name AS PROCEDURE,
       U.Date,
       Pt.Name AS Patient,
       T.CertificationExpires
FROM Physician P,
     Undergoes U,
     Patient Pt,
     PROCEDURE Pr,
               Trained_In T
WHERE U.Patient = Pt.SSN
  AND U.Procedure = Pr.Code
  AND U.Physician = P.EmployeeID
  AND Pr.Code = T.Treatment
  AND P.EmployeeID = T.Physician
  AND U.Date > T.CertificationExpires;

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

physician	procedure	date	patient	certificationexpires
Todd Quinlan	Obfuscated Dermogastrotomy	2008-05-09	Dennis Doe	2007-12-31 00:00:00

Obtain the information for appointments where a patient met with a physician other than his/her primary care physician. Show the following information: Patient name, physician name, nurse name (if any), start and end time of appointment, examination room, and the name of the patient's primary care physician.

• NOTE: END is a keyword, we need to escape it.

%%sql

SELECT Pt.Name AS Patient,
       Ph.Name AS Physician,
       N.Name AS Nurse,
       A.Start,
       A."End",
       A.ExaminationRoom,
       PhPCP.Name AS PCP
FROM Patient Pt,
     Physician Ph,
     Physician PhPCP,
     Appointment A
LEFT JOIN Nurse N ON A.PrepNurse = N.EmployeeID
WHERE A.Patient = Pt.SSN
  AND A.Physician = Ph.EmployeeID
  AND Pt.PCP = PhPCP.EmployeeID
  AND A.Physician <> Pt.PCP;

 * postgres://postgres:***@localhost:5432/wbhospital
4 rows affected.

patient	physician	nurse	start	End	examinationroom	pcp
Dennis Doe	Percival Cox	Paul Flowers	2008-04-25	2008-04-25	B	Christopher Turk
Dennis Doe	Percival Cox	None	2008-04-26	2008-04-26	C	Christopher Turk
John Smith	Christopher Turk	None	2008-04-26	2008-04-26	C	John Dorian
Dennis Doe	Molly Clock	Laverne Roberts	2008-04-27	2008-04-21	A	Christopher Turk

The Patient field in Undergoes is redundant, since we can obtain it from the Stay table. There are no constraints in force to prevent inconsistencies between these two tables. More specifically, the Undergoes table may include a row where the patient ID does not match the one we would obtain from the Stay table through the Undergoes.Stay foreign key. Select all rows from Undergoes that exhibit this inconsistency.

%%sql

SELECT *
FROM Undergoes U
WHERE Patient <>
    (SELECT Patient
     FROM Stay S
     WHERE U.Stay = S.StayID );

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

patient	procedure	stay	date	physician	assistingnurse
100000001	7	3217	2008-05-10	7	101

Obtain the names of all the nurses who have ever been on call for room 123.

%%sql

SELECT N.Name
FROM Nurse N
WHERE EmployeeID IN
    (SELECT OC.Nurse
     FROM On_Call OC,
          Room R
     WHERE OC.BlockFloor = R.BlockFloor
       AND OC.BlockCode = R.BlockCode
       AND R.Number = 123 );

 * postgres://postgres:***@localhost:5432/wbhospital
2 rows affected.

name
Laverne Roberts
Paul Flowers

The hospital has several examination rooms where appointments take place. Obtain the number of appointments that have taken place in each examination room.

%%sql

SELECT ExaminationRoom,
       COUNT(AppointmentID) AS Number
FROM Appointment
GROUP BY ExaminationRoom;

 * postgres://postgres:***@localhost:5432/wbhospital
3 rows affected.

examinationroom	number
B	3
C	3
A	3

Obtain the names of all patients (also include, for each patient, the name of the patient's primary care physician), such that \emph{all} the following are true:

• The patient has been prescribed some medication by his/her primary care physician.
• The patient has undergone a procedure with a cost larger that $5,000
• The patient has had at least two appointment where the nurse who prepped the appointment was a registered nurse.
• The patient's primary care physician is not the head of any department.

# NOTE: in sqlite we can compare boolean and integer 1 but in postgres we cant
# sqlite: N.Registered = 1
# postgres: N.Registered = true

%%sql

SELECT Pt.Name,
       PhPCP.Name
FROM Patient Pt,
     Physician PhPCP
WHERE Pt.PCP = PhPCP.EmployeeID
  AND EXISTS
    (SELECT *
     FROM Prescribes Pr
     WHERE Pr.Patient = Pt.SSN
       AND Pr.Physician = Pt.PCP )
  AND EXISTS
    (SELECT *
     FROM Undergoes U,
          PROCEDURE Pr
     WHERE U.Procedure = Pr.Code
       AND U.Patient = Pt.SSN
       AND Pr.Cost > 5000 )
  AND 2 <=
    (SELECT COUNT(A.AppointmentID)
     FROM Appointment A,
          Nurse N
     WHERE A.PrepNurse = N.EmployeeID
       AND N.Registered = TRUE )
  AND NOT Pt.PCP IN
    (SELECT Head
     FROM Department);

 * postgres://postgres:***@localhost:5432/wbhospital
1 rows affected.

name	name_1
John Smith	John Dorian

Time Taken

time_taken = time.time() - time_start_notebook
h,m = divmod(time_taken,60*60)
print('Time taken to run whole notebook: {:.0f} hr '\
      '{:.0f} min {:.0f} secs'.format(h, *divmod(m,60)))

Time taken to run whole notebook: 0 hr 0 min 1 secs