SQL 조인, 서브쿼리 연습문제 풀어보기

🎯 SQL Join과 서브쿼리를 활용해 다양한 예제를 풀어본다.

 

 

 

 

JOIN 실습문제

SCOTT 계정의 연습용 테이블 사용

오라클 조인도 사용해보고 ANSI조인도 사용해보기

 

 

1. 부서 테이블과 사원테이블에서 사번, 사원명, 부서코드, 부서명을 검색하시오. ( 사원명 오름차순 정렬할 것 )

 

SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM EMP, DEPT
ORDER BY ENAME;

 

 

2. 부서 테이블과 사원테이블에서 사번, 사원명 , 급여 , 부서명을 검색하시오. 단, 급여가 2000 이상인 사원에 대하여 급여기준으로 내림차순 정렬할 것.

 

 

 

3. 부서 테이블과 사원 테이블에서 사번, 사원명, 업무, 급여 , 부서명을 검색하시오. 단, 업무가 Manager이며 급여가 2500 이상인 사원에 대하여 사번을 기준으로 오름차순 정렬할 것.

 

 

 

4. 사원 테이블과 급여 등급 테이블에서 사번, 사원명, 급여, 등급을 검색하시오. 단, 등급은 급여가 하한값과 상한값 범위에 포함되고 등급이 4이며 급여를 기준으로 내림차순정렬할 것.

 

 

SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E, SALGRADE S
WHERE SAL BETWEEN LOSAL AND HISAL
AND GRADE = 4
ORDER BY SAL DESC;

 

 

5. 부서 테이블, 사원 테이블, 급여등급 테이블에서 사번, 사원명, 부서명, 급여 , 등급을 검색하시오. 단, 등급은 급여가 하한값과 상한값 범위에 포함되며 등급을 기준으로 내림차순 정렬할 것.

 

where절에서 e와 d에서 겹치는 컬럼을 명시해주지 않으면 카타 조인이 되어서 올바른 데이터값을 불러오지 못한다.

 

SELECT EMPNO, ENAME, DNAME, SAL, GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND SAL BETWEEN LOSAL AND HISAL
ORDER BY GRADE DESC;

 

 

6. 사원 테이블에서 사원명과 해당 사원의 관리자명을 검색하시오

 

 

SELECT E.ENAME 사원명, M.ENAME 관리자명
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;

 

 

7. 사원 테이블에서 사원명, 해당 사원의 관리자명, 해당 사원의 관리자의 관리자명을 검색하시오

 

 

SELECT E.ENAME 사원명, M.ENAME 관리자명, MM.ENAME "관리자의 관리자명"
FROM EMP E, EMP M, EMP MM
WHERE E.MGR = M.EMPNO AND M.MGR = MM.EMPNO;

 

 

8. 7번 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오.

 

틀린답안)

왜 OUTER가 EMPNO 쪽으로 붙는지 이해해야 한다.

 

SELECT E.ENAME 사원명, M.ENAME 관리자명, MM.ENAME "관리자의 관리자명"
FROM EMP E, EMP M, EMP MM
WHERE E.MGR = M.EMPNO(+) AND M.MGR = MM.EMPNO(+);

이 사원의 매니저 값이 NULL 이니까 M 을 조회하는 컬럼에 (+)를 붙인다.

 

 

9. 20번 부서의 이름과 그 부서에 근무하는 사원의 이름을 출력하시오.

 

 

SELECT DNAME, ENAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.DEPTNO = 20;

 

 

10. 커미션을 받는 사원의 이름, 커미션, 부서이름 출력하시오.

 

왜 틀렸나? >> 공통컬럼 명시가 잘못됐다.

 

정답! IS NOT NULL도 잘 사용했다

 

 

11. 이름에 ‘A’ 가 들어가는 사원들의 이름과 부서명 출력하기.

 

 

 

12. DALLAS에 근무하는 사원 중 급여 1500 이상인 사원의 이름, 급여, 입사일 , 보너스(comm)을 출력하시오.

 

 

 

13. 자신의 관리자 보다 연봉(sal)을 많이 받는 사원의 이름과 연봉을 출력하시오.

 

SELECT ENAME, SAL
FROM EMP E JOIN EMP M ON E.MGR = M.EMPNO
WHERE E.SAL > M.SAL;

-- 땡!ERRRRRRORRRRR

 

자신의 관리자의 연봉과 자신의 연봉을 비교

 

내가 놓친 부분 >> 셀프 조인한 상황에서 똑같은 컬럼을 가진 테이블 2개가 존재하기 때문에 SELECT에서도 정보를 뽑아올 테이블명을 명시해 주어야 한다.

 

 

14. 직원 중 현재시간 기준으로 근무 개월 수가 30년(12 * 30개월) 보다 많은 사람의 이름, 급여 , 입사일 , 부서명을 출력하시오

 

SELECT ENAME, SAL, HIREDATE, DNAME
FROM EMP E JOIN DEPT D USING(DEPTNO)
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE) > 360;

 

MONTHS_BETWEEN을 사용할 때 S 빠뜨리지 않게 주의할 것.

 

 

15. 각 부서별로 1982년 이전에 입사한 직원들의 인원수를 출력하시오.

 

 

-- 오라클 조인

SELECT DEPTNO 부서번호, COUNT(ENAME) 인원수
FROM EMP E, DEPT D
where d.deptno = e.deptno
and to_char(e.hiredate, 'yyyy') <=1982
group by d.dname;

 

 

 

---

 

서브쿼리 실습문제

 

1. 사원 테이블에서 BLAKE 보다 급여가 많은 사원들의 사번, 이름 , 급여를 검색하시오.

사원테이블에 BLAKE의 급여를 먼저 찾는다.

WHERE 절에 비교연산자를 사용해 SELECT문을 완성한다.

 

 

2. 사원 테이블에서 MILLER 보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색하시오

 

나의 틀린 문장

 

밀러보다 늦게 입사한 거면 HIREDATE 숫자가 더 커야 하니까 부등호는 반대가 되어야한다.

 

SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE > (SELECT HIREDATE
FROM EMP
WHERE ENAME = 'MILLER');

정답!

 

 

3. 사원 테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름 , 급여를 검색하시오.

 

 

 

4. 사원 테이블에서 부서별 최대 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 검색하시오.

 

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
	      FROM EMP
	      GROUP BY DEPTNO);

부서는 4개가 있고 그중 40은 데이터값이 없다. 따라서 3개 부서에 대한 최대급여 서브쿼리가 리턴된다.

그룹함수는 단일행을 리턴한다고 했는데, 왜 저기에 복수행 서브쿼리의 연산자(IN)가 붙어도 결과가 잘 나오는거지? 혼란스러웠는데, GROUP BY가 붙어서 복수행 결과가 출력되는 서브쿼리임을 확인했다.

그래서 동등연산자(=)가 아닌 IN 연산자가 사용된 것이다.

 

 

5. Salgrade가 2등급인 사원들의 평균 급여보다 적게 받는 사원 정보를 검색하시오.

 

SELECT * FROM EMP E, SALGRADE S
WHERE SAL < (SELECT AVG(SAL)
FROM EMP
WHERE S.GRADE = 2);

틀렸다! 이 쿼리문은 8명의 정보를 출력하게 된다.

두 테이블은 공통컬럼이 없는 논 이퀄 조인이다.

따라서 서브쿼리 WHERE 절에도 비교연산자를 써줘야 하는데 엉뚱한 이퀄이 들어가있다.

 

SELECT *
FROM EMP
WHERE SAL <= (SELECT AVG(SAL)
		FROM EMP E JOIN SALGRADE S
		ON E.SAL BETWEEN S.LOSAL AND S.HISAL
		WHERE S.GRADE = 2);

정답! 서브쿼리 안에서 ANSI조인을 사용한 이 쿼리문은 5명의 정보를 출력한다.

 

서로 공통컬럼이 없기 때문에 내가 조인조건을 명시하지 않으면 검색조건을 저렇게 써줘도 먹히지 않는다.

등급만으로 사원정보를 뽑아봤을 때 불필요한 모든 정보가 리턴되는 걸 알 수 있는데, 이건 조건절이 명확하지 않았기 때문이다. 논이퀄 조건절에 SAL등급의 범위를  SAL BETWEEN S.LOSAL AND S.HISAL 이렇게 명시해야 한다.

평균샐이 샐등급 조건의 어떤 값 사이에 포함되는지 확인하는 것이 먼저고, 그 다음 2등급에 해당되는 값을 걸러낸다.

 

-- 오라클 조인으로 써보기

SELECT *
FROM EMP
WHERE SAL <= (SELECT AVG(SAL)
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND S.GRADE = 2); -- 순서도 중요하다는 걸 알았다

 

 

 

😜 NEXT : SQL DML 명령문 알아보기