lab 3 BD rezolvat

--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')
                        );