- Sách bài tập SQL Server 2 AiTi-Aptech “Kiến tạo tương lai” Tài liệu lưu hành nội bộBài tập SQL 2005 2Trung tâm đào tạo Lập trình viên Quốc tế AiTi-Aptech Page 1 of 2035/115 Phố Định Công – Hoàng MaiSách bài tập SQL Server 2 AiTi-Aptech “Kiến tạo tương lai” Tài liệu lưu hành nội bộ Lời giới thiệuSách bài tập do tập thể giáo viên AiTi-Aptech thiết kế và được sử dụng như một phần không thểtách rời khỏi giáo trình đang học của Aptech Ấn Độ với các học viên đang theo học tại Trung tâm.Tập sách bài tập này là tài liệu lưu hành nội bộ, chỉ dành cho các học viên theo học tại Trung tâmđào tạo Lập trình viên Quốc tế AiTi-Aptech.
- Mọi hình thức sao chép lại nội dung của sách là vi phạmbản quyền và không tuân thủ Luật Sở hữu trí tuệ của Nhà nước Việt Nam.AiTi-Aptech luôn mong mỏi tạo dựng một môi trường học tập tốt cho các bạn học viên theo học tạitrung tâm.
- Mọi ý kiến đóng góp về xây dựng Sách bài tập, cải tiến hệ thống xin gửi mail về
[email protected] hoặc đường dây nóng .
- “Sự nghiệp tương lai của các bạn là thành công của chúng tôi” Đội thiết kế Sách bài tập Việt Nam luôn thiếu Lập trình viên đẳng cấp Quốc tếTrung tâm đào tạo Lập trình viên Quốc tế AiTi-Aptech Page 3 of 2035/115 Phố Định Công – Hoàng MaiContentsBài thực hành SQLDO - Quản lý điểm Bài thực hành SQLSDO - Quản lý bán hàng Bài tập thực hành SQLSDO - Quản lý bán sách Bài thực hành SQLDO - Quản lý điểmCác bảng trong csdl QLDIEM có cấu trúc như sau:Sơ đồ quan hệ (relationship) dạng đơn giản:Sơ đồ quan hệ cùng cấu trúc chi tiết các bảng:Sách bài tập SQL Server 2 AiTi-Aptech “Kiến tạo tương lai” Tài liệu lưu hành nội bộSố liệu mẫu trong bảng Class:Số liệu mẫu trong bảng Subject:Trung tâm đào tạo Lập trình viên Quốc tế AiTi-Aptech Page 5 of 2035/115 Phố Định Công – Hoàng MaiSố liệu mẫu trong bảng Student:Số liệu mẫu trong bảng Mark:Ghi chú: Trong bảng Class chứa thông tin về các lớp dự định mở và như vậy thông tin về lớp được nhậptrước khi lớp nhận sinh viên.
- Vì vậy có trường hợp tuy lớp đã đăng ký, nhưng do vì một lý do nào đó màlớp không khai giảng thì thông tin về lớp vẫn còn trong bảng Class nhưng trong bảng Student lại khôngcó.Ngoài các bảng trên, hãy tạo thêm bảng testXML có chứa cột Address có kiểu XML, sẽ được dùng đểthử cho các lệnh liên quan đến kiểu dữ liệu này như index, view…Bảng testXML có cấu trúc như sau:Sách bài tập SQL Server 2 AiTi-Aptech “Kiến tạo tương lai” Tài liệu lưu hành nội bộBÀI 1Câu 1:1.
- Tạo các bảng trên đây (lưu ý là trong bảng Class không có cột Stud_no).2.
- Nhập một số bản ghi cho các bảng trên.Câu hỏi 2:Viết các lệnh T-SQL thực hiện các công việc sau:1.
- Đặt foreign key constraint cho cột ClassCode trong bảng Student.2.
- Tạo các loại index một cách thích hợp và theo dõi tác động của các lệnh này khi thực hiện các lệnh truy vấn (select).3 Hiển thị không lặp lại tên của sinh viên có một phần địa chỉ email là “fpt.vn” hoặc "fpt.com.vn"4.
- Hiển thị những lớp có ngày kết thúc là 23/7/2006 mà không có sinh viên nào.5.
- Loại bỏ các bản ghi từ các bảng CLASS, STUDENT và MARK của các lớp có ngày kết thúc trước 1/1/2005.6.
- Hiển thị ClassCode, RollNo, FullName và tổng số sinh viên với mỗi lớp.
- Hiển thị tổng số sinh viên cho tất cả các lớp.7.
- Tạo view viewClass1 chứa danh sách lớp đối với các lớp có hơn 17 sinh viên.8.
- Hiển thị SubjectCode (mã môn học.
- SubjectName (tên môn học) và số sinh viên đã thi môn đó với số sinh viên tham gia đông nhất.Câu hỏi 3:Tạo trigger có tên là TrigDelClass cho thao tác xóa lớp trên bảng CLASS.Trước khi xóa thì mã lớp, tên giáo viên chủ nhiệm và ngày xóa được lưu vào bảng CLASS_HIST.
- Đồngthời xóa các bản ghi liên quan trong các bảng STUDENT và MARK.Cấu trúc của bảng CLASS_HISTCột Mô tảClassCode Mã lớpHeadTeacher Tên giáo viên chủ nhiệmDelDate Ngày xóa lớpTrung tâm đào tạo Lập trình viên Quốc tế AiTi-Aptech Page 7 of 2035/115 Phố Định Công – Hoàng MaiBÀI 2 Câu 1: Tạo bảng và nhập dữ liệu1.
- Viết các câu SQL nhập đầy đủ và chính xác những dữ liệu theo mô tả trên vào các bảng vừa tạo.
- Câu 2: Viết các câu truy vấn dữ liệu (query)1.
- Viết câu lệnh SQL cho kết quả là số sinh viên đã có điểm thi môn “CF”.2.
- Viết câu lệnh SQL cho kết quả là danh sách các môn học cùng với số sinh viên đã có điểm thi tương ứng của từng môn học, theo thứ tự tăng dần của tên môn học.3.
- Viết câu lệnh SQL cho kết quả là danh sách các sinh viên quê ở "HT" (Hà Tây), cùng với tên các môn học đã thi nhưng không qua.
- Viết câu lệnh SQL cho kết quả là danh sách các lớp học, cùng với tổng số sinh viên trong lớp.5.
- Viết câu lệnh SQL cho kết quả là danh sách các sinh viên, cùng với tên đầy đủ các môn học mà sinh viên đó đã tham gia thi.6.
- Viết câu lệnh SQL cho kết quả là danh sách các sinh viên, cùng với số lần đã tham gia thi thực hành (mỗi record trong bảng MARK có điểm PMark là một lần thi).7.
- Viết câu lệnh SQL cho kết quả là danh sách các tỉnh, cùng với điểm trung bình tất cả các môn thi của sinh viên quê ở tỉnh đó.
- Viết lệnh SQL cho kết quả là danh sách các sinh viên có điểm trung bình tất cả các môn học >15.
- Câu 3: Viết Stored ProcedureViết một script tạo stored procedure với các yêu cầu sau:- Tên Procedure: procStudentList- Thông số: pClassCode as varchar(10), pMark as float- Xử lý:Sách bài tập SQL Server 2 AiTi-Aptech “Kiến tạo tương lai” Tài liệu lưu hành nội bộ + Nếu thông số pClassCode được truyền.
- Nếu thông số pMark không truyền thì nhận giá trị ngầm định =0.Trung tâm đào tạo Lập trình viên Quốc tế AiTi-Aptech Page 9 of 2035/115 Phố Định Công – Hoàng MaiBÀI 3 Viết các lệnh T-SQL thực hiện các công việc sau:1.
- Liệt kê danh sách lớp với tên lớp, giáo viên chủ nhiệm và số sinh viên thực tế trong lớp (là số bản ghi có mã lớp như nhau trong bảng student).2.
- Thêm một trường mới vào bảng Class có tên là Stud_no có kiểu là smallint.
- Trường này nhận giá trị là số sinh viên thực tế trong lớp, tức là số sinh viên đếm được từ bảng Student.
- Hãy cập nhật thông tin cho trường này.3.
- Hãy sửa lại thông tin đã tính toán cho trường stud_no trong bảng Class.
- Như vậy thông tin về trường này trong những bản ghi đã sửa đổi không khớp với số bản ghi tương ứng trong bảng Student.
- Hãy viết câu lệnh liệt kê trên màn hình những lớp mà thông tin về số sinh viên trong trong hai bảng không khớp nhau.
- Danh sách các trường là: ClassCode, HeadTeacher, Stud_no, CountStud_no trong đó CountStud_no là số sinh viên được tính toán từ bảng student.4.
- Viết lệnh loại bỏ trường Stud_no trong bảng Class và thực hiện, để bảo đảm rằng trường này không còn tồn tại trong bảng Class.
- Hãy viết một stored procedure có tên là proc1 tạo bảng có tên là T1 gồm các thông tin sau: ClassCode, HeadTeacher, CountStud_no trong đó CountStud_no là số sinh viên thực tế có trong bảng Student.
- Nếu tên lớp đã có trong bảng Class nhưng trong bảng Student không có bản ghi nào tương ứng thì lớp được coi là có số sinh viên = 0.
- Viết Function có tên là func1 có tham số vào là pClassCode có kiểu varchar(10) và trả về số sinh viên thực tế của lớp đó.
- Viết lệnh thử lại kết quả trên màn hình, sau đó dùng hàm này để cập nhật trường stud_no của bảng Class.
- Viết Stored Procedure có tên là procTopClass có các tham số vào là pClassCode, pSubjectCode là mã lớp và mã môn học.
- tham số ra là pRollNo, là mã sinh viên có điểm thi môn học này cao nhất trong lớp.
- Viết lệnh thử lại bằng cách nhập mã lớp, mã môn học sau đó in ra màn hình mã sinh viên cùng với FullName có điểm thi cao nhất.
- Viết User-function có tên là func2 có các tham số vào là pClassCode, pSubjectCode là mã lớp và mã môn học.
- giá trị trả về là mã sinh viên có điểm thi môn học này cao nhất trong lớp.
- Viết lệnh thử lại bằng cách nhập mã lớp, mã môn học sau đó in ra màn hình mã sinh viên cùng với FullName có điểm thi cao nhất.Sách bài tập SQL Server 2 AiTi-Aptech “Kiến tạo tương lai” Tài liệu lưu hành nội bộ 8.
- Sử dụng cursor hiển thị classcode, rollno, fullname của các sinh viên tại các bản ghi chẵn trong bảng Student.
- Sử dụng cursor sửa lại giá trị mark của các sinh viên tại các bản ghi 3, 6, 9.
- thành 25 trong bảng Mark (câu này không có ý nghĩa thực tế, chủ yếu là để hiểu rõ hơn về cursor mà thôi).Bài thực hành SQLSDO - Quản lý bán hàngCSDL BANHANG lưu trữ thông tin về quản lý bán hàng tại siêu thị Bình Minh ở Hà Nội gồm 6 bảngcó cấu trúc như sau:Dữ liệu mẫu trong bảng NhaCungCap:Trung tâm đào tạo Lập trình viên Quốc tế AiTi-Aptech Page 11 of 2035/115 Phố Định Công – Hoàng MaiDữ liệu mẫu trong bảng KhachHang:Dữ liệu mẫu trong bảng NhanVien:Dữ liệu mẫu trong bảng LoaiSanPham:Sách bài tập SQL Server 2 AiTi-Aptech “Kiến tạo tương lai” Tài liệu lưu hành nội bộ(xem tiếp trang 3)Cấu trúc các bảng bảng có dạng (bạn có thể thay kiểu nvarchar bằng kiểu varchar):Trung tâm đào tạo Lập trình viên Quốc tế AiTi-Aptech Page 13 of 2035/115 Phố Định Công – Hoàng Mai(tiếp theo trang 1)Dữ liệu mẫu trong bảng SanPham:Lưu ý: Trường SoLuongTT có nghĩa là số lượng tối thiểu phải có trong kho.
- Nếu số lượng đến mức nàythì phải nhập hàng từ nhà cung cấp.Dữ liệu mẫu trong bảng HoaDon:Dữ liệu mẫu trong bảng HoaDonChiTiet:Hãy viết các lệnh T-SQL thực hiện các công việc sau:1.
- Tạo các loại index một cách thích hợp và theo dõi tác động của các lệnh này khi thực hiện các lệnh truy vấn (select).Sách bài tập SQL Server 2 AiTi-Aptech “Kiến tạo tương lai” Tài liệu lưu hành nội bộ2.
- Trong bảng HoaDonChiTiet cột thành tiền còn chưa được tính toán, hãy cập nhật trường ThanhTien= DonGia*SoLuong*(1-GiamGia).3.
- Sau khi đã có thông tin về ThanhTien trong HoaDonChiTiet, hãy cập nhật thông tin của trường Tien trong hóa đơn = tổng số tiền của các mặt hàng có trong hóa đơn = tổng của cột ThanhTien của các bản ghi trong HoaDonChiTiet có cùng số hóa đơn (MaHD).
- Hiển thị danh sách các mặt hàng với đầy đủ các thông tin sau: Loại hàng, mã hàng, tên hàng sắp xếp tăng dần theo tên hàng.5.
- Liệt kê chi tiết các mặt hàng đã bán bao gồm các thông tin sau: Số hóa đơn, Mã sản phẩm (hàng), tên sản phẩm, đơn giá, số lượng, giảm giá và thành tiền