README

테이블

1. 각 학과에서 가장 키가 큰 학생의 학번과 이름, 키를 출력

▶ 학과별로 가장 키가 큰 학생의 키

SELECT STU_HEIGHT, MAX(STU_HEIGHT)
FROM STUDENT
WHERE STU_DEPT IS NOT NULL
GROUP BY STU_DEPT;

▶ 코드

SELECT *
FROM  STUDENT S
INNER JOIN (
    SELECT STU_DEPT, MAX(STU_HEIGHT) AS HEIGHT
    FROM STUDENT
    WHERE STU_DEPT IS NOT NULL
    GROUP BY STU_DEPT
) S2 ON S.STU_DEPT = S2.STU_DEPT
    AND S.STU_HEIGHT = S2.HEIGHT
;

• 키 값만 동일하게 매치한다면 다른 학과에서 동일한 키 값을 가진 사람이 출력될 수 있다.
• 그렇기 때문에 과목도 같고 키 값도 일치하는 사람을 찾아야 한다.

▶ WHERE절 을 이용하여 학과와 최대 키 값을 매치할 수도 있다.

SELECT *
FROM  STUDENT S
WHERE (STU_DEPT, STU_HEIGHT) IN (
    SELECT STU_DEPT, MAX(STU_HEIGHT) AS HEIGHT
    FROM STUDENT
    WHERE STU_DEPT IS NOT NULL
    GROUP BY STU_DEPT
);

▶ 출력 결과

2. 각 학과별으로 점수가 가장 높은 학생의 학번, 학과, 이름, 키, 점수 출력

▶ 학과 별 최대 키 값

SELECT S.STU_DEPT, MAX(E.ENR_GRADE) AS GRADE
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
GROUP BY S.STU_DEPT;

▶ 코드

SELECT S.STU_NO, S.STU_DEPT, S.STU_NAME, S.STU_HEIGHT, S2.GRADE
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN (
    SELECT S.STU_DEPT, MAX(E.ENR_GRADE)AS GRADE
    FROM STUDENT S
    INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
    GROUP BY S.STU_DEPT
) S2 ON S.STU_DEPT = S2.STU_DEPT
    AND E.ENR_GRADE = S2.GRADE
;

▶ 출력 결과

3.  부하직원이 가장 많은 사람의 사번, 이름, 부하직원수

▶ 부하직원의 수

SELECT MGR, COUNT(MGR)
FROM EMP
GROUP BY MGR
ORDER BY COUNT(MGR) DESC;

• 부하직원수를 내림차순으로 정렬하여 최댓값을 최상위에 위치시킨다.

▶ 코드

SELECT *
FROM EMP E
INNER JOIN (
    SELECT MGR, COUNT(MGR)
    FROM EMP
    GROUP BY MGR
    ORDER BY COUNT(MGR) DESC
) E2 ON E.MGR = E2.MGR
WHERE ROWNUM=1;

▶ 출력 결과

테이블 - 1번 문제

<STUDENT> 테이블

1. 각 학과에서 가장 키가 큰 학생의 학번, 이름, 키 출력

SELECT S.STU_NO, S.STU_NAME, S.STU_DEPT, S.STU_HEIGHT
FROM STUDENT S
INNER JOIN(
    SELECT STU_DEPT, MAX(STU_HEIGHT) AS HEIGHT
    FROM STUDENT S
    GROUP BY STU_DEPT
) S2 ON S.STU_HEIGHT = S2.HEIGHT
    AND S.STU_DEPT = S2.STU_DEPT
;

테이블 - 2~5번 문제

<EMP> 테이블

<DEPT>테이블

2. 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력하라.

SELECT E.EMPNO AS 사원번호, E.ENAME AS 사원명, E.DEPTNO AS 부서번호, E.HIREDATE AS 입사일
FROM EMP E
INNER JOIN (
    SELECT DEPTNO, MIN(HIREDATE) AS HIREDATE
    FROM EMP
    WHERE DEPTNO IS NOT NULL
    GROUP BY DEPTNO
) E2 ON E.DEPTNO = E2.DEPTNO
    AND E.HIREDATE = E2.HIREDATE
ORDER BY E.DEPTNO;

3. 1981년 5월 31일 이후 입사자 중 커미션이 NULL이거나 0인 사원의 커미션은 500으로 그렇지 않으면 기존 커미션을 출력하라.

SELECT ENAME, HIREDATE, DECODE(COMM, NULL, 500, 0, 500, COMM) AS COMM
FROM EMP
WHERE HIREDATE > '81/05/31'
ORDER BY HIREDATE;

4. 1981년 6월 1일 ~ 1981년 12월 31일 입사자 중 부서명이 SALES인 사원의 부서번호, 사원명, 직업, 입사일을 출력하라.

SELECT E.DEPTNO AS 부서번호, E.ENAME AS 사원명, E.JOB AS 직업, E.HIREDATE AS 입사일
FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE HIREDATE BETWEEN '81/06/01' AND '81/12/31'
    AND D.DNAME = 'SALES'
ORDER BY HIREDATE;

5. 1980년~1982년 사이에 입사한 각 부서별 입사년도별 사원 수, 부서번호, 부서명 을 출력하라.

▶SUM/COUNT/DECODE 자유롭게 사용

SELECT
    D.DEPTNO AS 부서번호, D.DNAME AS 부서명,
    COUNT(DECODE(TO_CHAR(E.HIREDATE, 'YYYY'), '1980', 123)) "입사 1980",
    SUM(DECODE(TO_CHAR(E.HIREDATE, 'YYYY'), '1981', 1, 0)) "입사 1981",
    SUM(DECODE(TO_CHAR(E.HIREDATE, 'YYYY'), '1982', 1, 0)) "입사 1982"
FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME;

<오늘의 학습>

1. SELF JOIN

2. LEFT JOIN (OUTER JOIN)

3. 시간 함수

1. SELF JOIN

▶ 같은 테이블을 조인하는 방법이다.

SELECT E.ENAME AS 본인, E2.ENAME AS 팀장
FROM EMP E
INNER JOIN EMP E2 ON E.MGR = E2.EMPNO
WHERE E.ENAME = 'SMITH';

<EMP> 테이블

▷ 예제 1) 부하직원이 가장 많은 사람의 사번, 이름, 부하직원 수 출력

▶ MANAGER 기준으로 부하직원의 수를 구한 뒤, 최댓값을 찾기 위해 내림차순 정렬을 한다.

SELECT MGR, COUNT(MGR) AS CNT
FROM EMP
GROUP BY MGR
ORDER BY CNT DESC;

▶ 최댓값을 구한 테이블을 JOIN 한다

SELECT E.EMPNO AS 사번, E.ENAME AS 팀장이름, E2.CNT AS 부하직원수
FROM EMP E
INNER JOIN (
    SELECT MGR, COUNT(MGR) AS CNT
    FROM EMP
    GROUP BY MGR
    ORDER BY CNT DESC
)E2 ON E.EMPNO = E2.MGR
WHERE ROWNUM = 1;

※ WHERE ROWNUM=1 : 최상위 값을 하나 가져온다

SELECT *
FROM STUDENT
WHERE ROWNUM=1; --첫번째 값을 가져온다

▶ ROWNUM을 사용하지 않고 서브 쿼리를 이용한 코드

SELECT E.EMPNO AS 사번, E.ENAME AS 팀장이름, E2.CNT AS 부하직원수
FROM EMP E
INNER JOIN (
    SELECT MGR, COUNT(MGR) AS CNT
    FROM EMP
    GROUP BY MGR
    HAVING COUNT(MGR) = (
        SELECT MAX(CNT)
        FROM (
            SELECT MGR, COUNT(MGR) AS CNT
            FROM EMP
            GROUP BY MGR
        )
    )
) E2 ON E.EMPNO = E2.MGR;

• 부하직원의 수를 구한 테이블을 이용해 최댓값을 찾는다.
• 최댓값을 찾은 값으로 다시 부하직원의 수 중 최댓값과 일치하는 CNT를 가진 테이블을 구한다.

▷ 예제2) 부하직원의 평균 급여가 가장 높은 사람의 사번, 이름, 급여 평균 출력

▶ MANAGER 기준으로 부하직원의 평균 급여를 구한 뒤, 최댓값을 찾기 위해 내림차순 정렬을 한다.

SELECT MGR, AVG(SAL) AS AV
FROM EMP 
GROUP BY MGR
ORDER BY AV DESC;

▶ NULL이 아닌 값만을 조회하는 조건을 추가한다.

SELECT MGR, AVG(SAL) AS AV
FROM EMP 
WHERE MGR IS NOT NULL
GROUP BY MGR
ORDER BY AV DESC;

▶ 평균의 최댓값을 구한 테이블을 JOIN 한다

SELECT E.EMPNO AS 사번, E.ENAME AS 이름, E2.AV AS 평균
FROM EMP E
INNER JOIN(
    SELECT MGR, AVG(SAL) AS AV
    FROM EMP 
    GROUP BY MGR
    ORDER BY AV DESC
) E2 ON E.EMPNO = E2.MGR
WHERE ROWNUM = 1;

실습을 위한 <EMP> 테이블 수정

INSERT INTO EMP VALUES(1234,'HONG','SALESMAN',7698,SYSDATE, 3500,1000,NULL);
INSERT INTO EMP VALUES(9876,'KIM','CLERK',7902,SYSDATE,3000,500,NULL);

2. LEFT JOIN (LEFT OUTER JOIN)

• 값이 조회되지 않는 NULL 값이라도 JOIN된 결과를 출력해준다.
• 메인 테이블을 기준으로 LEFT와 RIGHT의 결과가 다르게 출력되며, RIGHT OUTER JOIN보다 LEFT OUTER JOIN이 많이 사용된다.
• OUTER는 생략이 가능하다.

▶ EMP&DEPT 테이블 조인을 통한 INNER/OUTER JOIN의 차이

▶ INNER JOIN

SELECT *
FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

▶ LEFT OUTER JOIN

SELECT *
FROM EMP E
LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

• DEPTNO 값이 없어도 EMP에 있는 값이 모두 출력되었다. (ENAME : HONG, KIM)

▷ 예제 1) STUDENT 테이블에서 한 번도 시험을 보지 않은 학생을 구하시오

SELECT S.*
FROM STUDENT S
LEFT JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE IS NULL;

• <ENROL> 테이블에서 시험 성적이 없는 학생은 한 번도 시험을 보지 않은 학생이다.
• 성적의 횟수가 NULL인 값을 받아오면 한 번도 시험을 보지 않은 학생을 알 수 있다.

▶ 출력 결과

▷ 예제 2) STUDENT 테이블에서 시험을 한 번 이하로 본 학생을 구하시오.

SELECT S.*, CNT
FROM STUDENT S
LEFT JOIN (
    SELECT STU_NO, COUNT(STU_NO) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
WHERE CNT = 1
    OR E.CNT IS NULL;

• <ENROL> 테이블에서 시험 성적이 없거나 한 개만 있는 학생을 구한다.

▶ 출력 결과

※ 다른 풀이 방법) 시험을 한 번 보거나 안 본 사람은 1으로 잡힌다.

SELECT S.STU_NO, COUNT(*)
FROM STUDENT S
LEFT OUTER JOIN ENROL E ON S.STU_NO = E.STU_NO
GROUP BY S.STU_NO
HAVING COUNT(*) < 2;

다음 학습

테이블

1. 학생 테이블에서 2013년도에 입학한 사람의 레코드 출력

▶ ****년도에 입학한 것을 확인하기 위해 학번을 받아온다.

SELECT SUBSTR(STU_NO, 1,4) AS NO
FROM STUDENT;

▶ 코드

SELECT *
FROM STUDENT S
INNER JOIN(
    SELECT STU_NO, SUBSTR(STU_NO, 1,4) AS NO
    FROM STUDENT
) N ON S.STU_NO = N.STU_NO
WHERE N.NO = '2013'
ORDER BY S.STU_NO;

• WHERE 절을 이용하여 받아온 학번이 2013일 값을 출력한다.

※ 학번(2015****), 이름, 학과 출력

SELECT RPAD(SUBSTR(STU_NO, 1,4),8,'*') AS 학번, STU_NAME AS 이름, STU_DEPT AS 학과
FROM STUDENT;

2. 학생 학번, 이름, 과목명, 점수 출력

▶ 코드

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, SUB.SUB_NAME AS 과목명, E.ENR_GRADE
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
ORDER BY S.STU_NO;

3. 기계공작법 수업을 듣는 학생의 학번, 이름, 수업 출력

▶ 코드

SELECT S.STU_NO AS 학번, STU_NAME AS 이름, SUB.SUB_NAME AS 수업
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
WHERE SUB.SUB_NAME = '기계공작법'
ORDER BY S.STU_NO;

4. 옥한빛의 평균 점수보다 낮은 점수를 받은 학생들의 이름, 과목명, 점수 출력

▶ 옥한빛의 평균 점수

SELECT E.STU_NO, AVG(E.ENR_GRADE)
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
WHERE S.STU_NAME = '옥한빛'
GROUP BY E.STU_NO;

▶ 코드

SELECT S.STU_NAME AS 이름, SUB.SUB_DEPT AS 과목명,  E.ENR_GRADE AS 점수
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
WHERE E.ENR_GRADE < (
    SELECT AVG(E.ENR_GRADE)
    FROM ENROL E
    INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
    WHERE S.STU_NAME = '옥한빛'
)
AND S.STU_NAME <> '옥한빛' -- 옥한빛은 빼고 출력하기 위함
ORDER BY S.STU_NAME;

5. 각 부서별 급여 등급이 2등급 이상인 사람의 평균 급여 구하기

▶ 급여 등급이 2등급 이상인 사람과 등급 출력

SELECT E.ENAME, S.GRADE AS GRADE
FROM SALGRADE S 
INNER JOIN EMP E ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE GRADE >= 2;

▶ 급여 등급이 2등급 이상인 사람의 목록

SELECT *
FROM EMP E
INNER JOIN (
    SELECT E.ENAME, S.GRADE AS GRADE
    FROM SALGRADE S 
    INNER JOIN EMP E ON E.SAL BETWEEN S.LOSAL AND S.HISAL
    WHERE GRADE >= 2
) S ON E.ENAME = S.ENAME;

▶ 코드

SELECT E.DEPTNO, AVG(E.SAL)
FROM EMP E
INNER JOIN (
    SELECT E.ENAME, S.GRADE AS GRADE
    FROM SALGRADE S 
    INNER JOIN EMP E ON E.SAL BETWEEN S.LOSAL AND S.HISAL
    WHERE GRADE >= 2
) S ON E.ENAME = S.ENAME
GROUP BY E.DEPTNO;

테이블

--1. 각 학생들의 학번, 이름, 학과, 수강과목 수 출력

SELECT STU_NO, COUNT(STU_NO) AS CNT
FROM ENROL
GROUP BY STU_NO;

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, S.STU_DEPT AS 학과, E.CNT AS 수강과목수
FROM STUDENT S
INNER JOIN (
    SELECT STU_NO, COUNT(STU_NO) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
ORDER BY E.CNT, S.STU_DEPT;

수강과목이 없는 학생도 출력하고 싶다면 OUTER JOIN을 이용한다.

LEFT OUTER JOIN STUDENT 테이블의 모든 학번에 대해 조인한다.

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, S.STU_DEPT AS 학과, E.CNT AS 수강과목수
FROM STUDENT S
LEFT OUTER JOIN (
    SELECT STU_NO, COUNT(STU_NO) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
ORDER BY E.CNT, S.STU_DEPT;

--1-1 : 각 학생들의 학번, 이름, 학과 출력. 단 1과목만 수강하는 학생들만 출력한다.

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, S.STU_DEPT AS 학과, E.CNT AS 수강과목수
FROM STUDENT S
INNER JOIN (
    SELECT STU_NO, COUNT(STU_NO) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
WHERE E.CNT = 1
ORDER BY S.STU_NO;

--1-2 각 학과별 수강 과목 수 구하기

SELECT S.STU_DEPT, SUM(CNT)
FROM STUDENT S
INNER JOIN (
    SELECT STU_NO, COUNT(*) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
GROUP BY S.STU_DEPT
ORDER BY S.STU_DEPT;

--2. 기계과 학생들중 최고 점수의 학생보다 높은 점수를 받은 학생들의 학번, 이름, 학과, 점수 출력

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, S.STU_DEPT AS 학과, E.ENR_GRADE AS 점수
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE > (
    SELECT MAX(ENR_GRADE)
    FROM ENROL E
    INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
    WHERE S.STU_DEPT = '기계'
);

--3. 각 부서별 급여 등급 평균 구하기

SELECT DEPTNO, ROUND(AVG(SAL),1)
FROM EMP E
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
GROUP BY DEPTNO;

--3-1 각 부서명, 평균 등급 출력

SELECT E.DEPTNO, D.DNAME, ROUND(AVG(S.GRADE),1)
FROM DEPT D
INNER JOIN EMP E ON D.DEPTNO = E.DEPTNO
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
GROUP BY E.DEPTNO, D.DNAME
ORDER BY E.DNAME, D.DNAME;

D.DEPTNO, D.DNAME를 같이 묶어줘야 PRIMARY KET라고 할 수 있다.

--4. 각 부서별 급여 등급이 3등급 이상인 사람의 수 구하기

SELECT E.DEPTNO, D.DNAME, COUNT(S.GRADE)
FROM EMP E
INNER JOIN DEPT D ON D.DEPTNO = E.DEPTNO
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE S.GRADE >= 3
GROUP BY E.DEPTNO, D.DNAME
ORDER BY E.DEPTNO, D.DNAME;

--4-1. 각 부서별 급여가 2500 이상인 사람의 수 구하기 -> 부서명을 출력하세요

SELECT E.DEPTNO, D.DNAME AS 부서명, COUNT(SAL)
FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE SAL > 2500
GROUP BY E.DEPTNO, D.DNAME
ORDER BY E.DEPTNO, D.DNAME;

--5. 평균급여 등급이 가장 높은 부서와 낮은 부서와의 등급 차이 구하기

SELECT MAX(NUM) AS MAX, MIN(NUM) AS MIN, MAX(NUM)-MIN(NUM) AS DIFFERENCE
FROM (
    SELECT DEPTNO, ROUND(AVG(GRADE), 1) AS NUM
    FROM EMP E
    INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
    GROUP BY DEPTNO
);

--6. 각 학과의 학생들의 평균 점수를 구해서 가장 큰 평균 값에서 가장 작은 평균 값 빼기

SELECT MAX(SCORE) AS MAX, MIN(SCORE) AS MIN, MAX(SCORE)-MIN(SCORE) AS DIFFERENCE
FROM (
    SELECT S.STU_DEPT, ROUND(AVG(ENR_GRADE), 1) AS SCORE
    FROM ENROL E
    INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
    GROUP BY S.STU_DEPT
);