BD LABORATOR 10.12.2018

--fisier de scripturi, seaprat ( File -> New -> Sql file )
-- il copiem undeva in calculator si in grupa254 rulam calea scriptului (exemplu ex. 21)
--ex 21 lab 7 in fila noua, file -> new sql file
SELECT *
FROM dept_cpi
WHERE department_id = &&cod_dep;

UPDATE dept_cpi
SET &coloana = &valoare_noua
WHERE department_id = &cod_dep;

SELECT *
FROM dept_cpi
WHERE department_id = &cod_dep;
ROLLBACK;

--lab 7
--ex 18
UPDATE emp_cpi ext
SET email = substr(last_name,1,1) || nvl(first_name, '.')
WHERE salary = (
      SELECT MAX(salary)
      FROM emp_cpi
      WHERE department_id = ext.department_id
      GROUP BY department_id);
ROLLBACK;

--ex19
UPDATE emp_cpi ext
SET salary = (
    SELECT avg(salary)
    FROM emp_cpi
    WHERE employee_id != 0)
WHERE hire_date = (
    SELECT MIN(hire_date)
    FROM emp_cpi
    WHERE department_id = ext.department_id);

--20
UPDATE emp_cpi
SET (job_id, department_id) = (
    SELECT job_id, department_id
    FROM emp_cpi
    WHERE employee_id = 205)
WHERE employee_id = 114;

--21
@C:\Users\Student\Desktop\21.SQL;

--delete
--ex 22
DELETE FROM dept_cpi;
WHERE department_id NOT IN (
      SELECT department_id
      SELECT nvl(department_id, 0)
      FROM emp_cpi);
ROLLBACK;

SAVEPOINT ex25;
INSERT INTO dept_cpi(department_id, department_name)
VALUES (312,'departament nou');
INSERT INTO dept_cpi(department_id, department_name)
VALUES (313,'departament nou2');
ROLLBACK TO ex25;
ROLLBACK;

--merge
--ex31
DELETE FROM emp_cpi;
WHERE commission_pct IS NOT NULL;

MERGE INTO emp_cpi a
USING employees e
ON (a.employee_id = e.employee_id)
WHEN matched THEN
    UPDATE SET
    a.email = e.email,
    a.salary = e.salary,
    a.department_id = e.department_id
WHEN NOT matched THEN
    INSERT (employee_id, last_name, hire_date, job_id, email)
    VALUES (e.employee_id, e.last_name, e.hire_date, e.job_id, e.email)

ROLLBACK;

--lab8
--ex1
CREATE TABLE angajati_cpi (
          cod_ang NUMBER(4),
          nume varchar2(20),
          prenume varchar2(20),
          email CHAR(15),
          data_ang DATE,
          job varchar2(15),
          cod_sef NUMBER(4),
          salariu NUMBER(8,2),
          cod_dep NUMBER(4) );

DROP TABLE angajati_cpi;

--ex1b
CREATE TABLE angajati_cpi (
          cod_ang NUMBER(4) CONSTRAINT cod_ang_pk_cpi PRIMARY KEY,
          nume varchar2(20) CONSTRAINT nume_not_null_cpi NOT NULL,
          prenume varchar2(20),
          email CHAR(15),
          data_ang DATE,
          job varchar2(15),
          cod_sef NUMBER(4) NOT NULL,
          salariu NUMBER(8,2),
          cod_dep NUMBER(4) );

DROP TABLE angajati_cpi;

--ex1c
CREATE TABLE angajati_cpi (
          cod_ang NUMBER(4),
          nume varchar2(20),
          prenume varchar2(20),
          email CHAR(15),
          data_ang DATE,
          job varchar2(15),
          cod_sef NUMBER(4),
          salariu NUMBER(8,2),
          cod_dep NUMBER(4),
        CONSTRAINT cod_ang_pk_cpi PRIMARY KEY(cod_ang)
        CONSTRAINT nume_not_null_cpi CHECK(nume IS NOT NULL)
          );

--adaugare constrangere dupa crearea tabelului
ALTER TABLE angajati_aga
ADD CONSTRAINT salariu_not_null_cpi CHECK(salariu IS NOT NULL);

DROP TABLE angajati_cpi;