Question
Q4) You have been contacted by a hospital manager to write some queries for them using the following relational schema shown below as a part
Q4) You have been contacted by a hospital manager to write some queries for them using the following relational schema shown below as a 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: Query #1 List all the patients details, alphabetically by name. SQL Query #2 List all the patients contained in the Surgical ward. SQL Query #3 List all the patients admitted today. SQL Query #4 Find the names of all the patients being prescribed Morphine. SQL 5 | P a g e Query #5 What is the total cost of Morphine supplied to a patient called John Smith ? SQL Query #6 What is the maximum, minimum and average number of beds in a ward? Create appropriate column headings for the results table. SQL Query #7 For each ward that admitted more than 10 patients today, list the ward number, ward type and number of beds in each ward. SQL Query #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. SQL
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started