[SQL] JOIN 실습 3 (21일차)

테이블

STUDENT

 

SUBJECT

 

ENROL

 

EMP

 

DEPT

 

SALGRADE

 

 

1. 학생 테이블에서 2013년도에 입학한 사람의 레코드 출력

▶ ****년도에 입학한 것을 확인하기 위해 학번을 받아온다.

SELECT SUBSTR(STU_NO, 1,4) AS NO
FROM STUDENT;

 

---

▶ 코드

SELECT *
FROM STUDENT S
INNER JOIN(
    SELECT STU_NO, SUBSTR(STU_NO, 1,4) AS NO
    FROM STUDENT
) N ON S.STU_NO = N.STU_NO
WHERE N.NO = '2013'
ORDER BY S.STU_NO;

• WHERE 절을 이용하여 받아온 학번이 2013일 값을 출력한다.

 

 

 

---

※ 학번(2015****), 이름, 학과 출력

SELECT RPAD(SUBSTR(STU_NO, 1,4),8,'*') AS 학번, STU_NAME AS 이름, STU_DEPT AS 학과
FROM STUDENT;

 

 

 

 

2. 학생 학번, 이름, 과목명, 점수 출력

▶ 코드

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, SUB.SUB_NAME AS 과목명, E.ENR_GRADE
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
ORDER BY S.STU_NO;

 

 

 

 

3. 기계공작법 수업을 듣는 학생의 학번, 이름, 수업 출력

▶ 코드

SELECT S.STU_NO AS 학번, STU_NAME AS 이름, SUB.SUB_NAME AS 수업
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
WHERE SUB.SUB_NAME = '기계공작법'
ORDER BY S.STU_NO;

 

 

 

 

4. 옥한빛의 평균 점수보다 낮은 점수를 받은 학생들의 이름, 과목명, 점수 출력

▶ 옥한빛의 평균 점수

SELECT E.STU_NO, AVG(E.ENR_GRADE)
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
WHERE S.STU_NAME = '옥한빛'
GROUP BY E.STU_NO;

 

 

---

▶ 코드

SELECT S.STU_NAME AS 이름, SUB.SUB_DEPT AS 과목명,  E.ENR_GRADE AS 점수
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
WHERE E.ENR_GRADE < (
    SELECT AVG(E.ENR_GRADE)
    FROM ENROL E
    INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
    WHERE S.STU_NAME = '옥한빛'
)
AND S.STU_NAME <> '옥한빛' -- 옥한빛은 빼고 출력하기 위함
ORDER BY S.STU_NAME;

 

 

 

 

5. 각 부서별 급여 등급이 2등급 이상인 사람의 평균 급여 구하기

▶ 급여 등급이 2등급 이상인 사람과 등급 출력

SELECT E.ENAME, S.GRADE AS GRADE
FROM SALGRADE S 
INNER JOIN EMP E ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE GRADE >= 2;

 

 

---

▶ 급여 등급이 2등급 이상인 사람의 목록

SELECT *
FROM EMP E
INNER JOIN (
    SELECT E.ENAME, S.GRADE AS GRADE
    FROM SALGRADE S 
    INNER JOIN EMP E ON E.SAL BETWEEN S.LOSAL AND S.HISAL
    WHERE GRADE >= 2
) S ON E.ENAME = S.ENAME;

 

 

---

▶ 코드

SELECT E.DEPTNO, AVG(E.SAL)
FROM EMP E
INNER JOIN (
    SELECT E.ENAME, S.GRADE AS GRADE
    FROM SALGRADE S 
    INNER JOIN EMP E ON E.SAL BETWEEN S.LOSAL AND S.HISAL
    WHERE GRADE >= 2
) S ON E.ENAME = S.ENAME
GROUP BY E.DEPTNO;