Picture 5.1
Question 6:
Write a query to display, for each department, all the doctors who have the highest number of medical records. The results must include DepartmentID, Name, DoctorID,DoctorFullName and NumberOfRecords, where DoctorFullName is the concatenation of the doctor's first name, a whitespace and the doctor's last name. The NumberOfRecords of a doctor is the total number of medical records associated with that doctor.
DepartmentID Name
DoctorID
DoctorFullName
NumberOfRecords
1
1
Cardiology 35 Neurology 14
2
2
Jessica Taylor
Rebecca Green 12
7
3
2
Neurology 31 Pediatrics 1
Deborah Sanchez 7
4 5 4
3
John Doe
10
Orthopedics 12
David Anderson 7
6 5
Dermatology 4
Michael Williams 5
7 5 8 5
Dermatology 32 Dermatology 39
Joshua Wright 5
Virginia Nelson
5
9 6
Radiology 49
Sharon Edwards 7
10 7
Oncology 30
11 8
Emergenc... 43 Psychiatry 24
Anthony Allen 7
Justin Roberts
8
12 9
13 10
General S...48
George Rodrigu... 4
Keith Evans 10
Picture 6.1
Question 7:
Write a query to display, for each year of the appointments table, the total cost of tests conducted for appointments in this year of each of the five departments: 'Cardiology',
'Neurology', 'Pediatrics', 'Dermatology' and 'Oncology'. The results must include DepartmentID, Name, Year and TotalCost as shown in the following figure. For example, the first row in the figure indicates that the total cost for tests conducted for appointments in 2020 in the 'Cardiology' department is 915. Note that the TotalCost for a given department in
6 of 6
Paper No: 6
a given year must be NULL if no test was performed in that year for appointments in that department (as shown in the 17th row of the figure).
Zoom
+ 100%
Close
