Picture 5.1
Question 6:
Write a query to display, for each department, all the doctors who have the highest number of tests. The results must include DepartmentID, Name, DoctorID, DoctorFullName and NumberOfTests, where DoctorFullName is the concatenation of the doctor's first name, a whitespace and the doctor's last name. The NumberOfTests of a doctor is the total number of tests required by that doctor during his appointments.
NumberOfTests
DepartmentID Name
DoctorID
DoctorFullName
1
1
Cardiology
35
Rebecca Green
12
2
2
Neurology Pediatrics
31
Deborah Sanchez 11
3
3
1
John Doe
15
4
4
Orthopedics
12
David Anderson 12
5
5
Dermatology
39
Virginia Nelson 8
6
6
Radiology
49
Sharon Edwards
8
7
7
Oncology
2
Jane Smith
8
8
8
Emergency Medicine 5
Linda Brown
7
9 8
Emergency Medicine 16
Mary Jackson
7
10 8
Emergency Medicine 36
Ryan Adams
7
11 8
Emergency Medicine 43
Justin Roberts
7
12 9
Psychiatry
24
George Rodrigu... 10
13 10
General Surgery
48
Keith Evans
11
Picture 6.1
Question 7:
Write a query to display, for each department, the number of distinct patients who have appointments with a doctor in that department for the years 2022, 2023 and 2024. The results must include DepartmentID, Name, NumberOfPatientsIn2022,NumberOfPatientsIn2023 and NumberOfPatientsIn2024, as shown in the following figure.
Zoom
+ 94%
Close
