Bài tập về View trong My SQL

1/ Khái quát sơ hiển về View:
– VIEW là một đối tượng đặc biệt trong SQL, có một số đặc điểm:
– View là bảng [ảo] (không tồn tại vật lý)
– View được định nghĩa thông qua truy vấn
– View được truy vấn như một bảng bình thường
– Một số view cho phép thay đổi dữ liệu ngược vào bảng vật lý
* [U]Sử dụng view nhằm:[/U]
– Tập trung trên dữ liệu mà người sử dụng quan tâm
– Giới hạn số lượng cột, dòng
– Dấu tên cột đối với người dùng
– Dễ dàng bảo mật, phân quyền

2/ Cấu trúc tạo/xóa/sửa view:
* Tạo View:
CREATE VIEW [(Cột1 [,,,)]
AS

* Xóa View:
DROP VIEW

*Sửa View
ALTER VIEW [(Cột1 [,,,)]
AS

3/ Bài tập minh họa:
Ta sẽ lấy 1 bài quen thuộc về ‘Quản Lý Việc Bán Hàng’, gồm 4 table, cụ thể như sau:
Tạo Database QLBANHANG. Tạo các Table có cấu trúc như sau:
(Lưu ý: các field có gạch chân là field khóa chính, gạch nét đứt là khóa ngoại)

Table: KHACHHANG
Name Type Size
MAKH varchar 5
TENKH nvarchar 30
DIACHI nvarchar 50
DT varchar 11
EMAIL varchar 30

Ràng buộc: TENKH not null, DT có thể có từ 8 chữ số đến 11 chữ số.

Table: VATTU
Name Type Size
MAVT varchar 5
TENVT Nvarchar 30
DVT Nvarchar 20
GIAMUA Money
SLTON Int

Ràng buộc: TENVT not null, GIAMUA > 0, SLTON >= 0.

Table: HOADON
Name Type Size
MAHD varchar 10
NGAY SmallDateTime
MAKH varchar 5
TONGTG Float

Ràng buộc: Giá trị nhập vào cho field NGAY phải trước ngày hiện hành.

Table: CTHD
Name Type Size
MAHD varchar 10
MAVT varchar 5
SL int
KHUYENMAI Float
GIABAN Float

Ràng buộc: Giá trị nhập vào cho field SL phải lớn hơn 0.

Nhập dữ liệu vào các Table:

Table VATTU:
MAVT TENVT DVT GIAMUA SLTON
VT01 Xi măng Bao 50000 5000
VT02 Cát Khối 45000 50000
VT03 Gạch ống Viên 120 800000
VT04 Gạch thẻ Viên 110 800000
VT05 Đá lớn Khối 25000 100000
VT06 Đá nhỏ Khối 33000 100000
VT07 Lam gió Cái 15000 50000

Table KHACHHANG:
MAKH TENKH DIACHI DT EMAIL
KH01 Nguyễn Thị Bé Tân Bình 38457895 bnt@yahoo.com
KH02 Lê Hoàng Nam Bình Chánh 39878987 namlehoang@gmail.com
KH03 Trần Thị Chiêu Tân Bình 38457895 NULL
KH04 Mai Thị Quế Anh Bình Chánh NULL NULL
KH05 Lê Văn Sáng Quận 10 NULL sanglv@hcm.vnn.vn
KH06 Trần Hoàng Tân Bình 38457897 NULL

Table HOADON:
MAHD NGAY MAKH TONGTG
HD001 12/05/2010 KH01 NULL
HD002 25/05/2010 KH02 NULL
HD003 25/05/2010 KH01 NULL
HD004 25/05/2010 KH04 NULL
HD005 26/05/2010 KH04 NULL
HD006 02/06/2010 KH03 NULL
HD007 22/06/2010 KH04 NULL
HD008 25/06/2010 KH03 NULL
HD009 15/08/2010 KH04 NULL
HD010 30/09/2010 KH01 NULL

Table CTHD:
MAHD MAVT SL KHUYENMAI 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

{U][B]* CÂU HỎI (LÀM BẰNG VIEW) :[/B][/U]

1. Hiển thị danh sách các khách hàng có địa chỉ là “Tân Bình” gồm mã khách hàng, tên khách hàng, địa chỉ, điện thoại, và địa chỉ E-mail.
==>
CREATE VIEW ([MÃ KHÁCH HÀNG], [TÊN KHÁCH HÀNG], [ĐỊA CHỈ], [ĐIỆN THOẠI], [EMAIL])
AS
SELECT MAKH, TENKH, DAICHI, DT, EMAIL
FROM KHACHHANG
WHERE DIACHI LIKE N’TÂN BÌNH’ — or WHERE DIACHI = ‘TÂN BÌNH’

2. Hiển thị 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.
3. Hiển thị 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ỉ.
4. Hiển thị 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.
5. Hiển thị danh sách các vật tư có đơn vị tính là “Cái” gồm mã vật tư, tên vật tư và giá mua.
6. Hiển thị 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.
7. Hiển thị 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.
8. Hiển thị 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.
9. Lấy ra các thông tin gồm Mã hóa đơn, ngày lập hóa đơn, tên khách hàng, địa chỉ khách hàng và số điện thoại.
10. Lấy ra các thông tin gồm Mã hóa đơ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/2010.
11. Lấy ra các thông tin gồm Mã hóa đơn, ngày lập hóa đơn, tên khách hàng, địa chỉ khách hàng và số điện thoại của những hóa đơn trong tháng 6/2010.
12. 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/2010.
13. Lấy ra danh sách những khách hàng không mua hàng trong tháng 6/2010 gồm các thông tin tên khách hàng, địa chỉ, số điện thoại.
14. Lấy ra các chi tiết hóa đơ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).
15. Lấy ra các chi tiết hóa đơ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.
16. 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.
17. Tìm ra những mặt hàng chưa bán được.
18. Tạo bảng tổng hợp gồm các thông tin: mã hóa đơn, ngày hóa đơ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.
19. Tạo bảng tổng hợp tháng 5/2010 gồm các thông tin: mã hóa đơn, ngày hóa đơ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.
20. Tạo bảng tổng hợp quý 1 – 2010 gồm các thông tin: mã hóa đơn, ngày hóa đơ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.
21. Lấy ra danh sách các hóa đơn gồm các thông tin: Số hóa đơn, ngày, tên khách hàng, địa chỉ khách hàng, tổng trị giá của hóa đơn.
22. Lấy ra hóa đơn có tổng trị giá lớn nhất gồm các thông tin: Số hóa đơn, ngày, tên khách hàng, địa chỉ khách hàng, tổng trị giá của hóa đơn.
23. Lấy ra hóa đơn có tổng trị giá lớn nhất trong tháng 5/2010 gồm các thông tin: Số hóa đơn, ngày, tên khách hàng, địa chỉ khách hàng, tổng trị giá của hóa đơn.
24. Đếm xem mỗi khách hàng có bao nhiêu hóa đơn.
25. Đếm xem mỗi khách hàng, mỗi tháng có bao nhiêu hóa đơn.
26. Lấy ra các thông tin của khách hàng có số lượng hóa đơn mua hàng nhiều nhất.
27. 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.
28. Lấy ra các thông tin về các mặt hàng mà được bán trong nhiều hóa đơn nhất.
29. Lấy ra các thông tin về các mặt hàng mà được bán nhiều nhất.
30. 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 hóa đơn đã mua (nếu khách hàng đó chưa mua hàng thì cột số lượng hóa đơn để trống)

*** NOTE: MÌNH SẼ GÕ ĐÁP ÁN DẦN, VỀ PHẦN TRUY CẬP VÀO SQL SERVER, TẠO DB, ĐẶT KHÓA CHÍNH, NGÀY THÁNG MẶC ĐỊNH, KẾT NỐI CÁC BẢNG BỞI VIỆC TẠO 1 DIAGRAM SẼ XUẤT HIỆN 1 SỐ LỖI NHỎ, PHẦN NÀY MÌNH SẼ CÓ 1 POST RIÊNG
*** NGUỒN TÀI LIỆU: ĐA PHẦN TỪ TRƯỜNG CỦA MÌNH.