LAborator Complet

27
1 Limbajul de definire a datelor (DDL). CREATE, ALTER, DROP Definirea tabelelor CREATE TABLE [schema.]nume_tabel ( nume_coloana tip_de_date [DEFAULT 1 expr], ...); CREATE TABLE nume_tabel [(col1, col2...)] AS subcerere; 1. CreaŃi tabelul salariat_*** având următoarea structură: Nume Caracteristici Tip cod_ang NOT NULL NUMBER(4) nume VARCHAR2(25) prenume VARCHAR2(25) functia VARCHAR2(20) sef NUMBER(4) data_angajarii Valoare implicită data curentă DATE varsta NUMBER(2) email CHAR(50) salariu Valoare implicită 0 NUMBER(9,2) CREATE TABLE salariat_*** ( cod_ang NUMBER(4) NOT NULL, nume VARCHAR2(25), prenume VARCHAR2(25), functia VARCHAR2(20), sef NUMBER(4), data_angajarii DATE DEFAULT SYSDATE, varsta NUMBER(2), email CHAR(50), salariu NUMBER(9,2) DEFAULT 0); 2. AfişaŃi structura tabelului creat anterior. 3. Se dau următoarele valori: COD _ANG NUME PRENUME FUNCTIA SEF DATA_ANG VARSTA EMAIL SALARIU 1 ..... ..... director null ........ 30 ..... 5500 2 ..... ..... functionar 1 ......... 25 ..... 0 3 ..... ...... economist 1 ......... 45 ..... 3000 4 ..... .... functionar 1 ......... 35 ...... 1000 4. InseraŃi în tabelul salariat_*** prima înregistrare din tabelul de mai sus fără să precizaŃi lista de coloane în comanda INSERT. 5. InseraŃi a doua înregistrare folosind o listă de coloane din care excludeŃi data_angajarii şi salariul care au valori implicite. ObservaŃi apoi rezultatul. 6. InseraŃi înregistrările 3 şi 4. 7. CreaŃi tabelul functionar_*** care să conŃină funcŃionarii din tabelul salariat_***, având următoarele coloane: codul, numele, salariul anual şi data angajării. VerificaŃi cum a fost creat tabelul şi ce date conŃine.

description

Baza date laborator

Transcript of LAborator Complet

  • 1

    Limbajul de definire a datelor (DDL). CREATE, ALTER, DROP

    Definirea tabelelor CREATE TABLE [schema.]nume_tabel (

    nume_coloana tip_de_date [DEFAULT1 expr], ...);

    CREATE TABLE nume_tabel [(col1, col2...)] AS subcerere;

    1. Creai tabelul salariat_*** avnd urmtoarea structur: Nume Caracteristici Tip cod_ang NOT NULL NUMBER(4) nume VARCHAR2(25) prenume VARCHAR2(25) functia VARCHAR2(20) sef NUMBER(4) data_angajarii Valoare implicit data

    curent DATE

    varsta NUMBER(2) email CHAR(50) salariu Valoare implicit 0 NUMBER(9,2)

    CREATE TABLE salariat_*** ( cod_ang NUMBER(4) NOT NULL, nume VARCHAR2(25), prenume VARCHAR2(25), functia VARCHAR2(20), sef NUMBER(4), data_angajarii DATE DEFAULT SYSDATE, varsta NUMBER(2), email CHAR(50), salariu NUMBER(9,2) DEFAULT 0); 2. Afiai structura tabelului creat anterior. 3. Se dau urmtoarele valori:

    COD _ANG NUME PRENUME FUNCTIA SEF DATA_ANG VARSTA EMAIL SALARIU 1 ..... ..... director null ........ 30 ..... 5500 2 ..... ..... functionar 1 ......... 25 ..... 0 3 ..... ...... economist 1 ......... 45 ..... 3000 4 ..... .... functionar 1 ......... 35 ...... 1000

    4. Inserai n tabelul salariat_*** prima nregistrare din tabelul de mai sus fr s precizai lista de coloane n comanda INSERT.

    5. Inserai a doua nregistrare folosind o list de coloane din care excludei data_angajarii i salariul care au valori implicite. Observai apoi rezultatul.

    6. Inserai nregistrrile 3 i 4. 7. Creai tabelul functionar_*** care s conin funcionarii din tabelul salariat_***, avnd urmtoarele

    coloane: codul, numele, salariul anual i data angajrii. Verificai cum a fost creat tabelul i ce date conine.

  • 2

    Modificarea tabelelor

    8. Adugai o nou coloan tabelului salariat_*** care s conin data naterii.

    ALTER TABLE salariat_*** ADD (datan DATE);

    9. Modificai dimensiunea coloanei nume la 30 si pe cea a salariului la 12 cu 3 zecimale.

    ALTER TABLE salariat_*** MODIFY (nume VARCHAR2(30), salariu NUMBER(12,3));

    10. Modificai tipul coloanei email la VARCHAR2. 11. Modificai valoarea implicit a coloanei data_angajarii la data sistemului + o zi. 12. Eliminai coloana varsta din tabelul salariat_***.

    ALTER TABLE salariat_*** DROP COLUMN varsta;

    Redenumirea i eliminarea tabelelor

    RENAME nume_tabel TO nume_nou; DROP TABLE nume_tabel;

    13. Redenumii tabelul functionar_*** cu funct_***. 14. Recreai tabelul functionar_*** utiliznd tabelul funct_***.. 15. Eliminai tabelul funct_***.

    Constrngeri

    Adugarea constrngerilor la crearea tabelului (CREATE TABLE) CREATE TABLE [schema.]nume_tabel (

    nume_coloana tip_de_date [DEFAULT expr] [constrangere_de_coloana], ... ..[constrangere la nivel de tabel])

    16. tergei i apoi creai din nou tabelul salariat_*** cu urmtoarea structur. NUME TIP CONSTRNGERE cod_ang NUMBER(4) Cheie primar nume VARCHAR2(25) NOT NULL prenume VARCHAR2(25) data_nasterii DATE data_nasterii 0 cod_dept NUMBER(4) Combinaia NUME + PRENUME s fie unic

  • 3

    Observaie: Constrngerile de tip CHECK se pot implementa la nivel de coloan doar dac nu refer o alt coloan a

    tabelului.

    DROP TABLE salariat_***; CREATE TABLE salariat_*** ( cod_ang NUMBER(4) PRIMARY KEY, nume VARCHAR2(25) NOT NULL, prenume VARCHAR2(25), data_nasterii DATE, functia VARCHAR2(9) NOT NULL, sef NUMBER(4) REFERENCES salariat_*** (cod_ang), data_angajarii DATE DEFAULT SYSDATE, email VARCHAR2(20) UNIQUE, salariu NUMBER(9,2) CHECK (salariu > 0), cod_dep NUMBER(4), CONSTRAINT const_c_*** CHECK (data_angajarii > data_nasterii), CONSTRAINT const_u_*** UNIQUE (nume,prenume,data_nasterii)); 17. tergei tabelul salariat_***, iar apoi recreai-l implementnd toate constrngerile la nivel de tabel. Observaie: Constrngerea de tip NOT NULL se poate declara doar la nivel de coloan.

    DROP TABLE salariat_***; CREATE TABLE salariat_*** ( cod_ang NUMBER(4), nume VARCHAR2(25) NOT NULL, prenume VARCHAR2(25), data_nasterii DATE, functia VARCHAR2(9) NOT NULL, sef NUMBER(4), data_angajarii DATE DEFAULT SYSDATE, email VARCHAR2(20), salariu NUMBER(9,2), cod_dep NUMBER(4), CONSTRAINT ccp_*** PRIMARY KEY (cod_ang), CONSTRAINT cce_*** FOREIGN KEY (sef) REFERENCES salariat_*** (cod_ang), CONSTRAINT cu1_*** UNIQUE (email), CONSTRAINT cc1_*** CHECK (data_angajarii > data_nasterii), CONSTRAINT cc2_***CHECK (salariu > 0), CONSTRAINT cu2_*** UNIQUE (nume,prenume,data_nasterii)); 18. Creai tabelul departament_*** care s aib urmtoarea structur.

    NUME TIP CONSTRNGERI COD_DEP NUMBER(4) Cheie primar NUME VARCHAR2(20) Not null ORAS VARCHAR2(25)

    Adugarea constrngerilor ulterior crerii tabelului, eliminarea, activarea sau dezactivarea constrngerilor (ALTER TABLE)

    - adaug constrngeri ALTER TABLE nume_tabel ADD [CONSTRAINT nume_constr] tip_constr (coloana);

    - elimin constrngeri ALTER TABLE nume_tabel

  • 4

    DROP [CONSTRAINT nume_constr] tip_constr (coloana); - activare/dezactivare constrngere

    ALTER TABLE nume_tabel MODIFY CONSTRAINT nume_constr ENABLE|DISABLE; sau ALTER TABLE nume_tabel ENABLE| DISABLE nume_constr;

    19. Inserai o nou nregistrare n salariat_*** de forma:

    cod nume prenume data_n functia sef data_ang email salariu cod_dep

    2 N2 P2 11-JUN-1960 economist 1 Sysdate E2 2000 10

    Ce observai? Introducei nregistrarea dar specificnd valoarea NULL pentru coloana sef. 20. ncercai s adugai o constrngere de cheie extern pe cod_dep din salariat_***. Ce observai?

    ALTER TABLE salariat_*** ADD CONSTRAINT cce2_*** FOREIGN KEY (cod_dep) REFERENCES departament_*** (cod_dep);

    21. Inserai o nou nregistrare n departament_***. Apoi adugai constrngerea de cheie extern definit anterior.

    22. Inserai noi nregistrri n salariat_***, respectiv n departament_***. Care trebuie s fie ordinea de inserare?

    cod nume prenume data_n functia sef data_ang email salariu cod_dep

    3 N3 P3 11-JUN-1967 jurist 2 Sysdate E3 2500 20

    23. tergei departamentul 20 din tabelul departament_***. Ce observai? 24. tergei constrngerea cce2_***. Recreai aceast constrngere adugnd opiunea ON DELETE

    CASCADE. 25. tergei departamentul 20 din tabelul departament_***. Ce observai n tabelul salariat_***? Anulai

    modificrile. 26. tergei constrngerea cce2_***. Recreai aceast constrngere adugnd opiunea ON DELETE SET

    NULL. 27. ncercai s tergei departamentul 10 din tabelul departament_***. Ce observai?

    Consultarea dicionarului datelor

    Informaii despre tabelele create se gsesc n vizualizrile : USER_TABLES informaii complete despre tabelele utilizatorului curent.

    cod_dep nume loc 10 Economic Bucuresti

    cod_dep nume loc 20 Juritic Constanta

  • 5

    ALL_TABLES informaii complete despre tabelele tuturor utilizatorilor. COLS informaii despre coloane. TAB informaii de baz despre tabelele existente n schema utilizatorului curent.

    Informaii despre constrngeri gsim n : USER_CONSTRAINTS informaii despre constrngerile definite de utilizatorul curent; ALL_CONSTRAINTS informaii despre cosntrngerile definite de toi utilizatorii.

    Definirea vizualizrilor

    Sintaxa simplificat a comenzii CREATE VIEW este: CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW nume_view [(alias, alias, ..)] AS subcerere [WITH CHECK OPTION [CONSTRAINT nume_constr]] [WITH READ ONLY [CONSTRAINT nume_constr]];

    - FORCE permite crearea vizualizarea nainte de a defini tabelele de baz; - subcererea poate fi orict de complex dar nu poate conine clauza ORDER BY; - WITH CHECK OPTION permite inserarea i modificarea prin intermediul vizualizrii numai a

    liniilor ce sunt accesibile vizualizrii; dac lipsete numele constrngerii atunci sistemul asociaz un nume implicit de tip SYS_Cn acestei constrngeri;

    - WITH READ ONLY asigur c prin intermediul vizualizrii nu se pot executa operaii LMD. Eliminarea unei vizualizri se face prin comanda DROP VIEW : DROP VIEW nume_viz;

    1. S se creeze vizualizarea v_emp_*** care s conin codul i numele salariailor din tabelul emp_***. S se afieze coninutul acesteia. S se insereze o nou nregistrare n aceast vizualizare. Ce observai? S se tearg vizualizarea v_emp_***.

    CREATE VIEW v_emp_*** (cod, nume) AS SELECT employee_id, last_name FROM emp_***;

    INSERT INTO v_emp_*** VALUES (400,N1);

    DROP VIEW v_emp_***;

    2. S se creeze vizualizarea v_emp_*** care s conin codul, numele, emailul, data angajrii, salariul i codul jobului salariailor din tabelul emp_***. S se analizeze structura i coninutul vizualizrii. S se insereze o nou nregistrare n aceast vizualizare. S se verifice c noua nregistrare a fost inserat i n tabelul de baz.

    CREATE VIEW v_emp_*** AS SELECT employee_id, last_name, email, hire_date, salary,job_id FROM emp_***;

    DESC v_emp_***

    SELECT * FROM v_emp_***;

  • 6

    INSERT INTO v_emp_*** VALUES (400,N1,E1,SYSDATE,5000,SA_REP);

    SELECT employee_id, last_name, email, hire_date, salary, job_id FROM emp_***;

    3. S se mreasc cu 1000 salariul angajatului avnd codul 400 din vizualizarea creat anterior. Ce efect va avea aceast aciune asupra tabelului de baz?

    4. S se tearg angajatul avnd codul 400 din vizualizarea creat anterior. Ce efect va avea aceast aciune asupra tabelului de baz?

    5. a) S se creeze vizualizarea v_emp_dept_*** care s conin employee_id, last_name, hire_date, job_id, department_id din tabelul emp_*** i coloana department_name din tabelul dept_***. b) S ncerce inserarea nregistrrii (500, 'N2', 'E2',SYSDATE,SA_REP,30, 'Administrativ') n vizualizarea creat anterior.

    c) Care dintre coloanele vizualizrii v_emp_dept_*** sunt actualizabile?

    SELECT column_name, updatable FROM user_updatable_columns WHERE UPPER(table_name) = UPPER('v_emp_dept_***');

    d) Adugai tabelului emp_*** constrngerea de cheie extern care refer tabelul dept_***, apoi verificai ce coloane din vizualizarea v_emp_dept_*** sunt actualizabile. e) Recreai vizualizarea v_emp_dept_***, apoi verificai ce coloane sunt actualizabile. f) Inserai o linie prin intermediul acestei vizualizri. Obs. Tabelul ale crui coloane sunt actualizabile este protejat prin cheie. g) Ce efect are o operaie de tergere prin intermediul vizualizrii v_emp_dept_***? Comentai.

    6. S se creeze vizualizarea v_dept_*** care s conine codul i numele departamentului, numrul de angajai din departamentul respectiv i suma alocat pentru plata salariilor. Aceast vizualizare permite actualizri?

    CREATE VIEW v_dept_*** (cod, nume, nr_angajati, val_salarii) AS SELECT e.department_id, department_name, COUNT(*) nr_angajati, SUM(salary) val_salarii FROM emp_*** e, dept_*** d WHERE e.department_id = d.department_id GROUP BY e.department_id, department_name;

    7. a) S se creeze vizualizarea v_emp30_*** care s conin numele, emailul, data angajrii, salariul, codul jobului i codul departamentului celor care lucreaz n departamentul 30. n aceast vizualizare nu se va permite modificarea sau inserarea liniilor ce nu sunt accesibile ei. Dai un nume constrngerii.

    CREATE VIEW v_emp30_*** AS SELECT employee_id, last_name, email, hire_date, salary, job_id, department_id FROM emp_*** WHERE department_id=30 WITH CHECK OPTION CONSTRAINT ck_option1_***;

    b) S se listeze structura i coninutul vizualizrii v_emp30_***.

  • 7

    c) S se ncerce prin intermediul vizualizrii inserarea unui angajat n departamentul 10 i a unui angajat n departamentul 30. d) S se ncerce prin intermediul vizualizrii modificarea departamentului unui angajat.

    UPDATE v_emp30_*** SET department_id =20 WHERE employee_id = 11;

    8. S se creeze o vizualizare (v_dept_***) asupra tabelului dept_*** s nu permit efectuarea nici unei operaii LMD. Testai operaiile de inserare, modificare i tergere asupra acestei vizualizri.

    CREATE VIEW v_dept_*** AS SELECT * FROM dept_*** WITH READ ONLY;

    9. S se consulte informaii despre vizualizarea v_dept_***. Folosii vizualizarea dicionarului datelor USER_VIEWS (coloanele VIEW_NAME i TEXT).

    Obs: Coloana TEXT este de tip LONG. n cazul selectrii unei coloane de tip LONG trebuie utilizat comanda SET LONG n pentru a seta numrul de caractere afiate.

    SET LONG 200

    SELECT view_name, text FROM user_views WHERE UPPER(view_name)=UPPER(v_dept_***);

    Definirea secvenelor

    Sintaxa comenzii CREATE SEQUENCE este: CREATE SEQUENCE nume_secven

    [INCREMENT BY n] [START WITH valoare_start] [ {MAXVALUE valoare_maxim | NOMAXVALUE} ] [ {MINVALUE valoare_minim | NOMINVALUE} ] [ {CYCLE | NOCYCLE} ] [ {CACHE n | NOCACHE} ];

    tergerea secvenelor se realizeaz cu ajutorul comenzii DROP SEQUENCE. DROP SEQUENCE nume_secv;

    10. S se creeze o secven care are pasul de incrementare 10 i ncepe de la 10, are ca valoare maxim 10000 i nu cicleaz.

    CREATE SEQUENCE sec_*** INCREMENT BY 10 START WITH 10 MAXVALUE 10000 NOCYCLE;

    11. S se modifice toate liniile din tabelul emp_***, regenernd codul angajailor astfel nct s utilizeze secvena sec_emp***. S se anuleze modificrile.

  • 8

    UPDATE emp_*** SET employee_id = sec_emp***.NEXTVAL; ROLLBACK;

    12. S se introduc un nou salariat n tabelul emp_*** folosindu-se pentru codul salariatului secvena creat. 13. S se afieze valoarea curent a secvenei.

    SELECT sec_***.CURRVAL valoare FROM DUAL;

    Exerciiu a) Creai o secven pentru generarea codurilor de departamente, seq_dept_***. Secvena va ncepe de la 200, va crete cu 10 la fiecare pas i va avea valoarea maxim 20000, nu va cicla. b) S se selecteze informaii despre secvenele utilizatorului curent (nume, valoare minim, maxim, de incrementare, ultimul numr generat). Se va utiliza vizualizarea user_sequences. c) S se insereze o nregistrare nou n DEPT_*** utiliznd secvena creat. d) S se selecteze valoarea curent a secvenei. e) S se tearg secvena.

    Definirea indecilor Sintaxa comenzii CREATE INDEX: CREATE [UNIQUE] INDEX nume_index ON tabel (coloana1 [, coloana2]); Modificarea unui index se face prin comanda ALTER INDEX. Eliminarea unui index se face prin comanda: DROP INDEX nume_index;

    14. S se creeze un index neunic, emp_last_name_idx_***, asupra coloanei last_name din tabelul emp_***.

    15. S se creeze indeci unici asupra codului angajatului (employee_id) i asupra combinaiei last_name, first_name, hire_date.

    16. Creai un index neunic asupra coloanei department_id din emp_*** pentru a eficientiza joinurile dintre acest tabel i dept_***.

    Definirea sinonimelor Comanda pentru crearea sinonimelor este:

    CREATE [PUBLIC] SYNONYM nume_sinonim FOR obiect;

    Eliminarea sinonimelor se face prin comanda DROP SYNONYM nume_sinonim; 17. Creai un sinonim public se_*** pentru tabelul emp_***. 18. Creai un sinonim pentru vizualizarea v_dept_***. 19. Utiliznd sinonimele create anterior, afiai informaii depre salarii i despre departamente.

  • 9

    Limbajul de interogare al datelor (DQL). SELECT

    CERERI MONOTABEL

    1. Analizai sintaxa simplificat a comenzii SELECT. Care dintre clauze sunt obligatorii? SELECT { [ {DISTINCT | UNIQUE} | ALL] lista_campuri | *} FROM [nume_schem.]nume_obiect ] [, [nume_schem.]nume_obiect ] [WHERE condiie_clauza_where] [GROUP BY expresie [, expresie ] [HAVING condiie_clauza_having] ] [ORDER BY {expresie | poziie} [, {expresie | poziie} ] ]

    2. S se listeze structura tabelelor din schema HR (EMPLOYEES, DEPARTMENTS, JOB_HISTORY, JOBS, LOCATIONS, COUNTRIES, REGIONS), observnd tipurile de date ale coloanelor.

    Obs: Se va utiliza comanda SQL*Plus DESCRIBE nume_tabel 3. S se listeze coninutul tabelelor din schema considerat, afind valorile tuturor cmpurilor. Obs: Se va utiliza comanda SQL SELECT * FROM nume_tabel; 4. S se obin nc o dat rezultatul cererii precedente, fr a rescrie cererea. Obs: Ultima comand SQL lansat de ctre client este pstrat n buffer-ul SQL. Pentru rularea acesteia se utilizeaz / sau RUN. 5. Listai structura tabelului EMPLOYEES i apoi dai comanda RUN (sau /). Ce observai? Comenzile

    SQL*Plus sunt pstrate n buffer? DESC employees RUN 6. S se afieze codul angajatului, numele, codul job-ului, data angajrii. Salvai instruciunea SQL ntr-un

    fiier numit p1_14.sql. Obs: Pentru salvarea ultimei comenzi SQL se utilizeaz comanda SAVE. Precizarea extensiei .sql a fiierului nu este obligatorie.

    SELECT employee_id, last_name, job_id, hire_date FROM employees; SAVE z:\\ p1_14.sql 7. Reexecutai cererea folosind fiierul p1_14.sql.

    START z:\\ p1_14.sql sau

    @ z:\\ p1_14.sql 8. Editai fiierul p1_14.sql, adugnd coloanelor cte un alias (cod, nume, cod job, data angajarii).

    EDIT z:\\ p1_14.sql 9. S se listeze, cu i fr duplicate, codurile job-urilor din tabelul EMPLOYEES. Obs. DISTINCT = UNIQUE 10. S se afieze numele concatenat cu prenumele, separate prin spaiu. Etichetai coloana Nume si

    prenume.

  • 10

    Obs: Operatorul de concatenare este ||. irurile de caractere se specific ntre apostrofuri (NU ghilimele, caz n care ar fi interpretate ca alias-uri). SELECT last_name|| ' ' || first_name " Nume si prenume " FROM employees;

    11. S se listeze numele i salariul angajailor care ctig mai mult de 10000 $. SELECT last_name, salary FROM employees WHERE salary > 10000;

    12. S se modifice cererea anterioar astfel nct s afieze numele i salariul pentru toi angajaii al cror salariu este cuprins ntre 5000$ i10000$. Obs: Pentru testarea apartenenei la un domeniu de valori se poate utiliza operatorul [NOT] BETWEEN valoare1 AND valoare2 SELECT last_name, salary FROM employees WHERE salary BETWEEN 5000 AND 10000;

    13. S se creeze o cerere pentru a afia numele angajatului i numrul departamentului pentru angajatul 104. 14. S se afieze numele i salariul pentru toi angajaii din departamentele 10 sau 30, n ordine alfabetic a

    numelor. Obs: Apartenena la o mulime finit de valori se poate testa prin intermediul operatorului IN, urmat de lista valorilor ntre paranteze i separate prin virgule: expresie IN (valoare_1, valoare_2, , valoare_n)

    15. S listeze numele i salariile angajailor care ctig mai mult de 10000 $ i lucreaz n departamentul 10 sau 30. Se vor eticheta coloanele drept Angajat si Salariu lunar.

    16. Care este data curent? Obs: Pseudocoloana care returneaz data curent este SYSDATE. Pentru completarea sintaxei obligatorii a comenzii SELECT, se utilizeaz tabelul DUAL:

    SELECT SYSDATE FROM dual; Datele calendaristice pot fi formatate cu ajutorul funciei TO_CHAR(data, format), unde formatul poate fi alctuit dintr-o combinaie a urmtoarelor elemente:

    Element Semnificaie D Numrul zilei din sptmn (duminic=1;

    luni=2; smbt=6). DD Numrul zilei din lun. DDD Numrul zilei din an. DY Numele zilei din sptmn, printr-o

    abreviere de 3 litere (MON, THU etc.) DAY Numele zilei din sptmn, scris n

    ntregime. MM Numrul lunii din an. MON Numele lunii din an, printr-o abreviere de 3

    litere (JAN, FEB etc.) MONTH Numele lunii din an, scris n ntregime. Y Ultima cifr din an YY, YYY, YYYY Ultimele 2, 3, respectiv 4 cifre din an. YEAR Anul, scris n litere (ex: two thousand

    four). HH12, HH24 Orele din zi, ntre 0-12, respectiv 0-24.

  • 11

    MI Minutele din or. SS Secundele din minut. SSSSS Secundele trecute de la miezul nopii.

    17. S se afieze numele i data angajrii pentru fiecare salariat care a fost angajat n 1987. Se cer 2 soluii: una n care se lucreaz cu formatul implicit al datei i alta prin care se formateaz data.

    Varianta1: SELECT first_name, last_name, hire_date FROM employees WHERE hire_date LIKE (%87); Varianta 2: SELECT first_name, last_name, hire_date FROM employees WHERE TO_CHAR(hire_date, YYYY)=1987; Sunt obligatorii ghilimelele de la irul 1987? Ce observai? 18. S se afieze numele i job-ul pentru toi angajaii care nu au manager. SELECT last_name, job_id FROM employees WHERE manager_id IS NULL; 19. S se afieze numele, salariul i comisionul pentru toi salariaii care ctig comisioane. S se sorteze

    datele n ordine descresctoare a salariilor, iar pentru cei care au acelai salariu n ordine cresctoare a comisioanelor.

    SELECT last_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC, commission_pct ASC; 20. S se listeze numele tuturor angajailor care au a treia litera din nume 'a'. Obs: Pentru a forma mtile de caractere utilizate mpreun cu operatorul LIKE cu scopul de a compara

    irurile de caractere, se utilizeaz: % - reprezentnd orice ir de caractere, inclusiv irul vid; _ (underscore) reprezentnd un singur caracter. SELECT DISTINCT last_name FROM employees WHERE last_name LIKE '__a%';

    21. Folosind data curent s se afieze urmtoarele informaii: - numele zilei, numrul zilei din sptmn, numrul zilei din luna, respectiv numrul zilei din an; - numrul lunii din an, numele lunii cu abreviere la 3 caractere, respectiv numele complet al lunii; - ora curent (ora, minute, secunde).

    22. S se listeze numele departamentelor care funcioneaz n locaia avnd codul 1700 i al cror manager este cunoscut.

    23. S se afieze codurile departamentelor n care lucreaz salariai. 24. S se afieze numele i prenumele salariailor angajai n luna mai 1987. 25. S se listeze codurile angajailor care au avut i alte joburi fa de cel prezent. S se ordoneze rezultatul

    descresctor dup codul angajatului. 26. S se afieze numele i data angajrii pentru cei care lucreaz n departamentul 80 i au fost angajai n

    luna martie a anului 1997. 27. S se afieze numele joburilor care permit un salariu cuprins ntre 8300$ i 14000$.

  • 12

    28. Care este grila de salarizare pentru un salariu de 10000$? 29. S se listeze numele tuturor angajailor care au 2 litere 'L' n nume i lucreaz n departamentul 30 sau

    managerul lor este 123. 30. S se afieze numele, job-ul i salariul pentru toi salariaii al cror job conine irul 'CLERK' sau 'REP'

    i salariul nu este egal cu 1000, 2000 sau 3000 $. 31. S se afieze numele, salariul i comisionul pentru toi angajaii al cror salariu este mai mare dect de 5

    ori valoarea comisionului (salary*commission_pct*5).

    FUNCII SQL (single-row)

    Principalele funcii SQL pot fi clasificate n urmtoarele categorii: Funcii single-row Funcii multiple-row (funcii agregat)

    Funciile single-row returneaz cte o linie rezultat pentru fiecare linie a tabelului sau vizualizrii interogate. Aceste funcii pot aprea n listele SELECT, clauzele WHERE, START WITH, CONNECT BY i HAVING.

    1. Analizai urmtoarele funcii pentru prelucrarea irurilor de caractere:

    Funcie Semnificaie Exemplu

    LOWER (expresie) Convertete un ir de caractere la minuscule. LOWER ('AbCdE') = 'abcde'

    UPPER (expresie) Convertete un ir de caractere la majuscule. UPPER ('AbCdE') = 'ABCDE'

    INITCAP (expresie) Convertete un ir de caractere la un ir care ncepe cu majuscul i continu cu minuscule.

    INITCAP ('AbCdE') = 'Abcde'

    SUBSTR (expresie, m[, n])

    Extrage din expresia de tip ir de caractere, n caractere ncepnd cu poziia m. Dac lipsete argumentul n, atunci extrage toate caracterele pn la sfritul irului. Dac m este negativ numrtoarea poziiilor ncepe de la sfritul irului de caractere spre nceput.

    SUBSTR ('AbCdE', 2, 2) = 'bC' SUBSTR ('AbCdE', 2) = 'bCdE' SUBSTR ('AbCdE', -3,2) = 'Cd' SUBSTR ('AbCdE', -3) = 'CdE'

    LENGTH (expresie) Returneaz numrul de caractere al expresiei. LENGTH ('AbCdE') = 5

    INSTR (expresie, expr1[, m][, n])

    Returneaz poziia la care se gsete a n-a ocurent a expresiei 'expr1' n cadrul expresiei 'expresie', cutarea ncepnd de la poziia m. Daca m sau n lipsesc, valorile implicite sunt 1 pentru ambele.

    INSTR (LOWER('AbC aBcDe'), 'ab', 5, 2) = 0 INSTR (LOWER('AbCdE aBcDe'), 'ab', 5) = 7

    LTRIM (expresie[, expr1]) sau RTRIM (expresie[, expr1])

    Reversul funciilor LPAD, RPAD. Truncheaz expresia caracter la stnga sau la dreapta prin eliminarea succesiv a caracterelor din expresia expr1. Implicit, daca lipsete, expr1

    RTRIM ('abcdeXXXX', 'X') = 'abcde' LTRIM (' abcde') = 'abcde'

  • 13

    este ' ' un spaiu.

    TRIM (LEADING | TRAILING | BOTH caractere_trim FROM expresie)

    Permite eliminarea caracterelor specificate (caractere_trim) de la nceputul (leading) , sfritul (trailing) sau din ambele pri, dintr-o expresie caracter data.

    TRIM (LEADING 'X' FROM 'XXXabcdeXXX') = 'abcdeXXX' TRIM (TRAILING 'X' FROM 'XXXabcdeXXX') = 'XXXabcde' TRIM ( BOTH 'X' FROM 'XXXabcdeXXX') = 'abcde' TRIM (' abcde ') = 'abcde'

    2. S se afieze pentru fiecare angajat din departamentul 20 un ir de caractere de forma "Funcia salariatului {prenume} {nume} este {cod functie}". S se afieze prenumele cu iniiala litera mare, iar numele cu litere mari (Stephen KING), iar codul funciei s se afieze cu litere mici.

    3. S se afieze pentru angajatul cu numele 'HIGGINS' codul, numele i codul departamentului. Cum se scrie condiia din WHERE astfel nct s existe sigurana ca angajatul 'HIGGINS' va fi gsit oricum ar fi fost introdus numele acestuia? Cutarea trebuie s nu fie case-sensitive, iar eventualele blank-uri care preced sau urmeaz numelui trebuie ignorate. UPPER(TRIM(last_name))='HIGGINS';

    4. S se afieze pentru toi angajaii al cror nume se termin n 'n', codul, numele, lungimea numelui i poziia din nume n care apare prima data litera 'a'. Asociai aliasuri coloanelor returnate de cerere.

    SELECT employee_id, last_name, LENGTH(last_name), INSTR(UPPER(last_name), 'A') FROM employees WHERE SUBSTR(last_name,-1)='n';

    5. Analizai urmtoarele funcii aritmetice:

    Funcie Semnificaie Exemplu

    ROUND (expresie [, n]) Returneaz valoarea rotunjit a expresiei pn la n zecimale. Daca n este negativ sunt rotunjite cifre din stnga virgulei. Valoarea implicit pentru n este 0.

    ROUND(1.6) = 2 ROUND(1.4) = 1 ROUND (1234.56,1) = 1234.6 ROUND (1230.56, -2) = 1200 ROUND (1260.56, -2) = 1300

    MOD (m,n) Returneaz restul mpririi lui m la n. MOD (11, 4) = MOD (11, -4) = 3 MOD(-11, 4) = MOD (-11, -4) = -3

    6. S se afieze detalii despre salariaii care au lucrat un numr ntreg de sptmni pn la data curent. MOD(ROUND(SYSDATE hire_date), 7)=0; 7. S se afieze numele, salariul i numrul de mii al salariului rotunjit la 2 zecimale pentru cei care nu au

    salariul divizibil cu 1000. 8. Analizai urmtoarele operaii pe expresii de tip dat calendaristic:

    Operaie Tipul de date al rezultatului Descriere

    date -/+ number Date Scade/Adaug un numr de zile dintr-o / la o dat.

    date1 - date2 Number ntoarce numrul de zile dintre dou date calendaristice. date +/- number/24 Date Scade/Adaug un numr de ore la o / dintr-o dat calendaristic.

    9. S se afieze data (luna, ziua, ora, minutul si secunda) de peste 10 zile.

  • 14

    SYSDATE+10 10. S se afieze numrul de zile rmase pn la sfritul anului.

    ROUND(TO_DATE(31-DEC-2009)-SYSDATE) 11. a. S se afieze data de peste 12 ore.

    SYSDATE+12/24 b. S se afieze data de peste 5 minute. SYSDATE+1/288 12. Analizai urmtoarele funcii pentru prelucrarea datelor calendaristice:

    Funcie Semnificaie Exemplu SYSDATE ntoarce data i timpul curent

    MONTHS_BETWEEN (date1, date2)

    Returneaz numrul de luni dintre data date1 i data date2. Rezultatul poate fi pozitiv sau negativ dup cum date1 este mai recent sau nu fa de date2. Zecimalele reprezint pari dintr-o luna!

    ROUND(MONTHS_BETWEEN (SYSDATE + 31, SYSDATE)) = 1

    ADD_MONTHS (date, n) Adaug n luni la o data specificat. Valoarea n trebuie s fie ntreag (pozitiv sau negativ).

    MONTHS_BETWEEN (ADD_MONTHS(SYSDATE, 3), SYSDATE) = 3

    NEXT_DAY (date, char) Returneaz data corespunztoare primei zile a sptmnii specificate (char) care urmeaz dup date.

    NEXT_DAY('15-dec-2006','Monday') = '18-dec-2006' NEXT_DAY ('15-dec-2006',1) = '18-dec-2006'

    13. S se afieze numele angajatului, data angajrii i data negocierii salariului, care a avut loc n prima zi de Luni, dup 6 luni de serviciu. Etichetai aceast coloan Negociere. NEXT_DAY(ADD_MONTHS(hire_date, 6), Monday)

    14. Pentru fiecare angajat s se afieze numele i numrul de luni de la data angajrii. Etichetai coloana Luni lucrate. S se ordoneze rezultatul dup numrul de luni lucrate. Se va rotunji numrul de luni la cel mai apropiat numr ntreg. SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) Luni lucrate FROM employees ORDER BY MONTHS_BETWEEN(SYSDATE, hire_date);

    SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) Luni lucrate FROM employees ORDER BY Luni lucrate;

    SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) Luni lucrate FROM employees

    ORDER BY 2; 15. Analizai urmtoarele funcii de conversie:

    Obs. Conversiile implicite asigurate de server-ul Oracle sunt: de la VARCHAR2 sau CHAR la NUMBER; de la VARCHAR2 sau CHAR la DATE; de la NUMBER la VARCHAR2 sau CHAR; de la DATE la VARCHAR2 sau CHAR.

  • 15

    SELECT last_name FROM employees WHERE TO_CHAR(hire_date,'yyyy')=1994; SELECT last_name FROM employees WHERE hire_date='07-JUN-1994'; SELECT employee_id||' '||last_name||' '||hire_date FROM employees WHERE department_id=10; Conversiile explicite se realizeaz cu ajutorul funciilor de tip TO_{tip}

    Funcie Semnificaie Exemplu

    TO_CHAR (expr_number_sau_date[, format][, nlsparameters])

    Convertete o valoare de tip numeric sau dat calendaristic, la un ir de caractere conform cu formatul specificat sau cu setrile naionale specificate (NLS - National Language Support). Daca formatul sau parametrii lipsesc se utilizeaz formatul i parametrii implicii. Formatul este case sensitive.

    TO_CHAR('3') = ' 3' TO_CHAR(-12) = '-12' TO_CHAR(sysdate, 'DDMMYYYY') = ' 09122004' TO_CHAR (sysdate + 365 * 57, 'ddmmyyyy') = ' 25112061'

    TO_NUMBER (expr_char[, format][, nlsparameters])

    Convertete o valoare de tip ir de caractere la o valoare numeric conform cu formatul specificat. Dac formatul sau parametrii lipsesc se utilizeaz formatul i parametrii implicii.

    TO_NUMBER ('-12.22', 'S99.99') = -12.22

    TO_DATE (expr_char[, format][, nlsparameters])

    Convertete o valoare de tip ir de caractere la o valoare de tip dat calendaristic n conformitate cu formatul specificat. Dac formatul sau parametrii lipsesc se utilizeaz formatul i parametrii implicii.

    TO_DATE ('15-feb-2006','dd-mon-yyyy')

    16. S se afieze numele i prenumele pentru toi angajaii care s-au angajat n luna mai. 17. Analizai urmtoarele funcii SQL:

    Funcie Semnificaie Exemplu

    NVL (expr1, expr2)

    Returneaz expr1 dac aceasta nu este NULL, expr2 n caz contrar. Cele 2 expresii trebuie s aib acelai tip sau expr2 s permit conversia implicit la tipul expresiei expr1.

    NVL(NULL, 1) = 1 NVL(2, 1) = 2 NVL('c', 1) = 'c' -- face conversie NVL(1, 'c') -- eroare --nu face conversie

    NVL2 (expr1, expr2, expr3) Dac expr1 este nenul atunci returneaz expr2, altfel Returneaz expr3

    NVL2 (1, 2, 3) = 2 NVL2 (NULL, 2, 3) = 3

    18. S se afieze numele angajailor i comisionul. Dac un angajat nu ctig comision, s se scrie Fara comision. Etichetai coloana Comision. NVL(TO_CHAR(commission_pct), Fara comision)

    19. S se listeze numele, salariul i comisionul tuturor angajailor al cror venit lunar depete 10000$. salary * NVL(commission_pct, 0) venit_lunar

    20. Analizai expresia CASE i funcia DECODE:

    Funcie/Expresie Semnificaie Exemplu CASE expr WHEN expr_bool1 THEN return_expr1

    n funcie de valoarea unei expresii returneaz valoarea primei perechi

  • 16

    [WHEN expr_bool2 THEN return_expr2 ...

    WHEN expr_booln THEN return_exprn ] [ELSE return_expr] END

    WHEN .. THEN care se potrivete sau dac nu se potrivete nici una expresia din ELSE. Nu se poate specifica NULL pentru toate expresiile de returnat. (return_expri). Toate expresiile trebuie sa aib acelai tip de date

    DECODE (expr, expr_cautare1, expr_rezultat1, [expr_cautare2, expr_rezultat2, ..

    expr_cautaren, expr_rezultatn, ] [rezultat_implicit])

    Decodific valoarea expresiei. Dac valoarea expresiei este expr_cautarei atunci e returnat expr_rezultati. Dac nu se potrivete nici o expresie de cutare atunci e returnat rezultat_implicit.

    DECODE (1, 1, 2, 3) = 2 DECODE (2, 1, 2, 3) = 3 DECODE (3, 1, 2, 3) = 3

    21. S se afieze numele, codul funciei, salariul i o coloana care s arate salariul dup mrire. Se tie c pentru IT_PROG are loc o mrire de 10%, pentru ST_CLERK 15%, iar pentru SA_REP o mrire de 20%. Pentru ceilali angajai nu se acord mrire. S se denumeasc coloana "Salariu revizuit".

    SELECT last_name, job_id, salary, DECODE(job_id, IT_PROG, salary*1.1, ST_CLERK, salary*1.15, SA_REP, salary*1.2, salary ) salariu revizuit FROM employees;

    SELECT last_name, job_id, salary, CASE job_id WHEN IT_PROG THEN salary* 1.1 WHEN ST_CLERK THEN salary*1.15 WHEN SA_REP THEN salary*1.2 ELSE salary END salariu revizuit FROM employees;

    SELECT last_name, job_id, salary, CASE WHEN job_id= IT_PROG THEN salary* 1.1 WHEN job_id=ST_CLERK THEN salary*1.15 WHEN job_id =SA_REP THEN salary*1.2 ELSE salary END salariu revizuit FROM employees; 22. S se afieze numele salariatului i codul departamentului n care acesta lucreaz. Dac exist salariai

    care nu au un cod de departament asociat, atunci pe coloana id_depratment s se afieze: textul fara departament; valoarea zero.

    23. a. S se afieze numele angajailor care nu au manager. b. S se afieze numele angajailor i codul managerilor lor. Pentru angajaii care nu au manager s apar textul nu are sef.

    24. S se afieze numele salariatului i: venitul anual dac are comision; salariul dac nu are comision.

    Se va utiliza funcia NVL2. 25. S se afieze numele salariatului, salariul i salariul revizuit astfel:

  • 17

    - dac lucreaz de mai mult de 200 de luni atunci salariul va fi mrit cu 20%; - dac lucreaz de mai mult de 150 de luni, dar mai puin de 200 de luni, atunci salariul va fi mrit cu 15%; - dac lucreaz de mai mult de 100 de luni, dar mai puin de 150 de luni, atunci salariul va fi mrit cu 10%; - altfel, salariul va fi mrit cu 5%.

    CERERI MULTITABEL, SUBCERERI

    Tipuri de join: equijoin (se mai numete inner join sau simple join) - compunerea a dou tabele diferite dup o

    condiie ce conine operatorul de egalitate. SELECT last_name, department_name, location_id, e.department_id

    FROM employees e, departments d WHERE e.department_id = d.department_id;

    Obs: Numele sau alias-urile tabelelor sunt obligatorii n dreptul coloanelor care au acelai nume n mai multe tabele.

    nonequijoin - compunerea a dou relaii tabele dup o condiie oarecare, ce NU conine operatorul de egalitate.

    SELECT last_name, salary, grade_level FROM employees, job_grades WHERE salary BETWEEN lowest_sal AND highest_sal;

    outerjoin - compunerea extern a dou tabele diferite completnd una dintre relaii cu valori NULL acolo unde nu exist n aceasta nici un tuplu ce ndeplinete condiia de corelare. Relaia completat cu valori NULL este cea n dreptul creia apare (+). Operatorul (+) poate fi plasat n orice parte a condiiei de join, dar nu n ambele pri. Full outer join = Left outer join UNION Right outer join.

    SELECT last_name, department_name,location_id FROM employees e, departments d WHERE e.department_id(+) = d.department_id;

    selfjoin - compunerea extern a unui tabel cu el nsui dup o condiie dat. SELECT sef.last_name, angajat.last_name FROM employees sef, employees angajat WHERE sef.employee_id = angajat.manager_id ORDER BY sef.last_name;

    1. Pentru fiecare angajat s se afieze numele, codul i numele departamentului. SELECT last_name, e.department_id, department_name FROM employees e, departments d WHERE e.department_id = d.department_id;

    2. S se afieze numele angajatului, numele departamentului pentru toi angajaii care ctig comision. 3. S se listeze numele job-urile care exist n departamentul 30.

    SELECT DISTINCT job_title FROM employees e, jobs j WHERE e.job_id = j.job_id AND department_id = 30;

    4. S se afieze numele, job-ul i numele departamentului pentru toi angajaii care lucreaz n Seattle. 5. S se afieze numele, salariul, data angajrii i numele departamentului pentru toi programatorii care

    lucreaz n America. region_name = Americas

  • 18

    job_title = Programmer 6. S se afieze numele salariailor i numele departamentelor n care lucreaz. Se vor afia i salariaii care

    nu lucreaz ntr-un departament (right outher join). SELECT last_name, department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+);

    7. S se afieze numele departamentelor i numele salariailor care lucreaz n ele. Se vor afia i departamentele care nu au salariai (left outher join).

    8. S se afieze numele, job-ul, numele departamentului, salariul i grila de salarizare pentru toi angajaii. 9. S se afieze codul angajatului i numele acestuia, mpreun cu numele i codul efului su direct. Se vor

    eticheta coloanele Ang#, Angajat, Mgr#, Manager. S se salveze instruciunea ntr-un fiier numit p3_9.sql. SELECT a.employee_id Ang#, a.last_name Angajat, b.employee_id Mgr#, b.last_name Manager FROM employees a, employees b WHERE a.manager_id = b. employee_id;

    10. S se modifice p3_9.sql pentru a afia toi salariaii, inclusiv pe cei care nu au ef. 11. S se afieze numele salariatului i data angajrii mpreun cu numele i data angajrii efului direct

    pentru salariaii care au fost angajai naintea efilor lor. Se vor eticheta coloanele Angajat, Data_ang, Manager si Data_mgr.

    12. Pentru fiecare angajat din departamentele 20 i 30 s afieze numele, codul departamentului i toi colegii si (salariaii care lucreaz n acelai departament cu el). Se vor eticheta coloanele corespunztor.

    13. S se afieze numele i data angajrii pentru salariaii care au fost angajai dup Fay. SELECT last_name, hire_date FROM employees WHERE hire_date > (SELECT hire_date

    FROM employees WHERE last_name = Fay);

    sau

    SELECT a.last_name, a.hire_date FROM employees a, employees b WHERE UPPER(b.last_name)=FAY AND a.hire_date>b.hire_date;

    14. Scriei o cerere pentru a afia numele i salariul pentru toi colegii (din acelai departament) lui Fay. Se va exclude Fay.

    15. S se afieze codul departamentului, codul i numele angajailor care lucreaz n acelai departament cu cel puin un angajat al crui nume conine litera T. S se ordoneze dup codul departamentului. SELECT employee_id, last_name, department_id FROM employees WHERE department_id IN (SELECT DISTINCT department_id

    FROM employees WHERE UPPER(last_name) LIKE %T%)

    ORDER BY department_id; 16. S se afieze numele i salariul angajailor condui direct de Steven King.

    SELECT last_name, salary FROM employees WHERE manager_id = (SELECT employee_id

    FROM employees

  • 19

    WHERE UPPER(last_name) ='KING' AND UPPER(first_name) ='STEVEN' );

    17. S se afieze numele i job-ul tuturor angajailor din departamentul Sales.

    18. S se afieze numele angajailor, numrul departamentului i job-ul tuturor salariailor al cror departament este localizat n Seattle. SELECT last_name, job_id, department_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = (SELECT location_id FROM locations WHERE city = Seattle)); Rezolvai aceast problem utiliznd join-uri.

    19. S se afle dac exist angajai care nu lucreaz n departamentul Sales i al cror salariu i comision coincid cu salariul i comisionul unui angajat din departamentul Sales. SELECT last_name, salary, commission_pct, department_id FROM employees WHERE (salary, commission_pct) IN (SELECT salary, commission_pct

    FROM employees e, departments d WHERE e.department_id = d.department_id AND department_name = Sales)

    AND department_id (SELECT department_id FROM departments WHERE department_name = Sales);

    20. Scriei o cerere pentru a afia numele, numele departamentului i salariul angajailor care nu ctig comision, dar al cror manager coincide cu managerul unui angajat care ctig comision.

    21. Scriei o cerere pentru a afia angajaii care ctig mai mult dect oricare funcionar. Sortai rezultatele dup salariu, n ordine descresctoare. SELECT last_name, salary, job_id FROM employees WHERE salary > (SELECT MAX(salary)

    FROM employees WHERE job_id LIKE '%CLERK')

    ORDER BY salary DESC; 22. S se afieze codul, numele i salariul tuturor angajailor care ctig mai mult dect salariul mediu. 23. S se afieze pentru fiecare salariat angajat n luna martie numele su, data angajrii i numele jobului. 24. S se afieze pentru fiecare salariat al crui ctig total lunar este mai mare dect 12000 numele su,

    ctigul total lunar i numele departamentului n care lucreaz. 25. S se afieze pentru fiecare angajat codul su i numele joburilor sale anterioare, precum i intervalul de

    timp n care a lucrat pe jobul respectiv. 26. S se modifice cererea de la punctul 25 astfel nct s se afieze i numele angajatului, respectiv codul

    jobului su curent. 27. S se modifice cererea de la punctul 26 astfel nct s se afieze i numele jobului su curent. 28. S se afieze salariaii care au acelai manager ca i angajatul avnd codul 140. 29. S se afieze numele departamentelor care funcioneaz n America.

  • 20

    Funcii multiple-row (grup). Gruparea datelor.

    Aceste tipuri de funcii pot fi utilizate pentru a returna informaia corespunztoare fiecruia dintre grupurile obinute n urma divizrii liniilor tabelului cu ajutorul clauzei GROUP BY. Pot aprea n clauzele SELECT, ORDER BY i HAVING. Server-ul Oracle aplic aceste funcii fiecrui grup de linii i returneaz un singur rezultat pentru fiecare mulime. Exemple de funcii grup: AVG, SUM, MAX, MIN, COUNT etc. Tipurile de date ale argumentelor funciilor grup pot fi CHAR, VARCHAR2, NUMBER sau DATE. Funciile AVG i SUM, opereaz numai asupra valorilor numerice. Funciile MAX i MIN pot opera asupra valorilor numerice, caracter sau dat calendaristic.

    Toate funciile grup, cu excepia lui COUNT(*), ignor valorile null. COUNT(expresie) returneaz numrul de linii pentru care expresia dat nu are valoarea null. Funcia COUNT returneaz un numr mai mare sau egal cu zero i nu ntoarce niciodat valoarea null.

    Cnd este utilizat clauza GROUP BY, server-ul sorteaz implicit mulimea rezultat n ordinea cresctoare a valorilor coloanelor dup care se realizeaz gruparea.

    Absena clauzei GROUP BY conduce la aplicarea funciei grup pe mulimea tuturor liniilor tabelului.

    n clauza GROUP BY se trec obligatoriu toate coloanele prezente n clauza SELECT, care nu sunt argument al funciilor grup.

    1. S se afieze cel mai mare salariu, cel mai mic salariu, suma i media salariilor tuturor angajatilor. Etichetai coloanele Maxim, Minim, Suma, respectiv Media. S se rotunjeasc rezultatele. SELECT MIN(salary) min, MAX(salary) max, SUM(salary) suma, ROUND(AVG(salary)) media FROM employees;

    2. Utiliznd funcia grup COUNT s se determine: a. numrul total de angajai; b. numrul de angajai care au manager;

    c. numrul de manageri. 3. S se afieze diferena dintre cel mai mare i cel mai mic salariu. Etichetai coloana Diferenta. 4. S se listeze numrul de angajai din departamentul avnd codul 50. 5. Cai angajai din departamentul 80 ctig comision? 6. S se selecteze valoarea medie i suma salariilor pentru toi angajaii care sunt reprezentani de vnzri

    (SA_MAN, SA_REP). 7. S se selecteze data angajrii primei persoane care a fost angajat de companie. 8. S se afieze numrul de angajai pentru fiecare job.

    SELECT job_id, COUNT(employee_id) nr_angajati FROM employees GROUP BY job_id;

    9. S se afieze minimul, maximul, suma i media salariilor pentru fiecare departament. 10. S se afieze codul departamentului i media salariilor pentru fiecare job din cadrul acestuia.

    SELECT department_id, job_id, AVG(salary) FROM employees GROUP BY department_id, job_id;

    11. a. S se afieze codul departamentelor pentru care salariul minim depete 5000$. SELECT department_id, MIN(salary) FROM employees

    GROUP BY department_id HAVING MIN(salary)>5000;

  • 21

    b. S se modifice cererea anterioar astfel nct s se afieze i oraul n care funcioneaz aceste departamente.

    12. S se obin codul departamentelor i numrul de angajai al acestora pentru departamentele care au cel puin 10 angajai.

    13. S se obin codul departamentelor i suma salariilor angajailor care lucreaz n acestea, n ordine descresctoare dup sum. Se consider angajaii care au comision i departamentele care au mai mult de 5 angajai.

    14. S se obin job-ul pentru care salariul mediu este minim. SELECT job_id

    FROM employees GROUP BY job_id

    HAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM employees

    GROUP BY job_id); 15. S se afieze cel mai mare dintre salariile medii pe departamente. 16. a. S se afieze codul, numele departamentului i suma salariilor pe departamente. SELECT d.department_id, department_name,a.suma FROM departments d, (SELECT department_id ,SUM(salary) suma FROM employees GROUP BY department_id) a WHERE d.department_id =a.department_id;

    b. Dai o alt metod de rezolvare a acestei probleme.

    17. a. Scriei o cerere pentru a afia numele departamentului, numrul de angajai i salariul mediu pentru angajaii din acel departament. Coloanele vor fi etichetate Departament, Nr. angajati, Salariu Mediu.

    SELECT department_name Departament, (SELECT COUNT(employee_id) FROM employees WHERE department_id = d.department_id ) Nr. angajati, (SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) Salariu mediu FROM departments d; b. Dai o alt metod de rezolvare pentru problema anterioar.

    18. S se creeze o cerere prin care s se afieze numrul total de angajai i, din acest total, numrul celor care au fost angajai n 1997, 1998, 1999 i 2000. Datele vor fi afiate n forma urmtoare:

    Total 1997 1998 1999 2000 --------------------------------------------------------------

    50 10 5 25 1

    SUM(DECODE(TO_CHAR(hire_date,'yyyy'),1997,1,0))

  • 22

    Operatorii ROLLUP i CUBE

    Clauza GROUP BY permite gruparea liniilor selectate dup valorile expresiilor precizate n aceasta. Pentru fiecare grup, va fi returnat o singur linie de informaie. Clauza GROUP BY poate produce grupri superagregat utiliznd extensiile CUBE sau ROLLUP.

    ROLLUP grupeaz liniile selectate pe baza valorilor primelor n, n - 1, , 0 expresii din specificaia GROUP BY i returneaz o singur linie pentru fiecare grup. ROLLUP creeaz grupri prin deplasarea ntr-o singur direcie, de la dreapta la stnga, de-a lungul listei de coloane specificate n clauza GROUP BY. Apoi, se aplic funcia agregat acestor grupri. Dac sunt specificate n expresii n operatorul ROLLUP, numrul de grupri generate va fi n + 1. Liniile care se bazeaz pe valoarea primelor n expresii se numesc linii obinuite, iar celelalte se numesc linii superagregat.

    GROUP BY ROLLUP (expr_1, expr_2, , expr_n) genereaz n+1 tipuri de linii, corespunztoare urmtoarelor grupri:

    GROUP BY (expr_1, expr_2, , expr_n-1, expr_n) GROUP BY (expr_1, expr_2, , expr_n-1)

    GROUP BY (expr_1, expr_2) GROUP BY (expr_1) GROUP BY () corespunztor absenei clauzei GROUP BY i deci, calculului funciilor

    grup din cerere pentru ntreg tabelul.

    CUBE grupeaz liniile selectate pe baza valorilor tuturor combinaiilor posibile ale expresiilor specificate i returneaz cte o linie totalizatoare pentru fiecare grup. Acest operator este folosit pentru a produce mulimi de rezultate care sunt utilizate n rapoarte. n vreme ce ROLLUP produce subtotalurile doar pentru o parte dintre combinaiile posibile, operatorul CUBE produce subtotaluri pentru toate combinaiile posibile de grupri specificate n clauza GROUP BY, precum i un total general.

    Dac exist n coloane sau expresii n clauza GROUP BY, vor exista 2n combinaii posibile superagregat.

    19. S se afieze codurile departamentelor n care lucreaz cel puin un angajat, iar pentru fiecare dintre acestea i pentru fiecare manager care lucreaz n departamentul respectiv s se afieze numrul de salariai. De asemenea, s se afieze numrul de salariai pentru fiecare departament indiferent de manager i numrul total de angajai din companie.

    SELECT department_id, manager_id, COUNT(employee_id) FROM employees WHERE manager_id IS NOT NULL AND department_id IS NOT NULL GROUP BY ROLLUP (department_id, manager_id);

    department_id manager_id COUNT(employee_id) ---------------------------------------------------------------------

    10 7782 1 10 7839 1 10 2 -----------------------------------------------------------------------

    20 7566 2 20 7788 1 20 7839 1 20 7902 1

    20 5 -----------------------------------------------------------------------

    30 7698 5 30 7839 1

    30 6

  • 23

    -----------------------------------------------------------------------

    13 20. S se afieze codurile departamentelor n care lucreaz cel puin un angajat, iar pentru fiecare dintre

    acestea i pentru fiecare manager care lucreaz n departamentul respectiv s se afieze numrul de salariai. De asemenea, s se afieze numrul de salariai pentru fiecare departament indiferent de manager, numrul de angajai subordonai unui manager indiferent de departament i numrul total de angajai din companie.

    SELECT department_id, manager_id, COUNT(employee_id) FROM employees WHERE manager_id IS NOT NULL AND department_id IS NOT NULL GROUP BY CUBE (department_id, manager_id);

    department_id manager_id COUNT(employee_id) ---------------------------------------------------------------------

    10 7782 1 10 7839 1 10 2 -----------------------------------------------------------------------

    20 7566 2 20 7788 1 20 7839 1 20 7902 1 20 5 -----------------------------------------------------------------------

    30 7698 5 30 7839 1 30 6 -----------------------------------------------------------------------

    7566 2 7698 5 7782 1 7788 1 7839 3

    7902 1 ----------------------------------------------------------------------

    13 21. Pentru fiecare departament, job, respectiv an al angajrii s se afieze numrul de salariai. De asemenea

    se va afia numrul de angajai: - pentru fiecare departament i job, indiferent de anul angajrii; - pentru fiecare departament, indiferent de job i de anul angajrii; - la nivel de companie.

    22. S se afieze suma alocat pentru plata salariilor pe joburi (codul jobului), n cadrul departamentului (codul departamentului). De asemenea, s se afieze valoarea total necesar pentru plata salariilor la nivel de departament, valoarea total necesar pentru plata salariilor la nivel de job, indiferent de departament i valoarea total necesar pentru plata salariilor la nivel de companie.

    23. Funcia GROUPING(expresie) ntoarce: - valoarea 0, dac expresia a fost utilizat pentru calculul valorii agregat - valoarea 1, dac expresia nu a fost utilizat.

    24. S se afieze numele departamentelor, titlurile job-urilor i valoarea medie a salariilor, pentru: - fiecare departament i, n cadrul su pentru fiecare job; - fiecare departament (indiferent de job); - ntreg tabelul.

  • 24

    De asemenea, s se afieze i o coloan care indic intervenia coloanelor department_name i job_title n obinerea rezultatului.

    25. Modificai cererea anterioar astfel nct s se afieze numele departamentelor, titlurile job-urilor i valoarea medie a salariilor, pentru: - fiecare departament i, n cadrul su pentru fiecare job; - fiecare departament (indiferent de job); - fiecare job(indiferent de departament); - ntreg tabelul. Cum intervin coloanele n obinerea rezultatului? S se afieze Dept, dac departamentul a intervenit n agregare i Job, dac job-ul a intervenit n

    agregare. DECODE(GROUPING(department_name), 0, Dept)

    26. Utilizai cererea de la punctul 20. a. Eliminai clauza WHERE din aceast cerere. Analizai rezultatul obinut. b. Modificai cererea obinut astfel nct s se identifice dac o valoare null din rezultat este stocat pe

    una dintre coloanele manager_id sau department_id sau este produs de operatorul CUBE. 27. Clauza GROUPING SETS. Permite obinerea numai a anumitor grupri superagregat. Acestea pot fi

    precizate prin intermediul clauzei:

    GROUP BY GROUPING SETS ((expr_11, expr_12, , expr_1n), (expr_21, expr_22, expr_2m), ) 28. S se afieze numele departamentelor, numele job-urilor, codurile managerilor angajailor, maximul i

    suma salariilor pentru: - fiecare departament i, n cadrul su, fiecare job; - fiecare job i, n cadrul su, pentru fiecare manager; - ntreg tabelul.

    GROUPING SETS ((department_name, job_title), (job_title, e.manager_id), ());

    Limbajul de control al datelor (DCL). COMMIT, SAVEPOINT, ROLLBACK. Comanda COMMIT permanentizeaz modificrile care au fost realizate de tranzacia curent (o

    tranzacie este un set de comenzi LMD); comanda suprim toate punctele intermediare definite n tranzacie i elibereaz blocrile tranzaciei. Observaie:

    Sistemul realizeaz COMMIT implicit: - la nchiderea normal a unui client Oracle (de exemplu SQL*Plus), - dup fiecare comand LDD (CREATE, ALTER, DROP).

    Comanda SAVEPOINT marcheaz un punct intermediar n procesarea tranzaciei. n acest mod este posibil mprirea tranzaciei n subtranzacii. Comanda SAVEPOINT are sintaxa: SAVEPOINT nume_pct_intermediar;

    Comanda ROLLBACK permite renunarea la modificrile efectuate; aceasta determin ncheierea tranzaciei, anularea modificrilor asupra datelor i restaurarea strii lor precedente. Comanda ROLLBACK are sintaxa: ROLLBACK [TO SAVEPOINT nume_punct_salvare];

  • 25

    Observaii: - sistemul realizeaz ROLLBACK implicit dac se nchide anormal (defeciune hardware sau software,

    pan de curent etc.); - nici o comanda LDD (CREATE, ALTER; DROP) nu poate fi anulat. 1. Ce efect are urmtoarea secven de instruciuni? CREATE TABLE dept_*** AS SELECT * FROM departmets;

    SELECT * FROM dept_***;

    SAVEPOINT a;

    DELETE FROM dept_***;

    INSERT INTO dept_*** VALUES (300,Economic,100,1000);

    INSERT INTO dept_*** VALUES (350,Cercetare,200,2000);

    SAVEPOINT b;

    INSERT INTO dept_*** VALUES (400,Juritic,150,3000);

    SELECT COUNT(*) FROM dept_***;

    ROLLBACK TO b;

    SELECT COUNT(*) FROM dept_***;

    ROLLBACK TO a;

    INSERT INTO dept_*** VALUES (500,Contabilitate,175,1500);

    COMMIT;

    SELECT * FROM dept_***;

    Limbajul de prelucrare a datelor (DML). INSERT, UPDATE, DELETE.

    1. S se creeze tabele emp_*** i dept_***, avnd aceeai structur i date ca i tabelele employees, respectiv departments.

    CREATE TABLE emp_*** AS SELECT * FROM employees; 2. S se selecteze toate nregistrrile din cele dou tabele create anterior. 3. tergei toate nregistrrile din cele 2 tabele create anterior. Salvai modificrile.

  • 26

    DELETE FROM emp_***; COMMIT;

    4. S se listeze structura tabelului employees i s se compare cu structura tabelului emp_***. Ce observai? 5. Sintaxa simplificat a comenzii INSERT

    - pentru inserarea unei singure linii: INSERT INTO nume_tabel [(col1,col2,...)] VALUES (expresie1, expresie2, ...);

    - pentru inserarea liniilor rezultat ale unei comenzi SELECT: INSERT INTO nume_tabel [(col1,col2,...)] comanda_SELECT;

    6. S se exemplifice cteva dintre erorile care pot s apar la inserare i s se observe mesajul returnat de sistem. - lipsa de valori pentru coloane NOT NULL (coloana department_name este definit NOT NULL) INSERT INTO dept_*** (department_id, location_id) VALUES (200, 2000); - nepotrivirea listei de coloane cu cea de expresii INSERT INTO dept_*** VALUES (200, 2000); INSERT INTO dept_*** (department_id, department_name,location_id) VALUES (200, 2000); - nepotrivirea tipului de date INSERT INTO dept_*** (department_id, location_id) VALUES (D23, 2000); - valoare prea mare pentru coloan INSERT INTO dept_*** (department_id, location_id) VALUES (15000, 2000); 7. Inserai n tabelul emp_*** salariaii (din tabelul employees) al cror comision depete 25% din salariu.

    8. Creai tabele emp1_***, emp2_*** i emp3_*** cu aceeai structur ca tabelul employees. Inserai, utiliznd o singur comand INSERT, informaii din tabelul employees:

    - n tabelul emp1_*** salariaii care au salariul mai mic dect 6000; - n tabelul emp2_*** salariaii care au salariul cuprins ntre 6000 i 10000; - n tabelul emp3_*** salariaii care au salariul mai mare dect 10000.

    Verificai rezultatele, apoi tergei toate nregistrrile din aceste tabele.

    Obs. Clauza ALL a comenzii INSERT determin evaluarea tuturor condiiilor din clauzele WHEN. Pentru cele a cror valoare este TRUE, se insereaz nregistrarea specificat n opiunea INTO corespunztoare. 9. S se creeze tabelul emp0_*** cu aceeai structur ca tabelul employees. Inserai, utiliznd o singur

    comand INSERT, informaii din tabelul employees: - n tabelul emp0_*** salariaii care lucreaz n departamentul 80; - n tabelul emp1_*** salariaii care au salariul mai mic dect 6000 (care nu se regsesc n tabelul

    emp0_***); - n tabelul emp2_*** salariaii care au salariul cuprins ntre 6000 i 10000 (care nu se regsesc n

    tabelele emp0_*** i emp1_***); - n tabelul emp3_*** salariaii care au salariul mai mare dect 10000 (care nu se regsesc n tabelele

    emp0_***, emp1_*** i emp2_***).

    Obs.

  • 27

    Clauza FIRST a comenzii INSERT determin inserarea corespunztoare primei clauze WHEN a crei condiie este evaluat TRUE. Toate celelalte clauze WHEN sunt ignorate. 10. Sintaxa simplificat a comenzii DELETE

    DELETE FROM nume_tabel [WHERE conditie];

    11. tergei toate nregistrrile din tabelele emp_*** i dept_***. Inserai n aceste tabele toate nregistrrile corespunztoare din employees, respectiv departments. Permanentizai tranzacia.

    12. tergei angajaii care nu au comision. Anulai modificrile.

    DELETE FROM emp_*** WHERE commission_pct IS NULL; ROLLBACK;

    13. Eliminai departamentele care nu au nici un angajat. Anulai modificrile. 14. Eliminai angajaii care nu aparin unui departament valid. Anulai modificrile. 15. Sintaxa simplificat a comenzii UPDATE:

    UPDATE nume_tabel [alias] SET col1 = expr1[, col2=expr2] [WHERE conditie]; sau

    UPDATE nume_tabel [alias] SET (col1,col2,...) = (subcerere) [WHERE conditie];

    16. Mrii salariul tuturor angajailor din tabelul emp_*** cu 5%. Anulai modificrile.

    UPDATE emp_*** SET salary = salary * 1.05; ROLLBACK;

    17. Schimbai jobul tuturor salariailor din departamentul 80 care au comision n 'SA_REP'. Anulai modificrile.

    18. S se modifice jobul i departamentul angajatului avnd codul 114, astfel nct s fie la fel cu cele ale angajatului avnd codul 205.

    19. Schimbai salariul i comisionul celui mai prost pltit salariat din firm, astfel nct s fie egale cu salariul si comisionul directorului.

    20. Pentru fiecare departament s se mreasc salariul celor care au fost angajai primii astfel nct s devin media salariilor din companie.

    21. S se modifice valoarea emailului pentru angajaii care ctig cel mai mult n departamentul n care lucreaz astfel nct acesta s devin iniiala numelui concatenat cu prenumele. Dac nu are prenume atunci n loc de acesta apare caracterul .. Anulai modificrile.