SQL programming project

project3.docx

Home >Information Systems homework help >SQL programming project

INSY 3304

Project 3

Create a new SQL file named “Project3_abc.sql” (where abc represents your initials). Log your statements and results by creating a new spool file (Project3_abc.txt) and using “set echo on.” You will need to submit the Project3_abc.txt (spooled) file with a cover sheet including the project name (“Project 3”), your full name, section, and date submitted.

1. 3 point

All column headings must show in their entirety--no truncated column headings.

2. ½ point

Line size should be set to 120 to minimize column wrapping.

3. 4 points

Properly use table aliases and dot notation where applicable.

4. 5 points

a. Using ALTER TABLE, drop the BlockTime attribute in the Block table.

b. Using ALTER TABLE, add a new BlockMinutes attribute to the Block table and set the data type to Number(3).

c. Update each rows in the Block table with the correct number of minutes.

5. 3 points

Add a new appointment. Generate the ApptID for by incrementing the max ApptID by 1.

ApptDate ApptTime PatientID BillType InsCo DrID ApptStatus

9/3/2018 11:00 15 SP 2 CN

Add appointment details using the ApptID for the new appointment above:

ReasonCode BlockLevelID

PSF L2

SR L1

6. 3 points

Add a new appointment. Generate the ApptID for by incrementing the max ApptID by 1.

ApptDate ApptTime PatientID BillType InsCo DrID ApptStatus

9/4/2018 9:00 101 I 323 3 NC

Add appointment details using the ApptID for the new appointment above:

ReasonCode BlockLevelID

PT L4

7. 1 point

Add a new appointment status:

ApptStatusCode ApptStatusDesc

CX Cancelled

8. 1 point

Change the ApptStatus for ApptID 108 to “CX”

9. ½ point

Commit all changes above before proceeding to the next step.

10. 1 point

List the patient ID, first name, and last name for all patients for whom no phone number exists.

11. 1 point

List the count of unique insurance companies found in the Appointment table. Use Insurance Co Count as the column heading.

12. 2 points

List the ApptReasonCode and count of appointments for each reason code. Use the following column headings: ReasonCode, ApptCount. Hint: Use a GROUP BY clause.

13. 2 points

List the PayType and count of appointments for each pay type. Sort by count. Hint: Use a GROUP BY clause.

14. 2 points

List all rows and all columns from the ApptDetail table; sort by ApptID then by ReasonCode, both in ascending order. Use the following column headings: ApptID, ReasonCode, BlockCode.

15. 2 points

List the average number of minutes for all rows in the ApptDetail table. Use “Avg Appt Time” as the column heading and format the result as “# Minutes” (where “#” represents the calculated number of minutes).

16. 3 points

List the appt ID, date, patient ID, patient last name, doctor ID, doctor last name, appt status desc for all appointments on or after 9/2/18. Show the date formatted as “mm/dd/yy.”

17. 3 points

List the appt ID, date, appt time, and total number of minutes for each appointment. Use the following column headings: Appt ID, Date, Time, Total Minutes. Hint: Use a GROUP BY clause.

18. 3 points

List the block code, block code description, and count of appointments for each block code. Sort by count in descending order. Use the following column headings: BlockCode, Description, Count. Hint: Use a GROUP BY clause.

19. 3 points

List the patient ID, first name, last name, and phone number for all patients. Show the phone number formatted as ‘(###) ###-####’ and sort by patient ID. Use the following column headings: Patient ID, First Name, Last Name, Phone.

20. 3 points

List the pay type, pay type description, and count of appointments for each pay type. Use the following column headings: Pay Type, Description, Count. Sort by count in descending order. Hint: use a GROUP BY clause.

21. 3 points

List the patient ID, first name, last name, and phone number for all “self pay” patients. Show the phone number formatted as ‘(###) ###-####’ and use the following column headings: PatientID, FirstName, LastName, Phone.

22. 4 points

List the appt ID, date, patient ID, last name, doctor ID, doctor last name, pay type description, and appointment status description for all appointments on or before 9/3/18. Format the date as “mm-dd-yyyy” and use the following column headings: Appt ID, Date, Patient ID, Patient Name, Dr ID, Dr Name, Pay Type, Appt Status. Sort by Appt ID.

23. 4 points

List the doctor ID, first name, last name, and count of appointments for each doctor. Combine the first and last name into one column and use the following column headings: Dr ID, Dr Name, Appt Count. Sort by doctor last name.

24. 4 points

List the patient ID, patient first name, patient last name, and total number of appointments for each patient. Sort by appointment count in descending order. Use the following column headings: Patient ID, Patient First Name, Patient Last Name, Appt Count. Hint: use a GROUP BY clause.

25. 4 points

For each appointment, list the appt ID, date, patient ID, patient first name, patient last name, doctor ID, doctor last name, and count of reason codes; combine the patient first and last name into one column, and sort by count of reason codes in descending order, then by appt ID in ascending order. Show the date formatted as ‘mm-dd-yyyy.’ Use the following column headings: Appt, Date, Patient ID, Patient Name, Dr ID, Dr Name, Code Count. Hint: use a GROUP BY clause.

26. 5 points

List the appt ID, appt date, appt time, and total number of minutes for the appointment(s) with the highest total minutes. Sort by appt ID. Use the following column headings: ApptID, Date, Time, TotalMinutes. Hint: use a GROUP BY clause and a nested SELECT or HAVING.

27. 5 points

List the doctor ID, first name, last name, and count of appointments for the doctor with the least number of appointments. Combine the first and last names into one column, use the following column headings: Dr ID, Name, Appt Count. Hint: use a GROUP BY clause and a nested SELECT or HAVING.

28. 5 points

List the appt ID, Date, patient ID, patient last name, doctor ID, and doctor last name for all appointments with a total number of minutes greater than or equal to 30 minutes. Sort by total minutes in descending order, then by appt ID in ascending order. Use the following column headings: Appt ID, Date, Patient ID, Patient Name, Doctor ID, Doctor Name.

29. 5 points

List the appt ID, date, patient last name, and doctor last name for all appointments that have a total number of minutes greater than the average total minutes for all appointments. Use the following column headings: ApptID, Date, Patient, Doctor, TotalMinutes. Sort by appt ID. Hint: use a nested SELECT.

30. 5 points

List the appt ID, date, patient last name, pay type description, insurance company, and count of reason codes for all appointments that have 2 or more reason. Sort by count of reason codes in descending order, then by appt ID in ascending order. Use the following column headings: ApptID, Date, Patient, PayType, InsCo, ReasonCount.

31. 5 points

List the reason code, reason description, block code, block minutes for the appt detail row with the highest number of minutes in each appointment. Show the minutes formatted as “# minutes” and use the following column headings: Reason Code, Description, Block Code, Minutes. Hint: use a GROUP by and nested SELECT.