Lab bd Cristina recapitulare

SELECT
  round (to_date ('31-dec-' || to_char(sysdate, 'yyyy'), 'dd-mon-yyyy') - sysdate) zile
FROM
  dual;

--Lab2
--Ex 22

SELECT
  ang.employee_id ANG,
  ang.first_name ang#,
  mgr.first_name MGR,
  mgr.manager_id mgr#
FROM
  employees ang
LEFT JOIN
  employees mgr
ON
  (ang.manager_id = mgr.employee_id);

--Ex 27

SELECT
  ang.first_name,
  to_char(ang.hire_date, 'dd-mm-yyyy'),
  mgr.first_name,
  to_char(mgr.hire_date, 'dd-mm-yyyy')
FROM
  employees ang
JOIN
  employees mgr
ON
  ang.manager_id = mgr.employee_id
WHERE
  ang.hire_date < mgr.hire_date;


--eX 25

SELECT
  e.last_name,
  j.job_id,
  j.job_title,
  d.department_name,
  e.salary
FROM
  employees e
LEFT JOIN
  departments d
USING
  (department_id)
JOIN
  jobs j
ON
  e.job_id = j.job_id;

--LAB 3
--EX 2

SELECT
  e.employee_id,
  e.first_name
FROM
  employees e
JOIN
  departments d
ON
  e.department_id = d.department_id
WHERE
  e.department_id IN (
                        select
                          e.department_id
                        FROM
                          employees e
                        WHERE
                         lower(e.last_name) LIKE '%t%');

--sau cu JOIN
SELECT DISTINCT
  e.employee_id,
  e.first_name,
  e.department_id,
  d.department_id
FROM
  employees e
JOIN
  departments d
ON
  e.department_id = d.department_id
JOIN
  employees es
ON
  e.department_id = es.department_id
WHERE
  lower(es.last_name) LIKE  '%t%';

--Ex4

SELECT
  d.department_id,
  d.department_name,
  e.last_name,
  j.job_id,
  j.job_title,
  to_char(e.salary, '$99,999,00')
FROM
  employees e
JOIN
  departments d
ON
  e.department_id = d.department_id
JOIN
  jobs j
ON
  e.job_id = j.job_id
  WHERE
  lower(d.department_name) LIKE '%ti%';

--Ex 17

SELECT
  e.last_name,
  e.salary
FROM
  employees e
WHERE
  e.manager_id = (
                  SELECT
                    e.employee_id
                  FROM
                    employees e
                  WHERE
                    e.manager_id IS NULL);

--Ex 18

SELECT
  e.last_name,
  e.department_id,
  e.salary
FROM
  employees e
WHERE
  (NVL(e.department_id, 0),
  e.salary)
        IN
            (SELECT
              NVL(department_id, 0),
              salary
            FROM
              employees
            WHERE
              commission_pct is not null);

--Ex 3

SELECT
  min(salary),
  max(salary),
  avg(salary),
  e.job_id
FROM
  employees e
GROUP BY
  e.job_id
HAVING min(salary) > 5000;

--Ex
SELECT
 round( max(avg(salary)))
FROM
  employees
GROUP BY
  department_id;

--Ex 30 lab 4

SELECT
  d.department_id,
  d.department_name
FROM
  departments d
JOIN
  (
    SELECT
      SUM(salary) SUM, department_id
    FROM
      employees
    GROUP BY
      department_id) Subcerere
ON
  d.department_id = Subcerere.department_id;

--sau
SELECT
  d.department_id,
  d.department_name
  (
    SELECT
      SUM(salary) SUM, department_id
       FROM
      employees
      WHERE
        department_id = d.department_id
    GROUP BY
      department_id
FROM
  departments d;