This shows you the differences between two versions of the page.
bd:laboratoare:07 [2019/03/31 09:26] maria_luiza.serban [Exerciții individuale] |
bd:laboratoare:07 [2022/05/05 16:55] (current) florin.radulescu [Funcții diverse] |
||
---|---|---|---|
Line 1: | Line 1: | ||
===== Laborator 07 - Funcții SQL (II) ===== | ===== Laborator 07 - Funcții SQL (II) ===== | ||
- | **Conținut** | + | |
+ | ===== Obiective ===== | ||
* Funcții de conversie | * Funcții de conversie | ||
* Funcții diverse | * Funcții diverse | ||
* Funcții de grup | * Funcții de grup | ||
+ | | ||
+ | ===== Materiale ajutătoare ===== | ||
+ | |||
+ | [[bd:resurse:tables|Resurse BD]] | ||
+ | |||
==== Funcții de conversie ==== | ==== Funcții de conversie ==== | ||
Line 17: | Line 24: | ||
Parametrul ‘nlsparam’ este utilizat pentru a seta parametrii de sistem să evalueze expresia în diferite limbi, formate, etc. | Parametrul ‘nlsparam’ este utilizat pentru a seta parametrii de sistem să evalueze expresia în diferite limbi, formate, etc. | ||
- | {{:bd:laboratoare:lab07_p1.png}} | + | {{:bd:laboratoare:lab07_p1.png?500}} |
<color red>Ex 1. </color>Să se selecteze toți angajații care au venit în firmă în 1982. | <color red>Ex 1. </color>Să se selecteze toți angajații care au venit în firmă în 1982. | ||
- | {{:bd:laboratoare:lab07_p2.png}} | + | {{:bd:laboratoare:lab07_p2.png?500}} |
Formatul pentru numere (se pot folosi toate cu comanda COLUMN): | Formatul pentru numere (se pot folosi toate cu comanda COLUMN): | ||
Line 28: | Line 35: | ||
| 9 | Numere (nr. de 9 determină lungimea de afișare) | | | 9 | Numere (nr. de 9 determină lungimea de afișare) | | ||
| 0 | Afișează 0 de la început | | | 0 | Afișează 0 de la început | | ||
- | | \$ | Semnul $ | | + | | ''$'' | Semnul ''$'' | |
| . | Punct decimal | | | . | Punct decimal | | ||
| , | Virgulă | | | , | Virgulă | | ||
Line 39: | Line 46: | ||
- | {{:bd:laboratoare:lab07_p3.png}} | + | {{:bd:laboratoare:lab07_p3.png?320}} |
- | |{{:bd:laboratoare:lab07_p4.png}}|{{:bd:laboratoare:lab07_p5.png}}| | + | |{{:bd:laboratoare:lab07_p4.png?380}}|{{:bd:laboratoare:lab07_p5.png?340}}| |
Line 48: | Line 55: | ||
|**LEAST(exp1[,expr2[,...]])** | returnează cel mai mic element din listă | | |**LEAST(exp1[,expr2[,...]])** | returnează cel mai mic element din listă | | ||
- | {{:bd:laboratoare:lab07_p6.png}} | + | {{:bd:laboratoare:lab07_p6.png?500}} |
=== DECODE și CASE === | === DECODE și CASE === | ||
Line 60: | Line 67: | ||
<color red>Ex 2. </color>Să se caluleze o primă pentru angajații din departamentul 20 în funcție de jobul angajatului. | <color red>Ex 2. </color>Să se caluleze o primă pentru angajații din departamentul 20 în funcție de jobul angajatului. | ||
- | {{:bd:laboratoare:lab07_p7_1.png}} | + | {{:bd:laboratoare:lab07_p7_1.png?500}} |
<color red>Ex 3. </color>Să se calculeze o primă în funcție de vechime pentru angajații din departamentul 20. | <color red>Ex 3. </color>Să se calculeze o primă în funcție de vechime pentru angajații din departamentul 20. | ||
- | {{:bd:laboratoare:lab07_p7_2.png}} | + | {{:bd:laboratoare:lab07_p7_2.png?520}} |
- | * **Instrucțiunea CASE** | + | * **Expresii CASE** |
- | Instrucținea CASE poate fi folosită în clauza SELECT sau WHERE: | + | Expresiile CASE pot fi folosite în clauza SELECT sau WHERE. Ele returnează o valoare (a nu se confunda cu instrucțiunea CASE): |
Sintaxa 1: | Sintaxa 1: | ||
<code sql>CASE expr | <code sql>CASE expr | ||
- | WHEN value1 THEN statements_1; | + | WHEN value1 THEN retval_1 |
- | WHEN value2 THEN statements_2; | + | WHEN value2 THEN retval_2 |
... | ... | ||
- | [ELSE statements_k;] | + | [ELSE retval_k] |
END</code> | END</code> | ||
* **expr** – reprezintă expresia care se va evalua; | * **expr** – reprezintă expresia care se va evalua; | ||
- | * **statements_1** – reprezintă valoarea care se va returna pentru **expr = value1;** | + | * **retval_1** – reprezintă valoarea care se va returna pentru **expr = value1;** |
- | * **statements_2** – reprezintă valoarea care se va returna pentru **expr = value2;** | + | * **retval_2** – reprezintă valoarea care se va returna pentru **expr = value2;** |
- | * **statements_k** – reprezintă valoarea implicită care se va returna pentru **expr <> value1, value2, ...** | + | * **retval_k** – reprezintă valoarea implicită care se va returna pentru **expr <> value1, value2, ...** |
Sintaxa 2: | Sintaxa 2: | ||
<code sql>CASE | <code sql>CASE | ||
- | WHEN expr_1 THEN statements_1; | + | WHEN expr_1 THEN retval_1 |
- | WHEN expr_2 THEN statements_2; | + | WHEN expr_2 THEN retval_2 |
... | ... | ||
- | [ELSE statements_k;] | + | [ELSE retval_k] |
END</code> | END</code> | ||
- | * **expr_i** – reprezintă expresia care se va evalua | + | * **expr_i** – reprezintă expresia **booleana** care se va evalua |
- | * **statements_i** – reprezintă valoarea care se va returna pentru **expr_i** | + | * **retval_i** – reprezintă valoarea care se va returna pentru **expr_i** |
- | |{{:bd:laboratoare:lab07_p8_1.png}}|{{:bd:laboratoare:lab07_p8_2.png}}| | + | |{{:bd:laboratoare:lab07_p8_1.png?320}}|{{:bd:laboratoare:lab07_p8_2.png?410}}| |
- | |{{:bd:laboratoare:lab07_p9_1.png}}|{{:bd:laboratoare:lab07_p9_2.png}}| | + | |{{:bd:laboratoare:lab07_p9_1.png?330}}|{{:bd:laboratoare:lab07_p9_2.png?390}}| |
* **NVL(expr1, expr2)** – returnează expr2 dacă expr1 este null | * **NVL(expr1, expr2)** – returnează expr2 dacă expr1 este null | ||
Line 104: | Line 111: | ||
<note important>Valoarea null trebuie obligatoriu convertită la 0 atunci când se fac operații aritmetice. Dacă această conversie nu se face, atunci rezultatul operației aritmetice va fi întotdeauna null.</note> | <note important>Valoarea null trebuie obligatoriu convertită la 0 atunci când se fac operații aritmetice. Dacă această conversie nu se face, atunci rezultatul operației aritmetice va fi întotdeauna null.</note> | ||
- | {{:bd:laboratoare:lab07_p10.png}} | + | {{:bd:laboratoare:lab07_p10.png?500}} |
<note tip>Funcțiile pot fi imbricate, ordinea de execuție este din interior spre exterior.</note> | <note tip>Funcțiile pot fi imbricate, ordinea de execuție este din interior spre exterior.</note> | ||
Line 123: | Line 130: | ||
<color red>Ex 4. </color> Afișați media salariilor pentru toate valorile din tabel, iar apoi doar pentru salariile distincte. Rezultatul este diferit deoarece există salarii duplicate. | <color red>Ex 4. </color> Afișați media salariilor pentru toate valorile din tabel, iar apoi doar pentru salariile distincte. Rezultatul este diferit deoarece există salarii duplicate. | ||
- | {{:bd:laboratoare:lab07_p11.png}} | + | {{:bd:laboratoare:lab07_p11.png?530}} |
<color red>Ex 5. </color> Să se calculeze salariul mediu pentru fiecare departament. | <color red>Ex 5. </color> Să se calculeze salariul mediu pentru fiecare departament. | ||
- | {{:bd:laboratoare:lab07_p12.png}} | + | {{:bd:laboratoare:lab07_p12.png?500}} |
<color red>Ex 6. </color> Să se calculeze venitul lunar mediu pentru fiecare departament. Afișati id_dep și venitul lunar doar pentru departamentele care au venitul lunar mediu mai mare de 2000. Pentru a aplica o condiție bazată pe funcții de agregare, folosim HAVING în loc de WHERE. | <color red>Ex 6. </color> Să se calculeze venitul lunar mediu pentru fiecare departament. Afișati id_dep și venitul lunar doar pentru departamentele care au venitul lunar mediu mai mare de 2000. Pentru a aplica o condiție bazată pe funcții de agregare, folosim HAVING în loc de WHERE. | ||
- | {{:bd:laboratoare:lab07_p13.png}} | + | {{:bd:laboratoare:lab07_p13.png?530}} |
* **COUNT(*|[DISTINCT|ALL] expr)** – returnează numărul de înregistrări întoarse de interogare. Dacă se folosește * se numără toate înregistrările, inclusiv cele nule, iar dacă se folosește expr se numără toate înregistrările not null . | * **COUNT(*|[DISTINCT|ALL] expr)** – returnează numărul de înregistrări întoarse de interogare. Dacă se folosește * se numără toate înregistrările, inclusiv cele nule, iar dacă se folosește expr se numără toate înregistrările not null . | ||
Line 137: | Line 144: | ||
<color red>Ex 7. </color> Să se afișeze numărul angajatilor care au primit salariu pentru fiecare departament. | <color red>Ex 7. </color> Să se afișeze numărul angajatilor care au primit salariu pentru fiecare departament. | ||
- | {{:bd:laboratoare:lab07_p14.png}} | + | {{:bd:laboratoare:lab07_p14.png?500}} |
<color red>Ex 8. </color> Să se afișeze departamentele care au cel puțin două funcții distincte pentru angajați. | <color red>Ex 8. </color> Să se afișeze departamentele care au cel puțin două funcții distincte pentru angajați. | ||
- | {{:bd:laboratoare:lab07_p15.png}} | + | {{:bd:laboratoare:lab07_p15.png?500}} |
* **MAX([DISTINCT|ALL] expr)** – returnează valoarea maximă pentru expr. | * **MAX([DISTINCT|ALL] expr)** – returnează valoarea maximă pentru expr. | ||
Line 149: | Line 156: | ||
<color red>Ex 9. </color> Să se afișeze salariul minim, maxim și suma slariilor pentru fiecare departament. | <color red>Ex 9. </color> Să se afișeze salariul minim, maxim și suma slariilor pentru fiecare departament. | ||
- | {{:bd:laboratoare:lab07_p16.png}} | + | {{:bd:laboratoare:lab07_p16.png?500}} |
* **VARIANCE([DISTINCT|ALL] expr)** – returnează variația standard pentru expr, ingnorând valorile null; | * **VARIANCE([DISTINCT|ALL] expr)** – returnează variația standard pentru expr, ingnorând valorile null; | ||
* **STDDEV([DISTINCT|ALL] expr)** – returnează deviația standard pentru expr, ingnorând valorile null. | * **STDDEV([DISTINCT|ALL] expr)** – returnează deviația standard pentru expr, ingnorând valorile null. | ||
- | <color red>Ex 10. </color> Să se afișeze varianțtia standard și deviația standard a salariilor pentru fiecare departament. | + | <color red>Ex 10. </color> Să se afișeze varianția standard și deviația standard a salariilor pentru fiecare departament. |
- | {{:bd:laboratoare:lab07_p17.png}} | + | {{:bd:laboratoare:lab07_p17.png?500}} |
==== Exerciții individuale ==== | ==== Exerciții individuale ==== | ||
- | Să se facă o listă cu: | + | <color red>Ex 1. </color> Să se facă o listă cu: |
- | * numele angajatului (formatați prima litera mare și restul mici) | + | * id-ul sefului |
- | * departamentul în care lucreaza angajatul (formataîi cu litere mici) | + | * numele sefului |
+ | * numarul de subalterni | ||
+ | * numele departamentului din care face parte seful | ||
+ | * salariu minim/mediu/maxim pt subalterni | ||
+ | |||
+ | <hidden> | ||
+ | <code> | ||
+ | select s.empno "id sef", | ||
+ | s.ename "nume sef", | ||
+ | count(e.empno) "nr angajati", | ||
+ | d.dname "dept", | ||
+ | min (e.sal) "min", | ||
+ | max (e.sal) "max", | ||
+ | avg (e.sal) "avg" | ||
+ | from emp e, emp s, dept d | ||
+ | where e.mgr = s.empno and | ||
+ | s.deptno = d.deptno | ||
+ | group by s.empno, s.ename, d.dname; | ||
+ | |||
+ | id sef nume sef nr angajati dept min max avg | ||
+ | ---------- ---------- ----------- -------------- ---------- ---------- ---------- | ||
+ | 7782 CLARK 1 ACCOUNTING 1300 1300 1300 | ||
+ | 7839 KING 3 ACCOUNTING 2450 2975 2758.33333 | ||
+ | 7566 JONES 2 RESEARCH 3000 3000 3000 | ||
+ | 7788 SCOTT 1 RESEARCH 1100 1100 1100 | ||
+ | 7902 FORD 1 RESEARCH 800 800 800 | ||
+ | 7698 BLAKE 5 SALES 950 1600 1310 | ||
+ | |||
+ | </code> | ||
+ | </hidden> | ||
+ | |||
+ | <color red>Ex 2. </color> Să se facă o listă cu: | ||
+ | * numele angajatului - formatați prima litera mare și restul mici | ||
+ | * departamentul în care lucreaza angajatul - formatați cu litere mici | ||
* data angajării pentru angajat - afișati doar luna și anul | * data angajării pentru angajat - afișati doar luna și anul | ||
- | * prima | + | * prima - calculată conform regulilor de mai jos |
- | * gradul salarial în funcîie de salariu | + | * gradul salarial în funcție de salariu |
- | * numele șefului (formatați prima litera mare și restul mici) | + | * numele șefului - formatați prima litera mare și restul mici |
+ | * departamentul în care lucreaza șeful - formatați cu litere mici | ||
- | Prima se acorda doar pentru angajații care au gradul salarial 2 sau 4 și au litera A în nume și au venit în firma în același an cu șeful lor | + | Prima se acorda doar pentru angajații care au gradul salarial 2 sau 4 și au venit în firmă în același an cu șeful lor |
- | * dacă angajatul are gradul salarial 2: salariu * sqrt(|((n^3/grad)*sin(n^3/grad))/(c * log2(n))|) | + | * dacă angajatul are gradul salarial 2: salariu * sqrt(c * log2(n)) |
- | * dacă angajatul are gradul salarial 4: salariu * sqrt(|((n^3/grad)*cos(n^3/grad))/(c * log3(n))|) | + | * dacă angajatul are gradul salarial 4: salariu * sqrt(c * log3(n)) |
- | unde n este vechimea în firmă calculată în ani cu virgulă și c este o valoare introdusa de la tastatură (folosiți 1000 pentru testare). | + | * altfel: 0 |
+ | unde n este vechimea în firmă calculată în luni și c este o valoare introdusă de la tastatură (folosiți 0.15 pentru testare). | ||
Ordonați rezultatele crescător dupa numele angajatului. | Ordonați rezultatele crescător dupa numele angajatului. | ||
- | ====Bibliografie==== | + | |
- | - **Alexandru Boicea** -// Oracle SQL SQL Plus//, Editura Printech | + | <hidden> |
+ | Rezolvare | ||
+ | |||
+ | {{:bd:laboratoare:lab07_ex.png}} | ||
+ | <code sql> | ||
+ | select initcap (a.ename) angajat, | ||
+ | lower (d.dname) dep_ang, | ||
+ | to_char (a.hiredate, 'MM-YYYY') data_ang, | ||
+ | case when to_number (to_char(a.hiredate, 'YYYY')) = to_number (to_char(s.hiredate, 'YYYY')) | ||
+ | then case g.grade | ||
+ | when 2 then a.sal * sqrt(&c * log(2, months_between(sysdate, a.hiredate))) | ||
+ | when 4 then a.sal * sqrt(&c * log(3, months_between(sysdate, a.hiredate))) | ||
+ | else 0 | ||
+ | end | ||
+ | else 0 | ||
+ | end prima, | ||
+ | g.grade, | ||
+ | initcap (s.ename) sef, | ||
+ | lower (ds.dname) dep_sef | ||
+ | from emp a | ||
+ | join dept d on a.deptno = d.deptno | ||
+ | join salgrade g on a.sal between g.losal and hisal | ||
+ | left outer join emp s | ||
+ | join dept ds on s.deptno = ds.deptno | ||
+ | on a.mgr = s.empno | ||
+ | order by a.ename; | ||
+ | </code> | ||
+ | |||
+ | Rezultat | ||
+ | |||
+ | {{:bd:laboratoare:lab07_ex_rez.png}} | ||
+ | <code> | ||
+ | ANGAJAT DEP_ANG DATA_AN PRIMA GRADE SEF DEP_SEF | ||
+ | ---------- -------------- ------- ---------- ---------- ---------- -------------- | ||
+ | Adams research 01-1983 0 1 Scott research | ||
+ | Allen sales 02-1981 0 3 Blake sales | ||
+ | Blake sales 05-1981 2605.28073 4 King accounting | ||
+ | Clark accounting 06-1981 2239.13302 4 King accounting | ||
+ | Ford research 12-1981 2738.90805 4 Jones research | ||
+ | James sales 12-1981 0 1 Blake sales | ||
+ | Jones research 04-1981 2720.03438 4 King accounting | ||
+ | King accounting 11-1981 0 5 | ||
+ | Martin sales 09-1981 1437.3065 2 Blake sales | ||
+ | Miller accounting 01-1982 0 2 Clark accounting | ||
+ | Scott research 12-1982 0 4 Jones research | ||
+ | Smith research 12-1980 0 1 Ford research | ||
+ | Turner sales 09-1981 0 3 Blake sales | ||
+ | Ward sales 02-1981 1439.17011 2 Blake sales | ||
+ | |||
+ | </code> | ||
+ | </hidden> | ||
+ | |||
+ |