[SQL] JOIN 실습 1 (20일차)

테이블

STUDENT

 

SUBJECT

 

ENROL

 

EMP

 

DEPT

 

SALGRADE

 

 

 

1. 김인중 학생이 듣는 수업의 과목명과 교수 이름을 출력

▶ 김인중 학생이 듣는 수업을 찾는다.

SELECT STU_DEPT, STU_GRADE
FROM STUDENT
WHERE STU_NAME = '김인중';

• 김인중은 과목 점수가 1인 컴퓨터 정보과 수업을 듣는다.

 

▶ 코드

SELECT SUB.SUB_DEPT AS 과목명, SUB.SUB_PROF AS 교수
FROM SUBJECT SUB
INNER JOIN (
    SELECT STU_DEPT, STU_GRADE
    FROM STUDENT
    WHERE STU_NAME = '김인중'
)STU ON SUB.SUB_DEPT = STU.STU_DEPT
    AND SUB.SUB_GRADE = STU.STU_GRADE
;

 

 

 

 

2. 컴퓨터개론 수업을 듣는 학생의 학번, 이름 출력

▶ 컴퓨터 개론의 SUB_NO을 받아온다. SUB_NO의 값이 기본키이기 때문이다. 

SELECT SUB_NO
FROM SUBJECT
WHERE SUB_NAME = '컴퓨터개론';

• SUB_NO이 101인 학생들은 컴퓨터 개론 수업을 듣는다.

 

▶ 코드

SELECT STU.STU_NO AS 학번, STU.STU_NAME AS 이름
FROM ENROL E
INNER JOIN (
    SELECT SUB_NO
    FROM SUBJECT
    WHERE SUB_NAME = '컴퓨터개론'
) SUB ON E.SUB_NO = SUB.SUB_NO
INNER JOIN STUDENT STU ON E.STU_NO = STU.STU_NO;

 

 

3. 기계공작법, 기초전자실험 수업을 듣는 학생의 학번, 이름, 수업명 출력

▶ 기계공작법, 기초전자실험 수업을 듣는 넘버를 받아온다.

SELECT SUB_NO
FROM SUBJECT
WHERE SUB_NAME IN('기계공작법','기초전자실험');

• 기계공작법, 기초전자실험 수업을 듣는 학생의 NO은 102,103이다.

 

▶ 코드

SELECT STU.STU_NO AS 학번, STU.STU_NAME AS 이름, SUB.SUB_NAME AS 수업명
FROM ENROL E
INNER JOIN (
    SELECT SUB_NO, SUB_NAME
    FROM SUBJECT
    WHERE SUB_NAME IN('기계공작법','기초전자실험')
) SUB ON E.SUB_NO = SUB.SUB_NO
INNER JOIN STUDENT STU ON E.STU_NO = STU.STU_NO;

 

 

 

 

4. 여자이면서 구봉규 교수의 수업을 듣는 학생의 학번, 이름, 학과, 수업명, 과목 점수 출력

▶구봉규 교수가 수업하는 과목의 넘버를 받아온다.

SELECT SUB_NO
FROM SUBJECT
WHERE SUB_PROF ='구봉규';

• 구봉규 교수가 수업하는 과목의 넘버는 107이다.

 

 

▶ 과목의 넘버를 이용해 구봉규 교수의 수업을 듣는 학생을 구한다.

SELECT STU.STU_NO, STU.STU_NAME, STU.STU_DEPT, SUB.SUB_NAME, STU.STU_GRADE
FROM ENROL E
INNER JOIN(
    SELECT SUB_NO, SUB_NAME
    FROM SUBJECT
    WHERE SUB_PROF ='구봉규'
) SUB ON E.SUB_NO = SUB.SUB_NO 
INNER JOIN STUDENT STU ON E.STU_NO =STU.STU_NO
    AND STU.STU_GENDER = 'F'
;

 

 

---

▶ 이너 조인 두 번 쓰는 방법

SELECT S.STU_NO, STU_NAME, STU_DEPT, SUB_NAME
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SUB ON SUB.SUB_NO = E.SUB_NO
WHERE S.STU_GENDER = 'F' AND SUB.SUB_PROF = '구봉규';

 

 

 

 

 

5. 김인중의 평균 점수보다 낮은 점수를 받은 학생들의 이름, 과목명, 점수 출력

▶ 김인중의 학번을 구한다.

SELECT STU_NO
FROM STUDENT
WHERE STU_NAME='김인중';

 

▶ 학번 20151062의 평균 점수

SELECT AVG(ENR_GRADE)
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE STU_NAME = '김인중';

 

▶ 김인중의 평균 점수보다 낮은 점수를 받은 학생들을 구하는 코드

SELECT *
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE ENR_GRADE < (
    SELECT AVG(ENR_GRADE)
    FROM STUDENT S
    INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
    WHERE STU_NAME = '김인중'
);

 

 

 

 

6. 기계과의 최고 점수보다 높은 점수를 받은 학생 출력

1) 기계과에서 가장 높은 점수 출력

SELECT MAX(E.ENR_GRADE)
FROM SUBJECT SUB
INNER JOIN ENROL E ON SUB.SUB_NO = E.SUB_NO
WHERE SUB_DEPT = '기계';

 

 

2) 학생들과 점수 목록

SELECT *
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO;

 

 

3) 기계과의 최고점수

SELECT MAX(ENR_GRADE)
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
WHERE S.STU_DEPT = '기계';

 

4) 기계과의 최고 점수보다 높은 점수를 받은 학생 출력

SELECT S.STU_NO, S.STU_NAME, E.ENR_GRADE
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE > (
    SELECT MAX(ENR_GRADE)
    FROM ENROL E
    INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
    WHERE S.STU_DEPT = '기계'
);

 

 

 

 

 

7. 2개 이상의 과목을 들은 학생들의 목록 출력

▶ 2개 이상의 과목을 듣는 학생의 학번

SELECT STU_NO, COUNT(STU_NO) AS CNT
FROM ENROL
GROUP BY STU_NO
HAVING COUNT(*) > 1;

 

▶ 해당 학번의 학생 목록 출력

SELECT *
FROM STUDENT S
INNER JOIN (
    SELECT STU_NO
    FROM ENROL
    GROUP BY STU_NO
    HAVING COUNT(*) > 1
) E ON S.STU_NO = E.STU_NO;

 

 

▶ INNER JOIN이 아닌 WHERE 절으로 표현이 가능하다.

SELECT *
FROM STUDENT S
WHERE STU_NO IN(
    SELECT STU_NO
    FROM ENROL
    GROUP BY STU_NO
    HAVING COUNT(*) > 1
);

 

 

▶ 코드-- HAVING 절을 사용하지 않고 CNT 값을 구할 수 있다.

SELECT *
FROM (
    SELECT STU_NO, COUNT(*) AS CNT
    FROM ENROL
    GROUP BY STU_NO
)
WHERE CNT > 1;

 

 

 

 

 

8. 전기전자과의 평균 점수보다 높은 점수를 받은 학생들의 목록 출력

▶ 전기전자과의 평균 점수

SELECT AVG(ENR_GRADE)
FROM ENROL E
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
WHERE SUB.SUB_DEPT = '전기전자';

 

▶ 전기전자과의 평균 점수보다 높은 점수를 받은 학생 출력

SELECT *
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE > (
    SELECT AVG(ENR_GRADE)
    FROM ENROL E
    INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
    WHERE SUB.SUB_DEPT = '전기전자'
);