olapprac.txt

create table temperature(
temp_id int PRIMARY KEY,
temp_range varchar(10),
temp_desc varchar(20)
);

insert into temperature values (1, '5 to 20' , 'cold');
insert into temperature values (2, '5 to 20' , 'cold');
insert into temperature values (3, '3 to 17' , 'cold');
insert into temperature values (4, '3 to 17' , 'cold');

select * from temperature;

create table region(
reg_id int PRIMARY KEY,
probe_loc varchar(50),
city varchar(50),
state varchar(50)
);

insert into region values(1, '30 N', 'Mumbai', 'Maha');
insert into region values(2, '20 S', 'Kolkata', 'Karnataka');
insert into region values(3, '23 W', 'Bangalore', 'Karnataka');
insert into region values(4, '15 E', 'Chennai', 'TN');

select * from region;


create table prec(
prec_id int PRIMARY KEY,
prec_range varchar(30),
prec_desc varchar(30)
);

insert into prec values(1, '20-30mm', 'high');
insert into prec values(2, '10-15mm', 'med');
insert into prec values(3, '35-45mm', 'very high');
insert into prec values(4, '5-15mm', 'low');

select * from prec;

create table fact(
temp_id int REFERENCES temperature(temp_id),
reg_id int REFERENCES region(reg_id),
prec_id int REFERENCES prec(prec_id),
area int
);

insert into fact (temp_id, reg_id, prec_id)SELECT temperature.temp_id, region.reg_id, prec.prec_id from temperature, region, prec;

UPDATE fact set area=100 where reg_id=1;
UPDATE fact set area=200 where reg_id=2;
UPDATE fact set area=300 where reg_id=3;
UPDATE fact set area=400 where reg_id=4;

select * from fact;

--slice
select city, area from fact inner join region on fact.reg_id = region.reg_id where city = 'Chennai';


--dice
select city, fact.area from (region inner join fact on region.reg_id = fact.reg_id) 
join temperature on fact.temp_id = temperature.temp_id
where city = 'Mumbai' or city = 'Kolkata' and temp_range = '5 to 20' or temp_range = '3 to 17';

--roll up  -- keep one group value same in all colums (years)
select temp_desc, sum(area) from (fact natural join region) 
join temperature on fact.temp_id = temperature.temp_id 
where city = 'Kolkata' group by temp_desc;

--drill down
select temp_range, sum(area) from (fact natural join region) 
join temperature on fact.temp_id = temperature.temp_id 
where city = 'Mumbai' group by temp_range;