Review the relations and write SQL queries to identify the following information:

Doctor (DoctorID, FirstName, LastName, SpecialtyID, EmployeeID)

Specialty (SpecialtyID, Specialty)

Patient (PatientID, FirstName, LastName, Sex, HealthCardNumber, WardID)

PatientRoster (PatientID, DoctorID)

Nurse (NurseID, FirstName, LastName, EmployeeID)

Ward (WardID, WardName, Location)

NurseCoverage (NurseID, WardID)

Problem One

1. a) Figure out how many patients do not have a doctor. *** Hint ** To figure this out you will need to write a query Patients with Doctors that identifies each patient that has at least one doctor.

Problem Two

1. a) Create a query titled Doctor Specialty that outputs all the doctors first names, last names, and their corresponding specialty. There should be no duplicates.
2. b) Create a query titled Specialty Count and enter the following SQL query.

SELECT Specialty.Specialty, COUNT(Doctor.SpecialtyID) AS SpecialtyCount
FROM Specialty, Doctor
WHERE Specialty.SpecialtyID = Doctor.SpecialtyID
GROUP BY Specialty.Specialty;

Analyze and run the query. You may need to modify and use this query later.

Problem Three

1. a) Create a query titled Patient Count that counts the number of patients for each doctor. Use MS Access filters on the results to identify the doctor with the most patients.
2. b) What is the specialty of the doctor with the most patients?

Problem Four

1. a) Create a query titled Busiest Ward that counts the number of patients in each ward.

Problem Five

1. a) Write a query Ward Nurses that identifies the first name, and last name for the nurses assigned to the ward treating the patient with Healthcard # 981293928.
2. b) Write a query Patient Name that identifies the name of the patient with Healthcard # 981293928 and the ward they are assigned to.