[easyupclass] 초심자를 위한 데이터베이스 및 빅데이터 플랫폼 구축과정(SQL, 하둡) 22

<제약>

constraint 제약 이름 primary key

1. PRIMARY KEY

constraint 제약 이름 primary key

2. UNIQUE

: 중복값 입력 제약

3. NOT NULL

4. FOREIGN KEY

: 부서 테이블에 있는 부서번호만 사원테이블에 입력

 

<WITH AS>

: 하나의 SQL에서 반복 사용하는 쿼리 문이 있을 때

 

-- 제약조건을 포함한 테이블 생성
create table dept5
( deptno number(10),
  dname varchar2(14) constraint dept5_dname_nn not null,
  loc varchar2(10) );
  
-- 만들어진 테이블에 제약조건 생성
create table dept6
( deptno number(10),
  dname varchar2(14),
  loc varchar2(10) );
  
 alter table dept6
 modify loc constraint dept6_loc_nn not null;
 -- add deptno constraint dept6_deptno primary key/unique ; 
 
 
 -- check 제약 : minimun / maximum 
 create table emp5
 ( empno number(10),
   ename varchar2(20),
   sal number(10) constraint emp6_sal_ck CHECK (sal BETWEEN0 AND 6000) );
insert into emp5 values (7782, 'CLARK', 2450);
commit;
-- check 제약조건 위배 
update emp5
set sal = 9000
where ename='CLARK';
insert into emp6 values (7566, 'ADAMS', 9000);

-- 제약 drop
alter table emp5
drop constraint emp5_sal_ck;

-- 제약 생성
alter table emp5
add constraint emp5_sal_ck CHECK (sal between 0 and 9000);


-- foreign key
create table dept6
( deptno number(10) constraint dept6_deptno_pk primary key,
  dname varchar2(14),
  loc varchar2(10) );

create table emp6
( empno number(10),
  ename varchar2(20),
  sal number(10),
  deptno number(10) constraint emp6_deptno_fk references dept6(deptno) );

-- 테이블별 제약 조건 확인
select a.constraint_name, a.constraint_type, b.column_name
from user_constraints a, user_cons_columns b
where a.table_name in ('DEPT6', 'EMP6');

insert into dept6 
select deptno, dname, loc
from dept;
insert into emp6
select empno, ename, sal, deptno
from emp;

-- 무결성 제약조건 위배
insert into emp6 values (1111, 'JACK' 4000, 80);

-- foreign key가 참조하고 있으면 primary key 제약 삭제 불가능
-- 마지막에 cascade를 붙이면 제약 두개 모두 삭제
alter table dept6
drop constraint dept6_deptno_pk;


-- WITH ~ AS : 하나의 테이블처럼 구성
select job, sum(sal) as total
from emp 
group by job
having sum(sal) > ( select avg(sum(sal))
                    from emp 
                    group by job );

WITH job_sumsal AS (
 select job, sum(sal) as total
 from emp
 group by job)
select job, total
from job_sumsal
where total > ( select avg(total)
				from job_sumsal);


-- subquery factoring
with job_sumsal as (
select job, sum(sal) total
from emp
group by job
)
, deptno_sumsal as (
select deptno, sum(sal) total
from emp
group by deptno
having sum(sal) > ( select avg(total) + 3000
					from job_sumsal )
                    
select deptno, total
from deptno_sumsal;