Question

Consider the following relations:

Patient (pid: integer, lname: string, fname: string, primary_did: integer, age: integer)
Doctor (did: integer, lname: string, fname: string, deptid: integer, age: integer, salary: integer)
Nurse (nid: integer, lname: string, fname: string, deptid: integer, rank: string, age: integer)
Department (deptid: integer, name: string, budget: integer)
Menu (menuid: integer, pid: integer, caloriecount: integer, saltlevel: integer, vegetarian: bit, diabetic: bit, nauseaSafe: bit, notes: string)
Vaccination (vaccinationid: integer, pid: integer, vaccinationname: string, dategiven: date, dateexpires: date)
Prescription (presid: integer, pid:integer, medname: string, quantity: integer, timesPerDay: integer, withfood: bit, writtenby: integer)
MedsGiven(presid: integer, timegiven: timestamp, givenbynurse: integer, notes: string)

The relations capture some basic information about a hospital.
Patients have primary physicians (primary_did).
Doctors work in departments (deptid) (oncology, pediatrics, etc).
Each patient staying for a while in the hospital has a specific menu ordered for them; pid in Menu is a foreign key referencing Patient.
Patients also have vaccinations (pid in Vaccination references Patient).
Patients may have prescriptions and, of course, each prescription must be written by a doctor. writtenby in Prescription references did in the Doctor relation.
Nurses give medicines based on prescriptions, and the database records the time each medicine is given (timegiven) and which nurse gave it (givenbynurse).

(did = doctor ID. lname = last name. pid = patient ID. nid = nurse ID. Etc.)

You may make the simplifying assumption that comparisons on "date" or "timestamp" fields will work with basic =, <, >, etc, boolean operators. This is not the case in many DBMS products; for real work you would need to consult the documentation for your particular DBMS software.

Write the following queries in SQL.

1. Find the age of the oldest patient.
2. Find the department names and department IDs of all departments that have a budget greater than $20 million ($20,000,000).
3. Find the menu IDs and patient IDs of all menus where the menu notes contain the word "apple" somewhere, or where the notes end in "pear" and one character after "pear" ("pears", "pearS", "pearz", etc -- any one character).
4. Find the first and last names of patients that have had a vaccination, where the vaccination expires before April 1, 2019.

Answer 1

SELECT MAX(age)

FROM Patient;

This query selects the maximum (by using MAX()) age from the table Patient and hence displays the oldest patient. Table used here is Patient.

SELECT name, deptid

FROM Department

WHERE budget >2000000;

This query displays the name and the department ID of the Department whose budget is over $20 million which is specified in the query using WHERE clause. Table used here is Department.

SELECT menuid, pid

FROM Menu

WHERE notes LIKE '%Apple%'

OR notes LIKE '%pear_';

This query displays the Menu ID and the Patient ID for all the Patient and Menus which contain the word 'Apple' anywhere in the notes or have note ending with the word 'pear' following by one single character be it any character. This is specified in the query using WHERE and LIKE which look for the given string in the notes. Table used here is Menu.

SELECT p.fname, p.lname

FROM Patient p JOIN Vaccination v

ON p.pid = v.pid

WHERE v.dateexpires < '2019-04-01';

This query display the names of the patients (First name and Last name) that have had a vaccination and the vaccination expires before April 1, 2019. As data from Patient table is needed with constraint from the vaccination table, we have joined both the tables using natural Join with primary key of Patient (i.e pid) and foreign key of Vaccination (i.e pid) and using WHERE clause we have specified that the dateexpires should be less than April 1, 2019.