ORACLE SQL 조인과 서브쿼리 SCOTT 문제 풀이

조인

1. ACCOUNTING 부서 소속 사원의 이름과 입사일을 출력

SELECT ENAME, HIREDATE, DNAME
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO --USING (DEPTNO)
WHERE DNAME = 'ACCOUNTING';

2. 커미션을 받는 사원의 이름과 그가 속한 부서명을 출력

SELECT ENAME, DNAME, COMM
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
WHERE COMM>0;

3. 뉴욕에서 근무하는 사원의 이름과 급여를 출력

SELECT ENAME, SAL, LOC
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
WHERE LOC = 'NEW YORK';

4. KING과 동일한 근무지에서 근무하는 사원의 이름을 출력

SELECT ENAME, LOC
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
WHERE LOC = (SELECT LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE ENAME = 'KING');

 

 

 

조인과 서브 쿼리

<<1>> EMP와 DEPT TABLE을 JOIN하여 부서 번호, 부서명, 이름, 급여를 출력하라.

SELECT EMP.DEPTNO, DNAME, ENAME, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

<<2>> 이름이 'ALLEN'인 사원의 부서명을 출력하라.

SELECT DNAME, ENAME
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME = 'ALLEN';

<<3>> EMP Table의 데이터를 출력하되 해당사원에 대한 상관번호와 상관의 성명을 함께 출력하라.

SELECT EN.ENAME, MN.EMPNO 상관번호, MN.ENAME 상관성명
FROM EMP EN, EMP MN
WHERE EN.MGR=MN.EMPNO;

<<4>> DEPT Table 에는 존재하는 부서코드이지만 해당부서에 근무하는 사람이 존재하지 않는 경우의 결과를 출력하라.           

select *
from dept
where deptno !=all(select deptno from emp);

 

 

<<5>> 'ALLEN'의 직무와 같은 사람의 이름, 부서명, 급여, 직무를 출력하라.

SELECT ENAME, DNAME, SAL, JOB
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB=(SELECT JOB FROM EMP WHERE ENAME = 'ALLEN');

 

 

<<6>> 'JONES'가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일자, 급여를 출력하라.

SELECT EMPNO, ENAME, HIREDATE, SAL, EMP.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.DEPTNO = (SELECT EMP.DEPTNO FROM EMP WHERE ENAME='JONES');

        

<<7>> 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름, 부서명, 입사일, 지역, 급여를 출력하라.

SELECT EMPNO, ENAME, DNAME, HIREDATE, LOC, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL > (SELECT AVG(SAL) FROM EMP);

<<8>> 10번 부서 사람들 중에서 20번 부서의 사원과 같은 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일, 지역을 출력하라.

--방법1--
SELECT EMPNO, ENAME, DNAME, HIREDATE, LOC, JOB, EMP.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.DEPTNO = 10 AND JOB = ANY(SELECT JOB FROM EMP WHERE EMP.DEPTNO=20);

--방법2--
SELECT EMPNO, ENAME, DNAME, HIREDATE, LOC, JOB, EMP.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.DEPTNO = 10 AND JOB IN (SELECT JOB FROM EMP WHERE EMP.DEPTNO=20);

-- ORA-01427: single-row subquery returns more than one row 서브쿼리 오류.

AND JOB = (SELECT JOB FROM EMP WHERE EMP.DEPTNO=20) (X)

-> AND JOB = ANY(SELECT JOB FROM EMP WHERE EMP.DEPTNO=20) (O)

-> AND JOB IN (SELECT JOB FROM EMP WHERE EMP.DEPTNO=20) (O)


<<9>> 10번 부서 중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일자, 지역을 출력하라.

SELECT EMPNO, ENAME, DNAME, HIREDATE, LOC, JOB
FROM EMP, DEPT
WHERE EMP.DEPTNO=10 AND JOB != ALL(SELECT JOB FROM EMP WHERE EMP.DEPTNO=30);

<<10>> 10번 부서에 근무하는 사원의 사원번호, 이름, 부서명, 지역, 급여를 급여가 많은 순으로 출력하라.

SELECT EMPNO, ENAME, LOC, SAL, EMP.DEPTNO
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND EMP.DEPTNO=10
ORDER BY SAL DESC;

<<11>> 'MARTIN'이나 'SCOTT'의 급여와 같은 사원의 사원번호, 이름, 급여를 출력하라.

SELECT EMPNO, ENAME, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND SAL = ANY(SELECT SAL FROM EMP WHERE ENAME = 'MARTIN' OR ENAME = 'SCOTT');

<<12>> 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.

SELECT EMPNO, ENAME, SAL
FROM EMP, DEPT 
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

  
<<13>> 급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30);