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

테이블

PROFESSOR

 

DEPARTMENT

 

STUDENT2

 

 

 

1. 공과대학에 속한 학생의 수를 구하시오. (전공 기준)

▶ 공과대학에 속한 학부를 구한다.

SELECT D2.DEPTNO, D2.DNAME, D1.DNAME
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D1.DEPTNO = D2.PART
WHERE D1.DNAME = '공과대학';

 

▶ 학부를 이용해 공과 대학에 속한 학과를 구한다.

SELECT D3.DEPTNO, D3.DNAME
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D1.DEPTNO = D2.PART
INNER JOIN DEPARTMENT D3 ON D2.DEPTNO = D3.PART
WHERE D1.DNAME = '공과대학';

 

▶ 코드

SELECT COUNT(*) AS 공과대학_학생수
FROM DEPARTMENT D1
INNER JOIN DEPARTMENT D2 ON D1.DEPTNO = D2.PART
INNER JOIN DEPARTMENT D3 ON D2.DEPTNO = D3.PART
INNER JOIN STUDENT2 S ON S.DEPTNO1 = D3.DEPTNO
WHERE D1.DNAME = '공과대학';

 

 

▶ <STUDENT2> 테이블을 메인으로 잡은 코드 (출력은 같다)

SELECT COUNT(*) AS 공과대학_학생수
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO --학과
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO --학부
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO --공과대학
WHERE D3.DNAME ='공과대학';

 

 

 

 

2. 공과대학 학생들의 평균 키 출력

SELECT ROUND(AVG(HEIGHT),1) AS 공과대학_평균키
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
WHERE D3.DNAME ='공과대학';

 

 

 

 

3. 공과대학, 인문대학 각각의 평균 키 출력

SELECT D3.DEPTNO, D3.DNAME, ROUND(AVG(HEIGHT),1) AS 평균키
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
INNER JOIN DEPARTMENT D3 ON D2.PART = D3.DEPTNO
GROUP BY D3.DEPTNO, D3.DNAME;

 

 

 

 

4. 컴퓨터정보학부와 메카트로닉스학부의 평균 키 차이 값

▶ 컴퓨터정보학부, 메카트로닉스학부의 평균 키만 출력

SELECT D2.DNAME AS DNAME, AVG(HEIGHT) AS HEIGHT
FROM STUDENT2 S
INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
WHERE D2.DNAME IN ('컴퓨터정보학부','메카트로닉스학부')
GROUP BY D2.DNAME;

 

 

▶ 코드

SELECT MAX(S.HEIGHT)-MIN(S.HEIGHT) AS 평균키_차이값
FROM (
    SELECT D2.DNAME AS DNAME, AVG(HEIGHT) AS HEIGHT
    FROM STUDENT2 S
    INNER JOIN DEPARTMENT D1 ON S.DEPTNO1 = D1.DEPTNO
    INNER JOIN DEPARTMENT D2 ON D1.PART = D2.DEPTNO
    WHERE D2.DNAME IN ('컴퓨터정보학부','메카트로닉스학부')
    GROUP BY D2.DNAME
) S;

 

 

▶ 출력 결과