뷰-인라인(inline)

📝 뷰-인라인이란?
별칭을 사용하는 서브쿼리 (일반적으로 from 절에서 사용한다.)
ex) select * from (select employee_id, last_name from employees);

 

뷰-Top N분석
Top N분석 : 조건에 맞는 최상위(최하위) 레코를 N개 식별해야 하는 경우에 사용한다.
ex) 최상위 소득자 3명, 최근 6개월동안 가장 많이 팔린 제품 3가지, 실적이 가장 좋은 영업사원 5명

 

MySQL에서 Top N 분석원리

• 원하는 순서대로 정렬한 후 최상위, 최하위를 구한다.

 

💡 참고
서브 쿼리를 이용해서 CREATE, INSERT, UPDATE, DELETE를 할수 있다.

 

✔️ 모든 사원의 평균 급여보다 적게 받는 사원들과 같은 부서에서 근무하는 사원의 사번, 이름, 급여, 부서번호를 구하시오

select e.employee_id, e.last_name, e.salary, d.department_id
from (select distinct department_id
	  from employees 
	  where salary < (select avg(salary) from employees)) d
join employees e on(d.department_id = e.department_id);

 

✔️ 모든 사원의 사번,이름,급여를 출력하시오.

-- 조건1) 사원 정보를 급여순으로 정렬
-- 조건2) 한 페이지당 5명을 출력
-- 조건3) 현재페이지가 3페이지라고 가정 (급여순 11등 ~ 15등까지 출력)

select a.*
from (select  employee_id, first_name, salary
     from employees e
     order by salary desc
    )a limit 0, 5;

 

✔️ 직급 아이디가 IT_PROG인 사원의 사번, 이름, 업무ID, 부서이름을 구하시오

select e.employee_id, e.first_name, job_id,
	  (select department_name from departments d 
	   where e.department_id = d.department_id) as department_name
from employees e
where job_id = 'IT_PROG';

 

✔️ 60번 부서에 근무하는 사원의 사번, 이름, 급여,부서번호, 60번부서의 평균급여를 구하시오

select e.employee_id, e.first_name, salary, department_id,
	(select avg(salary) from employees where department_id = 60) as avg60
from employees e
where department_id = 60;

 

✔️ 부서번호가 50인 부서의 총급여, 60인 부서의 평균급여, 90인 부서의 최고급여, 90인 부서의 최저급여를 구하시오

select
	(select sum(salary) from employees where department_id = 50) sum50,
	(select avg(salary) from employees where department_id = 60) avg60,
	(select max(salary) from employees where department_id = 90) max90,
	(select min(salary) from employees where department_id = 90) min90
from dual;   -- 가상테이블

 

✔️ employees table을 emp_copy라는 이름으로 복사 하시오

컬럼 이름은 동일하게

create table emp_copy
select * from employees;

 

✔️ 50번 부서의 사번(eid), 이름(name), 급여(sal), 부서번호(did)만 emp50 이라는 이름으로 생성하시오

create table emp50
select employee_id as "eid", first_name name, salary sal, department_id did
from employees
where department_id = 50;

 

✔️ 이외 참고

-- ex9) employees table에서 부서번호가 80인 사원의 모든 정보를 emp_blank에 insert하시오
insert into emp_blank
select * from employees
where department_id = 80;

select * from emp_blank;

-- ex10) employees table의 모든 사원의 평균 급여보다 적게 받는 emp50 table의 사원의 급여를 500 인상하시오.
update emp50
set sal = sal + 500
where sal < (select avg(salary) from employees);


select * from emp50;
insert into emp_blank
select * from employees
where department_id = 80;
-- ex11)employees table의 모든 사원의 평균 급여보다 적게 받는 emp50 table의 사원은 퇴사처리 하시오.

delete 
from emp50
where sal < (select avg(salary) from employees);