Bài 4: Ôn tập SQL

BÀI 4- ÔN TẬP Bài 1: Dùng câu lệnh T-SQL để thực hiện các công việc sau: 1.  Tạo lập CSDL với tên QLBH (Quản lý bán hàng) cho công ty xuất nhập khẩu ô tô –xe máy X. Trong CSDL đó, tạo các bảng có cấu trúc như sau: Bảng CUA_HANG lưu thông tin về các văn phòng đại diện đặt tại các tỉnh của công ty X. Bảng có cấu trúc như sau: TÊN TRƯỜNG KIỂU DỮ LIỆU KÍCH THƯỚC GIẢI THÍCH Mã hiệu của cửa hàng. Khóa chính của bảng Tên tỉnh mà cửa hàng được đặt; không nhận giá chị null Tên người quản lý cửa hàng; không nhận giá trị null Địa chỉ nơi đặt CH; không nhận giá trị null; Số điện thoại của CH; không nhận giá trị null Ma_CH Char 4 Tinh_CH nvarchar 15 Quan_Ly_CH nvarchar 30 Dia_Chi_CH nvarchar 50 Tel_CH varchar 13  Bảng KHACH_HANG lưu thông tin về các chủ đại lý đến đặt mua hàng của công ty. Bảng có cấu trúc như sau: TÊN TRƯỜNG KIỂU DỮ LIỆU KÍCH THƯỚC GIẢI THÍCH Mã hiệu của khách hàng; khóa chính của bảng. Tên của khách hàng; không nhận giá trị null Họ đệm KH; không nhận giá trị null Địa chỉ của KH; không nhận giá trị null; Số điện thoại của KH; không nhận giá trị null Ma_KH Char 4 Ten_KH nvarchar 7 Ho_Dem_KH nvarchar 25 Dia_Chi_KH nvarchar 50 Tel_KH varchar 13  Bảng NHA_CUNG_CAP lưu thông tin về các công ty cung cấp hàng cho công ty X. Bảng có cấu trúc như sau: TÊN TRƯỜNG KIỂU DỮ LIỆU KÍCH THƯỚC 1 GIẢI THÍCH Ma_NCC Char 4 Ten_NCC nvarchar 7  Bảng MAT_HANG lưu thông tin về các mặt hàng mà công ty X nhập khẩu. Bảng có cấu trúc như sau: TÊN TRƯỜNG KIỂU DỮ KÍCH LIỆU THƯỚC Ma_MH char 4 Ten_MH Loai_MH Tluong_MH Mau_MH Ton_Kho_MH varchar varchar Smallint nvarchar Smallint 20 5 Gia_Mua_MH Gia_Ban_MH Money Money Ma_NCC Char  25 4 GIẢI THÍCH Mã hiệu của mặt hàng; khóa chính của bảng Tên của mặt hàng; không nhận giá trị null Mã kí hiệu loại MH Trọng lượng của mặt hàng (kg); số dương Màu của mặt hàng Số lượng đang có trong kho của mặt hàng; số dương Giá nhập hàng (triệu đồng); mặc định là 0 Giá xuất hàng tại thời điểm hiện tại; mặc định là 0. Mã nhà cung cấp hàng Bảng DON_HANG lưu thông tin về các đơn hàng mà các đại lý đến các cửa hàng đại diện đặt mua hàng. Bảng này chỉ lưu một số thông tin chính của đơn hàng, còn chi tiết về các mặt hàng đặt mua với số lượng bao nhiêu sẽ được lưu trên một bảng khác có tên là bảng CHI_TIET_DH. Bảng DON_HANG có cấu trúc như sau: TÊN TRƯỜNG KIỂU DỮ LIỆU KÍCH THƯỚC GIẢI THÍCH 7 Mã hiệu của đơn hàng; khóa chính của bảng; không nhận giá trị null. Ngày KH đến đặt mua hàng; mặc định là ngày tháng giờ hiện tại của hệ thống máy tính; không nhận giá trị null. Mã khách hàng; không nhận giá trị null. Mã cửa hàng; không nhận giá trị null. Ma_DH char Ngay_Lap_DH Datetime Ma_KH Char 4 Ma_CH Char 4  Mã hiệu của cung cấp hàng; khóa chính của bảng. Tên của nhà cung cấp; không nhận giá trị null và duy nhất Bảng CHI_TIET_DH lưu thông tin chi tiết về các đơn hàng. Bảng có cấu trúc như sau: 2 TÊN TRƯỜNG KIỂU DỮ LIỆU KÍCH GIẢI THÍCH THƯỚC Ma_DH Char 7 Ma_MH Char 4 Sluong_dat Smallint Gia Money Ngay_giao_DK Date  Mã hiệu của đơn hàng; cùng với Ma_DH làm khóa chính của bảng Mã mặt hàng đặt mua; cùng với MaMH làm khóa chính của bảng Số lượng đặt mua của mặt hàng trong đơn hàng; số dương; mặc định là 0 Giá bán của mặt hàng tại thời điểm lập đơn hàng; giá trị mặc định là 0 Ngày dự kiến hàng sẽ được giao Bảng GIAO_HANG lưu thông tin về các lần giao hàng cho các đại lý. Bảng này chỉ lưu một số thông tin chính của hóa đơn giao hàng, còn chi tiết về các mặt hàng được giao với số lượng bao nhiêu sẽ được lưu trên một bảng khác có tên là bảng CHI_TIET_GH. Bảng GIAO_HANG có cấu trúc như sau: TÊN TRƯỜNG KIỂU DỮ LIỆU KÍCH GIẢI THÍCH THƯỚC Ma_GH Char Ngay_GH Datetime Ma_KH Ma_CH Char Char  7 4 4 Mã hiệu của hóa đơn giao hàng; Khóa chính của bảng Ngày công ty giao hàng cho đại lý; mặc định là ngày giờ của hệ thống Mã khách hàng Mã cửa hàng Bảng CHI_TIET_GH lưu thông tin chi tiết về các hóa đơn giao hàng. Bảng có cấu trúc như sau: TÊN TRƯỜNG KIỂU DỮ KÍCH LIỆU THƯỚC Ma_GH Char 7 Ma_MH Char 4 Sluong_giao smallint Ma_DH Char 7 GIẢI THÍCH Mã hiệu của hóa đơn giao hàng; cùng với Ma_GH làm khóa chính của bảng Mã mặt hàng đặt mua; cùng với Ma_MH làm khóa chính của bảng Số lượng hàng được giao; số dương; mặc định là 0. Mã đơn hàng đặt mua liên quan; không 3 nhận giá trị null. 2. Tạo thêm các ràng buộc tham chiếu sau cho các bảng: a. Ma_NCC ở bảng MAT_HANG tham chiếu đến Ma_NCC ở bảng NHA_CUNG_CAP. b. Ma_CH ở bảng DON_HANG tham chiếu đến Ma_CH ở bảng CUA_HANG. c. Ma_KH ở bảng DON_HANG tham chiếu đến Ma_KH ở bảng KHAC_HANG. d. Ma_CH ở bảng DON_HANG tham chiếu đến Ma_CH ở bảng CUA_HANG. e. Ma_DH ở bảng CHI_TIET_DH tham chiếu đến Ma_DH ở bảng DON_HANG. f. Ma_MH ở bảng CHI_TIET_DH tham chiếu đến Ma_MH ở bảng MAT_HANG. g. Ma_CH ở bảng GIAO_HANG tham chiếu đến Ma_CH ở bảng CUA_HANG. h. Ma_GH ở bảng CHI_TIET_GH tham chiếu đến Ma_GH ở bảng GIAO_HANG. i. Ma_MH ở bảng CHI_TIET_GH tham chiếu đến Ma_MH ở bảng MAT_HANG. 3. Thêm ràng buộc CHECK giá bán lớn hơn giá mua trong bảng MAT_HANG. 4. Thêm ràng buộc CHECK ngày giao dự kiến phải lớn hơn ngày lập đơn hàng trong bảng DON_HANG. 5. Tạo diagram hiển thì các ràng buộc tham chiếu đã tạo ở câu 2 (hình ví dụ ở phía dưới) 4 6. Tải xuống tệp INSERT_QLBH.sql trên tài khoản Onedrive của khóa học và chạy script này trong Management Studio để nhập dữ liệu vào cho các bảng. Bài 2: Thực hiện các truy vấn sau trên CSDL QLBH 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. Cho biết tên, địa chỉ liên hệ của các nhà cung ứng hàng. Đưa ra danh sách các khách hàng có địa chỉ ở Hà nội. Đưa ra danh sách các mặt hàng có giá bán lớn hơn 10% giá mua. Đưa ra danh sách các loại xe máy có màu đỏ. Đưa ra danh sách các loại xe máy màu đỏ hoặc các loại xe máy có giá bán nằm trong khoảng từ 25 đến 35 triệu đồng. Đưa ra danh sách các loại xe máy không phải là màu đỏ hoặc có giá bán không vượt quá 15 triệu đồng. Tìm ra những khách hàng có tên chứa vần ‘an’. Liệt kê ra những khách hàng có độ dài tên bằng 5 và tên bắt đầu bằng kí tự ‘H’, kết thúc bằng kí tự “n”. Tìm mọi mặt hàng chưa xác định màu. Đưa ra danh sách các mặt hàng có trọng lượng nhỏ hơn hay bằng 500 kg theo thứ tự tăng của trọng lượng và nếu trọng lượng bằng nhau thì sắp xếp theo giá mua giảm dần. Tìm trung bình (ngày) giữa ngày lập đơn hàng và ngày giao hàng dự kiến Tìm đơn hàng có khoảng cách giữa ngày lập đơn hàng và ngày giao hàng dự kiến là lớn nhất. Đưa ra tên và giá mua của hàng có giá mua đắt nhất trong kho. Đếm số màu khác nhau của các mặt hàng có trong kho. Đưa ra giá bán trung bình của từng loại hàng. Đưa ra mặt hàng có giá bán trung bình lớn nhất. Tìm các cửa hàng mà trong khoảng từ ngày 6 đến ngày 7 tháng 6 đã thực hiện được trên một lần bán hàng. 5 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. Cho biết tên các mặt hàng đã bán và với mỗi mặt hàng thì cho biết giá bán trung bình, tổng số lượng đã được bán. Kết quả sắp xếp theo chiều giảm dần của tổng số lượng bán? Hãy chọn các mặt hàng có màu đỏ với số hiệu mặt hàng, tên hàng, trọng lượng hàng, và tên nhà cung ứng nó (kết quả sắp xếp theo giá trị tăng dần của trọng lượng). Liệt kê danh sách tên các khách có số hiệu nhỏ hơn hay bằng ‘KH05’, cùng với ngày tháng các lần mua của họ. Nếu khách hàng đó chưa mua gì cả thì tên của khách hàng đó vẫn xuất hiện trên bảng với ngày tháng mua là chưa xác định. Tìm mọi mặt hàng có trong lượng nhỏ hơn trọng lượng của mặt hàng có số hiệu là ‘MH02’. Đưa ra danh sách các mặt hàng có cùng màu với mặt hàng MH10 và có trọng lượng lớn hơn hoặc bằng trọng lượng trung bình của mọi mặt hàng. Liệt kê ra tên các nhà cung ứng có bán mặt ít nhất 1 mặt hàng có màu đen. Liệt kê ra danh sách các mặt hàng có giá bán lớn hơn giá bán của mọi mặt hàng có màu trắng. Cho biết danh sách các khách hàng đã mua hàng ít nhất ở 2 cửa hiệu khác nhau. Cho biết tổng số lượng các khách hàng khác nhau từng năm (từ 2004 đến 2005) của tất cả các cửa hàng (HD:dùng Union). Đưa ra danh sách các cửa hàng chưa bán được một đơn hàng nào. Cập nhật giá mua và giá bán các mặt hàng của hãng Honda giảm xuống 1%. Đưa ra tên và giá mua của hàng có giá mua đắt nhất trong kho. Đưa ra danh sách các cửa hàng chưa bán được một đơn hàng nào. Lưu danh sách các cửa hàng đó vào bảng có tên KD_KEM. Sau đó, xóa tất cả các cửa hàng đó trong bảng CUA_HANG (HD: Có thể dùng nhiều câu lệnh). 6