문제풀이2번째

empno 사원번호
ename 사원이름
job 부서이름?
mgr 관리자 번호
hiredate 입사일
sal 월급
comm 커미션
deptno 부서번호

1. 10번부서와 같은일을 하는 사원의 사원번호,이름,급여를 조회하고 급여가 많은 순으로 출력하세요
2. 급여가 3000 이상이면, 급여+급여의 15%의 격려금을,
급여가 2000 이상이면, 급여+급여의 10%의 격려금을,
급여가 1000 이상이면, 급여+급여의 5%의 격려금을,
그렇지 않으면 급여를 구하여, 이름, 직업, 급여,격려금을 표시하시오
3.사원번호,이름,연봉을 연봉이 높은순으로 정렬하려 출력하세요.
(연봉은 sal*12+comm으로 계산)

 

use sample;
select * from emp;
select empno,sal from emp where deptno=10 order by sal desc; -- 1번문제

select * from emp;
select sal,ename,job, 
case when sal>3000 then sal*2*0.15
when sal>2000 then sal*2*0.1
when sal>1000 then sal*2*0.05
else sal 
end from emp; -- 2번문제

select ifnull(comm,0)+(sal*12) from emp order by ifnull(comm,0)+(sal*12) desc; 
select empno,ename,ifnull(comm,0)+(sal*12) from emp order by ifnull(comm,0)+(sal*12) desc; -- 3번문제