Oracle 예제 문제
Oracle의 emp, dept 테이블 사용
-- 1. 덧셈연산자를 이용하여 모든 사원에 대해서 $300의 급여 인상을 계산한 후 사원의 이름, 급여, 인상된 급여를 출력하시오.
select ename, sal, sal+300 as up_sal
from emp;
-- 2. 사원의 이름, 급여, 연간 총 수입을 총 수입이 많은 것부터 작은 순으로 출력하시오, 연간 총수입은 월급에 12를 곱한 후 $100의 상여금을 더해서 계산하시오.
select ename, sal,ysal
from (select ename , sal, sal*12 as ysal
from emp)
order by ysal asc;
select ename , sal, sal*12 as ysal
from emp
order by ysal asc;
-- 3. 급여가 2000을 넘는 사원의 이름과 급여를 표현, 급여가 많은 것부터 작은 순으로 출력하시오.
select ename, sal
from emp
where sal > 2000
order by sal desc;
-- 4. 사원번호가 7788인 사원의 이름과 부서번호를 출력하시오.
select ename, deptno
from emp
where empno = 7788;
-- 5. 급여가 2000에서 3000 사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.
select ename, sal
from emp
where sal between 2000 and 3000;
-- 6. 1981년 2월 20일 부터 1981년 5월 1일 사이에 입사한 사원의 이름, 담당업무, 입사일을 출력하시오.
select ename, job, hiredate
from emp
where hiredate between '1981-02-20' and '1981-05-01';
-- 7. 부서번호가 20 및 30에 속한 사원의 이름과 부서번호를 출력, 이름을 기준(내림차순)으로 영문자순으로 출력하시오.
select ename, deptno
from emp
where deptno in (20, 30)
order by ename desc;
-- 8. 사원의 급여가 2000에서 3000사이에 포함되고 부서번호가 20 또는 30인 사원의 이름, 급여와 부서번호를 출력, 이름순(오름차순)으로 출력하시오.
select ename, sal, deptno
from emp
where sal between 2000 and 3000 and deptno in (20, 30)
order by ename asc;
-- 9. 1981년도에 입사한 사원의 이름과 입사일을 출력하시오. (like 연산자와 와일드카드 사용)
select ename, hiredate
from emp
where hiredate like '1981%';
SELECT ENAME 사원명, JOB 담당업무, SAL 급여
FROM EMP
WHERE NOT SAL IN(1600,950,1300);
SELECT ENAME 사원명, JOB 담당업무, SAL 급여
FROM EMP
WHERE sal not IN(1600,950,1300);
------------------------------------------------------------
--기본 쿼리
--각 부서별 평균 급여를 계산하되, 평균 급여가 2000 이상인 부서만 표시하세요.
select deptno, avg(sal) asal
from emp
group by deptno
having avg(sal) >= 2000
order by deptno asc;
--직급(JOB)별 최고 급여를 받는 직원의 이름, 직급, 급여를 조회하세요.
select e.ename, e.job, m.msal
from emp e, (select job, max(sal) as msal
from emp
group by job) m
where m.msal = e.sal
order by m.msal desc;
--입사일이 가장 오래된 직원 5명의 이름, 입사일, 부서명을 조회하세요.
select e.ename, e.hiredate, d.dname
from (select ename, deptno, hiredate, rank() over(order by hiredate asc) as rkdate
from emp) e, dept d
where e.deptno = d.deptno
and e.rkdate between 1 and 5;
------------------------------------------------------------
--조인 및 서브쿼리
--자신의 매니저보다 높은 급여를 받는 직원의 이름, 급여, 매니저 이름, 매니저 급여를 조회하세요.
select e.ename, e.sal, m.ename, m.sal
from emp e, (select empno,ename, sal from emp) m
where e.mgr = m.empno
and e.sal > m.sal;
--부서별로 가장 높은 급여를 받는 직원의 이름, 급여, 부서명을 조회하세요.
select e.ename, e.sal, e.deptno, d.dname
from emp e, (select max(sal) as msal
from emp
group by deptno) m, dept d
where e.sal = m.msal
and d.deptno = e.deptno;
--각 부서의 평균 급여보다 높은 급여를 받는 직원들의 이름, 급여, 부서명을 조회하세요.
select e.ename, e.sal, round(m.asal,1), d.dname
from emp e, dept d, (select deptno, avg(sal) as asal
from emp
group by (deptno)) m
where e.deptno = m.deptno
and e.deptno = d.deptno
and e.sal > m.asal;
------------------------------------------------------------
--분석 함수
--각 부서 내에서 급여 순위가 1~3위인 직원들의 이름, 급여, 부서명, 부서 내 급여 순위를 조회하세요.
select e.ename, e.sal, d.dname, d.deptno
from emp e, dept d, (select ename, sal, row_number() over(order by sal desc) as rs
from emp) r
where r.ename = e.ename
and r.rs <= 3
and d.deptno = e.deptno;
--각 직원의 급여와 해당 부서의 평균 급여와의 차이를 계산하세요.
select e.ename, e.sal- a.asal as sals, a.asal, e.deptno
from emp e, (select deptno, round(avg(sal),1) as asal
from emp
group by deptno) a
where e.deptno = a.deptno
order by sals desc;
------------------------------------------------------------
--피벗 및 언피벗
--입사 직원 수를 연도별로 피벗하여 표시하세요.
select
sum(decode(to_char(hiredate,'yyyy') , '1980', 1, 0)) as 입사1980
,sum(decode(to_char(hiredate,'yyyy') , '1981', 1, 0)) as 입사1981
,sum(decode(to_char(hiredate,'yyyy') , '1982', 1, 0)) as 입사1982
from emp;
------------------------------------------------------------
--복합 쿼리
--부서별 최고 급여자와 최저 급여자의 급여 차이를 계산하고, 그 차이가 가장 큰 상위 3개 부서의 정보를 조회하세요.
select e.*, e.max_sal - e.min_sal as mm
from( select deptno, max(sal) as max_sal, min(sal) as min_sal
from emp
group by deptno) e;