bd lab 7.01.2019 recapitulare incomplet

complet: notepad.pw/cristinabd

--LAB 2

--PROB 9

select round(to_date('31-DEC-' || to_char(sysdate, 'YYYY')) - sysdate) as "Zile"
from dual;

--prob 22
select ang.last_name, ang.employee_id, mgr.last_name, mgr.employee_id
from employees ang
left join employees mgr
on (ang.manager_id = mgr.employee_id);

--prob 25
select last_name, job_id, job_title, department_name, salary
from employees
join departments
using (department_id)
join jobs
on e.job_id = j.job_id;

--lab 3
--ex2
select employee_id, last_name, department_id, department_name
from employees e join departments d
on e.department_id = d.department_id
where e.department_id =
      (select department_id
      from employees
      where lower(last_name) like '%t%');

--varianta cu join
select last_name, employee_id, department_id, department_name
from employees e join departments d
on e.department_id = ang.department_id
join employees ang
on e.department_id = ang.department_id
where lower(ang.last_name) like '%t%'
order by e.last_name;

--ex 4 lab 3

select e.department_id, d.department_name, last_name, job_id,
to_char(e.salary, '$99,999.00') as 'Salary'
from employees e
right join departments d
on e.department_id = d.department_id
where lower(department_name) like '%ti%';

--ex17
select last_name, salary
from employees
where manager_id =
      (select employee_id
      from employees
      where manager_id is null
      );

--ex 18
select last_name, department_id, salary
from employees
where nvl((department_id, 0), salary) in
    (select department_id, salary
    from employees
    where commission_pct is not null
    );


--ex3 lab4
select job_id, min(salary), max(salary), sum(salary), avg(salary)
from employees
where min(salary) > 5000