select trunc(sysdate, 'month') from dual;
--입사후 20주년 다음달 기념일자
select hire_date, trunc(add_months(hire_date, 20*12+1), 'month')
from employees;
--근속년
select round((sysdate - hire_date)/365.25, 2)
from employees;
--Neena가 근무하는 부서의 부서원 정보를 출력하시오.
--1-1.Neena가 근무하는 부서번호
select DEPARTMENT_ID
from EMPLOYEES
where first_name = 'Neena';
--1-2.90번 부서에 근무하는 사원들
select *
from EMPLOYEES
where DEPARTMENT_ID = 90;
--2. 한 쿼리문으로 나타내기 : Subquery(서브쿼리)
select *
from EMPLOYEES
where DEPARTMENT_ID = (select DEPARTMENT_ID
from EMPLOYEES
where first_name = 'Neena');
--Steven King(사장)보다 급여를 더 많이 받는 사람이 있는지 조회하시오. = 없당
select *
from employees
where salary > (select salary
from EMPLOYEES
where first_name = 'Steven' and last_name = 'King');
select *
from employees
where salary > all (select salary
from EMPLOYEES
where last_name = 'King'); --multi-row query결과가 나와야 하므로 all 연산을 적기
--퀴즈
/* Seattle에 근무하는 사원들의 정보를 출력하시오(단, 서브쿼리로). */
select *
from EMPLOYEES
where department_id = all (select DEPARTMENT_ID
from DEPARTMENTS
where location_id in (select location_id
from LOCATIONS
where city = 'Seattle'));
--Correlated Query(상호연관서브쿼리)
select *
from EMPLOYEES e
where salary > (select avg(salary) from employees
where department_id = e.department_id); --자신이 소속된 그룹의 평균급여
--부서별로 최고 급여를 받는 직원을 조회하시오.
select DEPARTMENT_ID, EMPLOYEE_ID, SALARY
from employees
where (department_id, salary) in (select department_id, max(salary)
from employees
group by department_id)
order by department_id;
--서브쿼리가 테이블처럼 from절에 사용되는 것
select e.DEPARTMENT_ID, e.FIRST_NAME, e.SALARY
from employees e, (select department_id, max(salary) max_salary
from employees
group by department_id) dept_max_sal
where e.DEPARTMENT_ID = dept_max_sal.department_id
and e.salary = dept_max_sal.max_salary;
--(질문) select절에도 서브쿼리를 사용할 수 있는가? ㅇㅇ
select employee_id, salary, (select avg(salary)
from employees
where department_id = e.department_id) dept_avg_sal
from employees e; --자신의 급여와 자신이 소속된 부서의 평균급여도 같이 볼 수 있다.
--과제6
select department_name, avg(salary)
from employees, departments
where avg(salary) <= (select avg(salary) a
from employees
where department_id = 30)
group by department_name;
