1. Click Start/All Programs/MS SQL Server 2014 R2/SQL Server Management Studio.
   1. Server type: Database Engine
   2. Server name: 142.147.68.56
   3. Authentication: SQL Server Authentication
   4. Login: studentuser
   5. Password: !BSA3620$
   6. Click the Connect button
   7. Select New Query and choose the Healthcare database

Assignment Data File:

Download (and rename) this document as [YourLastName]-ProjectReport.docx

Instructions: Answer the following queries using the HealthCare database. You will submit your answers on Blackboard/Healthcare SQL Assignment.

1. How many patients have never made an appointment at any of the hospitals?

Select Statement:

2. How many appointments were made with physicians who have a specialty listed as “Plastic surgery” that were scheduled at the “Everton Memorial Hospital?”

Select Statement:

3. Which hospital employs exactly 4 physicians who are surgeons? Hint: look for “surgery” within the specialty field.

Select Statement:

4. Which day of the week is most popular based on the number of appointments made at “Everton Memorial Hospital?”

Select Statement:

5. How many patients of the “Nashville Community Hospital” do not have a phone number with a 615 area code?

Select Statement:

6. How many patients have made at least 13 appointments?

Select Statement:

7. How many patients are between 10 and 17 years old and have made at least one appointment at “Anderson-Dade Hospital?” (Hint: 10 and 17 should be included in your results.)
8. What is the average difference in time (in minutes) between the time an appointment was supposed to start and a patient’s arrive time for the “Everton Memorial Hospital” clinic?
9. difference in time between his/her patients’ arrive time and their depart time?
10. What is the age of the oldest patient who made an appointment at the “Nashville Community Hospital?”
11. Which hospital generated $109,729 in revenue, which is based on physicians’ visit rate and their number of appointments?
12. By how much did annual revenue increase at the “Johnson Memorial Hospital” for the years 2014 to 2015?
13. How many appointments were made during the years 2013-2016?
14. Which physician charged the lowest average hourly rate and what was that hourly rate? (Hint: VisitRate / ((ApptTime – ApptDepartTime) / 60)
15. How many patients arrive, on average, 25 minutes late or later to their scheduled appointment start time? (Hint: make sure your results show unique patients.)

 

STATE 8 StateAbbrv StateName: SPECIALTY SpecialtyName CO HOSPITAL HospitalD HospitalName 8 Hospital Street HospitalCity HospitalState HospitalZip HospitalURL PHYSICIAN PhysID PhysFName PhysLName PhysDegree PhysPhone Hospital D SpecialtyName VisitRate A TO PATIENT PatientID PatientFName Patient Name PatientDOB PatientPhone: PatientState 8 APPOINTMENT ApptNumber ApptDate ApptTime ApptArrive Time ApptDepartTime PhysID PatientID