SQL 예제 모음 (1)

use SCOTT;
#1
-- 특별한 커미션 정책이 시행되었다. 기존 커미션은 없던 것으로 하고, 부하직원 한 명당 기존 임금의 3%만큼의 커미션이 추가된다. 
-- 단, 부하직원의 부하직원까지 커미션이 책정되는데 이런 경우는 명당 1%만큼의 커미션이 추가로 붙는다. 각 직원별 변경되는 커미션을 계산하시오.
select e1.empno, sum(0.03 * ifnull(e2.sal, 0)), sum(0.01 * ifnull(e3.sal, 0)), 
sum(0.03 * ifnull(e2.sal, 0)) + sum(0.01 * ifnull(e3.sal, 0)) "NEW COMM"
from emp e1 left outer join emp e2
on e1.empno = e2.mgr
left outer join emp e3
on e2.empno = e3.mgr
group by e1.empno;

#2
-- 상사의 급여등급이 4인 사원의 번호와 사원명을 출력하세요. 
-- (사원의 번호로 오름차순 정렬)
select empno, ename
from emp
where mgr in (
	select e.empno
	from emp e inner join salgrade s
	on e.sal between s.losal and s.hisal
	where s.grade = 4
)
order by empno;
                
#3
-- 업무가 salesman인 사람들 중 가장 연봉이 높은 사람과, 그 연봉의 등급을 출력하세요.
select e.ename, s.grade
from salgrade s
join emp e 
on e.sal between s.slosal and s.hisal
where e.job = "salesman"
order by e.sal desc
limit 1;
    
#4
-- 커미션이 존재하지 않는 사원의 관리자의 사원번호, 이름, 부서이름을 출력하시오.
select empno, ename, dname
from emp e
left outer join dept d
on e.deptno = d.deptno
where empno in (select distinct mgr
				from emp
				where comm is null)
order by e.ename;

#5
--  근속년수가 30년 이상인 인원을 근무지명과 그 근무지에 몇명있는지 출력하시오.
# 데이트 포맷 사용시 참고
select dname, count(dname)
from dept d
join emp e
on e.deptno = d.deptno
where e.deptno in (select deptno
				from emp
				where date_format(now(), '%Y-%m-%d') -date_format(hiredate, '%Y-%m-%d') >= 30
				)
group by dname;

# 이 문제 정해
select dname, count(*)
from emp e inner join dept d
on e.deptno = d.deptno
where year(now()) - year(hiredate) >= 30
group by e.deptno;


#6
--  각 사원의 연봉과 해당 사원의 상사의 연봉의 차를 출력하세요
--  출력 컬럼 : 사원의 이름, 상사의 이름, 사원과 상사의 연봉 차이 (상사가 없다면 '-' 출력)
select e.ename, m.ename, ifNULL(abs(e.sal - m.sal), '-') as '연봉차이'
from emp e left join emp m
on e.mgr = m.empno;

#7
-- 'CHICAGO'에 근무하는 직원들의 EMPNO, ENAME, JOB, GRADE 를 알고 싶다. 
-- 이때, 연봉이 가장 높은 3명만 출력하시오
select e.empno, e.ename, e.job, e.sal, s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal
join dept d
on e.deptno = d.deptno
where ucase(d.loc) = 'CHICAGO'
order by e.sal desc
limit 3;

#8
-- 직업이 'SALESMAN'인 사원들 중 커미션을 포함한 가장 높은 연봉 을 받는 사원의 사번, 이름, 연봉을 출력하세요.
select empno, ename, IFNULL(sal + comm , sal) '연봉'
from emp  
where ucase(job) = 'SALESMAN'
	  and 
      IFNULL(sal + comm , sal) = (select max(IFNULL(sal + comm , sal)) 
								  from emp 
								  where ucase(job) = 'SALESMAN');

#9
-- 회사는 급여 등급 테이블을 커미션을 포함하여 각 직원들에게 적용하고자 한다.
-- 이때 평균 등급이 가장 높은 부서명과 그 부서의 평균등급을 출력하라(평균 등급은 반올림하여 계산한다.)
SELECT D.DNAME, ROUND(AVG(G.GRADE)) R
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
JOIN SALGRADE G
ON E.SAL BETWEEN G.LOSAL AND G.HISAL
GROUP BY E.DEPTNO
ORDER BY R DESC
LIMIT 1;

#10
-- 부서번호가 10번인 사원들 중 급여 등급이 5인 사원의 사원명, 급여, 급여등급을 조회해주세요
SELECT E.ENAME, E.SAL, G.GRADE
FROM EMP E
JOIN SALGRADE G
ON E.DEPTNO = 10
AND G.GRADE = 5;