--01
select first_name, last_name, salary
from employees
where salary> (select avg(salary)
from employees);

--02
select *
from employees
where department_id =
  (select department_id
   from employees
   where first_name = 'Alexis' and last_name = 'Bull');

--03
select first_name, last_name
from employees
where job_id =
  (select job_id
   from employees
   where last_name = 'Weiss');

select first_name, last_name
from employees
where job_id in
  (select job_id
   from employees
   where last_name = 'King');

--04
select first_name, last_name, department_id
from employees
where department_id in
  (select department_id
    from employees
    where last_name like 'R%');

--05
select count(department_id)
from employees
where department_id in
  (select department_id
  from employees
  where last_name not like 'R%');

--06
select first_name, last_name
from employees
where manager_id in
  (select employee_id
  from employees
  where last_name = 'Hunold');

--07
select first_name, last_name, salary
from employees
where salary > any
  (select salary
  from employees
  where department_id = 70);

select first_name, last_name, salary
from employees
where salary >
  (select min(salary)
  from employees
  where department_id = 70);

--08
select first_name, last_name, salary
from employees
where salary > ALL
  (select salary
  from employees
  where department_id = 80);

select first_name, last_name, salary
from employees
where salary >
  (select max(salary)
  from employees
  where department_id = 80);

--09
select first_name, last_name, salary
from employees
where salary >
  (select max(salary)
  from employees join departments
  using (department_id)
  where department_name = 'Marketing');


select first_name, last_name, salary
from employees
where salary >
  (select max(salary)
  from employees
  where department_id =
    (select department_id
    from departments
    where department_name = 'Marketing'));

--10
select first_name, last_name
from employees
where salary>
  (select avg(salary)
    from employees)
and job_id =
    (select job_id
     from employees
     where last_name = 'Abel');

--11
select department_id, count(department_id)
from employees
group by department_id
having count(department_id) >= all
  (select count(department_id)
   from employees
   group by department_id);

select department_id, count(department_id)
from employees
group by department_id
having count(department_id) =
  (select max(count(department_id))
   from employees
   group by department_id);

--12
select department_id, department_name, count(department_id)
from employees join departments
using (department_id)
group by department_id, department_name
having count(department_id) =
  (select max(count(department_id))
   from employees
   group by department_id);

--13
select department_id, avg(salary)
from employees
group by department_id
having avg(salary) =
  (select max(avg(salary))
   from employees
   group by department_id);

--14
select department_id, department_name, avg(salary)
from employees join departments
using (department_id)
group by department_id, department_name
having avg(salary) =
  (select max(avg(salary))
   from employees
   group by department_id);

--15
select first_name, last_name, salary, department_id
from employees zew
where salary =
(
  select max(salary)
  from employees wew
  where zew.department_id = wew.department_id
);

--16
select first_name, last_name, salary, department_name
from employees zew join departments dep
on zew.department_id = dep.department_id
where salary =
(
  select max(salary)
  from employees wew
  where zew.department_id = wew.department_id
);

--17
select first_name, last_name, salary
from employees
order by salary desc
fetch first 5 row only;

select * from(
select first_name, last_name, salary
from employees
order by salary desc)
where rownum=5;

--18
select first_name, last_name, hire_date
from employees
order by hire_date
fetch first 10 row only;

--19
select first_name, last_name, salary, job_title
from employees join jobs
using (job_id)
where job_title like '%Manager'
fetch first 5 row only;