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

테이블

 

STUDENT

 

SUBJECT

 

ENROL

 

EMP

 

DEPT

 

SALGRADE

 

---

--1. 각 학생들의 학번, 이름, 학과, 수강과목 수 출력

SELECT STU_NO, COUNT(STU_NO) AS CNT
FROM ENROL
GROUP BY STU_NO;

 

 

 

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, S.STU_DEPT AS 학과, E.CNT AS 수강과목수
FROM STUDENT S
INNER JOIN (
    SELECT STU_NO, COUNT(STU_NO) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
ORDER BY E.CNT, S.STU_DEPT;

 

수강과목이 없는 학생도 출력하고 싶다면 OUTER JOIN을 이용한다.

LEFT OUTER JOIN STUDENT 테이블의 모든 학번에 대해 조인한다.

 

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, S.STU_DEPT AS 학과, E.CNT AS 수강과목수
FROM STUDENT S
LEFT OUTER JOIN (
    SELECT STU_NO, COUNT(STU_NO) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
ORDER BY E.CNT, S.STU_DEPT;

 

 

---

--1-1 : 각 학생들의 학번, 이름, 학과 출력. 단 1과목만 수강하는 학생들만 출력한다.

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, S.STU_DEPT AS 학과, E.CNT AS 수강과목수
FROM STUDENT S
INNER JOIN (
    SELECT STU_NO, COUNT(STU_NO) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
WHERE E.CNT = 1
ORDER BY S.STU_NO;

 

 

 

 

---

--1-2 각 학과별 수강 과목 수 구하기

SELECT S.STU_DEPT, SUM(CNT)
FROM STUDENT S
INNER JOIN (
    SELECT STU_NO, COUNT(*) AS CNT
    FROM ENROL
    GROUP BY STU_NO
) E ON S.STU_NO = E.STU_NO
GROUP BY S.STU_DEPT
ORDER BY S.STU_DEPT;

 

 

 

---

--2. 기계과 학생들중 최고 점수의 학생보다 높은 점수를 받은 학생들의 학번, 이름, 학과, 점수 출력

 

SELECT S.STU_NO AS 학번, S.STU_NAME AS 이름, S.STU_DEPT AS 학과, E.ENR_GRADE AS 점수
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 = '기계'
);

 

 

 

---

--3. 각 부서별 급여 등급 평균 구하기

SELECT DEPTNO, ROUND(AVG(SAL),1)
FROM EMP E
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
GROUP BY DEPTNO;

 

 

---

--3-1 각 부서명, 평균 등급 출력

SELECT E.DEPTNO, D.DNAME, ROUND(AVG(S.GRADE),1)
FROM DEPT D
INNER JOIN EMP E ON D.DEPTNO = E.DEPTNO
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
GROUP BY E.DEPTNO, D.DNAME
ORDER BY E.DNAME, D.DNAME;

D.DEPTNO, D.DNAME를 같이 묶어줘야 PRIMARY KET라고 할 수 있다.

 

 

 

---

--4. 각 부서별 급여 등급이 3등급 이상인 사람의 수 구하기

SELECT E.DEPTNO, D.DNAME, COUNT(S.GRADE)
FROM EMP E
INNER JOIN DEPT D ON D.DEPTNO = E.DEPTNO
INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE S.GRADE >= 3
GROUP BY E.DEPTNO, D.DNAME
ORDER BY E.DEPTNO, D.DNAME;

 

 

---

--4-1. 각 부서별 급여가 2500 이상인 사람의 수 구하기 -> 부서명을 출력하세요

SELECT E.DEPTNO, D.DNAME AS 부서명, COUNT(SAL)
FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE SAL > 2500
GROUP BY E.DEPTNO, D.DNAME
ORDER BY E.DEPTNO, D.DNAME;

 

 

 

---

--5. 평균급여 등급이 가장 높은 부서와 낮은 부서와의 등급 차이 구하기

SELECT MAX(NUM) AS MAX, MIN(NUM) AS MIN, MAX(NUM)-MIN(NUM) AS DIFFERENCE
FROM (
    SELECT DEPTNO, ROUND(AVG(GRADE), 1) AS NUM
    FROM EMP E
    INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
    GROUP BY DEPTNO
);

 

 

 

---

--6. 각 학과의 학생들의 평균 점수를 구해서 가장 큰 평균 값에서 가장 작은 평균 값 빼기

SELECT MAX(SCORE) AS MAX, MIN(SCORE) AS MIN, MAX(SCORE)-MIN(SCORE) AS DIFFERENCE
FROM (
    SELECT S.STU_DEPT, ROUND(AVG(ENR_GRADE), 1) AS SCORE
    FROM ENROL E
    INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
    GROUP BY S.STU_DEPT
);