drop table sales;

create table sales(item_name char(10), 
        color char(7), 
	ssize char(10), 
	snumber integer, 
	primary key (item_name,color,ssize));

 create table categories (category char(20), 
	item_name char(10),
	primary key (category, item_name));

insert into sales values('skirt','dark','large',5);
insert into sales values('skirt','dark','medium',2);
insert into sales values('skirt','dark','small',1);

insert into sales values('skirt','pastel','small',10);
insert into sales values('skirt','pastel','medium',15);
insert into sales values('skirt','pastel','large',10);

insert into sales values('skirt','white','small',5);
insert into sales values('skirt','white','large',2);
insert into sales values('skirt','white','medium',3);

insert into sales values('dress','dark','large',10);
insert into sales values('dress','dark','medium',8);
insert into sales values('dress','dark','small',2);

insert into sales values('dress','pastel','large',5);
insert into sales values('dress','pastel','medium',2);
insert into sales values('dress','pastel','small',3);

insert into sales values('dress','white','large',1);
insert into sales values('dress','white','medium',1);
insert into sales values('dress','white','small',3);


insert into sales values('shirt','dark','large',10);
insert into sales values('shirt','dark','medium',2);
insert into sales values('shirt','dark','small',2);

insert into sales values('shirt','pastel','large',5);
insert into sales values('shirt','pastel','medium',2);
insert into sales values('shirt','pastel','small',0);

insert into sales values('shirt','white','large',10);
insert into sales values('shirt','white','medium',10);
insert into sales values('shirt','white','small',8);


insert into sales values('pant','dark','large',10);
insert into sales values('pant','dark','medium',6);
insert into sales values('pant','dark','small',4);

insert into sales values('pant','pastel','large',1);
insert into sales values('pant','pastel','medium',1);
insert into sales values('pant','pastel','small',0);

insert into sales values('pant','white','large',1);
insert into sales values('pant','white','medium',2);
insert into sales values('pant','white','small',2);

insert into categories values('women','skirt');
insert into categories values('women','dress');
insert into categories values('men','shirt');
insert into categories values('men','pant');


/*
 select item_name, color, ssize, sum(snumber) from sales group by
 cube(item_name, color, ssize);

 select item_name, color, ssize, sum(snumber) from sales group by
 rollup(item_name, color, ssize);

 select item_name, color, ssize, sum(snumber), 
        grouping(item_name) as item_name_flag,
        grouping(color) as color_flag,
        grouping(ssize) as ssize_flag
  from sales
 group by  cube(item_name, color, ssize);

 create view mytotal as 
     select item_name, color, ssize, sum(snumber) as total 
    from sales group by  cube(item_name, color, ssize) ;

 select item_name, rank() over (order by (total) desc) as s_rank
    from mytotal;

select item_name, color, ssize, rank() over(order by (snumber) desc) as sale_rank from sales;

select item_name, color, ssize, snumber, rank() over(order by (snumber) desc) as sale_rank from sales;


 ---- the answer for the first question of the quiz 5

 select item_name, color, sum(snumber) 
 from sales 
 group by cube(item_name, color);

 ---- the answer for the second question of the quiz 5

 select category, s.item_name, color, sum(snumber) 
 from sales s, categories c 
 where c.item_name = s.item_name
 group by  rollup(category,s.item_name), rollup(color);


*/
