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

Sa se creeze tabele emp_*** si dept_***, avand aceeasi structura si date ca si tabelele employees, respectiv departments.

CREATE TABLE emp_*** AS

SELECT * FROM employees;

CREATE TABLE dept_*** AS

SELECT * FROM departments;

Sa se selecteze toate inregistrarile din cele doua tabele create anterior.

Stergeti toate inregistrarile din cele 2 tabele create anterior. Salvati modificarile.

DELETE FROM emp_***;

DELETE FROM dept_***;

COMMIT;

Sa se listeze structura tabelului employees si sa se compare cu structura tabelului emp_***. Ce observati?

Sintaxa simplificata a comenzii INSERT

pentru inserarea unei singuri linii:

INSERT INTO nume_tabel [(col1,col2,)]

VALUES (expresie1, expresie2, );

pentru inserarea liniilor rezultat ale unei comenzi SELECT:

INSERT INTO nume_tabel [(col1,col2,)]

Observatii:

lista de coloane (daca este precizata) trebuie sa se potriveasca ca numar si tip de date cu lista de expresii;

in loc de tabel (nume_tabel), insererea se mai poate face si prin intermediul unei vizualizari;

daca se omit coloane in lista de inserare, acestea primesc valoarea NULL sau valoarea implicita.

posibile probleme la inserare:

lipsa de valori pentru coloane NOT NULL,

nepotrivirea listei de coloane cu cea de expresii,

valori duplicate ce incalca o constrangere de unicitate,

incalcarea vreunei constrangeri de integritate referentiala,

incalcarea unei constrangeri de tip CHECK,

nepotrivire de tip de date,

valoare prea mare pentru coloana respectiva.

daca se foloseste expresia DEFAULT, atunci valoarea inserata este NULL sau valoarea implicita setata la nivel de tabel.

Sa se exemplifice cateva din erorile care pot sa apara la inserare si sa se observe mesajul returnat de sistem.

lipsa de valori pentru coloane NOT NULL (coloana department_name este 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);

nepotrivire de tip de date,

INSERT INTO dept_*** (department_id, location_id)

VALUES ('D23', 2000);

valoare prea mare pentru coloana

INSERT INTO dept_*** (department_id, location_id)

VALUES (15000, 2000);

Copiati in tabelul emp_*** salariatii (din tabelul employees) al caror comision depaseste 25% din salariu.

INSERT INTO emp_***

SELECT *

FROM employees

WHERE commission_pct > 0.25;

SELECT employee_id, last_name, salary, commission_pct

FROM emp_***;

Creati tabele emp1_***, emp2_*** si emp3_*** cu aceeasi structura ca tabelul employees. Copiati din tabelul employees:

in tabelul emp1_*** salariatii care au salariul mai mic decat 6000;

in tabelul emp2_*** salariatii care au salariul cuprins intre 6000 si 10000;

in tabelul emp3_*** salariatii care au salariul mai mare decat 10000.

Verificati rezultatele, apoi stergeti toate inregistrarile din aceste tabele.

Obs. Clauza FIRST determina inserarea corespunzatoare primei clauze WHEN a carei conditie este evaluata TRUE. Toate celelalte clauze WHEN sunt ignorate.

CREATE TABLE emp1_***

AS SELECT * FROM employees WHERE 1=0;

CREATE TABLE emp2_***

AS SELECT * FROM employees WHERE 1=0;

CREATE TABLE emp3_***

AS SELECT * FROM employees WHERE 1=0;

INSERT ALL

WHEN salary < =6000 THEN

INTO emp1_***

WHEN salary > = 6000 AND salary <= 10000 THEN

INTO emp2_***

ELSE

INTO emp3_***

SELECT * FROM employees;

DELETE FROM emp1_***;

DELETE FROM emp2_***;

DELETE FROM emp3_***;

COMMIT;

Sa se creeze tabelul emp0_*** cu aceeasi structura ca tabelul employees. Copiati din tabelul employees:

in tabelul emp0_*** salariatii care lucreaza in departamentul 80;

in tabelul emp1_*** salariatii care au salariul mai mic decat 6000;

in tabelul emp2_*** salariatii care au salariul cuprins intre 6000 si 10000;

in tabelul emp3_*** salariatii care au salariul mai mare decat 10000.

Daca un salariat se incadreaza in tabelul emp0_*** atunci acesta nu va mai fi inserat si in alt tabel (tabelul corespunzator salariului sau).

Obs. Clauza ALL determina evaluarea tuturor conditiilor din clauzele WHEN. Pentru cele a caror valoare este TRUE, se insereaza inregistrarea specificata in optiunea INTO corespunzatoare.

CREATE TABLE emp0_***

AS SELECT * FROM FROM employees WHERE 1=0;

INSERT FIRST

WHEN department_id = 80 THEN

INTO emp0_***

WHEN salary <= 6000 THEN

INTO emp1_***

WHEN salary > = 6000 AND salary <= 10000 THEN

INTO emp2_***

ELSE

INTO emp3_***

SELECT * FROM employees;

Sintaxa simplificata a comenzii DELETE

DELETE FROM nume_tabel

[WHERE conditie];

Stergeti toate inregistrarile din tabelele emp_*** si dept_***. Inserati in aceste tabele toate inregistrarile corespunzatoare din employees, respectiv departments.

DELETE FROM dept_***;

DELETE FROM emp_***;

INSERT INTO emp_***

SELECT * FROM employees;

INSERT INTO dept_***

SELECT * FROM departments;

COMMIT;

Stergeti angajatii care nu au comision. Anulati modificarile.

DELETE FROM emp_***

WHERE commission_pct IS NULL;

ROLLBACK;

Eliminati departamentele care nu au nici un angajat. Anulati modificarile.

DELETE FROM dept_***

WHERE department_id NOT IN (SELECT DISTINCT department_id

FROM emp_***

WHERE department_id IS NOT NULL);

ROLLBACK;

Eliminati angajatii care nu apartin unui departament valid. Anulati modificarile.

DELETE FROM emp_***

WHERE department_id NOT IN (SELECT department_id

FROM dept_***)

OR department_id IS NULL;

ROLLBACK;

Sintaxa simplificata 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];

Observatii:

de obicei pentru identificarea unei linii se foloseste o conditie ce implica cheia primara;

daca nu apare clauza WHERE atunci sunt afectate toate liniile tabelului specificat.

Mariti salariul tuturor angajatilor din tabelul emp_*** cu 5%. Anulati modificarile.

UPDATE emp_***

SET salary = salary * 1.05;

ROLLBACK;

Schimbati jobul tuturor salariatilor din departamentul 80 care au comision in 'SA_REP'. Anulati modificarile.

UPDATE emp_***

SET job_id = 'SA_REP'

WHERE department_id=80 AND commission_pct IS NOT NULL;

ROLLBACK;

Sa se promoveze Douglas Grant la manager in departamentul 20, avand o crestere de salariu cu 1000$.

UPDATE emp_***

SET job_id = 'SA_MAN', salary = salary + 1000, department_id = 20

WHERE first_name= 'Douglas' AND last_name = 'Grant';

ROLLBACK;

Sa se modifice jobul si departamentul angajatului avand codul 114, astfel incat sa fie la fel cu cele ale angajatului avand codul 205.

UPDATE emp_***

SET (job_id, department_id) = (SELECT job_id, department_id

FROM emp_***

WHERE employee_id = 205)

WHERE employee_id = 114;

ROLLBACK;

Schimbati salariul si comisionul celui mai prost platit salariat din firma, astfel incat sa fie egale cu salariul si comisionul directorului.

UPDATE emp_***

SET (salary, commission_pct) = (SELECT salary, commission_pct

FROM emp_***

WHERE manager_id IS NULL)

WHERE salary = (SELECT MIN(salary)

FROM emp_***);

ROLLBACK;

Pentru fiecare departament sa se mareasca salariul celor care au fost angajati primii astfel incat sa devina media salariilor din companie.

UPDATE emp_*** e

SET salary = (SELECT AVG(salary)

FROM emp_***)

WHERE hire_date = (SELECT MIN(hire_date)

FROM emp_***

WHERE department_id=e.department_id);

ROLLBACK;

Sa se modifice valoarea emailului pentru angajatii care castiga cel mai mult in departamentul in care lucreaza astfel incat acesta sa devina initiala numelui concatenata cu prenumele. Daca nu are prenume atunci in loc de acesta apare caracterul '.'. Anulati modificarile.

UPDATE emp_***

SET email = LOWER(SUBSTR(first_name,1,1)) || LOWER(NVL(last_name, '.

WHERE (department_id, salary) IN (SELECT department_id, MAX(salary)

FROM emp_***

GROUP BY department_id);

ROLLBACK;

Comanda MERGE are urmatoarea sintaxa simplificata:

MERGE INTO nume_tabel [alias]
USING [alias]
ON (conditie)
WHEN MATCHED THEN
UPDATE SET
coloana_1 = ,
coloana_n =
WHEN NOT MATCHED THEN
INSERT (coloana_1, , coloana_n)

VALUES (expr_i1, , expr_in);

Observatie: Instructiunea MERGE permite inserarea sau actualizarea conditionata a datelor dintr-un tabel al bazei. Instructiunea efectueaza UPDATE daca inregistrarea exista deja in tabel sau INSERT daca inregistrarea este noua. In acest fel, se pot evita instructiunile UPDATE multiple.

Sa se stearga din tabelul emp_*** toti angajatii care nu lucreaza in departamentul 20. Sa se introduca sau sa actualizeze datele din tabelul emp_*** folosind tabelul employees.

MERGE INTO emp_*** a

USING employees b

ON (a.employee_id = b.employee_id)

WHEN MATCHED THEN

UPDATE SET

a.first_name=b. first_name,

a.last_name=b.last_name,

a.email=b.email,

a.phone_number=b.phone_number,

a.hire_date= b.hire_date,

a.job_id= b.job_id,

a.salary = b.salary,

a.commission_pct= b.commission_pct,

a.manager_id= b.manager_id,

a.department_id= b.department_id

WHEN NOT MATCHED THEN

INSERT VALUES(b.employee_id, b.first_name, b.last_name, b.email,

b.phone_number, b.hire_date, b.job_id, b.salary,

b.commission_pct, b.manager_id, b.department_id)