Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Write a SQL query to provide the information requested for each of the following requirements. For each practitioner, list their ID, first name, last name,
Write a SQL query to provide the information requested for each of the following requirements.
- For each practitioner, list their ID, first name, last name, the total number of days and the total number of hours they are scheduled to work in a standard week at the Medical Practice. Assume a workday is nine hours long.
- List the Patient's first name, last name and the appointment date and time, for all appointments held on the 18/09/2019 by Dr Anne Funsworth.
- List the ID and date of birth of any patient who has not had an appointment and was born before 1950.
- List the patient ID, first name, last name and the number of appointments for patients who have had at least three appointments. List the patients in 'number of appointments' order from most to least.
- List the first name, last name, gender, and the number of days since the last appointment of each patient and the 23/09/2019.
- List the full name and full address of each practitioner in the following format exactly. Dr Mark P. Huston. 21 Fuller Street SUNSHINE, NSW 2343 Make sure you include the punctuation and the suburb in upper case.
- Sort the list by last name, then first name, then middle initial.
- List the patient id, first name, last name and date of birth of the fifth oldest patient(s).
- List the patient ID, first name, last name, appointment date (in the format 'Tuesday 17 September, 2019') and appointment time (in the format '14:15 PM') for all patients who have had appointments on any Tuesday after 10:00 AM.
- Make an address list for a special newsletter to all patients and practitioners. The mailing list should contain all relevant address fields for each household. Note that each household should only receive one newsletter.
- Makea view (called vwNurseDays) with the name and phone details of any nurse (registered or not) and the days that they work. Execute the SQL statements to create the view.
- Using your view, write a query to retrieve the name and phone number details of all nurses who are scheduled to work on a Wednesday.
- Makea view (called vwNSWPatients) that contains all patient details for patients whose address is in NSW. Execute the SQL statements to create the view.
- Makea stored procedure (called spSelect_vwNSWPatients) to retrieve all records and columns from vwNSWPatientsin postcode order ascending. Execute the stored procedure.
- Makea stored procedure (called spInsert_vwNSWPatients) to insert a new record into vwNSWPatients, using parameters for all relevant data. Execute the stored procedure inserting a record for a new patient named Mr Mickey M Mouse from 1 Smith St, Smithville, NSW 2222.
- Makea stored procedure (called spModify_PractitionerMobilePhone) using the Practitioner table to change a practitioner's mobile phone number, using the Practitioner ID and the new mobile number as parameters. Execute the stored procedure to change Hilda Brown's mobile number to 0412345678.
- Manipulate the Practitioner table to store a driver's licence number. For privacy and security purposes this data needs to be encrypted. Name the new column DriversLicenceHash. For encrypting the column, use a one-way hashing algorithm.
- Execute the statement to create the new column. Add the drivers licence number of 1066AD Dr Ludo Vergenargen's (Practitioner ID 10005) drivers licence using a SHA hashing function. Display Dr Vertenargen's record to view the hashed number.
- Manipulate the Patient table to add a new column that will store a date value which is the last date they made contact. The default value should be the date of record creation. Name the new column LastContactDate. Execute the statement to create the new column.
- Makea trigger on the Appointment table that will update LastContactDate on the Patient table each time a new record is added to the Appointment table. The value of the LastContactDate should be the date the record is added. Name the trigger tr_Appointment_AfterInsert.
- Delete the view vwNurseDays from the database.
- Delete the stored procedure spSelect_vwNSWPatients from the database.
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