[Oracle 과제] select 문제 / SubQuery 문제

본 문제는, Oracle Database 11g 실습 계정에 대한 문제 입니다.

1. emp테이블에서 등록되어 있는 인원수, 보너스에 NULL이 아닌 인원수, 보너스의 평균(보너스가 NULL이 아닌 평균, NULL포함한 평균),등록되어 있는 부서의 수(총개수와 중복을 제외한 개수)를 구하여 출력

select count(empno), count(comm), avg(comm), avg(nvl(comm,0)), count(deptno), count(distinct deptno) from emp;

2. 부서인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 조회

select deptno, count(*), sum(sal) from emp  group by deptno having(count(empno)>4);

3. 급여가 최대 2900 이상인 부서에 대해 부서번호, 평균 급여, 급여의 합을 조회

select deptno, avg(sal), sum(sal) from emp group by deptno having(max(sal)>2900);

4. 최고급여와 최소급여의 차이는 얼마인가?

select max(sal), min(sal) , max(sal)-min(sal) from emp;

5. 예시)

년도 count min max avg sum

---------------------------------------------

80 1 800 800 800 800

81 10 950 5000 2282.5 22825

82 2 1300 3000 2150 4300

83 1 1100 1100 1100 1100

select substr(hiredate,1,2) as 년도, count(*) as count, min(sal) as min, max(sal) as max, avg(sal) as avg, sum(sal)as sum from emp group by substr(hiredate,1,2) having(substr(hiredate,1,2)>=80 and substr(hiredate,1,2)<=83 )order by substr(hiredate,1,2);

6. 예시)

TOTAL 1980 1981 1982 1983

---------------------------------------

14 1 10 2 1

select count(*)as total, count(decode(to_char(hiredate,'YYYY'),'1980',0)) as "1980",

count(decode(to_char(hiredate,'YYYY'),'1981',0)) as "1981",

count(decode(to_char(hiredate,'YYYY'),'1982',0)) as "1982",

count(decode(to_char(hiredate,'YYYY'),'1983',0)) as "1983" from emp;

7. 예시)

업무 10 20 30 Total

-------------------------------------------------

ANALYST 6000 6000

CLERK 1300 1900 950 4150

MANAGER 2450 2975 2850 8275

PRESIDENT 5000 5000

SALESMAN 5600 5600

select distinct job as 업무, sum(decode(deptno,10,sal)) as "10",

sum(decode(deptno,20,sal)) as "20", sum(decode(deptno,30,sal)) as "30",

sum(nvl(decode(deptno,10,sal),0))+sum(nvl(decode(deptno,20,sal),0))+sum(nvl(decode(deptno,30,sal),0)) Total from emp group by job;

---------- Sub Query 문제

8. BLAKE와 같은 부서에 있는 모든 직원의 사번, 이름, 입사일자 조회

select empno, ename, hiredate from emp where deptno = (select deptno from emp where ename='BLAKE');

9. SELECT empno, ename, deptno, sal, comm FROM emp WHERE (sal, comm) IN(SELECT sal, comm FROM emp WHERE deptno=30);

  이 쿼리를 수정하여 comm이 null인 직원도 출력할 수 있도록 하시오

select empno, ename, deptno, sal, comm from emp where (sal, nvl(comm,0)) in(select sal, nvl(comm,0) from emp where deptno=30);

 

10. 평균 급여 이상을 받는 직원들의 사번, 이름을 조회. 단, 급여가 많은 순으로 정렬

select empno, ename, sal from emp where sal>=(select avg(sal) from emp) order by sal desc;

11. 이름에 'T'자가 들어가는 직원이 근무하는 부서에서 근무하는 직원의 사번, 이름, 급여 조회

select empno, ename, sal from emp where deptno in(select deptno from emp where ename like'%T%');

12. 부서의 위치가 dallas인 모든 직원에 대해 사번, 이름, 급여, 업무를 조회 // dept테이블

select empno, ename, sal, job from emp where  deptno = (select deptno from dept where loc='DALLAS');

13. King에게 보고하는 모든 직원의 이름과 부서,업무,급여를 조회

select ename, deptno, job, sal from emp where mgr = (select empno from emp where ename='KING');

14. 월급이 30번 부서의 최저 급여보다 높은 직원의 사번, 이름, 급여를 조회

select empno, ename, sal from emp where sal >(select min(sal) from emp where deptno=30);

15. 10번 부서에서 30번 부서의 직원과 같은 업무를 하는 직원의 이름과 업무를 조회

select  ename, job from emp e where deptno=10 and job in(select job from emp where  deptno=30 and (e.job = job));