28 trang 53 lượt tải

Bài tập hệ quản trị cơ sở dữ liệu | Cơ sở dữ liệu | Trường Đại học Công nghiệp TP.HCM

105

Bài tập hệ quản trị cơ sở dữ liệu môn Cơ sở dữ liệu của Trường Đại học Công nghiệp Thành phố Hồ Chí Minh. Hi vọng tài liệu này sẽ giúp các bạn học tốt, ôn tập hiệu quả, đạt kết quả cao trong các bài thi, bài kiểm tra sắp tới. Mời các bạn cùng tham khảo chi tiết bài viết dưới đây nhé.

Môn: Cơ sở dữ liệu (ĐHCN) 19 tài liệu

Trường: Đại học Công nghiệp Thành phố Hồ Chí Minh 405 tài liệu

Tác giả:

Profile

Lê Phương

4 tháng trước
Danh sách Quiz
/28
lOMoARcPSD|45470709
BÀI T P TU N 1 Ậ Ầ
M c tiêu: ụ
Thao tác đ c v i giao di n c a SQL Server 2008 ượ ớ ệ ủ
T o Database - c s d li u (CSDL) và th c hi n các thao tác c b n trên ạ ơ ở ữ ệ ự ệ ơ ả
CSDL b ng l nh và b ng công c design ằ ệ ằ ụ
T o các Table (B ng d li u) và nh p d li u b ng công c design ạ ả ữ ệ ậ ữ ệ ằ ụ
T o l c đ quan h (Relationship Diagram) ạ ượ ồ ệ
Bi t các ki u d li u (DataType) trong SQL Server 2008 ế ể ữ ệ
Bi t t o, s a, xóa và áp d ng các ki u d li u trong SQL Server 2008 ế ạ ử ụ ể ữ ệ
Bi t s d ng m t s th t c tr giúp v Database và Datatype ế ử ụ ộ ố ủ ụ ợ ề
1. T o CSDL QLSach b ng công c design có tham s nh sau: ạ ằ ụ ố ư
Tham s ố Giá tr ị
Database name QLSach
Tên logic c a data file chính ủ QLSach_Data
Tên t p tin và đ ng d n c a data file ậ ườ ẫ ủ
chính
T:\QLSach_Data.mdf
Kích c kh i t o c a CSDL ỡ ở ạ ủ 20 MB
Kích c t i đa c a CSDL ỡ ố ủ 40 MB
Gia s gia tăng t p tin CSDL ố ậ 1 MB
Tên logic c a transaction log ủ QLSach_Log
Tên t p ậ tin và đ ng ườ d n ẫ c a ủ T:\QLSach_Log.ldf
Kích c kh i t o c a transaction log ỡ ở ạ ủ 6 MB
Kích c t i đa c a transaction log ỡ ố ủ 8 MB
Gia s gia tăng t p tin transaction log ố ậ 1 MB
a. Xem l i thu c tính (properties) c a CSDL QLSach. (HD: Nh p ph i chu tạ ộ ủ ắ ả ộ
t i tên CSDL, ch n properties). Quan sát và cho bi t các trang th hi nạ ọ ế ể ệ
thông tin gì?
lOMoARcPSD|45470709
b. T i c a s properties c a CSDL, khai báo thêm ạ ử ổ ủ
M t Group File m i có tên là DuLieuSach ộ ớ
M t t p tin d li u (data file) th hai n m trong Group file v a t o trênộ ậ ữ ệ ứ ằ ừ ạ ở
và có thông s nh sau Tên login c a data file là QLSach_Data2; Tên t pố ư ủ ậ
tin và đ ng d n v t lý c a data file là T:\QLSach_Data2.ndf. ườ ẫ ậ ủ
Ch n thu c tính ReadOnly, sau đó đóng c a s properies. Quan sát màuọ ộ ử ổ
s c c a CSDL. B thu c tính ReadOnly. ắ ủ ỏ ộ
Thay đ i Owner: tên server đang k t n i. ổ ế ố
2. t i Query Analyzer (l u ý: sau m i l n có s thay đ i thì ph i dùng cácỞ ạ ư ỗ ầ ự ổ ả
l nh đ ki m tra s thay đ i đó) ệ ể ể ự ổ
a. Dùng l nh Create DataBase, t o m t CSDLệ ạ ộ v i các tham s đ c li t kêớ ố ượ ệ
nh trong b ng d i. L u ý r ng CSDL này g m m t data file và nó đ cư ả ướ ư ằ ồ ộ ượ
n m trong primary filegroup ằ
THAM S Ố GIÁ TR Ị
Database name QLBH
Tên logic c a data file chính ủ QLBH_data1
Tên t p tin và đ ng d n c a data file ậ ườ ẫ ủ
chính
T:\QLBH_data1.mdf
Kích c kh i t o c a CSDL ỡ ở ạ ủ 10 MB
Kích c t i đa c a CSDL ỡ ố ủ 40 MB
Gia s gia tăng t p tin CSDL ố ậ 1 MB
Tên logic c a transaction log ủ QLBH_Log
Tên t p ậ tin và đ ng ườ
d n ẫ c a transaction log ủ
T:\QLBH.ldf
Kích c kh i t o c a transaction log ỡ ở ạ ủ 6 MB
Kích c t i đa c a transaction log ỡ ố ủ 8 MB
Gia s gia tăng t p tin transaction log ố ậ 1 MB
b. Xem l i thu c tính c a CSDL QLBH b ng cách Click ph i vào tên CSDLạ ộ ủ ằ ả
ch n Property và b ng th t c h th ng sp_helpDb, sp_spaceused,ọ ằ ủ ụ ệ ố
sp_helpfile.
c. Thêm m t filegroup có tên là DuLieuQLBH (HD: dùng l nh Alter DataBaseộ ệ
<Tên Database> ADD FILEGROUP <Tên filegroup>)
d. Khai báo m t secondary file có tên logic là QLBH_data2, tên v t lýộ ậ
QLBH_data2.ndf n m T:\, các thông s khác tuỳ b n ch n, data file nàyằ ở ố ạ ọ
n m trong file group là DuLieuQLBH. (HD: Dùng l nh Alter Database ….ằ ệ
ADD
FILE …. TO FILEGROUP …)
lOMoARcPSD|45470709
e. Cho bi t th t c h th ng sp_helpfilegroup dùng đ làm gì? ế ủ ụ ệ ố ể
f. Dùng l nh Alter Database … Set … đ c u hình cho CSDL QLBH có thu cệ ể ấ ộ
tính là Read_Only. Dùng sp_helpDB đ xem l i thu c tính c a CSDL. H yể ạ ộ ủ ủ
b thu c tính Read_Only. ỏ ộ
g. Dùng l nh Alter DataBase … MODIFY FILE … đ tăng SIZE c a QLBH_data1ệ ể ủ
thành 50 MB. T ng t tăng SIZE c a t p tin QLBH_log thành 10 MB. Đươ ự ủ ậ ể
thay đ i SIZE c a các t p tin b ng công c Design b n làm nh th nào?ổ ủ ậ ằ ụ ạ ư ế
B n hãy th c hi n thay đ i kích th c c a t p tin QLBH_log v i kíchạ ự ệ ổ ướ ủ ậ ớ
th c là 15MB. N u thay đ i kích c nh h n ban đ u có đ c không?ướ ế ổ ỡ ỏ ơ ầ ượ
N u thay đ i kích c MAXSIZE nh h n kích c SIZE thì có đ c không?ế ổ ỡ ỏ ơ ỡ ượ
Gi i thích. ả
3. T o CSDL QLSV, các thông s tùy ch n. Dùng công c design t o c u trúcạ ố ọ ụ ạ ấ
c a các b ng sau trong CSDL QLSV: ủ ả
LOP (MaLop char(5) , TenLop NVarchar(20), SiSoDuKien Int,
NgayKhaiGiang DateTime)
SINHVIEN (MaSV char(5), TenHo NVarchar(40), NgaySinh DateTime,
MALOP char(5))
MONHOC(MaMh char(5), Tenmh Nvarchar(30), SoTC int)
KETQUA(MaSV char(5), MAMH char(5), Diem real)
L u ý: c t in đ m g ch chân là khóa chính và không ch p nh n giá tr Null,ư ộ ậ ạ ấ ậ ị
c t in đ m không ch p nh n giá tr Null ộ ậ ấ ậ ị
a. T o Diagram gi a hai b ng v a t o. ạ ữ ả ừ ạ
b. Nh p d li u tùy ý vào hai các b ng b ng công c design, m i b ngậ ữ ệ ả ằ ụ ỗ ả
kho ng 3 m u tin. Gi s b n nh p d li u cho b ng KETQUA tr c, sauả ẫ ả ử ạ ậ ữ ệ ả ướ
đó m i nh p d li u cho các b ng còn l i thì b n có nh p đ c không? Vìớ ậ ữ ệ ả ạ ạ ậ ượ
sao? Theo b n nên nh p d li u theo th t nào? ạ ậ ữ ệ ứ ự
c. Dùng tác v General Script, đ t o đo n Script cho CSDL và t t c các đ iụ ể ạ ạ ấ ả ố
t ng c a CSDL thành m t t p tin Script có tên là QLSV.SQL ượ ủ ộ ậ
d. Vào Query Analyzer, m t p tin Script v a t o và kh o sát công d ng và cúở ậ ừ ạ ả ụ
pháp c a các l nh có trong t p tin script. ủ ệ ậ
e. Đ i tên CSDL QLSV thành QLHS ổ
f. Dùng thao tác xóa đ xoá toàn b CSDL QLHS ể ộ 4. Tìm hi u v ki u d li uể ề ể ữ ệ
(datatype):
a. Tìm hi u và tr l i các câu h i sau: ể ả ờ ỏ
- Có m y lo i datatype, hãy li t kê. ấ ạ ệ
- Các system datatype đ c SQL Server l u tr trong Table nào ượ ư ữ ở
trong CSDL nào.
- Các User-defined datatype đ c SQL Server l u tr trong Table nào,ượ ư ữ
trong CSDL nào? ở
lOMoARcPSD|45470709
b. Vào Query Analyzer, ch n QLBH là CSDL hi n hành, đ nh nghĩa cácọ ệ ị
datatype:
Ki u d li u (Data ể ữ ệ
type)
Mô t d li u (Description of data) ả ữ ệ
Mavung 10 ký t ự
STT STT không v t quá 30,000 ượ
SoDienThoai 13 ký t , ch p nh n NULL ự ấ ậ
Shortstring S ký t thay đ i đ n 15 ký t ố ự ổ ế ự
HD: Dùng th t c sp_addtype đ đ nh nghĩa ủ ụ ể ị
Ví d : EXEC sp_addtype SODienThoai, 'char(13)', NULL ụ
c. Các User-defined datatype v a đ nh nghĩa đ c l u tr đâu và ph m viừ ị ượ ư ữ ở ạ
s d ng c a nó đâu (trong toàn b m t instance hay ch trong CSDLử ụ ủ ở ộ ộ ỉ ở
hi n hành). ệ
d. Có bao nhiêu cách li t kê danh sách các User-Defined datatype v a đ nhệ ừ ị
nghĩa.
SELECT domain_name, data_type, character_maximum_length
FROM information_schema.domains ORDER BY domain_name Ho cặ
SELECT * From Systype
e. T o 1 b ng có tên là ThongTinKH(MaKH (khóa chính) ki u d li u STT ,ạ ả ể ữ ệ
Vung ki u là Mavung, Diachi ki u là Shortstring, DienThoai ki u làể ể ể
SoDienThoai) trong CSDL QLBH và s d ng User-defined data type v aử ụ ừ
đ nh nghĩa trên. B n có t o đ c không? N u đ c b n nh p th dị ở ạ ạ ượ ế ượ ạ ậ ử ữ
li u 2 record b ng design. ệ ằ
f. Mu n User-Defined datatype đ c dùng trong t t c các CSDL thì b nố ượ ấ ả ạ
đ nh nghĩa nó đâu? ị ở
g. Xóa ki u d li u SoDienThoai. ể ữ ệ
h. Th c hi n vi c Backup và Restore CSDL QLBH ự ệ ệ
lOMoARcPSD|45470709
BÀI T P 1:Ậ
Cho mô t nghi p v c a h th ng qu n lý bán hàng c a m t siêu th nh sau: ả ệ ụ ủ ệ ố ả ủ ộ ị ư
• Siêu th bán nhi u s n ph m khác nhau. Các s n ph m đ c phân lo iị ề ả ẩ ả ẩ ượ ạ
theo t ng nhóm s n ph m, m i nhóm s n ph m có m t mã nhómừ ả ẩ ỗ ả ẩ ộ
(MANHOM) duy nh t, m i mã nhóm hàng xác đ nh tên nhóm hàngấ ỗ ị
(TENNHOM), t t nhiên m t nhóm hàng có th có nhi u s n ph m. M iấ ộ ể ề ả ẩ ỗ
s n ph m đ c đánh m t mã s (MASP) duy nh t, m i mã s s n ph mả ẩ ượ ộ ố ấ ỗ ố ả ẩ
xác đ nh các thông tin v s n ph m đó nh : tên s n ph m (TENSP), mô tị ề ả ẩ ư ả ẩ ả
s n ph m (MoTa), đ n v tính (Đ n v tính), đ n giá mua (ĐONGIA), sả ẩ ơ ị ơ ị ơ ố
l ng t n (SLTON). ượ ồ
• Siêu th l y hàng v t nhi u nhà cung c p khác nhau. M i s n ph mị ấ ề ừ ề ấ ỗ ả ẩ
đ c l y t m t nhà cung c p. H th ng ph i l u tr các thông tin v cácượ ấ ừ ộ ấ ệ ố ả ư ữ ề
nhà cung c p hàng cho siêu th . M i nhà cung c p có m t mã s (MaNCC)ấ ị ỗ ấ ộ ố
duy nh t, m i mã nhà cung c p sẽ xác đ nh tên nhà cung c p (TenNCC),ấ ỗ ấ ị ấ
đ a ch (Diachi), s đi n tho i (Phone), s fax (Sofax) và đ a ch mailị ỉ ố ệ ạ ố ị ỉ
(DCMail).
• Siêu th bán hàng cho nhi u lo i khách hàng khác nhau. M i khách hàng cóị ề ạ ỗ
m t mã khách hàng (MAKH) duy nh t, m i MAKH xác đ nh đ c các thôngộ ấ ỗ ị ượ
tin v khách hàng nh : h tên khách hàng (HOTEN), đ a ch (ĐIACHI), sề ư ọ ị ỉ ố
đi n tho i (ĐIENTHOAI), Ngày đăng ký th thành viên (NgayDKThe), đ aệ ạ ẻ ị
ch mail (DCMail), đi m tích lũy (DiemTL). Siêu th chia khách hàng thànhỉ ể ị
3 lo i khách hàng: VIP, TV, VL. Khách hàng VIP là nh ng khách hàng đã làạ ữ
thành viên trên 5 năm và có t ng s hóa đ n mua hàng trên 100, kháchổ ố ơ
hàng TV(thành viên là các khách hàng đã làm th thành viên nh ng khôngẻ ư
đ đi u ki n c a khách hàng VIP). Khách hàng vãng lai (VL) là khách hàngủ ề ệ ủ
ch a có th thành viên. Đ i v i khách hàng vãng lai thì MaKH sẽ đ c hư ẻ ố ớ ượ ệ
th ng t c p phát MaKH cho m i l n mua hàng do đó h th ng không c nố ự ấ ỗ ầ ệ ố ầ
l u các thông tin còn l i c a khách hàng vãng lai. ư ạ ủ
• M i l n mua hàng, khách hàng có m t hóa đ n. M i hóa đ n bán hàng cóỗ ầ ộ ơ ỗ ơ
m t s hóa đ n (SOHĐ) duy nh t, m i s hóa đ n xác đ nh đ c kháchộ ố ơ ấ ỗ ố ơ ị ượ
hàng và ngày l p hóa đ n (NGAYLAPHĐ), ngày giao hàng (NGAYGIAO) vàậ ơ
n i chuy n hàng (NoiChuyen). ng v i m i hóa đ n siêu th qui đ nh nhơ ể Ứ ớ ỗ ơ ị ị ư
sau: N u khách hàng VIP sẽ đ c t ng 20% t ng ti n vào đi m tích lũyế ượ ặ ổ ề ể
c a khách hàng, n u là thành viên là 10%t ng ti n, vãng lai thì khôngủ ế ổ ề
đ c t ng đi m tích lũy. D a vào đi m tích lũy siêu th sẽ t ng phi u quàượ ặ ể ự ể ị ặ ế
t ng ặ
vào cu i năm cho các khách hàng. M i đ n hàng có th mua nhi u s nố ỗ ơ ể ề ả
ph m, V i m i s n ph m trong m t hóa đ n cho bi t s l ng bánẩ ớ ỗ ả ẩ ộ ơ ế ố ượ
(SLBAN) c a m t hàng đó. ủ ặ
1. D a vào nghi p v trên, vẽ mô hình th c th k t h p ERD, sau đó chuy nự ệ ụ ự ể ế ợ ể
qua l c đ c s d li u cho h th ng trên và xác đ nh các ràng bu c khóaượ ồ ơ ở ữ ệ ệ ố ị ộ
chính và khóa ngo i cho l c đ CSDL. ạ ượ ồ
2. Xác đ nh các qui t c nghi p v c a h th ng trên. ị ắ ệ ụ ủ ệ ố
3. T i c a s Query analyzer, th c hi n: ạ ử ổ ự ệ
lOMoARcPSD|45470709
a. Dùng l nh Create Table … đ t o c u trúc c a các b ng sau trong CSDLệ ể ạ ấ ủ ả
QLBH:
L u ýư : Các b ng KHACHHANG, HOADON, CT_HOADON khai báo c t có Nullả ộ
ho c Not Null, không c n khai báo khóa chính, khóa ngo i. B ngặ ầ ạ ả
NhomSanPham, SanPham, NhaCungCap yêu c u t o khóa chính và khóa ngo iầ ạ ạ
trong l nh Create Table luôn, các b ng còn l i thì dùng l nh Alter Table đệ ả ạ ệ ể
t o khóa chính và khóa ngo i. ạ ạ
NhomSanPham
MaNhom Int Not null
TenNhom Nvarchar(15)
SanPham
MaSp int Not null
TenSp nvarchar(40) Not null
MaNCC Int
MoTa nvarchar(50)
MaNhom int
Đonvitinh nvarchar(20)
GiaGoc Money >0
SLTON Int >0
HoaDon
MaHD Int Not null
NgayLapHD DateTime >=Ngày hi n hành ệ
Giá tr m c đ nh là ị ặ ị
ngày hi n hành ệ
NgayGiao DateTime
Noichuyen NVarchar(60) Not Null
MaKh Nchar(5)
CT_HoaDon
MaHD Int Not null
MaSp int Not null
Soluong SmallInt >0
Dongia Money
ChietKhau Money >=0
NhaCungCap
MaNCC Int Not null
lOMoARcPSD|45470709
TenNcc Nvarchar(40) Not Null
Diachi Nvarchar(60)
Phone NVarchar(24)
SoFax NVarchar(24)
DCMail NVarchar(50)
KhachHang
MaKh NChar(5) Not null
TenKh Nvarchar(40) Not null
LoaiKh Nvarchar(3) Ch nh p VIP, TV, VLỉ ậ
DiaChi Nvarchar(60)
Phone NVarchar(24)
SoFax NVarchar(24)
DCMail NVarchar(50)
DiemTL Int >=0
b. Dùng l nh Alter Table … khai báo các ràng khóa chính (Primary Keyệ
Constraint) các b ng còn l i KHACHHANG, HOADON, CT_HOADON. ở ả ạ
c. Dùng l nh Alter Table … khai báo các ràng khóa ngo i (Foreign Keyệ ạ
Constraint) các b ng còn l i KHACHHANG, HOADON, CT_HOADON. . ở ả ạ
d. Dùng l nh Alter Table … khai báo các ràng bu c mi n giá tr (Check ệ ộ ề ị
Constraint) và ràng bu c giá tr m c đ nh cho các b ng trên ộ ị ặ ị ả
e. Thêm c t LoaiHD vào b ng HOADON, LoaiHD có ki u d li u char(1), Chộ ả ể ữ ệ ỉ
nh p N(Nh p), X(Xu t), C(Chuy n t c a hàng này sang c a hàng khác), Tậ ậ ấ ể ừ ử ử
(Tr ), giá tr m c đ nh là ‘N’. ả ị ặ ị
f. T o ràng bu c cho b ng HoaDon v i yêu c u NgayGiao>=NgayLapHD ạ ộ ả ớ ầ
4. Th c hi n phát sinh t p tin script cho CSDL QLBH v i các l a ch n sau, l uự ệ ậ ớ ự ọ ư
v i tên TableQLBH.sql: ớ
All Tables, All user-defined data types
Generate the CREATE <object> command for each object
Generate the DROP <object> command for each object
Generate the Constraint <object> command for each object
5. T o s đ quan h cho CSDL QLBH ạ ơ ồ ệ
BÀI T P 2 (làm thêm t i nhà và n p l i cho GV): Ậ ạ ộ ạ
1. Dùng T-SQL t o CSDL Movies v i các tham s sau: ạ ớ ố
T p ậ tin Datafile có: Name: Movies_data;
pathname: C:\Movies\Movies_data.mdf; Size: 25 MB; Maxsize: 40
MB; FileGrowth: 1 MB.
lOMoARcPSD|45470709
T p ậ tin Log file có: Name: Movies_log; pathname:
C:\Movies\Movies_log.ldf; Size: 6 MB; Maxsize: 8 MB; FileGrowth: 1 MB.
2. Th c hi n, ki m tra k t qu sau m i l n th c hi n: ự ệ ể ế ả ỗ ầ ự ệ
Thêm m t Datafile th 2 có Name: Movies_data2; pathname: C:\Movies\ộ ứ
Movies_data2.ndf; Size: 10 MB; thông s khác không c n ch đ nh. ố ầ ỉ ị
L n l t c u hình CSDL Movies v i ch đ single_user, restricted user,ầ ượ ấ ớ ế ộ
multi user. Sau đó cài đ t l i là multi_user. Nh dùng l nh đ ki m traặ ạ ớ ệ ể ể
l i. ạ
Tăng kích c c a data file th 2 t 10 MB lên 15 MB. Ki m tra l i. ỡ ủ ứ ừ ể ạ
C u hình CSDL v ch đ t đ ng SHRINK ấ ề ế ộ ự ộ
Phát sinh t p tin Script t CSDL Movies, t o script cho t t c các đ iậ ừ ạ ấ ả ố
t ng k c database. L u vào đĩa v i tên là Movies.SQL. ượ ể ả ư ớ Xoá CSDL
Movies
3. M t p tin Movies.SQL. Th c hi n: ở ậ ự ệ
B sung thêm câu l nh t o m t filegroup tên là Data. ổ ệ ạ ộ Hi u ệ
ch nh maxsize c a t p tin transaction log thành 10 MB ỉ ủ ậ Size
c a t p tin datafile th 2 thành 10 MB. ủ ậ ứ
Cho datafile th 2 n m trong filegroup có tên là Data. ứ ằ
L u t p tin scrip ư ậ
Cho th c thi toàn b t p tin script. ự ộ ậ
Dùng sp_helpDB đ ki m tra s t n t i c a Movies và các thông s c aể ể ự ồ ạ ủ ố ủ
nó.
4. Các b ng có trong CSDL Movies là ả
Tên b ng ả N i dung l u ch a ộ ư ứ
Movie Danh sách các phim có trong c a hàng ử
Customer Thông tin khách hàng
Category Danh sách các lo i phim ạ
Rental Thông tin thuê phim
Rental_detail Chi ti t thuê phim ế
B n hãy suy nghĩ xem m i b ng trên c n l u nh ng thông tin c th nàoạ ỗ ả ầ ư ữ ụ ể
(t c là các c t nào), ki u d li u ra sao? Khóa chính c a t ng b ng, m iứ ộ ể ữ ệ ủ ừ ả ố
quan h gi a các b ng, có nh ng ràng bu c toàn v n nào? ệ ữ ả ữ ộ ẹ
5. Th c hi n đ nh nghĩa các user-defined datatype sau vào trong CSDLự ệ ị
Movies. Ki m tra sau khi t o. ể ạ
Ki u d li u (Data ể ữ ệ
type)
Mô t d li u (Description of data) ả ữ ệ
Movie_num Int, không ch p nh n Null ấ ậ
Category_num Int, không ch p nh n Null ấ ậ
lOMoARcPSD|45470709
Cust_num Int, không ch p nh n Null ấ ậ
Invoice_num Int, không ch p nh n Null ấ ậ
6. Th c hi n t o các b ng vào CSDL Movies, nh ki m tra l i c u trúc b ngự ệ ạ ả ớ ể ạ ấ ằ
sp_help Customer
Tên c t ộ ki u d li u ể ữ ệ cho phép Null
Cust_num cust_num IDENTITY(300,1) No
Lname varchar(20) No
Fname varchar(20) No
Address1 varchar(30) Yes
Address2 varchar(20) Yes
City varchar(20) Yes
State Char(2) Yes
Zip Char(10) Yes
Phone Varchar(10) No
Join_date Smalldatetime No
Category
Tên c t ộ ki u d li u ể ữ ệ cho phép Null
Category_num category_num
IDENTITY(1,1)
No
Description Varchar(20) No
Movie
Tên c t ộ ki u d li u ể ữ ệ cho phép Null
Movie_num Movie_num No
Title Cust_num No
Category_Num category_num No
Date_purch Smalldatetime Yes
Rental_price Int Yes
Rating Char(5) Yes
Rental:
Tên c t ộ Ki u d li u ể ữ ệ cho phép Null
Invoice_num Invoice_num No
Cust_num Cust_num No
Rental_date Smalldatetime No
Due_date Smalldatetime No
lOMoARcPSD|45470709
Rental:_Detail
Tên c t ộ Ki u d li u ể ữ ệ cho phép Null
Invoice_num Invoice_num No
Line_num Int No
Movie_num Movie_num No
Rental_price Smallmoney No
7. Th c hi n phát sinh t p tin script cho CSDL Movies v i các l a ch n sau,ự ệ ậ ớ ự ọ
l u v i tên Table.sql: ư ớ
All Tables, All user-defined data types
Generate the CREATE <object> command for each object
Generate the DROP <object> command for each object
8. Th c hi n t o Diagram cho các b ng trong Movies. B n có t o đ cự ệ ạ ả ạ ạ ượ
không? T i sao? T m th i l u diagram v i tên là Movies. ạ ạ ờ ư ớ
9. Th c hi n đ nh nghĩa các khoá chính (Primary Key Constraint) cho cácự ệ ị
b ng nh sau, nh ki m tra các Constraint b ng l nh sp_helpconstraint ả ư ớ ể ằ ệ
Tên b ng ả c t làm khóa ộ Tên c a Primary Constraint ủ
Movie Movie_num PK_movie
Customer Cust_num PK_customer
Category Category_num PK_category
Rental Invoice_num PK_rental
10.Th c hi n đ nh nghĩa các khoá ngo i (Foreign Key Constraint) cho cácự ệ ị ạ
b ng nh sau, nh ki m tra các Constraint b ng l nh sp_helpconstraint ả ư ớ ể ằ ệ
Tên b ng ả C t làm khóa ộ Tên
b ng ả
đ c ượ
tham
chi u ế
đ n ế
C t ộ đ c ượ
tham chi u ế
Tên c a ủ
Primary
Constraint
Cascade
Movie Category_num Category Category_num FK_movie
Rental Cust_num Customer Cust_num FK_rental
Rental_detail Invoice_num Rental Invoice_num FK_detail_invoice delete
Rental_detail Movie_num Movie Movie_num PK_detail_movie
11.M l i Diagram có tên Movie, xem khóa chính, m i quan h gi a các b ng. ở ạ ố ệ ữ ả
lOMoARcPSD|45470709
12.Th c hi n đ nh nghĩa các giá tr m c đ nh (Default Constraint) cho các c tự ệ ị ị ặ ị ộ
các b ng nh sau, nh ki m tra các Constraint b ng l nhở ả ư ớ ể ằ ệ
sp_helpconstraint
Tên b ng ả C t có giá ộ
tr Default ị
Giá tr Default ị Tên c a ủ Primary
Constraint
Movie Date_purch Ngày hi n hành ệ DK_movie_date_purch
Customer join_date Ngày hi n hành ệ DK_customer_join_date
Rental Rental_date Ngày hi n hành ệ DK_rental_rental_date
Rental Due_date Ngày hi n hành + ệ
2
DK_rental_due_date
13.Th c hi n đ nh nghĩa các mi n giá tr (Check Constraint) cho các c t cácự ệ ị ề ị ộ ở
b ng nh sau, nh ki m tra các Constraint b ng l nh sp_helpconstraint ả ư ớ ể ằ ệ
Tên b ng ả C t có giá ộ
tr Default ị
Mi n giá tr ề ị Tên c a ủ
Constraint
Primary
Movie Rating ‘G’, ‘PG’, ‘R’, ‘NC17’, ‘NR’ CK_movie
Rental Due_date >= Rental_date CK_Due_date
14.Th c hi n phát sinh t p tin script cho các đ i t ng trong CSDL Movie.ự ệ ậ ố ượ
Tên c a t p tin là Constraint.sql. V i l a ch n Script Primary Keys,ủ ậ ớ ự ọ
Foreign Keys, Default, and Check Constraints.
BÀI T P 3: Ậ
Dùng ch c năng Import/Export (ki m tra k t qu sau m i l n th c hi n): ứ ể ế ả ỗ ầ ự ệ
T t c các thông tin nhân viên có trong b ng Employees trong NorthWind raấ ả ả
thành t p tin NhanVien.txt. ậ
D li u c a các b ng Products, Orders, Order Details trong b ngữ ệ ủ ả ả
NorthWind vào t p tin QLHH.MDB. L u ý: T p tin QLHH.MDB ph i t n t iậ ư ậ ả ồ ạ
trên đĩa tr c khi th c hi n Export. ướ ự ệ
D li u các b ng Products, Suppliers trong NorthWind ra thành t p tinữ ệ ả ậ
SP_NCC.XLS
Các khách hàng có City là LonDon t b ng Customers trong NorthWind raừ ả
thành t p tin KH.TXT. ậ
Danh sách các s n ph m Products trong NorthWind thành t p tinả ẩ ở ậ
SanPham.TXT, thông tin c n l y bao g m ProductID, ProductName,ầ ấ ồ
QuantityPerUnit, Unitprice.
Các s n ph m có SupplierID là ả ẩ 1 ho c ặ 2 ho c ặ 3 b ng Products trongở ả
NorthWind vào b ng SanPham trong QLBH. L u ý ch ch n nh ng c t màả ư ỉ ọ ữ ộ
trong b ng s n ph m c n. ả ả ẩ ầ
Các nhà cung c p có Country là ấ USA b ng Suppliers trong NorthWind vàoở ả
b ng NhaCungCap trong QLBH. L u ý: ch ch n nh ng c t mà trong b ngả ư ỉ ọ ữ ộ ả
Nhacungcap c n. ầ
Danh sách các nhân viên có trong t p tin Nhanvien.TXT vào b ng NhanVien ậ ả
lOMoARcPSD|45470709
BÀI T P 4: M CSDL QLBH, th c hi n các l nh sau: Ậ ở ự ệ ệ
1. Thêm vào m i Table 2 dòng d li u thông qua c a s Design. D li u sinh viênỗ ữ ệ ử ổ ữ ệ
t nghĩ. Chú ý: Các ràng bu c c a các Table ự ộ ủ
2. Dùng l nh Insert thêm d li u vào các b ng sau: N u b ng nào có d li u b nệ ữ ệ ả ế ả ữ ệ ạ
hãy xóa h t các d li u đó tr c r i m i nh p vào ế ữ ệ ướ ồ ớ ậ
Table NhomHang
Table NhaCungCap
Table SanPham
Table KhachHang
Table HoaDon
lOMoARcPSD|45470709
Table CT_HoaDon
3. Dùng l nh Update s a ch a d li u theo yêu c u sau ệ ử ữ ữ ệ ầ
a) Tăng đ n giá bán lên 5% cho các s n ph m có mã là 2 ơ ả ẩ
b) Tăng s l ng t n lên 100 cho các s n ph m có nhóm m t hàng là 3 c aố ượ ồ ả ẩ ặ ủ
nhà cung c p có mã là 2 ấ
c) Tăng đi m tích lũy lên 50 cho nh ng khách hàng không ph i là kháchể ữ ả
hàng vãng lai
d) C p nh t c t mô t cho s n ph m có tên là Lò vi sóng (d li u c t mô tậ ậ ộ ả ả ẩ ữ ệ ộ ả
sinh viên t thêm) ự
e) Tăng đ n giá g c lên 2% cho nh ng s n ph m mà ph n tên có ch a chơ ố ữ ả ẩ ầ ứ ữ
u
4. Dùng l nh Delete th c hi n các yêu c u sau: ệ ự ệ ầ
a) Xóa các s n ph m có SLTon <2 ả ẩ
b) Xóa các hóa đ n c a khách hàng vãng lai ơ ủ
c) Xóa khách hàng thu c lo i VIP mà có đi m tích lũy b ng 0 ộ ạ ể ằ
BÀI T P TU N 2-3 Ậ Ầ
lOMoARcPSD|45470709
Ch n CSDL hi n hành là NorthWind, tìm hi u c u trúc và d li u c a các b ng.ọ ệ ể ấ ữ ệ ủ ả
Xây d ng s đ quan h cho các b ng d i. Sau đó dùng câu l nh Select … Fromự ơ ồ ệ ả ướ ệ
… Where … đ truy v n d li u nh sau, v i c u trúc c a các b ng nh sau: ể ấ ữ ệ ư ớ ấ ủ ả ư
Products(ProductID,ProductName, SupplierID, UnitPrice, UnitInStock, … )
Customers(CustomerID, CompanyName, Address, City, Region, Country, …)
Employees(EmployeeID, LastName, FirstName, BirthDate, City, …)
Orders(OderID, CustomerID, EmployeeID, OrderDate,…)
Order Details(OrderID, ProductID, UnitPrice, Quantity, Discount)
Suppliers(SupplierID, SupplierName,…)
Chú ý: N u câu nào mà b n đánh đúng l nh mà k t qu không có, b n cho bi tế ạ ệ ế ả ạ ế
t i sao? ạ
BÀI T P 1: L NH SELECT – TRUY V N Đ N GI N Ậ Ệ Ấ Ơ Ả
1. Li t kê thông tin c a t t c các s n ph m (Products) ệ ủ ấ ả ả ẩ
2. Li t kê danh sách các customers. Thông tin bao g m CustomerID,ệ ồ
CompanyName, City, Phone.
3. Li t kê danh sách các products. Thông tin bao g m ProductId,ệ ồ
ProductName, UnitPrice.
4. Li t kê danh sách các employees. Thông tin bao g m EmployeeId,ệ ồ
EmployeeName, Phone, Age. Trong đó EmployeeName đ c ghép tượ ừ
LastName và FirstName; Age là tu i đ c tính t năm hi n hànhổ ượ ừ ệ
(GetDate()) và năm c a Birthdate. ủ
5. Li t kê danh sách các Customers có ContactTitle b t đ u b ng ch O ệ ắ ầ ằ ữ
6. Danh sách các customers thành ph Paris. ở ố
7. Li t kê danh sách Customers thành ph LonDon, Boise và Paris ệ ở ố
8. Li t kê danh sách Customers có tên b t đ u b ng ch V mà thành phệ ắ ầ ằ ữ ở ố
Lyon
9. Li t kê danh sách các Customers không có s fax ệ ố
10. Li t kê danh sách các Customers có s Fax ệ ố
11. Li t kê danh sách employees có năm sinh <=1960.ệ
12. Li t kê danh sách các products có t ‘Boxes’ trong c t QuantityPerUnit. ệ ừ ộ
13. Li t kê danh sách các products có Unitprice l n h n 10 và nh h n 15. ệ ớ ớ ỏ ơ
14. Li t kê danh sách các orders có OrderDate đ c l p trong tháng 9 nămệ ượ ậ
1996.
15. Li t kê danh sách các products ng v i ti n t n v n. Thông tin bao g mệ ứ ớ ề ồ ố ồ
ProductId, ProductName, Unitprice, UnitsInStock, TotalAccount. Trong đó
TotalAccount= UnitsInStock * Unitprice.
16. Li t kê danh sách 5 customers có city b t đ u ‘M’. ệ ắ ầ
17. Li t kê danh sách 2 employees có tu i l n nh t. Thông tin bao g mệ ổ ớ ấ ồ
EmployeeID, EmployeeName, Age. Trong đó, EmployeeName đ c ghépượ
t LastName và FirstName; Age là năm hi n hành tr năm sinh. ừ ệ ừ
lOMoARcPSD|45470709
18. Li t kê danh sách các Products có s l ng t n nh h n 5 ệ ố ượ ồ ỏ ơ
19. Li t kê danh sách các Orders g m OrderId, Productid, Quantity, Unitprice,ệ ồ
Discount, ToTal = Quantity * unitPrice – 20%*Discount.
20. Li t kê danh sách các Employees không thành ph London và Redmond ệ ở ố
BÀI T P 2: L NH SELECT – TRUY V N CÓ K T N I Ậ Ệ Ấ Ế Ố
1. Li t kê các customer không có l p hóa đ n trong tháng 7/1997 ệ ậ ơ
2. Li t kê các customer có l p hóa đ n trong 15 ngày đ u tiên c a 7/1997 ệ ậ ơ ầ ủ
3. Li t kê danh sách các s n ph m đ c giao vào ngày 16/7/1996 ệ ả ẩ ượ
4. Li t kê danh sách các hóa đ n c a các Customers mua hàng trong tháng 4,ệ ơ ủ
9 c a năm 1997. Thông tin g m Orderid, CompanyName, OrderDate,ủ ồ
RequiredDate, đ c s p x p theo CompanyName, cùng Companyname thìượ ắ ế
theo OrderDate gi m d n. ả ầ
5. Li t kê danh sách các hóa đ n do nhân viên có Lastname là Fuller l p. ệ ơ ậ
6. Li t kê danh sách các Products do nhà cung c p (supplier) có mã 1,3,6 bánệ ấ
đ c trong tháng 6,7 c a năm 1997, đ c s p x p theo mã nhà cung c pượ ủ ượ ắ ế ấ
(SupplierID), cùng mã nhà cung c p thì s p x p theo ProductID. ấ ắ ế
7. Li t kê danh sách các Products đã bán có Discount là 1 ệ
8. Li t kê danh sách các Products có đ n giá bán b ng đ n giá mua. ệ ơ ằ ơ
9. Li t kê danh sách các Products mà hóa đ n có OrderID là 10248 đã mua. ệ ơ
10.Li t kê danh sách các Employers đã l p các hóa đ n trong tháng 7 c a nămệ ậ ơ ủ
1996
11.Li t kê danh sách các s n ph m Products ch a bán đ c trong tháng 6ệ ả ẩ ư ượ
năm 1996
12.Li t kê danh sách các Employes không l p hóa đ n vào ngày hôm nay ệ ậ ơ
13.Li t kê danh sách các Customers ch a mua hàng trong năm 1997 ệ ư
14.Li t kê danh sách các nhà cung c p Suppliers không cung c p Products cóệ ấ ấ
mã là 59.
15.Tìm t t c các Customers mua các s n ph m có tên b t đ u b ng ch Tấ ả ả ẩ ắ ầ ằ ữ
trong tháng 7.
BÀI T P 3: L NH SELECT – TRUY V N GOM NHÓM Ậ Ệ Ấ
1. Danh sách các orders ng v i t ng ti n c a t ng hóa đ n. Thông tin baoứ ớ ổ ề ủ ừ ơ
g m OrdersId, OrderDate, TotalAccount. Trong đó TotalAccount là Sumồ
c a Quantity * Unitprice, k t nhóm theo OrderId. ủ ế
2. Danh sách các orders ng v i t ng ti n c a t ng hóa đ n có Shipcity làứ ớ ổ ề ủ ừ ơ
‘Madrid’. Thông tin bao g m OrdersId, OrderDate, TotalAccount. Trong đóồ
TotalAccount là Sum c a Quantity * Unitprice, k t nhóm theo OrderId. ủ ế
3. Danh sách các products có t ng s l ng l p hóa đ n l n nh t. ổ ố ượ ậ ơ ớ ấ
4. Cho bi t m i customers đã l p bao nhiêu hóa đ n. Thông tin g mế ỗ ậ ơ ồ
CustomerID, CompanyName, CountOfOrder. Trong đó CountOfOrder (t ngổ
s hóa đ n) đ c đ m (Count) theo t ng Customers. ố ơ ượ ế ừ
5. Cho bi t m i Employee đã l p đ c bao nhiêu hóa đ n, ng v i t ng ti n. ế ỗ ậ ượ ơ ứ ớ ổ ề
lOMoARcPSD|45470709
6. Danh sách các customer ng v i t ng ti n các hoá đ n đ c l p tứ ớ ổ ề ơ ượ ậ ừ
31/12/1996 đ n 1/1/1998. ế
7. Danh sách các customer ng v i t ng ti n các hoá đ n, mà các hóa đ nứ ớ ổ ề ơ ơ
đ c l p t 31/12/1996 đ n 1/1/1998 và t ng ti n các hóa đ n >20000. ượ ậ ừ ế ổ ề ơ
8. Danh sách các customer ng v i t ng s hoá đ n, t ng ti n các hoá đ n,ứ ớ ổ ố ơ ổ ề ơ
mà các hóa đ n đ c l p t 31/12/1996 đ n 1/1/1998 và t ng ti n cácơ ượ ậ ừ ế ổ ề
hóa đ n >20000. Thông tin đ c s p x p theo CustomerID, cùng mã thìơ ượ ắ ế
s p x p theo t ng ti n gi m d n. ắ ế ổ ề ả ầ
9. Danh sách các Category có t ng s l ng t n (UnitsInStock) l n h n 300,ổ ố ượ ồ ớ ơ
đ n giá trung bình nh h n 25. Thông tin k t qu bao g m CategoryID,ơ ỏ ơ ế ả ồ
CategoryName, Total_UnitsInStock, Average_Unitprice.
10.Danh sách các Category có t ng s product l n h n 10. Thông tin k t quổ ố ớ ớ ế ả
bao g m CategoryID, CategoryName, Total_UnitsInStock. ồ
11.Danh sách các product theo t ng CategoryName, thông tin bao g m:ừ ồ
Productname, CategoryName, Unitprice, UnitsinStock. Có dùng m nh đệ ề
COMPUTE đ li t kê đ n giá trung bình, t ng s l ng t n (sum ofể ệ ơ ổ ố ượ ồ
UnitsinStock) theo t ng CategoryName. ừ
12.Danh sách các Customer ng v i t ng ti n c a các hóa đ n t ng tháng.ứ ớ ổ ề ủ ơ ở ừ
Thông tin bao g m CustomerID, CompanyName, Month_Year, Total. Trongồ
đó Month_year là tháng và năm l p hóa đ n, Total là t ng c a Unitprice*ậ ơ ổ ủ
Quantity, có th ng k t ng c a total theo t ng Customer và Month_Year .ố ế ổ ủ ừ
(có dùng COMPUTE)
13.Cho bi t Employees nào bán đ c nhi u ti n nh t trong 7 c a năm 1997ế ượ ề ề ấ ủ
14. Danh sách 3 khách có nhi u đ n hàng nh t c a năm 1996. ề ơ ấ ủ
15. Cho bi t khách hàng nào có s l n mua hàng l n h n 10 trong năm 1997. ế ố ầ ớ ơ
BÀI T P 4: L NH SELECT – TRUY V N CON SUBQUERY Ậ Ệ Ấ
1. Các product có đ n giá l n h n đ n giá trung bình c a các product. ơ ớ ơ ơ ủ
2. Các product có đ n giá l n h n đ n giá trung bình c a các product cóơ ớ ơ ơ ủ
ProductName b t đ u là ‘N’ ắ ầ
3. Cho bi t nh ng s n ph m có tên b t đ u b ng ch N và đ n giá > đ n giáế ữ ả ẩ ắ ầ ằ ữ ơ ơ
c a s n ph m khác ủ ả ẩ
4. Danh sách các products đã có khách hàng đ t hàng (t c là ProductId cóặ ứ
trong Order Details). Thông tin bao g m ProductId, ProductName,ồ
Unitprice
5. Danh sách các products có đ n giá nh p l n h n đ n giá bán nh nh t c aơ ậ ớ ơ ơ ỏ ấ ủ
t t c các Products ấ ả
6. Danh sách các hóa đ n c a nh ng Customers mà Customers thành phơ ủ ữ ở ố
LonDon và Madrid.
7. Danh sách các products có đ n v tính có ch Box và có đ n giá mua nhơ ị ữ ơ ỏ
h n đ n giá trung bình c a t t c các Products. ơ ơ ủ ấ ả
8. Danh sách các Products có s l ng (Quantity) bán đ c l n nh t. ố ượ ượ ớ ấ
lOMoARcPSD|45470709
9. Danh sách các Customer ch a t ng l p hóa đ n (vi t b ng ba cách: dùngư ừ ậ ơ ế ằ
NOT EXISTS, dùng LEFT JOIN, dùng NOT IN )
10.Cho bi t các s n ph m có đ n giá bán cao nh t ế ả ẩ ơ ấ
11.Cho bi t các s n ph m có đ n v tính có ch a ch box và có đ n giá bánế ả ẩ ơ ị ứ ữ ơ
cao nh t ấ
12.Danh sách các products có đ n giá bán l n h n đ n giá bán trung bình c aơ ớ ơ ơ ủ
các Products có ProductId<=5
13.Danh sách các Customers và các Employees không cùng thành ph ở ố
14.Cho bi t nh ng s n ph m nào có t ng s l ng bán đ c l n h n sế ữ ả ẩ ổ ố ượ ượ ớ ơ ố
l ng trung bình bán ra ượ
15.Li t kê danh sách các khách hàng mua các hóa đ n mà các hóa đ n này chệ ơ ơ ỉ
mua nh ng s n ph m có mã >=3 ữ ả ẩ
16.Li t kê danh sách các khách hàng mà các khách hàng này mua các hóa đ nệ ơ
do các nhân viên l p hóa đ n cùng thành ph v i khách hàng ậ ơ ở ố ớ
17.Danh sách các Customers mà các Customers đã mua hàng trong tháng 7, 9
năm 1997
18.Danh sách các City có nhi u h n 3 customer. ề ơ
19.Tìm t t c các Customers mua ít nh t 2 đ n hàng. ấ ả ấ ơ
20.B n hãy đ a ra câu h i cho 3 câu truy v n sau: ạ ư ỏ ấ
Select ProductId, ProductName, UnitPrice from [Products]
Where Unitprice>ALL (Select Unitprice from [Products] where
ProductName like ‘B%’)
Select ProductId, ProductName, UnitPrice from [Products]
Where Unitprice>ANY (Select Unitprice from [Products] where
ProductName like ‘B%’)
Select ProductId, ProductName, UnitPrice from [Products]
Where Unitprice=ANY (Select Unitprice from [Products] where
ProductName like ‘B%’)
BÀI T P 5: L NH SELECT – CÁC LO I TRUY V N KHÁC Ậ Ệ Ạ Ấ
1. Li t kê danh sách các City có Customers ho c Employee (dùng Union) ệ ặ
2. Li t kê danh sách các Country có Customers ho c Employee (dùng Union) ệ ặ
3. K t danh sách các Customer và Employee l i v i nhau. Thông tin g m ế ạ ớ ồ
CodeID, Name, Address, Phone. Trong đó
CodeID là CustomerID/EmployeeID, Name là
Companyname/LastName + FirstName, Phone là Homephone.
4. Danh sách các Suppliers cung c p t t c các m t hàng. ấ ấ ả ặ
5. Danh sách các Customers đã mua t t c các m t hàngấ ả ặ
lOMoARcPSD|45470709
BÀI T P TU N 4 Ậ Ầ
1. T o view vw_Products_Info hi n th danh sách các s n ph m t b ng Productsạ ể ị ả ẩ ừ ả
và b ng Categories. Thông tin bao g m CategoryName, Description,ả ồ
ProductName, QuantityPerUnit, UnitPrice, UnitsInStock
2. T o view List_Product_view ch a danh sách các s n ph m d ng h p (box) cóạ ứ ả ẩ ạ ộ
đ n giá > 16, thông tin g m ProductID, ProductName, UnitPrice,ơ ồ
QuantityPerUnit, COUNT of OrderID
3. T o view vw_CustomerTotals hi n th t ng ti n bán đ c t m i khách hàngạ ể ị ổ ề ượ ừ ỗ
theo tháng và theo năm. Thông tin g m CustomerID, YEAR(OrderDate) ASồ
OrderYear, MONTH(OrderDate) AS OrderMonth, SUM(UnitPrice*Quantity).
4. T o view tr v t ng s l ng s n ph m bán đ c c a m i nhân viênạ ả ề ổ ố ượ ả ẩ ượ ủ ỗ
(Employee) theo t ng năm. Thông tin g m EmployeeID, OrderYear,ừ ồ
sumOfOrderQuantity
5. T o view ListCustomer_view ch a danh sách các khách hàng có trên 5 hóa đ nạ ứ ơ
đ t hàng t năm 1997 đ n 1998, thông tin g m mã khách (CustomerID) , h tênặ ừ ế ồ ọ
(CompanyName), S hóa đ n (CountOfOrders). ố ơ
6. T o view ListProduct_view ch a danh sách nh ng s n ph m nhóm Beveragesạ ứ ữ ả ẩ
và Seafood có t ng s l ng bán trong m i năm trên 30 s n ph m, thông tinổ ố ượ ỗ ả ẩ
g m CategoryName, ProductName, Year, SumOfOrderQuantity. ồ
7. T o view vw_OrderSummary v i t khóa WITH ENCRYPTION g m OrderYearạ ớ ừ ồ
(năm c a ngày l p hóa đ n), OrderMonth (tháng c a ngày l p hóa đ n),ủ ậ ơ ủ ậ ơ
OrderTotal (t ng ti n, =UnitPrice*Quantity). Sau đó xem thông tin và tr giúpổ ề ợ
v mã l nh c a view này ề ệ ủ
8. T o view vwProducts v i t khóa WITH SCHEMABINDING g m ProductID,ạ ớ ừ ồ
ProductName, Discount. Xem thông tin c a View. Xóa c t Discount. Có xóa đ củ ộ ượ
không? Vì sao?
9. T o view vw_Customer v i t khóa WITH CHECK OPTION ch ch a các kháchạ ớ ừ ỉ ứ
hàng thành ph London và Madrid, thông tin g m: CustomerID,ở ố ồ
CompanyName,
City.
a. Chèn thêm m t khách hàng m i không thành ph London và Madridộ ớ ở ố
thông qua view v a t o. Có chèn đ c không? Gi i thích. ừ ạ ượ ả
b. Chèn thêm m t khách hàng m i thành ph London và m t khách hàngộ ớ ở ố ộ
m i thành ph Madrid. Dùng câu l nh select trên b ng Customers đ xemớ ở ố ệ ả ể
k t qu .ế ả
lOMoARcPSD|45470709
BÀI T P TU N 5-6 Ậ Ầ
I) Batch
1. Vi t m t batch khai báo bi n @tongsoHD ch a t ng s hóa đ n c a s nế ộ ế ứ ổ ố ơ ủ ả
ph m có ProductID=’31’, n u @tongsoHD>50 thì in ra chu i “S n ph mẩ ế ỗ ả ẩ
31 có trên 50 đ n hàng”, ng c l i tin ra chu i “S n ph m 31 có ít đ nơ ượ ạ ỗ ả ẩ ơ
đ t hàng” ặ
2. Vi t m t đo n Batch v i tham s @makh và @n, ch a s hóa đ n @n c aế ộ ạ ớ ố ứ ố ơ ủ
khách hàng @makh, tham s @nam ch a năm l p hóa đ n (ví dố ứ ậ ơ ụ
@nam=1996), n u @n>0 thì in ra chu i:”Khách hàng có @n hóa đ nế ỗ ơ
trong năm 1996”, ng c l i n u @n=0 thì in ra chu i “Khách hàng khôngượ ạ ế ỗ
có hóa đ n nào trong năm 1996” ơ
3. Vi t m t batch tính s ti n gi m cho nh ng hóa đ n (OrderID) có t ngế ộ ố ề ả ữ ơ ổ
ti n>500, thông tin g m OrderID, TongTien=sum(UnitPrice*Quantity),ề ồ
Ti n gi m, v i Ti n gi m đ c tính nh sau: ề ả ớ ề ả ượ ư
• Nh ng hóa đ n có TongTien <500 thì không gi m, ữ ơ ả
• TongTien t 500 đ n <5000 thì gi m 5% c a TongTien ừ ế ả ủ
• TongTien t 5000 đ n <10000 thì gi m 10% c a TongTien ừ ế ả ủ
• TongTien t 10000 tr lên thì gi m 15% c a TongTien ừ ở ả ủ
(G i ý: Dùng c u trúc Case… when …then …) ợ ấ
4. Vi t m t Batch v i 3 tham s : @MaNCC, @MaSP, @SoLuongCC, ch a giáế ộ ớ ố ứ
tr c a các field SupplierID, ProductID,Quantity, v i giá tr truy n cho cácị ủ ớ ị ề
bi n @MaNCC, @MaSP (vd: @MaNCC =5, @MaSP =11, thì ch ng trình sẽế ươ
gán giá tr t ng ng c a field Quantity cho bi n @SoLuongCC, n uị ươ ứ ủ ế ế
@SoLuongCC tr v giá tr là null thì in ra chu i “Nha cung cap 5 khongả ề ị ỗ
cung cap san pham 11”, ng c l i (vd: @SoLuongCC =12) thì in chu iượ ạ ỗ
“Nha cung cap 5 cung cap san pham 11 v i s l ng là 12” ớ ố ượ
5. Vi t m t batch th c hi n tăng đ n giá (UnitPrice) trung bình c a đ nế ộ ự ệ ơ ủ ơ
hàng (Orders) theo đi u ki n sau: Khi trung bình c a đ n giá trong đ nề ệ ủ ơ ơ
hàng <
50 thì c p nh t tăng đ n giá c a đ n hàng lên 10%, n u sau khi c p nh tậ ậ ơ ủ ơ ế ậ ậ
mà đ n giá l n nh t c a đ n hàng >300 thì d ng. ơ ớ ấ ủ ơ ừ
M
ụ
c tiêu:
Hi
ể
u và bi
ế
t cách l
ậ
p trình trong SQL
−
Vi
ế
t các batch
T
ạ
o và th
ự
c thi các lo
ạ
i function và stored procedure
−
Function g
ồ
m 3 lo
ạ
i:
o
Scalar function
o
Table valued Function
o
Multi Statement table valued Function
−
Stored Procedure
o
Tham s
ố
input và output
lOMoARcPSD|45470709
II) Function: (Nh ki m tra t ng hàm sau khi làm xong, b ng cách g i hàm vàớ ể ừ ằ ọ
truy n tham s )ề ố
− Scalar Function:
1. Vi t hàm tên ế CountOfProducts (d ng scalar function) v i tham sạ ớ ố
@MaNhom, giá tr truy n vào l y t field CategoryID, hàm tr v s s nị ề ấ ừ ả ề ố ả
ph m t ng ng v i mã nhóm hàng. Áp d ng hàm đã vi t vào câu truy v nẩ ươ ứ ớ ụ ế ấ
li t kê danh sách các nhóm hàng cùng v i s s n ph m thu c m i nhóm,ệ ớ ố ả ẩ ộ ỗ
thông tin g m: CategoryID, CategoryName, CountOfProduct.ồ
2. Vi t hàm tên là ế InstockProd (d ng scalar function) v i tham s vào làạ ớ ố
@ProductID và @SupplierID. Hàm tr v s l ng t n kho (UnitInStock)ả ề ố ượ ồ
c a s n ph m @ProductID do nhà cung c p @SupplierID cung c p. ủ ả ẩ ấ ấ
3. Vi t hàm tên ế SalesOfEmp (d ng scalar function) tr v t ng doanh thu bánạ ả ề ổ
hàng (SUM(UnitPrice*Quantity)) c a m t nhân viên trong m t tháng tùy ýủ ộ ộ
và trong m t năm tùy ý, v i tham s vào @EmployeeID, @MonthOrder,ộ ớ ố
@YearOrder
− Table Valued Functions
1. Vi t hàm ế SumofOrder v i hai tham s @thang và @nam, tr v danh sáchớ ố ả ề
các hóa đ n (OrderID) l p trong tháng và năm đ c truy n vào t 2 thamơ ậ ượ ề ừ
s @thang và @nam, có t ng ti n >5000, thông tin g m OrderID,ố ổ ề ồ
OrderDate, SubTotal, trong đó SubTotal =sum(Quantity*UnitPrice).
2. Vi t hàm tên ế SumOfProduct v i tham s đ u vào là ớ ố ầ @MaNCC
(SupplierID), hàm dùng đ tính t ng s l ng (ể ổ ố ượ SumOfQuantity) và chi tế
kh u cao nh t (ấ ấ MaxOfDiscount) c a các s n ph m do nhà cung c pủ ả ẩ ấ
@MaNCC cung c p, thông tin g m ấ ồ ProductID, SumOfQuantity,
MaxOfDiscount
3. Vi t hàm tên ế Discount_Func tính s ti n gi m trên các hóa đ n (OrderID),ố ề ả ơ
thông tin g m OrderID, Quantity, Discount, trong đó, Discount đ c tínhồ ượ
nh sau: ư
N u Quantity< 10 thì Discount=0, ế
N u 10<= Quantity <30 thì Discount = 5% [ế UnitPrice*Quantity] N uế
30<= Quantity <50 thì Discount = 10%[UnitPrice*Quantity]
N u Quantity >=50 thì Discount = 15% [ế UnitPrice*Quantity]
G i ý: S d ng Case When …Then … ợ ử ụ
Select Discount=
Case
when Quantity <10 then 0 when Quantity >=10 and Quantity <30
then 5%* (UnitPrice*Quantity) when Quantity >=30 and Quantity <50
then 10% *(UnitPrice*Quantity) else 15% *(UnitPrice*Quantity)
End
From [Order Details]
4. Vi t hàm ế TotalOfEmp v i tham s @MonthOrder, @YearOrder đ tínhớ ố ể
t ng doanh thu c a các nhân viên trong tháng và năm đ c truy n vào 2ổ ủ ượ ề

Tài liệu khác của Đại học Công nghiệp Thành phố Hồ Chí Minh

Tài liệu liên quan: