create database DENANCONGTY
go
use DENANCONGTY
go
-- tao bang
create table PHONGBAN (
MAPHG varchar(4) primary key,
TENPHG varchar(100),
TRPHG varchar(8),
NG_NHAMCHUC datetime
)
create table NHANVIEN (
MANV varchar(8) primary key,
HONV varchar(50),
TENLOT varchar(50),
TENNV varchar(50),
NGSINH datetime,
PHAI varchar(5),
DCHI varchar(100),
LUONG float,
MA_NQL varchar(8) references NHANVIEN (MANV),
PHG varchar(4) references PHONGBAN (MAPHG)
)
alter table PHONGBAN
add constraint FK_PB_NV foreign key (TRPHG) references NHANVIEN (MANV)
create table DEAN (
MADA varchar(6) primary key,
PHONG varchar(4) references PHONGBAN (MAPHG),
TENDA varchar(50),
DDIEM_DA varchar(100),
)
create table PHANCONG (
MANV varchar(8) references NHANVIEN (MANV),
MADA varchar(6) references DEAN (MADA),
THOIGIAN float,
constraint PK_PC primary key (MANV, MADA)
)
create table THANNHAN (
MATN varchar(8),
MANV varchar(8) references NHANVIEN (MANV),
TENTN varchar(50),
PHAI varchar(5),
NGSINH datetime,
QUANHE varchar(30)
constraint PK_TN primary key (MATN, MANV)
)
create table DIADIEM_PHG (
MAPHG varchar(4) references PHONGBAN (MAPHG),
DIADIEM varchar (30),
constraint PK_DDPHG primary key (MAPHG, DIADIEM)
)
-- NHAP DU LIEU
insert into PHONGBAN values ('QL', 'Quan Ly', null, '2000-05-22')
insert into PHONGBAN values ('DH', 'Dieu Hanh', null, '2002-10-10')
insert into PHONGBAN values ('NC', 'Nghien Cuu', null, '2002-03-15')
insert into NHANVIEN values ('001', 'Vuong', 'Ngoc', 'Quyen', '1987-10-22', 'Nu', '450 Trung Vuong, Ha Noi', 3000000, null, 'QL')
insert into NHANVIEN values ('002', 'Nguyen', 'Thanh', 'Tung', '1985-01-09', 'Nam', '731 Tran Hung Dao, Q1, TpHCM', 2500000, '001', 'NC')
insert into NHANVIEN values ('003', 'Le', 'Thi', 'Nhan', '1980-12-18', 'Nu', '291 Ho Van Hue, QPN, TpHCM', 2500000, '001', 'DH')
insert into NHANVIEN values ('004', 'Dinh', 'Ba', 'Tien', '1982-01-09', 'Nam', '638 Nguyen Van Cu, Q5, TpHCM', 2200000, '002', 'NC')
insert into NHANVIEN values ('005', 'Bui', 'Thuy', 'Vu', '1982-07-19', 'Nam', '332 Nguyen Thai Hoc, Q1, TpHCM', 2200000, '003', 'DH')
insert into NHANVIEN values ('006', 'Nguyen', 'Manh', 'Hung', '1983-09-15', 'Nam', '978 Ba Ria, Vung Tau', 2000000, '002', 'NC')
insert into NHANVIEN values ('007', 'Tran', 'Thanh', 'Tam', '1985-07-31', 'Nu', '543 Mai Thi Luu, Q1, TpHCM', 2200000, '002', 'NC')
insert into NHANVIEN values ('008', 'Tran', 'Hong', 'Van', '1986-07-04', 'Nu', '980 Le Hong Phong, Q10, TpHCM', 1800000, '004', 'NC')
update PHONGBAN set TRPHG = '001' where MAPHG = 'QL'
update PHONGBAN set TRPHG = '003' where MAPHG = 'DH'
update PHONGBAN set TRPHG = '002' where MAPHG = 'NC'
insert into DIADIEM_PHG values ('NC', 'HANOI')
insert into DIADIEM_PHG values ('NC', 'TPHCM')
insert into DIADIEM_PHG values ('QL', 'TPHCM')
insert into DIADIEM_PHG values ('DH', 'HANOI')
insert into DIADIEM_PHG values ('DH', 'TPHCM')
insert into DIADIEM_PHG values ('DH', 'NHATRANG')
insert into THANNHAN values ('1', '003', 'Tran Minh Tien', 'Nam', '2010-12-11', 'Con')
insert into THANNHAN values ('2', '003', 'Tran Ngoc Linh', 'Nu', '2013-03-10', 'Con')
insert into THANNHAN values ('3', '003', 'Tran Minh Long', 'Nam', '1987-10-10', 'Vo Chong')
insert into THANNHAN values ('1', '001', 'Le Nhat Minh', 'Nam', '1985-04-27', 'Vo Chong')
insert into THANNHAN values ('1', '002', 'Le Hoai Thuong', 'Nu', '1980-12-05', 'Vo Chong')
insert into THANNHAN values ('1', '004', 'Le Phi Nhung', 'Nu', '1982-12-23', 'Vo Chong')
insert into THANNHAN values ('1', '005', 'Tran Thu Hong', 'Nu', '1988-04-11', 'Vo Chong')
insert into THANNHAN values ('2', '005', 'Nguyen Manh Tam', 'Nam', '2013-01-13', 'Con')
insert into DEAN values ('TH001', 'NC', 'Tin hoc hoa', 'HANOI')
insert into DEAN values ('TH002', 'NC', 'Nha truong thong tin', 'TPHCM')
insert into DEAN values ('DT001', 'DH', 'Tu dong hoa', 'NHATRANG')
insert into DEAN values ('DT002', 'DH', 'Dao tao 2', 'HANOI')
insert into PHANCONG values ('001', 'TH001', 30.0)
insert into PHANCONG values ('001', 'TH002', 12.5)
insert into PHANCONG values ('002', 'TH001', 10.0)
insert into PHANCONG values ('002', 'TH002', 10.0)
insert into PHANCONG values ('002', 'DT001', 10.0)
insert into PHANCONG values ('002', 'DT002', 10.0)
insert into PHANCONG values ('003', 'TH001', 37.5)
insert into PHANCONG values ('004', 'DT001', 22.5)
insert into PHANCONG values ('004', 'DT002', 10.0)
insert into PHANCONG values ('006', 'DT001', 30.5)
insert into PHANCONG values ('007', 'TH001', 20.0)
insert into PHANCONG values ('007', 'TH002', 10.0)
insert into PHANCONG values ('008', 'TH001', 10.0)
insert into PHANCONG values ('008', 'DT002', 12.5)
---CAU TRUY VAN
-- CAU 1
SELECT MANV,HONV,TENLOT,TENNV
FROM NHANVIEN
WHERE PHG='QL'
--CAU 2
SELECT MANV,HONV,TENLOT,TENNV,LUONG
FROM NHANVIEN
WHERE LUONG>2500000
--CAU 3
SELECT MANV,HONV,TENLOT,TENNV,LUONG,PHG
FROM NHANVIEN
WHERE (LUONG>250000 AND PHG='QL')OR(LUONG>3000000 AND PHG='NC')
--CAU 4
SELECT HONV+''+TENLOT+''+TENNV AS "HO VA TEN",DCHI
FROM NHANVIEN
WHERE DCHI LIKE '%TPHCM%'
-- CAU 5
SELECT HONV+''+TENLOT+''+TENNV AS 'HO VA TEN',NGSINH,DCHI
FROM NHANVIEN
WHERE HONV='Dinh' and TENLOT='Ba' and TENNV='Tien'
-- cau 6
select TENPHG,DIADIEM
FROM PHONGBAN, DIADIEM_PHG
WHERE PHONGBAN.MAPHG = DIADIEM_PHG.MAPHG
-- CAU 7
SELECT MAPHG,TENPHG,TRPHG,HONV+''+TENLOT+''+TENNV AS [HO VA TEN]
FROM PHONGBAN,NHANVIEN
WHERE PHONGBAN.TRPHG=NHANVIEN.MANV
-- cau8
select TENDA,MADA,DDIEM_DA,PHONG,TENPHG,MAPHG
TRPHG,NG_NHAMCHUC
FROM DEAN,PHONGBAN
WHERE DEAN.PHONG=PHONGBAN.MAPHG
-- CAU 9
SELECT HONV,TENLOT,TENNV,DCHI,TENPHG
FROM NHANVIEN,PHONGBAN
WHERE MAPHG=PHG AND TENPHG='Nghien cuu'
-- cau 10
select HONV,TENNV,TENTN
FROM NHANVIEN,THANNHAN
WHERE NHANVIEN.MANV=THANNHAN.MANV AND NHANVIEN.PHAI='Nam'
--CAU 11
SELECT MANV,TENNV
FROM NHANVIEN
WHERE MA_NQL IS NULL
-- CAU 12
SELECT HONV+''+TENLOT+''+TENNV AS [HO VA TEN], DCHI
FROM NHANVIEN
WHERE DCHI LIKE'%tphcm'
order by TENNV desc --asc:tang dan=>[mac dinh]
-- cau 14
select HONV+TENLOT+TENNV AS [HO VA TEN],DEAN.MADA,PHONG
FROM NHANVIEN,DEAN,PHANCONG
WHERE NHANVIEN.MANV=PHANCONG.MANV AND DEAN.MADA=PHANCONG.MADA AND DDIEM_DA='Ha noi'
-- CAU 15
SELECT MADA,MAPHG,HONV+TENLOT+TENNV AS [HO VA TEN],DCHI,NGSINH,DDIEM_DA
FROM DEAN,PHONGBAN,NHANVIEN
WHERE DEAN.PHONG=PHONGBAN.MAPHG AND PHONGBAN.TRPHG=NHANVIEN.MANV AND DEAN.DDIEM_DA LIKE '%tphcm'
-- cau 16
select NHANVIEN.HONV,NHANVIEN.TENLOT,NHANVIEN.TENNV,NHANVIEN.MA_NQL,NHANVIEN.HONV AS HO_NQL,NHANVIEN.TENLOT AS TENLOT_NQL, NHANVIEN.TENNV AS TEN_NQL,NHANVIEN.MANV
from NHANVIEN
where (NHANVIEN.MANV=NHANVIEN.MANV)
-- CAU 17
SELECT NHANVIEN.HONV,NHANVIEN.TENLOT,NHANVIEN.TENNV, NHANVIEN.HONV AS HO_TRPHG,NHANVIEN.TENLOT AS TENLOT_TRPHG,NHANVIEN.TENNV AS TEN_TRPHG,TENPHG
FROM NHANVIEN, PHONGBAN
WHERE PHONGBAN.MAPHG=NHANVIEN.PHG AND PHONGBAN.TRPHG=NHANVIEN.MANV
-- CAU 18
select HONV+' '+ TENLOT+' '+TENNV AS
[HO VA TEN],TENDA,PHONG,THOIGIAN
from NHANVIEN ,DEAN ,PHANCONG
where TENDA='SAN PHAM X' and MA_NQL=MA_NQL and MA_NQL=PHANCONG.MANV and PHG=5 and THOIGIAN >10
-- CAU 19
SELECT NHANVIEN.HONV,NHANVIEN.TENLOT,NHANVIEN.TENNV,NHANVIEN.PHG,NHANVIEN.HONV+''+NHANVIEN.TENLOT+''+NHANVIEN.TENNV AS 'HO TEN NGUOI QUAN LY'
FROM NHANVIEN,DEAN,PHANCONG