728x90
반응형
테이블
--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
);
728x90
반응형
'HRD_훈련 > 실습' 카테고리의 다른 글
| [SQL] JOIN 실습 4 (22일차) (0) | 2023.06.15 |
|---|---|
| [SQL] 시간 함수 실습 (21일차) (0) | 2023.06.14 |
| [SQL] JOIN 실습 1 (20일차) (0) | 2023.06.13 |
| [SQL] SQL 함수 실습 (19일차) (0) | 2023.06.12 |
| [SQL] 심화 문제 (19일차) (0) | 2023.06.12 |
