07. Oracle 중간연습문제

1. 이름이 ALLEN인 사원과 같은 업무를 하는 사람의 사원번호, 이름, 업무, 급여 추출

SELECT empno, ename, job, sal
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='ALLEN');

2. EMP 테이블의 사원번호가 7521인 사원과 업무가 같고, 급여가 7934번 사원보다 많은 사원의 사원번호, 이름, 담당업무, 입사일, 급여 추출

SELECT ename, job, hiredate, sal
FROM emp
WHERE job = (SELECT job FROM emp WHERE empno=7521) AND
sal > (SELECT sal FROM emp WHERE empno=7934);

 

3. EMP 테이블에서 급여의 평균보다 적은 사원의 사원번호, 이름, 업무, 급여, 부서번호 추출

SELECT empno, ename, job, sal, deptno
FROM emp
WHERE sal < (SELECT avg(sal) From emp);

4. 20번 부서의 최소급여보다 부서별 최소급여가 작은 부서의 부서번호, 최소 급여 추출

SELECT MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) < (SELECT MIN(sal) FROM emp WHERE deptno=20);

5. 업무별 급여 평균 중, 가장 작은 급여평균의 업무와 급여평균 추출

SELECT MIN(sal), job
FROM emp
GROUP BY job
HAVING AVG(sal) = (SELECT MIN(AVG(sal)) FROM emp GROUP BY job);

6. 업무별 최대 급여를 받는 사원의 사원번호, 이름, 업무, 입사일, 급여, 부서번호 추출

SELECT *FROM EMP;
SELECT empno, ename, job, hiredate, sal, deptno
FROM emp
WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY job);

7. 30번 부서의 최소급여를 받는 사원보다 많은 급여를 받는 사원의 사원번호, 이름, 업무, 입사일, 급여, 부서번호, 단 30번 부서는 제외하고 추출

SELECT empno, ename, hiredate, deptno
FROM emp
WHERE sal > (SELECT MIN(sal)  FROM emp WHERE deptno=30)
AND deptno!=30;

8. 급여와 보너스가 30번 부서에 있는 사원의 급여와 보너스가 같은 사원을 30번 부서의 사원은 제외하고 추출

SELECT *
FROM emp
WHERE (sal, NVL(comm,0)) IN (SELECT sal, NVL(comm,0) FROM emp WHERE deptno=30)
AND deptno != 30;

9. BLAKE와 같은 부서에 있는 모든 사원의 이름과 입사일자를 추출

SELECT ename, hiredate
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename='BLAKE');

10. 평균급여 이상을 받는 모든 사원에 대해 사원의 번호와 이름을 급여가 많은 순서로 추출

SELECT empno, ename
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%')
ORDER BY empno;

*다른 방법 (INSTR 함수)
INSTR(string, substring, position, occurrence)
INSTR('문자열 또는 칼럼명', '찾고 싶은 문자열', 시작 위치, 찾은 문자열이 여러개일 경우 몇 번째 나타나는 문자열인지
* 발견 인덱스는 생략 가능, 생략하면 모두 1로 간주

select deptno, empno, ename, sal from emp
where deptno in (select deptno from emp where instr(ename, 'T', 1, 1)!=0)
order by empno;

12. 부서위치가 CHICAGO인 모든 사원에 대해 이름,업무,급여 추출

SELECT ename, job, sal
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'CHICAGO');

13. KING에게 보고하는 모든 사원의 이름과 급여를 추출

SELECT ename, sal
FROM emp
WHERE mgr IN (SELECT empno FROM emp WHERE ename='KING');

14. FORD와 업무와 월급이 같은 사원의 모든 정보 추출

SELECT *
FROM emp
WHERE (job, sal) = (SELECT job, sal FROM emp WHERE ename = 'FORD');

15. 업무가 JONES와 같거나 월급이 FORD 이상인 사원의 이름,업무,부서번호,급여 추출

SELECT ename, job, deptno, sal
FROM emp 
WHERE job = (SELECT job FROM emp WHERE ename = 'JONES')
OR sal > (SELECT sal FROM emp WHERE ename = 'FROD');

16. SCOTT 또는 WARD와 월급이 같은 사원의 이름,업무,급여를 추출

SELECT ename, job, sal
FROM emp 
WHERE sal = (SELECT sal FROM emp WHERE ename = 'SCOTT')
OR sal = (SELECT sal FROM emp WHERE ename = 'WARD');