Oracle [6] 서브쿼리, 제약조건(PK, FK)

 

서브쿼리란? (하위 질의문)

 

 

-WHERE, HAVING절 하의질의문

-FROM절 하위질의문 ->

 

 

서브쿼리문 작성순서 

 

 

1. 서브쿼리문 먼저 작성

2. 메인 쿼리문 작성 

 

 

//부서 중 평균연봉이 가장 높은 부서의 번호와 평균 연봉 조회

순서 : 1. 평균연봉이 가장높은 쿼리문 2. 메인 쿼리문

SELECT MAX(AVG(SAL))
	FROM EMP
    GROUP BY DNO;
    
SELECT DNO
	, AVG(SAL)
    FROM EMP
    GROUP BY DNO
    HAVING AVG(SAL) = (
    					SELECT MAX(AVG(SAL))
                        	FROM EMP
                            GROUP BY DNO
                      );

 

 

 

ex) 물리학과 학생 중에 학년별로 성적이 가장 우수한 학생의 평점을 검색하세요.

 

 

 

SELECT A.MAJOR
	, A.SYEAR
    , A.SNO
    , A.SNAME
    , B.AVR
    FROM STUDENT A
    	, (
        	SELECT SYEAR
            	, MAX(AVR) AS AVR
                FROM STUDENT
                WHERE MAJOR = '물리;'
                GROUP BY SYEAR
                ) B
     WHERE A.AVR = B.AVR
     AND A.SYEAR = B.SYEAR
     AND MAJOR = '물리';

 

 

 

PRIMARY KEY 제약조건

 

 

테이블의 모든 데이터를 유일하게 식별해주는 식별자

 

PK로 설정된 컬럼의 중복 데이터는 허용되지 않음

 

-> UNIQUE + NOT NULL

 

(즉, 주민등록번호, ID와 같이 다른 데이터와 겹치지 않아야 하는 데이터를 사용할 때 이용하면 좋다.)

 

 

--컬럼 단위에서 지정
      CREATE TABLE 테이블 명 (
          컬럼1 데이터타입 CONSTRAINT 제약조건 PRIMARY KEY,
          컬럼2 .....,

      );
      
--테이블 단위에서 지정
      CREATE TABLE 테이블 명 (
          컬럼1 데이터타입,
          컬럼2 데이터타입,
          컬럼3 ....
          CONSTRAINT 제약조건 PRIMARY KEY(PK로 지정할 컬럼명)
      );

 

 

CREATE TABLE BOARD (
    NO NUMBER,
    NAME VARCHAR2(50),
    SUB VARCHAR2(200),
    CONSTRAINT BOARD_NO_PK PRIMARY KEY(NO)
);



--value는 1이라는 PK삽입
INSERT INTO BOARD (
    NO
) VALUES (
    1
);

 

 

 

FOREIGN KEY

 

테이블 간의 관계를 뜻함(다른 테이블에서 참조해오는 키 값)

 

FK를 설정하면 테이블간의 종속성이 생성된다.(부모, 자식 관계)

 

 

예를 들어 같은 ID값을 공유해야 하는 A테이블(부모)와 B테이블(자식)이 있다고 가정해보자.

 

A테이블과 B테이블은 같은 ID값을 공유해야 하는데 이때 B테이블의 ID 컬럼에 외래키를 지정하고

 

참조테이블로 A테이블의 ID컬럼을 참조하면 둘의 부모 / 자식 관계가 생긴다.

 

 

 

테이블 단위에서 지정
      CREATE TABLE 테이블 명 (
          컬럼1 데이터타입,
          컬럼2 데이터타입,
          ....
          CONSTRAINT 제약조건 FOREIGN KEY(컬럼명)
          REFERENCES 참조할 테이블 명(참조할 컬럼명)
          (ON DELETE CASCADE)
      )

 

 

CREATE TABLE B_CONTENT (
    NO NUMBER,
    CONTENT VARCHAR2(1000),
    CONSTRAINT B_CONTENT_NO_FK FOREIGN KEY(NO)
                                REFERENCES BOARD(NO)
);


INSERT INTO B_CONTENT (
    NO,
    CONTENT
) VALUES (
    1,
    'DDD'
);

 

 

CHECK

 

 

데이터의 값의 범위나 조건을 설정하여 조건에 해당되는 데이터만 허용한다.

 

 

DEFAULT

 

 

아무런 데이터를 입력하지 않았을 경우 지정한 데이터가 자동으로 입력된다. 

 

테이블 수정 방식

 

 

테이블을 먼저 생성 후 추가해 준다.

 

CREATE TABLE ST (
    SNO VARCHAR2(2),
    SNAME VARCHAR2(50),
    CNO VARCHAR2(2)
);


//ST 테이블에 PK추가
ALTER TABLE ST
    ADD CONSTRAINT ST_SNO_PK PRIMARY KEY(SNO);
  
  
//ST 테이블에 FK 추가
ALTER TABLE ST
    ADD CONSTRAINT ST_CNO_FK FOREIGN KEY(CNO) REFERENCES CLASS(CNO);
    
    
//삭제
ALTER TABLE ST
	DROP CONSTRAINT 제약조건 이름

 

 

EXAMPLE

 

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

 

FACTORY 테이블 - FNO NUMBER PK : 공장번호

FNMAE VARCHAR2(50) : 공장이름

LOC VARCHAR2(10) : 공장위치

GOODS 테이블 - GNO NUMBER PK : 제품번호

GNAME VARCHAR2(50) : 제품이름

PRI NUMBER : 제품단가

FNO NUMBER FK : 공장번호

PROD 테이블 - PNO NUMBER PK : 상품번호

GNO NUMBER FK : 제품번호

PRICE NUMBER : 출고단가

PDATE DATE : 생산일자

 

CREATE TABLE FACTORY (
    FNO NUMBER,
    FNAME VARCHAR2(50),
    LOC VARCHAR2(10),
    CONSTRAINT FACTORY_FNO_PK PRIMARY KEY(FNO)
);

CREATE TABLE GOODS (
    GNO NUMBER,
    GNAME VARCHAR2(50),
    PRI NUMBER,
    FNO NUMBER,
    CONSTRAINT GOODS_GNO_PK PRIMARY KEY(GNO),
    CONSTRAINT GOODS_FNO_FK FOREIGN KEY(FNO) REFERENCES FACTORY(FNO)
);

CREATE TABLE PROD (
    PNO NUMBER,
    GNO NUMBER,
    PRICE NUMBER,
    PDATE DATE,
    CONSTRAINT PROD_PNO_PK PRIMARY KEY(PNO),
    CONSTRAINT PROD_GNO_FK FOREIGN KEY(GNO) REFERENCES GOODS(GNO)
);

 

 

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

 

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)
);