Baøi thöïc haønh SQL Server
BÀI TH C HÀNH SQL SERVER S 1.Ự Ố
Bài 1:
1. T o các table sau: (các field có g ch chân là field khoá, không t o khoá ngo i mà s đ c th c hi nạ ạ ạ ạ ẽ ượ ự ệ
vào ph n trigger)ầ
Table: KHACHHANG
Name Type Size
MAKH Nvarchar 5
TENKH Nvarchar 30
DIACHI Nvarchar 50
DT Nvarchar 10
EMAIL Nvarchar 30
Ràng bu c: ộTENKH not null, DT có th 8 ch s ho c 10 ch s . ể ữ ố ặ ử ố
Table: VATTU
Name Type Size
MAVT Nvarchar 5
TENVT Nvarchar 30
DVT Nvarchar 20
GIAMUA Number 20
SLTON Number 20
Ràng bu c:ộ TENVT not null, GIAMUA >0, SLTON >=0.
Table: HOADON
Name Type Size
MAHD Nvarchar 10
NGAY Date/Time
MAKH Nvarchar 5
TONGTG Number 20
Ràng bu c: ộGiá tr nh p vào cho field NGAY ph i tr c ngày hi n hành; .ị ậ ả ướ ệ
Table: CHITIETHOADON
Name Type Size
MAHD Nvarchar 10
MAVT Nvarchar 5
SL Number 20
KHUYENMAI Number 20
GIABAN Number 20
Ràng bu c: ộGiá tr nh p vào cho field Sl ph i l n h n 0ị ậ ả ớ ơ
2./ Nh p d li u vào các table sauậ ữ ệ
Table VATTU:
Mã v t tậ ư Tên v t tậ ư đ n v tínhơ ị Gia mua SLTON
VT01 XI MANG BAO 50000 5000
VT02 CAT KHOI 45000 50000
VT03 GACH ONG VIEN 120 800000
VT04 GACH THE VIEN 110 800000
VT05 DA LON KHOI 25000 100000
VT06 DA NHO KHOI 33000 100000
VT07 LAM GIO CAI 15000 50000
1
Baøi thöïc haønh SQL Server
Table KHACHHANG:
MAKH TENKH DIACHI DT EMAIL
KH01 NGUYEN THI BE TAN BINH 8457895 bnt@yahoo.com
KH02 LE HOANG NAM BINH CHANH 9878987 namlehoang @abc.com.vn
KH03 TRAN THI CHIEU TAN BINH 8457895
KH04 MAI THI QUE ANH BINH CHANH
KH05 LE VAN SANG QUAN 10 sanglv@hcm.vnn.vn
KH06 TRAN HOANG KHAI TAN BINH 8457897
Table HOADON:
MAHD NGAY MAKH
HD001 12/05/2000 KH01
HD002 25/05/2000 KH02
HD003 25/05/2000 KH01
HD004 25/05/2000 KH04
HD005 26/05/2000 KH04
HD006 02/06/2000 KH03
HD007 22/06/2000 KH04
HD008 25/06/2000 KH03
HD009 15/08/2000 KH04
HD010 30/09/2000 KH01
HD011 27/12/2000 KH06
HD012 27/12/2000 KH01
Table CHITIETHOADON:
MAHD MAVT SL GIABAN
HD001 VT01 5 52000
HD001 VT05 10 30000
HD002 VT03 10000 150
HD003 VT02 20 55000
HD004 VT03 50000 150
HD004 VT04 20000 120
HD005 VT05 10 30000
HD005 VT06 15 35000
HD005 VT07 20 17000
HD006 VT04 10000 120
HD007 VT04 20000 125
HD008 VT01 100 55000
HD008 VT02 20 47000
HD009 VT02 25 48000
HD010 VT01 25 57000
HD011 VT01 20 55000
HD011 VT02 20 45000
HD012 VT01 20 55000
HD012 VT02 10 48000
HD012 VT03 10000 150
Câu 3: T o các view sauạ:
1. Hi n danh sách t t c các khách hàng g m mã khách hàng, tên khách hàng, đ a ch , đi nể ấ ả ồ ị ỉ ệ
tho i, và đ a ch E-mail.ạ ị ỉ
2
Baøi thöïc haønh SQL Server
2. Hi n danh sách các khách hàng có đ a ch là “TAN BINH” g m mã khách hàng, tên kháchể ị ỉ ồ
hàng, đ a ch , đi n tho i, và đ a ch E-mail.ị ỉ ệ ạ ị ỉ
3. Hi n danh sách các khách hàng có đ a ch là “BINH CHANH” g m mã khách hàng, tênể ị ỉ ồ
khách hàng, đ a ch , đi n tho i, và đ a ch E-mail.ị ỉ ệ ạ ị ỉ
4. Hi n danh sách các khách hàng g m các thông tin mã khách hàng, tên khách hàng, đ a chể ồ ị ỉ và
đ a ch E-mail c a nh ng khách hàng ch a có s đi n tho iị ỉ ủ ữ ư ố ệ ạ
5. Hi n danh sách các khách hàng ch a có s đi n tho i và cũng ch a có đ a ch Email g mể ư ố ệ ạ ư ị ỉ ồ
mã khách hàng, tên khách hàng, đ a ch .ị ỉ
6. Hi n danh sách các khách hàng đã có s đi n tho i và đ a ch E-mail g m mã khách hàng,ể ố ệ ạ ị ỉ ồ
tên khách hàng, đ a ch , đi n tho i, và đ a ch E-mail.ị ỉ ệ ạ ị ỉ
7. Hi n danh sách t t c các v t t g m mã v t t , tên v t t , đ n v tính và giá mua.ể ấ ả ậ ư ồ ậ ư ậ ư ơ ị
8. Hi n danh sách các v t t có đ n v tính là “CAI” g m mã v t t , tên v t t và giá mua.ể ậ ư ơ ị ồ ậ ư ậ ư
9. Hi n danh sách các v t t g m mã v t t , tên v t t , đ n v tính và giá mua mà có giá muaể ậ ư ồ ậ ư ậ ư ơ ị
trên 25000.
10. Hi n danh sách các v t t là “G CH” (bao g m các lo i g ch) g m mã v t t , tên v tể ậ ư Ạ ồ ạ ạ ồ ậ ư ậ
t ,ư đ n v tính và giá mua . ơ ị
11. Hi n danh sách các v t t g m mã v t t , tên v t t , đ n v tính và giá mua mà có giá muaể ậ ư ồ ậ ư ậ ư ơ ị
n m trong kho ng t 20000 đ n 40000.ằ ả ừ ế
12. T o query đ l y ra các thông tin g m Mã hoá đ n, ngày l p hoá đ n, tên khách hàng, đ aạ ể ấ ồ ơ ậ ơ ị
ch khách hàng và s đi n tho i.ỉ ố ệ ạ
13. T o query đ l y ra các thông tin g m Mã hoá đ n, tên khách hàng, đ a ch khách hàngạ ể ấ ồ ơ ị ỉ
và s đi n tho i c a ngày 25/5/2000.ố ệ ạ ủ
14. T o query đ l y ra các thông tin g m Mã hoá đ n, ngày l p hoá đ n, tên kháchạ ể ấ ồ ơ ậ ơ hàng,
đ a ch khách hàng và s đi n tho i c a nh ng hoá đ n trong tháng 6/2000.ị ỉ ố ệ ạ ủ ữ ơ
15. T o query đ l y ra các thông tin g m Mã hoá đ n, ngày l p hoá đ n, tên khách hàng, đ aạ ể ấ ồ ơ ậ ơ ị
ch khách hàng và s đi n tho i.ỉ ố ệ ạ
16. L y ra danh sách nh ng khách hàng (tên khách hàng, đ a ch , s đi n tho i) đã mua hàngấ ữ ị ỉ ố ệ ạ
trong tháng 6/2000.
17. L y ra danh sách nh ng khách hàng không mua hàng trong tháng 6/2000 g m các thông tinấ ữ ồ
tên khách hàng, đ a ch , s đi n tho i.ị ỉ ố ệ ạ
18. T o query đ l y ra các thông tin g m các thông tin mã hóa đ n, ,mã v t t , tên v t t ,ạ ể ấ ồ ơ ậ ư ậ ư
đ n v tính, giá bán, giá mua, s l ng , tr giá mua (giá mua * s l ng), tr giá bán , ( giáơ ị ố ượ ị ố ượ ị
bán * s l ng).ố ượ
19. T o query đ l y ra các chi ti t hoá đ n g m các thông tin mã hóa đ n, ,mã v t t , tên v tạ ể ấ ế ơ ồ ơ ậ ư ậ
t , đ n v tính, giá bán, giá mua, s l ng , tr giá mua (giá mua * s l ng), tr giá bán ,ư ơ ị ố ượ ị ố ượ ị
( giá bán * s l ng) mà có giá bán l n h n ho c b ng giá mua.ố ượ ớ ơ ặ ằ
20. T o query đ l y ra các thông tin g m mã hóa đ n, ,mã v t t , tên v t t , đ n v tính,ạ ể ấ ồ ơ ậ ư ậ ư ơ ị
giá bán, giá mua, s l ng , tr giá mua (giá mua * s l ng), tr giá bán , ( giá bán * số ượ ị ố ượ ị ố
l ng) và c t khuy n mãi v i khuy n mãi 10% cho nh ng m t hàng bán trong m t hóaượ ộ ế ớ ế ữ ặ ộ
đ n l n h n 100ơ ơ ơ .
21. Tìm ra nh ng m t hàng ch a bán đ c.ữ ặ ư ượ
22. T o b ng t ng h p g m các thông tin: mã hóa đ n, ngày hoá đ n, tên khách hàng, đ a ch ,ạ ả ổ ợ ồ ơ ơ ị ỉ
s đi n tho i, tên v t t , đ n v tính, giá mua, giá bán, s l ng, tr giá mua, tr giá bán. ố ệ ạ ậ ư ơ ị ố ượ ị ị
23. T o b ng t ng h p c a tháng 5/2000 g m các thông tin: mã hóa đ n, ngày hoá đ n, tênạ ả ổ ợ ủ ồ ơ ơ
khách hàng, đ a ch , s đi n tho i, tên v t t , đ n v tính, giá mua, giá bán, s l ng, tr giáị ỉ ố ệ ạ ậ ư ơ ị ố ượ ị
mua, tr giá bán. ị
24. T o b ng t ng h p c a tháng 6/2000 g m các thông tin: mã hóa đ n, ngày hoá đ n, tênạ ả ổ ợ ủ ồ ơ ơ
khách hàng, đ a ch , s đi n tho i, tên v t t , đ n v tính, giá mua, giá bán, s l ng, tr giáị ỉ ố ệ ạ ậ ư ơ ị ố ượ ị
mua, tr giá bán. ị
25. T o b ng t ng h p c a quý 1 năm 2000 g m các thông tin: mã hóa đ n, ngày hoá đ n, tênạ ả ổ ợ ủ ồ ơ ơ
khách hàng, đ a ch , s đi n tho i, tên v t t , đ n v tính, giá mua, giá bán, s l ng, tr giáị ỉ ố ệ ạ ậ ư ơ ị ố ượ ị
mua, tr giá bán. ị
26. L y ra danh sách các hoá đ n g m các thông tin: S hoá đ n, ngày, tên khách hàng, đ a chấ ơ ồ ố ơ ị ỉ
khách hàng, t ng tr giá c a hoá đ n.ổ ị ủ ơ
3
Baøi thöïc haønh SQL Server
27. L y ra hoá đ n có t ng tr giá l n nh t g m các thông tin: S hoá đ n, ngày, tên kháchấ ơ ổ ị ớ ấ ồ ố ơ
hàng, đ a ch khách hàng, t ng tr giá c a hoá đ n.ị ỉ ổ ị ủ ơ
28. L y ra hoá đ n có t ng tr giá l n nh t trong tháng 5/2000 g m các thông tin: S hoá đ n,ấ ơ ổ ị ớ ấ ồ ố ơ
ngày, tên khách hàng, đ a ch khách hàng, t ng tr giá c a hoá đ n.ị ỉ ổ ị ủ ơ
29. L y ra hoá đ n có t ng tr giá nh nh t g m các thông tin: S hoá đ n, ngày, tên kháchấ ơ ổ ị ỏ ấ ồ ố ơ
hàng, đ a ch khách hàng, t ng tr giá c a hoá đ n.ị ỉ ổ ị ủ ơ
30. Đ m xem m i khách hàng có bao nhiêu hoá đ n.ế ỗ ơ
31. L y ra các thông tin c a khách hàng có s l ng hoá đ n mua hàng nhi u nh t.ấ ủ ố ượ ơ ề ấ
32. L y ra các thông tin c a khách hàng có s l ng hàng mua nhi u nh t.ấ ủ ố ượ ề ấ
33. L y ra các thông tin v các m t hàng mà đ c bán trong nhi u hoá đ n nh t.ấ ề ặ ượ ề ơ ấ
34. L y ra các thông tin v các m t hàng mà đ c bán nhi u nh t.ấ ề ặ ượ ề ấ
35. L y ra danh sách t t c các khách hàng g m Mã khách hàng, tên khách hàng, đ a ch , sấ ấ ả ồ ị ỉ ố
l ng hoá đ n đã mua (n u khách hàng đó ch a mua hàng thì c t s l ng hoá đ n đượ ơ ế ư ộ ố ượ ơ ể
tr ng)ố
Câu 4: T o các function sau:ạ
1. Vi t hàm tính doanh thu cu năm.. v i năm là tham s truy n vào.ế ả ớ ố ề
2. Vi t hàm tính doanh thu cu tháng .. v i tháng là tham s truy n vào.ế ả ớ ố ề
3. Vi t hàm tính doanh thu c a khách hàng v i mã khách hàng là tham s truy n vào.ế ủ ớ ố ề
4. Vi t hàm tính t ng s l ng bán đ c cho t ng m t hàng theo tháng v i mã hàng và thàngế ổ ố ượ ượ ừ ặ ớ
nh p vào, n u tháng không nh p vào t c là tính t t c các tháng.ậ ế ậ ứ ấ ả
5. Vi t hàm tính lãi ((giá bán – tr giá mua )* sl l ng bán đ c ) cho t ng m t hàng, v iế ừ ố ượ ượ ừ ặ ớ
mã m t hàng là tham s truy n vào. N u mã m t hàng không truy n vào thì tính cho t t cặ ố ề ế ặ ề ấ ả
các m t hàng.ặ
Câu 5: T o các procedure sau:ạ
1. L y ra danh các khách hàng đã mua hàng trong ngày [ngày]…. V i [ngày] là tham s truy nấ ớ ố ề
vào.
2. L y ra danh sách 5 khách hàng có t ng tr giá các đ n hàng l n nh t.ấ ổ ị ơ ớ ấ
3. L y ra danh sách 10 m t hàng có s l ng bán l n nh t.ấ ặ ố ượ ớ ấ
4. L y ra danh sách 10 m t hàng bán ra có lãi 1t nh t.ấ ặ ấ
5. Tính giá tr cho c t khuy n mãi nh sau: Khuy n mãi 5% n u SL >100, 10% n u SL>500.ị ộ ế ư ế ế ế
6. Tính s l i s l ng t n cho t t c các m t hàng. (SLTON = SLTON – t ng sl bán đ c)ố ạ ố ượ ồ ấ ả ặ ổ ượ
7. Tính tr giá cho m i hoá đ n.ị ỗ ơ
8. T o ra table KH_VIP có c u trúc gi ng v i c u trúc table KHACHHANG. L u các kháchạ ấ ố ớ ấ ư
hàng có t ng tr giá c a t t c các đ n hàng >=10000000 vào table KH_VIP.ổ ị ủ ấ ả ơ
Câu 6: T o các trigger đ th c hi n các ràng bu c sau:ạ ể ự ệ ộ
1. Th c hi n vi c ki m tra r ng bu c khoá ngo i.ự ệ ệ ể ằ ộ ạ
2. Không cho phép cascade delete trong các r ng bu c khoá ngo i. Ví d không cho phépằ ộ ạ ụ
xoá các CTHOADON nào có SOHD còn trong table HOADON
3. không cho phép user nh p vào hai v t t có cùng tên.ậ ậ ư
4. Khi user đ t hàng thì KHUYENMAI là 5% n u SL >100, 10% n u SL >500.ặ ế ế
5. Ch cho phép mua các m t hàng có s l ng t n l n h n ho c b ng s l ng c nỉ ặ ố ượ ồ ớ ơ ặ ằ ố ượ ầ
mua và tính l i s l ng t n m i khi có đ n hàng.ạ ố ượ ồ ỗ ơ
6. Không cho phép user xoá m t lúc nhi u h n m t v t t .ộ ề ơ ộ ậ ư
7. Ch bán m t hàng G CH (các la i g cg) v i s l ng là b i s c a 100.ỉ ặ Ạ ọ ạ ớ ố ượ ộ ố ủ
4