Do it! 오라클로 배우는 데이터베이스 입문 : 연습문제 9
오라클 학습용 계정 scott 로그인:
9 SQL문 속 또 다른 SQL문, 서브쿼리
연습문제 page 262
늘 힘들었지만 오늘 특히나 힘들었다 ㅠㅠㅠ
1번문제부터 막혀서 미치는 줄...
DEPT테이블하고 EMP 테이블의 열을 뽑아내는 문제는 익숙한데,
SALGRADE 요 테이블이 낯설어... 2,4번 문제에 나왔는데 멘붕와서 시간 엄청 날림
솔직히 조인쓰면 풀만했는데, 서브쿼리 공부하는게 목적이니 서브쿼리로 어떻게 푸나 계속 화면 봤음
SALGRADE는 EMP 테이블의 SAL열과 관련이 있지만, 딱 일치하는게 아니라서 범위로 어떻게 잡나
이것저것 찾아봄 사실 이 테이블 자체에 대해 잘 몰랐네
그렇게 시간 헛보내다가 조인먼저 써서 풀고 그걸 보고 조인 안쓰는걸로 변환하는 방식으로 해보니, 되긴 되더라
헤맨 시간이 허무할정도로 한번 하고나니 쭉 풀림 특히 4번이 너무 간단해서 ...너무 허무했다
----------------------------------
(문제는 아래에 사진으로)
4번문제 코드/ 4번만 코드 4개다.
조인쓴거 2개, 안쓴거 2개, 교재문제에 다중행 함수 쓰거나 안쓴거 둘다 하라길래 했음 그래서 총 4개
SALGRADE 테이블 때문에 걱정했는데, 다행히 결과 다 똑같이 출력 됌
--4번문제
--조인쓴거
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E, SALGRADE S
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN')
AND (E.SAL BETWEEN S.LOSAL AND S.HISAL)
ORDER BY E.EMPNO;
--조인쓴거: 다중행 함수 미사용 (MAX) /SAL > ALL
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E, SALGRADE S
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN')
AND (E.SAL BETWEEN S.LOSAL AND S.HISAL)
ORDER BY E.EMPNO;
--조인 미사용
SELECT DISTINCT EMPNO, ENAME, SAL,
(SELECT GRADE FROM SALGRADE WHERE SAL>=LOSAL AND SAL<= HISAL) AS GRADE
FROM EMP E, SALGRADE S
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN')
ORDER BY E.EMPNO;
--다중행 함수 미사용 (MAX) /SAL > ALL
SELECT DISTINCT EMPNO, ENAME, SAL,
(SELECT GRADE FROM SALGRADE WHERE SAL>=LOSAL AND SAL<= HISAL) AS GRADE
FROM EMP E, SALGRADE S
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN')
ORDER BY E.EMPNO;
조인 안쓰니까 아래처럼 중복이 미친듯이 되어 나오더라.
하지만 난 바로 DISTICT을 생각해내서 EMPNO에 넣었음 해결 끝 ㅋ
그리고 다중행 함수 쓴거 안쓴거 차이
SAL > (SELECT MAX(SAL)
SAL > ALL
-포인트는 MAX를 쓰면 다중연산자를 안써줘도 된다.
3,4번문제
3번문제 : 이건 EXISTS 써서 해보려 해는데, 이 키워드 특징이 답 없으면 걍 출력화면에 암것도 안뜨는 거라;;
쓸 수가 없었다 ㅠㅠㅠ 방법이 있을거 같은데...
SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME, LOC
FROM EMP , (SELECT DNAME, LOC FROM DEPT WHERE DEPTNO =10) D
WHERE JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO=30)
AND DEPTNO = 10;
1,2번 문제
1,2번 문제 코드:
조인안쓰니 정렬이 틀어져서 ORDER BY추가해줌
--1번문제-----------------------------------------------------
--조인쓴거
SELECT JOB, EMPNO, ENAME, SAL, E.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN') --단일행 서브쿼리
AND E.DEPTNO = D.DEPTNO;
--조인안쓴거
SELECT JOB, EMPNO, ENAME, SAL, E.DEPTNO,
(SELECT DNAME FROM DEPT WHERE DEPTNO=E.DEPTNO) AS DNAME
FROM EMP E
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN') --단일행 서브쿼리
ORDER BY SAL DESC;
-----------------------------------------------------------------
--2번문제
--조인 사용
SELECT EMPNO,ENAME, D.DNAME,
TO_CHAR(HIREDATE,'YYYY-MM-DD') AS HIREDATE,
D.LOC,
E.SAL,
S.GRADE
FROM EMP E, SALGRADE S, DEPT D
WHERE SAL > (SELECT AVG(NVL(SAL,0)) FROM EMP)
AND E.DEPTNO=D.DEPTNO
AND (E.SAL BETWEEN S.LOSAL AND S.HISAL)
ORDER BY E.SAL DESC, E.EMPNO ASC;
--SAL > 여기에 다중연산자 안들어가도 되나??
-- 조인안쓴거
SELECT EMPNO,ENAME,
(SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO) AS DNAME,
TO_CHAR(HIREDATE,'YYYY-MM-DD') AS HIREDATE,
(SELECT LOC FROM DEPT WHERE DEPTNO = E.DEPTNO) AS LOC,
SAL,
(SELECT GRADE FROM SALGRADE WHERE SAL>=LOSAL AND SAL<= HISAL) AS GRADE
FROM EMP E
WHERE SAL > (SELECT AVG(NVL(SAL,0)) FROM EMP)
ORDER BY SAL DESC, EMPNO ASC;
* SCOTT계정의 SALGRADE 테이블 **
--GRADE COL : 1~5등급 , 1등급이 급여 최고로 낮은 등급
SELECT GRADE FROM SALGRADE WHERE LOSAL <= 1600 AND HISAL >= 1600; --3 등급
//뭘 할지 모르겠었을 때, 세일즈맨 조건으로 등급 구해본거 같은데... 코드가 어디갔는지 안보임
이게 사실 왜 되는지 아직도 좀 헷갈리지만...
EMP테이블하고 SALGRADE 테이블하고 연결접점 (?) 코드다 서브쿼리로 작성
SELECT * FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;