0203 오라클 DB 문제풀이 - 조인(JOIN), 서브쿼리

1. JOIN 239P

Q1) 보충

--Q1 
--SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME, E.SAL
    FROM DEPT D, EMP E
   WHERE D.DEPTNO = E.DEPTNO
     AND E.SAL > 2000
   ORDER BY D.DEPTNO;
SELECT * FROM DEPT;

--SQL-99 방식
SELECT D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME,E.SAL
FROM DEPT D JOIN EMP E ON (D.DEPTNO =E.DEPTNO)
WHERE E.SAL > 2000
ORDER BY D.DEPTNO ;

 

Q2)

--문제2
--SQL-99 이전 방식
SELECT D.DEPTNO, D.DNAME,
TRUNC(AVG(SAL)) AS AVG_SAL,
MAX(SAL) AS MAX_SAL,
MIN(SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO
GROUP BY D.DEPTNO, D.DNAME;

--SQL-99 이후 방식
SELECT D.DEPTNO, D.DNAME,
TRUNC(AVG(SAL)) AS AVG_SAL,
MAX(SAL) AS MAX_SAL,
MIN(SAL) AS MIN_SAL,
COUNT(*) AS CNT
FROM DEPT D JOIN  EMP E ON (D.DEPTNO =E.DEPTNO)
GROUP BY D.DEPTNO, D.DNAME;

Q3)

--문제 3
--SQL-99 이전 방식
SELECT D.DEPTNO,D.DNAME,E.EMPNO,E.ENAME,E.JOB,E.SAL
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO(+)
ORDER BY D.DEPTNO, E.ENAME;

--SQL-99 이후 방식
SELECT D.DEPTNO,D.DNAME,E.EMPNO,E.ENAME,E.JOB,E.SAL
FROM DEPT D LEFT OUTER JOIN EMP E ON (D.DEPTNO=E.DEPTNO)
ORDER BY D.DEPTNO, E.ENAME;

Q4)

--문제 4
--SQL-99 이전 방식
SELECT * FROM EMP;
SELECT * FROM DEPT; 
SELECT * FROM SALGRADE;

SELECT D.DEPTNO,D.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 DEPT D, EMP E1, SALGRADE S, EMP E2
WHERE D.DEPTNO = E1.DEPTNO (+)
AND E1.SAL BETWEEN S.LOSAL(+) AND S.HISAL(+)
AND E1.MGR = E2.EMPNO(+)
ORDER BY D.DEPTNO, E1.EMPNO ;

--SQL-99 이후 방식
SELECT * FROM EMP;
SELECT * FROM DEPT; 
SELECT * FROM SALGRADE;

SELECT D.DEPTNO,D.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 DEPT D LEFT OUTER JOIN EMP E1 ON (D.DEPTNO = E1.DEPTNO)
LEFT OUTER JOIN SALGRADE S ON (E1.SAL BETWEEN S.LOSAL AND S.HISAL)
LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
ORDER BY D.DEPTNO, E1.EMPNO ;

2. 서브쿼리 262P

Q1)

--Q1. 262P
--ALLEN 기준으로 어떻게? 
-- AND E.JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN');
SELECT E.JOB,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       D.DEPTNO,
       D.DNAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
 AND E.JOB = (SELECT JOB FROM EMP WHERE ENAME = 'ALLEN');

Q2) 

-- Q2. 
--중복제거 어떻게? AND 기능 참조
SELECT * FROM DEPT ; 
SELECT * FROM SALGRADE; 
--서브쿼리 : SELECT AVG(SAL) FROM EMP;
SELECT E.EMPNO,E.ENAME,D.DNAME,E.HIREDATE,E.SAL,S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY E.SAL DESC, EMPNO ASC;

 

Q3) 

--Q3.
--30번 부서 어떻게 넣어야 함?
--서브 쿼리 : SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 30;
--서브 쿼리의 개념은 마지막의 조건

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

Q4) 

--Q4.조인 어케함;;?
--서브 쿼리 : SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN'

--단일행
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO;

--다중행
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO;