HLST 3320 Health Database Applications SQL Assignment

In this assignment you will be asked to apply the important concepts and ideas we have covered so far in the course regarding the SQL syntax. On the course website you will find a script that will create the database structure below and will populate the tables with data.

Step 1. Run this script in the lab to create the database.

Step 2. Answer all the 20 questions below by writing a SQL query that will produce the intended result.

Step 3. Save all your queries in a script file in MySQL and upload the file on the course website.

Grading involves checking that your SQL script runs successfully in the lab and produces the intended results against the above database.

Specialization Table

Physician Table

Person Table

Note: The Physician_ID in the Person table shows the family physician for each individual.

Appointment Table

Questions:

1. Write the SQL code that will create a subset of the table Physician called Physician1 that will reproduce the structure and will add the first five rows from the original table.
2. Write the SQL code that will list all the attributes for the physician Anne Smith in the table Phisycian1.
3. Write the code to change the physician initial from N to M for the physician Anne Smith in the table Phisycian1.
4. Write the SQL code to delete the row from the table Physician1 for the person who was hired on December 6th, 2010
5. Write the code to add a new column called Physician_phone to the table Physician1. This is an optional column with the data type decimal(13,0).
6. Write the code to enter in the table Physician1 a phone number of 9056783456 for the person whose Specialization_ID is 101.
7. Write the code that will delete the Physician1 table from the database
8. Using the original Physician table, write the SQL code that will generate a list of all family medicine physicians. The list should include only physicians’ first and last name.
9. Write the SQL code that will generate a list of all the appointments made by the family medicine physicians.
10. Write the SQL code that will count and display the number of persons who have Denis Cook as family physician.
11. Write the SQL code required to list all the persons whose last names OR first names start with ‘S’
12. Write the SQL code that will produce a listing of the data in the Appointment table in the ascending order by Appointment_charge
13. Write the SQL code to find the average charge by the family medicine physicians.
14. Write the SQL code that will list only once the specializations of the physicians in the table.
15. Write the SQL code that will list only once the Physician-Person appointments. In other words it will not list twice an appointment between the same physician and person.
16. Write the code that will produce the result shown in the table below
17. Using the data in the Appointment table write the SQL code that will yield for each physician the total charges.
18. Write the SQL code that will display the Appointment_duration, Physician_F_name, Physician_L_name, Person_F_name and Person_L_name for the appointments with the maximum and minimum duration.
19. Write the SQL code that will count the number of appointments made in 2009 by family medicine physicians that were greater than one hour in duration.
20. Write the SQL code that will display the name and specialization of the physician with the greatest number of appointments.