블로그 과제 코드모음

22/11/05

-조인 주말과제
1.select e.EMPLOYEE_ID, e.FIRST_NAME, nvl(e2.FIRST_NAME, '관리자 없음'
from EMPLOYEES e, EMPLOYEES e2
where e.EMPLOYEE_ID = e2.manager_id and e.FIRST_NAME like '_t%';

2.select d.department_name, round(avg(e.salary),0)
from employees e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID and e.salary >= 5000
group by d.department_name
order by avg(e.salary) desc;

3.select e.first_name, e.salary, e.hire_date, d.department_name
from employees e, DEPARTMENTS d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID and to_char(hire_date,'yy') >= to_char(sysdate,'yy')-20

4.select j.job_title, count(e.employee_id)
from employees e, jobs j
where e.job_id(+) = j.job_id
group by  j.job_title;

select j.job_title, count(e.employee_id)
from employees e, jobs j
where j.job_title = e.employee_id(+)
group by  j.job_title;

-서브쿼리 주말과제
1.
select employee_id, first_name || ' ' || last_name  as NAME, hire_date
from employees
where to_char(hire_date,'yyyy')= '2005' and hire_date < (select hire_date
                   from employees
                   where first_name = 'Lisa');

2.
select e.first_name, e.salary, d.department_name
from employees e, departments d
where e.DEPARTMENT_ID = d.DEPARTMENT_ID and d.department_name = 'Sales'
      and e.salary < (select avg(salary)
                      from employees
                      where DEPARTMENT_ID = '100');

3.
select employee_id,  LAST_NAME, hire_date, salary
from employees
where  employee_id = (select manager_id
                      from employees
                      where LAST_NAME = 'De Haan');