1) Afisati subtotalurile si suma vanzarilor pentru urmatoarele
dimensiuni:
– denumirea canalului de distributie (channel_desc), luna de vanzare
(calendar_month_desc) si
prescurtarea tarii (country_iso_code);
– filtrati liniile dupa urmatoarele conditii: canalul de distributie sa
fie 'Tele Sales' si 'Internet';
trimestrul de vanzare al doilea al anului 2000;
tara de desfacere sa fie Statele Unite ale Americii si Marea Britanie.
- pentru valorile NULL generate de extensia GROUP BY afisati valorile:
'Toate canalele',
'Toate lunile' si 'Toate tarile'.
select nvl(c.channel_desc,'Toate Canalele'),
nvl(t.calendar_month_desc,'Toate Lunile'),
nvl(co.country_iso_code,'Toate Tarile'),
sum(s.amount_sold) as Suma
from channels c,times t,countries co,sales s,customers cust
where c.channel_id = s.channel_id and
s.time_id = t.time_id and
s.cust_id = cust.cust_id and
cust.country_id = co.country_id and
c.channel_desc in ('Direct Sales','Internet') and
t.calendar_quarter_number = 2 and
t.calendar_year = '2000' and
co.country_iso_code in ('US','UK')
group by rollup(c.channel_desc, t.calendar_month_desc,
co.country_iso_code)
2) Afisati si analizati planul de executie al interogarii anterioare.
Creati indexii corespunzatori, explicand scopul crearii fiecaruia.
Afisati noul plan de executie care sa evidentieze utilizarea
indexilor.
primii 4 indecsi sunt pentru coloanele din conditia join,acestia fiind
cea mai
eficienta metoda de acces la date
umatorii 4 indecsi sunt de tip bitmap,acestai pot imbunatati
performantele interogarilor
ad-hoc ce includ expresii avand coloane de cardinalitate redusa in
clauza where,coloane conecatate
cu operatori and,or
--create index index_Channel on sales (channel_id);
--create index index_Time_id on sales (time_id);
--create index index_Cust_id on sales (cust_id);
--create index index_Country_id on customers (country_id);
--CREATE BITMAP INDEX index_calendar_quarter_number ON times
(calendar_quarter_number);
--CREATE BITMAP INDEX index_country_iso_code ON times
(country_iso_code);
--CREATE BITMAP INDEX index_calendar_year ON times (calendar_year);
CREATE BITMAP INDEX index_channel_desc ON channels (channel_desc);
explain plan for
select nvl(c.channel_desc,'Toate Canalele'),
nvl(t.calendar_month_desc,'Toate Lunile'),
nvl(co.country_iso_code,'Toate Tarile'),
sum(s.amount_sold) as Suma
from channels c,times t,countries co,sales s,customers cust
where c.channel_id = s.channel_id and
s.time_id = t.time_id and
s.cust_id = cust.cust_id and
cust.country_id = co.country_id and
c.channel_desc in ('Direct Sales','Internet') and
t.calendar_quarter_number = 2 and
t.calendar_year = '2000' and
co.country_iso_code in ('US','UK')
group by rollup(c.channel_desc, t.calendar_month_desc,
co.country_iso_code);
select plan_table_output from
table(dbms_xplan.display('plan_table',null,'serial'));
3) Afisati suma vanzarilor (amount§sold) pentru clientii 2802 si 2807
grupat pe trimestrele
anului 2000. De asemenea sa se afiseze si vanzarile cumulate pe trimestre
de la inceputul
anului 2000. Informatii de afisat: clientul (id), trimestrul, suma
vanzarilor pe trimestrul curent,
suma cumulata pe trimestrele anterioare. Ordonarea si gruparea se face
dupa client si trimestru.
select t.calendar§quarter§desc, cust.cust§id, sum(s.amount§sold),
sum(sum(s.amount§sold)) over (partition by cust.cust§id
order by t.calendar§quarter§desc rows unbounded preceding)
from sales s, times t, customers cust
where s.time§id = t.time§id and s.cust§id = cust.cust§id and cust.cust§id
in (2802,2807) and
t.calendar§year = '2000'
group by cust.cust§id, t.calendar§year, t.calendar§quarter§desc;
/
4) Creati un view materializat optim care sa stocheze costurile
produselor pe luni calendaristice
si pe canale de distributie.
CREATE TABLE cost§prod
AS
select sum(cs.unit§cost) "Total§Cost", t.calendar§month§number,
ch.channel§desc
from costs cs, times t, channels ch, sales s
where ch.channel§id=s.channel§id and s.time§id=t.time§id and
s.prod§id=cs.prod§id
and s.time§id=cs.time§id
group by t.calendar§month§number, ch.channel§desc;
CREATE MATERIALIZED VIEW cost§prod
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
AS
select sum(cs.unit§cost) "Total§Cost", t.calendar§month§number,
ch.channel§desc
from costs cs, times t, channels ch, sales s
where ch.channel§id=s.channel§id and s.time§id=t.time§id and
s.prod§id=cs.prod§id
and s.time§id=cs.time§id
group by t.calendar§month§number, ch.channel§desc;
5) Afisati suma vanzarilor pe zile pe luna octombrie 2000. Adaugati doua
coloane, una
care sa afiseze suma vanzarilor pentru linia anterioara si una cu suma
vanzarilor pentru linia
urmatoare.
select t.day§number§in§month "ZiuaInLuna",sum(s.amount§sold) as
"SumaVanzarilor",
lag(sum(s.amount§sold),1) over (order by t.day§number§in§month)
"SumaLiniaAnteroara",
lead(sum(s.amount§sold),1) over (order by t.day§number§in§month)
"SumaLiniaUrmatoare"
from sales s, times t, customers cust
where s.time§id = t.time§id and
s.cust§id = cust.cust§id and
t.calendar§month§desc = '2000-10' and
t.day§number§in§month between 1 and 31
group by t.day§number§in§month;
6) Folosind SQL sau PL/SQL, creati un tabel avand coloanele
CUST§FIRST§NAME, CUST§LAST§NAME,
CUST§CITY, CALENDAR§MONTH§NAME, QUANTITY§SOLD, AMOUNT§SOLD
.
Continutul tabelei este dat de extragerea cantitatii si
valoarii vanzarilor,cumulate la nivel de luna, pentru clientii din Marea
Britanie,
pentru primele doua trimestre ale anului 2000.
--create table tabela
--(
--CUST§FIRST§NAME char(20),
--CUST§LAST§NAME char(20),
--CUST§CITY char(30),
--CALENDAR§MONTH§NAME char(20),
--QUANTITY§SOLD number(20,2),
--AMOUNT§SOLD number(20,2)
--);
create table tabela as
(
select c.cust§first§name,c.cust§last§name,c.cust§city,
t.calendar§month§name,sum(sum(c.quantity§sold))
over (partition by c.cust§last§name order by t.calendar§quarter§number
rows unbounded preceding) as "CantitateaVanduta",
sum(sum(s.amount§sold) over (partition by c.cust§last§name
order by t.calendar§name§number rows unbounded precending) as
"SumaVanduta")
from customer c,times t, sales s, countries p
where c.cust§id=s.cust§id and
t.time§id=s.time§id and
p.country§name='United§kingdom' and t.calendar§quarter§number=1
or t.calendar§quarter§number=2
and t.calendar§year=2000
group by
(c.cust§last§name,t.calendar§quarter§number,t.calendar§year)
);
ç
7) Afisati subtotalurile si suma costurilor pentru urmatoarele
dimensiuni:
– denumirea canalului de distributie (channel_desc), luna de vanzare
(calendar_month_desc) ;
– filtrati liniile dupa urmatoarele conditii: canalul de distributie sa
fie 'Direct Sales' si 'Internet';
trimestrul de vanzare al treilea al anului 2000;
- pentru valorile NULL generate de extensia GROUP BY afisati valorile:
'Toate canalele', 'Toate lunile'.
SELECT ch.channel_desc, t.calendar_month_desc,
decode(grouping(ch.channel_desc),1,'Toate canalele',ch.channel_desc) as
Ch,
decode(grouping(t.calendar_month_desc),1,'Toate
lunile',t.calendar_month_desc) as Cal,
sum(cs.unit_cost) as Suma
FROM channels ch, sales s, costs cs, times t
WHERE (ch.channel_desc = 'Direct Sales' OR ch.channel_desc='Internet')
AND t.calendar_month_desc IN ('2000-10','2000-11','2000-12')
AND ch.channel_id = s.channel_id
AND s.time_id = t.time_id
AND s.prod_id = cs.prod_id
GROUP BY rollup( t.calendar_month_desc,ch.channel_desc);
8)Afisati topul primelor 5 tari la vanzari pe ultimul trimestru (tara,
total vanzari, pozitie).
SELECT c.country_name, sum(s.amount_sold) as Suma,
dense_rank() over ( order by sum(s.amount_sold) desc) as Rank
FROM countries c, customers cus, sales s
WHERE c.country_id = cus.country_id AND
cus.cust_id = s.cust_id
GROUP BY c.country_name;