Quay tai lieu...

CREATE DATABASE QLSV
go

use QLSV
go
/*==============================================================*/
/* Table: KHOA */
/*==============================================================*/
create table KHOA (
MAKHOA varchar(4) not null,
TENKHOA varchar(100) not null,
NAMTHANHLAP int not null,

constraint PK_KHOA primary key (MAKHOA)
)
Go
//TAO BANG PHONG BANZ BO SUNG THEM CAC RANG BUOC//
alter table PHONGBAN add constraint FK_PHONGBAN_NHANVIEN foreign key (TRPHG) references NHANVIEN (MANV)
go
//XOA RANG BUOC TRONG BANG PHONG BAN//
alter table PHONGBAN drop FK_PHONGBAN_NHANVIEN
go

//DINH DANG NGAY……DATE-MONTH-YEAR//
set dateFormat dmy
go

//LENH UPDATE //
UPDATE NHANVIEN
SET MLUONG = MLUONG*1.1
(WHERE //TUY DE BAI)

//XOA NHAN VIEN CO HO NGUYEN
DELETE
FROM NHANVIEN
WHERE HONV = N’NGUYEN’

/*==============================================================*/
/* Input Table: PHONGBAN */
/*==============================================================*/

insert into PHONGBAN(MAPHG,TENPHG,TRPHG,NGNC) values('QL','Quan ly','001','22-05-2000')
go

-- Cau 1
SELECT MANV,HONV+' '+TENLOT+' '+TENNV AS HO_VA_TEN
FROM NHANVIEN
WHERE PHONG = 'NC'
go

-- Cau 2
SELECT MANV,HONV,TENLOT,TENNV,PHAI
FROM NHANVIEN
WHERE MLUONG > 3000000
go

-- Cau 3
SELECT TENNV,TENPHG
FROM NHANVIEN NV JOIN PHONGBAN PB ON NV.PHONG = PB.MAPHG
WHERE MLUONG BETWEEN 2000000 AND 3000000
go

-- Cau 4
SELECT HONV,TENLOT,TENNV,PHAI
FROM NHANVIEN
WHERE DCHI LIKE '%TpHCM'
go

-- Cau 5
SELECT NGSINH,DCHI
FROM NHANVIEN
WHERE HONV = 'Dinh' AND TENLOT = 'Ba' AND TENNV = 'Tien'
go

-- Cau 6
SELECT TENTN
FROM THANNHAN
WHERE (YEAR(NOW) - YEAR(NGSINH)) < '18' AND MANV = 'NV001' go --Cau 7 SELECT HONV, TENLOT, TENNV FROM NHANVIEN WHERE (YEAR(NOW) - YEAR(NGSINH))> '30' AND PHAI = 'NU'
go

--Cau 8
SELECT TENPHG, DIADIEM_PHG
FROM PHONGBAN
go

--Cau 9
SELECT HONV, TENLOT, TENNV
FROM PHONGBAN, NHANVIEN
WHERE MANV.TRPHG = MANV.PHG
go

--Cau 10
SELECT TENDA, MADA, DDIEM_DA, PHONG, TENPHG, MAPHG, TRPHG, NGNC
FROM PHANCONG, PHONGBAN
go

--Cau 11
SELECT TENNV, DCHI
FROM NHANVIEN, PHONGBAN
WHERE MANV.PHONG = 'NGHIEN CUU'