Question paper (build 07.05.20.19)
- CustomerID
- FullName
-Avg TotalOrderValue2023 - the average total order value of the customer's completed
orders in 2023, rounded to two decimal places.
- Avg TotalOrderValue2024 - the average total order value of the customer's completed
orders in 2024, rounded to two decimal places.
- PercentageChange - the percentage growth in the average total order value from 2023 to
2024. II is calculated as (Avg Total OrderValue2024 - Avg TotalOrderValue2023)/
Avg TotalOrderValue2023*100. Round the percentage change to two decimal places, and
append %' at the end.
Order the result in ascending order of CustomerID as shown in the following figure.
Avg TotalOrderValue2023 Avg TotalOrderValue2024 PercentageChange
CustomerID
1
2
FullName
Trần Văn Tâm
2
12
Đỗ Đức Thịnh
3
13
Võ Thị Hoa
4
17
Lê Thị Diễm
5
43
6
45
Lê Thị Tâm
Nguyễn Lan Hương 1705000.00
347000.00
7
48
Võ Đức Tài
530000.00
475000.00
1820000.00
1070500.00
340000.00
-35.85%
525000.00
10.53%
446000.00
-75.49%
235000.00
-78.05 %
680000.00
-60.12%
525000.00
1102000.00
255000.00
217.58%
-51.43 %
Picture 7.1
Question 8:
Write a stored procedure named MonthlySalesSummary that generates a monthly sales
report for a specified year. Your procedure must accept one input parameter: @year int - the
year for which the report will be generated. The procedure must display all 12 months (from
January to December), even if there are no orders in a given month. For each month,
calculate and display:
- Month in the format Month/Year
-NumberOfOrders - the total count of completed orders in that month
- Total Revenue - the total revenue (sum of Quantity * Price) from all completed orders in that
month
