[SQL] JOIN 심화 문제 (22일차)

테이블

PROFESSOR

 

DEPARTMENT

 

STUDENT2

 

 

 

1. 각 학년별 학생들의 평균 키를 구하시오.

SELECT GRADE AS 학년, AVG(HEIGHT) AS 평균키
FROM STUDENT2
GROUP BY GRADE;

 

 

 

 

2. 96학번과 97학번 중 가장 키가 큰 학생의 학번, 이름, 아이디, 키를 출력하시오.

▶ 학번 별 가장 키가 큰 값을 구한다.

SELECT SUBSTR(STUDNO,1,2) AS STNO, MAX(HEIGHT) AS HEIGHT
FROM STUDENT2
WHERE SUBSTR(STUDNO,1,2) IN (96, 97)
GROUP BY SUBSTR(STUDNO,1,2);

 

▶ 코드

SELECT S.STUDNO AS 학번, S.NAME AS 이름, S.ID AS 아이디, S.HEIGHT AS 키
FROM STUDENT2 S
INNER JOIN (
    SELECT SUBSTR(STUDNO,1,2) AS STNO, MAX(HEIGHT) AS HEIGHT
    FROM STUDENT2
    WHERE SUBSTR(STUDNO,1,2) IN (96, 97)
    GROUP BY SUBSTR(STUDNO,1,2)
) S2 ON SUBSTR(STUDNO,1,2) = STNO AND S.HEIGHT = S2.HEIGHT
WHERE ROWNUM = 1;

 

 

 

 

3. 교수의 평균임금이 가장 높은 학과의 학과명, 평균 임금을 출력하시오.

▶ 학과별 교수님의 평균 임금을 구한다.

SELECT DEPTNO, ROUND(AVG(PAY), 1) AS PAY
FROM PROFESSOR
GROUP BY DEPTNO
ORDER BY PAY DESC;

 

▶ 코드

SELECT DNAME AS 학과명, P.PAY AS 평균임금
FROM DEPARTMENT D
INNER JOIN (
    SELECT DEPTNO, ROUND(AVG(PAY), 1) AS PAY
    FROM PROFESSOR
    GROUP BY DEPTNO
    ORDER BY PAY DESC
) P ON D.DEPTNO = P.DEPTNO
WHERE ROWNUM=1;

 

 

 

 

4. 95년~05년 사이에 부임한 교수의 수를 구하시오.

SELECT COUNT(*) AS 부임교수의_수
FROM PROFESSOR
WHERE HIREDATE BETWEEN '95/01/01' AND '05/12/31';

 

 

 

 

5. '인문대학'에 소속된 교수들의 목록을 구하시오.

▶ 인문대학의 DEPTNO 값을 구한다. 인문대학에 소속된 학과 : 301 문헌정보학과

SELECT D1.DEPTNO
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
WHERE D3.DNAME = '인문대학';

 

▶ 코드

SELECT *
FROM PROFESSOR P
INNER JOIN (
    SELECT D1.DEPTNO AS DEPINO
    FROM DEPARTMENT D1
    INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
    INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
    WHERE D3.DNAME = '인문대학'
) D ON P.DEPTNO = D.DEPINO;

 

▶ 출력 결과

 

 

 

 

6. 각 학년별 키가 가장 큰 학생의 학번, 이름, 학년, 키를 구하시오.

▶ 학년별 키가 가장 큰 학생의 키를 구한다.

SELECT GRADE, MAX(HEIGHT)
FROM STUDENT2
GROUP BY GRADE;

 

▶ 코드

SELECT S1.STUDNO AS 학번, S1.NAME AS 이름, S1.GRADE AS 학년, S1.HEIGHT AS 키
FROM STUDENT2 S1
INNER JOIN (
    SELECT GRADE, MAX(HEIGHT) HEIGHT
    FROM STUDENT2
    GROUP BY GRADE
) S2 ON S1.GRADE = S2.GRADE
    AND S1.HEIGHT = S2.HEIGHT
;

 

▶ 출력 결과

 

 

 

 

7. 각 학과별 몸무게가 가장 적은 학생의 학번, 이름, 학년, 키를 구하시오.

▶ 각 학과별 가장 적은 몸무게

SELECT DEPTNO1, MIN(WEIGHT)
FROM STUDENT2
GROUP BY DEPTNO1;

 

▶ 코드

SELECT S.STUDNO, S.NAME, S.GRADE, S2.WEIGHT
FROM STUDENT2 S
INNER JOIN(
    SELECT DEPTNO1, MIN(WEIGHT) AS WEIGHT
    FROM STUDENT2
    GROUP BY DEPTNO1
) S2 ON S.DEPTNO1 = S2.DEPTNO1
    AND S.WEIGHT = S2.WEIGHT
;

 

▶ 출력 결과

 

 

 

 

8. 78년생 학생들의 성별(남,여) 인원수를 구하시오.

▶ 78년생인 학생들의 성별 구하기

SELECT SUBSTR(JUMIN,1,2) AS JUMIN
    , DECODE(SUBSTR(JUMIN,7,1), 1, '남자', 3, '남자', 2, '여자', 4, '여자') AS GENDER
FROM STUDENT2
WHERE JUMIN LIKE '78%';

 

 

SELECT S2.JUMIN, S2.GENDER, COUNT(GENDER) AS 인원수
FROM STUDENT2 S1
INNER JOIN (
    SELECT STUDNO, SUBSTR(JUMIN,1,2) AS JUMIN
        , DECODE(SUBSTR(JUMIN,7,1), 1, '남자', 3, '남자', 2, '여자', 4, '여자') AS GENDER
    FROM STUDENT2
    WHERE JUMIN LIKE '78%'
) S2 ON S1.STUDNO = S2.STUDNO
GROUP BY S2.JUMIN, S2.GENDER;

 

▶ 출력 결과

 

 

---

▶ 다른 방법으로 출력이 가능하다.

SELECT 
    COUNT(DECODE(SUBSTR(JUMIN, 7, 1), 1, 1)) AS 남
    , COUNT(DECODE(SUBSTR(JUMIN, 7, 1), 2, 1)) AS 여
FROM STUDENT2
WHERE SUBSTR(JUMIN, 1, 2) = '78';

•  COUNT(or SUM), DECODE, SUBSTR을 이용하여 주민번호 7 번째 자리의 값을 받아와 성별을 구분하여 출력하였다.

 

 

 

 

9. 96학번의 가장 큰 키 값과 97학번 중 가장 작은 키 값의 차이 구하시오.

▶ 96학번의 가장 큰 키 값 구하기

SELECT SUBSTR(STUDNO, 1, 2) AS STNO, MAX(HEIGHT) AS HEIGHT
FROM STUDENT2
WHERE SUBSTR(STUDNO, 1, 2) IN (96)
GROUP BY SUBSTR(STUDNO, 1, 2);

 

▶ 97학번의 가장 작은 키 값 구하기

SELECT SUBSTR(STUDNO, 1, 2) AS STNO, MIN(HEIGHT) AS HEIGHT
FROM STUDENT2
WHERE SUBSTR(STUDNO, 1, 2) IN (97)
GROUP BY SUBSTR(STUDNO, 1, 2);

 

---

▶ 코드

SELECT  MAX(S2.HEIGHT)- MIN(S3.HEIGHT) AS 차이
FROM STUDENT2 S
LEFT JOIN(
    SELECT SUBSTR(STUDNO, 1, 2) AS STNO, MAX(HEIGHT) AS HEIGHT
    FROM STUDENT2
    WHERE SUBSTR(STUDNO, 1, 2) IN (96)
    GROUP BY SUBSTR(STUDNO, 1, 2)
) S2 ON SUBSTR(S.STUDNO, 1, 2) = S2.STNO 
    AND S.HEIGHT = S2.HEIGHT
LEFT JOIN(
    SELECT SUBSTR(STUDNO, 1, 2) AS STNO, MIN(HEIGHT) AS HEIGHT
    FROM STUDENT2
    WHERE SUBSTR(STUDNO, 1, 2) IN (97)
    GROUP BY SUBSTR(STUDNO, 1, 2)
)S3 ON SUBSTR(S.STUDNO, 1, 2) = S3.STNO
    AND S.HEIGHT = S3.HEIGHT;

• 최댓값과 최솟값을 구한 테이블 두 개를 조인했다.
• 최댓값과 최솟값을 빼 결과를 얻었다.

 

▶ 출력 결과

 

 

---

▶ 좋지 않은 코드의 예제. 출력은 같다.

SELECT
    (   SELECT MAX(HEIGHT)
        FROM STUDENT2
        WHERE STUDNO LIKE '96%' ) -
    (   SELECT MIN(HEIGHT)
        FROM STUDENT2
        WHERE STUDNO LIKE '97%' )
FROM DUAL;