Show page

Differences

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

--- bd:laboratoare:07 [2019/03/31 09:37]
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) =====
-**Conținut**
+===== Obiective =====
   * Funcții de conversie
   * Funcții diverse
   * Funcții de grup
+===== Materiale ajutătoare =====
+[[bd:resurse:tables|Resurse BD]]
 ==== 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.
-{{: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.
-{{:bd:laboratoare:lab07_p2.png}}
+{{:bd:laboratoare:lab07_p2.png?500}}
 Formatul pentru numere (se pot folosi toate cu comanda COLUMN):
@@ Line 28: / Line 35: @@
 | 9    | Numere (nr. de 9 determină lungimea de afișare)    |
 | 0    | Afișează 0 de la început    |
-| \$   | Semnul $     |
+| ''$''   | Semnul ''$''     |
 | .    | Punct decimal     |
 | ,    | 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ă  |
-{{:bd:laboratoare:lab07_p6.png}}
+{{:bd:laboratoare:lab07_p6.png?500}}
 === 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.
-{{: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.
-{{: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:
 <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>
   * **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:
 <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>
-  * **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
@@ 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>
-{{: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>
@@ 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.
-{{:bd:laboratoare:lab07_p11.png}}
+{{:bd:laboratoare:lab07_p11.png?530}}
 <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.
-{{: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 .
@@ Line 137: / Line 144: @@
 <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.
-{{:bd:laboratoare:lab07_p15.png}}
+{{:bd:laboratoare:lab07_p15.png?500}}
   * **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.
-{{: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;
   * **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}}
@@ Line 161: / Line 168: @@
 <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
+---------- ---------- ----------- -------------- ---------- ---------- ----------
+CLARK                1 ACCOUNTING           1300       1300       1300
+KING                 3 ACCOUNTING           2450       2975 2758.33333
+JONES                2 RESEARCH             3000       3000       3000
+SCOTT                1 RESEARCH             1100       1100       1100
+FORD                 1 RESEARCH              800        800        800
+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
+  * 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
-  * departamentul în care lucreaza angajatul - formatați cu litere 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(c * log2(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.
-<color red>Ex 2. </color>
+<hidden>
-====Bibliografie====
+Rezolvare
-  - **Alexandru Boicea** -// Oracle SQL SQL Plus//, Editura Printech
+{{: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>

Laboratoare

Resurse

bd/laboratoare/07.1554014271.txt.gz · Last modified: 2019/03/31 09:37 by maria_luiza.serban

Show page Old revisions

Media Manager Back to top