· 8 years ago · May 06, 2018, 06:02 PM
1/* LABORATOR 1*/
2-- 1
3SELECT job, grade, ROUND(AVG(sal), 2), MIN(sal), MAX(sal), SUM(sal)
4FROM emp JOIN salgrade
5ON emp.sal BETWEEN losal AND hisal -- gradul de salarizare = non-equijoin intre tabelele "emp" si "salgrade"
6GROUP BY ROLLUP(job, grade); -- calculul de subtotaluri -> ROLLUP()
7
8-- 2
9SELECT job, grade, ROUND(AVG(sal), 2), MIN(sal), MAX(sal), SUM(sal)
10FROM emp JOIN salgrade
11ON emp.sal BETWEEN losal AND hisal
12GROUP BY CUBE(job, grade); -- subtotaluri intre toate combinatiile de cele 2 dimensiuni = in loc de ROLLUP() folosim CUBE()
13
14-- 3
15SELECT e2.ename AS manager, e1.job, SUM(e1.sal), AVG(e1.sal), MIN(e1.sal), MAX(e1.sal)
16FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno -- pentru a afla managerul, facem self-join cu tabela "emp"
17GROUP BY ROLLUP(e2.ename, e1.job); -- e2.ename = manager
18
19-- 4
20-- pentru a inlocui valorile de NULL folosim functia NVL()
21SELECT NVL(e2.ename, 'Total ename') AS manager, NVL(e1.job, 'Total job') AS job,
22 SUM(e1.sal), AVG(e1.sal), MIN(e1.sal), MAX(e1.sal)
23FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno
24GROUP BY ROLLUP(e2.ename, e1.job);
25
26-- 5
27SELECT NVL(d.dname, 'Total') AS dname, NVL(e1.job, 'Total') AS job,
28 NVL(e2.ename, 'Total') AS manager, NVL(TO_CHAR(s.grade), 'Total') AS grade,
29 SUM(e1.sal),
30 GROUPING_ID(d.dname, e1.job, e2.ename, s.grade) AS nivel_agregare -- functia GROUPING_ID() ne da nivelul de agregare
31FROM emp e1 JOIN emp e2 ON e1.mgr = e2.empno -- SELF JOIN intre emp si emp (pentru a afla manager-ul)
32 JOIN dept d ON e1.deptno = d.deptno -- EQUIJOIN intre emp si dept
33 JOIN salgrade s ON e1.sal BETWEEN losal AND hisal -- aflarea gradului de salarizare: NON-EQUIJOIN intre emp si salgrade
34GROUP BY GROUPING SETS(ROLLUP(d.dname, e1.job), ROLLUP(e2.ename, s.grade))
35HAVING GROUP_ID() = 0 -- evitarea duplicatelor -> eliminam inregistrarile cu GROUP_ID() nenul
36ORDER BY GROUPING_ID(d.dname, e1.job, e2.ename, s.grade) ASC;
37
38-- 6
39SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
40FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
41 JOIN times t ON (s.time_id = t.time_id)
42 JOIN customers cust ON (s.cust_id = cust.cust_id) -- JOIN cu tabela "customers" ca sa putem afla "country_id"
43 JOIN countries c ON (cust.country_id = c.country_id)
44 -- sales = tabela de fapte, restul sunt tabele de dimensiune
45WHERE ch.channel_desc IN ('Direct Sales', 'Internet') -- aplicam filtrarile cerute in enunt
46 AND t.calendar_month_desc IN ('2000-09', '2000-10')
47 AND cust.country_id IN ('UK', 'US')
48GROUP BY ROLLUP(ch.channel_desc, t.calendar_month_desc, cust.country_id); -- subtotalurile pe coloanele cerute
49
50-- 7
51SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
52FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
53 JOIN times t ON (s.time_id = t.time_id)
54 JOIN customers cust ON (s.cust_id = cust.cust_id)
55 JOIN countries c ON (cust.country_id = c.country_id)
56WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
57 AND t.calendar_month_desc IN ('2000-09', '2000-10')
58 AND cust.country_id IN ('UK', 'US')
59GROUP BY ch.channel_desc, ROLLUP(t.calendar_month_desc, cust.country_id);
60-- singura modificare este in GROUP BY, unde scoatem campul "channel_desc" din ROLLUP(), pentru a obtine gruparea partiala
61
62-- 8
63SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
64FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
65 JOIN times t ON (s.time_id = t.time_id)
66 JOIN customers cust ON (s.cust_id = cust.cust_id)
67 JOIN countries c ON (cust.country_id = c.country_id)
68WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
69 AND t.calendar_month_desc IN ('2000-09', '2000-10')
70 AND cust.country_id IN ('UK', 'US')
71GROUP BY CUBE(ch.channel_desc, t.calendar_month_desc, cust.country_id);
72-- modificare fata de problema 6: folosim CUBE() in loc de ROLLUP(), pentru ca vrem subtotaluri pentru toate combinatiile de dimensiuni
73
74SELECT NVL(ch.channel_desc, 'All channels'), NVL(cust.country_id, 'All countries'), SUM(s.amount_sold)
75-- valorile NULL se trateaza cu NVL()
76FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
77 JOIN times t ON (s.time_id = t.time_id)
78 JOIN customers cust ON (s.cust_id = cust.cust_id)
79 JOIN countries c ON (cust.country_id = c.country_id)
80WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
81 AND t.calendar_month_desc IN ('2000-09', '2000-10')
82 AND cust.country_id IN ('UK', 'US')
83GROUP BY CUBE(ch.channel_desc, cust.country_id);
84-- nu mai luam in considerare luna in calculul subtotalurilor (stergem si din CUBE() si din SELECT)
85
86-- 9
87SELECT NVL(ch.channel_desc, 'All channels'), NVL(cust.country_id, 'All countries'), SUM(s.amount_sold)
88FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
89 JOIN times t ON (s.time_id = t.time_id)
90 JOIN customers cust ON (s.cust_id = cust.cust_id)
91 JOIN countries c ON (cust.country_id = c.country_id)
92WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
93 AND t.calendar_month_desc IN ('2000-09', '2000-10')
94 AND cust.country_id IN ('UK', 'US')
95GROUP BY ROLLUP(cust.country_id, ch.channel_desc); -- doar totalurile pe tara, respectiv pe canal (ROLLUP() + campuri inversate fata de problema 8)
96
97-- 10
98SELECT ch.channel_desc, t.calendar_month_desc, cust.country_id, SUM(s.amount_sold)
99FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
100 JOIN times t ON (s.time_id = t.time_id)
101 JOIN customers cust ON (s.cust_id = cust.cust_id)
102 JOIN countries c ON (cust.country_id = c.country_id)
103WHERE ch.channel_desc IN ('Direct Sales', 'Internet')
104 AND t.calendar_month_desc IN ('2000-09', '2000-10')
105 AND cust.country_id IN ('UK', 'US')
106 -- in loc de ROLLUP() folosim GROUPING SETS pentru subtotaluri pe liste de grupuri specificate
107GROUP BY GROUPING SETS((ch.channel_desc, t.calendar_month_desc, cust.country_id),
108 (ch.channel_desc, cust.country_id),
109 (t.calendar_month_desc, cust.country_id));
110
111
112
113/* LABORATOR 2 */ ----------------------------------------------------------------------------------------------------
114-- 1
115SELECT d.dname, e.ename, e.hiredate, e.job,
116 RANK() OVER (ORDER BY e.hiredate ASC) clasament -- pozitia se afla ordonand dupa data angajarii si calculand "RANK()"-ul
117FROM emp e JOIN dept d ON e.deptno = d.deptno;
118
119-- 2
120SELECT deptno AS "DeptAvgSal", job AS "JobAvgSal",
121 ROUND(AVG(sal), 2) AS "AvgSal",
122 RANK() OVER (PARTITION BY deptno ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_dept -- pozitia in cadrul departamentului
123 -- folosim "PARTITION BY deptno" pentru a face clasamentul mediilor salariale pe departamente, si nu global
124 -- adica "pozitie_dept" este relativa la numarul departamentului = campul DeptAvgSal
125FROM emp
126GROUP BY ROLLUP(deptno, job); -- linii totalizatoare pentru departament si job
127
128-- 3
129SELECT deptno AS "DeptAvgSal", job AS "JobAvgSal",
130 ROUND(AVG(sal), 2) AS "AvgSal",
131 RANK() OVER (PARTITION BY deptno ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_dept,
132 RANK() OVER (PARTITION BY job ORDER BY ROUND(AVG(sal), 2) DESC) pozitie_job -- pozitia in cadrul job-ului
133 -- partitionam dupa job, iar pentru fiecare "job" fixat, se ordoneaza mediile salariale si se calculeaza pozitia in ierarhie
134FROM emp
135GROUP BY ROLLUP(deptno, job);
136
137-- 4
138SELECT *
139FROM (SELECT DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit,
140 -- ordonam dupa venit (salariu + comision) si calculam DENSE_RANK(), si nu RANK()
141 -- pentru ca vrem ca duplicatele sa fie tratate ca aflandu-se pe acelasi nivel ierarhic
142 -- (SCOTT si FORD sunt pe acelasi loc, nu ocupa 2 locuri diferite in ierarhie)
143 ename, sal, comm, sal + NVL(comm, 0) * sal AS "Venit"
144 FROM emp)
145-- dorim primele 10 inregistrari: folosim o subinterogare, din care preluam tot, cu conditia ca pozitia calculata sa fie <= 10
146-- 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!
147WHERE pozitie_venit <= 10;
148
149-- 5
150SELECT *
151FROM (SELECT DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit,
152 ROUND(PERCENT_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC), 2) distributie_venit,
153 -- distributia veniturilor = functia PERCENT_RANK() in loc de RANK() / DENSE_RANK()
154 ename, sal, comm, sal + NVL(comm, 0) * sal AS "Venit"
155 FROM emp)
156WHERE pozitie_venit <= 10;
157
158-- 6
159SELECT NTILE(4) OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) nr_crt, -- numarul buchetului
160 -- impartim in 4 buchete folosind functia NTILE()
161 DENSE_RANK() OVER (ORDER BY sal + NVL(comm, 0) * sal DESC) pozitie_venit, -- pozitia nivelului de venit
162 ename, sal, sal + NVL(comm, 0) * sal AS "Venit"
163FROM emp;
164
165-- 7
166SELECT ch.channel_desc, SUM(s.amount_sold) AS "Vanzari canal de distributie",
167 RANK() OVER(ORDER BY SUM(s.amount_sold) DESC) pozitie
168FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
169 -- JOIN-uri pentru a afla informatiile suplimentare necesare la filtrare si afisare
170 JOIN times t ON (s.time_id = t.time_id)
171 JOIN customers cust ON (s.cust_id = cust.cust_id)
172WHERE t.calendar_month_desc IN ('2000-09', '2000-10') -- filtrarile din enunt
173 AND cust.country_id = 'US'
174GROUP BY ch.channel_desc; -- vanzarile trebuie grupate pe canale de distributie
175
176-- 8
177SELECT ch.channel_desc, t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
178 RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_canal_distributie
179 -- partitionam dupa canalul de distributie, apoi pentru fiecare canal fixat, ordonam dupa valoarea vanzarilor ca sa aflam pozitia relativa
180FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
181 JOIN times t ON (s.time_id = t.time_id)
182WHERE t.calendar_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
183GROUP BY ch.channel_desc, t.calendar_month_desc;
184
185-- 9
186SELECT ch.channel_desc, t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
187 RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_canal_distributie,
188 RANK() OVER(PARTITION BY t.calendar_month_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_luna
189 -- pozitia in cadrul lunii = partitionam dupa luna si ordonam partitiile rezultate dupa valoarea vanzarilor, pentru a afla pozitia relativa din luna respectiva
190FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
191 JOIN times t ON (s.time_id = t.time_id)
192WHERE t.calendar_month_desc IN ('2000-08', '2000-09', '2000-10', '2000-11')
193GROUP BY ch.channel_desc, t.calendar_month_desc;
194
195-- 10
196SELECT ch.channel_desc, cust.country_id, SUM(s.amount_sold) AS "Total vanzari",
197 RANK() OVER(PARTITION BY ch.channel_desc ORDER BY SUM(s.amount_sold) DESC) pozitie_grup
198 -- pozitionare in cadrul canalului de distributie = facem partitionare dupa canalul de distributie
199 -- si pentru fiecare inregistrare pentru un canal fixat se calculeaza rangul in acea partitie
200FROM sales s JOIN channels ch ON (s.channel_id = ch.channel_id)
201 JOIN times t ON (s.time_id = t.time_id)
202 JOIN customers cust ON (s.cust_id = cust.cust_id)
203WHERE ch.channel_desc IN ('Direct Sales', 'Internet') -- filtrarile cerute
204 AND t.calendar_month_desc = '2000-09'
205 AND cust.country_id IN ('UK', 'US', 'JP')
206GROUP BY CUBE(ch.channel_desc, cust.country_id);
207 -- totalul vanzarilor pe canalul de distributie si pe tara + linii de subtotaluri pentru combinarea celor 2 dimensiuni
208 -- CUBE() in loc de ROLLUP() pentru ca trebuie toate combinatiile intre dimensiuni
209
210-- 11
211SELECT *
212FROM (
213 SELECT cust.country_id, SUM(s.amount_sold) AS "Total vanzari",
214 RANK() OVER(ORDER BY SUM(s.amount_sold) DESC) pozitie
215 -- top vanzari = ordonare descrescatoare in functie de suma valorilor vanzarilor
216 -- apoi calcul de rang pe aceasta ordonare
217 FROM sales s JOIN times t ON (s.time_id = t.time_id)
218 JOIN customers cust ON (s.cust_id = cust.cust_id)
219 WHERE t.calendar_month_desc = '2000-09'
220 GROUP BY cust.country_id -- trebuie grupate rezultatele pentru ca folosim functia agregat SUM(),
221 -- iar afisarea trebuie facuta in functie de tara
222 -- din interogarea interioara rezulta o lista cu toate tarile ordonate dupa vanzari
223 -- nu putem afisa doar primele 5 inregistrari in acest SELECT, deoarece alias-ul "pozitie" nu este vizibil in acelasi
224 -- SELECT in care este creat
225)
226-- se face un SELECT exterior din tabelul rezultat, din care selectam toate coloanele si filtram dupa coloana alias "pozitie",
227-- care devine vizibila de aceasta data
228WHERE pozitie <= 5;
229
230-- 12
231SELECT t.calendar_month_desc, SUM(s.amount_sold) AS "Total vanzari",
232 NTILE(4) OVER (ORDER BY SUM(s.amount_sold) DESC) pozitie_buchet
233 -- impartire in 4 buchete = functia NTILE(), dupa suma vanzarilor = ordonare dupa SUM(...)
234FROM sales s JOIN times t ON (s.time_id = t.time_id)
235 JOIN products p ON (s.prod_id = p.prod_id)
236WHERE t.calendar_year = '1999' -- filtrarile cerute
237 AND p.prod_category = 'Men'
238GROUP BY t.calendar_month_desc; -- se grupeaza din acelasi motiv ca la 11, folosim o functie agregat SUM()
239
240
241
242
243/* LABORATOR 3 */ -----------------------------------------------------------------------------------------------------------------
244-- 1
245SELECT ename, hiredate, sal,
246 SUM(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH FOLLOWING) AS sumsal_1_month,
247 -- pentru ca intervalul e lunar si nu numeric, se foloseste "RANGE BETWEEN INTERVAL x DAY / MONTH / YEAR
248 -- offset logic = RANGE
249 FIRST_VALUE(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND INTERVAL '6' MONTH FOLLOWING) AS maxsal,
250 -- valoarea maxima din setul de inregistrari din fereastra = FIRST_VALUE()
251 LAST_VALUE(sal) OVER (ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH PRECEDING AND INTERVAL '6' MONTH FOLLOWING) AS minsal
252 -- valoarea minima din setul de inregistrari din fereastra = LAST_VALUE()
253FROM emp;
254
255-- 2
256SELECT deptno, ename, sal,
257 SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS UNBOUNDED PRECEDING) AS sumsal_dept -- cu offset fizic = ROWS
258 -- pana la angajatul curent = de la inceput (UNBOUNDED PRECEDING), pana la inregistrarea curent, implicit
259FROM emp;
260
261SELECT deptno, ename, sal,
262 SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE UNBOUNDED PRECEDING) AS sumsal_dept -- cu offset logic = RANGE
263FROM emp;
264
265-- 3
266SELECT job, ename, sal, NVL(comm, 0) AS comision, sal + NVL(comm, 0) * sal AS venit_total,
267 SUM(sal + NVL(comm, 0) * sal) OVER(PARTITION BY job ORDER BY sal ROWS UNBOUNDED PRECEDING) AS sumsal_job -- cu offset fizic = ROWS
268FROM emp;
269
270SELECT job, ename, sal, NVL(comm, 0) AS comision, sal + NVL(comm, 0) * sal AS venit_total,
271 SUM(sal + NVL(comm, 0) * sal) OVER(PARTITION BY job ORDER BY sal RANGE UNBOUNDED PRECEDING) AS sumsal_job -- cu offset logic = RANGE
272FROM emp;
273
274-- 4
275CREATE OR REPLACE FUNCTION fn(dno NUMBER) RETURN NUMBER -- functia fn() din laborator
276IS
277 Result NUMBER;
278 res NUMBER;
279BEGIN
280 SELECT COUNT(*) - 1
281 INTO res
282 FROM emp
283 WHERE deptno = dno;
284
285 Result := res;
286 RETURN(Result);
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 Result := 0;
291END fn;
292
293SELECT deptno, ename, sal, fn(deptno),
294 SUM(sal) OVER (ORDER BY deptno, sal ROWS BETWEEN CURRENT ROW AND fn(deptno) FOLLOWING) AS sumsal_dept
295 -- ROWS = offset fizic, nu folosim offset logic pentru ca in enunt se specifica "linia curenta" si nu "valoarea curenta"
296 -- CURRENT ROW = linia curenta
297 -- fn(deptno) FOLLOWING = urmatoarele x linii, x = valoarea intoarsa de fn()
298FROM emp;
299
300-- schimbam situatia pentru partitionare pe job, modificam functia fn()
301CREATE OR REPLACE FUNCTION fnjob(v_job emp.job%TYPE) RETURN NUMBER
302IS
303 Result NUMBER;
304 res NUMBER;
305BEGIN
306 SELECT COUNT(*) - 1
307 INTO res
308 FROM emp
309 WHERE job = v_job;
310
311 Result := res;
312 RETURN(Result);
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 Result := 0;
317END fnjob;
318
319SELECT job, ename, sal, fnjob(job),
320 SUM(sal) OVER (ORDER BY job, sal ROWS BETWEEN CURRENT ROW AND fnjob(job) FOLLOWING) AS sumsal_job
321 -- acelasi lucru, doar schimbam deptno cu job
322FROM emp;
323
324-- 5
325SELECT job, ename, sal AS "Salariu curent",
326 LAG(sal, 1) OVER (PARTITION BY job ORDER BY sal) AS "Salariu anterior",
327 -- LAG(sal, 1) = valoarea campului "sal" de la "1" inregistrari anterioare inregistrarii curente
328 LEAD(sal, 1) OVER (PARTITION BY job ORDER BY sal) AS "Salariu urmator"
329 -- LEAD(sal, 1) = valoarea campului "sal" de la "1" inregistrari posterioare inregistrarii curente
330FROM emp;
331
332-- 6
333SELECT COUNT(empno) AS "Numar total angajati",
334 SUM(CASE WHEN sal BETWEEN 0 AND 1000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 0 si 1000",
335 SUM(CASE WHEN sal BETWEEN 1000 AND 2000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 1000 si 2000",
336 SUM(CASE WHEN sal BETWEEN 2000 AND 3000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul intre 2000 si 3000",
337 SUM(CASE WHEN sal > 3000 THEN 1 ELSE 0 END) AS "Numar de angajati cu salariul peste 3000"
338 -- SUM(CASE...) numara cate valori ale campului "sal" se incadreaza in conditiile din CASE WHEN
339FROM emp;
340
341-- 7
342SELECT cust.cust_first_name AS "Client", t.calendar_quarter_number AS "Trimestru",
343 SUM(s.amount_sold) AS "Suma vanzarilor pe trimestrul curent",
344 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"
345 -- partitionarea se face in functie de client, deoarece clientul este folosit ca referinta in calcule: client X a vandut cantitatea Y in trimestrul Z
346 -- pentru un client fixat, se ordoneaza inregistrarile dupa trimestru, pentru ca se calculeaza suma vanzarilor pe trimestre
347 -- ROWS UNBOUNDED PRECEDING = inregistrarile anterioare, de la inceput pana la cea curenta (offset fizic)
348
349 -- explicatia pentru SUM(SUM(...)): ni se cere sa afisam suma vanzarilor anterioare, pentru fiecare trimestru afisat
350 -- vanzarile pentru un anumit trimestru se obtin prin a calcula SUM(amount_sold), deci prin a suma tranzactiile efectuate de un anumit client
351 -- deci, suma vanzarilor anterioare se calculeaza prin adunarea tuturor vanzarilor trimestrelor anterioare, deci prin a suma sumele facute anterior, deci SUM(SUM(...))
352FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
353 JOIN times t ON (s.time_id = t.time_id)
354WHERE s.cust_id IN (6380, 6510) -- filtrarile cerute
355 AND t.calendar_year = 1999
356GROUP BY cust.cust_first_name, t.calendar_quarter_number -- grupare si ordonare dupa cum se cere in enunt, pentru ca folosim functia agregat SUM()
357ORDER BY cust.cust_first_name, t.calendar_quarter_number;
358
359-- 8
360SELECT cust.cust_first_name, t.calendar_month_number,
361 SUM(s.amount_sold) AS "Suma vanzari pe luna",
362 ROUND(AVG(SUM(s.amount_sold)) OVER (ORDER BY t.calendar_month_number RANGE 2 PRECEDING), 2) AS "Media vanzarilor anterioare"
363 -- ordonarea se face dupa luna, iar fereastra cuprinde si 2 luni anterioare -> atentie, 2 luni, nu 2 inregistrari!
364 -- deci se iau in calcul 2 valori anterioare, de aceea folosim offset logic (RANGE) si nu fizic (ROWS)
365 -- 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
366
367 -- AVG(SUM(...)) -> pentru ca se cere media vanzarilor anterioare, deci media unei sume calculate anterior, de aici AVG(SUM(...))
368FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
369 JOIN times t ON (s.time_id = t.time_id)
370WHERE s.cust_id = 6380
371 AND t.calendar_year = 1999
372GROUP BY cust.cust_first_name, t.calendar_month_number
373ORDER BY cust.cust_first_name, t.calendar_month_number;
374
375-- 9
376SELECT cust.cust_first_name, t.time_id,
377 SUM(s.amount_sold) AS "Suma vanzari pe zi",
378 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"
379 -- aceeasi logica precum la 7 si la 8 -> media vanzarilor pe o fereastra de 3 zile = medie de suma
380 -- offset logic din acelasi motiv, zilele se pot repeta in inregistrari si trebuie luate in considerare
381 -- la ferestre cu date calendaristice se foloseste BETWEEN INTERVAL
382FROM sales s JOIN customers cust ON (s.cust_id = cust.cust_id)
383 JOIN times t ON (s.time_id = t.time_id)
384WHERE s.cust_id IN (6380, 6510)
385 AND t.calendar_year = 1999
386 AND t.calendar_week_number = 51
387GROUP BY cust.cust_first_name, t.time_id
388ORDER BY cust.cust_first_name, t.time_id;
389
390-- 10
391SELECT t.day_number_in_month, SUM(s.amount_sold) AS "Suma vanzarilor pe ziua curenta",
392 LAG(SUM(s.amount_sold), 1) OVER (ORDER BY SUM(s.amount_sold)) AS "Suma vanzarilor pentru linia anterioara",
393 -- pentru valoarea de pe linia anterioara, folosim functia LAG() peste setul ordonat de inregistrari dupa suma vanzarilor
394 LEAD(SUM(s.amount_sold), 1) OVER (ORDER BY SUM(s.amount_sold)) AS "Suma vanzarilor pentru linia urmatoare"
395 -- pentru valoarea de pe linia anterioara, folosim functia LEAD() peste setul ordonat de inregistrari dupa suma vanzarilor
396FROM sales s JOIN times t ON (s.time_id = t.time_id)
397WHERE t.calendar_month_desc = '2000-10'
398 AND t.day_number_in_month BETWEEN 10 AND 15 -- intre 10 si 15 octombrie 2000
399GROUP BY t.day_number_in_month;
400
401
402
403
404/* LABORATOR 4 */ -----------------------------------------------------------------------------------------------------------------
405-- 1
406-- mai intai cream structura tabelului si datele puse folosind un SELECT cu 2 JOIN-uri
407CREATE TABLE orditem
408AS
409 SELECT o.custid, p.prodid, o.orderdate, o.commplan, o.shipdate, i.qty, i.actualprice, i.itemtot
410 FROM ord o JOIN item i ON (o.ordid = i.ordid)
411 JOIN product p ON (i.prodid = p.prodid);
412
413-- constrangerile de chei straine nu sunt pastrate, asa incat le cream manual
414ALTER TABLE orditem
415ADD CONSTRAINT fk_custid FOREIGN KEY (custid) REFERENCES customer(custid);
416ALTER TABLE orditem
417ADD CONSTRAINT fk_prodid FOREIGN KEY (prodid) REFERENCES product(prodid);
418
419-- trebuie sa cream o cheie primara pe acest tabel
420-- incepem cu o secventa (pentru autoincrement)
421CREATE SEQUENCE orditem_seq;
422
423-- adaugam un camp ID
424ALTER TABLE orditem
425ADD id NUMBER;
426
427-- pe care il facem cheie primara
428ALTER TABLE orditem
429ADD CONSTRAINT pk_orditem PRIMARY KEY(id);
430
431-- pentru fiecare inregistrare existenta, setam valoarea cheii primare
432DECLARE
433 v_orditem_record orditem%ROWTYPE; -- tipul de data inregistrare din tabelul orditem
434
435 CURSOR c_orditem IS -- acest cursor va tine toate inregistrarile din orditem
436 SELECT *
437 FROM orditem;
438BEGIN
439 FOR orditem_rec IN c_orditem -- iteram prin cursor
440 LOOP
441 UPDATE orditem -- si actualizam campul nou creat cu valoarea data de secventa
442 SET id = orditem_seq.NEXTVAL
443 WHERE custid = orditem_rec.custid
444 AND prodid = orditem_rec.prodid
445 AND NVL(commplan, 0) = NVL(orditem_rec.commplan, 0) -- posibilitate de NULL!
446 AND shipdate = orditem_rec.shipdate
447 AND qty = orditem_rec.qty
448 AND actualprice = orditem_rec.actualprice
449 AND itemtot = orditem_rec.itemtot;
450 END LOOP;
451END;
452
453-- 2
454CREATE MATERIALIZED VIEW
455 LOG
456 ON product
457 WITH PRIMARY KEY, ROWID
458 INCLUDING NEW VALUES;
459
460CREATE MATERIALIZED VIEW
461 LOG
462 ON customer
463 WITH PRIMARY KEY, ROWID
464 INCLUDING NEW VALUES;
465
466CREATE MATERIALIZED VIEW
467 LOG
468 ON orditem
469 WITH PRIMARY KEY, ROWID
470 INCLUDING NEW VALUES;
471
472-- 3
473CREATE MATERIALIZED VIEW order_info_mv
474 BUILD IMMEDIATE -- se populeaza imediat cu date
475 REFRESH FAST -- poate fi sincronizat rapid
476 ON COMMIT -- atunci cand se comite tranzactia
477 AS
478 SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
479 c.cust_id, c.cust_last_name, s.amount_sold,
480 s.quantity_sold, s.time_id
481 FROM sales s, times t, customers c
482 WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
483
484SELECT *
485FROM order_info_mv;
486
487-- inseram o comanda noua
488INSERT INTO sales
489VALUES (150, 100, '30-JUN-98', 'C', 123, 3, 1239.5);
490
491COMMIT;
492
493-- verificam ca a fost inserata in view-ul materializat
494SELECT *
495FROM order_info_mv
496WHERE cust_id = 100 AND time_id = '30-JUN-98' AND amount_sold = 3 AND quantity_sold = 1239.5;
497
498-- 4
499-- trebuie create view-urile materializate de tip LOG mai intai, pe tabelele ord si customer
500CREATE MATERIALIZED VIEW
501 LOG
502 ON ord
503 WITH PRIMARY KEY, ROWID
504 INCLUDING NEW VALUES;
505
506CREATE MATERIALIZED VIEW
507 LOG
508 ON customer
509 WITH PRIMARY KEY, ROWID
510 INCLUDING NEW VALUES;
511
512-- in view-ul materializat de tip JOIN trebuie incluse neaparat coloanele de tip "rowid" ale tabelelor implicate
513CREATE MATERIALIZED VIEW order_cust_info_mv
514 BUILD IMMEDIATE
515 REFRESH FAST
516 AS
517 SELECT c.rowid "customers_rid", o.rowid "ord_rid", o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
518 c.name, c.city, c.state, c.creditlimit
519 FROM ord o JOIN customer c ON (o.custid = c.custid);
520
521-- 5
522-- acest view se bazeaza pe cel creat la exercitiul 3 (order_info_mv)
523-- mai intai, trebuie creata o cheie primara pe view-ul materializat order_info_mv
524ALTER MATERIALIZED VIEW order_info_mv
525ADD CONSTRAINT PK_sales_times_cust PRIMARY KEY ("sales_rid", "times_rid", "customers_rid");
526
527-- apoi, un view materializat de tip LOG pe order_info_mv, care sa inregistreze ROWID-urile si cheia primara
528CREATE MATERIALIZED VIEW
529 LOG
530 ON order_info_mv
531 WITH ROWID, PRIMARY KEY
532 INCLUDING NEW VALUES;
533
534-- abia apoi se poate crea MV-ul de care avem nevoie
535CREATE MATERIALIZED VIEW total_sales_mv
536 BUILD IMMEDIATE
537 REFRESH FAST ON COMMIT
538 AS
539 -- trebuie sa se includa neaparat coloanele ce compun cheia primara!
540 -- puse intre ghilimele pt ca sunt case sensitive
541 SELECT "sales_rid", "times_rid", "customers_rid", quantity_sold, amount_sold
542 FROM order_info_mv;
543
544-- inseram o comanda noua
545INSERT INTO sales
546VALUES (150, 100, '01-JUN-98', 'C', 123, 4, 2259);
547
548COMMIT;
549
550-- verificam ca a fost inserata in view-ul materializat
551SELECT *
552FROM total_sales_mv
553WHERE amount_sold = 4 AND quantity_sold = 2259;
554
555-- 6
556CREATE MATERIALIZED VIEW total_sales_cust_mv
557 BUILD IMMEDIATE
558 REFRESH COMPLETE
559 AS
560 SELECT s.cust_id, SUM(s.amount_sold) AS "Total vanzari"
561 FROM sales s
562 GROUP BY s.cust_id;
563
564-- 7
565-- creare tabel initial
566CREATE TABLE sum_sales_months_tab
567 AS
568 SELECT t.calendar_month_desc,
569 SUM(s.amount_sold) AS "Total valoare vanzari",
570 SUM(s.quantity_sold) AS "Total cantitate vanduta"
571 FROM sales s JOIN times t ON (s.time_id = t.time_id)
572 GROUP BY t.calendar_month_desc;
573
574-- creare view materializat pre-inregistrat
575CREATE MATERIALIZED VIEW sum_sales_months_tab
576ON PREBUILT TABLE
577WITHOUT REDUCED PRECISION
578AS
579 SELECT t.calendar_month_desc,
580 SUM(s.amount_sold) AS "Total valoare vanzari",
581 SUM(s.quantity_sold) AS "Total cantitate vanduta"
582 FROM sales s JOIN times t ON (s.time_id = t.time_id)
583 GROUP BY t.calendar_month_desc;
584
585-- 8
586-- modificarea tipului de REFRESH
587ALTER MATERIALIZED VIEW total_sales_mv
588REFRESH FAST;
589
590-- fortarea urmatorului REFRESH
591ALTER MATERIALIZED VIEW sum_sales_tab
592REFRESH NEXT SYSDATE + 7;
593
594-- compilarea unui view materializat
595ALTER MATERIALIZED VIEW order_info_mv
596COMPILE;
597
598-- stergerea view-urilor materializate
599DROP MATERIALIZED VIEW detail_sales_mv;
600DROP MATERIALIZED VIEW order_cust_info_mv;
601DROP MATERIALIZED VIEW order_info_mv;
602DROP MATERIALIZED VIEW product_sales_mv;
603DROP MATERIALIZED VIEW sum_sales_months_tab;
604DROP MATERIALIZED VIEW total_sales_mv;
605
606
607
608
609
610/* LABORATOR 5 */ ----------------------------------------------------------------------------------------------------------------
611-- 1
612CREATE UNIQUE INDEX customeridx_name
613ON customer(name);
614
615CREATE INDEX customeridx_creditlimit
616ON customer(creditlimit); -- coloana ce contine valori neunice
617
618CREATE INDEX customeridx_repid
619ON customer(repid); -- coloana ce contine valori neunice
620
621CREATE INDEX customeridx_fn_name
622ON customer(LOWER(name));
623-- daca numele se introduc cu litere mici, se face index cu functia LOWER() pentru optimizare
624
625CREATE INDEX ordidx_custid
626ON ord(custid, -- pentru criteriul de JOIN cu tabela customer
627 shipdate); -- pentru filtrarea dupa data comenzii
628
629-- 3
630SELECT o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
631 c.name, c.city, c.state, c.creditlimit,
632 e.ename AS "NUME AGENT",
633 d.dname AS "NUME DEPARTAMENT",
634 p.descrip AS "NUME PRODUS",
635 i.qty, i.actualprice, i.itemtot
636FROM ord o JOIN customer c ON (o.custid = c.custid) -- pentru a prelua clientul
637 JOIN emp e ON (c.repid = e.empno) -- pentru a prelua agentul
638 JOIN dept d ON (e.deptno = d.deptno) -- pentru a prelua numele departamentului
639 JOIN item i ON (o.ordid = i.ordid) -- pentru a gasi ID-ul de produs pentru o anumita comanda
640 JOIN product p ON (i.prodid = p.prodid) -- pentru a prelua numele produsului
641WHERE o.shipdate BETWEEN TO_DATE('01/06/1986', 'DD/MM/YYYY') -- in lunile de vara ale anului 1986
642 AND TO_DATE('01/09/1986', 'DD/MM/YYYY');
643
644-- se creeaza indecsi pentru fiecare FOREIGN KEY, conform conditiilor de JOIN
645CREATE INDEX ordidx_custid
646ON ord(custid);
647
648CREATE INDEX customeridx_repid
649ON customer(repid);
650
651CREATE INDEX empidx_deptno
652ON emp(deptno);
653
654CREATE INDEX itemidx_ordid
655ON item(ordid);
656
657CREATE INDEX itemidx_prodid
658ON item(prodid);
659
660-- apoi se creeaza index pentru conditia din WHERE
661-- este index neunic, pentru ca data comenzii poate avea valori duplicate (2 comenzi in aceeasi zi)
662CREATE INDEX ordidx_shipdate
663ON ord(shipdate);
664
665-- afisam indecsii din baza de date
666SELECT * FROM USER_INDEXES idx, USER_IND_COLUMNS idxc
667WHERE idx.index_name = idxc.index_name
668 AND idx.index_name IN ('ORDIDX_CUSTID', 'CUSTOMERIDX_REPID', 'EMPIDX_DEPTNO',
669 'ITEMIDX_ORDID', 'IREMIDX_PRODID', 'ORDIDX_SHIPDATE');
670
671-- 4
672SELECT o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
673 c.name, c.city, c.state, c.creditlimit,
674 e.ename AS "NUME AGENT",
675 d.dname AS "NUME DEPARTAMENT",
676 p.descrip AS "NUME PRODUS",
677 i.qty, i.actualprice, i.itemtot
678FROM ord o JOIN customer c ON (o.custid = c.custid) -- pentru a prelua clientul
679 JOIN emp e ON (c.repid = e.empno) -- pentru a prelua agentul
680 JOIN dept d ON (e.deptno = d.deptno) -- pentru a prelua numele departamentului
681 JOIN item i ON (o.ordid = i.ordid) -- pentru a gasi ID-ul de produs pentru o anumita comanda
682 JOIN product p ON (i.prodid = p.prodid) -- pentru a prelua numele produsului
683WHERE o.shipdate BETWEEN TO_DATE('01/06/1986', 'DD/MM/YYYY') -- in lunile de vara ale anului 1986
684 AND TO_DATE('01/09/1986', 'DD/MM/YYYY')
685 AND i.qty * i.actualprice > 400; -- filtram si liniile de comanda cu valoare > 400
686
687-- pentru optimizarea accesului, cream un index de tip functie, cu formula calculului specificat in enunt
688CREATE INDEX itemidx_value
689ON item(qty * actualprice);
690
691-- 5
692-- mai intai cream structura tabelului si datele puse folosind un SELECT cu 2 JOIN-uri
693CREATE TABLE orditem
694AS
695 SELECT o.custid, p.prodid, o.orderdate, o.commplan, o.shipdate, i.qty, i.actualprice, i.itemtot
696 FROM ord o JOIN item i ON (o.ordid = i.ordid)
697 JOIN product p ON (i.prodid = p.prodid);
698
699-- constrangerile de chei straine nu sunt pastrate, asa incat le cream manual
700ALTER TABLE orditem
701ADD CONSTRAINT fk_custid FOREIGN KEY (custid) REFERENCES customer(custid);
702ALTER TABLE orditem
703ADD CONSTRAINT fk_prodid FOREIGN KEY (prodid) REFERENCES product(prodid);
704
705-- trebuie sa cream o cheie primara pe acest tabel
706-- incepem cu o secventa (pentru autoincrement)
707CREATE SEQUENCE orditem_seq;
708
709-- adaugam un camp ID
710ALTER TABLE orditem
711ADD id NUMBER;
712
713-- pe care il facem cheie primara
714ALTER TABLE orditem
715ADD CONSTRAINT pk_orditem PRIMARY KEY(id);
716
717-- pentru fiecare inregistrare existenta, setam valoarea cheii primare
718DECLARE
719 v_orditem_record orditem%ROWTYPE; -- tipul de data inregistrare din tabelul orditem
720
721 CURSOR c_orditem IS -- acest cursor va tine toate inregistrarile din orditem
722 SELECT *
723 FROM orditem;
724BEGIN
725 FOR orditem_rec IN c_orditem -- iteram prin cursor
726 LOOP
727 UPDATE orditem -- si actualizam campul nou creat cu valoarea data de secventa
728 SET id = orditem_seq.NEXTVAL
729 WHERE custid = orditem_rec.custid
730 AND prodid = orditem_rec.prodid
731 AND NVL(commplan, 0) = NVL(orditem_rec.commplan, 0) -- posibilitate de NULL!
732 AND shipdate = orditem_rec.shipdate
733 AND qty = orditem_rec.qty
734 AND actualprice = orditem_rec.actualprice
735 AND itemtot = orditem_rec.itemtot;
736 END LOOP;
737END;
738
739-- acum cream indecsii necesari:
740-- index unic compus pentru client, produs
741-- (adaugam si campul ID creat mai sus, pentru ca exista perechi (custid, prodid) duplicat)
742CREATE UNIQUE INDEX orditemidx_cust_prod
743ON orditem(custid, prodid, id);
744
745-- index bitmap pentru coloanele candidate (adica cele cu cardinalitate redusa)
746CREATE BITMAP INDEX orditemidx_commplan
747ON orditem(commplan);
748
749-- interogare care utilizeaza index-ul orditemidx_cust_prod
750SELECT custid, prodid, orderdate, shipdate
751FROM orditem
752WHERE custid = 106 AND (prodid = 100861 OR prodid = 101863) AND id < 14;
753
754-- interogare care utilizeaza index-ul orditemidx_commplan
755SELECT *
756FROM orditem
757WHERE commplan IN ('A', 'C');
758
759-- 6
760SELECT cu.cust_first_name, SUM(s.amount_sold) AS "Suma vanzarilor"
761FROM sales s JOIN customers cu ON (s.cust_id = cu.cust_id) -- pentru a afla numele clientului
762 JOIN countries co ON (cu.country_id = co.country_id) -- pentru a filtra in functie de cele 2 regiuni
763 JOIN times t ON (s.time_id = t.time_id) -- pentru a filtra in functie de trimestru si an
764WHERE t.calendar_quarter_desc = '1998-Q1' -- primul trimestru al anului 1998
765 AND co.country_subregion IN ('Western Europe', 'Southern America') -- clientii din Europa de vest si America de sud
766GROUP BY cu.cust_first_name; -- avem o functie de grup (SUM), trebuie grupate datele dupa clienti
767
768-- cream indecsii necesari:
769-- indecsi pentru coloanele de JOIN
770CREATE INDEX salesidx_cust_id
771ON sales(cust_id);
772
773CREATE UNIQUE INDEX customersidx_cust_id
774ON customers(cust_id);
775
776CREATE INDEX customersidx_country_id
777ON customers(country_id);
778
779CREATE UNIQUE INDEX countriesidx_country_id
780ON countries(country_id);
781
782CREATE INDEX salesidx_time_id
783ON sales(time_id);
784
785CREATE UNIQUE INDEX timesidx_time_id
786ON times(time_id);
787
788-- index de tip bitmap pentru subregiuni (coloana cu cardinalitate redusa)
789CREATE BITMAP INDEX countriesidx_country_subregion
790ON countries(country_subregion);
791
792-- index simplu, neunic pe trimestrele anilor (exista valori duplicat, iar cardinalitatea este destul de consistenta)
793CREATE INDEX timesidx_calendar_quarter_desc
794ON times(calendar_quarter_desc);
795
796
797
798
799/* LABORATOR 6 */ ---------------------------------------------------------------------------------------------------------------
800-- 2
801EXPLAIN PLAN FOR
802SELECT o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
803 c.name, c.city, c.state, c.creditlimit,
804 e.ename AS "NUME AGENT",
805 d.dname AS "NUME DEPARTAMENT",
806 p.descrip AS "NUME PRODUS",
807 i.qty, i.actualprice, i.itemtot
808FROM ord o JOIN customer c ON (o.custid = c.custid) -- pentru a prelua clientul
809 JOIN emp e ON (c.repid = e.empno) -- pentru a prelua agentul
810 JOIN dept d ON (e.deptno = d.deptno) -- pentru a prelua numele departamentului
811 JOIN item i ON (o.ordid = i.ordid) -- pentru a gasi ID-ul de produs pentru o anumita comanda
812 JOIN product p ON (i.prodid = p.prodid) -- pentru a prelua numele produsului
813WHERE o.shipdate BETWEEN TO_DATE('01/06/1986', 'DD/MM/YYYY') -- in lunile de vara ale anului 1986
814 AND TO_DATE('01/09/1986', 'DD/MM/YYYY');
815
816-- afisare plan sub forma de tabel
817SELECT plan_table_output
818FROM TABLE(dbms_xplan.display('plan_table', null, 'serial'));
819
820-- pentru optimizarea planului:
821-- se creeaza indecsi pentru fiecare FOREIGN KEY, conform conditiilor de JOIN
822CREATE INDEX ordidx_custid
823ON ord(custid);
824
825CREATE INDEX customeridx_repid
826ON customer(repid);
827
828CREATE INDEX empidx_deptno
829ON emp(deptno);
830
831CREATE INDEX itemidx_ordid
832ON item(ordid);
833
834CREATE INDEX itemidx_prodid
835ON item(prodid);
836
837-- apoi se creeaza index pentru conditia din WHERE
838CREATE INDEX ordidx_shipdate
839ON ord(shipdate);
840
841-- 3
842EXPLAIN PLAN FOR
843SELECT o.ordid, o.orderdate, o.commplan, o.custid, o.shipdate, o.total,
844 c.name, c.city, c.state, c.creditlimit,
845 e.ename AS "NUME AGENT",
846 d.dname AS "NUME DEPARTAMENT",
847 p.descrip AS "NUME PRODUS",
848 i.qty, i.actualprice, i.itemtot
849FROM ord o JOIN customer c ON (o.custid = c.custid) -- pentru a prelua clientul
850 JOIN emp e ON (c.repid = e.empno) -- pentru a prelua agentul
851 JOIN dept d ON (e.deptno = d.deptno) -- pentru a prelua numele departamentului
852 JOIN item i ON (o.ordid = i.ordid) -- pentru a gasi ID-ul de produs pentru o anumita comanda
853 JOIN product p ON (i.prodid = p.prodid) -- pentru a prelua numele produsului
854WHERE o.shipdate BETWEEN TO_DATE('01/06/1986', 'DD/MM/YYYY') -- in lunile de vara ale anului 1986
855 AND TO_DATE('01/09/1986', 'DD/MM/YYYY')
856 AND i.qty * i.actualprice > 400; -- filtram si liniile de comanda cu valoare > 400
857
858-- afisare plan sub forma de tabel
859SELECT plan_table_output
860FROM TABLE(dbms_xplan.display('plan_table', null, 'serial'));
861
862-- pentru optimizarea accesului, cream un index de tip functie, cu formula calculului specificat in enunt
863CREATE INDEX itemidx_value
864ON item(qty * actualprice);
865
866-- 4
867-- interogarea nr. 1
868SELECT ename, job, sal, dname
869FROM emp, dept
870WHERE dept.deptno = emp.deptno
871 AND NOT EXISTS (SELECT *
872 FROM salgrade
873 WHERE emp.sal BETWEEN losal AND hisal);
874
875-- afisare plan sub forma de tabel
876SELECT plan_table_output
877FROM TABLE(dbms_xplan.display('plan_table', null, 'serial'));
878
879-- pentru optimizare:
880CREATE INDEX sal_idx
881ON emp(sal);
882
883-- interogare dictata de prof
884SELECT *
885FROM sales s JOIN products p ON (s.prod_id = p.prod_id)
886 JOIN times t ON (s.time_id = t.time_id)
887WHERE p.prod_category = 'Men'
888 AND t.calendar_quarter_desc IN ('1998-Q1', '1998-Q2');
889
890-- pentru optimizare, cream index pentru cheile straine prod_id si time_id
891CREATE INDEX prod_id_idx
892ON sales(prod_id);
893
894CREATE INDEX time_id_idx
895ON sales(time_id);
896
897-- un index de tip bitmap pentru categoria de produse (pentru ca are cardinalitate mica)
898CREATE BITMAP INDEX productsidx_prod_category
899ON products(prod_category);
900
901-- si index pentru calendar_quarter_desc
902CREATE INDEX calendar_quarter_desc_idx
903ON times(calendar_quarter_desc);
904
905-- interogarea nr. 2
906SELECT ename, e.deptno, d.deptno, d.dname
907FROM emp e, dept d
908WHERE e.deptno = d.deptno AND ename like 'A%';
909
910-- optimizare:
911CREATE INDEX empidx_deptno
912ON emp(deptno);
913
914CREATE UNIQUE INDEX deptidx_deptno
915ON dept(deptno);
916
917CREATE INDEX empidx_ename
918ON emp(ename);
919
920-- interogarea nr. 3
921SELECT COUNT(*)
922FROM products p
923WHERE prod_list_price < 1.15 * (SELECT AVG(unit_cost)
924 FROM costs c
925 WHERE c.prod_id = p.prod_id);
926
927-- optimizare:
928CREATE INDEX costsidx_prod_id
929ON costs(prod_id);
930
931CREATE UNIQUE INDEX productsidx_prod_id
932ON products(prod_id);
933
934CREATE INDEX productsidx_prod_list_price
935ON products(prod_list_price);
936
937-- interogarea nr. 4
938SELECT COUNT(*)
939FROM products p, (SELECT prod_id, AVG(unit_cost) ac
940 FROM costs GROUP BY prod_id) c
941WHERE p.prod_id = c.prod_id
942 AND p.prod_list_price < 1.15 * c.ac;
943
944-- optimizare:
945-- index concatenat (pentru ca in WHERE sunt folosite 2 conditii cu coloane din acelasi tabel)
946CREATE INDEX productsidx_id_price
947ON products(prod_id, prod_list_price);
948
949-- apoi pentru tabela costs
950CREATE INDEX costsidx_prod_id
951ON costs(prod_id);
952
953-- interogarea nr. 5
954SELECT c.cust_first_name, c.cust_last_name, c.cust_id, COUNT(s.prod_id)
955FROM customers c, sales s
956WHERE c.cust_id = s.cust_id
957 AND c.cust_id < 100
958GROUP BY c.cust_first_name, c.cust_last_name, c.cust_id;
959
960-- optimizare: indecsi pentru conditiile din WHERE
961CREATE INDEX customersidx_cust_id
962ON customers(cust_id);
963
964CREATE INDEX salesidx_cust_id
965ON sales(cust_id);
966
967-- interogarea nr. 6
968SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
969FROM sales s, times t, customers c, channels ch
970WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
971 AND s.channel_id = ch.channel_id AND c.cust_state_province = 'CA' AND ch.channel_desc IN ('Internet','Catalog')
972 AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
973GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
974
975-- optimizare:
976-- index compus pentru perechea (time_id, cust_id, channel_id) din tabela sales, pentru ca apar impreuna in WHERE
977CREATE INDEX salesidx_time_cust_channel
978ON sales(time_id, cust_id, channel_id);
979
980-- index de tip bitmap pentru cust_state_province (are cardinalitate mica)
981CREATE BITMAP INDEX customersidx_cust_state_province
982ON customers(cust_state_province);
983
984-- asemenea pentru channel_desc
985CREATE BITMAP INDEX channelsidx_channel_desc
986ON channels(channel_desc);
987
988-- index pentru calendar_quarter_desc
989CREATE INDEX timesidx_calendar_quarter_desc
990ON times(calendar_quarter_desc);
991
992
993
994
995/* LABORATOR 8 */ -----------------------------------------------------------------------------------------------------------------
996-- 1
997CREATE TABLE CUST_EXT
998AS
999 SELECT cust_id, cust_gender, cust_year_of_birth, cust_marital_status, cust_city,
1000 cust_state_province, country_id, cust_income_level, cust_credit_limit
1001 FROM customers
1002 WHERE country_id = 'US';
1003
1004-- 2
1005-- cream tabelul CUST_DM
1006CREATE TABLE CUST_DM (
1007 cust_id NUMBER NOT NULL,
1008 gender CHAR(1),
1009 age NUMBER,
1010 marital_status VARCHAR2(20),
1011 city VARCHAR2(30),
1012 state_province VARCHAR2(40),
1013 country_id CHAR(2),
1014 income_id CHAR(1),
1015 credit_limit NUMBER
1016 );
1017
1018-- cream structura tabelului income_level
1019CREATE TABLE income_level (
1020 income_id CHAR(1) PRIMARY KEY,
1021 lim_inf NUMBER,
1022 lim_sup NUMBER
1023);
1024
1025-- populam tabelul income_level folosind datele din tabelul "customers" (SELECT-ul este dat de prof)
1026-- folosim PL/SQL
1027DECLARE
1028 CURSOR c_income_level -- cursorul care retine datele extrase pentru a fi puse in tabelul "income_level"
1029 IS
1030 SELECT DISTINCT lev, levmin, levmax
1031 FROM
1032 (
1033 SELECT
1034 SUBSTR(c.cust_income_level, 1, INSTR(c.cust_income_level, ':', 1, 1) -1) lev,
1035 (
1036 CASE INSTR(c.cust_income_level, 'Below')
1037 WHEN 4 THEN 0 ELSE
1038 ( CASE INSTR(c.cust_income_level ,'and above')
1039 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')
1040 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')
1041 END )
1042 END ) levmin,
1043 (CASE INSTR(c.cust_income_level, 'and above')
1044 WHEN 0 THEN
1045 ( CASE INSTR(c.cust_income_level ,'Below')
1046 WHEN 4 THEN to_number(TRIM(SUBSTR(c.cust_income_level , 4 + LENGTH('Below'))),'999,999') - 1
1047 ELSE
1048 to_number(TRIM(SUBSTR(c.cust_income_level, INSTR(c.cust_income_level, '-', 1, 1) +1)),'999,999')
1049 END)
1050 ELSE 999999
1051 END
1052 ) levmax
1053 FROM customers c
1054 WHERE country_id = 'US' AND c.cust_income_level IS NOT NULL
1055 ORDER BY c.cust_income_level
1056 )
1057 ORDER BY levmin;
1058BEGIN
1059 FOR income_level_record IN c_income_level -- iteram peste inregistrari
1060 LOOP
1061 INSERT INTO income_level
1062 VALUES (income_level_record.lev, income_level_record.levmin, income_level_record.levmax);
1063 END LOOP;
1064END;
1065
1066-- verificam tabela income_level
1067SELECT *
1068FROM income_level;
1069
1070-- acum extragem datele necesare din tabelul "cust_ext", le prelucram, si le inseram in tabelul "cust_dm"
1071-- folosim tot PL/SQL
1072DECLARE
1073 CURSOR c_cust_ext -- cursor ce contine toate inregistrarile din tabelul cust_ext
1074 IS
1075 SELECT *
1076 FROM cust_ext;
1077BEGIN
1078 FOR cust_record IN c_cust_ext -- iteram peste inregistrari
1079 LOOP
1080 INSERT INTO cust_dm -- inseram date procesate in tabelul cust_dm
1081 VALUES (cust_record.cust_id,
1082 NVL(cust_record.cust_gender, '?'), -- valorile NULL se inlocuiesc cu '?'
1083 DECODE(NVL(cust_record.cust_year_of_birth, 0), 0, -1, EXTRACT(YEAR FROM SYSDATE) - cust_record.cust_year_of_birth),
1084 NVL(cust_record.cust_marital_status, '?'),
1085 NVL(cust_record.cust_city, '?'),
1086 NVL(cust_record.cust_state_province, '?'),
1087 NVL(cust_record.country_id, '?'),
1088 SUBSTR(cust_record.cust_income_level, 1, 1), -- luam doar prima litera din "cust_income_level", aceasta este ID-ul catre tabelul "income_level"
1089 NVL(cust_record.cust_credit_limit, -1)); -- valorile NULL se inlocuiesc cu -1
1090 END LOOP;
1091END;
1092
1093-- setam "income_id" ca si cheie straina
1094ALTER TABLE cust_dm
1095ADD CONSTRAINT FK_income_id FOREIGN KEY (income_id) REFERENCES income_level(income_id);
1096
1097-- verificam tabelul "cust_dm"
1098SELECT *
1099FROM cust_dm;
1100
1101-- 3
1102-- cream structura discretizata a tabelului final
1103CREATE TABLE CUST_DM_BIN (
1104 gender NUMBER,
1105 income_id NUMBER,
1106 marital_status NUMBER,
1107 age NUMBER,
1108 state_province NUMBER,
1109 credit_limit NUMBER
1110);
1111
1112-- cream tabele de discretizare
1113CREATE TABLE cust_gender_vals (
1114 gender_val CHAR(1),
1115 disc_val NUMBER
1116);
1117
1118CREATE TABLE cust_income_id_vals (
1119 income_id_val CHAR(1),
1120 disc_val NUMBER
1121);
1122
1123CREATE TABLE cust_marital_status_vals (
1124 marital_status_val VARCHAR2(20),
1125 disc_val NUMBER
1126);
1127
1128CREATE TABLE cust_age_vals (
1129 age_loval NUMBER,
1130 age_hival NUMBER,
1131 disc_val NUMBER
1132);
1133
1134CREATE TABLE cust_state_province_vals (
1135 state_province_val VARCHAR2(40),
1136 disc_val NUMBER
1137);
1138
1139CREATE TABLE cust_credit_limit_vals (
1140 credit_limit_lo NUMBER,
1141 credit_limit_hi NUMBER,
1142 disc_val NUMBER
1143);
1144
1145-- populam tabelele de discretizare
1146DECLARE
1147 CURSOR c_gender_vals
1148 IS
1149 SELECT DISTINCT(gender)
1150 FROM cust_dm;
1151
1152 CURSOR c_income_id_vals
1153 IS
1154 SELECT DISTINCT(income_id)
1155 FROM cust_dm
1156 ORDER BY income_id ASC;
1157
1158 CURSOR c_marital_status_vals
1159 IS
1160 SELECT DISTINCT(marital_status)
1161 FROM cust_dm
1162 ORDER BY marital_status ASC;
1163
1164 CURSOR c_state_province_vals
1165 IS
1166 SELECT DISTINCT(state_province)
1167 FROM cust_dm
1168 ORDER BY state_province ASC;
1169
1170 v_index NUMBER; -- indicele de BIN (numarul discretizat)
1171
1172 v_crt_nr_of_provinces NUMBER; -- numarul de provincii dintr-un grup curent
1173
1174 v_credit_limit_min NUMBER; -- minimul si maximul limitei de creditare (pentru discretizare echidistanta)
1175 v_credit_limit_max NUMBER;
1176 v_credit_limit_delta NUMBER; -- diferenta intre intervalele echidistante
1177 v_credit_limit_lo NUMBER; -- intervalele de discretizare
1178 v_credit_limit_hi NUMBER;
1179BEGIN
1180 -- pentru gender
1181 v_index := 1;
1182 FOR gender_record IN c_gender_vals
1183 LOOP
1184 INSERT INTO cust_gender_vals
1185 VALUES (gender_record.gender, v_index);
1186 v_index := v_index + 1;
1187 END LOOP;
1188
1189 -- pentru income_id
1190 v_index := 1;
1191 FOR income_id_record IN c_income_id_vals
1192 LOOP
1193 INSERT INTO cust_income_id_vals
1194 VALUES (income_id_record.income_id, v_index);
1195 v_index := v_index + 1;
1196 END LOOP;
1197
1198 -- pentru marital_status
1199 v_index := 1;
1200 FOR marital_status_record IN c_marital_status_vals
1201 LOOP
1202 INSERT INTO cust_marital_status_vals
1203 VALUES (marital_status_record.marital_status, v_index);
1204 v_index := v_index + 1;
1205 END LOOP;
1206
1207 -- pentru age
1208 -- folosim intervalele date in enunt
1209 INSERT INTO cust_age_vals
1210 VALUES (0, 24, 1);
1211 INSERT INTO cust_age_vals
1212 VALUES (25, 39, 2);
1213 INSERT INTO cust_age_vals
1214 VALUES (40, 59, 3);
1215 INSERT INTO cust_age_vals
1216 VALUES (60, 70, 4);
1217 INSERT INTO cust_age_vals
1218 VALUES (71, 80, 5);
1219 INSERT INTO cust_age_vals
1220 VALUES (81, NULL, 6);
1221
1222 -- pentru state_province
1223 v_index := 1;
1224 v_crt_nr_of_provinces := 0;
1225 FOR state_province_record IN c_state_province_vals
1226 LOOP
1227 IF v_crt_nr_of_provinces = 5 -- stocam valorile discrete in grupuri de cate 5
1228 THEN v_crt_nr_of_provinces := 0; v_index := v_index + 1;
1229 END IF; -- adica 1 valoare discreta corespunde la 5 valori efective, ordonate alfabetic
1230 INSERT INTO cust_state_province_vals
1231 VALUES (state_province_record.state_province, v_index);
1232 v_crt_nr_of_provinces := v_crt_nr_of_provinces + 1;
1233 END LOOP;
1234
1235 -- pentru credit_limit
1236 v_index := 1;
1237
1238 -- preluam minimul si maximul
1239 SELECT MAX(credit_limit)
1240 INTO v_credit_limit_max
1241 FROM cust_dm;
1242
1243 SELECT MIN(credit_limit)
1244 INTO v_credit_limit_min
1245 FROM cust_dm;
1246
1247 -- calculam intervalul de discretizare
1248 v_credit_limit_delta := (v_credit_limit_max - v_credit_limit_min) / 10; -- se doresc 10 intervale
1249
1250 v_credit_limit_lo := v_credit_limit_min;
1251 v_credit_limit_hi := v_credit_limit_lo + v_credit_limit_delta;
1252
1253 WHILE v_credit_limit_hi <= v_credit_limit_max
1254 LOOP
1255 INSERT INTO cust_credit_limit_vals
1256 VALUES (v_credit_limit_lo, v_credit_limit_hi, v_index);
1257 v_index := v_index + 1;
1258
1259 v_credit_limit_lo := v_credit_limit_hi;
1260 v_credit_limit_hi := v_credit_limit_hi + v_credit_limit_delta;
1261 END LOOP;
1262END;
1263
1264-- verificam tabelele de discretizare
1265SELECT *
1266FROM cust_gender_vals;
1267
1268SELECT *
1269FROM cust_income_id_vals;
1270
1271SELECT *
1272FROM cust_marital_status_vals;
1273
1274SELECT *
1275FROM cust_age_vals;
1276
1277SELECT *
1278FROM cust_state_province_vals;
1279
1280SELECT *
1281FROM cust_credit_limit_vals;
1282
1283-- acum extragem datele necesare din tabelul "cust_dm" si le discretizam conform tabelelor de discretizare
1284-- folosim tot PL/SQL
1285DECLARE
1286 CURSOR c_cust_dm -- cursor ce contine toate inregistrarile din tabelul cust_dm
1287 IS
1288 SELECT *
1289 FROM cust_dm;
1290
1291 -- variabilele folosite la discretizare
1292 v_gender NUMBER;
1293 v_income_id NUMBER;
1294 v_marital_status NUMBER;
1295 v_age NUMBER;
1296 v_state_province NUMBER;
1297 v_credit_limit NUMBER;
1298BEGIN
1299 FOR cust_record IN c_cust_dm -- iteram peste inregistrari
1300 LOOP
1301 -- preluam valoarea discretizata pentru gender
1302 SELECT disc_val
1303 INTO v_gender
1304 FROM cust_gender_vals
1305 WHERE gender_val = cust_record.gender;
1306
1307 -- preluam valoarea discretizata pentru income_id
1308 SELECT disc_val
1309 INTO v_income_id
1310 FROM cust_income_id_vals
1311 WHERE income_id_val = cust_record.income_id;
1312
1313 -- preluam valoarea discretizata pentru marital_status
1314 SELECT disc_val
1315 INTO v_marital_status
1316 FROM cust_marital_status_vals
1317 WHERE marital_status_val = cust_record.marital_status;
1318
1319 -- preluam valoarea discretizata pentru age
1320 SELECT disc_val
1321 INTO v_age
1322 FROM cust_age_vals
1323 WHERE cust_record.age BETWEEN age_loval AND NVL(age_hival, 1000);
1324
1325 -- preluam valoarea discretizata pentru state_province
1326 SELECT disc_val
1327 INTO v_state_province
1328 FROM cust_state_province_vals
1329 WHERE state_province_val = cust_record.state_province;
1330
1331 -- preluam valoarea discretizata pentru credit_limit
1332 SELECT disc_val
1333 INTO v_credit_limit
1334 FROM cust_credit_limit_vals
1335 WHERE cust_record.credit_limit BETWEEN credit_limit_lo AND credit_limit_hi;
1336
1337 -- acum inseram toate valorile discretizate sub forma de inregistrare in tabelul final
1338 INSERT INTO cust_dm_bin
1339 VALUES (
1340 v_gender, v_income_id, v_marital_status, v_age, v_state_province, v_credit_limit
1341 );
1342 END LOOP;
1343END;
1344
1345-- verificam tabelul final
1346SELECT *
1347FROM cust_dm_bin;