Person PersonID FirstName LastName StreetAddress City State...

Person PersonID FirstName LastName StreetAddress City State Zip Phone Number SSN Doctor DoctoriD MedicalDegrees PersonID Patient PatientID SecPhoneNumber DOB PersonID Test TestID TestName PatientVisit VisitID PatientID DoctorID VisitDate DocNote Speciality SpecialityID SpecialityName DoctorSpeciality DoctoriD Speciality D PVisitPrescription VisitID PrescriptionID PVisit Test VisitID TestID Prescription PrescriptionID PrescriptionName

Thanks for your feedback!

Need help using the above entity relationship diagram to answer these questions 1. Write One SCRIPT to create this database (call it DocOffice) in SQL SERVER 2. Write a SQL statement for Doc Rob Belkin is retiring. We need to inform all his patients, and ask them to select a new doctor. For this purpose, Create a VIEW that finds the names and Phone numbers of all of Rob's patients. 3. Write a SQL statement to Create a view which has First Names, Last Names of all doctors who gave out a prescription for Panadol 4. Write a SQL statement to Create a view which Shows the First Name and Last name of all doctors and their specialty’s 5. Write a SQL statement to Modify the view created in Q4 to show the First Name and Last name of all doctors and their specialties ALSO include doctors who DO NOT have any specialty 6. Write a SQL statement to Create a stored procedure that gives the Prescription name and the number of patients from the city of Tacoma with that prescription. Example | 20 | Aspirin | | 2 | Ciprofloxacin | 7. Create trigger on the DoctorSpeciality so that every time a doctor specialty is updated or added a new entry is made in the audit table. The audit table will have the following (Hint-The trigger will be on DoctorSpecialty table) Doctor FirstName Action(indicate update or added) Specialty Date of modification Person PersonID FirstName LastName Speciality SpecialitylD SpecialityName StreetAddress Doctor CD State Zip DoctoriD MedicalDegrees PersonID DoctorSpeciality DoctoriD SpecialityD PhoneNumber SSN PVisitPrescription VisitID PrescriotionD Patient PatientlD PatientVisit VisitD SecPhoneNumber PatientID DOB DoctorlD VisitDate DocNote PersonID Prescription PrescriotionID PrescriptionName Test TestID PVisitTest VisitD TestD TestName

Play audio

Feedback

Question

Please give Ace some feedback