2023年11月25日

CS代考 | FIT5195 Business Intelligence and Data Warehousing

本次澳洲代考主要为商业智能和数据仓库限时测试

Question 1:

The table below shows some sample records of a fact table named “Pet_Fact”. The fact table records the
sales of pet products and the total transactions for a retail chain that has stores across Victoria, Australia.

TimePeriod City Product Price_Range Total_Sale
s_KG
Total_Transactions
Q1 2020 Geelong Dog Dry Food High 200 50
Q1 2020 Melbourne Dog Dry Food Medium 300 100
Q1 2020 Melbourne Cat Dry Food High 250 40
Q1 2020 Ballarat Cat Wet Food Medium 100 50
Q1 2020 Ballarat Cat Dry Food Low 120 80
Q1 2020 Geelong Dog Dry Food Medium 300 100
Q1 2020 Melbourne Dog Wet Food Low 200 100
Q2 2020 Melbourne Dog Dry Food Medium 400 250
Q2 2020 Ballarat Cat Dry Food Medium 500 300
Q2 2020 Melbourne Cat Wet Food High 300 60
Q2 2020 Ballarat Dog Dry Food Low 350 180
Q2 2020 Melbourne Dog Dry Food High 300 50
Q2 2020 Geelong Dog Wet Food High 150 30
Q2 2020 Geelong Dog Dry Food High 350 50

a) Given the sample fact table above, write a SQL statement that can produce the following result:
TIME_PERIOD CITY PRODUCT AVERAGE_TOTAL_SALES_KG
Q1 2020 Geelong Dog Dry Food 250
Q1 2020 Geelong (null) 250
Q1 2020 Melbourne Dog Dry Food 300
Q1 2020 Melbourne Dog Wet Food 200
Q1 2020 Melbourne (null) 250
Q1 2020 (null) (null) 250
Q2 2020 Ballarat Dog Dry Food 350
Q2 2020 Ballarat (null) 350
Q2 2020 Geelong Dog Dry Food 350
Q2 2020 Geelong Dog Wet Food 150
Q2 2020 Geelong (null) 250
Q2 2020 Melbourne Dog Dry Food 350
Q2 2020 Melbourne (null) 350
Q2 2020 (null) (null) 310
(null) (null) (null) 283.33

b) Write a SQL statement that generates the following table, which shows the total transactions of every
product in each city. The table also contains the sum of total transactions by products and by cities.

PRODUCT CITY TOTAL_TRANSACTIONS
All Products All Cities 1440
All Products Ballarat 610
All Products Geelong 230
All Products Melbourne 600
Cat Dry Food All Cities 420
Cat Dry Food Ballarat 380
Cat Dry Food Melbourne 40 Cat Wet Food All Cities 110
Cat Wet Food Ballarat 50
Cat Wet Food Melbourne 60
Dog Dry Food All Cities 780
Dog Dry Food Ballarat 180
Dog Dry Food Geelong 200
Dog Dry Food Melbourne 400
Dog Wet Food All Cities 130
Dog Wet Food Geelong 30
Dog Wet Food Melbourne 100