[오라클DB] 연습문제3

test0322_P3.sql

0.00MB

SELECT * FROM BOOK;
SELECT * FROM CUSTOMER;
SELECT * FROM ORDERS;

--1. 책 평균 구매 금액이 10000 이하인 사람의 핸드폰 번호를 1로 변경
UPDATE CUSTOMER
SET PHONE = '1'
WHERE CUSTID IN (
                    SELECT CUSTID
                    FROM (
                            SELECT CUSTID, AVG(SALEPRICE)AS P
                            FROM ORDERS
                            GROUP BY CUSTID
                            )
                    WHERE P <= 10000
                    );
                    
--2. 책 평균 구매 금액이 가장 낮은 사람의 핸드폰 번호를 2로 변경
UPDATE CUSTOMER
SET PHONE = '2'
WHERE CUSTID IN (
                SELECT CUSTID
                FROM (
                        SELECT CUSTID, AVG(SALEPRICE)AS P
                        FROM ORDERS
                        GROUP BY CUSTID
                        ORDER BY P
                        )
                WHERE ROWNUM = 1
                );

SELECT * FROM STUDENT;
SELECT * FROM SUBJECT;
SELECT * FROM ENROL;

--3. 학생들의 평균 성적이 가장 높은 학생과 가장 낮은 학생의 차이 구하기
SELECT MAX(A) - MIN(A)
FROM (
        SELECT STU_NO,AVG(ENR_GRADE) AS A
        FROM ENROL
        GROUP BY STU_NO
        );

 
--4. 2개의 수업을 들은 학생들의 평균점수와 1개의 수업을 들은 학생들의 평균점수의 차이 구하기 (못구함)
SELECT MAX(A)-MIN(A)
FROM (
        SELECT CNT, AVG(SCORE) AS A
        FROM (
                SELECT STU_NO,AVG(ENR_GRADE) AS SCORE, COUNT(*) AS CNT
                FROM ENROL
                GROUP BY STU_NO)
        GROUP BY CNT
        );

--5. 컴퓨터 학과보다 평균이 낮은 학과와 점수 출력
SELECT *
FROM (
        SELECT SUB_DEPT, AVG(ENR_GRADE) AS A
        FROM ENROL E
        INNER JOIN SUBJECT B ON E.SUB_NO = B.SUB_NO
        GROUP BY SUB_DEPT
        )
WHERE A < (
            SELECT AVG(A)
            FROM (
                   SELECT SUB_DEPT, AVG(ENR_GRADE) AS A
                    FROM ENROL E
                    INNER JOIN SUBJECT B ON E.SUB_NO = B.SUB_NO
                    GROUP BY SUB_DEPT
                    )
            WHERE SUB_DEPT = '컴퓨터정보'
            );
            
--6. 컴퓨터개론 수업을 듣는 학생들의 평균 점수보다 높은 점수를 가지고 있는 컴퓨터정보과 학생 출력
SELECT *
FROM (
        SELECT *
        FROM ENROL E
        INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
        WHERE ENR_GRADE > (
                            SELECT AVG(ENR_GRADE)
                            FROM ENROL E
                            INNER JOIN SUBJECT B ON B.SUB_NO = E.SUB_NO
                            WHERE SUB_NAME = '컴퓨터개론'
                            )
        )
WHERE STU_DEPT = '컴퓨터정보';


--컴퓨터개론 수업을 듣는 학생들의 평균 점수
SELECT AVG(ENR_GRADE)
FROM ENROL E
INNER JOIN SUBJECT B ON B.SUB_NO = E.SUB_NO
WHERE SUB_NAME = '컴퓨터개론';