Cho cơ sở dữ liệu gồm các bảng như sau:
Qui định khóa chính của các bảng như sau:
Bảng
ChiTietVanTai
TrongTai
LoTrinh
Khóa chính
MaVT
MaTrongTai
MaLoTrinh
Yêu cầu:
1. Hãy viết lệnh SQL để tạo cấu trúc các bảng trên. Nhập dữ liệu.
create table trongtai
(matrongtai varchar(20) not null constraint pk primary key,
trongtaiqd int);
create table lotrinh
(malotrinh varchar(20) not null constraint pk1 primary key,
tenlotrinh varchar(20),
dongia numeric,
thoigianqd int);
create table chitietvantai
(mavt varchar(20) not null constraint pk2 primary key,
soxe varchar(20),
matrongtai varchar(20) constraint rk references trongtai(matrongtai),
malotrinh varchar(20) constraint rk2 references lotrinh(malotrinh),
soluongvt int,
ngaydi int,
ngayden int);
-1-
2. Viết câu lệnh SQL để hiển thị các thông tin gồm: SoXe, TenLoTrinh, SoLuongVT,
NgayDi, NgayDen, ThoiGianVT, CuocPhi. Trong đó:
ThoiGianVT = NgayDen-NgayDi +1.
CuocPhi = SoLuongVT * DonGia.
select c.soxe, b.tenlotrinh, c.soluongvt, c.ngaydi, c.ngayden, c.ngaydenc.ngaydi+1 as thoigianvt, c.soluongvt*b.dongia as cuocphi
from chitietvantai c, lotrinh b
where c.malotrinh=b.malotrinh;
3. Tạo view có tên là Cau3 để lập danh sách các xe có lộ trình vận tải là Đà Nẵng
hoặc có SoLuongVT vượt trọng tải qui định. View gồm các trường SoXe,
TenLoTrinh, SoLuongVT, TrongTaiQD, NgayDi, NgayDen.
create view cau3a as
select c.soxe, b.tenlotrinh, c.soluongvt, a.trongtaiqd, c.ngaydi, c.ngayden
from trongtai a, lotrinh b, chitietvantai c
where c.matrongtai=a.matrongtai and c.malotrinh=b.malotrinh and
(b.tenlotrinh='Da Nang' or c.soluongvt>a.trongtaiqd);
4. Viết lệnh SQL để liệt kê các xe có cước phí lớn nhất trong mỗi lộ trình gồm các
trường: SoXe, TenLoTrinh, CuocPhi (SoLuongVT*DonGia).
select c.soxe, b.tenlotrinh, c.soluongvt*b.dongia as cuocphi
from chitietvantai c, lotrinh b
where c.malotrinh=b.malotrinh and c.soluongvt*b.dongia =
(select max(soluongvt*dongia)
from chitietvantai, lotrinh
where chitietvantai.malotrinh= lotrinh.malotrinh and
c.malotrinh=chitietvantai.malotrinh);
5. Hãy viết lệnh SQL thực hiện các thao tác sau:
-
Thêm một bản ghi mới vào bảng ChiTietVanTai; dữ liệu phù hợp nhưng
không được nhập giá trị null.
-
Thay đổi trọng tải qui định (TrongTaiQD) bằng 15 đối với mã trọng tải
(MaTrongTai) có trung bình vận tải lớn hơn 8.
nsert into chitietvantai
values (11,'555','51','QN',9,01/02/1999,02/03/1999);
update trongtai
set trongtaiqd =
(select trongtaiqd
from trongtai
where trongtaiqd>8);
6. Viết lệnh SQL để liệt kê các xe có cước phí lớn nhất trong mỗi xe gồm các trường:
SoXe, TenLoTrinh, CuocPhi (SoLuongVT*DonGia).
select c.soxe, c.malotrinh, c.soluongvt*b.dongia as cuocphi
from chitietvantai c, lotrinh b
where c.soluongvt*b.dongia =
(select max(soluongvt*dongia)
from chitietvantai, lotrinh
where c.soxe=chitietvantai.soxe);
7. Lập danh sách các xe có thời gian vận tải quá thời gian quy định hoặc có
SoluongVT vượt trọng tải quy định (TrongtaiQD). Trong đó ThoigianVT = Ngayden –
Ngaydi + 1.
-2-
select c.soxe, c.malotrinh, b.thoigianqd, c.ngayden-ngaydi+1 as thoigianvt,
a.trongtaiqd, c.soluongvt
from chitietvantai c, lotrinh b, trongtai a
where c.malotrinh=b.malotrinh and c.matrongtai=a.matrongtai and (c.ngaydenngaydi+1>b.thoigianqd or c.soluongvt>a.trongtaiqd);
8. Hiển thị cước phí trung bình lớn của mỗi xe với CuocPhi =SoLuongVT*DonGia.
select c.soxe, avg(c.soluongvt*b.dongia) as cuocphi
from lotrinh b, chitietvantai c
group by c.soxe;
9. Hiển thị số xe có tổng số lượng vận tải lớn nhất trong tháng 5.
select c.soxe, c.malotrinh, c.soluongvt*b.dongia as cuocphi
from chitietvantai c, lotrinh b
where c.soluongvt*b.dongia =
(select max(soluongvt*dongia)
from chitietvantai, lotrinh
where c.soxe=chitietvantai.soxe) and month(ngayden)=5;
10. Hiển thị lộ trình tổng số lượng vận tải trung bình lớn hơn hoặc bằng 10.
select c.soxe, c.malotrinh, avg(c.soluongvt) as cuocphi
from chitietvantai c, lotrinh b
group by c.soxe,c.malotrinh
having avg(c.soluongvt)>=10;
-3-
- Xem thêm -
.DOC 
3 
591 
111 
