Sales ( SalesNo , LocationID, TimeID, TypeID, CarsSold, DollarSold) Location ( L

Question

Sales(SalesNo, LocationID, TimeID, TypeID, CarsSold, DollarSold)

Location(LocationID, State, City, Store)

Time(TimeID, Year, Quarter, Month)
CarType(TypeID, Brand, Model, Trim)

(a) (10 points) Generate a report on the total number of [‘Toyota’(Brand) ‘RAV4’(Model) ‘LE’ (Trim)] sold in each states during each quarter of year. List the results by state alphabetically and then by quarter in ascending order;

(b) (10 points) Write SQL to implement the CUBE operation on given data to summarize total CarsSold over the following dimensions: (Year, State, Brand). Sort the results based on Year first, then State and third Brand, from low to high. You only need to show the first 20 rows in your result and report the total number of rows in your output. Based on the definition of the CUBE operation, describe how to hand-compute the total number of rows in the output;

(c) (10 points) Write SQL to implement the ROLLUP operation on given data to summarize average DollarSold over the following hierarchical dimensions: (Month, Brand). Sort the results basedonMonthfirst,thenBrandfromlowtohigh.Youonlyneedtoshowthefirst 20 rows in your result and report the total number of rows in your output. Based on the definition of the ROLLUP operation, describe how to hand-compute the total number of rows in the output;

(d) (5 points) Suppose our sample data is just a small portion of the entire data warehouse from the company. The company has car sales records for every month in the past 3 years at each of the 300 stores in the United States. Also the sales records involved totally 20 different trims of cars, which belong to 4 different brands. Calculate how many rows there should be in the fact table of the given star schema.

Explanation / Answer

a)select count(*),state,quarter
from cartype c
left join sales s
on C.TypeID=S.typeID
left join time t
on S.timeID=t.TimeID
left join Location l
on S.LocationID=l.LocationID
where brand='Toyota' and model='RAV4' and Trim='LE'
group by state,quarter
order by State asc,quarter asc

b)select top 20 year,state,brand,count(CarsSold)
from cartype c
left join sales s
on C.TypeID=S.typeID
left join time t
on S.timeID=t.TimeID
left join Location l
on S.LocationID=l.LocationID
group by cube(year,state,brand)
order by year asc,state asc,brand asc

c)select to 20 avg(dollarsold) from Sales S
left join time t
on t.TimeID=S.TimeID
left join Cartype C
on S.typeID=C.typeID
group by rollup(month,brand);

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.