--1
SELECT b.first_name || ' ' || b.last_name AS Pracownik, b.hire_date
FROM EMPLOYEES a, EMPLOYEES b
WHERE a.first_name = 'Den' AND a.hire_date < b.hire_date;

SELECT e.first_name || ' ' || e.last_name AS Pracownik, e.hire_date
FROM EMPLOYEES e
JOIN EMPLOYEES den
ON den.first_name = 'Den'
WHERE e.hire_date > den.hire_date;

--2
SELECT e.first_name || ' ' || e.last_name AS Pracownik, e.hire_date, b.first_name || ' ' || b.last_name AS Menadżer, b.hire_date
FROM EMPLOYEES e
JOIN EMPLOYEES b
ON b.manager_id = e.manager_id
WHERE e.hire_date < b.hire_date;

--3
--Where wykonywane jest przed having

--4
SELECT MIN(salary) AS Minimalne, MAX(salary) AS Maksymalne, SUM(salary) AS Suma, ROUND(AVG(salary),0) AS Średnia
FROM EMPLOYEES;

--5
SELECT COUNT(DISTINCT manager_id) AS Ilość_Menadżerów
FROM DEPARTMENTS;

--6
SELECT DEPARTMENTS.department_name, DEPARTMENTS.department_id, COUNT(EMPLOYEES.department_id) AS Ilu, ROUND(AVG(EMPLOYEES.salary),0) AS Średnia
FROM DEPARTMENTS
JOIN EMPLOYEES
ON DEPARTMENTS.department_id = EMPLOYEES.department_id
GROUP BY DEPARTMENTS.department_name, DEPARTMENTS.department_id;

--7
SELECT COUNT(*) AS Ilu
FROM EMPLOYEES
WHERE department_id = 50;

--8
SELECT DISTINCT department_id
FROM EMPLOYEES
WHERE department_id IS NOT NULL;

--9
SELECT department_id, ROUND(AVG(salary),0) AS "Średnie wynagrodzenie"
FROM EMPLOYEES GROUP BY department_id
HAVING AVG(salary)>8000;

--10
SELECT ROUND(MAX(avg_salary),0)
FROM (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM EMPLOYEES
  GROUP BY department_id
);