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

테이블

DEPARTMENT

 

STUDENT2

 

 

 

1. <DEPARTMENT> 공과대학 학부와 인문대학 학부의 학과 목록을 출력하시오.

SELECT D1.PART, D2.DNAME, D1.DNAME
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D1.PART
WHERE D2.LV = 1;

 

 

 

 

2. <DEPARTMENT> 컴퓨터정보학부의 학과 목록을 출력하시오.

SELECT D1.PART, D2.DNAME, D1.DNAME
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D2.DEPTNO = D1.PART
    AND D2.DNAME IN('컴퓨터정보학부');

 

 

 

 

3. <STUDENT2> 학생들의 주민번호를 800000-1****** 형태로 구하고 성별을 구분하시오.

SELECT NAME
    , (SUBSTR(JUMIN,1,6) || '-' || RPAD(SUBSTR(JUMIN,7,1), 7, '*')) AS JUMIN
    , (DECODE(SUBSTR(JUMIN,7,1),1,'남자',3,'남자',2,'여자',4,'여자','?')) AS GENDER
FROM STUDENT2;

 

 

 

 

4. 이름 가운데를 '*'으로 출력하시오.

SELECT SUBSTR(NAME,1,1) || '*' || SUBSTR(NAME,3,1) AS NAME
FROM STUDENT2;

 

 

 

 

5. 학부를 포함한 학생 목록을 출력하시오.

▶ 학생의 학과를 구한다.

SELECT S.*, D1.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO;

 

▶ 학과를 이용하여 학부를 구한다.

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

 

▶ 출력 결과

 

 

 

 

6. 평균 키가 가장 큰 학과의 학과명, 평균 키를 구하시오.

▶ 학과별 평균 키

SELECT D.DNAME, ROUND(AVG(S.HEIGHT),1) AS HEIGHT
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
GROUP BY D.DNAME
ORDER BY HEIGHT DESC;

• 여기서 바로 최댓값을 출력하고 싶었으나 ORDER BY 정렬 전에 WHERE절이 먼저 실행이 되어 최댓값을 출력할 수 없었다.
• 정렬된 테이블을 이용해 최댓값을 출력했다.

 

▶ 정렬된 테이블을 이용해 가장 큰 평균 값을 가진 학과를 출력

SELECT S.DNAME AS 학과, S.HEIGHT AS 평균키
FROM (
    SELECT D.DNAME, ROUND(AVG(S.HEIGHT),1) AS HEIGHT
    FROM STUDENT2 S
    INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
    GROUP BY D.DNAME
    ORDER BY HEIGHT DESC
) S
WHERE ROWNUM = 1;

 

▶ 출력 결과

 

 

 

7. 각 학과별 키가 가장 큰 학생의 이름, 학과명, 키를 구하시오.

▶ 학과별 가장 큰 키를 출력

SELECT D.DNAME AS DNAME, MAX(HEIGHT) AS HEIGHT
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
GROUP BY D.DNAME;

 

▶ 학과가 일치하면서 가장 큰 키 값을 가진 학생을 출력

SELECT S.NAME AS 이름, S2.DNAME AS 학과, S.HEIGHT AS 키
FROM STUDENT2 S
INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
INNER JOIN(
    SELECT D.DNAME AS DNAME, MAX(HEIGHT) AS HEIGHT
    FROM STUDENT2 S
    INNER JOIN DEPARTMENT D ON S.DEPTNO1 = D.DEPTNO
    GROUP BY D.DNAME
) S2 ON S2.DNAME = D.DNAME
    AND S2.HEIGHT = S.HEIGHT
;

 

▶ 출력 결과

 

 

 

 

8. 부전공(DEPTNO2)를 가진 학생의 목록과 전공 학부를 출력하시오.

▶ 부전공을 가진 학생 출력

SELECT S.NAME, S.DEPTNO1, D1.DNAME, S.DEPTNO2, D2.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON S.DEPTNO2 = D2.DEPTNO;

 

▶ 코드

SELECT S.*, D3.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON S.DEPTNO2 = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D1.PART = D3.DEPTNO;

 

▶ 출력 결과

 

 

 

 

9. 부전공의 학부를 구하시오.

SELECT S.*, D2.DNAME
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO2 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO;

 

 

 

 

10. 학번, 이름, 부전공의 학과, 학부 목록을 출력.  (부전공이 없을 경우는 NULL로 출력)

SELECT S.STUDNO, S.NAME, NVL(D1.DNAME, '없음') AS 부전공, NVL(D2.DNAME, '없다') AS 부전공학부
FROM STUDENT2 S
LEFT JOIN DEPARTMENT D1 ON S.DEPTNO2 = D1.DEPTNO
LEFT JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO;