restul lab reboratoarelor BD (incilvatem zolcate

--lab 3
--ex1
SELECT emp.last_name, to_char(emp.hire_date, 'MONTH'), to_char(emp.hire_date, 'YYYY')
FROM employees emp
JOIN employees gates ON emp.department_id = gates.department_id
WHERE emp.last_name LIKE ('%a%') AND gates.last_name LIKE ('Gates')
AND emp.last_name NOT LIKE ('Gates');

--ex1 v2
SELECT e.last_name, TO_CHAR(e.hire_date,'MON'), TO_CHAR(e.hire_date,'YYYY'), e.department_id, e.hire_date
FROM employees e JOIN employees Gates
ON e.department_id = gates.department_id
WHERE LOWER(e.last_name) LIKE '%a%' AND Gates.last_name = 'Gates'
AND e.employee_id  != Gates.employee_id;

--ex1 v2 metoda 2
SELECT e.last_name, TO_CHAR(e.hire_date,'MON'), TO_CHAR(e.hire_date,'YYYY'), e.department_id, e.hire_date
FROM employees e JOIN employees Gates
ON e.department_id = gates.department_id
WHERE instr(LOWER(e.last_name),'a') > 0 AND Gates.last_name = 'Gates'
AND e.employee_id  != Gates.employee_id;

--ex2
SELECT DISTINCT emp.employee_id, emp.last_name, dep.department_id, dep.department_name
FROM employees emp JOIN departments dep
ON emp.department_id = dep.department_id
JOIN employees coleg
ON coleg.department_id = emp.department_id
WHERE coleg.last_name LIKE ('%t%')
ORDER BY emp.last_name;

--ex3
SELECT last_name, salary, job_title, city, country_name
FROM employees emp JOIN jobs
ON emp.job_id = jobs.job_id
JOIN departments dep
ON emp.department_id = dep.department_id
JOIN locations loc
ON dep.location_id = loc.location_id
JOIN countries cnt
ON loc.country_id = cnt.country_id
WHERE emp.manager_id = (
                        SELECT emp.employee_id
                        FROM employees emp
                        WHERE emp.last_name LIKE ('King') AND emp.manager_id IS NULL
                        )
ORDER BY emp.last_name;

--ex4
SELECT dep.department_id, dep.department_name, emp.last_name, job_title, to_char(salary,'$99,999.99')
FROM departments dep JOIN employees emp
ON dep.department_id = emp.department_id
JOIN jobs
ON emp.job_id = jobs.job_id
WHERE LOWER(dep.department_name) LIKE ('%ti%')
ORDER BY department_name, last_name;

--ex5
SELECT emp.last_name, emp.department_id, dep.department_name, loc.city, jobs.job_title
FROM employees emp
JOIN departments dep ON emp.department_id = dep.department_id
JOIN locations loc ON dep.location_id = loc.location_id
JOIN jobs ON emp.job_id = jobs.job_id
WHERE dep.location_id = (
                            SELECT dep.location_id
                            FROM departments dep
                            JOIN locations loc
                            ON dep.location_id = loc.location_id
                            WHERE loc.city LIKE ('Oxford')
                        );

--ex6
SELECT employee_id, last_name, salary
FROM employees emp
JOIN jobs ON emp.job_id = jobs.job_id
WHERE salary > (min_salary + max_salary)/2 AND  emp.department_id IN
                                                                ( SELECT department_id
                                                                 FROM employees
                                                                 WHERE last_name LIKE ('%t')
                                                                 );

--ex7
SELECT emp.last_name, dep.department_name
FROM departments dep
RIGHT OUTER JOIN employees emp ON dep.department_id = emp.department_id;

--ex7v2
SELECT
    e.last_name,
    d.department_name
FROM
    employees e
    RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

--ex8
SELECT dep.department_name, emp.last_name
FROM departments dep
LEFT OUTER JOIN employees emp ON dep.department_id = emp.department_id;

--ex9
SELECT dep.department_name, emp.last_name
FROM departments dep
FULL OUTER JOIN employees emp ON dep.department_id = emp.department_id;

--ex10

SELECT department_id, department_name
FROM departments
WHERE LOWER(department_name) LIKE ('%re%') OR department_id IN
                                                            ( SELECT department_id
                                                            FROM employees
                                                            WHERE job_id LIKE ('SA_REP')
                                                            );

--ex10 v2
SELECT department_id
FROM departments
WHERE LOWER(department_name) LIKE ('%re%')
UNION
SELECT department_id
FROM employees
WHERE LOWER(job_id) = 'sa_rep';

--ex12
SELECT department_id
FROM departments
minus
SELECT department_id
FROM employees;

--ex12 v2
SELECT department_id
FROM departments
WHERE manager_id IS NULL;

--ex13
SELECT department_id
FROM departments
WHERE LOWER(department_name) LIKE ('%re%')
INTERSECT
SELECT department_id
FROM employees
WHERE LOWER(job_id) = 'hr_rep';

--ex13v2
SELECT department_id
FROM departments dep FULL OUTER JOIN employees emp
WHERE dep.department_id;

--ex14
SELECT employee_id, job_id, last_name, salary
FROM employees JOIN jobs USING (job_id)
WHERE salary > 3000 OR salary = (min_salary + max_salary)/2;

--ex14 v2
SELECT employee_id, job_id, last_name
FROM employees
WHERE salary > 3000
UNION
SELECT employee_id, j.job_id, last_name
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
WHERE salary = (min_salary + max_salary )/2;

--ex15
SELECT last_name, hire_date
FROM employees
WHERE hire_date > ( SELECT hire_date
                    FROM employees
                    WHERE last_name = 'Gates' );

--ex16
SELECT last_name, salary
FROM employees
WHERE department_id = ( SELECT department_id
                        FROM employees
                        WHERE last_name LIKE ('Gates')
                        )
AND last_name != 'Gates';

--ex17
SELECT last_name, salary
FROM employees
WHERE manager_id = ( SELECT employee_id
                    FROM employees
                    WHERE manager_id IS NULL);

--ex18
SELECT last_name, department_id, salary
FROM employees
WHERE (department_id,salary) IN (SELECT department_id, salary
                                    FROM employees
                                    WHERE commission_pct IS NOT NULL);

 --ex19
 -- e gata facuta

 --ex20
 SELECT last_name, salary
 FROM employees
 WHERE salary >ALL (SELECT salary
                    FROM employees
                    WHERE LOWER(job_id) LIKE ('%clerk%')
                    )
ORDER BY salary DESC;

 --ex21
 SELECT last_name, department_name, salary,emp.manager_id
 FROM employees emp JOIN departments dep ON emp.department_id = dep.department_id
 WHERE emp.commission_pct IS NULL AND emp.manager_id IN (  SELECT manager_id
                                                   FROM employees
                                                   WHERE commission_pct IS NOT NULL);
--ex22
SELECT last_name, department_id, salary, job_id
FROM employees
WHERE (salary,commission_pct) IN (  SELECT salary, commission_pct
                                    FROM employees emp JOIN departments dep
                                    ON emp.department_id = dep.department_id
                                    JOIN locations loc
                                    ON dep.location_id = loc.location_id
                                    WHERE city LIKE ('Oxford')
                                 );

--ex23
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN ( SELECT department_id
                        FROM departments dep JOIN locations loc
                        ON dep.location_id = loc.location_id
                        WHERE loc.city LIKE ('Toronto')
                        );

--lab 4
--ex2
SELECT
    MIN(salary) AS "Minim",
    MAX(salary) AS "Maxim",
    SUM(salary) AS "Suma",
    round(AVG(salary)) AS "Media"
FROM
    employees;

--ex3
SELECT job_id, MIN(salary), MAX(salary), SUM(salary), avg(salary)
FROM employees
GROUP BY job_id;

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

--ex5
SELECT COUNT(DISTINCT manager_id) AS "Nr. manageri"
FROM employees;

--ex6
SELECT MAX(salary) - MIN(salary) AS "Diferenta"
FROM employees;

--ex7
SELECT department_name, city, COUNT(employee_id), avg(salary)
FROM employees emp
JOIN departments dep USING (department_id)
JOIN locations loc USING (location_id)
GROUP BY department_name, city;

--ex8
SELECT employee_id, last_name
FROM employees
WHERE salary > (
                SELECT avg(salary)
                FROM employees
                )
ORDER BY salary DESC;

--ex9
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id, salary
HAVING MIN(salary) > 1000
ORDER BY 2 DESC;

--ex10
SELECT department_id, department_name, MAX(salary)
FROM departments dep JOIN employees emp USING (department_id)
GROUP BY department_id, department_name
HAVING MAX(salary) > 3000;

--ex11
SELECT round(avg(MIN(salary)))
FROM employees
GROUP BY job_id;

 --ex12
 SELECT department_id, department_name, SUM(salary)
 FROM departments dep JOIN employees emp USING (department_id)
 GROUP BY department_id, department_name;

 --ex13
 SELECT round(MAX(avg(salary)))
 FROM employees
 GROUP BY department_id;

 --ex14
 SELECT job_id, job_title, avg(salary)
 FROM jobs JOIN employees emp USING (job_id)
 GROUP BY job_id, job_title
 HAVING avg(salary) = (SELECT MIN(avg(salary))
                       FROM employees
                       GROUP BY job_id);

--ex 16
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id;

--ex 18a
SELECT department_id, department_name, COUNT(employee_id)
FROM departments dep JOIN employees emp USING (department_id)
GROUP BY department_id, department_name
HAVING COUNT(employee_id) < 4;

--ex 18b
SELECT department_id, department_name, COUNT(employee_id)
FROM departments dep JOIN employees emp USING (department_id)
GROUP BY department_id, department_name
HAVING COUNT(employee_id) = ( SELECT MAX(COUNT(employee_id))
                                FROM employees
                                GROUP BY department_id);


--ex 20 v2
SELECT COUNT(COUNT(department_id))
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 15;

--ex 21
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id != 30
GROUP BY department_id
HAVING COUNT(employee_id) > 10
ORDER BY SUM(salary);

--ex 22
SELECT department_id, department_name, COUNT(employee_id), avg(salary), last_name, salary, job_id
FROM departments dep JOIN employees emp USING (department_id)
GROUP BY department_id, department_name;

--ex23
SELECT city, department_name, job_id, SUM(salary)
FROM employees emp JOIN departments dep USING (department_id)
JOIN locations loc USING (location_id)
WHERE department_id > 80
GROUP BY department_name, job_id, city;

--ex24
SELECT employee_id
FROM job_history
GROUP BY employee_id
HAVING COUNT(employee_id) >= 2;

--ex25
SELECT avg(nvl(commission_pct,0))
FROM employees;

--ex28
SELECT COUNT(employee_id),
            (SELECT COUNT(employee_id)
            FROM employees
            WHERE to_char(hire_date, 'yyyy') = 1997) AS "1997"
            ,
            (SELECT COUNT(employee_id)
            FROM employees
            WHERE to_char(hire_date, 'yyyy') = 1998) AS "1998"
            ,
            (SELECT COUNT(employee_id)
            FROM employees
            WHERE to_char(hire_date, 'yyyy') = 1999) AS "1999"
FROM employees
GROUP BY 'orice';

--lab5
--ex3
SELECT department_name, job_id, emp.manager_id, MAX(salary), SUM(salary)
FROM employees emp JOIN departments dep USING (department_id)
GROUP BY GROUPING sets((department_name,job_id), (job_id,emp.manager_id), () );

--ex4
SELECT MAX(salary)
FROM employees
HAVING MAX(salary)>15000;


--ex5
SELECT last_name
FROM employees emp
WHERE salary >=  (SELECT avg(salary)
                    FROM employees col
                    WHERE col.department_id = emp.department_id
                    AND col.employee_id != emp.employee_id);


--ex6
SELECT last_name, salary
FROM employees
WHERE salary >ALL (SELECT (avg(salary))
                    FROM employees
                    GROUP BY department_id
                    );

--ex7 subcerere nesinc
SELECT last_name, salary
FROM employees
WHERE (salary, department_id) IN (SELECT MIN(salary), department_id
                FROM employees
                GROUP BY department_id);

--ex7 subcerere sinc
SELECT last_name, salary
FROM employees ext
WHERE salary = (SELECT MIN(salary)
                FROM employees
                WHERE department_id = ext.department_id
                GROUP BY department_id);

--ex8
SELECT department_name, last_name
FROM employees emp JOIN departments dep ON emp.department_id = dep.department_id
WHERE hire_date = (SELECT MIN(hire_date)
                    FROM employees xx
                    WHERE xx.department_id = emp.department_id
                    GROUP BY department_id);

--ex10
SELECT last_name, salary
FROM (
    SELECT *
    FROM employees
    ORDER BY salary)
WHERE rownum <=3
ORDER BY salary DESC;

--ex11
SELECT employee_id, last_name, first_name
FROM employees ext
WHERE 2 <= (
            SELECT COUNT(employee_id)
            FROM employees
            WHERE ext.manager_id = manager_id
            GROUP BY manager_id
            );

--ex12
SELECT city, COUNT(department_name)
FROM locations loc JOIN departments dep ON (loc.location_id=dep.location_id)
WHERE loc.location_id IN (SELECT DISTINCT location_id
                        FROM departments
                    )

GROUP BY city;

--ex13
SELECT department_id
FROM  departments
WHERE department_id NOT IN (SELECT department_id
                            FROM employees)
GROUP BY department_id;

--lab 7
--ex1
CREATE TABLE emp_cpi AS SELECT * FROM employees;
CREATE TABLE dept_cpi AS SELECT * FROM departments;

--ex2
DESC emp_cpi;
DESC dept_cpi;

--ex3
SELECT *
FROM dept_cpi;

--ex4
ALTER TABLE emp_cpi
ADD CONSTRAINT pk_emp_cpi PRIMARY KEY(employee_id);

ALTER TABLE dept_cpi
ADD CONSTRAINT pk_dept_cpi PRIMARY KEY(department_id);

ALTER TABLE emp_cpi
ADD CONSTRAINT fk_emp_dept_cpi
    FOREIGN KEY(department_id) REFERENCES dept_cpi(department_id);

--ex5
INSERT INTO dept_cpi
VALUES (300,'Programare');

INSERT INTO dept_cpi(department_id, department_name)
VALUES (300,'Programare');

INSERT INTO dept_cpi(department_name, department_id)
VALUES (300,'Programare');

INSERT INTO dept_cpi(department_id, department_name, location_id)
VALUES(300,'Programare', NULL);

INSERT INTO dept_cpi(department_name, location_id)
VALUES('Programare',NULL);

--ex6
INSERT INTO emp_cpi
VALUES(250,'Andrei','Ion','mailmail@gmail.com',NULL,to_date('24-12-2018','DD-MM-YYYY'),'SA_REP',1500,NULL,NULL,300);
commit;
--ex7
INSERT INTO emp_cpi(employee_id,last_name,email,hire_date,job_id,department_id)
VALUES(305,'Voinea','voineagabi@gmail.com',sysdate,'Aprozar',300);

--ex9
CREATE TABLE emp1_cpi AS
SELECT *
FROM employees;

DELETE FROM emp1_cpi;

INSERT INTO emp1_cpi
SELECT *
FROM employees
WHERE commission_pct > 0.25;
commit;

--ex 10
SELECT USER FROM dual;

INSERT INTO emp_cpi(employee_id, last_name, email, hire_date, job_id, salary, commission_pct, department_id)
SELECT 0, USER, 'total', sysdate, 'total',  SUM(salary), avg(commission_pct),NULL
FROM employees;

--ex12
CREATE TABLE emp2_cpi AS
SELECT *
FROM employees;

CREATE TABLE emp3_cpi AS
SELECT *
FROM employees;

DELETE FROM emp2_cpi;
DELETE FROM emp3_cpi;

INSERT INTO emp1_cpi
SELECT *
FROM employees
WHERE salary < 5000;

INSERT INTO emp2_cpi
SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 10000;

INSERT INTO emp3_cpi
SELECT *
FROM employees
WHERE salary >10000;

DELETE FROM emp1_cpi;
DELETE FROM emp2_cpi;
DELETE FROM emp3_cpi;

--ex13

CREATE TABLE emp0_cpi AS
SELECT *
FROM employees;

DELETE FROM emp0_cpi;

INSERT FIRST
WHEN department_id = 80 THEN INTO emp0_cpi
WHEN salary < 5000 THEN INTO emp1_cpi
WHEN salary BETWEEN 5000 AND 10000 THEN INTO emp2_cpi
WHEN salary > 10000 THEN INTO emp3_cpi
SELECT *
FROM employees;
commit;

--ex14
UPDATE emp_cpi
SET salary = salary * 1.05;
ROLLBACK;

--ex15
UPDATE emp_cpi
SET job_id = 'SA_REP'
WHERE commission_pct > 0 AND department_id = 80;

ROLLBACK;

--LAB 8
--ex1
CREATE TABLE angajati_cpi ( cod_ang NUMBER(4),
                            nume varchar2(20) CONSTRAINT nume_nn NOT NULL,
                            prenume varchar2(20),
                            email CHAR(15),
                            data_ang DATE,
                            job varchar2(10),
                            cod_sef NUMBER(4),
                            salariu NUMBER(8,2) CONSTRAINT salariu_nn NOT NULL,
                            cod_dep NUMBER(2),
                             CONSTRAINT cod_ang_pk PRIMARY KEY(cod_ang)
                            );
DROP TABLE angajati_cpi;

--ex2
INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
VALUES(100, 'nume1', 'prenume1', NULL, NULL, 'Director', NULL, 20000, 10);
INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
VALUES (101, 'nume2', 'prenume2', 'nume2', to_date('02-02-2004', 'dd-mm-yyyy'), 'inginer', 100, 10000, 10);
INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
VALUES(102, 'nume3', 'prenume3', 'nume3', to_date('05-06-2000', 'dd-mm-yyyy'), 'analist', 101, 5000, 20);

DELETE FROM angajati_cst
WHERE   cod_ang = 101;
DELETE FROM angajati_cst
WHERE   cod_ang = 102;

INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
VALUES (101, 'nume2', 'prenume2', 'nume2', to_date('02-02-2004', 'dd-mm-yyyy'), 'inginer', 100, 10000, 10);
INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
VALUES(102, 'nume3', 'prenume3', 'nume3', to_date('05-06-2000', 'dd-mm-yyyy'), 'analist', 101, 5000, 20);
INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
VALUES (103, 'nume4', 'prenume4', NULL, NULL, 'inginer', 100, 90000, 20);
INSERT INTO angajati_cst(cod_ang, nume, prenume, email, data_ang, job, cod_sef, salariu, cod_dep)
VALUES(104, 'nume5', 'prenume5', 'nume5', NULL, 'analist', 101, 3000, 30);

DELETE FROM angajati_cst
WHERE   cod_ang = 104;

--ex3
CREATE TABLE angajati10_cpi AS
SELECT *
FROM angajati_cpi
WHERE cod_dep = 10;

--ex4
ALTER TABLE angajati_cpi
ADD comision NUMBER(4,2);

--ex5
ALTER TABLE angajati_cpi
MODIFY salariu NUMBER(6,2);

--ex6
ALTER TABLE angajati_cpi
MODIFY salariu NUMBER DEFAULT 32121;

--ex7
ALTER TABLE angajati_cpi
MODIFY (comision NUMBER(2,2), salariu NUMBER(10,2));

--ex8
UPDATE angajati_cpi
SET comision = 0.1
WHERE LOWER(job) LIKE 'a%';