[오라클DB] 연습문제

DB 2일차

수업 13일차

SELECT * FROM student;
SELECT * FROM subject;
SELECT * FROM enrol;

--2023/03/22
-- 1. 학생번호, 이름, 과목명, 점수 출력
SELECT S.STU_NO, S.STU_NAME, SUB.SUB_NAME, E.ENR_GRADE
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;

-- 2. 점수가 70점 이상인 학생들의 이름 출력
SELECT s.stu_name
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE >= 70;

-- 3. 김인중 학생이 수강하는 과목과 해당 교수 출력
SELECT S2.SUB_PROF
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT S2 ON S2.SUB_NO = E.SUB_NO
WHERE S.STU_NAME = '김인중';

-- 4. 컴퓨터개론 수업을 듣는 학생의 학번,  이름 출력
SELECT S.STU_NO, S.STU_NAME
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT S2 ON S2.SUB_NO = E.SUB_NO
WHERE S2.SUB_NAME = '컴퓨터개론';

-- 5. 기계공작법, 기초전자실험 수업을 듣는 학생의 학번, 이름, 과목명 출력
SELECT S.STU_NO, S.STU_NAME, S2.SUB_NAME
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT S2 ON S2.SUB_NO = E.SUB_NO
WHERE S2.SUB_NAME IN ('기계공작법', '기초전자실험');

-- 6. 여자이면서 구봉규 교수의 수업을 듣는 학생 출력
SELECT *
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT S2 ON S2.SUB_NO = E.SUB_NO
WHERE S.STU_GENDER = 'F' AND S2.SUB_PROF = '구봉규';

-- 7. 김인중의 과목 평균 점수보다 낮은 학생들의 이름, 점수 출력, 단 점수가 높은 순서대로.
SELECT S.STU_NAME, E.ENR_GRADE
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE < (
    SELECT AVG(E.ENR_GRADE) AS AVG_KIM
    FROM ENROL E
    INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
    WHERE S.STU_NAME = '김인중'
)
ORDER BY E.ENR_GRADE DESC;

-- 8. 두 과목 이상을 수강한 학생(enrol에 2개이상 데이터 있는)들의 학번, 이름, 과목명, 점수 출력
SELECT S.STU_NO, S.STU_NAME, S2.SUB_NAME, E. ENR_GRADE
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT S2 ON S2.SUB_NO = E.SUB_NO
WHERE E.STU_NO IN (
    SELECT STU_NO
    FROM ENROL
    GROUP BY STU_NO
    HAVING COUNT(*) >=2
);

--8번 문제 (HAVING 안쓰고)
SELECT S.STU_NO, STU_NAME, STU_DEPT, ENR_GRADE
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_NO IN (
    SELECT STU_NO
    FROM (
        SELECT COUNT(*) AS CNT, STU_NO 
        FROM ENROL  
        GROUP BY STU_NO
    ) T
    WHERE T.CNT > 1
)
ORDER BY STU_NAME;



-- 9. 각 학과의 평균점수 중 가장 높은 점수보다 높은 점수를 받은 학생의 정보 출력
SELECT *
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE > (
    SELECT MAX(AVG_DEPT)
    FROM (
        SELECT STU_DEPT, AVG(ENR_GRADE) AS AVG_DEPT
        FROM ENROL E
        INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
        GROUP BY STU_DEPT
    )
);

--9번 문제 (ALL 사용)
SELECT * 
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE > ALL(
    SELECT AVG(ENR_GRADE)
    FROM STUDENT S
    INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
    GROUP BY STU_DEPT
);