Advertisement
Guest User

alex2

a guest
Apr 24th, 2017
82
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 15.69 KB | None | 0 0
  1. Subiect 2 ECBD:
  2.  
  3.     1) Afisati suma vanzarilor ("amount_sold") pentru clientii din Franta ("FR") grupat pe
  4. trimestrele ("calendar_quarter_desc") anului 2001. De asemenea sa se afiseze si vanzarile cumulate
  5. pe trimestre de la inceputul anului 2001. Informatii de afisat: clientul (cust_id), trimestrul,
  6. suma vanzarilor pe trimestrul curent, suma cumulata pe trimestrele anterioare. Ordonarea si gruparea
  7. se face dupa client si trimestru.
  8.  
  9.     Rezolvare:
  10.    
  11.     SELECT cust.cust_id, t.calendar_quarter_desc, SUM(s.amount_sold) AS 'suma_trim_curent',
  12.         SUM(SUM(s.amount_sold) over (PARTITION BY t.calendar_quarter_desc ORDER BY cust.cust_id) AS 'sum_trim_cumul')
  13.     FROM customers cust, countries co, sales s, times t
  14.     WHERE   cust.cust_id = s.cust_id
  15.         AND s.time_id = t.time_id
  16.         AND cust.country_id = co.country_id
  17.         AND t.calendar_year = '2001'
  18.         AND co.country_iso_code = 'FR'
  19.     GROUP BY cust.cust_id, t.calendar_quarter_desc;
  20.    
  21.    
  22.    
  23.  
  24.     2) Se dau tabelele: PRODUCTS, TIMES, CHANNELS, COSTS.
  25.     Creati un tabel SUMCOSTS avand coloanele PROD_NAME, PROD_SUBCATEGORY, CALENDAR_MONTH_NUMBER, UNIT_COST.
  26. Continutul tabelei este dat de extragerea costurilor pe produse, cumulare la nivel de luna,
  27. pentru canalul de distributie = "Direct Sales", pentru 1999.
  28.  
  29.     Din acelasi tabel sa se afiseze primele 10 categorii de produse cu costurile cele mai mici.
  30.    
  31.     Rezolvare:
  32.    
  33.     CREATE TABLE sumcosts AS
  34.     (
  35.         SELECT p.prod_name AS prod_name, p.prod_subcategory AS prod_subcategory, t.calendar_month_number AS calendar_month_number,
  36.         --  sum(co.unit_cost) over (order by t.calendar_quarter_number) AS unit_cost
  37.             SUM(co.unit_cost) over (PARTITION BY t.calendar_month_number ORDER BY p.prod_name) AS unit_cost
  38.         FROM products p, times t, costs co, channels ch
  39.         WHERE   p.prod_id = co.prod_id
  40.             AND co.time_id = t.time_id
  41.             AND co.channel_id = ch.channel_id
  42.             AND ch.channel_desc = 'Direct Sales'
  43.             AND t.calendar_year = '1999'
  44.     );
  45.    
  46.     SELECT *
  47.     FROM
  48.         (SELECT DISTINCT p.prod_name,
  49.             DENSE_RANK() over (ORDER BY SUM(co.unit_cost)) sumC
  50.         FROM products p
  51.         )
  52.     WHERE sumC<= 10;
  53.        
  54.        
  55. =====================================================================================================
  56. 1) Afisati subtotalurile si suma vanzarilor pentru urmatoarele dimensiuni:
  57. – denumirea canalului de distributie (channel_desc), luna de vanzare (calendar_month_desc) si
  58. prescurtarea tarii (country_iso_code);
  59. – filtrati liniile dupa urmatoarele conditii: canalul de distributie sa fie 'Tele Sales' si 'Internet';
  60.    trimestrul de vanzare al doilea al anului 2000;
  61.   tara de desfacere sa fie Statele Unite ale Americii si Marea Britanie.
  62. - pentru valorile NULL generate de extensia GROUP BY afisati valorile: 'Toate canalele',
  63.     'Toate lunile' si 'Toate tarile'.
  64.  
  65.  
  66. SELECT NVL(c.channel_desc,'Toate Canalele'),
  67.        NVL(t.calendar_month_desc,'Toate Lunile'),
  68.        NVL(co.country_iso_code,'Toate Tarile'),
  69.        SUM(s.amount_sold) AS Suma
  70.         FROM channels c,times t,countries co,sales s,customers cust
  71.         WHERE c.channel_id = s.channel_id AND
  72.               s.time_id = t.time_id AND
  73.               s.cust_id = cust.cust_id AND
  74.               cust.country_id = co.country_id AND
  75.               c.channel_desc IN ('Direct Sales','Internet') AND
  76.               t.calendar_quarter_number = 2 AND
  77.               t.calendar_year = '2000' AND
  78.               co.country_iso_code IN ('US','UK')
  79.               GROUP BY rollup(c.channel_desc, t.calendar_month_desc, co.country_iso_code)
  80.                  
  81.  
  82.  
  83. 2) Afisati si analizati planul de executie al interogarii anterioare. Creati indexii corespunzatori, explicand scopul crearii fiecaruia.
  84.    Afisati noul plan de executie care sa evidentieze utilizarea indexilor.
  85.  
  86. primii 4 indecsi sunt pentru coloanele din conditia join,acestia fiind cea mai
  87. eficienta metoda de acces la DATE
  88. umatorii 4 indecsi  sunt de tip bitmap,acestai pot imbunatati performantele interogarilor
  89.    ad-hoc ce includ  expresii avand coloane de cardinalitate redusa IN clauza WHERE,coloane conecatate
  90.  cu operatori AND,OR
  91.  
  92.  
  93.  
  94.  
  95. --create index index_Channel on sales (channel_id);
  96. --create index index_Time_id on sales (time_id);
  97. --create index index_Cust_id on sales (cust_id);
  98. --create index index_Country_id on customers (country_id);
  99. --CREATE BITMAP  INDEX index_calendar_quarter_number ON times (calendar_quarter_number);
  100. --CREATE BITMAP  INDEX index_country_iso_code ON times (country_iso_code);
  101. --CREATE BITMAP  INDEX index_calendar_year ON times (calendar_year);
  102.   CREATE BITMAP  INDEX index_channel_desc ON channels (channel_desc);
  103.  
  104. explain plan FOR
  105. SELECT NVL(c.channel_desc,'Toate Canalele'),
  106.        NVL(t.calendar_month_desc,'Toate Lunile'),
  107.        NVL(co.country_iso_code,'Toate Tarile'),
  108.        SUM(s.amount_sold) AS Suma
  109.         FROM channels c,times t,countries co,sales s,customers cust
  110.         WHERE c.channel_id = s.channel_id AND
  111.               s.time_id = t.time_id AND
  112.               s.cust_id = cust.cust_id AND
  113.               cust.country_id = co.country_id AND
  114.               c.channel_desc IN ('Direct Sales','Internet') AND
  115.               t.calendar_quarter_number = 2 AND
  116.               t.calenda
  117.  
  118.  
  119.  
  120. 3) Afisati suma vanzarilor (amount§sold) pentru clientii 2802 si 2807 grupat pe trimestrele
  121. anului 2000. De asemenea sa se afiseze si vanzarile cumulate pe trimestre de la inceputul
  122. anului 2000. Informatii de afisat: clientul (id), trimestrul, suma vanzarilor pe trimestrul curent,
  123. suma cumulata pe trimestrele anterioare. Ordonarea si gruparea se face dupa client si trimestru.
  124.  
  125.  
  126. SELECT t.calendar§quarter§DESC, cust.cust§id, SUM(s.amount§sold),
  127.        SUM(SUM(s.amount§sold)) over (PARTITION BY cust.cust§id
  128.            ORDER BY t.calendar§quarter§DESC  rows unbounded preceding)
  129. FROM sales s, times t, customers cust
  130. WHERE s.TIME§id = t.TIME§id AND s.cust§id = cust.cust§id AND cust.cust§id IN (2802,2807) AND
  131.        t.calendar§YEAR = '2000'
  132. GROUP BY cust.cust§id, t.calendar§YEAR, t.calendar§quarter§DESC;
  133.  
  134. /
  135.  
  136. 4) Creati un VIEW materializat optim care sa stocheze costurile produselor pe luni calendaristice
  137. si pe canale de distributie.
  138.  
  139. CREATE TABLE cost§prod
  140. AS
  141. SELECT SUM(cs.unit§cost) "Total§Cost", t.calendar§MONTH§NUMBER, ch.channel§DESC
  142. FROM costs cs, times t, channels ch, sales s
  143. WHERE ch.channel§id=s.channel§id AND s.TIME§id=t.TIME§id AND s.prod§id=cs.prod§id
  144.       AND s.TIME§id=cs.TIME§id
  145. GROUP BY t.calendar§MONTH§NUMBER, ch.channel§DESC;
  146.  
  147.  
  148. CREATE MATERIALIZED VIEW cost§prod
  149. ON PREBUILT TABLE WITHOUT REDUCED PRECISION
  150. AS
  151. SELECT SUM(cs.unit§cost) "Total§Cost", t.calendar§MONTH§NUMBER, ch.channel§DESC
  152. FROM costs cs, times t, channels ch, sales s
  153. WHERE ch.channel§id=s.channel§id AND s.TIME§id=t.TIME§id AND s.prod§id=cs.prod§id
  154.       AND s.TIME§id=cs.TIME§id
  155. GROUP BY t.calendar§MONTH§NUMBER, ch.channel§DESC;
  156.  
  157.  
  158.  
  159.  
  160. 5) Afisati suma vanzarilor pe zile pe luna octombrie 2000. Adaugati doua coloane, una
  161. care sa afiseze suma vanzarilor pentru linia anterioara si una cu suma vanzarilor pentru linia
  162. urmatoare.
  163.  
  164.  
  165. SELECT t.DAY§NUMBER§IN§MONTH "ZiuaInLuna",SUM(s.amount§sold) AS "SumaVanzarilor",
  166.   LAG(SUM(s.amount§sold),1) over (ORDER BY t.DAY§NUMBER§IN§MONTH) "SumaLiniaAnteroara",
  167.   LEAD(SUM(s.amount§sold),1) over (ORDER BY t.DAY§NUMBER§IN§MONTH) "SumaLiniaUrmatoare"
  168.     FROM sales s, times t, customers cust
  169.       WHERE s.TIME§id = t.TIME§id AND
  170.         s.cust§id = cust.cust§id AND
  171.         t.calendar§MONTH§DESC = '2000-10' AND
  172.         t.DAY§NUMBER§IN§MONTH BETWEEN 1 AND 31
  173.           GROUP BY t.DAY§NUMBER§IN§MONTH;
  174.  
  175.  
  176. 6) Folosind SQL sau PL/SQL, creati un tabel avand coloanele CUST§FIRST§NAME, CUST§LAST§NAME,
  177. CUST§CITY, CALENDAR§MONTH§NAME, QUANTITY§SOLD, AMOUNT§SOLD
  178. .
  179. Continutul tabelei este dat de extragerea cantitatii si
  180. valoarii vanzarilor,cumulate la nivel de luna, pentru clientii din Marea Britanie,
  181. pentru primele doua trimestre ale anului 2000.
  182. --create table tabela
  183. --(
  184. --CUST§FIRST§NAME char(20),
  185. --CUST§LAST§NAME char(20),
  186. --CUST§CITY char(30),
  187. --CALENDAR§MONTH§NAME char(20),
  188. --QUANTITY§SOLD number(20,2),
  189. --AMOUNT§SOLD number(20,2)
  190. --);
  191.  
  192. CREATE TABLE tabela AS
  193. (
  194. SELECT c.cust§FIRST§name,c.cust§LAST§name,c.cust§city,
  195. t.calendar§MONTH§name,SUM(SUM(c.quantity§sold))
  196.   over (PARTITION BY c.cust§LAST§name ORDER BY t.calendar§quarter§NUMBER
  197.     rows unbounded preceding) AS "CantitateaVanduta",
  198. SUM(SUM(s.amount§sold) over (PARTITION BY c.cust§LAST§name
  199.   ORDER BY t.calendar§name§NUMBER rows unbounded precending) AS "SumaVanduta")
  200.     FROM  customer c,times t, sales s, countries p
  201.       WHERE c.cust§id=s.cust§id AND
  202.         t.TIME§id=s.TIME§id AND
  203.         p.country§name='United§kingdom' AND t.calendar§quarter§NUMBER=1
  204.           OR t.calendar§quarter§NUMBER=2
  205.           AND t.calendar§YEAR=2000
  206.             GROUP BY (c.cust§LAST§name,t.calendar§quarter§NUMBER,t.calendar§YEAR)
  207.             );
  208. ç
  209.  
  210.  
  211. 7) Afisati subtotalurile si suma costurilor pentru urmatoarele dimensiuni:
  212. – denumirea canalului de distributie (channel_desc), luna de vanzare (calendar_month_desc) ;
  213. – filtrati liniile dupa urmatoarele conditii: canalul de distributie sa fie 'Direct Sales' si 'Internet';
  214. trimestrul de vanzare al treilea al anului 2000;
  215. - pentru valorile NULL generate de extensia GROUP BY afisati valorile: 'Toate canalele', 'Toate lunile'.
  216.  
  217.  
  218. SELECT ch.channel_desc,  t.calendar_month_desc,
  219. DECODE(GROUPING(ch.channel_desc),1,'Toate canalele',ch.channel_desc) AS Ch,
  220.   DECODE(GROUPING(t.calendar_month_desc),1,'Toate lunile',t.calendar_month_desc) AS Cal,
  221.   SUM(cs.unit_cost) AS Suma
  222. FROM channels ch, sales s, costs cs, times t
  223. WHERE (ch.channel_desc = 'Direct Sales' OR ch.channel_desc='Internet')
  224.   AND t.calendar_month_desc IN ('2000-10','2000-11','2000-12')
  225.   AND ch.channel_id = s.channel_id
  226.   AND s.time_id = t.time_id
  227.   AND s.prod_id = cs.prod_id
  228.   GROUP BY rollup( t.calendar_month_desc,ch.channel_desc);
  229.  
  230. 8)Afisati topul primelor 5 tari la vanzari pe ultimul trimestru (tara, total vanzari, pozitie).
  231.  SELECT c.country_name, SUM(s.amount_sold) AS Suma,
  232.       DENSE_RANK() over ( ORDER BY SUM(s.amount_sold) DESC) AS RANK
  233. FROM countries c, customers cus, sales s
  234. WHERE c.country_id = cus.country_id AND
  235.       cus.cust_id = s.cust_id
  236. GROUP BY c.country_name;
  237.  
  238. 9) Creati un VIEW materializat optim care sa stocheze costurile produselor pe luni calendaristice
  239. si pe canale de distributie.
  240.  
  241.  
  242.  
  243.  
  244.  
  245. =========================================================================================================
  246. 1) Afisati subtotalurile si suma costurilor pentru urmatoarele dimensiuni:
  247. – denumirea canalului de distributie (channel_desc), luna de vanzare (calendar_month_desc) ;
  248. – filtrati liniile dupa urmatoarele conditii: canalul de distributie sa fie 'Direct Sales' si 'Internet';
  249. trimestrul de vanzare al treilea al anului 2000;
  250. - pentru valorile NULL generate de extensia GROUP BY afisati valorile: 'Toate canalele', 'Toate lunile'.
  251.  
  252. SELECT c.channel_desc, t.calendar_month_desc,
  253. NVL(GROUPING(c.channel_desc), 'Toate canalele'),
  254. NVL(GROUPING(t.calendar_month_desc), 'Toate lunile'),
  255. SUM(cs.unit_cost)
  256. FROM channels c, times t, costs cs, sales s
  257. WHERE cs.time_id=t.time_id AND s.channel_id=c.channel_id AND s.prod_id=cs.prod_id
  258. AND (c.channel_desc='Direct Sales' OR c.channel_desc='Internet')
  259. AND t.calendar_month_desc IN ('2000-10', '2000-11', '2000-12')
  260. GROUP BY rollup( c.channel_desc, t.calendar_month_desc);
  261.  
  262. 2)Afisati topul primelor 5 tari la vanzari pe ultimul trimestru (tara, total vanzari, pozitie).
  263.  
  264. SELECT * FROM (
  265. SELECT c.country_name, SUM(s.anount_sold),
  266. DENSE_RANK() over (ORDER BY SUM(s.anount_sold)) pozitie
  267. FROM countries c, sales s, customers cs
  268. WHERE c.country_id=ch.country_id AND ch.cust_id=s.cust_id
  269. GROUP BY c.country_name;
  270. ) WHERE pozitie<=5;
  271.  
  272. 1) Afisati suma vanzarilor pe zile pe luna octombrie 2000. Adaugati doua coloane, una
  273. care sa afiseze suma vanzarilor pentru linia anterioara si una cu suma vanzarilor pentru linia
  274. urmatoare.
  275.  
  276. SELECT t.day_number_in_month, SUM(s.amount_sales),
  277. LAG(SUM(s.amount_sales),1) over (ORDER BY t.day_number_in_month) LAG,
  278. leag(SUM(s.amount_sales),1) over (ORDER BY t.day_number_in_month) LEAD
  279. FROM times t, sales s
  280. WHERE t.time_id=s.time_id AND t.calendar_month_number=11
  281. GROUP BY t.day_number_in_month;
  282.  
  283. 2) Folosind SQL sau PL/SQL, creati un tabel avand coloanele CUST§FIRST§NAME, CUST§LAST§NAME,
  284. CUST§CITY, CALENDAR§MONTH§NAME, QUANTITY§SOLD, AMOUNT§SOLD.
  285. Continutul tabelei este dat de extragerea cantitatii si
  286. valoarii vanzarilor,cumulate la nivel de luna, pentru clientii din Marea Britanie,
  287. pentru primele doua trimestre ale anului 2000.
  288.  
  289. CREATE TABLE tabela AS
  290. (
  291.   SELECT c.cust_first_name, c.cust_last_name, c.cust_city, t.caledar_month_desc,
  292.   SUM(SUM(s.quality_sold) over (PARTITION BY c.cust_last_name ORDER BY t.calendar_quarter_number rows unbound preceding)) cantitate,
  293.   SUM(SUM(s.amount_sold) over (PARTITION BY c.cust_last_name ORDER bt t.calendar_quarter_number roqs unbounded preceding)) vanzari
  294.   FROM customers c, times t, sales s, countries cc
  295.   WHERE c.cust_id=s.cust_id AND s.time_id=t.time_id AND c.country_id=cc.country_id
  296.   AND cc.country_name='United_Kingdom' AND (t.calendar_quarter_number=1 OR t.calendar_quarter_number=2)
  297.   AND t.calendar_year=2000
  298.   GROUP BY (c.cust_last_name,t.calendar_quarter_number,t.calendar_year);
  299. );
  300.  
  301. 1) Afisati suma vanzarilor (amount§sold) pentru clientii 2802 si 2807 grupat pe trimestrele
  302. anului 2000. De asemenea sa se afiseze si vanzarile cumulate pe trimestre de la inceputul
  303. anului 2000. Informatii de afisat: clientul (id), trimestrul, suma vanzarilor pe trimestrul curent,
  304. suma cumulata pe trimestrele anterioare. Ordonarea si gruparea se face dupa client si trimestru.
  305.  
  306. SELECT c.cust_id, t.calendar_quarter_number, SUM(s.amount_sales),
  307. SUM(SUM(s.amount_sales)) over (PARTITION BY t.calendar_quarter_number ORDER BY c.cust_id rows unbounded preceding)
  308. FROM sales s, customers c, times t
  309. WHERE s.cust_id=sales.cust_id AND s.time_id=t.time_id
  310. AND c.cust_id IN (2802, 2807) AND t.calendar_year=2000
  311. GROUP BY c.cust_id, t.calendar_quarter_number;
  312.  
  313. 2) Creati un VIEW materializat optim care sa stocheze costurile produselor pe luni calendaristice
  314. si pe canale de distributie.
  315.  
  316. CREATE TABLE cost_prod AS
  317.   SELECT SUM(c.unit_cost), t.calendar_month_desc, ch.channel_desc
  318.   FROM costs c, times t, channel ch, sales s
  319.   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
  320.   GROUP BY t.calendar_month_desc, ch.channel_desc;
  321.  
  322. CREATE materialized VIEW cost_prod AS
  323. ON prebuilt TABLE without reduced precision
  324. AS
  325.   SELECT SUM(c.unit_cost), t.calendar_month_desc, ch.channel_desc
  326.   FROM costs c, times t, channel ch, sales s
  327.   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
  328.   GROUP BY t.calendar_month_desc, ch.channel_desc;
  329.  
  330. 1) Afisati suma vanzarilor ("amount_sold") pentru clientii din Franta ("FR") grupat pe
  331. trimestrele ("calendar_quarter_desc") anului 2001. De asemenea sa se afiseze si vanzarile cumulate
  332. pe trimestre de la inceputul anului 2001. Informatii de afisat: clientul (cust_id), trimestrul,
  333. suma vanzarilor pe trimestrul curent, suma cumulata pe trimestrele anterioare. Ordonarea si gruparea
  334. se face dupa client si trimestru.
  335.  
  336. SELECT c.cust_id, t.calendar_quarter_number, SUM(s.smount_sold),
  337. SUM(SUM(s.amount_sold) over (PARTITION BY t.calendar_quarter_number ORDER BY c.cust_id rowe unbounded preceding))
  338. FROM customers cust, countries co, sales s, times t
  339. WHERE cust.cust_id = s.cust_id AND s.time_id = t.time_id AND cust.country_id = co.country_id
  340. AND t.calendar_year = '2001'
  341. AND co.country_iso_code = 'FR'
  342. GROUP BY cust.cust_id, t.calendar_quarter_desc;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement