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)
Formulate the following SQL statements:
(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.

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) 
Formulate the following SQL statements:
(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.

Quizplus · Accepted Answer

The Answer of 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) 
Formulate the following SQL statements:
(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.

The Relational Schema Shown Below Is Part of a Hospital