--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