BD laborator

--lab 5
--1
SELECT department_name, job_title, avg(salary)
FROM departments d JOIN employees e
ON d.department_id = e.department_id
JOIN jobs j
ON j.job_id = e.job_id
GROUP BY rollup(department_name, job_title);

--1b
SELECT department_name, job_title, round(avg(salary)),
GROUPING (department_name) Dep, GROUPING(job_title) job
FROM departments d JOIN employees e
ON d.department_id = e.department_id
JOIN jobs j
ON j.job_id = e.job_id
GROUP BY rollup(department_name, job_title);

--2
SELECT department_name, job_title, round(avg(salary))
FROM employees
JOIN departments USING (department_id)
JOIN jobs USING (job_id)
GROUP BY cube (department_name, job_title);

--2b
SELECT department_name, job_title, round(avg(salary)),
  decode(GROUPING(department_name),'0','Dep','Nu dep') AS "Dep",
  decode(GROUPING(job_title),'0','Job','Nu job') AS "Job"
FROM employees
JOIN departments USING (department_id)
JOIN jobs USING (job_id)
GROUP BY cube (department_name, job_title);

--3
SELECT department_name, job_title, e.manager_id, MAX(salary), SUM(salary)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id
GROUP BY GROUPING sets(department_name, job_title), (job_title, e.manager_id);

--4
SELECT MAX(salary)
FROM employees
HAVING MAX(salary) >= 1500;

--5
SELECT last_name
FROM employees ext
WHERE salary >=
      (
      SELECT avg(salary)
      FROM employees
      WHERE department_id = ext.department_id
      AND employee_id != ext.employee_id
      );

--5b
SELECT last_name, department_name, avg(salary),s, COUNT(employee_id)
FROM employees
JOIN
  (
  SELECT avg(salary)
  FROM employees
  JOIN departments
  GROUP BY department_id
  )tabel ON department_id = tabel.department_id
  JOIN (
  SELECT COUNT(employee_id) c, department_id
  FROM employees
  GROUP BY department_id
)tabel2; --incomplet

--5
SELECT last_name,
    (SELECT department_name
    FROM departments
    WHERE ext.department_id = department_id) Dep,
    (SELECT round(avg(salary))
    FROM employees
    WHERE ext.department_id = department_id) Medie,
    (SELECT COUNT (employee_id)
    FROM employees
    WHERE ext.department_id = department_id) Nr
  FROM employees ext;

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

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

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

--7b
SELECT last_name, salary
FROM employees
WHERE (salary, department_id) IN
      (SELECT MIN(salary), department_id
      FROM employees
      GROUP BY department_id);

--7c
SELECT last_name, salary
FROM employees e JOIN
SELECT MIN(salary) --incomplet

--8
SELECT last_name
FROM employees
JOIN departments
ON ext.department_id = d.department_id
WHERE hire_date =
    (
    SELECT MIN(hire_date)
    FROM employees
    WHERE department_id = ext.department_id
    GROUP BY department_id
    )
    ORDER BY department_name;

--9
SELECT last_name, department_id
FROM employees e
WHERE EXISTS (
      SELECT 'string salary'
      FROM employees
      WHERE department_id = e.department_id AND salary =
        (
        SELECT MAX(salary)
        FROM employees
      WHERE department_id=30
      GROUP BY department_id)
      );

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

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

    --12,13, 21,22 tema