Laborator 07 - Funcții SQL (II)

Conținut

  • Funcții de conversie
  • Funcții diverse
  • Funcții de grup

Funcții de conversie

Aceste funcții fac converia de la un tip de date la un alt tip de date:

Funcție Descriere funcție
TO_CHAR(expr[,format[,’nlsparams’]]) face conversia lui expr (care poate avea tipul fie numeric, fie dată) la VARCHAR2
TO_DATE(expr[,format[,’nlsparams’]]) face conversia lui expr (cu tipul CHAR sau VARCHAR2) în formatul DATE
TO_NUMBER(expr [,format[,’nlsparams’]]) face conversia lui expr la o valoare de tip NUMBER

Parametrul format specifică formatul în care se va face afișarea, dacă lipsește se ia formatul implicit. Pentru TO_NUMBER specifică formatul în care este dat expr. Parametrul ‘nlsparam’ este utilizat pentru a seta parametrii de sistem să evalueze expresia în diferite limbi, formate, etc.

Ex 1. Să se selecteze toți angajații care au venit în firmă în 1982.

Formatul pentru numere (se pot folosi toate cu comanda COLUMN):

Format Seminificație
9 Numere (nr. de 9 determină lungimea de afișare)
0 Afișează 0 de la început
\$ Semnul $
. Punct decimal
, Virgulă
MI Semnul minus la dreapta
PR Paranteze pentru numere negative
EEEE Notație științifică
V Înmulțire cu 10 (nr. de 9 de după V specifică de câte ori se face înmulțirea)
B Afișează valori 0 ca blancuri
D Specifică câte zerouri să fie înainte și după delimitatorul decimal

Funcții diverse

GREATEST(exp1[,expr2[,…]]) returnează cel mai mare elemet din listă
LEAST(exp1[,expr2[,…]]) returnează cel mai mic element din listă

DECODE și CASE

  • DECODE(expr, search_1, result_1, search_2, result_2, …, search_n, result_n, default) – compară expr cu fiecare valoare search_i și întoarce valoarea result_i dacă expr este egală cu valoarea search_i, dacă nu găsește nicio egalitate întoarce valoarea default (i=1..n).
    • expr – poate din orice tip de dată
    • search_i – este de același tip ca expr
    • result_i – este valoarea întoarsă și poate fi de orice tip
    • default – este de același tip ca result_i

Această funcție are efectul unei unei structuri case sau if-then-else.

Ex 2. Să se caluleze o primă pentru angajații din departamentul 20 în funcție de jobul angajatului.

Ex 3. Să se calculeze o primă în funcție de vechime pentru angajații din departamentul 20.

  • Instrucțiunea CASE

Instrucținea CASE poate fi folosită în clauza SELECT sau WHERE:

Sintaxa 1:

CASE expr
WHEN value1 THEN statements_1;
WHEN value2 THEN statements_2;
...
[ELSE statements_k;]
END
  • expr – reprezintă expresia care se va evalua;
  • statements_1 – reprezintă valoarea care se va returna pentru expr = value1;
  • statements_2 – reprezintă valoarea care se va returna pentru expr = value2;
  • statements_k – reprezintă valoarea implicită care se va returna pentru expr <> value1, value2, …

Sintaxa 2:

CASE 
WHEN expr_1 THEN statements_1;
WHEN expr_2 THEN statements_2;
...
[ELSE statements_k;]
END
  • expr_i – reprezintă expresia care se va evalua
  • statements_i – reprezintă valoarea care se va returna pentru expr_i
  • NVL(expr1, expr2) – returnează expr2 dacă expr1 este null

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.

Funcțiile pot fi imbricate, ordinea de execuție este din interior spre exterior.

Pentru a afla userul curent se folosește funcția user:

SELECT USER FROM dual;

Funcții de grup

Aceste funcții returnează rezultate pe grupuri de înregistrări, nu pe o singură înregistrare, cum au fost cele prezentate până acum. Gruparea se face folosind clauza GROUP BY într-o comandă SELECT și în acest caz toate elementele listei, cu exceptia funcțiilor de grupare, trebuie cuprinse în clauza de grupare. Pentru a se pune condiții folosind funcțiile de grup acestea trebuie să apară în clauza HAVING, nu în clauza WHERE. Se poate folosi operatorul DISTINCT pentru a selecta doar elemente distincte din listă, dar se poate folosi și operatorul ALL pentru a selecta și duplicatele. Funcțiile de grup se mai numesc și funcții de agregare (SQL Aggregate Functions).

  • AVG([DISTINCT|ALL] expr) – returnează valoarea medie a unei expresii

Ex 4. Afișați media salariilor pentru toate valorile din tabel, iar apoi doar pentru salariile distincte. Rezultatul este diferit deoarece există salarii duplicate.

Ex 5. Să se calculeze salariul mediu pentru fiecare departament.

Ex 6. 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.

  • 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 .

Ex 7. Să se afișeze numărul angajatilor care au primit salariu pentru fiecare departament.

Ex 8. Să se afișeze departamentele care au cel puțin două funcții distincte pentru angajați.

  • MAX([DISTINCT|ALL] expr) – returnează valoarea maximă pentru expr.
  • MIN([DISTINCT|ALL] expr) – returnează valoarea minimă pentru expr.
  • SUM([DISTINCT|ALL] expr) – returnează suma valorilor pentru expr.

Ex 9. Să se afișeze salariul minim, maxim și suma slariilor pentru fiecare departament.

  • 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.

Ex 10. Să se afișeze varianția standard și deviația standard a salariilor pentru fiecare departament.

Exerciții individuale

Ex 1. 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

Ex 2. 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.

bd/laboratoare/07.txt · Last modified: 2019/09/22 18:02 by ciprian.truica
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