Hospital has a database that tracks the current doctors on staff, the doctors' specialties, the nurses...

Transcribed Image Text:

Hospital has a database that tracks the current doctors on staff, the doctors' specialties, the nurses on staff, the wards, the wards the nurses are assigned to, the patients currently in the hospital, and the patients the doctors are assigned to. Review the database and design SQL queries to identify the following information. Problem One 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 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 b) Create a query titled "Specialty Count" and enter the following SQL query. SELECT Specially Specialty, COUNTIDoctor SpecialXD) AS Specially.Count FROM Specialty. Doctor WHERE Specialty Specialhun-Dector Specialti GROUP BY Specially Specialty Analyze and run the query. You may need to modify and use this query later. Problem Three 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. b) What is the specialty of the doctor with the most patients? Problem Four a) Create a query titled "Busiest Ward' that counts the number of patients in each ward Problem Five 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. b) Write a query "Patient Name" that identifies the name of the patient with Healthcad # 981293928 and the ward they are assigned to Hospital has a database that tracks the current doctors on staff, the doctors' specialties, the nurses on staff, the wards, the wards the nurses are assigned to, the patients currently in the hospital, and the patients the doctors are assigned to. Review the database and design SQL queries to identify the following information. Problem One 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 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 b) Create a query titled "Specialty Count" and enter the following SQL query. SELECT Specially Specialty, COUNTIDoctor SpecialXD) AS Specially.Count FROM Specialty. Doctor WHERE Specialty Specialhun-Dector Specialti GROUP BY Specially Specialty Analyze and run the query. You may need to modify and use this query later. Problem Three 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. b) What is the specialty of the doctor with the most patients? Problem Four a) Create a query titled "Busiest Ward' that counts the number of patients in each ward Problem Five 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. b) Write a query "Patient Name" that identifies the name of the patient with Healthcad # 981293928 and the ward they are assigned to

Answer rating: 100% (QA)

Problem One a Find out how many patients do not have a doctor sql SELECT COUNT AS PatientsWithoutDoc... View the full answer