[Oracle] 연습문제 풀이

 

Day1

 

1) 급여를 10%인상했을 때 연간 지급되는 급여를 검색하라(별명을 사용)

 

 

SELECT ENO AS 사번,
       ENAME AS 이름,
       SAL * 1.1 AS 인상급여
    FROM EMP;

 

 

2) 담당 교수가 없는 과목의 정보를 검색하라

 

 

SELECT *
    FROM COURSE
    WHERE PNO IS NULL;

 

 

3) 1, 2학년 학생 중에 평점이 2.0에서 3.0사이인 학생을 검색하라

 

 

SELECT SNO
    , SNAME
    , SYEAR
    , AVR
    FROM STUDENT
    WHERE SYEAR IN (1, 2)
    AND AVR BETWEEN 2.0 AND 3.0;

 

 

4) 화학, 물리학과 학생 중 1, 2 학년 학생을 성적순으로 검색하라

 

 

SELECT MAJOR
    , SYEAR
    , SNAME
    , AVR
    FROM STUDENT
    WHERE MAJOR IN ('화학', '물리') //문자옆에 ''
    AND SYEAR IN (1 , 2)
    ORDER BY MAJOR, SYEAR ASC, AVR DESC;

 

 

Day2

 

 

1) 송강 교수가 강의하는 과목을 검색한다

 

 

 

SELECT A.CNAME
    , A.PNO
    , B.PNO
    , B.PNAME
    FROM COURSE A
        , PROFESSOR B
    WHERE A.PNO = B.PNO
    AND B.PNAME = '송강';

 

 

2) 학생중에 동명이인을 검색한다

 

 

 

SELECT A.SNO
    , A.SNAME
    FROM STUDENT A
        , STUDENT B
    WHERE A.SNAME = B.SNAME
    AND A.SNO != B.SNO; //1개의 테이블을 2개의 데이터로 쪼갬

 

 

 

 

3) 화학과 1학년 학생의 일반화학 기말고사 점수를 검색한다

 

 

 

 

//
구조 나누기 1. (필요한 SELECT) 
STUDENT SNO, SANME, SYEAR, MAJOR
COURSE CNO,CNAME
SCORE SNO, CNO, RESULT

2. (조건) SYEAR = 1 MAJOR = 화학
RESULT

3. (정렬순서) ORDER BY RESULT DESC;
//

SELECT A.SNO
    , A.SNAME
    , A.SYEAR
    , A.MAJOR
    , B.CNAME
    , C.RESULT
    FROM STUDENT A
        , COURSE B
        , SCORE C
    WHERE A.SNO = C.SNO
    AND B.CNO = C.CNO
    AND A.SYEAR = 1
    AND A.MAJOR = '화학'
    AND B.CNAME = '일반화학'
    ORDER BY C.RESULT DESC;

 

4) 화학과 학생이 수강하는 과목을 담당하는 교수의 명단을 검색하라

 

 

 

 SELECT A.SNO
    , A.SNAME
    , A.SYEAR
    , A.MAJOR
    , B.CNO
    , B.CNAME
    , D.PNO
    , D.PNAME
    FROM STUDENT A
        , COURSE B
        , SCORE C
        , PROFESSOR D
    WHERE A.SNO = C.SNO
    AND B.CNO = C.CNO
    AND B.PNO = D.PNO
    AND A.MAJOR = '화학'
    ORDER BY A.SYEAR, A.SNO;

 

Day3

 

1) 일반 과목을 기초 과목으로 변경해서 모든 과목을 검색하세요 (일반화학 -> 기초화학)

 

 

 

SELECT REPLACE(CNAME, '일반', '기초')
    FROM COURSE
    WHERE CNAME LIKE '%일반%';

 

 

 

2) 1991년에서 1995년 사이에 부임한 교수를 검색하세요

 

 

 

SELECT PNO
     , PNAME
     , HIREDATE
    FROM PROFESSOR
    WHERE TRUNC(HIREDATE, 'YYYY') BETWEEN TO_DATE('1991', 'YYYY') AND TO_DATE('1995', 'YYYY');

 

 

3) 화학과와 생물학과 학생 4.5 환산 평점의 평균을 각각 검색하세요

 

 

SELECT MAJOR
     , ROUND(AVG(AVR), 2)
    FROM STUDENT
    GROUP BY MAJOR
    HAVING MAJOR IN ('화학', '생물');

 

 

4) 기말고사 평균이 60점 이상인 학생의 정보를 검색하세요

 

 

SELECT A.SNO
     , A.SNAME
     , B.RESULT
    FROM STUDENT A
       , (
            SELECT SNO
                 , ROUND(AVG(RESULT), 2) AS RESULT
                FROM SCORE
                GROUP BY SNO
         ) B
    WHERE A.SNO = B.SNO
      AND B.RESULT >= 60;

 

Day4

 

 1) 모든 학생의 성적을 4.5만점 기준으로 수정하세요

 

 

UPDATE STUDENT
	SET AVR = NVL(AVR, 0) * 4.5 / 4.0;

 

 

 

 

2) 모든 교수의 부임일자를 100일 앞으로 수정하세요

 

 

 

UPDATE PROFESSOR
	SET HIREDATE = HIREDATE - 100;

 

 

 

 

3) 다음 구조를 갖는 테이블을 생성하세요.

 

PRODUCT 테이블 - PNO NUMBER PK : 제품번호

                                PNMAE VARCHAR2(50) : 제품이름

                                PRI NUMBER : 제품단가

PAYMENT 테이블 - MNO NUMBER PK : 전표번호

                                PDATE DATE NOT NULL : 판매일자

                                CNAME VARCHAR2(50) NOT NULL : 고객명

                                TOTAL NUMBER TOTAL > 0 : 총액

PAYMENT_DETAIL - MNO NUMBER PK FK : 전표번호

                                 PNO NUMBER PK FK : 제품번호

                                 AMOUNT NUMBER NOT NULL : 수량

                                 PRICE NUMBER NOT NULL : 단가

                                 TOTAL_PRICE NUMBER NOT NULL TOTAL_PRICE > 0 : 금액

 

 

CREATE TABLE PRODUCT (
	PNO NUMBER
    , PNAME VARCHAR2(50)
    , PRI NUMBER
    , CONSTRAINT PRODUCT_PNO_PK PRIMARY KEY(PNO)
);

CREATE TABLE PAYMENT (
	MNO NUMBER
    , PDATE DATE NOT NULL
    , CNAME VARCHAR2(50) NOT NULL
    , TOTAL NUMBER
    , CONSTRAINT PAYMENT_MNO_PK PRIMARY KEY(MNO)
    , CONSTRAINT PAYMENT_TOTAL_CH CHECK(TOTAL > 0)
);    
    
CREATE TABLE PAYMENT_DETAIL (
	MNO NUMBER
    , PNO NUMBER
    , AMOUNT NUMBER NOT NULL
    , PRICE NUMBER NOT NULL
    , TOTAL_PRICE NUMBER NOT NULL
    , CONSTRAINT PAYMENT_DETAIL_PK PRIMARY KEY(MNO, PNO)
    , CONSTRAINT PAYMENT_DETAIL_MNO_FK FOREIGN KEY(MNO) REFERENCES PAYMENT(MNO)
    , CONSTRAINT PAYMENT_DETAIL_PNO_FK FOREIGN KEY(PNO) REFERENCES PRODUCT(PNO)
    , CONSTRAINT PAYMENT_DETAIL_TOTAL_PRICE_CH CHECK (TOTAL_PRICE > 0)
);​