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).
DepartmentID
Name
Year
Total Cost
1
1
Cardiology
2020
915.00
2
5
Dermatology
2020
295.00
3
2
Neurology
2020 1250.00
4
7
Oncology
2020 335.00
5
3
Pediatrics
2020 60.00
6
1
Cardiology
2021 595.00
7
5
Dermatology
2021 250.00
8
2
Neurology 2021 425.00
9
7
Oncology
2021 240.00
10
3
Pediatrics
2021 615.00
11
1
Cardiology
2022 355.00
12
5
Dermatology
2022 320.00
13
2
Neurology
2022 760.00
14
7
Oncology
2022 145.00
15
2
Dodintrins
470.00
+ 100%
Close
Zoom FUD
