Esercitazione sql con Db Hospital

(online db and sql editor)

Dato lo schema relazione come da immagine iniziare a consultare le tabelle implicate:

List of tables in the hospital database:

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

physician:

employeeid – this is a unique ID of a physician
name – this is the name of a physician
position – this is the designation of a physician
ssn – this is a security number of a physician

department:

departmentid – this is a unique ID for a department
name – this is the name of a department
head – this is the ID of the physician who is the head of a department, referencing to the column employeeid of the table physician

affiliated_with:

physician – this is the ID of the physicians which is referencing to the column employeeid of the physician table
department – this is the ID the department which is referencing to the column departmentid of the department table
primaryaffiliation – this is a logical column which indicate that whether the physicians are yet to be affiliated or not
Note: The combination of physician, department will come once in that table.

procedure:

code – this is the unique ID of a medical procedure
name – the name of the medical procedure
cost – the cost for the procedure

trained_in:

physician – this is ID of the physicians which is referencing to the column employeeid of the physician table
treatment – this is the ID of the medical procedure which is referencing to the column code of the procedure table
certificationdate – this is the starting date of certification
certificationexpires – this is the expiry date of certification
Note: The combination of physician and treatement will come once in that table.

patient:

ssn – this is a unique ID for each patient
name – this is the name of the patient
address – this is the address of the patient
phone – this is the phone number of the patient
insuranceid – this is the insurance id of the patient
pcp – this is the ID of the physician who primarily checked up the patient which is referencing to the column employeeid of the physician table

nurse:

employeeid – this is the unique ID for a nurse
name – name of the nurses
position – the designation of the nurses
registered – this is a logical column which indicate that whether the nurses are registered for nursing or not
ssn – this is the security number of a nurse

appointment:

appointmentid – this is the unique ID for an appointment
patient – this is the ID of each patient which is referencing to the ssn column of patient table
prepnurse – the ID of the nurse who may attend the patient with the physician, which is referencing to the column employeeid of the nurse table
physician – this is the ID the physicians which is referencing to the employeeid column of the physician table
start_dt_time – this is the schedule date and approximate time to meet the physician
end_dt_time – this is the schedule date and approximate time to end the meeting
examinationroom – this the room where to meet a patient to the physician

medication:

code – this is the unique ID for a medicine
name – this is the name of the medicine
brand – this is the brand of the medicine
description – this is the description of the medicine

prescribes:

physician – this is the ID of the physician referencing to the employeeid column of the physician table
patient – this is the ID of the patient which is referencing to the ssn column of the patient table
medication – the ID of the medicine which is referencing to the code of the medication table
date – the date and time of the prescribed medication
appointment – the prescription made by the physician to a patient who may taken an appointment which is referencing to column appointmentid of appointment table
dose – the dose prescribed by the physician
Note: The combination of physician, patient, medication, date will come once in that table.

block:

blockfloor – ID of the floor
blockcode – ID of the block
Note: The combination of blockfloor, blockcode will come once in that table.

room:

roomnumber – this is the unique ID of a room
roomtype – this is type of room
blockfloor – this is the floor ID where the room in
blockcode – this is the ID of the block where the room in
unavailable – this is the logical column which indicate that whether the room is available or not
Note: The of blockfloor, blockcode columns are refercing to the combination of blockfloor and blockcode columns of the table block.

on_call:

nurse – this is ID of the nurse which is referencing to the employeeid column of the table nurse
blockfloor – this is the ID of the floor
blockcode – this is the ID of block
oncallstart – the starting date and time of on call duration
oncallend – the ending date and time of on call duration
Note: The combination of nurse, blockfloor, blockcode, oncallstart, oncallend will come once in that table and the combination of blockfloor, blockcode columns are refercing to the combination of blockfloor and blockcode columns of the table block .

stay:

stayid – this is unique ID for the admission
patient – this is the ID of the patient which is referencing the ssn column of patient table
room – this is the ID of the room where the patient admitted and which is referencing to the roomnumber column of the room table
start_time – this is the time when a patient admitted
end_time – this is the time how long a patient is staying

undergoes:

patient – this is ID of the patient which is referencing to the ssn column of the patient table
procedure – this is ID of the procedure and referencing to the code column of the procedure table
stay – this is the ID admission of a patient, which is referencing to the stayid column of the stay table
date – this is the date when a patient undergoes for a medical procedure
physician – this is the ID of a physician which is referencing to the column employeeid of the table physician
assistingnurse – this is the ID of a nurse who will assists the physician, referencing to the column employeeid of the table nurse
Note: The combination ofpatient, procedure, stay, date will come once in that table.

The database for hospital management system used for this exercises is based upon a database available in wikipedia. Sturcture and Data are may have gone through alterations sporadically.

Ecco le query necessarie per iniziare ad interrogare il DB:

1 query:   Table nurse:
Results:

employeeid |      name       |  position  | registered |    ssn
101 | Carla Espinosa  | Head Nurse | t          | 111111110         
102 | Laverne Roberts | Nurse      | t          | 222222220         
103 | Paul Flowers    | Nurse      | f          | 333333330

go to editor to try to code the right query:

2) Write a query in SQL to find the name of the nurse who are the head of their department.

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

3Write a query in SQL to obtain the name of the physicians who are the head of each department.

Sample table: physician

Write a query in SQL to obtain the name of all those physicians who completed a medical procedure with certification after the date of expiration of their certificate, their position, procedure they have done, date of procedure, name of the patient on which the procedure had been applied and the date when the certification expired.

Sample table: physician

Sample table: undergoes

Sample table: patient

Sample table: procedure

Sample table: trained_in

35. Write a query in SQL to obtain the names of all the nurses who have ever been on call for room 122. Go to the editor

Sample table: nurse

Sample table: on_call

Sample table: room

36. Write a query in SQL to Obtain the names of all patients who has been prescribed some medication by his/her physician who has carried out primary care and the name of that physician.

Sample table: patient

Sample table: prescribes

Sample table: physician

37. Write a query in SQL to obtain the names of all patients who has been undergone a procedure costing more than $5,000 and the name of that physician who has carried out primary care. Go to the editor

Sample table: patient

Sample table: undergoes

Sample table: physician

Sample table: procedure

3 Write a query in SQL to Obtain the names of all patients who had at least two appointment where the nurse who prepped the appointment was a registered nurse and the physician who has carried out primary care. Go to the editor

Sample table: appointment

Sample table: patient

Sample table: nurse

Sample table: physician

Write a query in SQL to Obtain the names of all patients whose primary care is taken by a physician who is not the head of any department and name of that physician along with their primary care physician.

Sample table: patient

Sample table: department

Sample table: physician