Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Subiect 2 ECBD:
- 1) Afisati suma vanzarilor ("amount_sold") pentru clientii din Franta ("FR") grupat pe
- trimestrele ("calendar_quarter_desc") anului 2001. De asemenea sa se afiseze si vanzarile cumulate
- pe trimestre de la inceputul anului 2001. Informatii de afisat: clientul (cust_id), trimestrul,
- suma vanzarilor pe trimestrul curent, suma cumulata pe trimestrele anterioare. Ordonarea si gruparea
- se face dupa client si trimestru.
- Rezolvare:
- SELECT cust.cust_id, t.calendar_quarter_desc, SUM(s.amount_sold) AS 'suma_trim_curent',
- SUM(SUM(s.amount_sold) over (PARTITION BY t.calendar_quarter_desc ORDER BY cust.cust_id) AS 'sum_trim_cumul')
- FROM customers cust, countries co, sales s, times t
- WHERE cust.cust_id = s.cust_id
- AND s.time_id = t.time_id
- AND cust.country_id = co.country_id
- AND t.calendar_year = '2001'
- AND co.country_iso_code = 'FR'
- GROUP BY cust.cust_id, t.calendar_quarter_desc;
- 2) Se dau tabelele: PRODUCTS, TIMES, CHANNELS, COSTS.
- Creati un tabel SUMCOSTS avand coloanele PROD_NAME, PROD_SUBCATEGORY, CALENDAR_MONTH_NUMBER, UNIT_COST.
- Continutul tabelei este dat de extragerea costurilor pe produse, cumulare la nivel de luna,
- pentru canalul de distributie = "Direct Sales", pentru 1999.
- Din acelasi tabel sa se afiseze primele 10 categorii de produse cu costurile cele mai mici.
- Rezolvare:
- CREATE TABLE sumcosts AS
- (
- SELECT p.prod_name AS prod_name, p.prod_subcategory AS prod_subcategory, t.calendar_month_number AS calendar_month_number,
- -- sum(co.unit_cost) over (order by t.calendar_quarter_number) AS unit_cost
- SUM(co.unit_cost) over (PARTITION BY t.calendar_month_number ORDER BY p.prod_name) AS unit_cost
- FROM products p, times t, costs co, channels ch
- WHERE p.prod_id = co.prod_id
- AND co.time_id = t.time_id
- AND co.channel_id = ch.channel_id
- AND ch.channel_desc = 'Direct Sales'
- AND t.calendar_year = '1999'
- );
- SELECT *
- FROM
- (SELECT DISTINCT p.prod_name,
- DENSE_RANK() over (ORDER BY SUM(co.unit_cost)) sumC
- FROM products p
- )
- WHERE sumC<= 10;
- =====================================================================================================
- 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.calenda
- 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;
- 9) Creati un VIEW materializat optim care sa stocheze costurile produselor pe luni calendaristice
- si pe canale de distributie.
- =========================================================================================================
- 1) 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 c.channel_desc, t.calendar_month_desc,
- NVL(GROUPING(c.channel_desc), 'Toate canalele'),
- NVL(GROUPING(t.calendar_month_desc), 'Toate lunile'),
- SUM(cs.unit_cost)
- FROM channels c, times t, costs cs, sales s
- WHERE cs.time_id=t.time_id AND s.channel_id=c.channel_id AND s.prod_id=cs.prod_id
- AND (c.channel_desc='Direct Sales' OR c.channel_desc='Internet')
- AND t.calendar_month_desc IN ('2000-10', '2000-11', '2000-12')
- GROUP BY rollup( c.channel_desc, t.calendar_month_desc);
- 2)Afisati topul primelor 5 tari la vanzari pe ultimul trimestru (tara, total vanzari, pozitie).
- SELECT * FROM (
- SELECT c.country_name, SUM(s.anount_sold),
- DENSE_RANK() over (ORDER BY SUM(s.anount_sold)) pozitie
- FROM countries c, sales s, customers cs
- WHERE c.country_id=ch.country_id AND ch.cust_id=s.cust_id
- GROUP BY c.country_name;
- ) WHERE pozitie<=5;
- 1) 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, SUM(s.amount_sales),
- LAG(SUM(s.amount_sales),1) over (ORDER BY t.day_number_in_month) LAG,
- leag(SUM(s.amount_sales),1) over (ORDER BY t.day_number_in_month) LEAD
- FROM times t, sales s
- WHERE t.time_id=s.time_id AND t.calendar_month_number=11
- GROUP BY t.day_number_in_month;
- 2) 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 AS
- (
- SELECT c.cust_first_name, c.cust_last_name, c.cust_city, t.caledar_month_desc,
- SUM(SUM(s.quality_sold) over (PARTITION BY c.cust_last_name ORDER BY t.calendar_quarter_number rows unbound preceding)) cantitate,
- SUM(SUM(s.amount_sold) over (PARTITION BY c.cust_last_name ORDER bt t.calendar_quarter_number roqs unbounded preceding)) vanzari
- FROM customers c, times t, sales s, countries cc
- WHERE c.cust_id=s.cust_id AND s.time_id=t.time_id AND c.country_id=cc.country_id
- AND cc.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);
- );
- 1) 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 c.cust_id, t.calendar_quarter_number, SUM(s.amount_sales),
- SUM(SUM(s.amount_sales)) over (PARTITION BY t.calendar_quarter_number ORDER BY c.cust_id rows unbounded preceding)
- FROM sales s, customers c, times t
- WHERE s.cust_id=sales.cust_id AND s.time_id=t.time_id
- AND c.cust_id IN (2802, 2807) AND t.calendar_year=2000
- GROUP BY c.cust_id, t.calendar_quarter_number;
- 2) 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(c.unit_cost), t.calendar_month_desc, ch.channel_desc
- FROM costs c, times t, channel ch, sales s
- WHERE ch.channel_id=s.channel_id AND s.time_id=t.time_id AND s.prod_id=c.prod_id AND s.time_id=cs.time_id
- GROUP BY t.calendar_month_desc, ch.channel_desc;
- CREATE materialized VIEW cost_prod AS
- ON prebuilt TABLE without reduced precision
- AS
- SELECT SUM(c.unit_cost), t.calendar_month_desc, ch.channel_desc
- FROM costs c, times t, channel ch, sales s
- WHERE ch.channel_id=s.channel_id AND s.time_id=t.time_id AND s.prod_id=c.prod_id AND s.time_id=cs.time_id
- GROUP BY t.calendar_month_desc, ch.channel_desc;
- 1) Afisati suma vanzarilor ("amount_sold") pentru clientii din Franta ("FR") grupat pe
- trimestrele ("calendar_quarter_desc") anului 2001. De asemenea sa se afiseze si vanzarile cumulate
- pe trimestre de la inceputul anului 2001. Informatii de afisat: clientul (cust_id), trimestrul,
- suma vanzarilor pe trimestrul curent, suma cumulata pe trimestrele anterioare. Ordonarea si gruparea
- se face dupa client si trimestru.
- SELECT c.cust_id, t.calendar_quarter_number, SUM(s.smount_sold),
- SUM(SUM(s.amount_sold) over (PARTITION BY t.calendar_quarter_number ORDER BY c.cust_id rowe unbounded preceding))
- FROM customers cust, countries co, sales s, times t
- WHERE cust.cust_id = s.cust_id AND s.time_id = t.time_id AND cust.country_id = co.country_id
- AND t.calendar_year = '2001'
- AND co.country_iso_code = 'FR'
- GROUP BY cust.cust_id, t.calendar_quarter_desc;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement