[SQL] JOIN 실습 4 (22일차)

테이블

STUDENT

 

SUBJECT

 

ENROL

 

EMP

 

DEPT

 

SALGRADE

 

 

 

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;

 

 

▶ 출력 결과