Do it! 오라클로 배우는 데이터베이스 입문 : 연습문제 8,9 다시

 

10일쯤 풀어서 포스팅했던 join 문제 ,서브쿼리 문제 다르게 풀어보았다.

 

JOIN 연습문제 : 

Do it! 오라클로 배우는 데이터베이스 입문 : 연습문제 8 (tistory.com)

Do it! 오라클로 배우는 데이터베이스 입문 : 연습문제 8

 

서브쿼리 연습문제: 

Do it! 오라클로 배우는 데이터베이스 입문 : 연습문제 9 (tistory.com)

Do it! 오라클로 배우는 데이터베이스 입문 : 연습문제 9

 

SQL 99를 잘 안쓴다는건 내가 잘못 기억한거 같고 자주 쓴대서

교재보고 공부한 뒤에 위의 포스팅과 같은 문제(조인,서브쿼리)를 SQL-99 문법으로 다시 풀어봤다.

 

조인 문제 페이지 239 

그 유난이 문제였던 4번문제...( 외부조인)  

테이블이 4개인데, 3가지 외부조인을 다써야 문제처럼 출력이 되었다. 흠 

FULL OUTER JOIN도 써서 다시 해봄 

 

--1
SELECT DEPTNO, DNAME, EMPNO,ENAME,SAL
	FROM EMP E NATURAL JOIN DEPT D
 WHERE SAL > 2000
 	ORDER BY DEPTNO, EMPNO;

--2
SELECT DEPTNO, TRUNC(AVG(NVL(SAL,0))) AS AVG_SAL, MAX(SAL) AS MAX_SAL,
	MIN(SAL) AS MIN_SAL,
	COUNT(*) AS CNT
	FROM EMP E JOIN DEPT D USING (DEPTNO)
GROUP BY DEPTNO
ORDER BY DEPTNO;

--3
SELECT D.DEPTNO, DNAME, EMPNO,
	ENAME, JOB, SAL
FROM EMP E JOIN DEPT D ON (E.DEPTNO= D.DEPTNO)
ORDER BY DEPTNO, ENAME;

--4
SELECT D.DEPTNO, DNAME, E1.EMPNO, E1.ENAME, E1.MGR,
	E1.SAL, E1.DEPTNO, S.LOSAL, S.HISAL, S.GRADE,
 	E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
	FROM EMP E1 JOIN DEPT D ON (E1.DEPTNO = D.DEPTNO)
		JOIN SALGRADE S ON (E1.SAL>= S.LOSAL AND E1.SAL<=S.HISAL)
		JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
	ORDER BY D.DEPTNO, E1.EMPNO;
--이제 여기서 아우터조인, 외부조인만 하면 된다./ MGR과 DEPT 의 부서

CREATE TABLE EMP_CP AS SELECT * FROM EMP;

SELECT D.DEPTNO, DNAME, E1.EMPNO, E1.ENAME, E1.MGR,
	E1.SAL, E1.DEPTNO, S.LOSAL, S.HISAL, S.GRADE,
 	E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
	FROM EMP E1 LEFT OUTER JOIN EMP_CP E2 ON (E1.MGR = E2.EMPNO)
		RIGHT OUTER JOIN DEPT D ON (E1.DEPTNO = D.DEPTNO)
		LEFT OUTER JOIN SALGRADE S ON (E1.SAL BETWEEN S.LOSAL AND S.HISAL)
		ORDER BY D.DEPTNO, E1.EMPNO;
    -- SALGRADE까지 아우터 조인 처리해야   DEPT의 개발부서 그게 나온다



--FULL OUTER 써봄.  복제 테이블 제외시 출력 값 같음 
SELECT D.DEPTNO, DNAME, E1.EMPNO, E1.ENAME, E1.MGR,
	E1.SAL, E1.DEPTNO, S.LOSAL, S.HISAL, S.GRADE,
 	E2.EMPNO AS MGR_EMPNO, E2.ENAME AS MGR_ENAME
 	FROM EMP E1 LEFT OUTER JOIN EMP_CP E2 ON (E1.MGR = E2.EMPNO)
		FULL OUTER JOIN DEPT D ON (E1.DEPTNO = D.DEPTNO)
		FULL OUTER JOIN SALGRADE S ON (E1.SAL BETWEEN S.LOSAL AND S.HISAL)
		ORDER BY D.DEPTNO, E1.EMPNO;

 

 

서브쿼리 262 페이지,  역시 SQL-99문법으로 다시 풀어봄 

 

--1
SELECT
	JOB, EMPNO, ENAME, SAL, D.DEPTNO, DNAME
	FROM EMP E JOIN DEPT D ON ( E.DEPTNO = D.DEPTNO)
 WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN')
 ORDER BY SAL DESC;


--2
SELECT EMPNO, ENAME, DNAME, TO_CHAR(HIREDATE,'YYYY-MM-DD'), LOC, SAL, GRADE
	FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
		JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE SAL > (SELECT AVG(NVL(SAL,0)) FROM EMP)
	ORDER BY SAL DESC, EMPNO;


--3
SELECT EMPNO, ENAME, JOB, D.DEPTNO, DNAME,LOC
	FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO=30)
 AND E.DEPTNO=10;

--4 : 다중햄 함수 사용 
SELECT EMPNO, ENAME, SAL, GRADE
	FROM EMP E JOIN SALGRADE S ON (SAL BETWEEN LOSAL AND HISAL)
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY EMPNO;

-- 4: 다중행 함수 안쓴거
SELECT EMPNO, ENAME, SAL, GRADE
	FROM EMP E JOIN SALGRADE S ON (SAL BETWEEN LOSAL AND HISAL)
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY EMPNO;