[Day11] Oracle 11 - PURGE, 제약조건2, JOIN

[Day11] Oracle11 [10/12]

--1.  truncate / delete / drop 대해서 설명하세요
--     DDL : CREATE, ALTER , [ DROP ] 
--     DML : INSERT, UPDATE, [DELETE], [TRUNCATE], RENAME + 커밋, 롤백
                                                       삭제         삭제 + 자동 커밋.

--2.  insert 문 수행 중 다음과 같은 오류가 발생했다면 이유에 대해 설명하세요
--  ㄱ. 00947. 00000 -  "not enough values"
     INSERT INTO emp ( empno, ename, hiredate ) VALUES ( 2222, 'aaa');
--  ㄴ. ORA-00001: unique constraint (SCOTT.SYS_C007770) violated  
      SYS_?????  제약조건 이름 자동 부여
      PK_테이블명_컬럼명
      PK_EMP_EMPNO
      UK 유일성 제약조건, 중복 X
      
    empno 7369 SMITH
    INSERT    empno    VALUES ( 7369 )  PK 제약조건 위배 = UK + NN
    
     개체 무결성..
      
--  ㄷ. ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found
--    무결성 제약조건 위배 :  FK_DEPTNO 포린키==참조키== 외래키
    dept 테이블에 : deptno 10,20,30,40     부모테이블( parent )
    
    관계: RDBMS      소속관계  dept.deptno(PK) -> emp.deptno(FK) 
    
    INSERT emp 테이블에서 : deptno(FK) 50  자식테이블( child )
    
    개체, [참조], 도메인 무결성
    
    kor NUMBER(3)  -999~999                  0~100 CHECK 제약조건 설정


--3. 서브쿼리를 사용해서 테이블 생성
--  ㄱ. deptno, dname, empno, ename, sal+nvl(comm,0) pay, grade 컬럼을 가진 새로운 테이블 생성
--  ㄴ. 테이블명 : tbl_empdeptgrade   

dept : deptno, dname
emp  : empno, ename, sal, comm
salgrade : grade 

3개 테이블 조인
-- ORA-00918: column ambiguously defined

CREATE TABLE tbl_empdeptgrade
AS ( 
     SELECT d.deptno, dname, empno, ename, sal+nvl(comm,0) pay, grade
     FROM emp e JOIN dept d ON d.deptno = e.deptno
                JOIN salgrade s ON e.sal BETWEEN losal AND hisal
);

--4-1. insa 테이블에서 num, name 가져와서 tbl_score 테이블 생성
--     (tbl_score 테이블이 있으면 삭제 후 시작하세요 )

CREATE TABLE tbl_score
AS ( 
     SELECT num, name
     FROM insa
);

--4-2. kor, eng, mat, tot, avg , grade, rank 컬럼 추가

ALTER TABLE tbl_score
ADD (
        kor NUMBER(3)
        , 
        
        , grade NVARCHAR2(3)      --  CHAR(1 CHAR) 'A'~'F'
        , rank NUMBER(3)
);

--4-3. 각 학생들의 kor,eng,mat 점수 0~100 랜덤하게 채워넣기.

UPDATE tbl_score
SET   kor = TRUNC( dbms_random.value(0, 101) )
, eng = TRUNC( dbms_random.value(0, 101) )
, mat = TRUNC( dbms_random.value(0, 101) );

dbms_random.value -> 실수 가져옴

TRUNC 통해서 정수로 만들어줘야함

--4-4. 총점, 평균, 등급, 등수 수정     *****
--    조건)
--     등급은 모든 과목이 40점이상이고, 평균 60 이상이면 "합격"
--           평균 60 이상이라도 한 과목이라 40점 미만이라면  "과락"
--           그외는 "불합격" 이라고 저장.

정보처리기능사/기사 실기          과락  40 미만,  평균 60 

UPDATE tbl_score  a
SET  tot =  kor+eng+mat
, avg = (kor+eng+mat)/3   -- NUMBER(5,2)
, grade = CASE
                    WHEN  kor>=40 AND eng>=40 AND mat>=40 AND 평 >=60 THEN '합격'
                    WHEN (kor<40 OR eng<40 OR mat<40) AND 평 >=60  THEN '과락'
                    ELSE '불합격'
          END
, rank = (  SELECT COUNT(*)+1  FROM tbl_score b WHERE b.총 > a.총)

--5.  emp 테이블의 구조를 확인하고, 제약조건을 확인하고, 임의의 사원 정보를 추가하는 INSERT 문을 작성하세요.
--   ㄱ. 구조확인 쿼리 
DESC emp;

--   ㄴ. 제약조건 확인 쿼리

SELECT *
FROM user_constraints
WHERE table_name = 'EMP'; -- 뷰(VIEW)
FROM user_users;
FROM user_tables;

--   ㄷ. INSERT 쿼리 
--
--6-1. emp 테이블의 구조만 복사해서 새로운 tbl_emp10, tbl_emp20, tbl_emp30, tbl_emp40 테이블을 생성하세요. 

CREATE TABLE tbl_emp10  AS ( SELECT * FROM emp WHERE 1 = 0 );
CREATE TABLE tbl_emp20  AS ( SELECT * FROM emp WHERE 1 = 0 );
CREATE TABLE tbl_emp30  AS ( SELECT * FROM emp WHERE 1 = 0 );
CREATE TABLE tbl_emp40  AS ( SELECT * FROM emp WHERE 1 = 0 );

--6-2. emp 테이블의 각 부서에 해당하는 사원정보를  위에서 생성한 테이블에 INSERT 하는 쿼리를 작성하세요.   

다중 INSERT문  4가지
1. UC I A           X
2. C I A
3. P I              X
4. C I F

INSERT ALL
      WHEN deptno = 10 THEN
        INTO tbl_emp10 VALUES ( 칼럼명~ )
      WHEN deptno = 20 THEN  
        INTO tbl_emp20 VALUES ( 칼럼명~ )
      WHEN deptno = 30 THEN   
        INTO tbl_emp30 VALUES ( 칼럼명~ )
      ELSE  
        INTO tbl_emp40 VALUES ( 칼럼명~ )
   SELECT * FROM emp;

--7. 조건이 있는 다중 INSERT 문에서  INSERT ALL 과 INSERT FIRST 문에 대한 차이점을 설명하세요.
INSERT ALL : 모든 WHEN 조건 실행
INSERT FIRST : 첫번째 만족하는 WHEN만 실행

 

--8. 제약조건( Contratrint ) 
--  ㄱ. 제약조건이란 ?  테이블에 DML 작업 시 데이터 무결성 위한 규칙 
--       데이타 무결성 ? 데이터가 허가 되지 않는 값으로 추가,수정,삭제 제한하는 특성.

--  ㄴ. 제약조건을 설정하는 2가지 방법에 대해 설명하세요.
--        컬럼레벨 - IN-LINE
          테이블레벨 - OUT-LINE
          
--  ㄷ. 제약조건의 5가지 종류 
--      PK, FK, NN, CK, UK

--  ㅁ. 데이터 무결성 종류 및 설명
       개체,
       참조,
       도메인 무결성

---

* 테이블 삭제 시

drop table 테이블명;

 

* 휴지통에 넣고 비우기
DROP TABLE 테이블명 purge;

purge recyclebin;

* 휴지통에 있는 테이블 복원
FLASHBACK TABLE 테이블명 TO BEFORE DROP

---

-- 제약조건[constraint] --

1. 테이블에 제약 조건 확인 : user_constraints 뷰(view);

SELECT *
FROM user_constraints
WHERE table_name = 'EMP';

2. 제약 조건 ? 
   ㄱ. 데이터 무결성(integrity constraint )을 위해서 테이블에 레코드(행)을 추가, 수정, 삭제할 때 적용되는 규칙
   ㄴ. 테이블의 삭제 방지를 위해서도 제약조건을 사용한다. 
         ex) DEPT( deptno PK ) -- EMP ( deptno  FK )

DELETE FROM dept
WHERE deptno = 30;
-- ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

    ㄷ. 데이터 무결성? 데이터가 허가되지 않는 값으로 추가,수정,삭제 제한하는   특성
        • 1) 개체 무결성(Entity Integrity)

INSERT INTO dept  VALUES ( 10, 'QC', 'SEOUL');
-- ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

- 릴레이션(table)에 저장되는 튜플(tuple == 행 == row == record )의 유일성을 보장하기 위한 제약조건이다 == PK 고유키

        • 2) 참조 무결성(Relational Integrity)
        

UPDATE emp
SET deptno = 90   -- dept테이블에서는 90 번 부서가 존재하지 않기때문에 참조할 수 없다. 
WHERE empno = 7369;

ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

 

 - 릴레이션(테이블) 간의 데이터의 [일관성]을 보장하기 위한 제약조건이다

        
      • 3) 도메인 무결성(domain integrity)
           컬럼(속성)의 값의 데이터 타입, 길이, 기본 키, [유일성], null 허용, 허용 제약 조건
           예) 국어 점수
               kor NUMBER(3) NOT NULL  DEFAULT 0   입력을 하지 않으면 기본값 0 으로 입력
                                    필수 입력 사항        NOT NULL
                                    
                -999~999 정수를 저장할 수 있다.                  0<=  정수 <=100 ( 범위 제한 )


 3. 제약조건을 사용하는 이유 ? DML 할 때 잘못 조작되는 것을 방지하기 위해서...
      DML  = insert, update, delete

 4. 제약 조건을 선언(설정)하는 방법
    ㄱ. CREATE TABLE 테이블 생성
    ㄴ. ALTER TABLE 테이블 수정..
    
    
 5. 제약 조건의 종류 5가지.
   ㄱ. PRIMARY KEY 제약조건( 고유키  PK)
   ㄴ. FOREIGN KEY 제약조건( 외래키, 참조키 FK )
   ㄷ. NOT NULL 제약조건 (NN)
   ㄹ. UNIQUE 제약조건 ( 유일성   UK )
   ㅁ. CHECK 제약조건 ( CK )
   
 6. 제약조건을 생성하는 2가지 방법
   ㄱ. 컬럼 레벨( column level ) == IN-LINE constaint 방법
   ㄴ. 테이블 레벨( table level )  == OUT-OF-LINE constaint 방법
       
------------------------------------------------------------------------     

-- 각 사원을 구별할 수 있는 고유한 키  : empno   PK  설정 == NN+UK
--                       후 보키   : empno, [email X] , rrn, [ hp X ], 등등
--                                   0000              14

-- 제약조건 :  1개( PK )  DML X  -> 부여(설정)
1) 컬럼 레벨( in-line 방식)
   -- PK, NN,  FK, UK, CK

 CREATE TABLE tbl_column_level
 (
    empno  number(4) NOT NULL  CONSTRAINT PK_TBLCOLUMNLEVEL_EMPNO PRIMARY KEY -- UK + NN
    , ename  varchar2(20) NOT NULL
    
    -- dept 테이블의 deptno(PK)       참조하는 외래키( FK )
    , deptno number(2) NOT NULL CONSTRAINT FK_TBLCOLUMNLEVEL_DEPTNO REFERENCES dept(deptno)
    -- -999~999 X   [0~100 정수]
    , kor  number(3) CONSTRAINT CK_TBLCOLUMNLEVEL_KOR CHECK( kor BETWEEN 0 AND 100 )
    -- 유일해야된다.( 유일성 )
    , email varchar2(250)  CONSTRAINT UK_TBLCOLUMNLEVEL_EMAIL UNIQUE
    -- 서울, 부산, 대구, 대전   목록에서 하나를 선택할 수 있도록 체크 제약 설정
    , city varchar2(20) CONSTRAINT CK_TBLCOLUMNLEVEL_CITY CHECK( city IN ('서울', '부산', '대구', '대전') )
 );
-- Table TBL_COLUMN_LEVEL이(가) 생성되었습니다.
COMMIT;

-- ORA-02291: integrity constraint (SCOTT.FK_TBLCOLUMNLEVEL_DEPTNO) violated - parent key not found
INSERT INTO TBL_COLUMN_LEVEL ( empno, ename, deptno, kor, email, city )
VALUES ( 1111, '홍길동', 50, 100, 'hong@sist.com', null );

-- ORA-02290: check constraint (SCOTT.CK_TBLCOLUMNLEVEL_KOR) violated
-- NUMBER(3) -999~999           CHECK( kor between 0 AND 100 )
INSERT INTO TBL_COLUMN_LEVEL ( empno, ename, deptno, kor, email, city )
VALUES ( 1111, '홍길동', 40, 101, 'hong@sist.com', null );

INSERT INTO TBL_COLUMN_LEVEL ( empno, ename, deptno, kor, email, city )
VALUES ( 1111, '홍길동', 40, 100, 'hong@sist.com', null );
-- 1 행 이(가) 삽입되었습니다.

-- ORA-00001: unique constraint (SCOTT.UK_TBLCOLUMNLEVEL_EMAIL) violated
INSERT INTO TBL_COLUMN_LEVEL ( empno, ename, deptno, kor, email, city )
VALUES ( 1112, '김딜동', 40, 44, 'hong@sist.com', null );

-- ORA-02290: check constraint (SCOTT.CK_TBLCOLUMNLEVEL_CITY) violated
-- 서울 대구 대전 부산
INSERT INTO TBL_COLUMN_LEVEL ( empno, ename, deptno, kor, email, city )
VALUES ( 1112, '김딜동', 40, 44, 'hong2@sist.com', '포항' );

-- NOT NULL 제약 == 필수입력.
-- ORA-01400: cannot insert NULL into ("SCOTT"."TBL_COLUMN_LEVEL"."ENAME")
INSERT INTO TBL_COLUMN_LEVEL ( empno, ename, deptno, kor, email, city )
VALUES ( 1112,  null, 40, 44, 'hong2@sist.com', '대전' );

SELECT *
FROM tbl_column_level
-- ORA-02290: check constraint (SCOTT.CK_TBLCOLUMNLEVEL_CITY) violated
UPDATE tbl_column_level
SET city = '포항'
WHERE empno = 1111;

2) 테이블 레벨( out-line 방식)
-- NOT NULL 제약조건은 테이블 레벨 방식으로 설정할 수 없다. 

CREATE TABLE tbl_table_level
 (
    empno  number(4)        NOT NULL  
    , ename  varchar2(20)   NOT NULL
    , deptno number(2)  
    , kor  number(3)   
    , email varchar2(250) 
    , city varchar2(20)
    
    -- 제약조건
    -- 복합키설정 = 컬럼레벨 X, 테이블 레벨 O
    , CONSTRAINT PK_TBLCOLUMNLEVEL_EMPNO PRIMARY KEY( empno ) 
    , CONSTRAINT FK_TBLCOLUMNLEVEL_DEPTNO FOREIGN KEY(deptno) REFERENCES dept(deptno)
    , CONSTRAINT CK_TBLCOLUMNLEVEL_KOR CHECK( kor BETWEEN 0 AND 100 )
    , CONSTRAINT UK_TBLCOLUMNLEVEL_EMAIL UNIQUE( email )
    , CONSTRAINT CK_TBLCOLUMNLEVEL_CITY CHECK( city IN ('서울', '부산', '대구', '대전') )
    
 );
-- Table TBL_TABLE_LEVEL이(가) 생성되었습니다.

복합키

급여지급 테이블 예시

이 테이블에서는 PK가 없음

 -- 복합키( 지급일 + 사원번호  )

성능 떨어짐

 UPDATE 급여지급테이블
 SET 지급액 = 3,800,00
 WHERE 복합키
    22.08.25 AND  1111
   --> 역정규화   seq 컬럼 추가 
 

 지급일      사원번호     지급액         ...  시퀀스seq(PK)
 22.08.25    1111        3,500,000            1
 22.08.25    1112        3,500,000            2
 22.08.25    1113        3,500,000            3
 :
 22.09.25    1111        3,500,000
 22.09.25    1112        3,500,000
 22.09.25    1113        3,500,000
 :
 22.10.25    1111        3,500,000
 22.10.25    1112        3,500,000
 22.10.25    1113        3,500,000

-- 제약 조건 확인

SELECT *
FROM user_constraints
WHERE table_name = 'TBL_TABLE_LEVEL' ;

 

  
C : NN,   CK
P : PK
U : UK
R : FK

 

OWNER       CONSTRAINT_NAME                            CONSTRAINT_TYPE
SCOTT        SYS_C009056                                       C                        // 자동으로 제약조건명이 부여(설정)  SYS_   NN
SCOTT        SYS_C009057                                       C
SCOTT        CK_TBLCOLUMNLEVEL_KOR              C
SCOTT        CK_TBLCOLUMNLEVEL_CITY             C
SCOTT        PK_TBLCOLUMNLEVEL_EMPNO         P
SCOTT        UK_TBLCOLUMNLEVEL_EMAIL           U
SCOTT        FK_TBLCOLUMNLEVEL_DEPTNO        R 
  
 -- 도메인 무결성 위배 ( UK, CK )
 kor    0~100  111 X
 -- 개체 무결성( PK 위배 )
 empno 1111 INSERT O
 empno 1111 INSERT X  PK
 -- 참조 무결성 ( FK 위배 )
 deptno 50   INSERT X      dept.deptno 참조( 10,20,30,40)
  
 --제약조건 제거 --
 -- 1) TBL_TABLE_LEVEL 테이블의 PK 제약 조건 제거. 

ALTER TABLE  TBL_TABLE_LEVEL
DROP PRIMARY KEY;

ALTER TABLE  TBL_TABLE_LEVEL
DROP CONSTRAINT PK_TBLCOLUMNLEVEL_EMPNO;   -- FK X  [CASCADE]X
-- Table TBL_TABLE_LEVEL이(가) 변경되었습니다.

• 제약조건은 수정할 수 없으며, 기존의 constraint를 삭제 후 재 생성하여야 한다.
• constraint를 삭제할려면, 직접 constraint명을 사용해서 삭제하거나 
 또는
 constraint가 포함된 [테이블을 삭제]하면 그 테이블에 속한 constraint도 함께 삭제된다.
• 무결성 constraint를 삭제할 때, 그 constraint는 더 이상 서버에 의해서 적용되지 않기 때문에
  ***[data dictionary]***에서 확인할 수 없다.
• primary key는 테이블당 하나만 존재하므로 ,삭제시 constraint명을 지정하지 않아도 primary key 제약조건이 삭제된다.
예) 

ALTER TABLE 테이블명 
   DROP PRIMARY KEY;

방법1)

ALTER TABLE 테이블명 
DROP [CONSTRAINT constraint명 | PRIMARY KEY | UNIQUE(컬럼명)]
[CASCADE];

 

* CASCADE옵션은 참조하는 FOREIGN KEY가 있을 때 사용한다.

방법2)

DROP TABLE 테이블명 CASCADE CONSTRAINTS;

테이블과 그 테이블을 참조하는 foreign key를 동시에 삭제할 수 있다.

방법3)

DROP TABLESPACE 테이블스페이스명 
INCLUDING CONTENTS
CASCADE CONSTRAINTS;

 -- 2) 테이블 생성 완료 후 제약조건 추가 --
 무결성 제약조건은 기존 테이블에 대해서도 추가로 생성할 수 있다
 . ALTER TABLE ... ADD CONSTRAINT 문은 기존 테이블에 제약조건을 추가하기 위한 명령문이다. 
 하지만, NOT NULL 무결성 제약조건의 추가는 'NULL 허용' 상태를 'NULL 입력불가' 상태로 변경하는 것을 의미하므로
 ALTER TABLE ... MODIFY 문을 사용해야 한다.


【형식】
   ALTER TABLE 테이블명
   ADD [CONSTRAINT 제약조건명] 제약조건타입 (컬럼명);

-- 문제) 모든 제약조건 삭제 쿼리 작성  
SCOTT   SYS_C009056  NN
SCOTT   SYS_C009057  NN   

SCOTT   CK_TBLCOLUMNLEVEL_KOR
SCOTT   CK_TBLCOLUMNLEVEL_CITY
SCOTT   UK_TBLCOLUMNLEVEL_EMAIL
SCOTT   FK_TBLCOLUMNLEVEL_DEPTNO  

ALTER TABLE tbl_table_level 
DROP CONSTRAINT CK_TBLCOLUMNLEVEL_KOR;

ALTER TABLE tbl_table_level 
DROP CONSTRAINT CK_TBLCOLUMNLEVEL_CITY;

ALTER TABLE tbl_table_level 
DROP CONSTRAINT UK_TBLCOLUMNLEVEL_EMAIL;

ALTER TABLE tbl_table_level 
DROP CONSTRAINT FK_TBLCOLUMNLEVEL_DEPTNO;

ALTER TABLE tbl_table_level
DROP CONSTRAINT SYS_C009056; -- NN

ALTER TABLE tbl_table_level
DROP CONSTRAINT SYS_C009057;   -- NN

-- 고려~ 

ALTER TABLE TBL_TABLE_LEVEL
MODIFY empno NULL;
ALTER TABLE TBL_TABLE_LEVEL
MODIFY ename NULL;

-- 문제) 모든 제약 조건 다시 추가해 주세요.   
--SCOTT   SYS_C009056   C        자동으로 제약조건명이 부여(설정)  SYS_   NN

ALTER TABLE TBL_TABLE_LEVEL
ADD CHECK( empno IS NOT NULL );

--SCOTT   SYS_C009057   C

ALTER TABLE TBL_TABLE_LEVEL
MODIFY ename NOT NULL;

--SCOTT   CK_TBLCOLUMNLEVEL_KOR   C

ALTER TABLE TBL_TABLE_LEVEL
ADD CONSTRAINT CK_TBLTABLELEVEL_KOR  CHECK( kor BETWEEN 0 AND 100 );

--SCOTT   CK_TBLCOLUMNLEVEL_CITY   C

ALTER TABLE TBL_TABLE_LEVEL
ADD CONSTRAINT CK_TBLTABLELEVEL_CITY  CHECK( city IN ('서울', '부산', '대구', '대전') );

--SCOTT   PK_TBLCOLUMNLEVEL_EMPNO   P

ALTER TABLE TBL_TABLE_LEVEL
ADD CONSTRAINT PK_TBLTABLELEVEL_EMPNO  PRIMARY KEY( empno );

--SCOTT   UK_TBLCOLUMNLEVEL_EMAIL   U

ALTER TABLE TBL_TABLE_LEVEL
ADD CONSTRAINT UK_TBLTABLELEVEL_EMAIL  UNIQUE( email );

--SCOTT   FK_TBLCOLUMNLEVEL_DEPTNO   R 

ALTER TABLE TBL_TABLE_LEVEL
ADD CONSTRAINT FK_TBLTABLELEVEL_DEPTNO FOREIGN KEY(deptno) REFERENCES dept(deptno);

-- 제약조건 비활성화/활성화

ALTER TABLE 테이블명
ENABLE | DISABLE  CONSTRAINT 제약조건명

-- *** FK 설정 형식. --
1) 【컬럼레벨의 형식】
        컬럼명 데이터타입 CONSTRAINT constraint명
   REFERENCES 참조테이블명 (참조컬럼명) 
             [ON DELETE CASCADE | ON DELETE SET NULL]
             

    deptno  NUMBER(2) CONSTRAINT  FK_TBLCOLUMNLEVEL_DEPTNO     
          REFERENCES dept(deptno) [ON DELETE CASCADE | ON DELETE SET NULL];

2) 【테이블레벨의 형식】
        컬럼명 데이터타입,
        컬럼명 데이터타입,
         ...
        CONSTRAINT constraint명 FOREIGN KEY(컬럼)
   REFERENCES 참조테이블명 (참조컬럼명)
             [ON DELETE CASCADE | ON DELETE SET NULL]

    , deptno  NUMBER(2)
    , CONSTRAINT FK_TBLCOLUMNLEVEL_DEPTNO FOREIGN KEY(depnto)   
           REFERENCES dept (deptno ) [ON DELETE CASCADE | ON DELETE SET NULL];

   옵션 1) ON DELETE CASCADE  : 부모행이 삭제될 때 참조하는 자식행도 강제로 삭제.
   옵션 2) ON DELETE SET NULL :          "          "       행의 FK 값을 NULL로 설정.      
  
  예) 
    ㄱ. emp -> tbl_emp 생성 
        dept -> tbl_dept생성
        

       DROP TABLE tbl_emp PURGE; 
       DROP TABLE tbl_dept PURGE;

       CREATE TABLE tbl_emp AS (  SELECT * FROM emp  );
       CREATE TABLE tbl_dept AS (  SELECT * FROM dept  );

       NN 제약조건 복사 되고 그 외 모든 제약조건은 복사 되지 않는다. 
       
     ㄴ. tbl_dept 테이블의 PK( deptno ) 설정
         기존 테이블에 제약조건 추가할 때 .

     ALTER TABLE tbl_dept
     ADD CONSTRAINT PK_TBLDEPT PRIMARY KEY( deptno );

     ㄷ. tbl_emp   테이블의 FK( deptno)  설정

     ALTER TABLE tbl_emp
     ADD CONSTRAINT PK_TBLEDP_EMPNO PRIMARY KEY( empno );
     
     ALTER TABLE tbl_emp
     ADD CONSTRAINT FK_TBLEDP_DEPTNO FOREIGN KEY( deptno ) REFERENCES tbl_dept( deptno );

     ㄹ. 제약 조건 확인..
     
    테스트) 

    -- ORA-02292: integrity constraint (SCOTT.FK_TBLEDP_DEPTNO) violated - child record found
    DELETE tbl_dept
    WHERE deptno = 10;

    
    ㅁ. FK 제약조건 삭제.

     ALTER TABLE tbl_emp
     DROP CONSTRAINT FK_TBLEDP_DEPTNO;

    ㅂ. FK 제약조건 추가 + ON DELETE CASCADE 옵션 추가.

    ALTER TABLE tbl_emp
     ADD CONSTRAINT FK_TBLEDP_DEPTNO FOREIGN KEY( deptno ) 
     REFERENCES tbl_dept( deptno      )
     ON DELETE CASCADE;\

    ㅅ. FK 제약조건 추가 + ON DELETE SET NULL 옵션 추가.

    ALTER TABLE tbl_emp
     ADD CONSTRAINT FK_TBLEDP_DEPTNO FOREIGN KEY( deptno ) 
     REFERENCES tbl_dept( deptno      )
     ON DELETE SET NULL;

 

---

 -- JOIN( 조인 )
1) 같거나 서로 다른 [두 개 이상]의 테이블에서 컬럼을 검색할 때 사용한다. 이때, where절에 [join 조건을 작성]하여 테이블을 연결한다  
2) 샘플 테이블   
  -- <1> 책 테이블

  CREATE TABLE book(
       b_id     VARCHAR2(10)    NOT NULL PRIMARY KEY   -- 책ID
      ,title      VARCHAR2(100) NOT NULL  -- 책 제목
      ,c_name  VARCHAR2(100)    NOT NULL     -- c 이름
  --    ,  price  NUMBER(7) NOT NULL
 );
 
 -- Table BOOK이(가) 생성되었습니다.
INSERT INTO book (b_id, title, c_name) VALUES ('a-1', '데이터베이스', '서울');
INSERT INTO book (b_id, title, c_name) VALUES ('a-2', '데이터베이스', '경기');
INSERT INTO book (b_id, title, c_name) VALUES ('b-1', '운영체제', '부산');
INSERT INTO book (b_id, title, c_name) VALUES ('b-2', '운영체제', '인천');
INSERT INTO book (b_id, title, c_name) VALUES ('c-1', '워드', '경기');
INSERT INTO book (b_id, title, c_name) VALUES ('d-1', '엑셀', '대구');
INSERT INTO book (b_id, title, c_name) VALUES ('e-1', '파워포인트', '부산');
INSERT INTO book (b_id, title, c_name) VALUES ('f-1', '엑세스', '인천');
INSERT INTO book (b_id, title, c_name) VALUES ('f-2', '엑세스', '서울');

COMMIT;

  -- <2> 단가 테이블

  CREATE TABLE danga(
      b_id  VARCHAR2(10)  NOT NULL  -- PK , FK
      ,price  NUMBER(7) NOT NULL    -- 책 가격
      
      ,CONSTRAINT PK_dangga_id PRIMARY KEY(b_id)
      ,CONSTRAINT FK_dangga_id FOREIGN KEY (b_id)
              REFERENCES book(b_id)
              ON DELETE CASCADE
);
-- Table DANGA이(가) 생성되었습니다.

INSERT INTO danga (b_id, price) VALUES ('a-1', 300);
INSERT INTO danga (b_id, price) VALUES ('a-2', 500);
INSERT INTO danga (b_id, price) VALUES ('b-1', 450);
INSERT INTO danga (b_id, price) VALUES ('b-2', 440);
INSERT INTO danga (b_id, price) VALUES ('c-1', 320);
INSERT INTO danga (b_id, price) VALUES ('d-1', 321);
INSERT INTO danga (b_id, price) VALUES ('e-1', 250);
INSERT INTO danga (b_id, price) VALUES ('f-1', 510);
INSERT INTO danga (b_id, price) VALUES ('f-2', 400);

COMMIT;

-- 왜 ? 책의 단가 테이블이 따로 만들어져 있을 까요? 
  자바정석 2000.1.1 초판   25000
  자바정석 2002.12.1 2판   30000
  :
  자바정석 2022.12.1 10판   50000
    
  출판사 -> [ 서점(고객) ]-> 고객(일반 사용자)
       판매             판매
 -- <3> 고객 테이블  

 CREATE TABLE gogaek(
      g_id       NUMBER(5) NOT NULL PRIMARY KEY 
      ,g_name   VARCHAR2(20) NOT NULL
      ,g_tel      VARCHAR2(20)
);
 
 INSERT INTO gogaek (g_id, g_name, g_tel) VALUES (1, '우리서점', '111-1111');
INSERT INTO gogaek (g_id, g_name, g_tel) VALUES (2, '도시서점', '111-1111');
INSERT INTO gogaek (g_id, g_name, g_tel) VALUES (3, '지구서점', '333-3333');
INSERT INTO gogaek (g_id, g_name, g_tel) VALUES (4, '서울서점', '444-4444');
INSERT INTO gogaek (g_id, g_name, g_tel) VALUES (5, '수도서점', '555-5555');
INSERT INTO gogaek (g_id, g_name, g_tel) VALUES (6, '강남서점', '666-6666');
INSERT INTO gogaek (g_id, g_name, g_tel) VALUES (7, '강북서점', '777-7777');

COMMIT;

 
 -- <4> 판매 테이블 

 CREATE TABLE panmai(
       id         NUMBER(5) NOT NULL PRIMARY KEY
      ,g_id       NUMBER(5) NOT NULL CONSTRAINT FK_PANMAI_GID
                     REFERENCES gogaek(g_id) ON DELETE CASCADE
      ,b_id       VARCHAR2(10)  NOT NULL CONSTRAINT FK_PANMAI_BID
                     REFERENCES book(b_id) ON DELETE CASCADE
      ,p_date     DATE DEFAULT SYSDATE
      ,p_su       NUMBER(5)  NOT NULL
);

INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (1, 1, 'a-1', '2000-10-10', 10);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (2, 2, 'a-1', '2000-03-04', 20);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (3, 1, 'b-1', DEFAULT, 13);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (4, 4, 'c-1', '2000-07-07', 5);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (5, 4, 'd-1', DEFAULT, 31);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (6, 6, 'f-1', DEFAULT, 21);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (7, 7, 'a-1', DEFAULT, 26);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (8, 6, 'a-1', DEFAULT, 17);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (9, 6, 'b-1', DEFAULT, 5);
INSERT INTO panmai (id, g_id, b_id, p_date, p_su) VALUES (10, 7, 'a-2', '2000-10-10', 15);

COMMIT;

 -- <5> 저자 테이블

CREATE TABLE au_book(
       id   number(5)  NOT NULL PRIMARY KEY
      ,b_id VARCHAR2(10)  NOT NULL  CONSTRAINT FK_AUBOOK_BID
            REFERENCES book(b_id) ON DELETE CASCADE
      ,name VARCHAR2(20)  NOT NULL
);
INSERT INTO au_book (id, b_id, name) VALUES (1, 'a-1', '저팔개');
INSERT INTO au_book (id, b_id, name) VALUES (2, 'b-1', '손오공');
INSERT INTO au_book (id, b_id, name) VALUES (3, 'a-1', '사오정');
INSERT INTO au_book (id, b_id, name) VALUES (4, 'b-1', '김유신');
INSERT INTO au_book (id, b_id, name) VALUES (5, 'c-1', '유관순');
INSERT INTO au_book (id, b_id, name) VALUES (6, 'd-1', '김하늘');
INSERT INTO au_book (id, b_id, name) VALUES (7, 'a-1', '심심해');
INSERT INTO au_book (id, b_id, name) VALUES (8, 'd-1', '허첨');
INSERT INTO au_book (id, b_id, name) VALUES (9, 'e-1', '이한나');
INSERT INTO au_book (id, b_id, name) VALUES (10, 'f-1', '정말자');
INSERT INTO au_book (id, b_id, name) VALUES (11, 'f-2', '이영애');

COMMIT;

--------------------------------------------------------------------
  다이어그램  -- ExERD

--------------------------------------------------------------------  
  
3) JOIN 종류
  1) EQUI JOIN  = Natural Join:  조건에 만족하는 데이터 추출
     ㄴ 두 개 이상의 테이블에 관계되는 컬럼들의 값들이 일치하는 경우에 사용되는 가장 일반적인 조인 형태.
     ㄴ WHERE 조인 조건에   =  ( 이콜 ) 등호를 사용한다. 
     ㄴ PK     -    FK  관계 이용
     ㄴ USING 절을 사용하여 이콜조인과 동일한 결과를 추출한다. 
     
     문제) 책ID(b_id), 책제목(title), 출판사(c_name), 단가(price) 출력
     BOOK : b_id, title, c_name
     DANGA : b_id, price
     
     방법1 )  객체명 표기하는 경우     

     SELECT book.b_id, title, c_name, price
     FROM book, danga
     WHERE  book.b_id = danga.b_id; 

-- 조인 조건에   = 등호를 사용했다.   EQUI JOIN 이라고 한다.

     
     *** 방법2 )  별칭( alias) 표기하는 경우     

     SELECT b.b_id, title, c_name, price
     FROM book b, danga d
     WHERE  b.b_id = d.b_id;

     *** 방법3 )  JOIN~ ON 구문을 사용하는 경우

     SELECT b.b_id, title, c_name, price
     FROM book b  JOIN danga d ON  b.b_id = d.b_id;

     방법4) USING 절을 사용하는 경우

     SELECT b_id, title, c_name, price
     FROM book   JOIN danga    USING ( b_id );  -- USING 절을 사용할 때는 객체명, 별칭 붙이지 않는다.

     
     방법 5) NATURAL JOIN 구문 사용

SELECT b_id, title, c_name, price
FROM book NATURAL JOIN danga ; -- 객체명을 붙이지 않는다.

EQUI JOIN

    (문제) 책코드(b_id), 책명(title), 수량(p_su), 단가(price), 서점명(g_name), 판매금액(p_su*price) 출력

    
    b_id, title            : book
    b_id, price               : danga
    b_id, p_su, g_name        : panmai
    g_name                    : gogaek
   

    SELECT b.b_id, title, p_su,  price , g_name , p_su * price
    FROM book b JOIN danga d   ON b.b_id = d.b_id
                JOIN panmai p  ON b.b_id = p.b_id
                JOIN gogaek g  ON p.g_id = g.g_id;

   (문제) 위의 쿼리를 USING 절 사용해서 수정

    SELECT b_id, title, p_su,  price , g_name , p_su * price
    FROM book  JOIN danga    USING(b_id)
                JOIN panmai    USING(b_id) 
                JOIN gogaek    USING(g_id) ;

   
     
  2) NON-EQUI JOIN 
     ㄴ 관계되는 컬럼이 정확히 일치하지 않는 경우에 사용되는 JOIN의 형태이다
     ㄴ WHERE 조인조건절에 = X
             BETWEEN A AND B 연산자를 사용한다. 
             
    (문제) empno, ename, sal , grade         
     emp      : empno, ename, sal
     salgrade : grade           losal <=         <=  hisal
     

     SELECT empno, ename, sal , grade         
     FROM emp e JOIN salgrade s ON  sal BETWEEN losal AND hisal;

  
  3 + 5          + 연산자 :    항의 갯수 ->   이항연산자
                              기능     ->    산술연산자( 덧셈연산자 )
                          
 

NON EQUI JOIN

  3) INNER JOIN 
      ㄴ조인 조건을 만족하는 행"만"  반환하는 조인
      
      테스트)
      SELECT *
      FROM emp;
      
    ㄱ) KING 사원의 부서번호   NULL 로 수정

    UPDATE emp
    SET   deptno = null
    WHERE empno = 7839; -- KING

    ㄴ)  dept, emp  조인해서 출력.
    
   

SELECT d.deptno, dname, ename, job
    FROM dept d INNER JOIN emp e ON  d.deptno = e.deptno;  -- EQUI JOIN, INNER JOIN

    
    emp : KING 사원 정보 보이지 않고.  O
    dept : 40 부서 정보 보이지 않는다.  O
    

INNER JOIN

  4) OUTER JOIN  *****
      ㄴJOIN 조건을 만족하지 않는 행을 보기 위한 추가적인 join의 형태이다.
      ㄴ '(+)' 연산자를 사용

    SELECT d.deptno, dname, ename, job
    FROM dept d FULL JOIN emp e ON  d.deptno = e.deptno;

OUTER JOIN

  (문제) 책ID, 책제목, 판매수량, 단가, 서점명(고객), 판매금액(판매수량*단가) 출력
  book  : b_id, title
  danga : price
  gogaek : g_name
  panami : p_su
  

  SELECT b.b_id, title, p_su, price, g_name
     , p_su * price 판매금액
  FROM book b, danga d, gogaek g, panmai p
  WHERE b.b_id=d.b_id AND b.b_id = p.b_id AND p.g_id = g.g_id;
  --
  SELECT b.b_id, title, p_su, price, g_name
     , p_su * price 판매금액
  FROM book b JOIN danga d  ON b.b_id=d.b_id
              JOIN panmai p ON  b.b_id = p.b_id
              JOIN gogaek g ON p.g_id = g.g_id 
  ;

   (문제) 출판된 책들이 각각 총 몇권이 판매되었는지 조회
    (    책ID, 책제목, 총판매권수, 단가 컬럼 출력 )
    book : b_id, title
    danga : price
    panmai :p_su
    

   SELECT b_id, title , price, SUM( p_su ) 총판매권수
   FROM (    
            SELECT b.b_id, title, p_su , price
            FROM  book b JOIN  panmai p ON b.b_id = p.b_id
                         JOIN  danga  d  ON b.b_id = d.b_id
     ) t 
   GROUP BY  b_id, title, price

   
   (문제) 가장 판매권수가 많은 것 조회 -     a-1
     TOP-N 방식
     RANK 함수      1/1/1
     

SELECT t.*, ROWNUM     
FROM(      
               SELECT b_id, title , price, SUM( p_su ) 총판매권수
               FROM (    
                        SELECT b.b_id, title, p_su , price
                        FROM  book b JOIN  panmai p ON b.b_id = p.b_id
                                     JOIN  danga  d  ON b.b_id = d.b_id
                 ) t 
               GROUP BY  b_id, title, price
               ORDER BY 총판매권수 DESC
   ) t
WHErE  ROWNUM = 1   ;

     

   --  ORA-00933: SQL command not properly ended
   SELECT *
   FROM ( 
           SELECT b_id, title , price, SUM( p_su ) 총판매권수
               , RANK() OVER(  ORDER BY  SUM( p_su )  DESC )  순위
           FROM (    
                    SELECT b.b_id, title, p_su , price
                    FROM  book b JOIN  panmai p ON b.b_id = p.b_id
                                 JOIN  danga  d  ON b.b_id = d.b_id
             ) t 
           GROUP BY  b_id, title, price
   ) x
   WHERE 순위= 1;
   
   -- HAVING 순위 = 1;

   
   WITH
   SELECT 
   FROM 
   WHERE 
   GROUP BY
   HAVING
    
 1   1   a-1   00/10/10   10
2   2   a-1   00/03/04   20
3   1   b-1   22/10/12   13
4   4   c-1   00/07/07   5
5   4   d-1   22/10/12   31
6   6   f-1   22/10/12   21
7   7   a-1   22/10/12   26
8   6   a-1   22/10/12   17
9   6   b-1   22/10/12   5
10   7   a-2   00/10/10   15


  문제) book 테이블에서 판매가 된 적이 없는 책의 정보 조회
  문제) book 테이블에서 판매가 된 적이 있는 책의 정보 조회
   ( b_id, title, price  컬럼 출력 )
   
-- 총 책 : 9권
SELECT COUNT(*)   
FROM book;
-- 판매 테이블  : 6권
SELECT DISTINCT b_id
FROM panmai;
a-1
c-1
b-1
d-1
f-1
a-2

--
SELECT b.b_id, title, price
FROM book b JOIN danga  d ON b.b_id = d.b_id
WHERE b.b_id IN ( SELECT DISTINCT b_id FROM panmai  );
--
SELECT b.b_id, title, price
FROM book b JOIN danga  d ON b.b_id = d.b_id
WHERE b.b_id NOT IN ( SELECT DISTINCT b_id FROM panmai  );

 

WITH 
a AS (
    SELECT b.b_id, title, price
    FROM book b JOIN danga  d ON b.b_id = d.b_id
)
, b AS (
  SELECT DISTINCT b_id FROM panmai
)
SELECT a.b_id, title, price
FROM a JOIN b ON  a.b_id = b.b_id; -- EQUI JOIN ( INNER JOIN )

  5) SELF JOIN
  

  SELECT a.empno, a.ename, a.mgr , b.ename  -- , 직속상사ename
  FROM emp a  LEFT JOIN emp b ON a.mgr = b.empno;

SELF (LEFT) JOIN

  

 [문제9] 가장 많이 판매가 된 책의 정보를 조회( TOP-1 )
   ( 책ID, 제목, 가격, 총판매량 )
   
[문제10]  많이 판매가 된 책의 정보를 조회( TOP-3 )
   ( 책ID, 제목, 가격, 총판매량 ) 
  
  6) CROSS JOIN 
  7) ANTI  JOIN 
  8) SEMI  JOIN