drop database if exists CsdlCongty;
create database CsdlCongty;
use CsdlCongty;

create table NHANVIEN (
    HoNV    varchar(15),
    Tenlot    varchar(15),
    TenNV    varchar(15),
    MaNV    char(9),
    NgSinh    datetime,
    DChi    varchar(30),
    Phai    varchar(3),
    Luong    real,
    MaNQL    char(9),
    Phg    int,
    constraint NHANVIEN_PK    primary key (MaNV),
    constraint NHANVIEN_FK_NHANVIEN foreign key (MaNQL) references NHANVIEN (MaNV),
    constraint NHANVIEN_DM_Phai check (Phai in ('F', 'f', 'M', 'm'))
);

create table PHONGBAN (
    TenPhg    varchar(15),
    MaPhg    int,
    TrPhg    char(9),
    NgNhanChuc    datetime,
    constraint PHONGBAN_PK primary key (MaPhg),
    constraint PHONGBAN_FK_NHANVIEN foreign key (TrPhg) references NHANVIEN (MaNV)
);

alter table NHANVIEN
add constraint NHANVIEN_FK_PHONGBAN foreign key (Phg) references PHONGBAN (MaPhg);

create table DIADIEM_PHG (
    MaPhg    int,
    Diadiem    varchar(15),
    constraint DIADIEM_PHG_PK primary key (MaPhg, Diadiem),
    constraint DIADIEM_PHG_FK_PHONGBAN foreign key (MaPhg) references PHONGBAN (MaPhg)
);

create table DUAN (
    TenDA    varchar(15),
    MaDA    int,
    Diadiem    varchar(15),
    Phong    int,
    constraint DUAN_PK primary key (MaDA),
    constraint DUAN_FK_PHONGBAN foreign key (Phong) references PHONGBAN (MaPhg)
);

create table PHANCONG (
    MaNV    char(9),
    MaDA    int,
    Thoigian    decimal(6,1),
    constraint PHANCONG_PK primary key (MaNV, MaDA),
    constraint PHANCONG_FK_NHANVIEN foreign key (MaNV) references NHANVIEN (MaNV),
    constraint PHANCONG_FK_DUAN foreign key (MaDA) references DUAN (MaDA)
);

create table THANNHAN (
    MaNV    char(9),
    TenTN    varchar(15),
    Phai    varchar(3),
    NgSinh    datetime,
    Quanhe    varchar(8),
    constraint THANNHAN_PK primary key (MaNV, TenTN),
    constraint THANNHAN_FK_NHANVIEN foreign key (MaNV) references NHANVIEN (MaNV),
    constraint THANNHAN_DM_Phai check (Phai in ('M', 'm', 'F', 'f'))
);

-- chen du lieu PHONGBAN
insert into PHONGBAN values ('Nghien cuu',5,null,null);
insert into PHONGBAN values ('Dieu hanh',4,null,null);
insert into PHONGBAN values ('Quan ly',1,null,null);

-- chen du lieu NHANVIEN
insert into NHANVIEN values ('Le','Van','Bo','888665555','1937-11-10','450 Trung Vuong, Ha Noi','M',55000,null,1);
insert into NHANVIEN values ('Phan','Van','Nghia','333445555','1955-12-08','638 Nguyen Van Cu, Q5, TpHCM','M',40000,'888665555',5);
insert into NHANVIEN values ('Nguyen','Bao','Hung','123456789','1965-01-09','731 Tran Hung Dao, Q1, TpHCM','M',30000,'333445555',5);
insert into NHANVIEN values ('Tran',null,'Nam','666884444','1962-09-15','975 Ba Ria Vung Tau','M',38000,'333445555',5);
insert into NHANVIEN values ('Hoang','Kim','Yen','453453453','1972-07-31','543 Mai Thi Luu, Q1, TpHCM','F',25000,'333445555',5);
insert into NHANVIEN values ('Du','Thi','Hau','987654321','1951-06-20','291 Ho Van Hue, QPN, TpHCM','F',43000,'888665555',4);
insert into NHANVIEN values ('Au',null,'Vuong','999887777','1968-07-19','332 Nguyen Thai Hoc, Q1, TpHCM','F',25000,'987654321',4);
insert into NHANVIEN values ('Nguyen','Van','Giap','987987987','1969-03-09','980 Le Hong Phong, Q10, TpHCM','M',25000,'987654321',4);

-- chinh sua du lieu PHONGBAN
update PHONGBAN
set TrPhg='888665555',NgNhanChuc='1981-06-19'
where MaPhg=1;
update PHONGBAN
set TrPhg='987987987',NgNhanChuc='1995-01-01'
where MaPhg=4;
update PHONGBAN
set TrPhg='333445555',NgNhanChuc='1988-05-22'
where MaPhg=5;

-- chen du lieu DIADIEM_PHG
insert into DIADIEM_PHG values (1,'Phu Nhuan');
insert into DIADIEM_PHG values (4,'Go Vap');
insert into DIADIEM_PHG values (5,'Tan Binh');
insert into DIADIEM_PHG values (5,'Phu Nhuan');
insert into DIADIEM_PHG values (5,'Thu Duc');

-- chen du lieu DUAN
insert into DUAN values ('San pham X',1,'Tan Binh',5);
insert into DUAN values ('San pham Y',2,'Thu Duc',5);
insert into DUAN values ('San pham Z',3,'Phu Nhuan',5);
insert into DUAN values ('Tin hoc hoa',10,'Go Vap',4);
insert into DUAN values ('Tai to chuc',20,'Phu Nhuan',1);
insert into DUAN values ('Phuc loi',30,'Go Vap',4);

-- chen du lieu PHANCONG
insert into PHANCONG values ('123456789',1,32.5);
insert into PHANCONG values ('123456789',2,7.5);
insert into PHANCONG values ('666884444',3,40);
insert into PHANCONG values ('453453453',1,20);
insert into PHANCONG values ('453453453',2,20);
insert into PHANCONG values ('333445555',2,10);
insert into PHANCONG values ('333445555',3,10);
insert into PHANCONG values ('333445555',10,10);
insert into PHANCONG values ('333445555',20,10);
insert into PHANCONG values ('999887777',30,30);
insert into PHANCONG values ('999887777',10,10);
insert into PHANCONG values ('987987987',10,35);
insert into PHANCONG values ('987987987',30,5);
insert into PHANCONG values ('987654321',30,20);
insert into PHANCONG values ('987654321',20,15);
insert into PHANCONG values ('888665555',20,null);

-- chen du lieu THANNHAN
insert into THANNHAN values ('333445555','Anh','F','1986-04-05','Con gai');
insert into THANNHAN values ('333445555','The','M','1983-10-25','Con trai');
insert into THANNHAN values ('333445555','Loi','F','1958-05-03','Vo');
insert into THANNHAN values ('987654321','An','M','1942-02-28','Chong');
insert into THANNHAN values ('123456789','Minh','M','1988-01-01','Con trai');
insert into THANNHAN values ('123456789','Anh','F','1988-12-30','Con gai');
insert into THANNHAN values ('123456789','Yen','F','1967-05-05','Vo');

-- Tim nhan vien lam viec trong phong so 4

select *
from NHANVIEN
where Phg = 4

-- Cho biet ho ten gioi tinh va muc luong cua nhan vien
select HoNV,TenNV,Phai,Luong
from NHANVIEN

-- Cho biet ten cac truong phong
select HoNV,TenNV
from NHANVIEN,PHONGBAN
where PHONGBAN.TrPhg=NHANVIEN.MaNV

-- phep toan tap hop
(select MaNV as MaNV1
from NHANVIEN
where MaNV=1)
union all
(select MaNV as MANV2
from NHANVIEN
)

--Dat ten
select count(*) as 'Tong so nhan vien'
from NHANVIEN

--Dat ten cho bang
select NV.HoNV,NV.TenNV,PB.TenPhg
from NHANVIEN as NV,PHONGBAN as PB
where PB.MaPhg = NV.Phg

-- PHEP TOAN SO HOC
select 1.1*Sum(Luong) as 'Luong moi'
from NHANVIEN

--Neu khong nhan voi 1.1 thi
select sum(Luong) as 'Luong moi'
from NHANVIEN

--Ap dung cho kieu ngay gio

select NgNhanChuc + 150 as 'Cong ngay'
from PHONGBAN

--Phep so sanh va luan ly

select*
from NHANVIEN
where (Luong>=30000)AND(Luong<=40000)AND Phg=5

--Phep toan so sanh chuoi

select MaNV,HoNV,TenNV
from NHANVIEN
where HoNV like 'Nguyen%'

--Tim nhan vien ho Nguyen_

select MaNV,HoNV,TenNV
from NHANVIEN
where HoNV like 'Nguyen\_%'escape'\'

--Khu cac dong giong nhau

select Luong
from NHANVIEN

--Ta co the dung nhu sau:

select distinct Luong
from NHANVIEN

-- Gom nhom cac bo

-- voi moi phong cho biet so du an phong do dieu phoi.

select phong, count(MADA)as 'so du an'
from DUAN
Group by Phong

--Cho biet ma so ten du an va so nhan vien tham
gia doi voi nhung du an co nhieu hon hai nhan vien

select DA.MADA,DA.TenDA,count(*) as 'so nhan vien'
from DUAN as DA,PHANCONG as TG
where DA.MADA = TG.MADA
Group by DA.MaDA,DA.TenDA
Having count(*)>2

--Sap xep tang giam

select MaNV,MADA
from PHANCONG
order by MANV,MADA DESC

-- So sanh voi NULL

--Tim nhan vien khong co nguoi quan ly:

select MANV,HONV,TENNV
from NHANVIEN
where MaNQL is null

--Tìm nhân viên có ngu?i giám sát:

select MANV,HONV,TENNV
from NHANVIEN
where MaNQL is not null

--So sánh t?p h?p:

select*
from NHANVIEN
where Phg in(2,5)
----------------------------

select*
from NHANVIEN
where phg =(
        select MaPhg
        from PHONGBAN
        where TenPhg='Nghien cuu'
)
--Cho bi?t các nhân viên
không tham gia các d? án do phòng s? 5 qu?n lý:
 
select MANV
from PHANCONG
where MADA not in(
        select MADA
        from DUAN
        where Phong =5
)

---

Mai Xuan Canh @ 21:32 17/06/2010
Số lượt xem: 281