SET NOCOUNT ON
go
use pubs
go
IF EXISTS (SELECT * FROM sysobjects WHERE name='automobile_sales_detail' and type='U')
drop table automobile_sales_detail
go
IF EXISTS (SELECT * FROM sysobjects WHERE name='auto_cube' and type='V')
drop view auto_cube
go
create table automobile_sales_detail
(
rowid int not null identity ,
model char(6) null ,
year smallint null check (year between 1985 and 2000),
color char(5) not null,
units_sold int not null
)
go
insert automobile_sales_detail values ('Chevy',1990,'Red',5)
insert automobile_sales_detail values ('Chevy',1990,'White',87)
insert automobile_sales_detail values ('Chevy',1990,'Blue',62)
insert automobile_sales_detail values ('Chevy',1991,'Red',54)
insert automobile_sales_detail values ('Chevy',1991,'White',95)
insert automobile_sales_detail values ('Chevy',1991,'Blue',49)
insert automobile_sales_detail values ('Chevy',1992,'Red',31)
insert automobile_sales_detail values ('Chevy',1992,'White',54)
insert automobile_sales_detail values ('Chevy',1992,'Blue',71)
insert automobile_sales_detail values ('Ford',1990,'Red',64)
insert automobile_sales_detail values ('Ford',1990,'White',62)
insert automobile_sales_detail values ('Ford',1990,'Blue',63)
insert automobile_sales_detail values ('Ford',1991,'Red',52)
insert automobile_sales_detail values ('Ford',1991,'White',9)
insert automobile_sales_detail values ('Ford',1991,'Blue',55)
insert automobile_sales_detail values ('Ford',1992,'Red',27)
insert automobile_sales_detail values ('Ford',1992,'White',62)
insert automobile_sales_detail values ('Ford',1992,'Blue',39)
insert automobile_sales_detail values (NULL,NULL,'White',10)
go
SET NOCOUNT OFF
go
select * from automobile_sales_detail
go
create view auto_cube (units, model, year, color) as
select sum(units_sold),
CASE WHEN (grouping(model)=1) THEN 'ALL'
ELSE isnull(model,'????')
END,
CASE WHEN (grouping(year)=1) THEN 'ALL'
ELSE isnull(convert(char(6),year),'????')
END,
CASE WHEN (grouping(color)=1) THEN 'ALL'
ELSE isnull(color,'????')
END
from automobile_sales_detail
group by model,year,color with cube
go
select * from auto_cube
go
-- Find all Chevy sales.
select * from auto_cube where model='Chevy' and year='ALL' and color='ALL'
go
