TR NG CAO Đ NG NGH TNDT TÂY NGUYÊNƯỜ Ẳ Ề
KHOA TIN H C – NN - KDỌ
C NG HOÀ XÃ H I CH NGHĨA VI T NAMỘ Ộ Ủ Ệ
Đ c ộl p - T do - H nhậ ự ạ phúc
Đ KI M TRA K T THÚCỀ Ể Ế
MÔ ĐUN: QU N TR H TH NG V I SQL SERVERẢ Ị Ệ Ố Ớ
Th i gian làm bài: 120 phútờ
Hình th c: ứTH C HÀNHỰ
L p:CĐN CNTT 11ớ
(H c sinh đ c phép s d ng tài li u khi làm bài thi)ọ ượ ử ụ ệ
Cho CSDL sau:
CHUCVU (MACV , TENCV)
THANHPHO (MATP , TENTP, PHI_CT_CHUAN1NGAY)
PHICONGTAC (MACV, MATP, PHI_1_NGAY)
NHANVIEN (MANV , TENNV, NGAYSINH, GIOITINH, MACV)
DOTCONGTAC (MADOT , MATP, SONGAY , NGAYDI)
THAMGIACONGTAC (MADOT, MANV)
Yêu c u:ầ
I. Đ nh nghĩa CSDL và b ng d li u (3 đi m)ị ả ữ ệ ể
a. S dung l nh t o c s d li u và t o b ng trong SQL server th c hiênư ệ ạ ơ ử ữ ệ ạ ả ư
viêc tao c s d liêu QLNS nh mô ta trên. (1đ) ơ ơ ư ư
b. Tao cac rang buôc sau đây: ( 1đ)
- Phí 1 ngày trong bang PHICONGTAC phai >=0 và s ngày đi trên b ng ố ả
DOTCONGTAC ph i >0.ả
- Nhân viên phai đat tuôi tôi thiêu la 18.
c. HSSV t xac đinh d liêu va th c hiên nhâp ( dùng l nh) vao môi bang it nhâtư ư ư ệ
4 dong. ( 1đ)
II. Th c hi n các truy v n sau:ự ệ ấ (4.5 đi m)ể
a. Cho bi t danh sách tât ca ế nhân viên có gi i tình n và có ch c v là ’Giámớ ữ ứ ụ
đ c’. (0.75đ)ố
b. Cho biêt thông tin gôm c a nh ng nhân viên có phi đi công tác trên 200 ngànủ ữ
m t ngày (0.5đ)ộ
c. Liêt kê nh ng thành ph co trên 5 nhân viên có s ngày đi công tác là 5 ư ố ố
đ n công tác(0.75đ)ế
d. Chuyên đôi tên cua nhân viên trong bang NHANVIEN sang ch in hoa. (0.5 ư
đi m)ể
e. Tăng s ngày công tác lên 20% cho nhân viên co tên la ’Lê Qu c C ng’.ố ố ườ
(0.5đ)
f. Xoá nh ng nhân viên đa có tu i trên 60. (0.75đ)ư ổ
g. Liêt kê tât ca cac nhân viên có cùng tu i v i nhân viên có ch c v ’phó ổ ớ ứ ụ
giám đ c’ố(0.75đ)
III. Xây d ng các hàm, th t c, trigger: (2.5 đi m)ự ủ ụ ể
Đ s : 1 ề ố
a. Viêt thu tuc Nhap Chucvu s dung cho viêc nhâp d liêu cho bang CHUCVU.ư ư
(0.75 đ)
b. Vi t hàm tr v m t b ng trong đó cho bi t danh sach đ t công tác có sế ả ề ộ ả ế ợ ố
ngày đi l n h n 5 và đi tr c ngày 15/8/2012 c a nhân viên có mã do ng iớ ơ ướ ủ ườ
dùng nh p vào (ma nhân viên la tham sô). (0.75 đ)ậ
c. Vi t trigger cho b ng PHICONGTAC sao cho khi c p nh t phí 1 ngày thìế ả ậ ậ
giá tr c a nó không đ c v t quá phí_CT_chuan1ngay trên b ngị ủ ượ ượ ả
THANHPHO. N u v t quá thì h y b thao tác đó. (1 đ)ế ượ ủ ỏ
Giáo viên ra đề Duy t c aệ ủ
Khoa
Nguy n Th Ái Nhiễ ị Huỳnh Ng c Đ ngọ ồ
Cán b coi thi không gi i thích gì thêm.ộ ả
ĐÁP ÁN Đ KI M TRA K T THÚCỀ Ể Ế
MÔ H C: PTTK H TH NG THÔNG TINỌ Ệ Ố
Đ S : 1Ề Ố
Th i gian làm bài :90 phútờ
Hình th c: Lý thuy tứ ế
L p CĐN CNTT 11ớ
CÂU
H IỎYÊU C U TR L IẦ Ả Ờ ĐI MỂ
I
a.1 Dùng l nh Create database đ t o CSDLệ ể ạ
a.2 Dùng l nh Create table đ t o các b ngệ ể ạ ả
a.3 Đ m b o t o đúng và đ các ràng bu c khóa chính khóaả ả ạ ủ ộ
ngo i trên m i b ng nh mô t trên mô hìnhạ ỗ ả ư ả
3
b.1 Alter table PHICONGTAC add constraint ck_PCT_phi
Check (Phi_1ngay >=0)
b.2 Alter table DOTCONGTAC add constraint ck_DCT_songay
Check (songay >0)
b.3 Alter table NHANVIEN add constraint ck_nhanvien_tuoi
Check ( datediff(year,ngaysinh,getdate())>=18)
c. Dùng l nh Insert into thêm m i b ng ít nh t 4 dòng d li uệ ỗ ả ấ ữ ệ
II
a. Select nhanvien.* from NHANVIEN where gioitinh=N’N ’ and MaCVữ
= (select MaCV from ChucVu where TenCV =N’Giám đ c’ố
4.5
b. select nhanvien.* fron NHANVIEN inner join PHICONGTAC on
PHICONGTAC.MaCV =NHANVIEN.MaCV where Phi_1ngay >200000
c. Select TenTP from THANHPHO where MATP in ( select MATP from
DOTCONGTAC inner join THAMGIACONGTAC on DOTCONGTAC.
MaDot = THAMGIACONGTAC.MaDot where songay =5 group by
MaDot having count(MaNV) >5
d. Update SINHVIEN set TenNV =Upper(TenNv)
e. Update DOTCONGTAC set songay =songay +songay*0.2 from
THAMGIACONG where MaNV = (select MaNV from NHANVIEN
where TenNV =N’Lê Qu c C ng’)ố ườ
f. Delete from NHANVIEN where where
datediff(year,ngaysinh,getdate()) >60)
g. select NhANVIEN.* from NHANVIEN WHERE MaNV NOT in
(select MANV from NHANVIEN where MaCV = (select MACV from
CHUCVU where TenCV =N’Phó giám đ c’)) and ngaysinh =any(selectố
ngaysinh from NHANVIEN NHANVIEN where MaCV = (select MACV
from CHUCVU where TenCV =N’Phó giám đ c’) )ố
III
-Dùng l nh Create proc đ t o th t c nh p d li u cho b ngệ ể ạ ủ ụ ậ ữ ệ ả
CHUCVU.
- Đ m b o có đ và đúng các tham sả ả ủ ố
- Có ki m tra ràng bu c khóa chính trong quá trình nh p li uể ộ ậ ệ
2.5
-Dùng l nh Create Function đ t o hàm cho bi t danh sách ệ ể ạ ế đ tợ
công tác có s ngày đi l n h n 5 và đi tr c ngày 15/8/2012ố ớ ơ ướ .
- Đ m b o có đ và đúng các tham sả ả ủ ố
- Ki u tr v c a hàm là 1 TABLEể ả ề ủ
-Đ m b o đi u ki n c a l nh select là songay >5 vàả ả ề ệ ủ ệ
datediff(day,ngaydi,’15/8/2012’)>0
- Có tham s đ u vào là @MaNVố ầ
-Dùng lênh Create Trigger đ t o trigger ki m tra giá tr c aể ạ ể ị ủ
Phi_1ngay trên b ng PHICONGTACả
-Hành đ ng c p nh t trên c t Phi_1ngay s kích ho t triggerộ ậ ậ ộ ẽ ạ
-N u Phi_1ngay <=Phi_ct_chuan1ngay thì cho phép c p nh tế ậ ậ
trên phi_1ngay. Ng c l i thì ROLLBACK TRANSACTION.ượ ạ