Q2. Consider the table given below: The ApplianceSales table gives you informati
ID: 3548446 • Letter: Q
Question
Q2. Consider the table given below:
The ApplianceSales table gives you information about Appliance sales for Minnesota and Wisconsin for the years 2003, 2004 and 2005.
a. Compute the full data cube operation for the above data set and show the extended table. Make sure that the individual sub-tables are clearly marked
b. Which component of the data cube is best for computing/answering the query below, and why? Show a query in which the GROUP BY clause can be eliminated
SELECT SUM(Sales)
FROM ApplianceSales
GROUP BY State
c. Which component of the data cube is best for computing/answering the query below? Why?
SELECT SUM(Sales)
FROM ApplianceSales
WHERE Year <= 2004
d. Which component of the data cube is best for computing/answering the query below? Why?
SELECT SUM(Sales)
FROM ApplianceSales
WHERE Year <= 2004 AND Color =
Consider the table given below: The Appliance Sales table gives you information about Appliance sales for Minnesota and Wisconsin for the years 2003, 2004 and 2005. Compute the full data cube operation for the above data set and show the extended table. Make sure that the individual sub-tables are clearly marked Which component of the data cube is best for computing/answering the query below, and why? Show a query in which the GROUP BY clause can be eliminated Which component of the data cube is best for computing/answering the query below? Why? Which component of the data cube is best for computing/answering the query below? Why?Explanation / Answer
create table appliancesales
(state varchar2(10),
color varchar2(10),
year number(4),
sales number(3)
);
insert into appliancesales values('MN','White',2003,28);
insert into appliancesales values('MN','Silver',2003,17);
insert into appliancesales values('MN','White',2004,33);
insert into appliancesales values('MN','Silver',2004,30);
insert into appliancesales values('MN','White',2005,47);
insert into appliancesales values('MN','Silver',2005,50);
insert into appliancesales values('WI','White',2003,13);
insert into appliancesales values('WI','Silver',2003,20);
insert into appliancesales values('WI','White',2004,16);
insert into appliancesales values('WI','Silver',2004,25);
insert into appliancesales values('WI','White',2005,21);
insert into appliancesales values('WI','Silver',2005,30);
a)
SELECT state,
color,
year,
SUM(sales) AS sales_value
FROM appliancesales
GROUP BY CUBE (state, color,year)
ORDER BY state, color,year;
b)
Answer:cube(state) component
SELECT state,SUM(Sales)
FROM ApplianceSales
GROUP BY cube(state)
order by 1;
or
with out group by:
SELECT distinct state, sum(sales) OVER (PARTITION BY state) sum_sales
FROM ApplianceSales;
c) Answer:cube(year) component
SELECT year,SUM(Sales)
FROM ApplianceSales
WHERE Year <= 2004
GROUP BY cube(year)
order by 1;
d)
SELECT state,SUM(Sales)
FROM ApplianceSales
WHERE Year <= 2004 AND Color = 'White'
GROUP BY cube(state)
order by 1
state Color Year sales_value MN Silver 2003 17 MN Silver 2004 30 MN Silver 2005 50 MN Silver 97 MN White 2003 28 MN White 2004 33 MN White 2005 47 MN White 108 MN 2003 45 MN 2004 63 MN 2005 97 MN 205 WI Silver 2003 20 WI Silver 2004 25 WI Silver 2005 30 WI Silver 75 WI White 2003 13 WI White 2004 16 WI White 2005 21 WI White 50 WI 2003 33 WI 2004 41 WI 2005 51 WI 125 Silver 2003 37 Silver 2004 55 Silver 2005 80 Silver 172 White 2003 41 White 2004 49 White 2005 68 White 158 2003 78 2004 104 2005 148 330Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.