Formulate the following SQL statements:

Question

The relational schema shown below is part of a hospital database. The primary keys are highlighted in bold.
Patient (patientNo, patName, patAddr, DOB)
Ward (wardNo, wardName, wardType, noOfBeds)
Contains (patientNo, wardNo, admissionDate)
Drug (drugNo, drugName, costPerUnit)
Prescribed (patientNo, drugNo, unitsPerDay, startDate, finishDate)

(1) List all the patients’ details, alphabetically by name.
(2) List all the patients contained in the ‘Surgical’ ward.
(3) List all the patients admitted today.
(4) Find the names of all the patients being prescribed ‘Morphine’.
(5) What is the total cost of Morphine supplied to a patient called ‘John Smith’ ?
(6) What is the maximum, minimum and average number of beds in a ward? Create appropriate column headings for the results table.
(7) For each ward that admitted more than 10 patients today, list the ward number, ward type and number of beds in each ward.
(8) List the numbers and names of all patients and the drugNo and number of units of their medication. The list should also include the details of patients that are not prescribed medication.

Answer 1

(1) SELECT *
FROM Patient
ORDER BY patName
(2) SELECT p.patientNo, p.patName
FROM Patient p, Ward w, Contains c
WHERE w.wardNo = c.wardNo AND c.patientNo = p.patientNo AND
wardName= ‘Surgical’
(3) SELECT p.patientNo, p.patName
FROM Patient p, Contains c
WHERE c.patientNo = p.patientNo AND admissionDate = ‘today’
(4) SELECT p.patName
FROM Patient p, Prescribed pr, Drug d
WHERE pr.patientNo = p.patientNo AND pr.drugNo = d.drugNo AND
drugName = ‘Morphine’.
(5) SELECT SUM(((finishDate – startDate) * unitsPerDay) * costPerUnit) AS
totalCost
FROM Patient p, Prescribed pr, Drug d
WHERE pr.patientNo = p.patientNo AND pr.drugNo = d.drugNo AND
drugName = ‘Morphine’ AND patName = ‘John Smith’
(6) SELECT MAX(noOfBeds) AS Maximum, MIN(noOfBeds) AS Minimum,

AVG(noOfBeds) AS Average

FROM Ward
(7) SELECT w.wardNo, wardType, noOfBeds
FROM Patient p, Ward w, Contains c
WHERE w.wardNo = c.wardNo AND c.patientNo = p.patientNo AND
admissionDate = ‘today’
GROUP BY wardNo, wardType, noOfBeds
HAVING COUNT(*) > 10
(8) SELECT *
FROM Patient p Left Join Prescribed pr ON pr.patientNo = p.patientNo