Cho một hệ thống quản lý việc cho và nhận hàng cứu trợ đồng bào lũ lụt của chính quyền có lược đồ cơ sở dữ liệu quan hệ như sau:
Link đề thi
Câu 1: Tạo đầy đủ lược đồ cơ sở dữ liệu quan hệ như mô tả ở trên. Sinh viên tự định nghĩa kiểu dữ liệu cho các cột (0.5 điểm)
Câu 2: Chèn toàn bộ dữ liệu mẫu đã được minh họa ở trên vào tất cả các bảng một cách chính xác (0.5 điểm)
Câu 3: Liệt kê những hộ dân có từ 5 nhân khẩu trở lên (0.5 điểm)
Câu 4: Liệt kê các hộ dân là hộ nghèo và thuộc diện gia đình 'Thuong binh' và các hộ dân không là hộ nghèo và thuộc diện gia đình 'Cong nhan vien chuc' (0.5 điểm)
Câu 5: Liệt kê các đơn vị ủng hộ có MaDVUH bắt đầu bằng cụm từ "CTY" (0.5 điểm)
Câu 6: Liệt kê thông tin của toàn bộ các hộ dân, yêu cầu sắp xếp giảm dần theo số nhân khẩu (0.5 điểm)
Câu 7: Đếm số lượt ủng hộ diễn ra trong ngày hiện tại (của hệ thống) (0.5 điểm)
Câu 8: Liệt kê toàn bộ các loại diện gia đình trong bảng HO_DAN với yêu cầu mỗi loại diện gia đình được liệt kê một lần duy nhất (0.5 điểm)
Câu 9: Liệt kê MaDotUngHo, NgayUngHo, HoTenNguoiDaiDien, DiaChiNguoiDaiDien, SoDienThoaiLienLac, SoCMNDNguoiDaiDien của tất cả các đợt ủng hộ có trong hệ thống (0.5điểm)
Câu 10: Liệt kê MaHoDan, HoTenChuHo, ToDanPho, KhoiHoacThon, DienGiaDinh, MaDotNhanUngHo, NgayNhanUngHo của tất cả các hộ dân với yêu cầu những hộ dân nào chưa từng được nhận ủng hộ lần nào thì cũng liệt kê thông tin những hộ dân đó ra (0.5 điểm)
Câu 11: Liệt kê thông tin của các hộ dân đã từng được nhận ủng hộ dưới hình thức là 'Tien mat' hoặc là hộ dân thuộc diện hộ nghèo mà có số nhân khẩu dưới 3 người (0.5 điểm)
Câu 12: Liệt kê thông tin của các hộ dân chưa từng được nhận ủng hộ lần nào cả (0.5 điểm)
Câu 13: Liệt kê thông tin của các đơn vị ủng hộ đã từng ủng hộ dưới hình thức 'Tien mat' và chưa từng ủng hộ dưới hình thức 'Quan ao' (0.5 điểm)
Câu 14: Liệt kê thông tin của những đơn vị ủng hộ đã từng ủng hộ vào năm "2015" nhưng chưa từng ủng hộ vào năm "2016" (0.5 điểm)
Câu 15: Hiển thị thông tin của những đơn vị ủng hộ đã thực hiện ít nhất 2 đợt ủng hộ tính từ 30/07/2015 đến hết năm 2015 (0.5 điểm)
Câu 16: Đếm tổng số đợt được nhận ủng hộ của từng hộ dân trong năm 2016 với yêu cầu chỉ thực hiện tính đối với những
đợt được nhận ủng hộ có hình thức nhận ủng hộ là 'Tien mat' (0.5 điểm)
Câu 17: Liệt kê những ngày vừa diễn ra sự kiện có đơn vị ủng hộ đến trao hàng cứu trợ cho chính quyền,
vừa diễn ra sự kiện có hộ dân được chính quyền phân phối hàng cứu trợ, kết quả được sắp xếp tăng dần theo ngày tìm được (0.5 điểm)
Câu 18: Cập nhật cột DonViTinh trong bảng CHI_TIET_UNG_HO thành NULL đối với record có hình thức ủng hộ với TenHinhThucUngHo là 'Quan ao' và có ngày ủng hộ (NgayUngHo) trước ngày 30/12/2015 (0.5 điểm)
Câu 19: Xóa những hộ dân chưa từng được nhận một lần ủng hộ nào từ chính quyền (0.5 điểm)
Câu 20: Xóa những record trong bảng CHI_TIET_UNG_HO của những đợt ủng hộ diễn ra trước năm 2015 (0.5 điểm)
Lời giải:
3,select * from HO_DAN where SoNhanKhau >= 5
4,
select * from HO_DAN where LaHoNgheo='Dung' and DienGiaDinh='Thuong binh'
union
select * from HO_DAN where LaHoNgheo='Sai' and DienGiaDinh='Cong nhan vien chuc'
5,
select * from DON_VI_UNG_HO where MaDVUH like 'CTY%'
6,
select * from HO_DAN order by SoNhanKhau desc
7,
select count(*) as 'So luong' from DOT_UNG_HO where NgayUngHo = GETDATE()
8,
select distinct DienGiaDinh from HO_DAN
9,
select MaDotUngHo ,NgayUngHo,HoTenNguoiDaiDien,DiaChiNguoiDaiDien,SoDienThoaiLienLac,SoCMNDNguoiDaiDien
from DON_VI_UNG_HO, DOT_UNG_HO
where DON_VI_UNG_HO.MaDVUH = DOT_UNG_HO.MaDVUH
10,
select DOT_NHAN_UNG_HO.MaHoDan,HoTenChuHo,ToDanPho,KhoiHoacThon,DienGiaDinh,MaDotNhanUngHo,NgayNhanUngHo
from HO_DAN left join DOT_NHAN_UNG_HO
on HO_DAN.MaHoDan = DOT_NHAN_UNG_HO.MaHoDan
11,
select * from HO_DAN where MaHoDan in (
select DOT_NHAN_UNG_HO.MaHoDan
from HINH_THUC_UH,CHI_TIET_NHAN_UNG_HO,DOT_NHAN_UNG_HO,HO_DAN
where HINH_THUC_UH.MaHinhThucUH = CHI_TIET_NHAN_UNG_HO.MaDotNhanUngHo
and CHI_TIET_NHAN_UNG_HO.MaDotNhanUngHo = DOT_NHAN_UNG_HO.MaDotNhanUngHo
and TenHinhThucUngHo = 'Tien mat' or (LaHoNgheo = 'Dung' and SoNhanKhau < 3)
)
12,
select * from HO_DAN
where MaHoDan not in ( select MaHoDan from DOT_NHAN_UNG_HO )
13,
select * from DON_VI_UNG_HO where MaDVUH IN
(select MaDVUH from DOT_UNG_HO,CHI_TIET_UNG_HO,HINH_THUC_UH
where DOT_UNG_HO.MaDotUngHo = CHI_TIET_UNG_HO.MaDotUngHo
and CHI_TIET_UNG_HO.MaHinhThucUH = HINH_THUC_UH.MaHinhThucUH and TenHinhThucUngHo = 'Tien mat')
except
select * from DON_VI_UNG_HO where MaDVUH IN
(select MaDVUH from DOT_UNG_HO,CHI_TIET_UNG_HO,HINH_THUC_UH
where DOT_UNG_HO.MaDotUngHo = CHI_TIET_UNG_HO.MaDotUngHo
and CHI_TIET_UNG_HO.MaHinhThucUH = HINH_THUC_UH.MaHinhThucUH and TenHinhThucUngHo = 'Quan ao')
14,
select *
from DON_VI_UNG_HO
where MaDVUH in (select MaDVUH from DOT_UNG_HO
where year(DOT_UNG_HO.NgayUngHo) = 2015
) and MaDVUH in (select MaDVUH from DOT_UNG_HO
where year(DOT_UNG_HO.NgayUngHo) = 2016
)
15,
select * from DON_VI_UNG_HO where MaDVUH in (
select MaDVUH from DOT_UNG_HO
WHERE NgayUngHo BETWEEN '2015/7/30' and '2015/12/31'
group by MaDVUH
having COUNT(MaDotUngHo)>=2)
16,
select DOT_NHAN_UNG_HO.MaHoDan,HoTenChuHo,ToDanPho,count(DOT_NHAN_UNG_HO.MaDotNhanUngHo) as 'so lan'
from DOT_NHAN_UNG_HO,HO_DAN,CHI_TIET_NHAN_UNG_HO,HINH_THUC_UH
where DOT_NHAN_UNG_HO.MaHoDan = HO_DAN.MaHoDan and DOT_NHAN_UNG_HO.MaDotNhanUngHo = CHI_TIET_NHAN_UNG_HO.MaDotNhanUngHo
and CHI_TIET_NHAN_UNG_HO.MaHinhThucUH = HINH_THUC_UH.MaHinhThucUH and year(NgayNhanUngHo) = 2016 and TenHinhThucUngHo='Tien mat'
group by DOT_NHAN_UNG_HO.MaHoDan,HoTenChuHo,ToDanPho
17,
select NgayNhanUngHo as 'Ngay'
from DOT_UNG_HO,DOT_NHAN_UNG_HO
where NgayNhanUngHo = NgayUngHo
order by NgayNhanUngHo asc
18,
update CHI_TIET_UNG_HO
set DonViTinh = NULL
where MaHinhThucUH in
(select MaHinhThucUH from HINH_THUC_UH,CHI_TIET_NHAN_UNG_HO,DOT_NHAN_UNG_HO
where HINH_THUC_UH.MaHinhThucUH = CHI_TIET_NHAN_UNG_HO.MaHinhThucUH and
CHI_TIET_NHAN_UNG_HO.MaDotNhanUngHo=DOT_NHAN_UNG_HO.MaDotNhanUngHo and TenHinhThucUngHo = 'Quan ao' and NgayNhanUngHo < '2015-12-30')
19,
delete from HO_DAN where MaHoDan not in (
select MaHoDan from DOT_NHAN_UNG_HO)
20,
delete from CHI_TIET_UNG_HO
where MaDotUngHo in(
select MaDotUngHo
from DOT_UNG_HO
where year(NgayUngHo) < 2015
)