README

1. PROFESSOR 테이블에서 교수들의 이메일을 아래와 같이 출력 하시오.

• 조건 1. 전체 길이는 해당 컬럼 값의 길이
• 조건 2. 마지막 4글자를 제외한 나머지 부분을 문자 '*'로 채워서 출력
• 조건 3. 출력 컬럼은 교수 이름, 아래와 같이 편집된 EMAIL 컬럼

SELECT NAME
    , LPAD(SUBSTR(EMAIL,LENGTH(EMAIL)-3,4),LENGTH(EMAIL),'*') AS EMAIL
FROM PROFESSOR;

2. 학부별 키(HEIGHT)가 가장 작은 학생들의 목록을 출력하시오.

• 출력 컬럼 : 학부명, 학번, 학생 이름, 키
• 참고 테이블 : STUDENT2, DEPARTMENT

▶ 학부를 받아온다.

SELECT *
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO;

▶ 출력 결과

▶ 학부별로 가장 작은 키를 구한다.

SELECT D.PART AS PART, MIN(S.HEIGHT) AS MHEIGHT
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
GROUP BY D.PART;

▶ 출력 결과

▶ 같은 키와 학부를 매칭한다.

SELECT D2.DNAME AS 학부명, S.STUDNO AS 학번, S.NAME AS 학생이름, S.HEIGHT AS 키
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
INNER JOIN(
    SELECT D.PART AS PART, MIN(S.HEIGHT) AS MHEIGHT
    FROM STUDENT2 S
    INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
    GROUP BY D.PART
)D3 ON D3.PART = D.PART
    AND S.HEIGHT = D3.MHEIGHT
;

▶ 출력 결과

3. 직급이 정교수인 레코드의 급여 총 합과 전임강사 급여 총 합의 차이를 구하시오.

• 참고 테이블 : PROFESSOR

▶ 직급별

SELECT POSITION, SUM(PAY) AS PAY
FROM PROFESSOR
WHERE POSITION IN ('정교수','전임강사')
GROUP BY POSITION;

▶ 코드

SELECT MAX(P.PAY)-MIN(P.PAY) AS 급여합차이
FROM (
    SELECT POSITION, SUM(PAY) AS PAY
    FROM PROFESSOR
    WHERE POSITION IN ('정교수','전임강사')
    GROUP BY POSITION
) P;

▶ 출력 결과

4. 공과대학에 속한 94, 95 학번 학생들의 남녀 수를 구하는 쿼리를 작성하시오.

• 참고테이블 : STUDENT2, DEPARTMENT

▶ 코드

SELECT 
     COUNT(DECODE(SUBSTR(JUMIN,7,1),1,'남자',3,'남자')) AS 남자수
     , COUNT(DECODE(SUBSTR(JUMIN,7,1),2,'여자',4,'여자')) AS 여자수
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
    AND D3.DEPTNO = 10
WHERE SUBSTR(STUDNO,1,2) IN (94,95);

▶ 출력 결과

5. 공과대학에서 속한 교수들 중 가장 큰 급여를 가지는 사람과 가장 적은 급여를 가지는 사람의 교수 이름, 소속 학과, 직급, 급여를 출력하시오

• 참고 테이블 : PROFESSOR, DEPARTMENT

▶ 가장 큰 급여와 가장 적은 급여를 구한다.

SELECT MAX(PAY), MIN(PAY)
FROM PROFESSOR P
INNER JOIN DEPARTMENT D ON P.DEPTNO = D.DEPTNO
INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
    AND D3.DEPTNO = 10;

▶ 코드

SELECT P.NAME AS 교수이름, D.DNAME AS 소속학과, P.POSITION AS 직급, P.PAY AS 급여
FROM PROFESSOR P
INNER JOIN DEPARTMENT D ON P.DEPTNO = D.DEPTNO
INNER JOIN(
    SELECT MAX(PAY) AS MAXP, MIN(PAY) AS MINP
    FROM PROFESSOR P
    INNER JOIN DEPARTMENT D ON P.DEPTNO = D.DEPTNO
    INNER JOIN DEPARTMENT D2 ON D.PART = D2.DEPTNO
    INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
        AND D3.DEPTNO = 10
) P2 ON P.PAY = P2.MAXP
    OR P.PAY = P2.MINP
;

▶ 출력 결과

<오늘의 학습>

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;

다음 학습