Đề Thi Và Bài Giải SQL :)

https://www.box.com/s/vi9iubnxnx6rej3abd3u

--Giai de SQL--
--De 1
--Cau 1
create database QuanLyDatGiaoHang
go
use QuanLyDatGiaoHang

create table MatHang
(
    MSMH smallint constraint pk_MSMH primary key,
    TenMH nvarchar(50) Not Null,
    DonGia money Not Null
)

create table DonDatHang
(
    MSDH smallint identity(1,1) constraint pk_MSDH primary key,
    MSMH smallint constraint fk_MSMH references MatHang(MSMH),
    SLDat int Not Null,
    DonGiaDH money Not Null,
    NgayDH smalldatetime Not Null,
    KhachHang nvarchar(50) Not Null
)

create table PhieuGiaoHang
(
    MSPG smallint identity(1,1) constraint pk_MSPG primary key,
    MSDH smallint constraint fk_MSDH references DonDatHang(MSDH),
    NgayGH smalldatetime Not Null,
    SLGiao int Not Null
)

--Cau 2
--a
alter table DonDatHang
add
    constraint chk_SLDat check(SLDat > 0)
--b
alter table MatHang
add
    constraint un_TenMH unique(TenMH)
   
--Cau 3
--a
create view vwGiaoHang(MSDH,TongDaGiao)
as
    select MSDH,sum(SLGiao) as TongGiao
    from PhieuGiaoHang
    group by MSDH
--b
create view vwDDHChuaGiao
as
    select MSDH,MSMH,SLDat,DonGiaDH,NgayDH,KhachHang
    from DonDatHang
    where MSDH NOT IN
        ( select MSDH
          from PhieuGiaoHang
        )

--Cau 4
create trigger itrg_PhieuGiaoHang on PhieuGiaoHang
for Insert
as
if (select NgayGH from INSERTED) < (select NgayDH from DonDatHang)
    begin
        print ' Ngay giao hang khong nho hon ngay dat hang'
        rollback tran
    end
if (select SLGiao from INSERTED) > (select SLDat from DonDatHang)
    begin
        print ' So luong giao khong lon hon so luong dat'
        rollback tran
    end
   
--Cau 5
create proc sp_TongGiao (@MSDH smallint)
as
select MSDH,sum(SLGiao)
from PhieuGiaoHang
where MSDH=@MSDH
group by MSDH

--De 2
--Cau 1
create database QuanLyThi
go
use QuanLyThi

create table ThiSinh
(
    MSTS smallint constraint pk_MSTS primary key,
    Ho nvarchar(20) Not Null,
    Ten nvarchar(10) Not Null,
    Ngaysinh smalldatetime Not Null,
    SBD int Not Null,
    PhongThi char(10)
)

create table MonThi
(
    MSMon smallint constraint pk_MSMon primary key,
    TenMon nvarchar(50) Not Null
)

create table DiemMonThi
(
    MSTS smallint constraint fk_MSTS references ThiSinh(MSTS),
    MSMon smallint constraint fk_MSMon references MonThi(MSMon),
    DiemThi float Not Null,
    constraint pk_MSTS_MSMon primary key(MSTS,MSMon)
)

--Cau 2
--a
alter table ThiSinh
add
    constraint un_SBD unique(SBD)
--b
alter table DiemMonThi
add
    constraint chk_DiemThi check(DiemThi>=0 or DiemThi<=10)

--Cau 3
--a
create view vwBoThi
as
    select ThiSinh.MSTS,Ho,Ten,Ngaysinh,SBD,PhongThi
    from ThiSinh
    where ThiSinh.MSTS NOT IN
        (
            select MSTS
            from DiemMonThi
        )
--b
create view vwKetQuaThi(SBD,Ho,Ten,NgaySinh,PhongThi,TongDiem)
as
    select TOP 100 PERCENT SBD,Ho,Ten,Ngaysinh,PhongThi,sum(DiemThi)
    from ThiSinh,DiemMonThi
    where ThiSinh.MSTS=DiemMonThi.MSTS
    group by SBD,Ho,Ten,Ngaysinh,PhongThi
    order by PhongThi,SBD
   
--Cau 4
create trigger utrig_ThiSinh on ThiSinh
for update
as
if (
        select count(MSTS)
        from ThiSinh,INSERTED
        where ThiSinh.PhongThi=INSERTED.PhongThi
    ) >= 25
begin
    print 'So thi sinh cua phong thi da du 25 thi sinh'
    rollback tran
end

--Cau 5
create view vwSBD
as
    select STT=rank() over(order by Ten,Ho)
    from ThiSinh
   
create proc sp_DienSBD
as
    update vwSBD
    set SBD=STT

--De 3
--Cau 1
create database QuanLyLuongSanPham
go
use QuanLyLuongSanPham

create table NhanVien
(   
    MaNV smallint constraint pk_MaNV primary key,
    Ho nvarchar(20) Not Null,
    Ten nvarchar(10) Not Null,
    LoaiNV smallint Not Null
)

create table SanPham
(
    MaSP smallint constraint pk_MaSP primary key,
    TenSP nvarchar(50) Not Null,
    DonGiaSX money Not Null
)

create table KQSX
(
    MaNV smallint,
    NgaySX smalldatetime,
    MaSP smallint constraint fk_MaSP references SanPham(MaSP),
    SoLuong int Not Null,
    constraint pk_MaNV_NgaySX primary key(MaNV,NgaySX)
)

--Cau 2
--a
alter table SanPham
add
    constraint un_TenSP unique(TenSP)
--b
alter table KQSX
add
    constraint df_NgaySX default Getdate() for NgaySX

--Cau 3
--a
create view vwKQSX(MaNV,MaSP,SoLuong)
as
select MaNV,MaSP,SoLuong
from KQSX
where NgaySX=Getdate()
--b
create view vwLuongSX(MaNV,Luong)
as
select MaNV,sum(SoLuong*DonGiaSX) as Luong
from KQSX,SanPham
where KQSX.MaSP=SanPham.MaSP and Month(NgaySX)=Month(Getdate())
group by MaNV

--Cau 4
create trigger itrg_KQSX on KQSX
for insert
if (select LoaiNV from INSERTED)<>1
begin
    print 'Phai la cong nhan san xuat'
    rollback tran
end

--Cau 5
create proc sp_LuongThang(@thang smallint,@nam smallint)
as
if (@thang Is Null or @nam Is Null or @thang >12)
begin
    return 1
end
else
begin
    select NhanVien.MaNV,Ho,Ten,sum(DonGiaSX*SoLuong) as Luong
    from NhanVien,KQSX,SanPham
    where NhanVien.MaNV=KQSX.MaNV and SanPham.MaSP=KQSX.MaSP
        and Month(NgaySX)=@thang and Year(NgaySX)=@nam
    group by NhanVien.MaNV,Ho,Ten
end

--De 4
--Cau 1
create database QuanLyMauGiao
go
use QuanLyMauGiao

create table CapHoc
(
    MaCH smallint constraint pk_MaCH primary key,
    TenCH nvarchar(10) Not Null,
)

create table GiaoVien
(
    MaGV smallint constraint pk_MaGV primary key,
    HoTen nvarchar(50) Not Null,
    NgaySinh smalldatetime Not Null,
    LoaiGV smallint Not Null
)

create table LopHoc
(
    MaCH smallint,
    STTLop smallint,
    MaGVDH smallint constraint fk_MaGVDH references GiaoVien(MaGV),
    MaGVBM smallint constraint fk_MaGVBM references GiaoVien(MaGV),
    constraint pk_MaCH_STTLop primary key(MaCH,STTLop)
)

--Cau 2
--a
alter table LopHoc
add
    constraint uni_MaGVDH unique(MaGVDH)
--b
alter table GiaoVien
add
    constraint df_LoaiGV default 0 for LoaiGV

--Cau 3
--a
create view vwKiemTraPCGVDH(MaGV,HoTen)
as
select MaGV,HoTen
from GiaoVien
where MaGV Not In (select MaGVDH from LopHoc) and LoaiGV=0
--b
create view vwPhanCong(MaGV,HoTen,LoaiGV,MaCH,STTLop)
as
select MaGV,HoTen,LoaiGV,MaCH,STTLop
from GiaoVien,LopHoc
where MaGV=MaGVDH or MaGV=MaGVBM

--Cau 4
create trigger utrg_GiaoVien on GiaoVien
for update
update LopHoc
set MaGVDH = (select MaGV from INSERTED)
where MaGVDH = (select MaGV from DELETED)
update LopHoc
set MaGVBM= (select MaGV from INSERTED)
where MaGVBM = (select MaGV from DELETED)

--Cau 5
create proc sp_PhanCong(@MaGV smallint)
as
if exists(select MaGV from GiaoVien where (MaGV Not In (select MaGVDH from LopHoc)
            or MaGV Not In (select MaGVBM from LopHoc))
begin
print 'Co loi'
else
begin
select MaGV,MaCH,STTLop
from GiaoVien,LopHoc
where (MaGV=MaGVDH or MaGV=MaGVBM)
        and @MaGV=MaGV
end

--De 5
--Cau 1
create database QuanLyVatTu
go
use QuanLyVatTu

create table VatTu
(
    MaVT smallint constraint pk_MaVT primary key,
    TenVT nvarchar(50) Not Null,
    NgayMua smalldatetime Not Null
)

create table PhongBan
(
    MaPB smallint constraint pk_MaPB primary key,
    TenPB nvarchar(50) Not Null
)

create table CapCho
(
    MaVT smallint constraint fk_MaVT references VatTu(MaVT),
    MaPB smallint constraint fk_MaPB references PhongBan(MaPB),
    NgayCap smalldatetime Not Null,
    constraint pk_MaVT_CapCho primary key(MaVT)
)

--Cau 2
--a
alter table PhongBan
add
constraint un_TenPB unique(TenPB)
--b
alter table CapCho
add
constraint df_NgayCap default Getdate() for NgayCap

--Cau 3
--a
create view vwLietKe(TenPB,TenVT,NgayCap)
as
select TOP 100 PERCENT TenPB,TenVT,NgayCap
from VatTu,PhongBan,CapCho
where VatTu.MaVT=CapCho.MaVT and PhongBan.MaBP=CapCho.MaPB
order by TenPB
--b
create view vwThongKe(MaPB,TenPB,SoLuong)
as
select PhongBan.MaPB,TenPB,count(MaVT)
from PhongBan,CapCho
where PhongBan.MaPB=CapCho.MaPB
group by PhongBan.MaPB,TenPB

--Cau 4
create trigger utrig_CapCho on CapCho
for update
if NgayCap < (select NgayMua from VatTu,INSERTED where VatTu.MaVT = INSERTED.MaVT)
begin
    print 'error'
    rollback tran
end

--Cau 5
create proc sp_CapCho(@MaVT smallint,@MaPB smallint,@NgayCap smalldatetime)
as
if @NgayCap >(select NgayMua from VatTu where @MaVT=MaVT)
begin
    insert CapCho(MaVT,MaPB,NgayCap)
    values (@MaVT,@MaPB,@NgayCap)
    return 0
end
else
begin
    return 1
end

https://www.box.com/s/vi9iubnxnx6rej3abd3u