Mục lục
BÀI TEST SỐ 1
Yêu cầu
Câu 1 – Tạo cơ sở dữ liệu sau
Cơ sở dữ liệu quản lý sinh viên có chứa 3 table. Viết lệnh tạo các table với các ràng buộc khóa chính, khóa ngoại được nêu trong tân từ. Kiểu dữ liệu của các Fields là tự chọn theo ngữ nghĩa.
SinhVien(MSSV, Lop, Ho, Ten, Ngaysinh, Nu)
Tân từ: Mỗi sinh viên có một mã số (MSSV) là số nguyên phân biệt, thuộc một lớp (Lop), có họ (HO) và tên (Ten) sinh viên, ngày sinh (Ngaysinh), thuộc phái nữ hay nam (Nu).
MonHoc(MSMon, TenMon)
Tân từ: Mỗi môn học có một mã số (MSMon) là một số nguyên phân biệt, có tên môn học (TenMon).
DiemThi(MSSV, MSMon, LanThi, Diem)
Tân từ: Mỗi sinh viên (MSSV) có thể học nhiều môn (MSMon). Mỗi môn học có thể thi nhiều lần (LanThi), mỗi lần thi được đánh số thứ tự từ 1 trở đi và ghi nhận điểm thi (Diem) của các lần thi đó.
Câu 2 – Khai báo bổ sung các ràng buộc sau:
- Lần thi (LanThi) trong table DiemThi có giá trị mặc định là 1
- Điểm thi (Diem) trong table DiemThi được chấm theo thang điểm 10
Câu 3 – Tạo View:
- Tạo view vwLanThiCuoi dùng liêt kê danh sách lần thi cuối cùng của các sinh viên gồm: Mã số sinh viên, mã số môn học, lần thi cuối cùng của môn học (ví dụ sinh viên A thi môn học C ba lần thì lần thi cuối cùng là 3).
- Tạo view vwDiemThiCuoi dùng liêt kê danh sách sinh viên gồm: Mã số sinh viên, mã số môn học, lần thi cuối cùng của môn học (ví dụ sinh viên A thi môn học M ba lần thì lần thi cuối cùng là 3) và điểm của lần thi cuối cùng đó.
Câu 4 – Tạo trigger insert
Tạo trigger Insert cho table DiemThi dùng điền tự động số thứ tự lần thi khi thêm điểm thi một môn học của một sinh viên. Ví dụ sinh viên A đã thi môn học M hai lần thì lần thi mới thêm vào phải là 3.
Câu 5 – Viết hàm
Viết thủ tục hoặc hàm liệt kê kết quả thi các môn của một sinh viên khi biết mã số của sinh viên (MSSV) gồm các thông tin: mã số môn học, lần thi, điểm thi. Trong đó, mã số sinh viên là giá trị input.
Hướng dẫn
-- Bài test 1 - Thực hành SQL Server
use master
go
if exists(select * from sys.databases where name='De1')
drop database De1
go
create database De1
go
use De1
go
--------------------
--Câu 1 : Tạo TABLE
create table SinhVien
( MSSV int identity(1,1) primary key,
Lop varchar(10),
Ho varchar(10),
Ten varchar(30),
NgaySinh datetime,
Nu bit
)
--------------------
create table MonHoc
( MSMon int identity(1,1) primary key,
TenMon varchar(30)
)
--------------------
create table DiemThi
( MSSV int,
MSMon int,
LanThi int,
Diem int,
primary key(MSSV,MSMon,LanThi),
foreign key(MSSV) references SinhVien(MSSV),
foreign key(MSMon) references MonHoc(MSMon),
)
--Câu 2 : Bổ sung ràng buộc
go
Alter table DiemThi ADD
default 1 for LanThi,
check(Diem between 0 and 10)
--------------------
insert into SinhVien values ('07ct112','Nguyen','Hoang Long','11/10/1989','True')
insert into SinhVien values ('07ct112','Le','Ngoc Nam','11/10/1989','True')
insert into SinhVien values ('07ct112','Le','Thanh Phuc','12/25/1989','True')
--------------------
insert into MonHoc values ('SQL 2005')
insert into MonHoc values ('TTHCM')
--------------------
insert into DiemThi values(1,1,1,10)
--------------------
insert into DiemThi values(2,1,1,7)
insert into DiemThi values(2,1,2,9)
--------------------
insert into DiemThi values(3,1,1,8)
insert into DiemThi values(3,2,1,2)
insert into DiemThi values(3,2,2,6)
insert into DiemThi values(3,2,3,10)
--------------------
--Câu 3.1) Tạo view vwLanThiCuoi
go
create view vwLanThiCuoi
as
select MSSV, MSMon, LanThi=max(LanThi)
from DiemThi
group by MSSV, MSMon
--------------------
--Câu 3.2) Tạo view vwDiemThiCuoi
go
create view vwDiemThiCuoi
as
with BangTam as(select MSSV, MSMon, LanThiMax=max(LanThi)from DiemThi
group by MSSV, MSMon)
select D.*
from BangTam B join DiemThi D on (B.MSSV=D.MSSV and B.MSMon=D.MSMon and B.LanThiMAX =D.LanThi)
--------------------
--Câu 4 : Tạo trigger
GO
create trigger itrg_AutoLanThi on DiemThi
for INSERT
AS
declare @MSSV int
declare @MSMon int
select @MSSV = MSSV,@MSMon = MSMon from inserted
if NOT EXISTS (select * from DiemThi where MSSV=@MSSV and MSMon=@MSMon)
begin
print'MSSV them vao khong ton tai'
rollback tran
return
end
update DiemThi set DiemThi.LanThi = DiemThi.LanThi + 1
from INSERTED I where I.MSSV=@MSSV and I.MSMon=@MSMon
--------------------
--Câu 5 : Tạo thủ tục
go
create proc ThongTinSV(@MSSV int=1)
as
Begin
select MSMon,LanThi,Diem
from DiemThi
where MSSV = @MSSV
End
--------------------
go
exec ThongTinSV 3
go
insert into DiemThi(MSSV,MSMon,Diem) values(4,1,7)BÀI TEST SỐ 2
Yêu cầu
Câu 1 – Tạo cơ sở dữ liệu
Cơ sở dữ liệu quản lý các trận đấu bóng đá tại một sân vận động có chứa 3 table. Viết lệnh tạo các table với các ràng buộc khóa chính, khóa ngoại được nêu trong tân từ. Kiểu dữ liệu của các Fields là tự chọn theo ngữ nghĩa.
Doi(MSDoi, TenDoi, Phai)
Tân từ: Mỗi đội có 1 mã số (MSDoi) là một số nguyên phân biệt; có tên (TenDoi) và thuộc phái nam hay nữ (Phai).
TranDau(MSTD, NgayTD, GioBD, GioKT)
Tân từ: Mỗi trận đấu có 1 mã số (MSTD) là một số nguyên phân biệt với những trận đấu khác, diễn ra vào 1 ngày (NgayTD), bắt đầu ở một giờ (GioBD) và dự kiến kết thúc tại một giờ (GioKT).
DoiThiDau(MSTD, MSDoi)
Tân từ: Lưu trữ danh sách các đội (MSDoi) tham gia thi đấu trong các trận đấu (MSTD). Biết rằng, mỗi trận đấu là một cuộc gặp gỡ giữa 2 đội và cả 2 đội phải thuộc cùng một phái.
Câu 2 – Viết ràng buộc
Viết lệnh khai báo bổ sung các ràng buộc sau:
- Giờ bắt đầu thi đấu (GioTD) phải nhỏ hơn giờ kết thúc (GioKT) trận đấu.
- Hai trận đấu trong table TranDaudiễn ra trong cùng một ngày (NgayTD) thì không bắt đầu ở cùng một giờ thi đấu (GioTD).
Câu 3 – Tạo view
- Tạo view vwDoiChuaThiDaudùng liêt kê danh sách các đội chưa có mã số đội trong table DoiThiDau gồm: Mã số đội, tên đội, phái.
- Tạo view vwSoTranDaudùng thống kê số trận đấu diễn ra trong từng ngày thi đấu với các thông tin: Ngày thi đấu và số trận đấu trong ngày.
Câu 4 – Tạo trigger
Tạo insert trigger cho table DoiThiDau dùng kiểm tra ràng buộc mỗi trận đấu chỉ là một cuộc gặp gỡ giữa 2 đội và cả 2 đội phải thuộc cùng một phái.
Câu 5 – Tạo hàm
Tạo thủ tục hoặc hàm hiển thị thông tin 2 đội tham gia trong một trận đấu khi biết mã số trận đấu. Nếu mã trân đấu không có trong table DoiThiDau thì hiện thị thông báo lỗi. Trong đó, mã số trận đấu (MSTD) là giá trị input.
Hướng dẫn
-- Bài test số 2 - Thực hành SQL server
-- Câu 1
use master
go
if exists(select * from sys.databases where name='De2')
drop database De2
go
create database De2
go
use De2
go
--------------------
create table Doi
( MSDoi int primary key,
TenDoi varchar(40),
Phai bit
)
--------------------
create table TranDau
( MSTD int primary key,
NgayTD datetime,
GioBD smalldatetime,
GioKT smalldatetime ,
)
--------------------
create table DoiThiDau
( MSTD int references TRANDAU(MSTD),
MSDOI int,
primary key(MSTD,MSDoi),
foreign key(MSDOI) references Doi(MSDOI),
)
-- Câu 2
go
Alter table TranDau ADD
check(GioBD < GioKT),
unique(GioBD,NgayTD)
--------------------
go
insert into Doi values (1,'Hoang Anh Gia Lai','True')
insert into Doi values (2,'Hoang Anh Gia Lai','False')
insert into Doi values (3,'Dong Tam Long An','True')
insert into Doi values (4,'SHB Da Nang','True')
insert into Doi values (5,'SHB Da Nang','False')
insert into Doi values (6,'Dong Nai','False')
insert into TranDau values (1,'10/10/1998','10/10/1998 2:00:00 PM','10/10/1998 4:00:00 PM')
insert into TranDau values (2,'10/10/1998','10/10/1998 5:00:00 PM','10/10/1998 7:00:00 PM')
insert into TranDau values (3,'10/11/1998','10/10/1998 7:00:00 PM','10/10/1998 9:00:00 PM')
insert into TranDau values (4,'10/12/1998','10/10/1998 6:15:00 AM','10/10/1998 8:15:00 AM')
insert into TranDau values (5,'10/12/1998','10/10/1998 3:30:00 PM','10/10/1998 5:30:00 PM')
insert into TranDau values (6,'10/12/1998','10/10/1998 7:00:00 PM','10/10/1998 9:00:00 PM')
insert into DoiThiDau values (1,1)
insert into DoiThiDau values (1,3)
insert into DoiThiDau values (2,1)
insert into DoiThiDau values (2,4)
insert into DoiThiDau values (3,2)
insert into DoiThiDau values (3,5)
------------------
-- Câu 3
go
create view vwDoiChuaThiDau
as
select * from Doi A
where A.MSDoi NOT IN (select B.MSDoi from DoiThiDau B)
--------------------
go
create view vwSoTranDau
as
select NgayTD, SoTD=count(MSTD)
from TranDau
group by NgayTD
--------------------
go
-- Câu 4,5
create proc ThongTinTD(@MSTD int)
as
if NOT exists (select * from DoiThiDau where MSTD = @MSTD)
Begin
print'Tran dau khong ton tai'
End
else
Begin
select MSTD,DoiThiDau.MSDoi,TenDoi,Phai
from DoiThiDau join Doi on DoiThiDau.MSDoi = Doi.MSDoi
where MSTD = @MSTD
End
--------------------
go
exec ThongTinTD 2BÀI TEST SỐ 3
Yêu cầu
Câu 1 – Tạo bảng
Cơ sở dữ liệu quản lý hóa đơn bán hàng có chứa 3 table. Viết lệnh tạo các table với các ràng buộc khóa chính, khóa ngoại được nêu trong tân từ. Kiểu dữ liệu của các Fields là tự chọn theo ngữ nghĩa.
MatHang(MSMH, TenMH, DonGia, SoTon)
Tân từ: Mỗi mặt hàng có một mã số (MSMH) là một số nguyên phân biệt, có tên mặt hàng (TenMH), đơn giá (DonGia) bán mới nhất của mặt hàng đó và số lượng hàng tồn kho (SoTon).
HoaDon(MSHD, NgayLap)
Tân từ: Mỗi hóa đơn có một mã số phân biệt (MSHD), ngày lập hóa đơn (NgayLap)
CTHD(MSHD, MSMH, SoLuong, DonGiaHD)
Tân từ: Mỗi hóa đơn (MSHD) ghi một hoặc nhiều mặt hàng (MSMH) cùng với số lượng (SoLuong) và đơn giá bán tại thời điểm ghi hóa đơn (DonGiaHD).
Câu 2 – Tạo ràng buộc
Khai báo bổ sung các ràng buộc sau:
- Số tồn (SoTon) trong table MatHang có giá trị mặc định (Default) bằng 0.
- Tên mặt hàng trong table MatHang có giá trị phân biệt giữa các mặt hàng.
Câu 3 – Tạo view
- Tạo view “vwTienHD” dùng hiển thị tiền bán trên từng hóa đơn gồm các thông tin: mã số hóa đơn, ngày lập, tiền hóa đơn bằng tổng tiền bán từng mặt hàng ghi trên mỗi hóa đơn.
- Tạo view “vwHangKhongBanDuoc” dùng liêt kê danh sách các mặt hàng không bán được trong tháng hiện hành.
Câu 4 – Tạo trigger
Xây dựng Insert trigger cho CTHD thực hiện yêu cầu: khi thêm một chi tiết hóa đơn phải cập nhật lại số lượng tồn (SoTon) của mặt hàng tương ứng.
Câu 5 – Viết hàm
Viết thủ tục hoặc hàm nhận 2 giá trị input kiểu số nguyên là tháng và năm. Hãy liệt kê doanh thu của từng mặt hàng gồm các thông tin: mã số mặt hàng, doanh thu mặt hàng trong :
- Một năm nếu tháng là Null và năm khác Null
- Một tháng nếu tháng và năm khác Null
Hướng dẫn
-- Bài test số 3 - Thực hành về sql server
-- Câu 1
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'De3')
BEGIN
DROP DATABASE De3
END
GO
CREATE DATABASE De3
GO
USE De3
GO
--------------------
CREATE TABLE MATHANG
( MSMH int PRIMARY KEY ,
TENMH varchar(100),
DONGIA money DEFAULT 0 CHECK (DONGIA >=0),
SOTON int
)
--------------------
CREATE TABLE HOADON
( MSHD int PRIMARY KEY,
NGAYLAPHD datetime DEFAULT GETDATE() NOT NULL,
)
--------------------
CREATE TABLE CTHD
( MSHD int references HOADON,
MSMH int references MATHANG,
SOLUONG Smallint DEFAULT 0 CHECK ( SOLUONG > 0),
DONGIABAN MONEY DEFAULT 0 CHECK ( DONGIABAN > 0),
PRIMARY KEY (MSHD,MSMH),
FOREIGN KEY(MSHD) references HOADON(MSHD),
FOREIGN KEY(MSMH) references MATHANG(MSMH)
)
-- Câu 2
GO
ALTER TABLE MATHANG ADD
default 0 for SOTON,
unique(TENMH)
--------------------
INSERT INTO MATHANG VALUES (1,'Ruou',230.5,201)
INSERT INTO MATHANG VALUES (2,'Gia vi',40,82)
INSERT INTO MATHANG VALUES (3,'Banh kem',10,0)
INSERT INTO MATHANG VALUES (4,'Bo',38,30)
INSERT INTO MATHANG VALUES (5,'Banh mi',8,435)
INSERT INTO MATHANG VALUES (6,'Nem',23.8,97)
INSERT INTO MATHANG VALUES (7,'Tao',5,33)
INSERT INTO MATHANG VALUES (8,'Ca hop',62.5,100)
INSERT INTO MATHANG VALUES (9,'Cam',11,50)
--------------------
INSERT INTO HOADON VALUES (10144,'3/30/2003')
INSERT INTO HOADON VALUES (10150,'4/9/2003')
INSERT INTO HOADON VALUES (10156,'4/20/2003')
INSERT INTO HOADON VALUES (10158,'4/22/2003')
INSERT INTO HOADON VALUES (10175,'5/19/2003')
INSERT INTO HOADON VALUES (10177,'5/21/2003')
INSERT INTO HOADON VALUES (10193,'6/10/2003')
INSERT INTO HOADON VALUES (10207,'6/29/2003')
INSERT INTO HOADON VALUES (10225,'7/21/2003')
INSERT INTO HOADON VALUES (10226,'7/22/2003')
INSERT INTO HOADON VALUES (10255,'8/27/2003')
INSERT INTO HOADON VALUES (10258,'8/30/2003')
INSERT INTO HOADON VALUES (10261,'9/5/2003')
--------------------
INSERT INTO CTHD VALUES (10144,2,35,42)
INSERT INTO CTHD VALUES (10144,6,20,25)
INSERT INTO CTHD VALUES (10144,3,10,10.5)
INSERT INTO CTHD VALUES (10150,6,9,25)
INSERT INTO CTHD VALUES (10156,2,25,42)
INSERT INTO CTHD VALUES (10158,4,12,39.9)
INSERT INTO CTHD VALUES (10158,6,20,25)
INSERT INTO CTHD VALUES (10175,8,12,65.6)
INSERT INTO CTHD VALUES (10177,4,6,39.9)
INSERT INTO CTHD VALUES (10177,6,30,25)
INSERT INTO CTHD VALUES (10193,6,15,25)
INSERT INTO CTHD VALUES (10207,1,10,242)
INSERT INTO CTHD VALUES (10225,1,10,242)
INSERT INTO CTHD VALUES (10225,4,70,39.9)
INSERT INTO CTHD VALUES (10226,4,21,39.9)
INSERT INTO CTHD VALUES (10255,3,35,10.5)
INSERT INTO CTHD VALUES (10255,1,20,242)
INSERT INTO CTHD VALUES (10255,5,55,8.4)
INSERT INTO CTHD VALUES (10258,1,50,242)
INSERT INTO CTHD VALUES (10261,3,60,10.5)
--------------------
-- Câu 3
GO
CREATE VIEW vwTienHD
AS
SELECT H.MSHD,NGAYLAPHD,TIENHD= SUM(SOLUONG*DONGIABAN)
FROM HOADON H JOIN CTHD C ON H.MSHD = C.MSHD
GROUP BY H.MSHD,NGAYLAPHD
--------------------
GO
CREATE VIEW vwHangKhongBanDuoc
AS
SELECT M.* FROM MATHANG M
WHERE MSMH NOT IN( SELECT C.MSMH FROM CTHD C
WHERE C.MSHD IN(SELECT H.MSHD FROM HOADON H))
--------------------
GO
-- Câu 4,5
CREATE PROC DoanhThu(@Thang int = NULL,@Nam int = NULL)
AS
IF @Nam is NULL
BEGIN
print'Gia tri INPUT = NULL'
END
ELSE
IF @Thang is NULL
BEGIN
SELECT M.MSMH,DOANHTHU= SUM(SOLUONG*DONGIABAN)
FROM (MATHANG M JOIN CTHD C ON M.MSMH = C.MSMH) JOIN HOADON H ON H.MSHD = C.MSHD
WHERE YEAR(NGAYLAPHD) = @Nam
GROUP BY M.MSMH
END
ELSE
BEGIN
SELECT M.MSMH,DOANHTHU= SUM(SOLUONG*DONGIABAN)
FROM (MATHANG M JOIN CTHD C ON M.MSMH = C.MSMH) JOIN HOADON H ON H.MSHD = C.MSHD
WHERE MONTH(NGAYLAPHD) = @Thang and YEAR(NGAYLAPHD) = @Nam
GROUP BY M.MSMH
END
--------------------
GO
EXEC DoanhThu 6,2003
GO
EXEC DoanhThu @Nam=2003BÀI TEST SỐ 4
Yêu cầu
Câu 1: Cơ sở dữ liệu quản lý việc mượn trả sách có chứa 3 table. Viết lệnh tạo các table với các ràng buộc khóa chính, khóa ngoại được nêu trong tân từ. Kiểu dữ liệu của các Fields là tự chọn theo ngữ nghĩa.
Sach(MaSach, TenSach, SoLuong, SoDaMuon)
Tân từ: Mỗi tựa sách có một mã số (MaSach) là một số nguyên phân biệt, có tên sách (TenSach), số lượng (SoLuong), và số sách hiện đã cho mượn (SoDaMuon).
DocGia(MaDG, TenDG)
Tân từ: Mỗi đọc giả có một mã số đọc giả (MaDG) là một số nguyên phân biệt, có tên đọc giả (TenDG).
PhieuMuon(MaDG, NgayMuon, MaSach, NgayTra)
Tân từ: Mỗi ngày mỗi đọc giả chỉ được mượn một quyển sách. Khi đọc giả mượn sách cần ghi nhận mã đọc giả (MaDG), ngày mượn (NgayMuon), mã sách (MaSach), ngày trả sách (NgayTra). Ngày trả sách là Null khi chưa trả sách.
Câu 2: Khai báo bổ sung các ràng buộc sau:
- Số lượng sách (SoLuong) trong table Sachkhông nhỏ hơn số sách đã mượn (SoDaMuon)
- Ngày mượn sách (NgayMuon) trong table PhieuMuon có giá trị mặc định là ngày hiện hành
Câu 3: Tạo View:
- Tạo view “vwHetSach” dùng liêt kê các sách có số lượng sách (SoLuong) bằng với số lượng sách đã cho mượn (SoDaMuon) gồm các thông tin: Mã sách, tên sách.
- Tạo view “vwChuaTraSach” dùng hiển thị danh sách các đọc giả chưa trả sách (có ngày trả là rổng) gồm các thông tin: mã đọc giả, tên đọc giả, ngày mượn, tên sách đã mượn.
Câu 4: Tạo update trigger trên table PhieuMuon thực hiện yêu cầu sau: Khi sửa ngày trả của một phiếu mượn từ giá trị Null sang một giá trị khác Null thì phải giảm số lượng sách đã mượn của tựa sách tương ứng trên table Sach, ngược lại nếu sửa ngày trả từ một giá trị khác Null sang giá trị Null thì phải tăng số lượng sách đã mượn của tựa sách tương ứng trên table Sach.
Câu 5: Tạo thủ tục hoặc hàm trả về số sách còn có thể cho mượn (SoLuong – SoDaMuon) của một mã sách nào đó. Trong đó, mã sách là giá trị input. Hiển thị thông báo lỗi nếu mã sách không tồn tại trong table Sach.
Hướng dẫn
-- Bài test số 4 - Thực hành về sql server
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'De4')
BEGIN
DROP DATABASE De4
END
GO
CREATE DATABASE De4
GO
USE De4
GO
-- Câu 1
--------------------
CREATE TABLE SACH
( MASACH INT PRIMARY KEY,
TENSACH VARCHAR(100),
SOLUONG INT,
SODAMUON INT
)
--------------------
CREATE TABLE DOCGIA
( MADG INT PRIMARY KEY,
TENDG VARCHAR(100)
)
--------------------
CREATE TABLE PHIEUMUON
( MADG INT,
NGAYMUON DATETIME,
MASACH INT,
NGAYTRA DATETIME,
PRIMARY KEY(MADG,NGAYMUON),
FOREIGN KEY(MADG) REFERENCES DOCGIA(MADG)
)
GO
-- Câu 2
ALTER TABLE SACH ADD
CHECK(SOLUONG >= SODAMUON)
GO
ALTER TABLE PHIEUMUON ADD
DEFAULT GETDATE() FOR NGAYMUON
--------------------
GO
INSERT INTO SACH VALUES (1,'Cuon theo chieu gio',10,2)
INSERT INTO SACH VALUES (2,'Ong gia va bien ca',2,2)
INSERT INTO SACH VALUES (3,'Tat den',20,3)
INSERT INTO SACH VALUES (4,'Toan roi rac',1,1)
INSERT INTO SACH VALUES (5,'Lap trinh C#',4,4)
INSERT INTO SACH VALUES (6,'Lich su Dang',3,0)
INSERT INTO SACH VALUES (7,'Kinh van hoa',30,5)
INSERT INTO SACH VALUES (8,'Chien tranh va hoa binh',10,0)
--------------------
GO
INSERT INTO DOCGIA VALUES (1,'Nguyen Hoang Long')
INSERT INTO DOCGIA VALUES (2,'Nguyen Minh Tam')
INSERT INTO DOCGIA VALUES (3,'Le Quoc Thinh')
INSERT INTO DOCGIA VALUES (4,'To Hoai Tan')
INSERT INTO DOCGIA VALUES (5,'Nguyen Trung Truc')
INSERT INTO DOCGIA VALUES (6,'La Phi Hung')
--------------------
GO
INSERT INTO PHIEUMUON VALUES (1,'5/20/2000',2,'5/27/2000')
INSERT INTO PHIEUMUON VALUES (1,'5/30/2000',5,'6/20/2000')
INSERT INTO PHIEUMUON VALUES (1,'7/7/2000',7,'7/25/2000')
INSERT INTO PHIEUMUON VALUES (1,'7/1/2000',8,NULL)
INSERT INTO PHIEUMUON VALUES (2,'7/2/2000',7,NULL)
INSERT INTO PHIEUMUON VALUES (2,'7/3/2000',5,NULL)
INSERT INTO PHIEUMUON VALUES (3,'7/4/2000',5,'7/11/2000')
INSERT INTO PHIEUMUON VALUES (3,'7/5/2000',3,'7/12/2000')
INSERT INTO PHIEUMUON VALUES (3,'7/6/2000',7,'7/13/2000')
INSERT INTO PHIEUMUON VALUES (4,'7/7/2000',1,'7/14/2000')
INSERT INTO PHIEUMUON VALUES (4,'7/8/2000',7,NULL)
INSERT INTO PHIEUMUON VALUES (4,'7/9/2000',2,'7/16/2000')
INSERT INTO PHIEUMUON VALUES (4,'7/10/2000',3,'7/17/2000')
INSERT INTO PHIEUMUON VALUES (5,'7/11/2000',5,NULL)
INSERT INTO PHIEUMUON VALUES (5,'7/12/2000',4,NULL)
INSERT INTO PHIEUMUON VALUES (5,'7/13/2000',7,'7/20/2000')
INSERT INTO PHIEUMUON VALUES (6,'7/14/2000',3,'7/21/2000')
--------------------
-- Câu 3
GO
CREATE VIEW vwHetSach
AS
SELECT MASACH,TENSACH FROM SACH
WHERE SOLUONG = SODAMUON
--------------------
GO
CREATE VIEW vwChuaTraSach
AS
SELECT D.MADG,D.TENDG,NGAYMUON,TENSACH
FROM (DOCGIA D JOIN PHIEUMUON P ON D.MADG=P.MADG) JOIN SACH S ON S.MASACH=P.MASACH
WHERE NGAYTRA IS NULL
--------------------
-- Câu 4,5
GO
CREATE PROC SoSachChoMuon(@MASACH INT)
AS
BEGIN
IF NOT EXISTS (SELECT MASACH FROM SACH WHERE MASACH = @MASACH)
PRINT'Ma sach khong ton tai'
ELSE
SELECT MASACH,SoSachConLai = SOLUONG - SODAMUON
FROM SACH
WHERE MASACH = @MASACH
END
--------------------
GO
EXEC SoSachChoMuon 7BÀI TEST SỐ 5
Yêu cầu
Câu 1: Cơ sở dữ liệu quản lý lương hành chánh sự nghiệp có chứa 3 table. Viết lệnh tạo các table với các ràng buộc khóa chính, khóa ngoại được nêu trong tân từ. Kiểu dữ liệu của các Fields là tự chọn theo ngữ nghĩa.
- NgachLuong(MSNgach, MoTa)
Tân từ: Hệ thống lương hành chánh gồm nhiều ngạch, mỗi ngạch lương có một mã số (MSNgach) là một số nguyên phân biệt, mục mô tả (MoTa) dùng chỉ định nhân viên thực hiện công việc nào, bằng cấp nào thì thuộc ngạch lương này.
- DMNgachBac(MSNgach, Bac, HSLuong)
Tân từ: Mỗi ngạch lương (MSNgach) có nhiều bậc lương (Bac). Mỗi bậc lương là một số nguyên có giá trị từ 1 trở đi phân biệt trong cùng một ngạch lương. Mỗi bậc trong một ngạch lương có một hệ số lương (HSLuong).
- NhanVien(MSNV, Ho, Ten, MSNgach, Bac, NgayNLCC)
Tân từ: Mỗi nhân viên có một mã số (MSNV) là một số nguyên phân biệt, có họ (Ho), tên (Ten), thuộc ngạch lương (MSNgach), bậc lương (Bac) và ngày nâng lương cuối cùng (NgayNLCC).
Câu 2: Khai báo bổ sung các ràng buộc sau:
- Trong table DMNgachBac, Hệ số lương (HSLuong) phải phân biệt trong cùng một ngạch lương (MSNgach).
- Bậc lương (Bac) trong table NhanVien có giá trị mặc định (Default) bằng 1.
Câu 3: Tạo View:
- Tạo view vwHSLuong dùng liêt kê hệ số lương của mỗi nhân viên gồm các thông tin: mã số nhân viên, mã số ngạch, bậc, hệ số lương.
- Tạo view vwNangLuong dùng liệt kê nhân viên có số tháng tính từ ngày nâng lương cuối cùng (NgayNLCC) đến ngày hiện hành có giá trị không nhỏ hơn 24 tháng.
Câu 4: Tạo update trigger cho table NhanVien để kiểm tra ràng buộc: Khi nâng bậc lương (Bac) của một nhân viên thì bậc lương mới không lớn hơn bậc lương cao nhất trong ngạch lương của nhân viên (ví dụ, ngạch lương mã số 15113 có số thứ tự bậc lương từ 1 đến 10 thì bậc lương mới của nhân viên có mã ngạch 15113 không đượt lớn hơn 10).
Câu 5: Viết thủ tục hay hàm trả về danh sách nhân viên đã được nâng lương trong một năm (nghĩa là có ngày nâng lương cuối cùng thuộc năm tìm kiếm). Trong đó, năm là một giá trị Input kiểu số nguyên. Nếu năm là Null thì trả về danh sách nhân viên đã được nâng lương trong năm hiện hành.
Hướng dẫn
-- Bài test số 5 - Thực hành về sql server
-- Câu 1
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'De5')
BEGIN
DROP DATABASE De5
END
GO
CREATE DATABASE De5
GO
USE De5
GO
--------------------
CREATE TABLE NgachLuong
( MSNgach INT PRIMARY KEY,
MOTA VARCHAR(100)
)
--------------------
CREATE TABLE DMNganhBac
( MSNgach INT,
BAC INT,
HSLuong FLOAT,
PRIMARY KEY(MSNgach,BAC),
FOREIGN KEY(MSNgach) REFERENCES NgachLuong(MSNgach)
)
--------------------
CREATE TABLE NhanVien
( MSNV INT PRIMARY KEY,
HO VARCHAR(50),
TEN VARCHAR(50),
MSNgach INT,
BAC INT,
NGAYNLCC DATETIME
)
--------------------
-- Câu 2
GO
ALTER TABLE DMNganhBac ADD
UNIQUE(MSNgach,HSLuong)
GO
ALTER TABLE NhanVien ADD
DEFAULT 1 FOR BAC
--------------------
-- Câu 3
GO
CREATE VIEW vwHSLuong
AS
SELECT MSNV,N.MSNgach,N.BAC,HSLuong
FROM DMNganhBac D JOIN NhanVien N ON D.MSNgach = n.MSNgach
GROUP BY MSNV,N.MSNgach,N.BAC,HSLuong
--------------------
GO
CREATE VIEW vwNangLuong
AS
SELECT * FROM NhanVien
WHERE DATEDIFF(M,NGAYNLCC,GETDATE())>=24
--------------------
GO
-- Câu 4,5
CREATE PROC DSNangLuong(@Nam int = NULL)
AS
IF @Nam IS NULL
SELECT * FROM NhanVien
WHERE YEAR(NGAYNLCC)=YEAR(GETDATE())
ELSE
SELECT * FROM NhanVien
WHERE YEAR(NGAYNLCC) = @Nam
--------------------
GO
EXEC DSNangLuong 2000TranNhuomEDU Chúc các bạn THÀNH CÔNG!
