DoctorID
FirstName
LastName
DepartmentName
NumberOfAppointments
1
1
John
Doe
Pediatrics
2
2
8
Robert Martinez
General Surgery 2
3 29
Melissa Young Pediatrics
2
4
40
Samuel Carter
Pediatrics
2
5
48
Keith Evans
General Surgery 2
6 50
Frank
Collins
Pediatrics
2
7
11
Jennifer Wilson
Pediatrics
1
8 20
Helen Thompson General Surgery 1
9
23
Betty Clark
Pediatrics
1
10 28
Christopher Hall General Surgery 0
11 37
Laura Baker General Surgery 0
12 42
Katherine Perez
General Surgery 0
Picture 5.1
Zoom
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.
DepartmentID Name
DoctorID DoctorFullName
NumberOfTests
1
1
Cardiology
35
Rebecca Green 12
2
2
Neurology
31
Deborah Sanchez 11 John Doe 15
3 3
Pediatrics
1
Orthonadine
12
Douid Andereen
12
+ 100%
Close
