1.
create table 부서(
부서번호 nchar(2) constraint PK_부서번호 primary key,
부서명 nchar(10)
)
select * from 부서
create table 사원(
사번 nchar(4) primary key,
이름 nchar(10),
주민등록번호 nchar(10),
성별 nchar(1) check(성별 in ('남','여')),
자격증갯수 smallint,
부서번호 nchar(2) foreign key references 부서(부서번호),
지원부서번호 nchar(2)
)
create table 자격증(
사번 nchar(4) not null,
일련번호 smallint not null,
자격증id nchar(10) not null,
자격증명 nchar(20) not null,
취득일자 smalldatetime not null,
유효일자 smalldatetime not null
)
2. 자격증 테ㅐ이블에 PRIMARY KEY PK_사번_일련번호를 추가하시오.
alter table 자격증
alter column 일련번호 nchar(4) not null
alter table 자격증
alter column 사번 nchar(4) not null
위의 2개를 먼저 해야함
alter table 자격증
add constraint PK_사번_일련번호
primary key(사번,일련번호)
select * from 자격증
3. 사원테이블의 지원부서번호가 부서테이블의 부서번호를 참조하는 외래키 FK_부서번호를 추가하시오.
단, 이미 입력된 지원부서번호가 제약조건에 위배되더라도 무시하고 넘어가고자 한다.
alter table 사원 with nocheck
add constraint FK_부서번호 foreign key(지원부서번호)
references 부서(부서번호)
4. 자격증 테이블의 사번이 사원테이블의 사번을 참조하는 외래키 FK_지원부서번호를 추가하시오.
단, 사원테이블의 사원데이터가 삭제되면 자격증테이블의 해당 사원의 자격증 데이터도 삭제된다.
alter table 자격증
add constraint FK_지원부서번호
foreign key (사번)
references 사원(사번)
on delete cascade
select * from 사원
5. 사원테이블에 주민등록번호에 UNIQUE 제약 조건 UK_주민등록번호를 추가하시오.
alter table 사원
add constraint UK_주민등록번호 UNIQUE(주민등록번호)
6. 사원테이블의 이름이 NOT NULL이 되도록 수정하시오.
alter table 사원
alter column 이름 nchar(10) not null
7. 사원테이블의 자격증개수에 DEFAULT 제약 조건 DF_자격증갯수를 추가하시오.
alter table 사원
add constraint DF_자격증갯수
default 0 for 자격증갯수
8 자격증테이블의 취득일자가 유효일자보다 작도록 CHECK 제약조건 CK_취득일자를 추가하시오.
alter table 자격증
add constraint CK_취득일자
check(취득일자 < 유효일자)
9번 사원테이블의 주민등록번호의 크기를 +4 증가하시오
alter table 사원 alter column 주민등록번호 nchar(14) null
alter table 사원
drop constraint uk_주민등록번호
alter table 사원
add constraint uk_주민등록번호 unique(주민등록번호)
10번 자격증이 2개 이상인 사원의 사번, 이름, 자격증명만 검색하는 뷰 MYVIEW01을
만드시오.
create view MYVIEW01
as
select a.사번,이름,b.자격증명
from 사원 a join 자격증 b on a.사번 = b.사번
where a.자격증갯수 >=2
11번 모든 사원의 사번, 이름, 부서번호, 부서명, 지원부서번호, 지원부서명을 출력하는데
필요한 뷰 MYVIEW02을 만들고 그 뷰를 이용하여 출력하는 SELECT문을 쓰시오.
create view MYVIEW02
as
select 사번,이름,b.부서번호,부서명,지원부서번호,지원부서명
from 사원 a join 부서 b on a.부서번호 = b.부서번호