Ck7dDdqr

1-- LAB 1 ---------------------------------------------------------------------------------------------------------------------------
2------------------------------------------------------------------------------------------------------------------------------------
3
4/* Extensii SQL pentru procesari analitice in Oracle */
5
6/* EXEMPLE */
7-- FUNCTIILE RANK() SI DENSE_RANK()
8-- clasamentul angajatilor dupa salarii
9SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) position
10FROM emp;
11
12SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) position
13FROM emp;
14
15-- clasificare pe partitii
16-- afisarea angajatilor pe departamente
17SELECT deptno, ename, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) posdept
18FROM emp;
19
20-- clasificare dupa expresii multiple
21-- clasamentul salariatilor dupa salariu si dupa data angajarii
22SELECT deptno, ename, sal, hiredate, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC, hiredate ASC) posdept
23FROM emp;
24
25-- clasificare dupa partitii multiple
26-- clasamentul angajatilor dupa salarii, clasament grupat dupa departament si dupa job
27SELECT deptno, job, ename, sal,
28    RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) posdept,
29    RANK() OVER (PARTITION BY job ORDER BY sal DESC) posjob
30FROM emp;
31
32-- FUNCTIA CUME_DIST()
33-- distributia salariilor angajatilor
34SELECT ename, sal,
35    ROUND(CUME_DIST() OVER (ORDER BY sal DESC), 2) cume_dist_sal
36FROM emp;
37
38-- distributia salariilor pe departamente
39SELECT deptno, ename, sal,
40    ROUND(CUME_DIST() OVER (PARTITION BY deptno ORDER BY sal DESC), 2) cume_dist_sal_dept
41FROM emp;
42
43-- FUNCTIA PERCENT_RANK()
44-- distributiile pozitionale ale salariilor pe departamente
45SELECT deptno, ename, sal,
46    ROUND(PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC), 2) percent_rank_sal_dept
47FROM emp;
48
49-- FUNCTIILE NTILE() SI ROW_NUMBER()
50-- salariile descrescator, impartite in 4 categorii
51SELECT deptno, ename, sal,
52    NTILE(4) OVER (ORDER BY sal DESC) ntile4
53FROM emp;
54
55-- salariile descrescator, partitionate dupa departament si impartite in cate 2 buchete
56SELECT deptno, ename, sal,
57    NTILE(2) OVER (PARTITION BY deptno ORDER BY sal DESC) ntile2_deptno
58FROM emp;
59
60-- salariile angajatilor partitionate dupa departamente, impreuna cu o coloana de numerotare
61-- care se reseteaza dupa fiecare partitie
62SELECT deptno, ename, sal,
63    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) nrcrt
64FROM emp;
65
66/* EXERCITII */
67-- 1
68-- Afisati clasamentul angajatilor pe departamente dupa data angajarii crescator. Afisati
69-- informatiile: numele departamentului, numele angajatului, data angajarii si pozitia angajatului.
70
71SELECT d.dname, e.ename, e.hiredate, e.job,
72    RANK() OVER (ORDER BY e.hiredate ASC) clasament -- pozitia se afla ordonand dupa data angajarii si calculand "RANK()"-ul
73FROM emp e JOIN dept d ON e.deptno = d.deptno;
74
75-- 2
76-- Afisati clasamentul salariilor medii (rotunjit la 2 zecimale) in ordine descrescatoare, pe
77-- departamente. Adaugati si liniile totalizatoare pentru departament si job. Afisati urmatoarele
78-- informatii: numarul departamentului sau „DeptAvgSal”, denumirea job-ului sau „JobAvgSal”,
79-- salarul mediu si pozitia in cadrul departamentului.
80
81SELECT deptno AS "DeptAvgSal", job AS "JobAvgSal", 
82    ROUND(AVG(sal), 2) AS "AvgSal", 
83    RANK() OVER (PARTITION BY deptno ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_dept -- pozitia in cadrul departamentului
84    -- folosim "PARTITION BY deptno" pentru a face clasamentul mediilor salariale pe departamente, si nu global
85    -- adica "pozitie_dept" este relativa la numarul departamentului = campul DeptAvgSal
86FROM emp
87GROUP BY ROLLUP(deptno, job); -- linii totalizatoare pentru departament si job
88
89-- 3
90-- La interogarea anterioara adaugati si pozitia in cadrul job-ului.
91
92SELECT deptno AS "DeptAvgSal", job AS "JobAvgSal", 
93    ROUND(AVG(sal), 2) AS "AvgSal", 
94    RANK() OVER (PARTITION BY deptno ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_dept,
95    RANK() OVER (PARTITION BY job ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_job -- pozitia in cadrul job-ului
96    -- partitionam dupa job, iar pentru fiecare "job" fixat, se ordoneaza mediile salariale si se calculeaza pozitia in ierarhie
97FROM emp
98GROUP BY ROLLUP(deptno, job);
99
100-- 4
101-- Afisati angajatii cu primele 10 niveluri de venituri (pot sa existe mai mult de 10 angajati).
102-- Venit = salar + comision. Afisati urmatoarele informatii: pozitia angajatului (fara
103-- discontinuitati) in clasamentul veniturilor, numele, salarul, comisionul si venitul.
104
105SELECT *
106FROM (SELECT DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit,
107        -- ordonam dupa venit (salariu + comision) si calculam DENSE_RANK(), si nu RANK() 
108        -- pentru ca vrem ca duplicatele sa fie tratate ca aflandu-se pe acelasi nivel ierarhic
109        -- (SCOTT si FORD sunt pe acelasi loc, nu ocupa 2 locuri diferite in ierarhie)
110        ename, sal, comm, sal + NVL(comm, 0) * sal AS "Venit"
111        FROM emp)
112-- dorim primele 10 inregistrari: folosim o subinterogare, din care preluam tot, cu conditia ca pozitia calculata sa fie <= 10
113-- nu avem cum folosi conditia din WHERE-ul exterior in SELECT-ul din interior, deoarece alias-ul "pozitie_venit" nu este cunoscut decat in exterior!
114WHERE pozitie_venit <= 10;
115
116-- 5
117-- La exercitiul anterior afisati si distributia veniturilor (rotunjita la 2 zecimale).
118
119SELECT *
120FROM (SELECT DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit,
121        ROUND(PERCENT_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC), 2) distributie_venit,
122        -- distributia veniturilor = functia PERCENT_RANK() in loc de RANK() / DENSE_RANK()
123        ename, sal, comm, sal + NVL(comm, 0) * sal AS "Venit"
124        FROM emp)
125WHERE pozitie_venit <= 10;
126
127-- 6
128-- Afisati informatiile despre angajati impartiti in 4 buchete. Afisati urmatoarele informatii:
129-- numarul current, pozitia nivelului de venit, numarul buchetului, numele angajatului, salarul,
130-- comisionul si venitul (salar + commision).
131
132SELECT NTILE(4) OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) nr_crt, -- numarul buchetului
133    -- impartim in 4 buchete folosind functia NTILE()
134    DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit, -- pozitia nivelului de venit
135    ename, sal, sal + NVL(comm, 0) * sal AS "Venit"
136FROM emp;
137
138-- 7
139-- Afisati clasamentul vanzarilor canalelor de distributie pentru lunile '2000-09' si '2000-10' si
140-- tara US. Informatii: numele canalului, valoarea vanzarilor si pozitia in clasament.
141
142SELECT ch.channel_desc, SUM(s.amount_sold) AS "Vanzari canal de distributie",
143    RANK() OVER(ORDER BY SUM(s.amount_sold) DESC) pozitie
144FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
145    -- JOIN-uri pentru a afla informatiile suplimentare necesare la filtrare si afisare
146    JOIN times t ON (s.time_id = t.time_id)
147    JOIN customers cust ON (s.cust_id = cust.cust_id)
148WHERE t.calendar_month_desc IN ('2000-09', '2000-10') -- filtrarile din enunt
149    AND cust.country_id = 'US'
150GROUP BY ch.channel_desc; -- vanzarile trebuie grupate pe canale de distributie
151
152-- 8
153-- Afisati clasamentul vanzarilor pe canale de distributie, pentru lunile '2000-08', '2000-09',
154-- '2000-10', '2000-11’. Informatii: channel_desc, calendar_month_desc, total vanzari, pozitia in
155-- cadrul canalului de distributie.
156
157SELECT ch.channel_desc, t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
158    RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_canal_distributie
159    -- partitionam dupa canalul de distributie, apoi pentru fiecare canal fixat, ordonam dupa valoarea vanzarilor ca sa aflam pozitia relativa
160FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
161    JOIN times t ON (s.time_id = t.time_id)
162WHERE t.calendar_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
163GROUP BY ch.channel_desc, t.calendar_month_desc;
164
165-- 9
166-- La interogarea anterioara adaugati si pozitia in cadrul lunii.
167
168SELECT ch.channel_desc, t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
169    RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_canal_distributie,
170    RANK() OVER(PARTITION BY t.calendar_month_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_luna
171    -- pozitia in cadrul lunii = partitionam dupa luna si ordonam partitiile rezultate dupa valoarea vanzarilor, pentru a afla pozitia relativa din luna respectiva
172FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
173    JOIN times t ON (s.time_id = t.time_id)
174WHERE t.calendar_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
175GROUP BY ch.channel_desc, t.calendar_month_desc;
176
177-- 10
178-- Afisati totalul vanzarilor pe canalul de distributie si pe tara impreuna cu pozitionarea in
179-- cadrul acestui grup (channel_desc, country_id), precum si liniile de subtotaluri generate pentru
180-- combinarea celor doua dimensiuni. Se filtreaza liniile pentru canalele: 'Direct Sales', 'Internet',
181-- luna: '2000-09' si tarile: 'UK', 'US', 'JP'. Informatii: channel_desc, country_id, total vanzari,
182-- pozitie in grup.
183
184SELECT ch.channel_desc, cust.country_id, SUM(s.amount_sold) AS "Total vanzari",
185    RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_grup
186    -- pozitionare in cadrul canalului de distributie = facem partitionare dupa canalul de distributie
187    -- si pentru fiecare inregistrare pentru un canal fixat se calculeaza rangul in acea partitie
188FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
189    JOIN times t ON (s.time_id = t.time_id)
190    JOIN customers cust ON (s.cust_id = cust.cust_id)
191WHERE ch.channel_desc IN ('Direct Sales', 'Internet') -- filtrarile cerute
192    AND t.calendar_month_desc = '2000-09'
193    AND cust.country_id IN ('UK', 'US', 'JP')
194GROUP BY CUBE(ch.channel_desc, cust.country_id);
195    -- totalul vanzarilor pe canalul de distributie si pe tara + linii de subtotaluri pentru combinarea celor 2 dimensiuni
196    -- CUBE() in loc de ROLLUP() pentru ca trebuie toate combinatiile intre dimensiuni
197
198-- 11
199-- Afisati topul primelor 5 tari la vanzari pe luna ‘2000-09’ (tara, total vanzari, pozitie).
200
201SELECT *
202FROM (
203    SELECT cust.country_id, SUM(s.amount_sold) AS "Total vanzari",
204        RANK() OVER(ORDER BY SUM(s.amount_sold) DESC) pozitie
205        -- top vanzari = ordonare descrescatoare in functie de suma valorilor vanzarilor
206        -- apoi calcul de rang pe aceasta ordonare
207    FROM sales s JOIN times t ON (s.time_id = t.time_id)
208        JOIN customers cust ON (s.cust_id = cust.cust_id)
209    WHERE t.calendar_month_desc = '2000-09'
210    GROUP BY cust.country_id -- trebuie grupate rezultatele pentru ca folosim functia agregat SUM(),
211        -- iar afisarea trebuie facuta in functie de tara
212    -- din interogarea interioara rezulta o lista cu toate tarile ordonate dupa vanzari
213    -- nu putem afisa doar primele 5 inregistrari in acest SELECT, deoarece alias-ul "pozitie" nu este vizibil in acelasi
214    -- SELECT in care este creat
215)
216-- se face un SELECT exterior din tabelul rezultat, din care selectam toate coloanele si filtram dupa coloana alias "pozitie",
217-- care devine vizibila de aceasta data
218WHERE pozitie <= 5;
219
220-- 12
221-- Afisati suma vanzarilor pe anul 1999, pentru categoria de produse ‘Men’, impartite in 4
222-- buchete dupa suma vanzarilor (luna, vanzarile, numar buchet).
223
224SELECT t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
225    NTILE(4) OVER (ORDER BY SUM(s.amount_sold) DESC) pozitie_buchet
226    -- impartire in 4 buchete = functia NTILE(), dupa suma vanzarilor = ordonare dupa SUM(...)
227FROM sales s JOIN times t ON (s.time_id = t.time_id)
228    JOIN products p ON (s.prod_id = p.prod_id)
229WHERE t.calendar_year = '1999' -- filtrarile cerute
230    AND p.prod_category = 'Men'
231GROUP BY t.calendar_month_desc; -- se grupeaza din acelasi motiv ca la 11, folosim o functie agregat SUM()
232
233
234-- LAB 2 ---------------------------------------------------------------------------------------------------------------------------
235------------------------------------------------------------------------------------------------------------------------------------
236
237/* Extensii SQL pentru agregari in Oracle */
238
239/* EXEMPLE */
240-- extensia ROLLUP
241
242-- Afisati numele departamentului, numele meseriei, salarul minim, salarul maxim si media
243-- salarului pentru meserie, respectiv departament. Sa se afiseze si valorile agregate la nivel de
244-- meserie, la nivel de department si la nivel general. Ordonati liniile dupa numele departamentului
245-- si apoi dupa numele meseriei.
246SELECT dname, job, AVG(sal), MIN(sal), MAX(sal)
247FROM emp e, dept d
248WHERE e.deptno = d.deptno
249GROUP BY ROLLUP(dname, job)
250ORDER BY dname, job;
251
252-- doar cu clauza GROUP BY
253SELECT dname, job, AVG(sal), MIN(sal), MAX(sal)
254FROM emp e, dept d
255WHERE e.deptno = d.deptno
256GROUP BY dname, job
257ORDER BY dname, job;
258
259-- ROLLUP partial
260SELECT loc, dname, job, AVG(sal), MIN(sal), MAX(sal)
261FROM emp e, dept d
262WHERE e.deptno = d.deptno
263GROUP BY loc, ROLLUP(dname, job);
264
265-- extensia CUBE
266
267-- Afisati numele departamentului, numele meseriei, precum si toate valorile pentru salarul
268-- minim, salarul maxim si media salarului pentru meserie, respectiv departament. Sa se afiseze si
269-- valorile agregate la nivel de meserie, la nivel de department, la nivel de meserie si departament,
270-- precum si la nivel general.
271SELECT dname, job, AVG(sal), MIN(sal), MAX(sal)
272FROM emp e, dept d
273WHERE e.deptno = d.deptno
274GROUP BY CUBE(dname, job);
275
276-- CUBE partial = ROLLUP() pentru toate combinatiile de dimensiuni
277SELECT loc, dname, job, AVG(sal), MIN(sal), MAX(sal)
278FROM emp e, dept d
279WHERE e.deptno = d.deptno
280GROUP BY loc, CUBE(dname, job);
281
282-- GROUPING -> pentru a afla daca valorile de NULL provin din ROLLUP / CUBE sau din tabela initiala
283SELECT dname, job, ROUND(AVG(sal), 2), MIN(sal), MAX(sal),
284    GROUPING(dname) AS gdept, GROUPING(job) AS gjob
285FROM emp e, dept d
286WHERE e.deptno = d.deptno
287GROUP BY ROLLUP(dname, job);
288
289SELECT dname, job, AVG(sal), MIN(sal), MAX(sal),
290    GROUPING(dname) AS gdept, GROUPING(job) AS gjob
291FROM emp e, dept d
292WHERE e.deptno = d.deptno
293GROUP BY CUBE(dname, job)
294HAVING GROUPING(dname) = 1 OR GROUPING(job) = 1;
295
296-- GROUPING_ID -> pentru a afla nivelul de agregare
297SELECT dname, job, AVG(sal), MIN(sal), MAX(sal),
298    GROUPING(dname) AS gdept, GROUPING(job) AS gjob,
299    GROUPING_ID(dname, job) AS nivel
300FROM emp e, dept d
301WHERE e.deptno = d.deptno
302GROUP BY CUBE(dname, job)
303ORDER BY nivel;
304
305-- GROUP_ID -> pentru a afla duplicatele
306SELECT dname, job, AVG(sal), 2,
307    GROUPING(dname) AS gdept, GROUPING(job) AS gjob,
308    GROUPING_ID(dname, job) AS nivel, GROUP_ID()
309FROM emp e, dept d
310WHERE e.deptno = d.deptno
311GROUP BY dname, ROLLUP(dname, job)
312ORDER BY nivel;
313
314-- GROUPING SETS
315-- Calculati salarul mediu pentru doua grupuri de date: department / job si job/manager
316SELECT dname, job, mgr, avg(sal)
317FROM emp e, dept d
318WHERE e.deptno = d.deptno
319GROUP BY GROUPING SETS((dname, job), (job, mgr));
320
321/* EXERCITII */
322-- 1
323-- Afisati salarul mediu, minim, maxim precum si suma salariilor grupate pe job si gradul de
324-- salarizare. Sa se calculeze subtotaluri la nivel de job, grad si total general.
325
326SELECT job, grade, ROUND(AVG(sal), 2), MIN(sal), MAX(sal), SUM(sal)
327FROM emp JOIN salgrade
328ON emp.sal BETWEEN losal AND hisal -- gradul de salarizare = non-equijoin intre tabelele "emp" si "salgrade"
329GROUP BY ROLLUP(job, grade); -- calculul de subtotaluri -> ROLLUP()
330
331-- 2
332-- Adaugati la interogarea anterioara subtotalurile pentru toate combinatiile intre cele doua
333-- dimensiuni: job si gradul de salarizare.
334
335SELECT job, grade, ROUND(AVG(sal), 2), MIN(sal), MAX(sal), SUM(sal)
336FROM emp JOIN salgrade
337ON emp.sal BETWEEN losal AND hisal
338GROUP BY CUBE(job, grade); -- subtotaluri intre toate combinatiile de cele 2 dimensiuni = in loc de ROLLUP() folosim CUBE()
339
340-- 3
341-- Scrieti o interogare care sa afiseze urmatoarele informatii:
342-- - numele managerului, job-ul angajatului, suma salariilor, media salariilor, salarul minim si
343-- maxim, grupate pe manager si job;
344-- - subtotaluri la nivelul managerului si a jobului, a managerului si totalul general
345
346SELECT e2.ename AS manager, e1.job, SUM(e1.sal), AVG(e1.sal), MIN(e1.sal), MAX(e1.sal)
347FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno -- pentru a afla managerul, facem self-join cu tabela "emp"
348GROUP BY ROLLUP(e2.ename, e1.job); -- e2.ename = manager
349
350-- 4
351-- La interogarea anterioara adaugati functii de grupare care sa identifice care din valorile
352-- NULL afisate sunt generate de extensia clauzei GROUP BY si care sunt din tabele. Pe
353-- coloanele ename si job, pentru valorile NULL generate de extensia clauzei GROUP BY afisati
354-- urmatoarele valori: Total ename, Total job.
355-- *pentru a inlocui valorile de NULL folosim functia NVL()
356
357SELECT NVL(e2.ename, 'Total ename') AS manager, NVL(e1.job, 'Total job') AS job,
358    SUM(e1.sal), AVG(e1.sal), MIN(e1.sal), MAX(e1.sal)
359FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno
360GROUP BY ROLLUP(e2.ename, e1.job);
361
362-- 5
363-- Folosind optiunea GROUPING SETS afisati urmatoarele grupuri:
364-- - nume departament si job
365-- - nume manager si grad salar
366-- Interogarea trebuie sa calculeze suma salariilor. Afisati si nivelul subtotalurilor calculate, iar
367-- valorile NULL generate le afisati cu „Total”. Excludeti eventualele linii duplicate. Ordonati
368-- dupa nivelul subtotalurilor.
369
370SELECT NVL(d.dname, 'Total') AS dname, NVL(e1.job, 'Total') AS job, 
371    NVL(e2.ename, 'Total') AS manager, NVL(TO_CHAR(s.grade), 'Total') AS grade, 
372    SUM(e1.sal), 
373    GROUPING_ID(d.dname, e1.job, e2.ename, s.grade) AS nivel_agregare -- functia GROUPING_ID() ne da nivelul de agregare
374FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno -- SELF JOIN intre emp si emp (pentru a afla manager-ul)
375    JOIN dept d ON e1.deptno = d.deptno -- EQUIJOIN intre emp si dept
376    JOIN salgrade s ON e1.sal BETWEEN losal AND hisal -- aflarea gradului de salarizare: NON-EQUIJOIN intre emp si salgrade
377GROUP BY GROUPING SETS(ROLLUP(d.dname, e1.job), ROLLUP(e2.ename, s.grade))
378HAVING GROUP_ID() = 0 -- evitarea duplicatelor -> eliminam inregistrarile cu GROUP_ID() nenul
379ORDER BY GROUPING_ID(d.dname, e1.job, e2.ename, s.grade) ASC;
380
381-- 6
382-- Afisati subtotalurile si suma vanzarilor pentru urmatoarele dimensiuni:
383-- – denumirea canalului de distributie (channel_desc), luna de vanzare (calendar_month_desc) si
384-- prescurtarea tarii (country_id);
385-- – filtrati liniile dupa urmatoarele conditii: canalul de distributie sa fie 'Direct Sales' si 'Internet'; luna
386-- de vanzare sa fie '2000-09' si '2000-10', tara de desfacere sa fie 'UK' si 'US'.
387
388SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
389FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
390    JOIN times t ON (s.time_id = t.time_id)
391    JOIN customers cust ON (s.cust_id = cust.cust_id) -- JOIN cu tabela "customers" ca sa putem afla "country_id"
392    JOIN countries c ON (cust.country_id = c.country_id)
393    -- sales = tabela de fapte, restul sunt tabele de dimensiune
394WHERE ch.channel_desc IN ('Direct Sales', 'Internet') -- aplicam filtrarile cerute in enunt
395    AND t.calendar_month_desc IN ('2000-09', '2000-10')
396    AND cust.country_id IN ('UK', 'US')
397GROUP BY ROLLUP(ch.channel_desc, t.calendar_month_desc, cust.country_id); -- subtotalurile pe coloanele cerute
398
399-- 7
400-- Pentru interogarea anterioara afisati gruparea partiala dupa canalul de distributie.
401
402SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
403FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
404    JOIN times t ON (s.time_id = t.time_id)
405    JOIN customers cust ON (s.cust_id = cust.cust_id)
406    JOIN countries c ON (cust.country_id = c.country_id)
407WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
408    AND t.calendar_month_desc IN ('2000-09', '2000-10')
409    AND cust.country_id IN ('UK', 'US')
410GROUP BY ch.channel_desc, ROLLUP(t.calendar_month_desc, cust.country_id);
411-- singura modificare este in GROUP BY, unde scoatem campul "channel_desc" din ROLLUP(), pentru a obtine gruparea partiala
412
413-- 8
414-- Afisati subtotalurile pentru fiecare combinatie de dimensiuni de la pb 6. Refaceti interogarea si
415-- afisati doar dimensiunile pentru canalul de distributie si tara. Pentru valorile NULL generate de
416-- extensia GROUP BY afisati valorile: 'All countries', respectiv „All channels”.
417
418SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
419FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
420    JOIN times t ON (s.time_id = t.time_id)
421    JOIN customers cust ON (s.cust_id = cust.cust_id)
422    JOIN countries c ON (cust.country_id = c.country_id)
423WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
424    AND t.calendar_month_desc IN ('2000-09', '2000-10')
425    AND cust.country_id IN ('UK', 'US')
426GROUP BY CUBE(ch.channel_desc, t.calendar_month_desc, cust.country_id);
427-- modificare fata de problema 6: folosim CUBE() in loc de ROLLUP(), pentru ca vrem subtotaluri pentru toate combinatiile de dimensiuni
428
429SELECT NVL(ch.channel_desc, 'All channels'), NVL(cust.country_id, 'All countries'), SUM(s.amount_sold)
430-- valorile NULL se trateaza cu NVL()
431FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
432    JOIN times t ON (s.time_id = t.time_id)
433    JOIN customers cust ON (s.cust_id = cust.cust_id)
434    JOIN countries c ON (cust.country_id = c.country_id)
435WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
436    AND t.calendar_month_desc IN ('2000-09', '2000-10')
437    AND cust.country_id IN ('UK', 'US')
438GROUP BY CUBE(ch.channel_desc, cust.country_id);
439-- nu mai luam in considerare luna in calculul subtotalurilor (stergem si din CUBE() si din SELECT)
440
441-- 9
442-- La problema 8 afisati doar totalurile pe tara, respectiv pe canal, precum si totalul general.
443
444SELECT NVL(ch.channel_desc, 'All channels'), NVL(cust.country_id, 'All countries'), SUM(s.amount_sold)
445FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
446    JOIN times t ON (s.time_id = t.time_id)
447    JOIN customers cust ON (s.cust_id = cust.cust_id)
448    JOIN countries c ON (cust.country_id = c.country_id)
449WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
450    AND t.calendar_month_desc IN ('2000-09', '2000-10')
451    AND cust.country_id IN ('UK', 'US')
452GROUP BY ROLLUP(cust.country_id, ch.channel_desc); -- doar totalurile pe tara, respectiv pe canal (ROLLUP() + campuri inversate fata de problema 8)
453
454-- 10
455-- Pentru problema 6 afisati totalul vanzarilor pentru urmatoarele seturi de grupuri de date
456-- ('GROUPING SETS'):
457-- - channel_desc, calendar_month_desc, country_id
458-- - channel_desc, country_id
459-- - calendar_month_desc, country_id)
460-- Conditiile de filtrare si informatiile de afisat raman aceleasi.
461
462SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
463FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
464    JOIN times t ON (s.time_id = t.time_id)
465    JOIN customers cust ON (s.cust_id = cust.cust_id)
466    JOIN countries c ON (cust.country_id = c.country_id)
467WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
468    AND t.calendar_month_desc IN ('2000-09', '2000-10')
469    AND cust.country_id IN ('UK', 'US')
470    -- in loc de ROLLUP() folosim GROUPING SETS pentru subtotaluri pe liste de grupuri specificate
471GROUP BY GROUPING SETS((ch.channel_desc, t.calendar_month_desc, cust.country_id),
472            (ch.channel_desc, cust.country_id),
473            (t.calendar_month_desc, cust.country_id));
474
475
476-- LAB 3 ---------------------------------------------------------------------------------------------------------------------------
477------------------------------------------------------------------------------------------------------------------------------------
478
479/* Extensii SQL pentru procesari analitice in Oracle – ferestre de calcul */
480
481/* EXEMPLE */
482-- Exemplu cu totaluri cumulative intr-o fereastra cu offset logic
483-- Afisati salariul angajatilor precum si suma salariilor de la primul salariu pana la salariul curent,
484-- ordonati crescator dupa salariu.
485SELECT ename, sal,
486    SUM(sal) OVER (ORDER BY sal RANGE UNBOUNDED PRECEDING) AS SalCumul_logic
487FROM emp;
488-- fereastra de calcul este: [salariu_angajat_1, salariu_angajat_2, ...,  salariu_angajat_curent]
489
490-- Exemplu cu functii analitce de mutare a liniilor intr-o fereastra cu offset logic
491-- Afisati salariul angajatilor precum si media salariului angajatului curent si a celor precedenti
492-- care au cu cel mult 200 salariul mai mic, ordonati crescator dupa salariu.
493SELECT ename, sal,
494    ROUND(AVG(sal) OVER (ORDER BY sal RANGE 200 PRECEDING), 2) AS avgsal_range
495FROM emp;
496-- fereastra de calcul este: [sal-200, sal]
497
498-- Exemplu de calcul pentru fereastra centrata de date
499-- Afisati suma salariilor pentru o fereastra de plus minus 100 la salariu.
500SELECT ename, hiredate, sal,
501    SUM(sal) OVER (ORDER BY sal RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) sumsal_center
502FROM emp;
503-- fereastra de calcul este: [sal-100, sal+100]
504
505-- Fereastra de date cu limita mobila (dinamica)
506-- Afisati angajatii, salariile si suma salariilor pentru un interval variabil definit de un offset logic
507-- dat de o functie utilizator fn
508
509-- Functia fn intoarce numarul de angajati pentru un departament
510CREATE OR REPLACE FUNCTION fn(dno NUMBER) RETURN NUMBER
511IS
512    Result NUMBER;
513    res NUMBER;
514BEGIN
515    SELECT COUNT(*) - 1
516    INTO res
517    FROM emp
518    WHERE deptno = dno;
519    
520    Result := res;
521    RETURN(Result);
522    
523    EXCEPTION
524        WHEN OTHERS THEN
525            Result := 0;
526END fn;
527
528SELECT deptno, ename, sal, fn(deptno),
529    SUM(sal) OVER (ORDER BY deptno RANGE fn(deptno) PRECEDING) sumsal_dept
530FROM emp;
531
532-- offset fizic = inregistrari / offset logic = deplasare pe functia cumulativa
533-- adica daca sunt mai multi angajati cu acelasi salariu, offset-ul logic considera offset 1,
534-- iar cel fizic le ia pe toate in considerare ca si inregistrari separate
535-- Exemplu cu totaluri cumulative intr-o fereastra cu offset fizic
536-- Afisati salariul angajatilor precum si suma salariilor de la primul salariu pana la salariul curent,
537-- ordonati crescator dupa salariu.
538SELECT ename, sal,
539    SUM(sal) OVER (ORDER BY sal ROWS UNBOUNDED PRECEDING) AS SalCumul_fizic
540FROM emp;
541
542-- Exemplu fereastra centrata pentru offset fizic
543-- Afisati angajatii si suma salariilor pentru intervalul fizic de plus minus 3 angajati, ordonati
544-- dupa salarii.
545SELECT ename, sal,
546    SUM(sal) OVER (ORDER BY sal ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) sum3_fizic
547FROM emp;
548
549-- Fereastra de date cu limita mobila (dinamica) si ofs.amount_sold, fset fizic
550-- Afisati angajatii, salariile si suma salariilor pentru un interval variabil, definit de un offset fizic
551-- dat de o functie utilizator fn.
552SELECT deptno, ename, sal, fn(deptno),
553    SUM(sal) OVER (ORDER BY deptno ROWS fn(deptno) PRECEDING) sumsal_dept_fizic
554FROM emp;
555-- Fereastra este definita de la un punct de start pana la linia curenta. Punctul de start este
556-- variabil si este dat de numarul de linii fizice precedente calculate de functia utilizator fn.
557
558-- Exemplu de folosire a functiilor pentru aflarea valorii minime si maxime intr-o fereastra
559-- de calcul
560-- Afisati angajatii, salariile, salariul minim si salariul maxim pentru o fereastra de calcul cu offset
561-- fizic de plus minus 3 angajati, ordonati dupa salariu.
562SELECT ename, sal,
563    FIRST_VALUE(sal) OVER (ORDER BY sal ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) firstval,
564    LAST_VALUE(sal) OVER (ORDER BY SAL ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) lastval
565FROM emp;
566
567-- Exemplu de folosire a functiilor LAG/LEAD - pentru accesul la inregistrari din vecinatate
568-- Afisati angajatii, salariile, precum si salariile precedentilor 2 si urmatorilor 2 angajati.
569SELECT ename, sal, 
570    LAG(sal, 2) OVER (ORDER BY sal) lagval,
571    LEAD(sal, 2) OVER (ORDER BY sal) leadval
572FROM emp;
573
574-- Exemplu de utilizare CASE
575-- Afisati numele, salariul si gradul salariatului, unde gradul se calculeaza astfel: 1 pentru salariu <
576-- 1000, 2 pentru [1000,2000), 3 pentru [2000,3000) si 4 pentru salariu mai mare de 4000. Ordonati
577-- dupa grad si salariu.
578SELECT ename, sal,
579    (CASE WHEN e.sal BETWEEN 0 AND 1000 THEN 1
580        WHEN e.sal BETWEEN 1000 AND 2000 THEN 2
581        WHEN e.sal BETWEEN 2000 AND 3000 THEN 3
582        ELSE 4
583    END) grad
584FROM emp e
585ORDER BY grad, sal;
586
587/* EXERCITII */
588-- 1
589-- Afisati angajatii si suma salariilor pentru o fereastra cu offset logic, centrata pe data angajarii
590-- cu limitele de plus minus o luna. Afisati salarul minim si maxim pentru aceasi fereastra dar cu
591-- limita de plus minus 6 luni.
592
593SELECT ename, hiredate, sal,
594    SUM(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH FOLLOWING) AS sumsal_1_month,
595    -- pentru ca intervalul e lunar si nu numeric, se foloseste "RANGE BETWEEN INTERVAL x DAY / MONTH / YEAR
596    -- offset logic = RANGE
597    FIRST_VALUE(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND INTERVAL '6' MONTH FOLLOWING) AS maxsal,
598    -- valoarea maxima din setul de inregistrari din fereastra = FIRST_VALUE()
599    LAST_VALUE(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND INTERVAL '6' MONTH FOLLOWING) AS minsal
600    -- valoarea minima din setul de inregistrari din fereastra = LAST_VALUE()
601FROM emp;
602
603-- 2
604-- Afisati numarul departamentului, numele angajatului, salarul si suma salariilor cumulative
605-- pana la angajatul curent pe departamente. Ordonarea se face dupa salar. Se considera fereastra
606-- cu offset fizic si apoi cu offset logic. Comparati rezultatele.
607
608SELECT deptno, ename, sal,
609    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS UNBOUNDED PRECEDING) AS sumsal_dept -- cu offset fizic = ROWS
610    -- pana la angajatul curent = de la inceput (UNBOUNDED PRECEDING), pana la inregistrarea curent, implicit
611FROM emp;
612
613SELECT deptno, ename, sal,
614    SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE UNBOUNDED PRECEDING) AS sumsal_dept -- cu offset logic = RANGE
615FROM emp;
616
617-- 3
618-- La interogarea anterioara adaugati comisionul si venitul total (salar + comision) si afisati
619-- suma cumulativa a veniturilor. Inlocuiti partitionarea pe departament cu cea pe job. Rulati
620-- interogarea pentru ferestre cu offset fizic si logic.
621
622SELECT job, ename, sal, NVL(comm, 0) AS comision, sal + NVL(comm, 0) * sal AS venit_total,
623    SUM(sal + NVL(comm, 0) * sal) OVER(PARTITION BY job ORDER BY sal ROWS UNBOUNDED PRECEDING) AS sumsal_job -- cu offset fizic = ROWS
624FROM emp;
625
626SELECT job, ename, sal, NVL(comm, 0) AS comision, sal + NVL(comm, 0) * sal AS venit_total,
627    SUM(sal + NVL(comm, 0) * sal) OVER(PARTITION BY job ORDER BY sal RANGE UNBOUNDED PRECEDING) AS sumsal_job -- cu offset logic = RANGE
628FROM emp;
629
630-- 4
631-- Afisati numarul departamentului, numele, salarul, valoarea functiei fn si suma salariilor
632-- pentru fereastra mobila intre linia curenta si urmatoarele x linii intoarse de fn. Ordonarea se
633-- face dupa departament si salar. Schimbati functia fn cu functia fnjob care intoarce numarul de
634-- angajati pe job minus 1. Schimbati si interogarea astfel incat situatia sa fie pe job in loc de
635-- departament.
636
637CREATE OR REPLACE FUNCTION fn(dno NUMBER) RETURN NUMBER -- functia fn() din laborator
638IS
639    Result NUMBER;
640    res NUMBER;
641BEGIN
642    SELECT COUNT(*) - 1
643    INTO res
644    FROM emp
645    WHERE deptno = dno;
646    
647    Result := res;
648    RETURN(Result);
649    
650    EXCEPTION
651        WHEN OTHERS THEN
652            Result := 0;
653END fn;
654
655SELECT deptno, ename, sal, fn(deptno),
656    SUM(sal) OVER (ORDER BY deptno, sal ROWS BETWEEN CURRENT ROW AND fn(deptno) FOLLOWING) AS sumsal_dept
657    -- ROWS = offset fizic, nu folosim offset logic pentru ca in enunt se specifica "linia curenta" si nu "valoarea curenta"
658    -- CURRENT ROW = linia curenta
659    -- fn(deptno) FOLLOWING = urmatoarele x linii, x = valoarea intoarsa de fn()
660FROM emp;
661
662-- schimbam situatia pentru partitionare pe job, modificam functia fn()
663CREATE OR REPLACE FUNCTION fnjob(v_job emp.job%TYPE) RETURN NUMBER
664IS
665    Result NUMBER;
666    res NUMBER;
667BEGIN
668    SELECT COUNT(*) - 1
669    INTO res
670    FROM emp
671    WHERE job = v_job;
672    
673    Result := res;
674    RETURN(Result);
675    
676    EXCEPTION
677        WHEN OTHERS THEN
678            Result := 0;
679END fnjob;
680
681SELECT job, ename, sal, fnjob(job),
682    SUM(sal) OVER (ORDER BY job, sal ROWS BETWEEN CURRENT ROW AND fnjob(job) FOLLOWING) AS sumsal_job
683    -- acelasi lucru, doar schimbam deptno cu job
684FROM emp;
685
686-- 5
687-- Afisati jobul, numele, salarul curent, salarul anterior si salarul urmator pentru angajati pe
688-- job-uri.
689
690SELECT job, ename, sal AS "Salariu curent",
691    LAG(sal, 1) OVER (PARTITION BY job ORDER BY sal) AS "Salariu anterior",
692    -- LAG(sal, 1) = valoarea campului "sal" de la "1" inregistrari anterioare inregistrarii curente
693    LEAD(sal, 1) OVER (PARTITION BY job ORDER BY sal) AS "Salariu urmator"
694    -- LEAD(sal, 1) = valoarea campului "sal" de la "1" inregistrari posterioare inregistrarii curente
695FROM emp;
696
697-- 6
698-- Afisati numarul total de angajati, precum si numarul lor defalcat pe transe de salarii: 0-1000,
699-- 1000-2000, 2000-3000, peste 3000.
700
701SELECT COUNT(empno) AS "Numar total angajati",
702    SUM(CASE WHEN sal BETWEEN 0 AND 1000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 0 si 1000",
703    SUM(CASE WHEN sal BETWEEN 1000 AND 2000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 1000 si 2000",
704    SUM(CASE WHEN sal BETWEEN 2000 AND 3000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 2000 si 3000",
705    SUM(CASE WHEN sal > 3000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul peste 3000"
706    -- SUM(CASE...) numara cate valori ale campului "sal" se incadreaza in conditiile din CASE WHEN
707FROM emp;
708
709-- 7
710-- Afisati suma vanzarilor (amount_sold) pentru clientii 6380 si 6510 grupat pe trimestrele
711-- anului 1999. De asemenea sa se afiseze si vanzarile cumulate pe trimestre de la inceputul
712-- anului 1999. Informatii de afisat: clientul, trimestrul, suma vanzarilor pe trimestrul curent,
713-- suma cumulata pe trimestrele anterioare. Ordonarea si gruparea se face dupa client si trimestru.
714
715SELECT cust.cust_first_name AS "Client", t.calendar_quarter_number AS "Trimestru", 
716    SUM(s.amount_sold) AS "Suma vanzarilor pe trimestrul curent",
717    SUM(SUM(s.amount_sold)) OVER (PARTITION BY cust.cust_first_name ORDER BY t.calendar_quarter_number ROWS UNBOUNDED PRECEDING) AS "Suma vanzarilor anterioare"
718    -- partitionarea se face in functie de client, deoarece clientul este folosit ca referinta in calcule: client X a vandut cantitatea Y in trimestrul Z
719    -- pentru un client fixat, se ordoneaza inregistrarile dupa trimestru, pentru ca se calculeaza suma vanzarilor pe trimestre
720    -- ROWS UNBOUNDED PRECEDING = inregistrarile anterioare, de la inceput pana la cea curenta (offset fizic)
721    
722    -- explicatia pentru SUM(SUM(...)): ni se cere sa afisam suma vanzarilor anterioare, pentru fiecare trimestru afisat
723    -- vanzarile pentru un anumit trimestru se obtin prin a calcula SUM(amount_sold), deci prin a suma tranzactiile efectuate de un anumit client
724    -- deci, suma vanzarilor anterioare se calculeaza prin adunarea tuturor vanzarilor trimestrelor anterioare, deci prin a suma sumele facute anterior, deci SUM(SUM(...))
725FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
726    JOIN times t ON (s.time_id = t.time_id)
727WHERE s.cust_id IN (6380, 6510) -- filtrarile cerute
728    AND t.calendar_year = 1999
729GROUP BY cust.cust_first_name, t.calendar_quarter_number -- grupare si ordonare dupa cum se cere in enunt, pentru ca folosim functia agregat SUM()
730ORDER BY cust.cust_first_name, t.calendar_quarter_number;
731
732-- 8
733-- Afisati suma vanzarilor pentru clientul 6380 pe anul 1999 pe luni. Informatii de afisat:
734-- clientul, luna si anul, suma vanzarilor pe luna. Sa se adauge media sumei vanzarilor pe luna
735-- curenta si pe anterioarele 2 luni. Ordonarea si gruparea se face dupa client si luna.
736
737SELECT cust.cust_first_name, t.calendar_month_number, 
738    SUM(s.amount_sold) AS "Suma vanzari pe luna",
739    ROUND(AVG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_month_number RANGE 2 PRECEDING), 2) AS "Media vanzarilor anterioare"
740    -- ordonarea se face dupa luna, iar fereastra cuprinde si 2 luni anterioare -> atentie, 2 luni, nu 2 inregistrari!
741    -- deci se iau in calcul 2 valori anterioare, de aceea folosim offset logic (RANGE) si nu fizic (ROWS)
742    -- din cauza offset-ului logic, pot exista 4 inregistrari care fac referire la aceleasi 2 luni si sunt luate in calcul ca facand parte din fereastra, desi sunt in numar de 4
743    
744    -- AVG(SUM(...)) -> pentru ca se cere media vanzarilor anterioare, deci media unei sume calculate anterior, de aici AVG(SUM(...))
745FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
746    JOIN times t ON (s.time_id = t.time_id)
747WHERE s.cust_id = 6380
748    AND t.calendar_year = 1999
749GROUP BY cust.cust_first_name, t.calendar_month_number
750ORDER BY cust.cust_first_name, t.calendar_month_number;
751
752-- 9
753-- Afisati suma vanzarilor (amount_sold) pe zile pentru clientii 6380 si 6510, pentru anul 1999
754-- si saptamana 51. Adaugati media sumei vanzarilor pe o fereastra de 3 zile (una inainte si una
755-- dupa ziua curenta). Ordonarea si gruparea se face dupa client si zi (time_id).
756
757SELECT cust.cust_first_name, t.time_id, 
758    SUM(s.amount_sold) AS "Suma vanzari pe zi",
759    ROUND(AVG(SUM(s.amount_sold)) OVER (ORDER BY t.time_id RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING), 2) AS "Media sumei vanzarilor pe 3 zile"
760    -- aceeasi logica precum la 7 si la 8 -> media vanzarilor pe o fereastra de 3 zile = medie de suma
761    -- offset logic din acelasi motiv, zilele se pot repeta in inregistrari si trebuie luate in considerare
762    -- la ferestre cu date calendaristice se foloseste BETWEEN INTERVAL
763FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
764    JOIN times t ON (s.time_id = t.time_id)
765WHERE s.cust_id IN (6380, 6510)
766    AND t.calendar_year = 1999
767    AND t.calendar_week_number = 51
768GROUP BY cust.cust_first_name, t.time_id
769ORDER BY cust.cust_first_name, t.time_id;
770
771-- 10
772-- Afisati suma vanzarilor pe zile intre 10 si 15 octombrie 2000. Adaugati doua coloane, una
773-- care sa afiseze suma vanzarilor pentru linia anterioara si una cu suma vanzarilor pentru linia
774-- urmatoare,
775SELECT t.day_number_in_month, SUM(s.amount_sold) AS "Suma vanzarilor pe ziua curenta",
776    LAG(SUM(s.amount_sold), 1) OVER (ORDER BY SUM(s.amount_sold)) AS "Suma vanzarilor pentru linia anterioara",
777    -- pentru valoarea de pe linia anterioara, folosim functia LAG() peste setul ordonat de inregistrari dupa suma vanzarilor
778    LEAD(SUM(s.amount_sold), 1) OVER (ORDER BY SUM(s.amount_sold)) AS "Suma vanzarilor pentru linia urmatoare"
779    -- pentru valoarea de pe linia anterioara, folosim functia LEAD() peste setul ordonat de inregistrari dupa suma vanzarilor
780FROM sales s JOIN times t ON (s.time_id = t.time_id)
781WHERE t.calendar_month_desc = '2000-10'
782    AND t.day_number_in_month BETWEEN 10 AND 15 -- intre 10 si 15 octombrie 2000
783
784
785-- LAB 4 ---------------------------------------------------------------------------------------------------------------------------
786------------------------------------------------------------------------------------------------------------------------------------
787
788
789/* View-uri materializate in Oracle */
790
791/* EXEMPLE */
792-- Exemplu de view materializat de tip log care suporta sincronizarea rapida pentru tabela CUSTOMER
793CREATE MATERIALIZED VIEW
794    LOG
795    ON customer
796    WITH PRIMARY KEY, ROWID
797    INCLUDING NEW VALUES;
798
799-- Exemplu view materializat cu functii agregat ce stocheaza totalul vanzarilor pe produse
800-- Intai se creeaza view-urile materializate de tip log pentru tabelele referite in view
801CREATE MATERIALIZED VIEW
802    LOG
803    ON products
804    WITH SEQUENCE, ROWID (prod_id, prod_name, prod_desc, prod_subcategory, prod_subcat_desc,
805                        prod_category, prod_cat_desc, prod_weight_class, prod_unit_of_measure,
806                        prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price)
807    INCLUDING NEW VALUES;
808
809CREATE MATERIALIZED VIEW
810    LOG
811    ON sales
812    WITH SEQUENCE, ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
813    INCLUDING NEW VALUES;
814
815-- Se creeaza view-ul materializat cu valori agregate:
816CREATE MATERIALIZED VIEW product_sales_mv
817BUILD IMMEDIATE
818REFRESH FAST
819AS
820    SELECT p.prod_name, SUM(amount_sold) AS dollar_sales,
821        COUNT(*) AS cnt, COUNT(amount_sold) AS cnt_amt
822    FROM sales s, products p
823    WHERE s.prod_id = p.prod_id
824    GROUP BY p.prod_name;
825/* View-ul calculeaza totalul vanzarilor si numarul produselor vandute. Tabelele master sunt
826SALES si PRODUCT, aflate in relatie de join pe baza coloanei prod_id. View-ul este populat
827imediat (BUILD IMMEDIATE) si suporta sincronizarea rapida (REFRESH FAST) deoarece
828tabelele de baza au view-uri de tip log. */
829
830-- Exemplu de view materializat de tip join
831-- Se afiseaza clientii si valorile de vanzari si de cantitati pe unitati de timp.
832-- Se creeaza view-urile de tip log daca nu sunt deja create.
833CREATE MATERIALIZED VIEW
834    LOG
835    ON sales
836    WITH ROWID;
837CREATE MATERIALIZED VIEW
838    LOG
839    ON times
840    WITH ROWID;
841CREATE MATERIALIZED VIEW
842    LOG
843    ON customers
844    WITH ROWID;
845-- Se creeaza view-ul de tip join:
846CREATE MATERIALIZED VIEW detail_sales_mv
847    BUILD IMMEDIATE
848    REFRESH FAST
849    AS
850    SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
851        c.cust_id, c.cust_last_name, s.amount_sold,
852        s.quantity_sold, s.time_id
853    FROM sales s, times t, customers c
854    WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
855-- imbunatatire de performanta:
856CREATE INDEX mv_ix_salesrid ON detail_sales_mv("sales_rid");
857
858-- Exemplu de view materializat imbricat („nested”):
859CREATE MATERIALIZED VIEW 
860    LOG
861    ON sales
862    WITH ROWID;
863CREATE MATERIALIZED VIEW 
864    LOG 
865    ON customers
866    WITH ROWID;
867CREATE MATERIALIZED VIEW 
868    LOG 
869    ON times
870    WITH ROWID;
871-- Se creaza view-ul de tip join:
872CREATE MATERIALIZED VIEW join_sales_cust_time
873    REFRESH FAST
874    ON COMMIT
875    AS
876        SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,
877            t.day_number_in_week, s.rowid srid, t.rowid trid, c.rowid crid
878        FROM sales s, customers c, times t
879        WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;
880
881-- Se creeaza view-ul de tip log pentru join_sales_cust_time:
882CREATE MATERIALIZED VIEW
883    LOG
884    ON join_sales_cust_time
885    WITH ROWID (cust_last_name, day_number_in_week, amount_sold)
886    INCLUDING NEW VALUES;
887
888-- Se creeaza view-ul materializat de tip agregat pe o singura tabela (view):
889CREATE MATERIALIZED VIEW sum_sales_cust_time
890    REFRESH FAST
891    ON COMMIT
892    AS
893        SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales,
894            COUNT(amount_sold) cnt_sales,
895            cust_last_name, day_number_in_week
896        FROM join_sales_cust_time
897        GROUP BY cust_last_name, day_number_in_week;
898
899-- Exemplu de view pre-inregistrat:
900-- Creare tabel initial:
901CREATE TABLE sum_sales_tab
902    AS
903        SELECT s.prod_id,
904            SUM(amount_sold) AS dollar_sales,
905            SUM(quantity_sold) AS unit_sales
906        FROM sales s GROUP BY s.prod_id;
907-- Creare view materializat pre-inregistrat:
908CREATE MATERIALIZED VIEW sum_sales_tab
909    ON PREBUILT TABLE
910    WITHOUT REDUCED PRECISION
911    AS
912        SELECT s.prod_id,
913            SUM(amount_sold) AS dollar_sales,
914            SUM(quantity_sold) AS unit_sales
915        FROM sales s GROUP BY s.prod_id;
916
917-- Exemplu DROP:
918DROP MATERIALIZED VIEW sum_sales_tab;
919
920-- Exemple ALTER:
921ALTER MATERIALIZED VIEW sum_sales_tab
922REFRESH FAST;
923
924ALTER MATERIALIZED VIEW sum_sales_tab
925REFRESH NEXT SYSDATE + 7;
926
927ALTER MATERIALIZED VIEW sum_sales_tab 
928COMPILE;
929
930/* EXERCITII */
931-- 1
932-- Se dau tabelele PRODUCT, CUSTOMER, ORD, ITEM. Creati o tabela fapta (“fact”),
933-- numita ORDITEM care sa contina datele din tabelele ORD si ITEM si care sa aiba coloanele:
934-- custid, prodid, orderdate, commplan, shipdate, qty, actualprice si itemtot.
935-- *mai intai cream structura tabelului si datele puse folosind un SELECT cu 2 JOIN-uri
936
937CREATE TABLE orditem
938AS
939    SELECT o.custid, p.prodid, o.orderdate, o.commplan, o.shipdate, i.qty, i.actualprice, i.itemtot
940    FROM ord o JOIN item i ON (o.ordid = i.ordid)
941        JOIN product p ON (i.prodid = p.prodid);
942
943-- constrangerile de chei straine nu sunt pastrate, asa incat le cream manual
944ALTER TABLE orditem
945ADD CONSTRAINT fk_custid FOREIGN KEY (custid) REFERENCES customer(custid);
946ALTER TABLE orditem
947ADD CONSTRAINT fk_prodid FOREIGN KEY (prodid) REFERENCES product(prodid);
948
949-- trebuie sa cream o cheie primara pe acest tabel
950-- incepem cu o secventa (pentru autoincrement)
951CREATE SEQUENCE orditem_seq;
952
953-- adaugam un camp ID
954ALTER TABLE orditem
955ADD id NUMBER;
956
957-- pe care il facem cheie primara
958ALTER TABLE orditem
959ADD CONSTRAINT pk_orditem PRIMARY KEY(id);
960
961-- pentru fiecare inregistrare existenta, setam valoarea cheii primare
962DECLARE
963    v_orditem_record orditem%ROWTYPE; -- tipul de data inregistrare din tabelul orditem
964    
965    CURSOR c_orditem IS -- acest cursor va tine toate inregistrarile din orditem
966        SELECT *
967        FROM orditem;
968BEGIN
969    FOR orditem_rec IN c_orditem -- iteram prin cursor
970    LOOP
971        UPDATE orditem -- si actualizam campul nou creat cu valoarea data de secventa
972        SET id = orditem_seq.NEXTVAL
973        WHERE custid = orditem_rec.custid
974            AND prodid = orditem_rec.prodid
975            AND NVL(commplan, 0) = NVL(orditem_rec.commplan, 0) -- posibilitate de NULL!
976            AND shipdate = orditem_rec.shipdate
977            AND qty = orditem_rec.qty
978            AND actualprice = orditem_rec.actualprice
979            AND itemtot = orditem_rec.itemtot;
980    END LOOP;
981END;
982
983-- 2
984-- Pentru tabelele PRODUCT, CUSTOMER si ORDITEM creati view-urile materializate de tip
985-- log pentru a folosi tipul de sincronizare rapida („FAST REFRESH”) . Includeti si coloanele
986-- care trebuie logate.
987
988CREATE MATERIALIZED VIEW
989    LOG
990    ON product
991    WITH PRIMARY KEY, ROWID
992    INCLUDING NEW VALUES;
993
994CREATE MATERIALIZED VIEW
995    LOG
996    ON customer
997    WITH PRIMARY KEY, ROWID
998    INCLUDING NEW VALUES;
999
1000CREATE MATERIALIZED VIEW
1001    LOG
1002    ON orditem
1003    WITH PRIMARY KEY, ROWID
1004    INCLUDING NEW VALUES;
1005
1006-- 3
1007-- Creati un view materializat de tip join, fara valori agregat, care sa stocheze informatiile din
1008-- comenzi precum si denumirile de produse. View-ul se populeaza imediat cu date, poate fi
1009-- sincronizat rapid (FAST REFRESH) atunci cand se comite tranzactia. Afisati informatiile din
1010-- view-ul materializat. Inserati o comanda noua si verificati informatiile din view-ul materializat.
1011
1012CREATE MATERIALIZED VIEW order_info_mv
1013    BUILD IMMEDIATE -- se populeaza imediat cu date
1014    REFRESH FAST -- poate fi sincronizat rapid
1015    ON COMMIT -- atunci cand se comite tranzactia
1016    AS
1017    SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
1018        c.cust_id, c.cust_last_name, s.amount_sold,
1019        s.quantity_sold, s.time_id
1020    FROM sales s, times t, customers c
1021    WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
1022
1023SELECT *
1024FROM order_info_mv;
1025
1026-- inseram o comanda noua
1027INSERT INTO sales
1028VALUES (150, 100, '30-JUN-98', 'C', 123, 3, 1239.5);
1029
1030COMMIT;
1031
1032-- verificam ca a fost inserata in view-ul materializat
1033SELECT *
1034FROM order_info_mv
1035WHERE cust_id = 100 AND time_id = '30-JUN-98' AND amount_sold = 3 AND quantity_sold = 1239.5;
1036
1037-- 4
1038-- In mod analog create un view materializat de tip join pentru afisarea datelor din comenzi
1039-- precum si informatiile din clienti: nume, oras, stat, limita de credit.
1040-- *trebuie create view-urile materializate de tip LOG mai intai, pe tabelele ord si customer
1041
1042CREATE MATERIALIZED VIEW
1043    LOG
1044    ON ord
1045    WITH PRIMARY KEY, ROWID
1046    INCLUDING NEW VALUES;
1047
1048CREATE MATERIALIZED VIEW
1049    LOG
1050    ON customer
1051    WITH PRIMARY KEY, ROWID
1052    INCLUDING NEW VALUES;
1053
1054-- in view-ul materializat de tip JOIN trebuie incluse neaparat coloanele de tip "rowid" ale tabelelor implicate
1055CREATE MATERIALIZED VIEW order_cust_info_mv
1056    BUILD IMMEDIATE
1057    REFRESH FAST
1058    AS
1059        SELECT c.rowid "customers_rid", o.rowid "ord_rid", o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
1060            c.name, c.city, c.state, c.creditlimit
1061        FROM ord o JOIN customer c ON (o.custid = c.custid);
1062
1063-- 5
1064-- Creati un view materializat care sa stocheze totalul vanzarilor si cantitatile totale pe produse,
1065-- view bazat pe cel creat anterior, de tip join. View-ul se populeaza imediat cu date, poate fi
1066-- sincronizat rapid (FAST REFRESH) atunci cand se comite tranzactia. Afisati informatiile din
1067-- view-ul materializat. Inserati o comanda noua si verificati informatiile din view-ul materializat.
1068-- *acest view se bazeaza pe cel creat la exercitiul 3 (order_info_mv)
1069-- *mai intai, trebuie creata o cheie primara pe view-ul materializat order_info_mv
1070
1071ALTER MATERIALIZED VIEW order_info_mv
1072ADD CONSTRAINT PK_sales_times_cust PRIMARY KEY ("sales_rid", "times_rid", "customers_rid");
1073
1074-- apoi, un view materializat de tip LOG pe order_info_mv, care sa inregistreze ROWID-urile si cheia primara
1075CREATE MATERIALIZED VIEW
1076    LOG
1077    ON order_info_mv
1078    WITH ROWID, PRIMARY KEY
1079    INCLUDING NEW VALUES;
1080
1081-- abia apoi se poate crea MV-ul de care avem nevoie
1082CREATE MATERIALIZED VIEW total_sales_mv
1083    BUILD IMMEDIATE
1084    REFRESH FAST ON COMMIT
1085    AS
1086        -- trebuie sa se includa neaparat coloanele ce compun cheia primara!
1087        -- puse intre ghilimele pt ca sunt case sensitive
1088        SELECT "sales_rid", "times_rid", "customers_rid", quantity_sold, amount_sold
1089        FROM order_info_mv;
1090
1091-- inseram o comanda noua
1092INSERT INTO sales
1093VALUES (150, 100, '01-JUN-98', 'C', 123, 4, 2259);
1094
1095COMMIT;
1096
1097-- verificam ca a fost inserata in view-ul materializat
1098SELECT *
1099FROM total_sales_mv
1100WHERE amount_sold = 4 AND quantity_sold = 2259;
1101
1102-- 6
1103-- Creati acelasi tip de view materializat pentru totalul vanzarilor pe clienti. View-ul sa poate
1104-- aplica sincronizarea rapida daca este posibil sau sincronizarea completa in caz contrar.
1105-- Executati aceleasi operatii.
1106
1107CREATE MATERIALIZED VIEW total_sales_cust_mv
1108    BUILD IMMEDIATE
1109    REFRESH COMPLETE
1110    AS
1111        SELECT s.cust_id, SUM(s.amount_sold) AS "Total vanzari"
1112        FROM sales s
1113        GROUP BY s.cust_id;
1114
1115-- 7
1116-- Creati un view materializat pre-inregistrat care sa stocheze suma vanzarilor si a cantitatilor
1117-- pe luni calendaristice.
1118-- *creare tabel initial
1119
1120CREATE TABLE sum_sales_months_tab
1121    AS
1122    SELECT t.calendar_month_desc,
1123        SUM(s.amount_sold) AS "Total valoare vanzari",
1124        SUM(s.quantity_sold) AS "Total cantitate vanduta"
1125    FROM sales s JOIN times t ON (s.time_id = t.time_id)
1126    GROUP BY t.calendar_month_desc;
1127
1128-- creare view materializat pre-inregistrat
1129CREATE MATERIALIZED VIEW sum_sales_months_tab
1130ON PREBUILT TABLE
1131WITHOUT REDUCED PRECISION
1132AS
1133    SELECT t.calendar_month_desc,
1134        SUM(s.amount_sold) AS "Total valoare vanzari",
1135        SUM(s.quantity_sold) AS "Total cantitate vanduta"
1136    FROM sales s JOIN times t ON (s.time_id = t.time_id)
1137    GROUP BY t.calendar_month_desc;
1138
1139-- 8
1140-- Modificati view-urile create anterior cu diversi parametri. Stergeti view-uirile create.
1141
1142-- modificarea tipului de REFRESH
1143ALTER MATERIALIZED VIEW total_sales_mv
1144REFRESH FAST;
1145
1146-- fortarea urmatorului REFRESH
1147ALTER MATERIALIZED VIEW sum_sales_tab
1148REFRESH NEXT SYSDATE + 7;
1149
1150-- compilarea unui view materializat
1151ALTER MATERIALIZED VIEW order_info_mv
1152COMPILE;
1153
1154-- stergerea view-urilor materializate
1155DROP MATERIALIZED VIEW detail_sales_mv;
1156DROP MATERIALIZED VIEW order_cust_info_mv;
1157DROP MATERIALIZED VIEW order_info_mv;
1158DROP MATERIALIZED VIEW product_sales_mv;
1159DROP MATERIALIZED VIEW sum_sales_months_tab;
1160DROP MATERIALIZED VIEW total_sales_mv;
1161
1162
1163-- LAB 5 ---------------------------------------------------------------------------------------------------------------------------
1164------------------------------------------------------------------------------------------------------------------------------------
1165
1166/* Indecsi in Oracle */
1167
1168/* EXEMPLE */
1169-- Index normal, simplu, unic
1170-- Index unic pe coloana ename din tabela EMP
1171CREATE UNIQUE INDEX idx_empename
1172ON emp(ename);
1173-- Interogare pentru care este folosit indexul:
1174SELECT *
1175FROM emp
1176WHERE ename = 'MARTIN';
1177
1178-- Index normal, simplu, ne-unic
1179-- Se creeaza pe o tabela pe o coloana cu valori ne-unice
1180-- pentru coloanele din conditia de join:
1181CREATE INDEX idxemp_deptno 
1182ON emp(deptno);
1183-- Serverul foloseste indexul pentru interogari de tipul:
1184SELECT *
1185FROM emp e, dept d
1186WHERE e.deptno = d.deptno;
1187
1188-- pentru coloane de filtrare din clauza WHERE:
1189CREATE INDEX idxemp_hiredate
1190ON emp(hiredate);
1191
1192-- Serverul foloseste indexul pentru interogari de tipul:
1193SELECT *
1194FROM emp
1195WHERE hiredate BETWEEN TO_DATE('20/02/1981', 'DD/MM/YYYY') 
1196    AND TO_DATE('20/02/1982', 'DD/MM/YYYY');
1197
1198-- Index normal, concatenat
1199-- Se creeaza atunci cand doua sau mai multe coloane sunt utilizate impreuna in majoritatea
1200-- interogarilor pe tabele mari.
1201CREATE INDEX idxemp_salcomm
1202ON emp(sal, comm);
1203
1204-- Serverul foloseste indexul pentru interogari de tipul
1205SELECT *
1206FROM emp
1207WHERE sal < 1500 AND comm IS NOT NULL;
1208
1209-- Index normal bazat pe functie
1210CREATE INDEX idxemp_upperemp
1211ON emp(UPPER(ename));
1212
1213-- Serverul foloseste indexul pentru interogari de tipul:
1214SELECT *
1215FROM emp
1216WHERE UPPER(ename) LIKE '%LL%';
1217
1218-- Indecsi de tip bitmap
1219CREATE BITMAP INDEX custidx_gender
1220ON customers (cust_gender);
1221
1222CREATE BITMAP INDEX custidx_marital
1223ON customers (cust_marital_status);
1224
1225CREATE BITMAP INDEX custidx_income
1226ON customers (cust_income_level);
1227
1228-- Cati clienti casatoriti au nivelul de venituri de tipul G sau H?
1229SELECT COUNT(*)
1230FROM customers
1231WHERE cust_marital_status = 'married'
1232    AND cust_income_level IN ('H: 150,000 - 169,999', 'G: 130,000 - 149,999');
1233
1234/* EXERCITII */
1235-- 2
1236-- Se dau tabelele PRODUCT, CUSTOMER, ORD, ITEM. Creati urmatorii indexi:
1237-- - pentru tabela CUSTOMER:
1238-- - creati indexii unici pentru coloana NAME;
1239-- - creati indexi simpli, normali pentru CREDITLIMIT si REPID;
1240-- - creati un index pe baza de functie care sa optimizeze accesul dupa numele clientului
1241-- atunci cand valorile se introduc cu litere mici;
1242-- - pentru tabela ORD creati indexii necesari pentru interogarile care implica un join cu tabela de
1243-- clienti si care filtreaza liniile dupa data comenzii;
1244
1245CREATE UNIQUE INDEX customeridx_name
1246ON customer(name);
1247
1248CREATE INDEX customeridx_creditlimit 
1249ON customer(creditlimit); -- coloana ce contine valori neunice
1250
1251CREATE INDEX customeridx_repid
1252ON customer(repid); -- coloana ce contine valori neunice
1253
1254CREATE INDEX customeridx_fn_name
1255ON customer(LOWER(name));
1256-- daca numele se introduc cu litere mici, se face index cu functia LOWER() pentru optimizare
1257
1258CREATE INDEX ordidx_custid
1259ON ord(custid, -- pentru criteriul de JOIN cu tabela customer
1260    shipdate); -- pentru filtrarea dupa data comenzii
1261
1262-- 3
1263-- Afisati produsele comandate in lunile de vara ale anului 1986. Se vor afisa informatiile din
1264-- comanda, numele clientului, orasul, statul si limita de credit, numele agentului si numele
1265-- departamentului din care face parte, precum si liniile de detaliu (numele produsului, cantitate,
1266-- pretul si valoarea). Creati indexii necesari si apoi sa-i afisati din dictionarul de date.
1267
1268SELECT o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
1269    c.name, c.city, c.state, c.creditlimit, 
1270    e.ename AS "NUME AGENT",
1271    d.dname AS "NUME DEPARTAMENT",
1272    p.descrip AS "NUME PRODUS", 
1273    i.qty, i.actualprice, i.itemtot
1274FROM ord o JOIN customer c ON (o.custid = c.custid) -- pentru a prelua clientul
1275    JOIN emp e ON (c.repid = e.empno) -- pentru a prelua agentul
1276    JOIN dept d ON (e.deptno = d.deptno) -- pentru a prelua numele departamentului
1277    JOIN item i ON (o.ordid = i.ordid) -- pentru a gasi ID-ul de produs pentru o anumita comanda
1278    JOIN product p ON (i.prodid = p.prodid) -- pentru a prelua numele produsului
1279WHERE o.shipdate BETWEEN TO_DATE('01/06/1986', 'DD/MM/YYYY') -- in lunile de vara ale anului 1986
1280    AND TO_DATE('01/09/1986', 'DD/MM/YYYY');
1281
1282-- se creeaza indecsi pentru fiecare FOREIGN KEY, conform conditiilor de JOIN
1283CREATE INDEX ordidx_custid
1284ON ord(custid);
1285
1286CREATE INDEX customeridx_repid
1287ON customer(repid);
1288
1289CREATE INDEX empidx_deptno
1290ON emp(deptno);
1291
1292CREATE INDEX itemidx_ordid
1293ON item(ordid);
1294
1295CREATE INDEX itemidx_prodid
1296ON item(prodid);
1297
1298-- apoi se creeaza index pentru conditia din WHERE
1299-- este index neunic, pentru ca data comenzii poate avea valori duplicate (2 comenzi in aceeasi zi)
1300CREATE INDEX ordidx_shipdate
1301ON ord(shipdate);
1302
1303-- afisam indecsii din baza de date
1304SELECT * FROM USER_INDEXES idx, USER_IND_COLUMNS idxc
1305WHERE idx.index_name = idxc.index_name
1306    AND idx.index_name IN ('ORDIDX_CUSTID', 'CUSTOMERIDX_REPID', 'EMPIDX_DEPTNO',
1307        'ITEMIDX_ORDID', 'IREMIDX_PRODID', 'ORDIDX_SHIPDATE');
1308
1309-- 4
1310-- La interogarea anterioara filtrati liniile de comanda care au valoare mai mare decat 400, unde
1311-- valoarea se calculeaza ca produs intre cantitate si pret. Creati un index corespunzator pentru
1312-- optimizarea accesului.
1313
1314SELECT o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
1315    c.name, c.city, c.state, c.creditlimit, 
1316    e.ename AS "NUME AGENT",
1317    d.dname AS "NUME DEPARTAMENT",
1318    p.descrip AS "NUME PRODUS", 
1319    i.qty, i.actualprice, i.itemtot
1320FROM ord o JOIN customer c ON (o.custid = c.custid) -- pentru a prelua clientul
1321    JOIN emp e ON (c.repid = e.empno) -- pentru a prelua agentul
1322    JOIN dept d ON (e.deptno = d.deptno) -- pentru a prelua numele departamentului
1323    JOIN item i ON (o.ordid = i.ordid) -- pentru a gasi ID-ul de produs pentru o anumita comanda
1324    JOIN product p ON (i.prodid = p.prodid) -- pentru a prelua numele produsului
1325WHERE o.shipdate BETWEEN TO_DATE('01/06/1986', 'DD/MM/YYYY') -- in lunile de vara ale anului 1986
1326    AND TO_DATE('01/09/1986', 'DD/MM/YYYY')
1327    AND i.qty * i.actualprice > 400; -- filtram si liniile de comanda cu valoare > 400
1328
1329-- pentru optimizarea accesului, cream un index de tip functie, cu formula calculului specificat in enunt
1330CREATE INDEX itemidx_value
1331ON item(qty * actualprice);
1332
1333-- 5
1334-- Pe baza tabelelor PRODUCT, CUSTOMER, ORD, ITEM, creati o tabela fapta (“fact”),
1335-- numita ORDITEM care sa contina datele din tabelele ORD si ITEM si care sa aiba coloanele:
1336-- custid, prodid, orderdate, commplan, shipdate, qty, actualprice si itemtot. Creati indexi
1337-- necesari:
1338-- - unic compus pentru client, produs;
1339-- - creati un index de tip bitmap pentru coloanele candidate;
1340-- - lansati o serie de interogari care sa utilizeze indexii creati;
1341-- *mai intai cream structura tabelului si datele puse folosind un SELECT cu 2 JOIN-uri
1342
1343CREATE TABLE orditem
1344AS
1345    SELECT o.custid, p.prodid, o.orderdate, o.commplan, o.shipdate, i.qty, i.actualprice, i.itemtot
1346    FROM ord o JOIN item i ON (o.ordid = i.ordid)
1347        JOIN product p ON (i.prodid = p.prodid);
1348
1349-- constrangerile de chei straine nu sunt pastrate, asa incat le cream manual
1350ALTER TABLE orditem
1351ADD CONSTRAINT fk_custid FOREIGN KEY (custid) REFERENCES customer(custid);
1352ALTER TABLE orditem
1353ADD CONSTRAINT fk_prodid FOREIGN KEY (prodid) REFERENCES product(prodid);
1354
1355-- trebuie sa cream o cheie primara pe acest tabel
1356-- incepem cu o secventa (pentru autoincrement)
1357CREATE SEQUENCE orditem_seq;
1358
1359-- adaugam un camp ID
1360ALTER TABLE orditem
1361ADD id NUMBER;
1362
1363-- pe care il facem cheie primara
1364ALTER TABLE orditem
1365ADD CONSTRAINT pk_orditem PRIMARY KEY(id);
1366
1367-- pentru fiecare inregistrare existenta, setam valoarea cheii primare
1368DECLARE
1369    v_orditem_record orditem%ROWTYPE; -- tipul de data inregistrare din tabelul orditem
1370    
1371    CURSOR c_orditem IS -- acest cursor va tine toate inregistrarile din orditem
1372        SELECT *
1373        FROM orditem;
1374BEGIN
1375    FOR orditem_rec IN c_orditem -- iteram prin cursor
1376    LOOP
1377        UPDATE orditem -- si actualizam campul nou creat cu valoarea data de secventa
1378        SET id = orditem_seq.NEXTVAL
1379        WHERE custid = orditem_rec.custid
1380            AND prodid = orditem_rec.prodid
1381            AND NVL(commplan, 0) = NVL(orditem_rec.commplan, 0) -- posibilitate de NULL!
1382            AND shipdate = orditem_rec.shipdate
1383            AND qty = orditem_rec.qty
1384            AND actualprice = orditem_rec.actualprice
1385            AND itemtot = orditem_rec.itemtot;
1386    END LOOP;
1387END;
1388
1389-- acum cream indecsii necesari:
1390-- index unic compus pentru client, produs
1391-- (adaugam si campul ID creat mai sus, pentru ca exista perechi (custid, prodid) duplicat)
1392CREATE UNIQUE INDEX orditemidx_cust_prod
1393ON orditem(custid, prodid, id);
1394
1395-- index bitmap pentru coloanele candidate (adica cele cu cardinalitate redusa)
1396CREATE BITMAP INDEX orditemidx_commplan
1397ON orditem(commplan);
1398
1399-- interogare care utilizeaza index-ul orditemidx_cust_prod
1400SELECT custid, prodid, orderdate, shipdate
1401FROM orditem
1402WHERE custid = 106 AND (prodid = 100861 OR prodid = 101863) AND id < 14;
1403
1404-- interogare care utilizeaza index-ul orditemidx_commplan
1405SELECT *
1406FROM orditem
1407WHERE commplan IN ('A', 'C');
1408
1409-- 6
1410-- Afisati suma vanzarilor pe primul trimestru al anului 1998 pentru clientii din Europa de vest
1411-- si America de sud (tabelele SALES, CUSTOMERS, COUNTRIES, TIMES).
1412-- Creati indexii necesari si sa-i afisati din dictionarul de date.
1413
1414SELECT cu.cust_first_name, SUM(s.amount_sold) AS "Suma vanzarilor"
1415FROM sales s JOIN customers cu ON (s.cust_id = cu.cust_id) -- pentru a afla numele clientului
1416    JOIN countries co ON (cu.country_id = co.country_id) -- pentru a filtra in functie de cele 2 regiuni
1417    JOIN times t ON (s.time_id = t.time_id) -- pentru a filtra in functie de trimestru si an
1418WHERE t.calendar_quarter_desc = '1998-Q1' -- primul trimestru al anului 1998
1419    AND co.country_subregion IN ('Western Europe', 'Southern America') -- clientii din Europa de vest si America de sud
1420GROUP BY cu.cust_first_name; -- avem o functie de grup (SUM), trebuie grupate datele dupa clienti
1421
1422-- cream indecsii necesari:
1423-- indecsi pentru coloanele de JOIN
1424CREATE INDEX salesidx_cust_id
1425ON sales(cust_id);
1426
1427CREATE UNIQUE INDEX customersidx_cust_id
1428ON customers(cust_id);
1429
1430CREATE INDEX customersidx_country_id
1431ON customers(country_id);
1432
1433CREATE UNIQUE INDEX countriesidx_country_id
1434ON countries(country_id);
1435
1436CREATE INDEX salesidx_time_id
1437ON sales(time_id);
1438
1439CREATE UNIQUE INDEX timesidx_time_id
1440ON times(time_id);
1441
1442-- index de tip bitmap pentru subregiuni (coloana cu cardinalitate redusa)
1443CREATE BITMAP INDEX countriesidx_country_subregion
1444ON countries(country_subregion);
1445
1446-- index simplu, neunic pe trimestrele anilor (exista valori duplicat, iar cardinalitatea este destul de consistenta)
1447CREATE INDEX timesidx_calendar_quarter_desc
1448ON times(calendar_quarter_desc);
1449
1450
1451-- LAB 6 ---------------------------------------------------------------------------------------------------------------------------
1452------------------------------------------------------------------------------------------------------------------------------------
1453
1454/* Optimizarea interogarilor in Oracle */
1455
1456/* EXEMPLU */
1457EXPLAIN PLAN FOR
1458SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc
1459FROM emp e, dept d
1460WHERE e.deptno = d.deptno AND e.sal < 2200;
1461
1462-- afisare plan sub forma de tabel
1463SELECT plan_table_output
1464FROM TABLE(dbms_xplan.display('plan_table', null, 'serial'));
1465
1466/* EXERCITII */
1467-- 2
1468-- Afisati produsele comandate in lunile de vara ale anului 1986. Se vor afisa informatiile din
1469-- comanda, numele clientului, orasul, statul si limita de credit, numele agentului si numele
1470-- departamentului din care face parte, precum si liniile de detaliu (numele produsului, cantitate,
1471-- pretul si valoarea). Executati interogarea si afisati planul de executie al interogarii. Analizati
1472-- acest plan. Creati indexii necesari pentru a optimiza acest plan de executie
1473
1474EXPLAIN PLAN FOR
1475SELECT o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
1476    c.name, c.city, c.state, c.creditlimit, 
1477    e.ename AS "NUME AGENT",
1478    d.dname AS "NUME DEPARTAMENT",
1479    p.descrip AS "NUME PRODUS", 
1480    i.qty, i.actualprice, i.itemtot
1481FROM ord o JOIN customer c ON (o.custid = c.custid) -- pentru a prelua clientul
1482    JOIN emp e ON (c.repid = e.empno) -- pentru a prelua agentul
1483    JOIN dept d ON (e.deptno = d.deptno) -- pentru a prelua numele departamentului
1484    JOIN item i ON (o.ordid = i.ordid) -- pentru a gasi ID-ul de produs pentru o anumita comanda
1485    JOIN product p ON (i.prodid = p.prodid) -- pentru a prelua numele produsului
1486WHERE o.shipdate BETWEEN TO_DATE('01/06/1986', 'DD/MM/YYYY') -- in lunile de vara ale anului 1986
1487    AND TO_DATE('01/09/1986', 'DD/MM/YYYY');
1488
1489-- afisare plan sub forma de tabel
1490SELECT plan_table_output
1491FROM TABLE(dbms_xplan.display('plan_table', null, 'serial'));
1492
1493-- pentru optimizarea planului:
1494-- se creeaza indecsi pentru fiecare FOREIGN KEY, conform conditiilor de JOIN
1495CREATE INDEX ordidx_custid
1496ON ord(custid);
1497
1498CREATE INDEX customeridx_repid
1499ON customer(repid);
1500
1501CREATE INDEX empidx_deptno
1502ON emp(deptno);
1503
1504CREATE INDEX itemidx_ordid
1505ON item(ordid);
1506
1507CREATE INDEX itemidx_prodid
1508ON item(prodid);
1509
1510-- apoi se creeaza index pentru conditia din WHERE
1511CREATE INDEX ordidx_shipdate
1512ON ord(shipdate);
1513
1514-- 3
1515-- La interogarea anterioara filtrati liniile de comanda care au valoare mai mare decat 400,
1516-- unde valoarea se calculeaza ca produs intre cantitate si pret. Creati un index corespunzator
1517-- pentru optimizarea accesului. Afisati si analizati planul de executie al interogarii.
1518
1519EXPLAIN PLAN FOR
1520SELECT o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
1521    c.name, c.city, c.state, c.creditlimit, 
1522    e.ename AS "NUME AGENT",
1523    d.dname AS "NUME DEPARTAMENT",
1524    p.descrip AS "NUME PRODUS", 
1525    i.qty, i.actualprice, i.itemtot
1526FROM ord o JOIN customer c ON (o.custid = c.custid) -- pentru a prelua clientul
1527    JOIN emp e ON (c.repid = e.empno) -- pentru a prelua agentul
1528    JOIN dept d ON (e.deptno = d.deptno) -- pentru a prelua numele departamentului
1529    JOIN item i ON (o.ordid = i.ordid) -- pentru a gasi ID-ul de produs pentru o anumita comanda
1530    JOIN product p ON (i.prodid = p.prodid) -- pentru a prelua numele produsului
1531WHERE o.shipdate BETWEEN TO_DATE('01/06/1986', 'DD/MM/YYYY') -- in lunile de vara ale anului 1986
1532    AND TO_DATE('01/09/1986', 'DD/MM/YYYY')
1533    AND i.qty * i.actualprice > 400; -- filtram si liniile de comanda cu valoare > 400
1534    
1535-- afisare plan sub forma de tabel
1536SELECT plan_table_output
1537FROM TABLE(dbms_xplan.display('plan_table', null, 'serial'));
1538
1539-- pentru optimizarea accesului, cream un index de tip functie, cu formula calculului specificat in enunt
1540CREATE INDEX itemidx_value
1541ON item(qty * actualprice);
1542
1543-- 4
1544-- Analizati planurile de executie ale urmatoarelor interogari. Creati indexii necesari pentru a
1545-- optimiza aceste planuri de executie. Comparati planurile de executie inainte si dupa crearea
1546-- indexilor.
1547-- *interogarea nr. 1
1548
1549SELECT ename, job, sal, dname
1550FROM emp, dept
1551WHERE dept.deptno = emp.deptno
1552    AND NOT EXISTS (SELECT *
1553                    FROM salgrade
1554                    WHERE emp.sal BETWEEN losal AND hisal);
1555                    
1556-- afisare plan sub forma de tabel
1557SELECT plan_table_output
1558FROM TABLE(dbms_xplan.display('plan_table', null, 'serial'));
1559
1560-- pentru optimizare:
1561CREATE INDEX sal_idx
1562ON emp(sal);
1563
1564-- interogare dictata de prof
1565SELECT *
1566FROM sales s JOIN products p ON (s.prod_id = p.prod_id)
1567    JOIN times t ON (s.time_id = t.time_id)
1568WHERE p.prod_category = 'Men'
1569    AND t.calendar_quarter_desc IN ('1998-Q1', '1998-Q2');
1570
1571-- pentru optimizare, cream index pentru cheile straine prod_id si time_id
1572CREATE INDEX prod_id_idx
1573ON sales(prod_id);
1574
1575CREATE INDEX time_id_idx
1576ON sales(time_id);
1577
1578-- un index de tip bitmap pentru categoria de produse (pentru ca are cardinalitate mica)
1579CREATE BITMAP INDEX productsidx_prod_category
1580ON products(prod_category);
1581
1582-- si index pentru calendar_quarter_desc
1583CREATE INDEX calendar_quarter_desc_idx
1584ON times(calendar_quarter_desc);
1585
1586-- interogarea nr. 2
1587SELECT ename, e.deptno, d.deptno, d.dname
1588FROM emp e, dept d
1589WHERE e.deptno = d.deptno AND ename like 'A%';
1590
1591-- optimizare:
1592CREATE INDEX empidx_deptno
1593ON emp(deptno);
1594
1595CREATE UNIQUE INDEX deptidx_deptno
1596ON dept(deptno);
1597
1598CREATE INDEX empidx_ename
1599ON emp(ename);
1600
1601-- interogarea nr. 3
1602SELECT COUNT(*)
1603FROM products p
1604WHERE prod_list_price < 1.15 * (SELECT AVG(unit_cost)
1605                                FROM costs c
1606                                WHERE c.prod_id = p.prod_id);
1607
1608-- optimizare:
1609CREATE INDEX costsidx_prod_id
1610ON costs(prod_id);
1611
1612CREATE UNIQUE INDEX productsidx_prod_id
1613ON products(prod_id);
1614
1615CREATE INDEX productsidx_prod_list_price
1616ON products(prod_list_price);
1617
1618-- interogarea nr. 4
1619SELECT COUNT(*)
1620FROM products p, (SELECT prod_id, AVG(unit_cost) ac 
1621                FROM costs GROUP BY prod_id) c
1622WHERE p.prod_id = c.prod_id 
1623    AND p.prod_list_price < 1.15 * c.ac;
1624
1625-- optimizare:
1626-- index concatenat (pentru ca in WHERE sunt folosite 2 conditii cu coloane din acelasi tabel)
1627CREATE INDEX productsidx_id_price
1628ON products(prod_id, prod_list_price);
1629
1630-- apoi pentru tabela costs
1631CREATE INDEX costsidx_prod_id
1632ON costs(prod_id);
1633
1634-- interogarea nr. 5
1635SELECT c.cust_first_name, c.cust_last_name, c.cust_id, COUNT(s.prod_id)
1636FROM customers c, sales s
1637WHERE c.cust_id = s.cust_id
1638    AND c.cust_id < 100
1639GROUP BY c.cust_first_name, c.cust_last_name, c.cust_id;
1640
1641-- optimizare: indecsi pentru conditiile din WHERE
1642CREATE INDEX customersidx_cust_id
1643ON customers(cust_id);
1644
1645CREATE INDEX salesidx_cust_id
1646ON sales(cust_id);
1647
1648-- interogarea nr. 6
1649SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
1650FROM sales s, times t, customers c, channels ch
1651WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id 
1652    AND s.channel_id = ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc IN ('Internet','Catalog')
1653    AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
1654GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
1655
1656-- optimizare:
1657-- index compus pentru perechea (time_id, cust_id, channel_id) din tabela sales, pentru ca apar impreuna in WHERE
1658CREATE INDEX salesidx_time_cust_channel
1659ON sales(time_id, cust_id, channel_id);
1660
1661-- index de tip bitmap pentru cust_state_province (are cardinalitate mica)
1662CREATE BITMAP INDEX customersidx_cust_state_province
1663ON customers(cust_state_province);
1664
1665-- asemenea pentru channel_desc
1666CREATE BITMAP INDEX channelsidx_channel_desc
1667ON channels(channel_desc);
1668
1669-- index pentru calendar_quarter_desc
1670CREATE INDEX timesidx_calendar_quarter_desc
1671ON times(calendar_quarter_desc);
1672
1673
1674-- LAB 7 ---------------------------------------------------------------------------------------------------------------------------
1675------------------------------------------------------------------------------------------------------------------------------------
1676
1677/* Tabele partitionate in Oracle */
1678
1679/* EXEMPLE */
1680
1681-- partitionarea dupa interval
1682CREATE TABLE sales_range (
1683    year NUMBER(4),
1684    product_id NUMBER,
1685    amount NUMBER(10, 2)
1686)
1687PARTITION BY RANGE (year)
1688(
1689    PARTITION p1 VALUES LESS THAN (2010),
1690    PARTITION p2 VALUES LESS THAN (2011),
1691    PARTITION p3 VALUES LESS THAN (2012),
1692    PARTITION p4 VALUES LESS THAN (2013),
1693    PARTITION p5 VALUES LESS THAN (MAXVALUE)
1694);
1695
1696-- partitionarea hash
1697CREATE TABLE products_hash (
1698    product_id NUMBER,
1699    description VARCHAR2(60)
1700)
1701PARTITION BY HASH (product_id)
1702PARTITIONS 4;
1703
1704-- partitionarea lista
1705CREATE TABLE clients_list (
1706    client_id NUMBER,
1707    name VARCHAR2(50),
1708    country VARCHAR2(2)
1709)
1710PARTITION BY LIST(country)
1711(
1712    PARTITION clients_benelux VALUES ('BE', 'NE', 'LU'),
1713    PARTITION clients_uk VALUES ('UK'),
1714    PARTITION clients_other VALUES (DEFAULT)
1715);
1716
1717-- partitionare compusa range-range
1718CREATE TABLE shipments_range_range
1719( 
1720    order_id NUMBER NOT NULL, 
1721    order_date DATE NOT NULL,
1722    delivery_date DATE NOT NULL,
1723    customer_id NUMBER NOT NULL,
1724    sales_amount NUMBER NOT NULL
1725)
1726PARTITION BY RANGE (order_date)
1727SUBPARTITION BY RANGE (delivery_date)
1728(
1729    PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))
1730    (
1731        SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy')), 
1732        SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy')),
1733        SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
1734    ),
1735    PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
1736    (
1737        SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy')),
1738        SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')),
1739        SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)
1740    ),
1741    PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
1742    (
1743        SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy')),
1744        SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy')),
1745        SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)
1746    ),
1747    PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
1748    (
1749        SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy')),
1750        SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy')),
1751        SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)
1752    ),
1753    PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
1754    (
1755        SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy')),
1756        SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')),
1757        SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)
1758    ),
1759    PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
1760    (
1761        SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy')),
1762        SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')),
1763        SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)
1764    )
1765);
1766
1767-- partitionare compusa range-hash
1768CREATE TABLE sales_range_hash
1769(
1770    prod_id NUMBER(6),
1771    cust_id NUMBER,
1772    time_id DATE,
1773    channel_id CHAR(1),
1774    promo_id NUMBER(6), 
1775    quantity_sold NUMBER(3),
1776    amount_sold NUMBER(10,2)
1777)
1778PARTITION BY RANGE (time_id)
1779SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 8
1780(
1781    PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')),
1782    PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')),
1783    PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')),
1784    PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
1785);
1786
1787-- partitionare compusa range-list
1788CREATE TABLE regional_sales_range_list
1789(
1790    deptno NUMBER,
1791    item_no VARCHAR2(20),
1792    txn_date DATE,
1793    txn_amount NUMBER,
1794    state VARCHAR2(2)
1795)
1796PARTITION BY RANGE (txn_date)
1797SUBPARTITION BY LIST (state)
1798(
1799    PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
1800    (
1801        SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
1802        SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
1803        SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
1804        SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
1805        SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
1806        SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
1807    ),
1808    PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
1809    (
1810        SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
1811        SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
1812        SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
1813        SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
1814        SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
1815        SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
1816    ),
1817    PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
1818    (
1819        SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
1820        SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
1821        SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
1822        SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
1823        SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
1824        SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
1825    ),
1826    PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
1827    (
1828        SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
1829        SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
1830        SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
1831        SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
1832        SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
1833        SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
1834    )
1835);
1836
1837-- partitionare compusa list-hash
1838CREATE TABLE clients_list_hash 
1839(
1840    client_id NUMBER,
1841    name VARCHAR2(50),
1842    country VARCHAR2(2)
1843)
1844PARTITION BY LIST(country) 
1845SUBPARTITION BY HASH(name) SUBPARTITIONS 5
1846(
1847    PARTITION clients_benelux VALUES ('BE','NE','LU'),
1848    PARTITION clients_uk VALUES ('UK'),
1849    PARTITION clients_other VALUES (DEFAULT)
1850);
1851
1852-- partitionarea compusa list-list
1853CREATE TABLE accounts
1854(
1855    id NUMBER,
1856    account_number NUMBER,
1857    customer_id NUMBER,
1858    balance NUMBER,
1859    branch_id NUMBER,
1860    region VARCHAR(2),
1861    status VARCHAR2(1)
1862)
1863PARTITION BY LIST (region)
1864SUBPARTITION BY LIST (status)
1865(
1866    PARTITION p_northwest VALUES ('OR', 'WA')
1867    ( 
1868        SUBPARTITION p_nw_bad VALUES ('B'), 
1869        SUBPARTITION p_nw_average VALUES ('A'),
1870        SUBPARTITION p_nw_good VALUES ('G')
1871    ),
1872    PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
1873    (
1874        SUBPARTITION p_sw_bad VALUES ('B'),
1875        SUBPARTITION p_sw_average VALUES ('A'),
1876        SUBPARTITION p_sw_good VALUES ('G')
1877    ),
1878    PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
1879    (
1880        SUBPARTITION p_ne_bad VALUES ('B'),
1881        SUBPARTITION p_ne_average VALUES ('A'),
1882        SUBPARTITION p_ne_good VALUES ('G')
1883    ),
1884    PARTITION p_southeast VALUES ('FL', 'GA')
1885    ( 
1886        SUBPARTITION p_se_bad VALUES ('B'),
1887        SUBPARTITION p_se_average VALUES ('A'),
1888        SUBPARTITION p_se_good VALUES ('G')
1889    ),
1890    PARTITION p_northcentral VALUES ('SD', 'WI')
1891    ( 
1892        SUBPARTITION p_nc_bad VALUES ('B'),
1893        SUBPARTITION p_nc_average VALUES ('A'),
1894        SUBPARTITION p_nc_good VALUES ('G')
1895    ),
1896    PARTITION p_southcentral VALUES ('OK', 'TX')
1897    (
1898        SUBPARTITION p_sc_bad VALUES ('B'),
1899        SUBPARTITION p_sc_average VALUES ('A'),
1900        SUBPARTITION p_sc_good VALUES ('G')
1901    )
1902);
1903
1904-- partitionare de tip referinta
1905CREATE TABLE orders_p
1906(
1907    order_id NUMBER(12),
1908    order_date DATE,
1909    order_mode VARCHAR2(8),
1910    customer_id NUMBER(6),
1911    order_status NUMBER(2),
1912    order_total NUMBER(8, 2),
1913    sales_rep_id NUMBER(6),
1914    promotion_id NUMBER(6),
1915    CONSTRAINT orders_pk PRIMARY KEY(order_id)
1916)
1917PARTITION BY RANGE(order_date)
1918(
1919    PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
1920    PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
1921    PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
1922    PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
1923);
1924
1925CREATE TABLE orderItems_c
1926(
1927    order_id NUMBER(12) NOT NULL,
1928    line_item_id NUMBER(3) NOT NULL,
1929    product_id NUMBER(6) NOT NULL,
1930    unit_price NUMBER(8, 2),
1931    quantity NUMBER(8),
1932    CONSTRAINT order_items_fk
1933    FOREIGN KEY(order_id) REFERENCES orders_p(order_id)
1934)
1935PARTITION BY REFERENCE(order_items_fk);
1936
1937-- indecsi partitionati
1938CREATE TABLE invoices
1939(
1940    invoice_no NUMBER,
1941    invoice_date DATE,
1942    client_id NUMBER,
1943    amount NUMBER(8, 2)
1944)
1945PARTITION BY RANGE (invoice_date)
1946(
1947    PARTITION Q1_2001 VALUES LESS THAN (TO_DATE('01-APR-2001','DD-MON-YYYY')),
1948    PARTITION Q2_2001 VALUES LESS THAN (TO_DATE('01-JUL-2001','DD-MON-YYYY')),
1949    PARTITION Q3_2001 VALUES LESS THAN (TO_DATE('01-OCT-2001','DD-MON-YYYY')),
1950    PARTITION Q4_2001 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY'))
1951);
1952
1953INSERT INTO invoices VALUES (1, '05-MAR-2001', 100, 560.3);
1954INSERT INTO invoices VALUES (2, '20-APR-2001', 104, 100.55);
1955INSERT INTO invoices VALUES (3, '11-DEC-2001', 101, 1400);
1956INSERT INTO invoices VALUES (4, '01-JAN-2001', 101, 54.7);
1957INSERT INTO invoices VALUES (5, '03-JUL-2001', 106, 14834);
1958INSERT INTO invoices VALUES (6, '14-FEB-2001', 100, 4345);
1959INSERT INTO invoices VALUES (7, '09-OCT-2001', 102, 233.32);
1960INSERT INTO invoices VALUES (8, '30-AUG-2001', 105, 5784.57);
1961INSERT INTO invoices VALUES (9, '16-MAY-2001', 109, 9802.12);
1962INSERT INTO invoices VALUES (10, '12-JUN-2001', 101, 924);
1963INSERT INTO invoices VALUES (11, '02-SEP-2001', 103, 3249);
1964
1965-- index local prefixat
1966CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;
1967
1968CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL
1969(
1970    PARTITION invoices_q1 TABLESPACE example,
1971    PARTITION invoices_q2 TABLESPACE example,
1972    PARTITION invoices_q3 TABLESPACE example,
1973    PARTITION invoices_q4 TABLESPACE example
1974);
1975
1976-- index local ne-prefixat
1977CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL
1978(
1979    PARTITION invoices_q1 TABLESPACE example,
1980    PARTITION invoices_q2 TABLESPACE example,
1981    PARTITION invoices_q3 TABLESPACE example,
1982    PARTITION invoices_q4 TABLESPACE example
1983);
1984
1985-- index global prefixat
1986CREATE INDEX invoices_idx ON invoices (invoice_date) GLOBAL
1987PARTITION BY RANGE (invoice_date)
1988(
1989    PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE example,
1990    PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE example,
1991    PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE example,
1992    PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE example
1993);
1994
1995-- adaugarea partitiilor
1996-- partitionare range
1997ALTER TABLE sales
1998ADD PARTITION jan96 VALUES LESS THAN ('01-FEB-1999') TABLESPACE example;
1999
2000-- partitionare hash
2001ALTER TABLE scubagear
2002ADD PARTITION p_named TABLESPACE example;
2003
2004-- partitionare lista
2005ALTER TABLE q1_sales_by_region
2006ADD PARTITION q1_nonmainland VALUES ('HI', 'PR');
2007
2008-- partitionare range-hash
2009ALTER TABLE sales 
2010ADD PARTITION q1_2000 VALUES LESS THAN (2000, 04, 01)
2011SUBPARTITIONS 8 STORE IN example;
2012
2013-- adaugare partitie la tabela partitionata range-list
2014ALTER TABLE quarterly_regional_sales
2015ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
2016STORAGE (INITIAL 20K NEXT 20K) TABLESPACE example NOLOGGING
2017(
2018    SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
2019    SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
2020    SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
2021    SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
2022    SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
2023    SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
2024);
2025
2026-- adaugare subpartitie la o tabela partitionata range-list
2027ALTER TABLE quarterly_regional_sales
2028MODIFY PARTITION q1_1999
2029ADD SUBPARTITION q1_1999_south VALUES ('AR','MS','AL') TABLESPACE example;
2030
2031-- stergerea partitiilor
2032ALTER TABLE sales 
2033DROP PARTITION dec98;
2034
2035ALTER INDEX sales_area_ix 
2036REBUILD;
2037
2038DELETE 
2039FROM sales 
2040WHERE TRANSID < 10000;
2041
2042ALTER TABLE sales
2043DROP PARTITION dec98;
2044
2045ALTER TABLE sales
2046DROP PARTITION dec98
2047UPDATE GLOBAL INDEXES;
2048
2049ALTER TABLE sales
2050DISABLE CONSTRAINT dname_sales1;
2051
2052ALTER TABLE sales
2053DROP PARTITTION dec98;
2054
2055ALTER TABLE sales
2056ENABLE CONSTRAINT dname_sales1;
2057
2058-- unirea partitiilor
2059-- pentru partitionarea hash (COALESCE):
2060ALTER TABLE diving
2061MODIFY PARTITION us_locations
2062COALESCE SUBPARTITION;
2063
2064-- pentru partitionarile range si list (MERGE):
2065CREATE TABLE four_seasons
2066(
2067    one DATE,
2068    two VARCHAR2(60),
2069    three NUMBER
2070)
2071PARTITION BY RANGE (one)
2072(
2073    PARTITION quarter_one
2074        VALUES LESS THAN (TO_DATE('01-apr-1998','dd-mon-yyyy'))
2075        TABLESPACE quarter_one,
2076    PARTITION quarter_two
2077        VALUES LESS THAN (TO_DATE('01-jul-1998','dd-mon-yyyy'))
2078        TABLESPACE quarter_two,
2079    PARTITION quarter_three
2080        VALUES LESS THAN (TO_DATE('01-oct-1998','dd-mon-yyyy'))
2081        TABLESPACE quarter_three,
2082    PARTITION quarter_four
2083        VALUES LESS THAN (TO_DATE('01-jan-1999','dd-mon-yyyy'))
2084        TABLESPACE quarter_four
2085);
2086--
2087-- Create local PREFIXED index on Four_Seasons
2088-- Prefixed because the leftmost columns of the index match the
2089-- Partition key
2090--
2091CREATE INDEX i_four_seasons_l ON four_seasons (one, two) LOCAL
2092(
2093    PARTITION i_quarter_one TABLESPACE i_quarter_one,
2094    PARTITION i_quarter_two TABLESPACE i_quarter_two,
2095    PARTITION i_quarter_three TABLESPACE i_quarter_three,
2096    PARTITION i_quarter_four TABLESPACE i_quarter_four
2097);
2098
2099-- pentru partitionare range:
2100ALTER TABLE four_seasons
2101MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;
2102
2103ALTER TABLE four_seasons 
2104MODIFY PARTITION quarter_two
2105REBUILD UNUSABLE LOCAL INDEXES;
2106
2107-- pentru partitionare list:
2108ALTER TABLE q1_sales_by_region
2109MERGE PARTITIONS q1_northcentral, q1_southcentral INTO PARTITION q1_central
2110PCTFREE 50 STORAGE(MAXEXTENTS 20);
2111
2112-- pentru partitionare range-list:
2113ALTER TABLE stripe_regional_sales
2114MERGE PARTITIONS q1_1999, q2_1999 INTO PARTITION q1_q2_1999;
2115
2116/* EXERCITII */
2117-- 2
2118-- Creati doua tabele partitionate de tip referinta, astfel:
2119-- - tabela parinte COMANDA, partitionata pe interval (“range”), trimestrial pe anul 2015, dupa coloana
2120-- data_comanda, avand coloanele: idcomanda, data_comanda, idclient, totalval, data_livare, idagent,
2121-- tipvanzare.
2122-- - tabela copil ITEM, partitionata referinta (“reference”) avand coloanele: idcomanda, iditem, idprodus,
2123-- cant, pret.
2124-- Inserati date de test in cele doua tabele. Afisati primele 3 produse vandute cel mai bine (valoric), in
2125-- trimestrul al doilea al anului 2015.
2126-- Creati indexi locali si/sau globali necesari pentru optimizarea timpilor de raspuns al interogarii.
2127
2128-- tabela parinte COMANDA
2129CREATE TABLE COMANDA (
2130    idcomanda NUMBER,
2131    data_comanda DATE,
2132    idclient NUMBER NOT NULL,
2133    totalval NUMBER,
2134    data_livrare DATE,
2135    idagent NUMBER NOT NULL,
2136    tipvanzare VARCHAR2(16),
2137    CONSTRAINT PK_idcomanda PRIMARY KEY(idcomanda)
2138)
2139PARTITION BY RANGE(data_comanda) -- partitionata dupa interval
2140(
2141    PARTITION trimestrul1
2142        VALUES LESS THAN (TO_DATE('01-apr-2015','dd-mon-yyyy')),
2143    PARTITION trimestrul2
2144        VALUES LESS THAN (TO_DATE('01-jul-2015','dd-mon-yyyy')),
2145    PARTITION trimestrul3
2146        VALUES LESS THAN (TO_DATE('01-oct-2015','dd-mon-yyyy')),
2147    PARTITION trimestrul4
2148        VALUES LESS THAN (TO_DATE('01-jan-2016','dd-mon-yyyy')),
2149    PARTITION altele
2150        VALUES LESS THAN (MAXVALUE)
2151);
2152
2153-- tabela copil ITEM
2154CREATE TABLE ITEM (
2155    idcomanda NUMBER NOT NULL,
2156    iditem NUMBER NOT NULL,
2157    idprodus NUMBER NOT NULL,
2158    cant NUMBER,
2159    pret NUMBER,
2160    CONSTRAINT FK_item_order FOREIGN KEY(idcomanda) REFERENCES comanda(idcomanda)
2161)
2162PARTITION BY REFERENCE(FK_item_order); -- partitionata dupa referinta
2163
2164-- inseram date de test in cele 2 tabele
2165INSERT INTO comanda
2166VALUES (1, TO_DATE('02-feb-2015'), 1, 150, TO_DATE('10-feb-2015'), 1, 'directa');
2167INSERT INTO comanda
2168VALUES (2, TO_DATE('20-may-2015'), 2, 660, TO_DATE('21-may-2015'), 3, 'internet');
2169INSERT INTO comanda
2170VALUES (3, TO_DATE('05-sep-2015'), 3, 30, TO_DATE('07-sep-2015'), 3, 'directa');
2171INSERT INTO comanda
2172VALUES (4, TO_DATE('30-dec-2015'), 3, 1000, TO_DATE('03-jan-2016'), 2, 'directa');
2173INSERT INTO comanda
2174VALUES (5, TO_DATE('22-jun-2015'), 4, 700, TO_DATE('25-jun-2015'), 2, 'internet');
2175INSERT INTO comanda
2176VALUES (6, TO_DATE('28-mar-2015'), 4, 80, TO_DATE('03-apr-2015'), 1, 'telefonic');
2177
2178INSERT INTO item
2179VALUES (1, 100, 101, 2, 50);
2180INSERT INTO item
2181VALUES (1, 102, 103, 1, 50);
2182INSERT INTO item
2183VALUES (2, 110, 105, 3, 100);
2184INSERT INTO item
2185VALUES (2, 100, 101, 2, 50);
2186INSERT INTO item
2187VALUES (2, 103, 150, 2, 130);
2188INSERT INTO item
2189VALUES (3, 104, 110, 1, 30);
2190INSERT INTO item
2191VALUES (4, 114, 115, 1, 500);
2192INSERT INTO item
2193VALUES (4, 109, 116, 5, 100);
2194INSERT INTO item
2195VALUES (5, 111, 113, 1, 700);
2196INSERT INTO item
2197VALUES (6, 100, 101, 1, 50);
2198INSERT INTO item
2199VALUES (6, 115, 117, 2, 15);
2200
2201COMMIT;
2202
2203-- afisam primele 3 produse vandute cel mai bine in trimestrul al doilea al anului 2015
2204SELECT *
2205FROM
2206    (SELECT i.idprodus, 
2207        RANK() OVER (ORDER BY i.pret * i.cant DESC) AS "Indice clasament",
2208        i.pret * i.cant AS "Valoare produs"
2209    FROM comanda c JOIN item i ON (c.idcomanda = i.idcomanda)
2210    WHERE c.data_comanda BETWEEN TO_DATE('01-apr-2015','dd-mon-yyyy') 
2211        AND TO_DATE('01-jul-2015','dd-mon-yyyy') -- in trimestrul al 2-lea
2212    )
2213WHERE "Indice clasament" <= 3; -- vrem doar primele 3 produse
2214-- doar dintr-un SELECT extern alias-ul "Indice clasament" este vizibil pentru filtrare
2215
2216-- optimizam timpii de raspuns ai interogarii prin crearea de indecsi
2217
2218-- index-ul este prefixat, pentru ca in interogarea de mai sus, cheia de partitionare este in clauza WHERE
2219-- si atunci se foloseste partition pruning
2220CREATE INDEX comandaidx_data_comanda
2221ON comanda(data_comanda) LOCAL -- index-ul va fi local pentru ca data comenzii este filtrata pe trimestre
2222(
2223    PARTITION trimestrul1,
2224    PARTITION trimestrul2,
2225    PARTITION trimestrul3,
2226    PARTITION trimestrul4,
2227    PARTITION altele
2228);
2229
2230-- 3
2231-- Creati o tabela partitionata dupa interval (“range”), care sa stocheze vanzarile unui lant de magazine
2232-- in intreaga tara, incepand cu anul 2010 pana in 2016. Tabela VANZARI are coloanele: idmagazin,
2233-- idprodus, data, idclient, valoare. Partitionarea de tip range se face semestrial pe coloana data.
2234-- Inserati date de test in tabela de vanzari. Creati indexi locali si/sau globali necesari.
2235-- *cream tabela VANZARI
2236
2237CREATE TABLE VANZARI (
2238    idmagazin NUMBER PRIMARY KEY,
2239    idprodus NUMBER NOT NULL,
2240    data DATE,
2241    idclient NUMBER NOT NULL,
2242    valoare NUMBER
2243)
2244PARTITION BY RANGE(data) -- partitionare dupa interval
2245(
2246    PARTITION s1_2010 VALUES LESS THAN (TO_DATE('01-jul-2010', 'dd-mon-yyyy')),
2247    PARTITION s2_2010 VALUES LESS THAN (TO_DATE('01-jan-2011', 'dd-mon-yyyy')),
2248    PARTITION s1_2011 VALUES LESS THAN (TO_DATE('01-jul-2011', 'dd-mon-yyyy')),
2249    PARTITION s2_2011 VALUES LESS THAN (TO_DATE('01-jan-2012', 'dd-mon-yyyy')),
2250    PARTITION s1_2012 VALUES LESS THAN (TO_DATE('01-jul-2012', 'dd-mon-yyyy')),
2251    PARTITION s2_2012 VALUES LESS THAN (TO_DATE('01-jan-2013', 'dd-mon-yyyy')),
2252    PARTITION s1_2013 VALUES LESS THAN (TO_DATE('01-jul-2013', 'dd-mon-yyyy')),
2253    PARTITION s2_2013 VALUES LESS THAN (TO_DATE('01-jan-2014', 'dd-mon-yyyy')),
2254    PARTITION s1_2014 VALUES LESS THAN (TO_DATE('01-jul-2014', 'dd-mon-yyyy')),
2255    PARTITION s2_2014 VALUES LESS THAN (TO_DATE('01-jan-2015', 'dd-mon-yyyy')),
2256    PARTITION s1_2015 VALUES LESS THAN (TO_DATE('01-jul-2015', 'dd-mon-yyyy')),
2257    PARTITION s2_2015 VALUES LESS THAN (TO_DATE('01-jan-2016', 'dd-mon-yyyy')),
2258    PARTITION s1_2016 VALUES LESS THAN (TO_DATE('01-jul-2016', 'dd-mon-yyyy')),
2259    PARTITION s2_2016 VALUES LESS THAN (TO_DATE('01-jan-2017', 'dd-mon-yyyy'))
2260);
2261
2262-- inseram date de test
2263INSERT INTO vanzari
2264VALUES (1, 100, TO_DATE('04-mar-2012'), 10, 65);
2265INSERT INTO vanzari
2266VALUES (2, 101, TO_DATE('04-mar-2012'), 11, 190);
2267INSERT INTO vanzari
2268VALUES (3, 102, TO_DATE('04-mar-2012'), 12, 570);
2269INSERT INTO vanzari
2270VALUES (4, 103, TO_DATE('04-mar-2012'), 11, 90);
2271INSERT INTO vanzari
2272VALUES (5, 102, TO_DATE('04-mar-2012'), 15, 570);
2273
2274COMMIT;
2275
2276-- cream indecsii necesari
2277-- index-ul este prefixat, local
2278CREATE INDEX vanzariidx_data
2279ON vanzari(data) LOCAL -- index-ul va fi local pentru ca data comenzii este filtrata pe trimestre
2280(
2281    PARTITION s1_2010,
2282    PARTITION s2_2010,
2283    PARTITION s1_2011,
2284    PARTITION s2_2011,
2285    PARTITION s1_2012,
2286    PARTITION s2_2012,
2287    PARTITION s1_2013,
2288    PARTITION s2_2013,
2289    PARTITION s1_2014,
2290    PARTITION s2_2014,
2291    PARTITION s1_2015,
2292    PARTITION s2_2015,
2293    PARTITION s1_2016,
2294    PARTITION s2_2016
2295);
2296
2297-- 4
2298-- Creati tabela dimensiune PRODUS corespunzatoare tabelei VANZARI. Tabela contine coloanele:
2299-- idprodus, denprodus, furnizor. Tabela este partitionata hash dupa idprodus, in 4 partitii.
2300-- Inserati date de test in tabel. Creati indexi locali si/sau globali necesari.
2301-- *cream tabela dimensiune PRODUS
2302
2303CREATE TABLE PRODUS (
2304    idprodus NUMBER PRIMARY KEY,
2305    denprodus VARCHAR2(40),
2306    furnizor VARCHAR2(60)
2307)
2308PARTITION BY HASH(idprodus) -- partitionare hash dupa idprodus
2309PARTITIONS 4; -- in 4 partitii
2310
2311-- inseram date de test
2312INSERT INTO produs
2313VALUES (100, 'Pasta de dinti', 'Colgate');
2314INSERT INTO produs
2315VALUES (101, 'Paine feliata', 'Panagro');
2316INSERT INTO produs
2317VALUES (102, 'Tort de ciocolata', 'Magica');
2318INSERT INTO produs
2319VALUES (103, 'Laptop de gaming', 'Lenovo');
2320
2321COMMIT;
2322
2323-- cream indecsii necesari
2324CREATE UNIQUE INDEX produsidx_idprodus
2325ON produs(idprodus) LOCAL;
2326
2327-- 5
2328-- Creati tabela dimensiune MAGAZIN corespunzatoare tabelei VANZARI. Tabela contine coloanele:
2329-- idmagazin, denmagazin, sefmagazin, localitate, judet. Tabela este partitionata lista in urmatoarele
2330-- regiuni istorice: Moldova, Muntenia, Dobrogea, Oltenia, Ardeal, Banat-Crisana, Maramures. Pentru
2331-- fiecare regiune se iau in considerare 3 judete reprezentative.
2332-- Inserati date de test in tabel. Creati indexi locali si/sau globali necesari.
2333
2334CREATE TABLE MAGAZIN (
2335    idmagazin NUMBER PRIMARY KEY,
2336    denmagazin VARCHAR2(50),
2337    sefmagazin VARCHAR2(60),
2338    localitate VARCHAR2(40),
2339    judet VARCHAR2(40)
2340)
2341PARTITION BY LIST(judet) -- partitionare dupa lista
2342(
2343    PARTITION magazine_Moldova VALUES ('Botosani', 'Suceava', 'Iasi'),
2344    PARTITION magazine_Muntenia VALUES ('Ilfov', 'Teleorman', 'Giurgiu'),
2345    PARTITION magazine_Dobrogea VALUES ('Tulcea', 'Constanta'),
2346    PARTITION magazine_Oltenia VALUES ('Olt', 'Mehedinti', 'Gorj'),
2347    PARTITION magazine_Ardeal VALUES ('Brasov', 'Harghita', 'Mures'),
2348    PARTITION magazine_Banat_Crisana VALUES ('Timis', 'Arad', 'Caras-Severin'),
2349    PARTITION magazine_Maramures VALUES ('Satu Mare', 'Maramures')
2350);
2351
2352-- inseram date de test in tabel
2353INSERT INTO magazin
2354VALUES (1, 'Nordic2', 'Icsulescu', 'Dorohoi', 'Botosani');
2355INSERT INTO magazin
2356VALUES (2, 'Tudor Faliment', 'Nastasescu', 'Iasi', 'Iasi');
2357INSERT INTO magazin
2358VALUES (3, 'Altex', 'Altexescu', 'Constanta', 'Constanta');
2359INSERT INTO magazin
2360VALUES (4, 'Carrefour', 'Pixulescu', 'Olt', 'Olt');
2361INSERT INTO magazin
2362VALUES (5, 'DM', 'Un neamt smecher', 'Satu Mare', 'Satu Mare');
2363
2364COMMIT;
2365
2366-- cream indecsii necesari
2367CREATE BITMAP INDEX magazinidx_judet -- index de tip bitmap, pentru ca exista putine judete posibile
2368ON magazin(judet) LOCAL;
2369
2370
2371-- LAB 8 ---------------------------------------------------------------------------------------------------------------------------
2372------------------------------------------------------------------------------------------------------------------------------------
2373
2374/* Procesul de extragere de cunostinte din baze de date */
2375
2376/* EXERCITII */
2377-- 1
2378-- Conectati-va la schema DMxx. Extrageti clientii din CUSTOMERS intr-un tabel
2379-- CUST_EXT dupa urmatoarele criterii:
2380-- - clientii din US;
2381-- - se vor extrage urmatoarele coloane:
2382--    CUST_ID
2383--    CUST_GENDER
2384--    CUST_YEAR_OF_BIRTH
2385--    CUST_MARITAL_STATUS
2386--    CUST_CITY
2387--    CUST_STATE_PROVINCE
2388--    COUNTRY_ID
2389--    CUST_INCOME_LEVEL
2390--    CUST_CREDIT_LIMIT
2391
2392CREATE TABLE CUST_EXT
2393AS
2394    SELECT cust_id, cust_gender, cust_year_of_birth, cust_marital_status, cust_city,
2395        cust_state_province, country_id, cust_income_level, cust_credit_limit
2396    FROM customers
2397    WHERE country_id = 'US';
2398
2399-- 2
2400-- Creati tabelul CUST_DM cu urmatoarea structura:
2401--    CUST_ID
2402--    GENDER
2403--    AGE
2404--    MARITAL_STATUS
2405--    CITY
2406--    STATE_PROVINCE
2407--    COUNTRY_ID
2408--    INCOME_ID
2409--    CREDIT_LIMIT
2410-- Se populeaza cu datele din CUST_EXT si se respecta urmatoarele reguli:
2411-- - toate valorile null se inlocuiesc cu ‘?’ sau cu -1;
2412-- - valorile pentru AGE se calculeaza;
2413-- INCOME_ID este un ID cu valori intre A si L care refera spre tabelul nou creat
2414-- INCOME_LEVEL cu structura: INCOME_ID, LIM_INF,LIM_SUP.
2415
2416CREATE TABLE CUST_DM (
2417    cust_id NUMBER NOT NULL,
2418    gender CHAR(1),
2419    age NUMBER,
2420    marital_status VARCHAR2(20),
2421    city VARCHAR2(30),
2422    state_province VARCHAR2(40),
2423    country_id CHAR(2),
2424    income_id CHAR(1),
2425    credit_limit NUMBER
2426    );
2427
2428-- cream structura tabelului income_level
2429CREATE TABLE income_level (
2430    income_id CHAR(1) PRIMARY KEY,
2431    lim_inf NUMBER,
2432    lim_sup NUMBER
2433);
2434
2435-- populam tabelul income_level folosind datele din tabelul "customers" (SELECT-ul este dat de prof)
2436-- folosim PL/SQL
2437DECLARE
2438    CURSOR c_income_level -- cursorul care retine datele extrase pentru a fi puse in tabelul "income_level"
2439    IS
2440        SELECT DISTINCT lev, levmin, levmax
2441        FROM
2442        (
2443            SELECT 
2444              SUBSTR(c.cust_income_level, 1, INSTR(c.cust_income_level, ':', 1, 1) -1) lev,
2445              (
2446                CASE INSTR(c.cust_income_level, 'Below')
2447                  WHEN 4 THEN 0 ELSE
2448                     ( CASE INSTR(c.cust_income_level ,'and above')
2449                          WHEN 0 THEN to_number(TRIM(SUBSTR(c.cust_income_level, INSTR(c.cust_income_level, ':', 1, 1) + 1, INSTR(c.cust_income_level, '-', 1, 1) - 3)), '999,999')
2450                                 ELSE to_number(TRIM(SUBSTR(c.cust_income_level, INSTR(c.cust_income_level, ':', 1, 1) + 1, INSTR(c.cust_income_level, 'and', 1, 1) - 3)), '999,999')
2451                       END )          
2452                END ) levmin,
2453                (CASE INSTR(c.cust_income_level, 'and above')
2454                          WHEN 0 THEN 
2455                                ( CASE INSTR(c.cust_income_level ,'Below')
2456                                    WHEN 4 THEN to_number(TRIM(SUBSTR(c.cust_income_level , 4 + LENGTH('Below'))),'999,999') - 1
2457                                    ELSE
2458                                      to_number(TRIM(SUBSTR(c.cust_income_level, INSTR(c.cust_income_level, '-', 1, 1) +1)),'999,999')
2459                                   END) 
2460                                 ELSE 999999 
2461                          END       
2462                ) levmax    
2463            FROM customers c
2464            WHERE country_id = 'US' AND c.cust_income_level IS NOT NULL
2465            ORDER BY c.cust_income_level
2466        )
2467        ORDER BY levmin;
2468BEGIN
2469    FOR income_level_record IN c_income_level -- iteram peste inregistrari
2470    LOOP
2471        INSERT INTO income_level
2472        VALUES (income_level_record.lev, income_level_record.levmin, income_level_record.levmax);
2473    END LOOP;
2474END;
2475
2476-- verificam tabela income_level
2477SELECT *
2478FROM income_level;
2479
2480-- acum extragem datele necesare din tabelul "cust_ext", le prelucram, si le inseram in tabelul "cust_dm"
2481-- folosim tot PL/SQL
2482DECLARE
2483    CURSOR c_cust_ext -- cursor ce contine toate inregistrarile din tabelul cust_ext
2484    IS
2485        SELECT *
2486        FROM cust_ext;
2487BEGIN
2488    FOR cust_record IN c_cust_ext -- iteram peste inregistrari
2489    LOOP
2490        INSERT INTO cust_dm -- inseram date procesate in tabelul cust_dm
2491        VALUES (cust_record.cust_id,
2492            NVL(cust_record.cust_gender, '?'), -- valorile NULL se inlocuiesc cu '?'
2493            DECODE(NVL(cust_record.cust_year_of_birth, 0), 0, -1, EXTRACT(YEAR FROM SYSDATE) - cust_record.cust_year_of_birth),
2494            NVL(cust_record.cust_marital_status, '?'),
2495            NVL(cust_record.cust_city, '?'),
2496            NVL(cust_record.cust_state_province, '?'),
2497            NVL(cust_record.country_id, '?'),
2498            SUBSTR(cust_record.cust_income_level, 1, 1), -- luam doar prima litera din "cust_income_level", aceasta este ID-ul catre tabelul "income_level"
2499            NVL(cust_record.cust_credit_limit, -1)); -- valorile NULL se inlocuiesc cu -1
2500    END LOOP;
2501END;
2502
2503-- setam "income_id" ca si cheie straina
2504ALTER TABLE cust_dm
2505ADD CONSTRAINT FK_income_id FOREIGN KEY (income_id) REFERENCES income_level(income_id);
2506
2507-- verificam tabelul "cust_dm"
2508SELECT *
2509FROM cust_dm;
2510
2511-- 3
2512-- Creati si populati tabelul CUST_DM_BIN cu valorile discretizate pentru urmatoarele
2513-- atribute:
2514-- GENDER, INCOME_ID, MARITAL_STATUS - dupa valorile existente
2515-- AGE – dupa intervalele 0-24, 25-39, 40-59, 60-70, peste 80
2516-- STATE_PROVINCE – dupa grupuri de cate 5 provincii, ordonate alfabetic
2517-- CREDIT_LIMIT – stabiliti 10 limite astfel incat valorile sa fie distribuite
2518-- echidistant
2519-- *cream structura discretizata a tabelului final
2520
2521CREATE TABLE CUST_DM_BIN (
2522    gender NUMBER,
2523    income_id NUMBER,
2524    marital_status NUMBER,
2525    age NUMBER,
2526    state_province NUMBER,
2527    credit_limit NUMBER
2528);
2529
2530-- cream tabele de discretizare
2531CREATE TABLE cust_gender_vals (
2532    gender_val CHAR(1),
2533    disc_val NUMBER
2534);
2535
2536CREATE TABLE cust_income_id_vals (
2537    income_id_val CHAR(1),
2538    disc_val NUMBER
2539);
2540
2541CREATE TABLE cust_marital_status_vals (
2542    marital_status_val VARCHAR2(20),
2543    disc_val NUMBER
2544);
2545
2546CREATE TABLE cust_age_vals (
2547    age_loval NUMBER,
2548    age_hival NUMBER,
2549    disc_val NUMBER
2550);
2551
2552CREATE TABLE cust_state_province_vals (
2553    state_province_val VARCHAR2(40),
2554    disc_val NUMBER
2555);
2556
2557CREATE TABLE cust_credit_limit_vals (
2558    credit_limit_lo NUMBER,
2559    credit_limit_hi NUMBER,
2560    disc_val NUMBER
2561);
2562
2563-- populam tabelele de discretizare
2564DECLARE
2565    CURSOR c_gender_vals
2566    IS
2567        SELECT DISTINCT(gender)
2568        FROM cust_dm;
2569    
2570    CURSOR c_income_id_vals
2571    IS
2572        SELECT DISTINCT(income_id)
2573        FROM cust_dm
2574        ORDER BY income_id ASC;
2575    
2576    CURSOR c_marital_status_vals
2577    IS
2578        SELECT DISTINCT(marital_status)
2579        FROM cust_dm
2580        ORDER BY marital_status ASC;
2581    
2582    CURSOR c_state_province_vals
2583    IS
2584        SELECT DISTINCT(state_province)
2585        FROM cust_dm
2586        ORDER BY state_province ASC;
2587    
2588    v_index NUMBER; -- indicele de BIN (numarul discretizat)
2589    
2590    v_crt_nr_of_provinces NUMBER; -- numarul de provincii dintr-un grup curent
2591    
2592    v_credit_limit_min NUMBER; -- minimul si maximul limitei de creditare (pentru discretizare echidistanta)
2593    v_credit_limit_max NUMBER;
2594    v_credit_limit_delta NUMBER; -- diferenta intre intervalele echidistante
2595    v_credit_limit_lo NUMBER; -- intervalele de discretizare
2596    v_credit_limit_hi NUMBER;
2597BEGIN
2598    -- pentru gender
2599    v_index := 1;
2600    FOR gender_record IN c_gender_vals
2601    LOOP
2602        INSERT INTO cust_gender_vals
2603        VALUES (gender_record.gender, v_index);
2604        v_index := v_index + 1;
2605    END LOOP;
2606    
2607    -- pentru income_id
2608    v_index := 1;
2609    FOR income_id_record IN c_income_id_vals
2610    LOOP
2611        INSERT INTO cust_income_id_vals
2612        VALUES (income_id_record.income_id, v_index);
2613        v_index := v_index + 1;
2614    END LOOP;
2615    
2616    -- pentru marital_status
2617    v_index := 1;
2618    FOR marital_status_record IN c_marital_status_vals
2619    LOOP
2620        INSERT INTO cust_marital_status_vals
2621        VALUES (marital_status_record.marital_status, v_index);
2622        v_index := v_index + 1;
2623    END LOOP;
2624    
2625    -- pentru age
2626    -- folosim intervalele date in enunt
2627    INSERT INTO cust_age_vals
2628    VALUES (0, 24, 1);
2629    INSERT INTO cust_age_vals
2630    VALUES (25, 39, 2);
2631    INSERT INTO cust_age_vals
2632    VALUES (40, 59, 3);
2633    INSERT INTO cust_age_vals
2634    VALUES (60, 70, 4);
2635    INSERT INTO cust_age_vals
2636    VALUES (71, 80, 5);
2637    INSERT INTO cust_age_vals
2638    VALUES (81, NULL, 6);
2639    
2640    -- pentru state_province
2641    v_index := 1;
2642    v_crt_nr_of_provinces := 0;
2643    FOR state_province_record IN c_state_province_vals
2644    LOOP
2645        IF v_crt_nr_of_provinces = 5 -- stocam valorile discrete in grupuri de cate 5
2646            THEN v_crt_nr_of_provinces := 0; v_index := v_index + 1;
2647        END IF; -- adica 1 valoare discreta corespunde la 5 valori efective, ordonate alfabetic
2648        INSERT INTO cust_state_province_vals
2649        VALUES (state_province_record.state_province, v_index);
2650        v_crt_nr_of_provinces := v_crt_nr_of_provinces + 1;
2651    END LOOP;
2652    
2653    -- pentru credit_limit
2654    v_index := 1;
2655    
2656    -- preluam minimul si maximul
2657    SELECT MAX(credit_limit)
2658    INTO v_credit_limit_max
2659    FROM cust_dm;
2660    
2661    SELECT MIN(credit_limit)
2662    INTO v_credit_limit_min
2663    FROM cust_dm;
2664    
2665    -- calculam intervalul de discretizare
2666    v_credit_limit_delta := (v_credit_limit_max - v_credit_limit_min) / 10; -- se doresc 10 intervale
2667
2668    v_credit_limit_lo := v_credit_limit_min;
2669    v_credit_limit_hi := v_credit_limit_lo + v_credit_limit_delta;
2670    
2671    WHILE v_credit_limit_hi <= v_credit_limit_max
2672    LOOP
2673        INSERT INTO cust_credit_limit_vals
2674        VALUES (v_credit_limit_lo, v_credit_limit_hi, v_index);
2675        v_index := v_index + 1;
2676        
2677        v_credit_limit_lo := v_credit_limit_hi;
2678        v_credit_limit_hi := v_credit_limit_hi + v_credit_limit_delta;
2679    END LOOP;
2680END;
2681
2682-- verificam tabelele de discretizare
2683SELECT *
2684FROM cust_gender_vals;
2685
2686SELECT *
2687FROM cust_income_id_vals;
2688
2689SELECT *
2690FROM cust_marital_status_vals;
2691
2692SELECT *
2693FROM cust_age_vals;
2694
2695SELECT *
2696FROM cust_state_province_vals;
2697
2698SELECT *
2699FROM cust_credit_limit_vals;
2700
2701-- acum extragem datele necesare din tabelul "cust_dm" si le discretizam conform tabelelor de discretizare
2702-- folosim tot PL/SQL
2703DECLARE
2704    CURSOR c_cust_dm -- cursor ce contine toate inregistrarile din tabelul cust_dm
2705    IS
2706        SELECT *
2707        FROM cust_dm;
2708    
2709    -- variabilele folosite la discretizare
2710    v_gender NUMBER;
2711    v_income_id NUMBER;
2712    v_marital_status NUMBER;
2713    v_age NUMBER;
2714    v_state_province NUMBER;
2715    v_credit_limit NUMBER;
2716BEGIN
2717    FOR cust_record IN c_cust_dm -- iteram peste inregistrari
2718    LOOP
2719        -- preluam valoarea discretizata pentru gender
2720        SELECT disc_val
2721        INTO v_gender
2722        FROM cust_gender_vals
2723        WHERE gender_val = cust_record.gender;
2724        
2725        -- preluam valoarea discretizata pentru income_id
2726        SELECT disc_val
2727        INTO v_income_id
2728        FROM cust_income_id_vals
2729        WHERE income_id_val = cust_record.income_id;
2730        
2731        -- preluam valoarea discretizata pentru marital_status
2732        SELECT disc_val
2733        INTO v_marital_status
2734        FROM cust_marital_status_vals
2735        WHERE marital_status_val = cust_record.marital_status;
2736        
2737        -- preluam valoarea discretizata pentru age
2738        SELECT disc_val
2739        INTO v_age
2740        FROM cust_age_vals
2741        WHERE cust_record.age BETWEEN age_loval AND NVL(age_hival, 1000);
2742        
2743        -- preluam valoarea discretizata pentru state_province
2744        SELECT disc_val
2745        INTO v_state_province
2746        FROM cust_state_province_vals
2747        WHERE state_province_val = cust_record.state_province;
2748        
2749        -- preluam valoarea discretizata pentru credit_limit
2750        SELECT disc_val
2751        INTO v_credit_limit
2752        FROM cust_credit_limit_vals
2753        WHERE cust_record.credit_limit BETWEEN credit_limit_lo AND credit_limit_hi;
2754        
2755        -- acum inseram toate valorile discretizate sub forma de inregistrare in tabelul final
2756        INSERT INTO cust_dm_bin
2757        VALUES (
2758            v_gender, v_income_id, v_marital_status, v_age, v_state_province, v_credit_limit
2759        );
2760    END LOOP;
2761END;
2762
2763-- verificam tabelul final
2764SELECT *
2765FROM cust_dm_bin;