Tên trường kết xuất: Biểu thức Ví dụ: Tong: [Toan] +[Ly]+ [Hoa]
Trong đó Tong (Tổng điểm) là trường kết xuất được tính từ ba trường Toan (Toán), Ly (Lý), Hoa (Hóa) đã có sẵn trong bảng.
II. CÂU HỎI VÀ BÀI TẬP 1.Câu hỏi trắc nghiệm
Câu 1: Khi tạo truy vấn mới. Dữ liệu nguồn được lấy từ:
a. Bảng
b. Query được tạo trước đó.
c. Cả hai phương án trên.
Câu 2: Để tạo một bảng từ CSDL có sẵn dùng truy vấn nào sau đây:
a. Select Query b. Crosstab Query c. Make table Query d. Update Query
2.Bài tập
Bài tập 1 : CƠ SỞ DỮ LIỆU QUẢN LÝ KHO VẬT TƯ Sử dụng công cụ query của MS Access để xây dựng các truy vấn sau:
1. Tạo truy vấn để lấy các thông tin về tình hình nhập và xuất vật tư ở tất cả các kho của công ty, gồm các trường: Mã vật tư, Mã kho, Số hóa đơn, Ngày, Số lượng.
Đặt tên truy vấn là QrCau1.
2. Tạo truy vấn lấy danh sách vật tư nhập vào các kho “01”, “02”, “03” và
“04” trong tháng 1 năm 2013, gồm các trường: Mã kho, Mã vật tư, Số hóa đơn, Giá nhập, Số lượng (Lưu ý: chỉ lấy các hóa đơn nhập).
Đặt tên truy vấn là QrCau2.
3. Tạo truy vấn lấy ra thông tin xuất kho của tất cả các kho trong tháng 12 năm 2012, yêu cầu hiển thị các trường sau: Mã kho, Mã vật tư, Số hóa đơn, Giá xuất, Số lượng (Lưu ý: chỉ lấy các hóa đơn xuất).
Trong đó: Giá xuất = Giá nhập + 10% Giá nhập.
Đặt tên truy vấn là QrCau3.
4. Tạo truy vấn lấy thông tin chi tiết thanh toán của các hóa đơn nhập và xuất vật tư, gồm các trường sau: Số hóa đơn, Mã vật tư, Mã kho, Tên kho, Khách hàng, Số lượng, Giá, Khuyến mãi, Thành tiền.
Trong đó:
Giá = Giá nhập + 10% Giá nhập nếu là hóa đơn xuất; Giá= Giá nhập nếu là hóa đơn nhập;
Khuyến mãi: nếu nhập tháng 12 được khuyến mãi 2% Giá nhập,còn lại không có khuyến mãi.
Thành tiền = Số lượng * Giá – Khuyến mãi.
Đặt tên truy vấn là QrCau4.
5. Tạo truy vấn lấy danh sách nhập gạch men, gạch lát của Viglacera và Prime, yêu cầu hiển thị các trường: Mã vật tư, Tên vật tư, Xuất xứ, Đơn vị, Giá nhập, Hết hàng, Khuyến mãi, Thành tiền. Đặt tên truy vấn là QrCau5.
6. Tạo truy vấn lấy danh sách xi măng xuất sau ngày 15 hàng tháng, yêu cầu hiển thị các trường: Mã vật tư, Tên vật tư, Xuất xứ, Số hóa đơn, Ngày, Loại, Số lượng, Thành tiền. Đặt tên truy vấn là QrCau6.
7. Thống kê tổng số lượng vật tư, tổng thành tiền của từng hóa đơn. Đặt tên truy vấn là QrCau7.
8. Thống kê số lượng trung bình vật tư nhập xuất theo từng ngày. Đặt tên truy vấn là QrCau8.
9. Thống kê tổng số lượng vật tư nhập xuất theo từng kho. Đặt tên truy vấn là QrCau9.
10. Thống kê số lượng vật tư nhập xuất của từng kho, theo tháng. Đặt tên truy vấn là QrCau10.
11. Tính tổng số vật tư nhập và vật tư xuất theo từng kho. Đặt tên truy vấn là QrCau11.
12. Tính tổng Thành tiền nhập và tổng Thành tiền xuất của từng kho. Đặt tên truy vấn là QrCau12.
13. Sử dụng query để điều chỉnh cột Giá nhập trong bảng Vật tư: giá vật tư trong năm 2013 tăng thêm 2% so với giá cũ. Đặt tên truy vấn là QrCau13.
14. Thêm cột Ghi chú vào cuối bảng Vật tư, dùng truy vấn điền cột theo nguyên tắc sau:
Nếu vật tư đã hết hàng thì điền “Không xuất kho”, còn lại để trống.
Đặt tên truy vấn là QrCau14.
15. Tạo bảng mới với tên VTNhap, lấy ra các hóa đơn nhập kho, yêu cầu hiển thị các trường sau: Số hóa đơn, Mã vật tư, Mã kho, Ngày, Loại, Số lượng, Thành tiền. Đặt tên truy vấn là QrCau15.
16. Tạo bảng mới tên VTXuat, lấy ra các hóa đơn xuất kho, yêu cầu hiển thị các trường sau: Số hóa đơn, Mã vật tư, Mã kho, Ngày, Loại, Số lượng, Thành tiền. Đặt tên truy vấn là QrCau16..
17. Dùng query để xóa đi các bản ghi có số lượng <100 trong bảng VTnhap vừa tạo ra. Đặt tên truy vấn là QrCau17.
18. Dùng query để xóa đi các bản ghi của kho số 2 và số 4 trong bảng VTxuat vừa tạo ra. Đặt tên truy vấn là QrCau18.
19. Dùng query để nhập bảng VTxuat vào bảng VTnhap. Đặt tên truy vấn là QrCau19.
Bài tập 2: CƠ SỞ DỮ LIỆU QUẢN LÝ NHÂN SỰ Sử dụng công cụ query của MS Access để xây dựng các truy vấn sau:
1. Tạo truy vấn hiển thị các trường sau: Mã cán bộ, Họ và tên, Giới tính, Hệ số lương, Hệ số phụ cấp, Tên chức vụ, Lương, Phụ cấp.
Trong đó:
Lương = 1080000*Hệ số lương;
Phụ cấp = 1080000*Hệ số phụ cấp.
Đặt tên truy vấn là QrCau1.
2. Tạo truy vấn tính thu nhập cho tất cả nhân viên các phòng với các trường sau: Mã cán bộ, Họ tên, Ngày sinh, Mã phòng, Hệ số lương, Hệ số phụ cấp, Ngày công, Lương, Phụ cấp, Thưởng, Bảo hiểm, Thu nhập.
Trong đó:
Thưởng = 700000 với những ai có số ngày công >=30, Thưởng=
400000 với những ai có số ngày công >=27, còn lại Thưởng = 0;
Tại trường mới, bấm phải chuột, chọn Build, gõ:
thuong:iif([ngaycong]>=30,700000,iif([ngaycon g]>=27,400000,0))
Bảo hiểm = 7% Lương;
Tại trường mới, bấm phải chuột, chọn Build, gõ:
baohiem:0.07*[Luong]
Thu nhập = Lương+Phụ cấp+Thưởng – Bảo hiểm.
Đặt tên truy vấn là QrCau2.
3. Tạo danh sách lương cho phòng Tổng hợp hoặc phòng Kế toán, yêu cầu hiển thị đầy đủ các trường như sau: Mã cán bộ, Họ tên, Ngày sinh, Mã phòng, tên phòng, Hệ số lương, Hệ số phụ cấp, Ngày công. Đặt tên truy vấn là QrCau3.
Tại vùng Criteria của trường Tên phòng, gõ:
Tổng hợp or Kế toán
4. Hãy tạo danh sách các nhân viên nam của phòng Kế toán hoặc phòng Tổng hợp có số ngày công >=28. Yêu cầu hiển thị các trường:
Họ tên, Ngày sinh, Tên phòng,giới tính, ngày công. Đặt tên truy vấn là QrCau4.
5. Tạo danh sách lương của các nhân viên có Họ tên bắt đầu bằng chữ “N*” hoặc “L*”, là nhân viên của phòng Kế toán hoặc phòng Tổ chức, có hệ số lương từ 4.0 trở lên và có số Ngày công không dưới 27. Yêu cầu hiển thị các trường: Mã cán bộ, Họ và tên, Ngày sinh, Mã phòng,.
Đặt tên truy vấn là QrCau5.
6. Tạo danh sách thống kê tổng tiền Thưởng, tổng Thu nhập, Thu nhập cao nhất, Thu nhập thấp nhất của từng phòng, gồm các trường: Mã phòng, Tên phòng, tổng Thưởng, tổng Thu nhập, Thu nhập cao nhất, Thu nhập thấp nhất (không hiển thị số thập phân). Đặt tên truy vấn là QrCau6.
7. Tính tổng số tiền bảo hiểm mà các cán bộ phải nộp theo từng phòng. Đặt tên truy vấn là QrCau7.
8. Thống kê tổng số nhân viên, số nhân viên nam, nhân viên nữ theo từng phòng. Đặt tên truy vấn là QrCau8.
9. Tính lương trung bình theo từng chức vụ. Đặt tên truy vấn là QrCau9.
10. Tính thu nhập trung bình của các cán bộ nam, cán bộ nữ theo từng phòng. Đặt tên truy vấn là QrCau10.
11. Tạo bảng mới KhenThuong chứa danh sách những người được khen thưởng (có thưởng), gồm các trường: Họ và tên, Ngày sinh, Giới tính, Tên chức vụ, Tên phòng, Ngày công, Lương, Thưởng, Thu nhập.
Đặt tên query là QrCau11.
12. Sử dụng query để cộng thêm cho mỗi người 01 Ngày công vào bảng Cán bộ. Đặt tên query là QrCau12.
13. Dùng query để xoá trong bảng Nhân viên những bản ghi nào có số Ngày công = 0.
Đặt tên query là QrCau13.
14. Tạo bảng phòng ban bổ sung và nhập dữ liệu như sau:
Sử dụng query để nhập dữ liệu của bảng PhongBanBoSung vào bảng Phòng ban đã có. Đặt tên query là QrCau14.
15. Sử dụng query để tạo bảng mới có chứa danh sách thu nhập của phòng Kế toán (tên bảng: ThuNhapKeToan) gồm các trường sau: Mã nhân viên, Họ tên, Ngày sinh, Tên phòng, Hệ số lương, Hệ số phụ cấp, Ngày công, Lương, Phụ cấp, Thưởng, Bảo hiểm, Thu nhập (chỉ lấy phòng Kế toán).
Đặt tên query là QrCau15.
Bài tập 3 : CƠ SỞ DỮ LIỆU QUẢN LÝ SÁCH
Sử dụng công cụ query của MS Access để xây dựng các truy vấn sau:
1. Tổng hợp thông tin sách mượn, yêu cầu hiển thị các trường sau: Số phiếu, Số thẻ, Tên độc giả, Lớp, Khoa, Mã sách, Thư mục, Tên sách, Tác giả, Ngày mượn, Hạn trả, Tình trạng.Đặt tên truy vấn là QrCau1.
2. Tổng hợp thông tin trả sách, yêu cầu hiển thị các trường sau: Số thẻ, Tên độc giả, Ngày sinh, Lớp, Khoa, Mã sách, Tên sách, Ngày mượn, Hạn trả, Số ngày mượn, Hình thức mượn, Tình trạng, Ghi chú.
Trong đó: Số ngày mượn = Hạn trả - Ngày mượn (nếu trả trong ngày tính 1 ngày);
Hình thức mượn: nếu trả sách trong ngày thì ghi “Đọc tại chỗ”, mượn trong vòng một tuần thì ghi “Mượn ngắn hạn”, mượn quá một tháng thì ghi
“mượn dài hạn”.
Ghi chú: Nếu sách đã đến hạn trả và chưa trả (tình trạng là False) thì ghi
“Quá hạn”, còn lại bỏ trống. Đặt tên truy vấn là QrCau2.
3. Tạo danh sách sách mượn của sinh viên hai khoa Kế toán và Bảo hiểm.
Yêu cầu hiển thị đầy đủ các trường như sau: Số thẻ, Tên độc giả, Ngày sinh, Lớp, Mã sách, Tên sách, Ngày mượn, Hạn trả, Tình trạng, Ghi chú. Đặt tên truy vấn là QrCau3.
4. Lấy danh sách các độc giả mượn sách trong năm 2012 của các khoa:
Bảo hiểm, Công tác Xã hội, Quản lý. Yêu cầu hiển thị đầy đủ các trường như trong bảng Độc giả. Đặt tên truy vấn là QrCau4.
5. Tạo truy vấn lấy thông tin về các thẻ mượn đã quá hạn, yêu cầu hiển thị đầy đủ các trường như trong bảng Độc giả. Đặt tên truy vấn là QrCau5.
6. Thống kê số đầu sách của từng danh mục sách có trong thư viện. Đặt tên truy vấn là QrCau6.
7. Thống kê số lượt mượn của mỗi đầu sách. Đặt tên truy vấn là QrCau7.
8. Đếm số thẻ độc giả của từng lớp, theo khoa. Đặt tên truy vấn là QrCau8.
9. Tính số lượt mượn từng ngày của mỗi mã sách. Đặt tên truy vấn là QrCau9.
10. Tạo danh sách các Tên sách có chữ cái đầu tiên là “G”, do sinh viên khoa Kế toán hoặc khoa Quản lý mượn trong tháng 12 năm 2012. Yêu cầu hiển thị đầy đủ các trường sau: Số thẻ, Số phiếu, Mã sách, Tên sách, Ngày mượn, Hạn trả, Tình trạng, Ghi chú. Đặt tên truy vấn là QrCau10.
11. Thống kê số lượt mượn sách của sinh viên mỗi Khoa theo từng tháng.
Đặt tên truy vấn là QrCau11.
12. Dùng truy vấn để điền vào cột Ghi chú của bảng Sách mượn, nếu sách đến hạn chưa trả thì ghi “Quá hạn”, nếu đến hạn và đã trả rồi thì ghi là “Đã trả”, còn lại bỏ trống. Đặt tên truy vấn là QrCau12.
13. Tạo thêm một cột Số trang vào cuối bảng sách, tạo một bảng bổ sung như sau:
Dùng truy vấn để nhập số trang từ bảng bổ sung vào trường số trang của bảng sách. Đặt tên truy vấn là QrCau13.
14. Sử dụng truy vấn để tạo bảng mới chứa danh sách các Giáo trình, yêu cầu hiển thị các trường: Mã sách, Tên sách, Tác giả, Nhà xuất bản, Năm xuất bản, Giá bìa, Số trang. Đặt tên bảng mới tạo là GiaoTrinh.
Đặt tên truy vấn là QrCau14.
15. Sử dụng truy vấn để xóa đi trong bảng độc giả các bản ghi về các thẻ mượn đã hết hạn sử dụng. Đặt tên truy vấn là QrCau15.
16. Sử dụng truy vấn để tạo bảng mới chứa danh sách độc giả của các khoa Bảo hiểm, Kế toán, Công tác xã hội. Yêu cầu hiển thị các trường như trong bảng Độc giả. Đặt tên truy vấn là QrCau16.
Bài tập 4: CƠ SỞ DỮ LIỆU QUẢN LÝ BÁN HÀNG Sử dụng công cụ query của MS Access để xây dựng các truy vấn sau:
1. Tạo query cau1 đưa ra thông tin gồm: Mã khách, Tên khách, Mã hóa đơn, Ngày bán, Mã hàng, Tên hàng, Số lượng, Đơn giá. Chỉ gồm các khách hàng mua “Tivi’’ hoặc “Tủ lạnh’’và có Số lượng >=5
Tại trường Tên hàng, vùng Criteria, gõ: Tivi or Tủ lạnh Tại trường Thành tiền, vùng Criteria, gõ: >=5
2. Đưa ra thông tin gồm : Mã khách, Tên khách, Mã hóa đơn, Ngày bán, Mã hàng, Tên hàng, Số lượng, Đơn giá, Thành tiền. Trong đó:
Thành tiền = Số lượng * Đơn giá, biết rằng nếu số lượng hàng khách mua >=5 thì được giảm 20% đơn giá. Đặt tên query là cau1.
Tại trường mới, bấm phải chuột, chọn Build, gõ:
Thanhtien: iif([soluong]>=5,0.8*[soluong]*[dongia],[soluong]*[dongia]) 3. Tạo query cau2 đưa ra thông tin gồm: Mã khách, Tên khách, Mã hóa đơn, Ngày bán, Mã hàng, Tên hàng, Số lượng, Đơn giá, Thành tiền, Thưởng. Biết rằng:
- Nếu khách hàng có hóa đơn thanh toán >10000000 được thưởng 3000000.
- Nếu khách hàng có hóa đơn thanh toán >10000000 được thưởng 2000000.Còn lại thưởng 1000000.
Tại trường mới, bấm phải chuột, chọn Build, gõ:
Thuong:iif([thanhtien]>10000000,3000000,iif(([thanhtien]>10000 000,2000000,1000000))
4. Tạo Qrc4 đưa ra thông tin gồm các trường: Mã khách, Tên khách, Mã hóa đơn, Mã hàng, Ngày bán, Số lượng, Đơn giá. Chỉ lấy các bản ghi thỏa mãn những điều kiện sau: mua tủ lạnh hoặc máy giặt với số lượng >=5, trong năm 2011.
Tại trường Tên hàng, vùng Criteria, gõ: Tủ lạnh or máy giặt Tại trường Số lượng, vùng Criteria, gõ: >=5
Tại trường Ngày bán, vùng Criteria, gõ: **/**/2011 (cách 1) Hoặc gõ year([ngayban])=2011 (cách 2)
5. Tạo Qrc5 gồm các trường như trong Cau2, chỉ lấy các bản ghi thỏa mãn điều kiện: khách hàng họ Nguyễn, mua hàng trong tháng 2 hoặc tháng 3 năm 2012.
Tại trường Họ tên, vùng Criteria, gõ: Nguyễn*
Tại trường Ngày bán, vùng Criteria, gõ: month([ngayban])=2 or month([ngayban])=3 and year([ngayban])=2012
6. Thống kê tổng (sum) số lượng của từng tên hàng. Đặt tên query là cau6.
7. Thống kê tổng thành tiền, thành tiền lớn nhất theo tên khách hàng. Đặt tên query là cau5.
8. Thống kê tổng tiền bán được theo từng năm cho mỗi khách hàng, tổng số lượng hàng bán trong từng năm. Đặt tên query là cau6.
9. Tạo query cau7 để tạo bảng gồm Tên khách, Ngày bán,Tên hàng,Số lượng, Đơn giá, Thành tiền, Thưởng.Đặt tên bảng là BANGMOI.
10. Tạo query cau8 để cập nhật dữ liệu cho cột thưởng trong BANGMOI. Biết thưởng được tính như câu 2.
11. Tạo query cau9 đưa ra thông tin gồm: Mã khách, Tên khách, Mã hóa đơn, Ngày bán, Mã hàng, Tên hàng, Số lượng, Đơn giá, Thành tiền. Chỉ bao gồm các khách hàng có họ tên bắt đầu bằng chữ ’’T’’ hoặc ’’N’’.
12. Tạo query cau10 đưa ra thông tin gồm: Mã khách, Tên khách, Mã hóa đơn, Ngày bán, Mã hàng, Tên hàng, Số lượng, Đơn giá, Thành tiền. Chỉ bao gồm các khách hàng có họ tên bắt đầu bằng chữ ’’T’’ hoặc ’’N’’ và mua hàng với số lượng >10.
13. Tạo query cau11 dùng để xóa khỏi BANGMOI tất cả các bản ghi có ngày bán hàng trong năm 2011.
14. Tạo query cau12 dùng để thêm BANGMOI tất cả các bản ghi vừa xóa trong cau11.
Bài tập 5: CƠ SỞ DỮ LIỆU QUẢN LÝ ĐIỂM
Sử dụng công cụ query của MS Access để xây dựng các truy vấn sau:
1. Tạo danh sách sinh viên gồm các trường sau : Mã sinh viên, Họ và tên (Họ và + Tên), Giới tính, Ngày sinh, Tuổi, Mã lớp, Mã khoa, Mã môn học, Điểm quá trình, Điểm thi lần 1, Điểm thi lại, Điểm môn học. Đặt tên truy vấn là QrCau1.
2. Tạo danh sách các bạn nữ của hai khoa Công tác Xã hội và Bảo hiểm Xã hội, yêu cầu hiển thị các trường: Mã sinh viên, Họ tên, Ngày sinh, Giới tính, Mã lớp, Mã khoa. Đặt tên truy vấn là QrCau2.
3. Tạo danh sách điểm cho sinh viên Đại học khóa 8, gồm các trường sau: Mã sinh viên, Họ và tên (Họ và + Tên), Giới tính, Ngày sinh, Mã lớp, Mã khoa, Tên môn học, Điểm quá trình, Điểm thi lần 1, Điểm thi lại, Điểm môn học, Kết quả.
Trong đó: Điểm môn học = trung bình điểm quá trình và điểm thi lần 1 (điểm quá trình lấy 40%, điểm thi lấy 60%, nếu thi lại thì tính điểm thi lại thay vào điểm thi lần 1);
Kết quả: nếu điểm môn học dưới 5 thì điền “Học lại môn”, còn lại bỏ trống.
Đặt tên truy vấn là QrCau3.
4. Lập danh sách các sinh viên khoa Kế toán và khoa Bảo hiểm Xã hội phải thi lại (sau khi thi lần 1 có điểm tổng kết môn <5). Yêu cầu hiển thị các trường sau: Mã sinh viên, Họ và tên, Giới tính, Ngày sinh, Mã lớp, Tên khoa, Tên môn học, Điểm quá trình, Điểm thi lần 1, Điểm tổng kết môn. Trong đó:
Điểm tổng kết môn = (Điểm quá trình*4+Điểm thi lần 1*6)/10.
Đặt tên truy vấn là QrCau4.
5. Tính điểm trung bình chung và xếp loại cho sinh viên, yêu cầu hiển thị các trường sau: Mã sinh viên, Họ tên, Điểm trung bình, Xếp loại. Trong đó:
Điểm trung bình = trung bình cộng tất cả các Điểm môn học của sinh viên;
Xếp loại theo nguyên tắc sau:
- Nếu Điểm trung bình >=9 thì xếp loại xuất sắc;
- Nếu Điểm trung bình >=8 thì xếp loại giỏi;
- Nếu Điểm trung bình >=6.5 thì xếp loại khá;
- Nếu Điểm trung bình >=5 thì xếp loại trung bình; còn lại xếp loại Yếu.
Đặt tên truy vấn là QrCau5.
6. Tạo danh sách sinh viên không phải thi lại (không có Điểm tổng kết môn<5) gồm các trường sau : Mã sinh viên, Họ tên, Ngày sinh, Giới tính, Mã lớp, Mã khoa, Điểm tổng kết môn.
Trong đó, Điểm tổng kết môn tính như ở QrCau4.