Differences

This shows you the differences between two versions of the page.

Link to this comparison view

bd:laboratoare:07 [2019/03/30 22:48]
maria_luiza.serban [Funcții de conversie]
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_p4.png}}|{{:​bd:​laboratoare:​lab07_p5.png}}|+{{:​bd:​laboratoare:​lab07_p3.png?320}} 
 + 
 +|{{:​bd:​laboratoare:​lab07_p4.png?380}}|{{:​bd:​laboratoare:​lab07_p5.png?340}}|
  
  
Line 46: 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 58: 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 102: 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 118: Line 127:
  
   * **AVG([DISTINCT|ALL] expr)** – returnează valoarea medie a unei expresii   * **AVG([DISTINCT|ALL] expr)** – returnează valoarea medie a unei expresii
 + 
 +<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.ex15.png |}}+{{:​bd:​laboratoare:​lab07_p11.png?530}}
  
-{{ :​bd:​laboratoare:​lab07.ex16.png |}}+<color red>Ex 5. </​color>​ Să se calculeze salariul mediu pentru fiecare departament. 
 + 
 +{{:​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. 
 + 
 +{{:​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 .
  
-{{ :​bd:​laboratoare:​lab07.ex17.png |}}+<color red>Ex 7</​color>​ Să se afișeze numărul angajatilor care au primit salariu pentru fiecare departament.
  
-{{ :​bd:​laboratoare:​lab07.ex18.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. 
 + 
 +{{:​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 133: Line 154:
   * **SUM([DISTINCT|ALL] expr)** – returnează suma valorilor pentru expr.   * **SUM([DISTINCT|ALL] expr)** – returnează suma valorilor pentru expr.
  
-{{ :​bd:​laboratoare:​lab07.ex19.png |}}+<color red>Ex 9. </​color>​ Să se afișeze salariul minim, maxim și suma slariilor pentru fiecare departament. 
 + 
 +{{:​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.
  
-{{ :​bd:​laboratoare:​lab07.ex20.png |}}+<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?500}} 
  
 ==== Exerciții individuale ==== ==== Exerciții individuale ====
 +
 +<color red>Ex 1. </​color>​ Să se facă o listă cu:
 +  * id-ul sefului
 +  * 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
 +  * prima - calculată conform regulilor de mai jos
 +  * gradul salarial în funcție de salariu
 +  * 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 venit în firmă în același an cu șeful lor
 +  * dacă angajatul are gradul salarial 2: salariu * sqrt(c * log2(n))
 +  * dacă angajatul are gradul salarial 4: salariu * sqrt(c * log3(n))
 +  * 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.
 +
 +<​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>​
 +
  
  
-====Bibliografie==== 
-  - **Alexandru Boicea** -// Oracle SQL SQL Plus//, Editura Printech ​ 
bd/laboratoare/07.1553978890.txt.gz · Last modified: 2019/03/30 22:48 by maria_luiza.serban
CC Attribution-Share Alike 3.0 Unported
www.chimeric.de Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0