SELECT last_name, salary FROM employees
WHERE salary>(SELECT avg(salary) FROM employees where department_id IN (20,80,90) AND last_name LIKE '%a%'); --zad1

SELECT d.last_name, d.employee_id, d.salary, department_name, city
FROM employees d
JOIN departments on d.department_id = departments.department_id
JOIN locations on locations.location_id = departments.location_id
WHERE salary>(SELECT avg(salary) from employees e where e.department_id=d.department_id) AND department_name!='Sales'; --zad2

SELECT first_name, last_name, hire_date FROM employees
WHERE hire_date>(SELECT hire_date from employees WHERE salary=(Select max(salary) from employees)); --zad3

SELECT department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE department_name!='Executive'
HAVING avg(salary) =
  (SELECT max(avg(salary))
   FROM employees
   JOIN departments ON employees.department_id = departments.department_id
   WHERE department_name!='Executive'
   GROUP BY department_name)
GROUP BY department_name; --zad4

SELECT department_name FROM employees
JOIN departments ON employees.department_id = departments.department_id
HAVING COUNT(employee_id)= (SELECT MAX(COUNT(employee_id)) FROM employees WHERE department_id IS NOT NULL
GROUP BY department_id)
GROUP BY department_name; --zad5

SELECT department_name FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE hire_date between to_date('2005/01/01','YYYY/MM/DD') AND to_date('2005/12/31','YYYY/MM/DD')
HAVING COUNT(employee_id)= (SELECT MAX(COUNT(employee_id)) FROM employees WHERE department_id IS NOT NULL AND hire_date between to_date('2005/01/01','YYYY/MM/DD') AND to_date('2005/12/31','YYYY/MM/DD')
GROUP BY department_id)
GROUP BY department_name; --zad6

SELECT d.employee_id as manager_id, d.salary as manager_salary,
(Select count(employee_id) from employees e where e.manager_id=d.employee_id) as liczba_podwladnych
FROM employees d
WHERE (Select count(employee_id) from employees e where e.manager_id=d.employee_id)!=0;--zad7

SELECT e.last_name, e.salary FROM employees e
WHERE e.salary>(select d.salary from employees d
where d.employee_id=e.manager_id) and e.manager_id IS NOT NULL; --zad8