Bài tập quản lý vật tư sql có lời giải năm 2024

MỤC LỤC GIỚI THỆU CHUNG .................................................................................................... 4 CHƯƠNG 1. XÂY DỰNG CƠ SỞ DỮ LIỆU ..................................................... 6 1. CƠ SỞ DỮ LIỆU QUẢN LÝ SINH VIÊN .............................................. 6 1.1

BÀI TOÁN ......................................................................................... 6

1.2. CƠ SỞ DỮ LIỆU QUAN HỆ ............................................................ 7 1.3. BẢNG DỮ LIỆU CHI TIẾT .............................................................. 7 2. CƠ SỞ DỮ LIỆU QUẢN LÝ BÁN HÀNG ............................................. 9 2.1. BÀI TOÁN ......................................................................................... 9 2.2. CƠ SỞ DỮ LIỆU QUAN HỆ ............................................................ 9 2.3. BẢNG DỮ LIỆU CHI TIẾT ............................................................ 10 CHƯƠNG 2. CÂU LỆNH TRUY VẤN SQL .................................................... 11 A. KIẾN THỨC CẦN NHỚ ........................................................................... 11 1. Câu lệnh truy vấn với cấu trúc đơn giản ................................................. 11 2. Câu lệnh truy vấn với cấu trúc phức tạp ................................................. 12 2.1. Cấu trúc lồng nhau............................................................................ 12 2.2. Cấu trúc lượng từ .............................................................................. 13 2.3. Cấu trúc tập hợp ............................................................................... 13 3. Bổ sung, cập nhật, xoá dữ liệu ................................................................ 14 3.1. Lệnh INSERT ................................................................................... 14 3.2. Lệnh UPDATE ................................................................................. 15 3.3. Lệnh DELETE .................................................................................. 15 B. PHÂN LOẠI BÀI TẬP .............................................................................. 16 DẠNG 1: CÂU LỆNH TRUY VẤN CÓ ĐIỀU KIỆN ................................. 16 Bài số 1: Câu lệnh SQL không kết nối ....................................................... 16 Bài số 2: Câu lệnh SQL có kết nối ............................................................. 17 BÀI TẬP TỰ GIẢI ........................................................................................ 18 DẠNG 2: CÂU LỆNH TRUY VẤN CÓ PHÂN NHÓM ............................ 19 Bài số 1: Câu lệnh SQL có từ khoá GROUP BY không điều kiện. .......... 19 Bài số 2: Câu lệnh SQL có từ khoá GROUP BY với điều kiện lọc........... 20 Bài số 3: Câu lệnh SQL có từ khoá GROUP BY với điều kiện nhóm. ..... 21

1

Bài số 4: Câu lệnh SQL có từ khoá TOP. .................................................. 22 BÀI TẬP TỰ GIẢI: ................................................................................... 23 DẠNG 3: CÂU LỆNH TRUY VẤN VỚI CẤU TRÚC LỒNG NHAU....... 24 Bài số 1: Cấu trúc lồng nhau phủ định (KHÔNG, CHƯA). ...................... 24 Bài số 2: Cấu trúc lồng nhau không kết nối. .............................................. 25 BÀI TẬP TỰ GIẢI..................................................................................... 26 DẠNG 4: CÂU LỆNH TRUY VẤN VỚI LƯỢNG TỪ ALL, ANY, EXISTS ....................................................................................................................... 26 Bài số 1: Lượng từ ALL ............................................................................. 26 Bài số 2: Lượng từ ANY ........................................................................... 27 Bài số 3: Lượng từ EXISTS ....................................................................... 27 DẠNG 5: CÂU LỆNH TRUY VẤN VỚI CẤU TRÚC TẬP HỢP .............. 28 DẠNG 6: CÂU LỆNH BỔ SUNG, CẬP NHẬT, XOÁ DỮ DIỆU.............. 28 Bài số 1: Lệnh INSERT bổ sung dữ liệu.................................................... 28 Bài số 2: Lệnh DELETE xoá dữ liệu ......................................................... 29 Bài số 3: Lệnh UPDATE cập nhật dữ liệu ................................................. 30 CHƯƠNG 3: LẬP TRÌNH VỚI SQL ................................................................. 31 A. KIẾN THỨC CẦN NHỚ ........................................................................... 31 1. Khai báo và sử dụng biến ........................................................................ 31 2. Một số cấu trúc lệnh cơ bản .................................................................... 32 2.1. Cấu trúc IF… .................................................................................... 32 2.2. Cấu trúc CASE ................................................................................. 32 2.3. Cấu trúc WHILE............................................................................... 33 3. THỦ TỤC (Stored Procedure) ................................................................ 34 4. HÀM (Function) ..................................................................................... 35 5. CON TRỎ (Cursor) ................................................................................. 36 6. Một số hàm cơ bản: ................................................................................. 38 6.1. Các hàm toán học: ............................................................................ 38 6.2. Các hàm xử lý chuỗi ......................................................................... 38 6.3. Hàm xử lý ngày tháng ...................................................................... 39 6.4. Hàm chuyển đổi kiểu dữ liệu............................................................ 39 B. PHÂN LOẠI BÀI TẬP .............................................................................. 40

2

DẠNG 1: HÀM ............................................................................................. 40 Bài số 1: Viết hàm xếp loại dựa vào điểm ................................................. 40 Bài số 2: Viết hàm tách tên từ chuỗi Họ tên .............................................. 40 Bài số 3: Viết hàm đọc điểm nguyên ra thành chữ tương ứng................... 41 Bài số 4: Viết hàm đọc điểm 1 chữ số thập phân ra thành chữ tương ứng 43 Bài số 4: Các dạng hàm liên quan đến tính toán trong CSDL ................... 43 BÀI TẬP TỰ GIẢI: ................................................................................... 45 DẠNG 2: THỦTỤC ...................................................................................... 46 DẠNG BÀI 1: Tạo thủ tuc cập nhật, bổ sung , xoá dữ liệu. ...................... 46 DẠNG BÀI 2: Tạo thủ tục hiển thị dữ liệu với các điều kiện chỉ định. .... 52 BÀI TẬP TỰ GIẢI..................................................................................... 59 DẠNG 3: CON TRỎ ..................................................................................... 59 Bài số 1: Tạo thủ tục đánh Số báo danh theo từng lớp chỉ định. ............... 59 Bài số 2: Tạo thủ tục đánh số báo danh tự động ........................................ 60 Bài số 3: Tạo thủ tục cập nhật mã thẻ sinh viên với công thức như sau: ... 61 Bài số 4: Viết thủ tục phân lớp theo yêu cầu khác nhau ............................ 62 CHƯƠNG 4: MỘT SỐ ĐỐI TƯỢNG TIỆN ÍCH KHÁC ................................. 65 A. KIẾN THỨC CẦN NHỚ ........................................................................... 65 1. TRANSACTION .................................................................................... 65 2. TRIGGER ............................................................................................... 66 B. PHÂN LOẠI BÀI TẬP .............................................................................. 66 DẠNG 1: Tạo bẫy lỗi INSERT ..................................................................... 66 DẠNG 2: Bẫy lỗi DELETE ........................................................................... 68 DẠNG 3: Bẫy lỗi UPDATE ....................................................................... 70 BÀI TẬP TỰ GIẢI..................................................................................... 73 PHẦN ĐỌC THÊM ............................................................................................. 74 ỨNG DỤNG SQL TRONG LẬP TRÌNH C# CƠ BẢN ..................................... 74 Bài số 1. Tạo Form kết nối ............................................................................ 74 Bài số 2: Tạo Form hiển thị danh sách sinh viên ......................................... 76 Bài số 3: Tạo Form Lọc danh sách sinh viên theo lớp .................................. 78 Bài số 4: Tạo Form nhập dữ liệu cho bảng SINHVIEN ............................... 80 Tài liệu tham khảo ........................................................................................................ 83

3

GIỚI THỆU CHUNG SQL, viết tắt của Structure Query Language, là một công cụ quản lý dữ liệu, đơn giản nhưng rất hiệu quả, được sử dụng phổ biến ở nhiều lĩnh vực. Mặc khác, hầu hết tất cả các ngôn ngữ lập trình bậc cao đều có hỗ trợ SQL. Các công cụ lập trình đều cho phép người sử dụng kết nối và truy cập tới CSDL bằng cách nhúng các câu lệnh SQL vào trong các ngôn ngữ lập trình hoặc viết lời gọi đến các chương trình con trên hệ quản trị CSDL. SQL ngày càng đóng vai trò quan trọng khi mà hiện nay Internet ngày càng phát triển. SQL được sử dụng như là công cụ để giao tiếp giữa các trình ứng dụng phía máy khách với máy chủ cơ sở dữ liệu, SQL sẽ thực hiện việc truy cập thông tin và kết quả hiển thị trên ứng dụng khi người dùng yêu cầu. Trong các hệ quản trị cơ sở dữ liệu, SQL xuất hiện với vai trò ngôn ngữ, là công cụ giao tiếp giữa người sử dụng và hệ quản trị cơ sở dữ liệu với nhiều vai trò khác nhau như: truy vấn dữ liệu, lập trình cơ sở dữ liệu, quản trị cơ sở dữ liệu, truy cập dữ liệu trên Internet, … Để phục vụ nhu cầu học tập và nghiên cứu của sinh viên nói chung và sinh viên ngành Cao đẳng Bình Định nói riêng, một tài liệu tham khảo mang tính thực hành là cần thiết. Phân loại và giải chi tiết các dạng bài tập SQL sẽ giúp cho sinh viên nhận biết chính xác các dạng câu hỏi, sử dụng câu lệnh SQL hiệu quả nhất. Trong lập trình, tác giả sử dụng các thuật toán đơn giản, dễ hiểu để giải quyết các bài toán quản lý, đó là mục tiêu trong tài liệu này. Trong tài liệu này, tác giả sử dụng CSDL Quản lý sinh viên làm bài mẫu từ đó sinh viên tự làm các bài tập trên CSDL bán hàng và các CSDL khác. Tài liệu cung cấp những kiến thức căn bản nhất về 2 nội dung chính là ngôn ngữ thao tác dữ liệu và lập trình với cơ sở dữ liệu, từ đó sinh viên có thể xây dựng một ứng dụng quản lý trên windows từ đơn giản đến phức tạp. Trong mỗi chương tài liệu chia làm 2 phần chính là: tóm tắt lý thuyết và phân loại bài tập. Cụ thể chia thành 4 chương như sau: Chương 1: Xây dựng Cơ sở dữ liệu. Trong chương này tác giả giới thiệu 2 CSDL mẫu, CSDL quản lý sinh viên và CSDL quản lý bán hàng, là 2 cơ sở dữ liệu mang tính cơ bản nhất, nó tập hợp tất cả các yêu cầu tổng quan để từ đó 4

sinh viên có thể làm một cách tương tự đối với các CSDL khác. Chương 2: Câu lệnh truy vấn SQL. Trong chương này tác giả chia câu lệnh thao tác dữ liệu thành 6 dạng cơ bản, mỗi dạng có từ 3-4 bài tập minh hoạ, giúp sinh viên nhanh chóng nhận dạng đúng các yêu cầu của mỗi câu lệnh SQL. Chương 3: Lập trình với SQL. Trong chương này tác giả chia cấu trúc lập trình thành 3 dạng: Hàm, Thủ tục và Con trỏ. Mỗi dạng bao gồm nhiều dạng bài khác nhau, mỗi dạng bài là cơ bản được tác giả chọn lọc và rất cần thiết trong lập trình ứng dụng sau này. Chương 4: Một số đối tượng tiện ích khác, nhằm nâng cao kỹ năng lập trình, người lập trình phải hạn chế tối đa nhất các lỗi thường xẩy ra, lường trước lỗi và bẫy lỗi là kỹ năng cần thiết của người lập trình chuyên nghiệp. Phần đọc thêm: Ứng dụng SQL trong lập trình C# căn bản. Trong chương này tác giả minh hoạ một số ứng dụng cơ bản, trong đó thể hiện một kết nối từ ứng dụng tới thủ tục trong hệ quản trị CSDL SQL Server. Giúp sinh viên thấy được mối liên hệ giữa lập trình CSDL với lập trình trên công cụ C#, được xem là kỹ thuật mang tính bảo mật cao. Tài liệu tham khảo “Phân loại và giải chi tiết các dạng bài tập SQL” mang tính thực hành cao, là tài liệu gối đầu cho tất cả sinh viên đang ngồi ghế nhà trường, tài liệu giúp sinh viên học tốt các học phần liên quan như: Hệ quản trị CSDL Access, Hệ quản trị CSDL SQL, Lập trình Windows, Lập trình Website, …Tài liệu sẽ hoàn thiện hơn khi nhận nhiều ý kiến đóng góp quý báu của các bạn đọc. Tác giả rất mong nhận nhiều góp ý để tài liệu hữu ích hơn.

5

CHƯƠNG 1. XÂY DỰNG CƠ SỞ DỮ LIỆU 1. CƠ SỞ DỮ LIỆU QUẢN LÝ SINH VIÊN 1.1 BÀI TOÁN Dữ liệu vào: - Danh mục các Ngành học của mỗi Khoa. - Danh sách hồ sơ sinh viên gồm những thông tin đầu vào như Mã sinh viên, Họ tên, Giới tính, Ngày sinh, Địa chỉ, Khoá học, Hệ đào tạo, Khoa, Ngành học, Lớp học, ... - Danh sách các học phần ở mỗi học kỳ theo từng ngành. - Danh sách điểm học phần của mỗi sinh viên. Dữ liệu ra: - Thực hiện một số thống kê: Tính số lượng sinh viên mỗi lớp, mỗi ngành, mỗi khoa, … - Phân lớp và đánh mã sinh viên theo các yêu cầu khác nhau từ dễ đến khó. - Theo dõi chương trình giảng dạy các học phần theo từng ngành. - Theo dõi điểm học phần của mỗi sinh viên của từng học kỳ, cả năm và cả khoá. Đưa ra danh sách sinh viên tích luỹ, ngừng tiến độ học tập. - Đưa ra bảng điểm tổng hợp có xếp loại học tập theo từng lớp ở mỗi học kỳ, cả năm và cả khoá học. - Xử lý dữ liệu: tạo các thủ tục (Procedure) hiển thị dữ liệu, tính toán, bổ sung, cập nhật, xoá, …

6

1.2.

CƠ SỞ DỮ LIỆU QUAN HỆ

1.3.

BẢNG DỮ LIỆU CHI TIẾT Bảng DMKHOA

Bảng DMNGANH

Bảng DMLOP

Bảng SINHVIEN

7

Bảng DMHOCPHAN

Bảng DIEMHP

8

2. CƠ SỞ DỮ LIỆU QUẢN LÝ BÁN HÀNG 2.1.

BÀI TOÁN Dữ liệu vào - Danh sách các mặt hàng bán lẻ tại các cửa hàng (chẳng hạn như Siêu thị) gồm các thông tin Mã hàng, Tên hàng và đơn giá bán hiện tại. - Danh sách các khách hàng Thành viên và VIP gồm các thông tin Họ tên, Địa chỉ, Số điện thoại. - Danh sách các mặt hàng của từng hoá đơn. Dữ liệu ra - Hoá đơn bán hàng cho mỗi khách hàng và theo dõi quá trình mua hàng của mỗi khách hàng để có những ưu đãi thích hợp. - Theo dõi từng mặt hàng bán theo tháng, quý và năm. Những mặt hàng mức tiêu thụ cao, tiêu thu thấp để điều chỉnh giá phù hợp. - Tổng hợp doanh thu của từng mặt hàng theo từng tháng, quý và năm. - Tổng hợp tiền mua của từng khách hàng trong mỗi năm, tích điểm và in chiết khấu.

2.2.

CƠ SỞ DỮ LIỆU QUAN HỆ

9

2.3.

BẢNG DỮ LIỆU CHI TIẾT

Bảng KHACHHANG

Bảng HANGHOA

Bảng HOADON

Bảng CHITIETHD

10

CHƯƠNG 2. CÂU LỆNH TRUY VẤN SQL A.

KIẾN THỨC CẦN NHỚ

1. Câu lệnh truy vấn với cấu trúc đơn giản Ý nghĩa: Câu lệnh SELECT dùng để truy xuất dữ liệu từ một hay nhiều bảng. Cú pháp: SELECT [ALL|DISTINCT]|[TOP n] [INTO tên_bảng_mới] FROM INNER JOIN ON ...

INNER JOIN ON

[WHERE điều_kiện lọc] [GROUP BY ds cột phân nhóm] [HAVING điều_kiện nhóm] [ORDER BY cột_sắp_xếp][DESC | ASC] Giải thích: • Danh sách cột: là dãy các cột/ biểu thức cột cách nhau bởi dấu phẩy. Dấu * có nghĩa là hiển thị tất cả các cột trong bảng. • Tham chiếu đến cột Khoá của bảng: . • Điều kiện trong câu lệnh SELECT WHERE : Điều kiện nằm sau từ khóa WHERE, là một biểu thức Logic gồm các phép toán sau: Các toán tử kết hợp điều kiện: AND, OR Các toán tử so sánh: >,=,= 5. 2. Hiển thị danh sách MaSV, HoTen , MaLop, MaHP, DiemHP được sắp xếp theo ưu tiên Mã lớp, Họ tên tăng dần. 3. Hiển thị danh sách gồm MaSV, HoTen, MaLop, DiemHP, MaHP của những sinh viên có điểm HP từ 5 đến 7 ở học kỳ I. 4. Hiển thị danh sách sinh viên gồm MaSV, HoTen, MaLop, TenLop, MaKhoa của Khoa có mã CNTT. Lời giải: 1. SELECT SINHVIEN.MaSV, HoTen, MaLop, DiemHP, MaHP FROM SINHVIEN INNER JOIN

DIEMHP ON DIEMHP.MaSV=SINHVIEN.MaSV

WHERE DiemHP>5 2. SELECT SINHVIEN.MaSV, HoTen, MaLop, TenLopDiemHP, MaHP FROM SINHVIEN INNER JOIN

DIEMHP ON DIEMHP.MaSV=SINHVIEN.MaSV

INNER JOIN

DMLOP ON

SINHVIEN.MaLop=DMLOP.MaLop

ORDER BY MaLop, HoTen ASC

17

3. SELECT SINHVIEN.MaSV, HoTen, MaLop, DiemHP, MaHP, Hocky FROM SINHVIEN INNER JOIN WHERE

DIEMHP ON DIEMHP.MaSV=SINHVIEN.MaSV

(DiemHP>=5 AND DiemHP10. • Kết nối 2 hay nhiều bảng 7. Cho biết MaHD, MaH, TenH, DonGia, SoLuong, ThanhTien của hoá đơn 001. 8. Cho biết MaHD, MaH, TenH, DonGia, SoLuong, ThanhTien có Thành tiền từ 1 triệu đến 2 triệu. 9. Cho biết thông tin khách hàng không mua hàng vào tháng 6.

18

10. Cho biết MaHD, NgayLapHD, MaHK, TenH, DonGia, SoLuong, ThanhTien bán vào tháng 6 11. Cho biết danh sách các mặt hàng đã bán được. DẠNG 2: CÂU LỆNH TRUY VẤN CÓ PHÂN NHÓM Bài số 1: Câu lệnh SQL có từ khoá GROUP BY không điều kiện. 1. Cho biết MaLop, TenLop, tổng số sinh viên của mỗi lớp. 2. Cho biết điểm trung bình chung của mỗi sinh viên, xuất ra bảng mới có tên DIEMTBC, biết rằng công thức tính DiemTBC như sau: DiemTBC =  (DiemHP * SoDvht) /  (SoDvht) 3. Cho biết điểm trung bình chung của mỗi sinh viên ở mỗi học kỳ. 4. Cho biết MaLop, TenLop, số lượng nam nữ theo từng lớp. Lời giải: 1. SELECT SINHVIEN.MaLop, TenLop, COUNT(Masv) AS Siso FROM DMLOP INNER JOIN SINHVIEN ON DMLOP.MaLop=SINHVIEN.MaLop GROUP BY SINHVIEN.MaLop,TenLop 2. SELECT MaSV, SUM(DiemHP*Sodvht)/SUM(Sodvht) AS DiemTBC INTO DIEMTBC FROM DMHOCPHAN INNER JOIN

DIEMHP

ON DMHOCPHAN.MaHP=DIEMHP.MaHP

GROUP BY MaSV 3. SELECT HocKy,MaSV,SUM(DiemHP*Sodvht)/SUM(Sodvht) AS DiemTBC FROM DMHOCPHAN INNER JOIN DIEMHP ON DMHOCPHAN.MaHP=DIEMHP.MaHP GROUP BY HocKy,MaSV ORDER BY HocKy

19

4. SELECT SINHVIEN.MaLop,Tenlop,CASE GioiTinh WHEN 0 THEN N'Nữ' ELSE N'Nam' END AS GioiTinh, COUNT(MaSV) AS Soluong FROM DMLOP INNER JOIN SINHVIEN ON DMLOP.MaLop=SINHVIEN.MaLop GROUP BY SINHVIEN.MaLop,Tenlop,GioiTinh ORDER BY

SINHVIEN.MaLop

Bài số 2: Câu lệnh SQL có từ khoá GROUP BY với điều kiện lọc. 1. Cho biết điểm trung bình chung của mỗi sinh viên ở học kỳ 1. DiemTBC =  (DiemHP * SoDvht) /  (SoDvht) 2. Cho biết MaSV, HoTen, Số các học phần thiếu điểm (DiemHP=7 THEN N'Khá' WHEN @Diem>=5 THEN N'Trung bình' ELSE

N'Yếu'

END RETURN

@xl

END

Ứng dụng: Hiển thị danh sách gồm: MaSV, DiemTBC, Xếp Loại của mỗi sinh viên và xuất ra bảng mới tên là DIEMTBC. SELECT MaSV, SUM(DiemHP*Sodvht)/SUM(Sodvht) AS DiemTBC, dbo.XEPLOAI(SUM(DiemHP*Sodvht)/SUM(Sodvht) AS ‘Xếp loại’ IN TO DIEMTBC FROM DMHOCPHAN INNER JOIN DIEMHP ON DMHOCPHAN.MaHP=DIEMHP.MaHP GROUP BY MaSV

Bài số 2: Viết hàm tách tên từ chuỗi Họ tên CREATE

FUNCTION

TACHTEN(@ht nvarchar(30))

40

RETURNS

nvarchar(10)

AS BEGIN DECLARE @ten varchar(10), @L int, @i int,@j int,@kt varchar(10) SET @L=LEN(@ht) SET @i=1 WHILE