Bài tập CSDL ...insert into...

create database QLDA

create table NHANVIEN
(
HONV varchar(15),
TENLOT varchar(15),
TENNV varchar(15),
MANV varchar(9) not null,
NGAYSINH datetime,
DCHI varchar(30),
PHAI varchar(3),
LUONG float,
MA_NQL varchar(9),
PHG INT,
check (PHAI='Nam' or PHAI='Nu'),
constraint PK_NV primary key (MANV),
)
create table DEAN
(
TENDEAN varchar(15),
MADA int not  null,
DDIEM_DA varchar(15),
PHONG int,
constraint PK_DA primary key (MADA),
)
create table CONGVIEC
(
MADA int not null,
STT int not null,
TEN_CONG_VIEC varchar(50),
constraint PK_CV primary key (MADA,STT),
)
create table PHONGBAN
(
TENPHG varchar(15),
MAPHG int not null,
TRPHG varchar(9),
NG_NHANCHUC datetime,
constraint PK_PB primary key (MAPHG),
)
create table PHANCONG
(
MA_NVIEN varchar(9) not null,
MADA int not null,
STT int not null,   
THOIGIAN float,
constraint PK_PC primary key (MA_NVIEN,MADA,STT),
)
create table THANNHAN
(
MA_NVIEN varchar(9) not null,
TENTN varchar(15) not null,
PHAI varchar(3),
NGSINH datetime,
QUANHE varchar(15),
check (PHAI='Nam' or PHAI='Nu'),
constraint PK_TN primary key (MA_NVIEN,TENTN),
)
create table DIADIEM_PHG
(
MAPHG int not null,
DIADIEM varchar(15) not null,
constraint PK_MP primary key (MAPHG,DIADIEM),
)
alter table NHANVIEN
add constraint NV_NV foreign key (MA_NQL) references NHANVIEN (MANV),
constraint NV_PB foreign key (PHG) references PHONGBAN (MAPHG)

alter table DEAN
add constraint DA_PB foreign key (PHONG) references PHONGBAN (MAPHG)

alter table CONGVIEC
add constraint CV_DA foreign key (MADA) references DEAN (MADA)

alter table PHONGBAN
add constraint PB_NV foreign key (TRPHG) references NHANVIEN (MANV)

alter table PHANCONG
add constraint PC_NV foreign key (MA_NVIEN) references NHANVIEN (MANV)

alter table DIADIEM_PHG
add constraint DDP_PB foreign key (MAPHG) references PHONGBAN (MAPHG)

alter table THANNHAN
add constraint TN_NV foreign key (MA_NVIEN) references NHANVIEN (MANV)

insert into NHANVIEN (HONV,TENLOT,TENNV,MANV,NGAYSINH,DCHI,PHAI,LUONG) values('DINH','BA','TIEN','009','11/02/1960','119 CONG HUYNH- TPHCM','nam',30000)
insert into NHANVIEN (HONV,TENLOT,TENNV,MANV,NGAYSINH,DCHI,PHAI,LUONG) values('NGUYEN','THANH','TUNG','005','02/08/1962','222 NGUYEN VAN- CU TPHCM','nam',40000)
insert into NHANVIEN (HONV,TENLOT,TENNV,MANV,NGAYSINH,DCHI,PHAI,LUONG) values('BUI','NGOC','HANG','007','11/03/1954','332 NGUYEN THAI HOC- TPHCM','nam',25000)
insert into NHANVIEN (HONV,TENLOT,TENNV,MANV,NGAYSINH,DCHI,PHAI,LUONG) values('LE','QUYNH','NHU','001','01/02/1967','291 HO VAN HUE- TPHCM','nu',43000)
insert into NHANVIEN (HONV,TENLOT,TENNV,MANV,NGAYSINH,DCHI,PHAI,LUONG) values('NGUYEN','MANH','HUNG','004','04/03/1967','95 BA RIA- VUNG TAU','nam',38000)
insert into NHANVIEN (HONV,TENLOT,TENNV,MANV,NGAYSINH,DCHI,PHAI,LUONG) values('TRAN','THANH','TAM','003','04/05/1957','34 MAI THI LU- TPHCM','nam',25000)
insert into NHANVIEN (HONV,TENLOT,TENNV,MANV,NGAYSINH,DCHI,PHAI,LUONG) values('TRAN','HONG','QUANG','008','01/09/1967','80 LE HONG- PHONG TPHCM','nam',25000)
insert into NHANVIEN (HONV,TENLOT,TENNV,MANV,NGAYSINH,DCHI,PHAI,LUONG) values('PHAM','VAN','VINH','006','01/01/1965','45 TRUNG VUONG- HA NOI','nu',55000)

insert into PHONGBAN values('nghien cuu',5,'005','12/05/1978')
insert into PHONGBAN values('dieu hanh',4,'008','01/01/1985')
insert into PHONGBAN values('quan ly',1,'006','10/06/1971')

insert into DEAN values('san pham x',1,'VUNG TAU',5)
insert into DEAN values('san pham y',2,'NHA TRANG',5)
insert into DEAN values('san pham z',3,'TPHCM',5)
insert into DEAN values('tin hoa hoc',10,'HA NOI',4)
insert into DEAN values('cap quang',20,'TPHCM',1)
insert into DEAN values('dao tao',20,'HA NOI',4)

insert into THANNHAN values('005','TIRNH','nu','05/04/1976','con gai')
insert into THANNHAN values('005','KHANG','nam','02/10/1973','con trai')
insert into THANNHAN values('005','PHUONG','nu','03/03/1948','vo chong')
insert into THANNHAN values('001','MINH','nam','19/02/1932','vo chong')
insert into THANNHAN values('009','TIEN','nam','01/01/1978','con trai')
insert into THANNHAN values('009','CHAU','nu','10/12/1978','con gai')
insert into THANNHAN values('009','PHUONG','nu','05/05/1957','vo chong')

insert into DIADIEM_PHG values(1,'TPHCM')
insert into DIADIEM_PHG values(4,'HA NOI')
insert into DIADIEM_PHG values(5,'VUNG TAU')
insert into DIADIEM_PHG values(5,'NHA TRANG')
insert into DIADIEM_PHG values(5,'TPHCM')

insert into PHANCONG values('009',1,1,32)
insert into PHANCONG values('009',2,2,8)
insert into PHANCONG values('004',3,1,40)
insert into PHANCONG values('003',1,2,20.0)
insert into PHANCONG values('003',2,1,20.0)
insert into PHANCONG values('008',10,1,35)
insert into PHANCONG values('008',30,2,5)
insert into PHANCONG values('001',30,1,20)
insert into PHANCONG values('001',20,1,25)
insert into PHANCONG values('006',20,1,30)
insert into PHANCONG values('005',3,1,10)
insert into PHANCONG values('005',10,2,10)
insert into PHANCONG values('005',20,1,10)
insert into PHANCONG values('007',30,2,30)
insert into PHANCONG values('007',10,2,10)

insert into CONGVIEC values(1,1,'thiet ke san pham x')
insert into CONGVIEC values(1,2,'thu nghiem san pham x')
insert into CONGVIEC values(2,1,'san xuat san pham y')
insert into CONGVIEC values(2,2,'quang cao san pham y')
insert into CONGVIEC values(3,1,'khuyen mai san pham z')
insert into CONGVIEC values(10,1,'tin hoc hoa phong dan su')
insert into CONGVIEC values(10,2,'tin hoc hoa phong kinh doanh')
insert into CONGVIEC values(20,1,'lap dat cap quang')
insert into CONGVIEC values(30,1,'dao tao nhan vien marketing')
insert into CONGVIEC values(30,2,'dao tao nhan vien thiet ke')

update NHANVIEN set MA_NQL='005',PHG=5 where MANV='009'
update NHANVIEN set MA_NQL='006',PHG=5 where MANV='005'
update NHANVIEN set MA_NQL='001',PHG=4 where MANV='007'
update NHANVIEN set MA_NQL='006',PHG=4 where MANV='001'
update NHANVIEN set MA_NQL='005',PHG=5 where MANV='004'
update NHANVIEN set MA_NQL='005',PHG=5 where MANV='003'
update NHANVIEN set MA_NQL='001',PHG=4 where MANV='008'
update NHANVIEN set MA_NQL='001',PHG=1 where MANV='006'