Bài tập ôn thi SQL

Chia sẻ: Nguyen Dinh Ka | Ngày: | Loại File: DOC | Số trang:15

Thêm vào BST

Báo xấu

370
lượt xem 164
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tài liệu tham khảo về bài tập ôn thi SQL

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài tập ôn thi SQL

Bài Tập Tuan 3 -Viết View Tuan 4 -Viết Cursor Tuan 5 -Viết SP Tuan 6 -Viết Trigger Tuan 7-Viết Function ------------------------------------VIEW------------------- --Tuần 3 câu 1 --Cho biết danh số xuất từng mặt hàng trong 6 tháng đầu năm create view cau_1 as select TENMH,sum(DONGIAXUAT*SOLUONGXUAT) as [DOANH THU] from HOA_DON_XUAT HD,CHI_TIET_XUAT CT,MAT_HANG MH where HD.SOHDX=CT.SOHDX and CT.MAMH=MH.MAMH and year(getdate())=year(NGAYXUAT) and month(NGAYXUAT)between 1 and 6 group by TENMH ----------------------------------------------------------- câu 2 --Cho biết mặt hàng có doanh số lớn nhất create view cau_2 as select TENMH,sum(DONGIAXUAT*SOLUONGXUAT) as [MAX DOANH THU] from MAT_HANG MH,CHI_TIET_XUAT CT where MH.MAMH=CT.MAMH group by TENMH having sum(DONGIAXUAT*SOLUONGXUAT)>=all(select sum(DONGIAXUAT*SOLUONGXUAT) from MAT_HANG MH,CHI_TIET_XUAT CT where MH.MAMH=CT.MAMH group by TENMH) ---------------------------------------------------------- câu 3 --Cho biết những mặt hàng có doanh thu lớn hơn 1b create view cau_3 as select TENMH,sum(DONGIAXUAT*SOLUONGXUAT) as [DOANH THU]
from MAT_HANG MH,CHI_TIET_XUAT CT where MH.MAMH=CT.MAMH group by TENMH having sum(DONGIAXUAT*SOLUONGXUAT)>1000000000 ----------------------------------------------------------- câu 4 --Cho biết đơn giá nhập trung bình của từng mặt hàng create view cau_4 as select TENMH,avg(DONGIANHAP) as [ĐƠN GIÁ NHẬP TB] from MAT_HANG MH,HOA_DON_NHAP HD,CHI_TIET_NHAP CT where HD.SOHDN=CT.SOHDN and MH.MAMH=CT.MAMH group by TENMH ----------------------------------------------------------- câu 5 --Cho biết thành tiền của từng mặt hàng theo từng hoá đơn và tổng tiền của từng hoá đơn ----------------------------------------------------------- câu 6 --Cho biết thông tin nhân viên chưa nhập hàng bao giờ create view cau_6 as select * from NHAN_VIEN where MANV not in (select MANV from HOA_DON_NHAP) ----------------------------------------------------------- câu 7 --Cho biết những mặt hàng nhập chưa bao giờ bán create view cau_7 as select * from MAT_HANG where MAMH not in (select MAMH from HOA_DON_NHAP) --------------------------------------------------------- câu 8 --Cho biết những nhà cung cấp cung ứng những mặt hàng giống nhà cung cấp 1 create view cau_8 as select TENNCC
from NHA_CUNG_CAP NCC,CHI_TIET_NHAP CT,MAT_HANG MH where NCC.MANCC=MH.MANCC and MH.MAMH=CT.MAMH and MH.MAMH in (select MAMH from NHA_CUNG_CAP NCC,MAT_HANG MH where NCC.MANCC=MH.MANCC and NCC.MANCC=1) --------------------------------------------------------- câu 9(hỏi lại) --Tạo và mã hoá view : cho biết những mặt hàng chưa được xuất trong năm hiện tại create view cau_9 with encryption as select TENMH from MAT_HANG where MAMH in (select MH.MAMH from MAT_HANG MH,HOA_DON_XUAT HD,CHI_TIET_XUAT CT where MH.MAMH=CT.MAMH and HD.SOHDX=CT.SOHDX and NGAYXUAT=year(getdate())) câu 10 --Cho biết danh sách mặt hàng thuộc loại 1 có sử dụng WITH CHECK OPTION. Sau đó chèn thử 1 bảng ghi vào view này create view cau_10 as select * from MAT_HANG where MAMH=1 with check option insert into cau_10 values(0,1,N'MẶT HÀNG 1',N'TẤN',N'GOOD') select * from cau_10 -----------------------------------CURSOR------------------ --Tuần 4 câu 1 --Viết một đoạn lệnh sử dụng con trỏ để hiển thị danh sách khách hàng. Nếu khách hàng đã mua 10 lần thì thông báo được -- khuyến mại 100 USD, mua 20 lần khuyến mại 200 USD. Distinct: bo nhug ket qua trug nhau
--khai báo biến cục bộ declare @MAKH char(6),@solan tinyint --khai báo biến cursor declare cur_khuyenmai cursor dynamic for select distinct MAKH from HOA_DON_XUAT --mở cursor open cur_khuyenmai --đọc dữ liệu fetch first from cur_khuyenmai into @MAKH while @@fetch_status=0 begin select @solan=count(*) from HOA_DON_XUAT where @MAKH=MAKH --print select * from KHACH_HANG where @MAKH=MAKH if @solan>=3 print 'Khách hàng: '+@MAKH+'được khuyến mãi 20% vì đã mua: '+cast(@solan as char(3))+'lần' else print 'Khách hàng: '+@MAKH+'được khuyến mãi 10% vì đã mua: '+cast(@solan as char(3))+'lần' fetch next from cur_khuyenmai into @MAKH end --đóng cursor close cur_khuyenmai deallocate cur_khuyenmai ---------------------------------------------------------- câu 2 --Thêm một thuộc tính trong So_MH_CC kiểu dữ liệu số nguyên trong bảng NhaCungCap để lưu số lượng mặt hàng mà công ty -- đó cung cấp. Viết cursor cập nhật cho thuộc tính này alter table NHA_CUNG_CAP add SO_MH_CC int declare @MANCC char(6),@SO_MH_CC int declare cur_SLMH cursor forward_only for select MANCC from NHA_CUNG_CAP open cur_SLMH fetch next from cur_SLMH into @MANCC
while @@fetch_status=0 begin select SO_MH_CC=sum(SOLUONGNHAP) from NHA_CUNG_CAP NCC,MAT_HANG MH,CHI_TIET_NHAP CTN where NCC.MANCC=MH.MANCC and CTN.MAMH=MH.MAMH and @MANCC=NCC.MANCC print 'Đang cập nhật nhà cung cấp:'+@MANCC update NHA_CUNG_CAP set SO_MH_CC=@SO_MH_CC where MANCC=@MANCC print 'Đang đọc' fetch next from cur_SLMH into @MANCC end close cur_SLMH deallocate cur_SLMH ----------------------------------------------------------- câu 3 --Thêm thuộc tính TongMH trong bang HoaDonNhap lưu thông tin tổng số mặt hàng đã bán trong đơn hàng. Viết cursor cập --nhật thông tin cho thuộc tính này. alter table HOA_DON_NHAP add TONGMH int declare @SOHDN char(6),@TONGMH int declare cur_TMH cursor forward_only for select distinct MAMH from MAT_HANG open cur_TMH fetch next from cur_TMH into @SOHDN while @@fetch_status=0 begin select TONGMH=sum(SOLUONGNHAP) from MAT_HANG MH,CHI_TIET_NHAP CTN,HOA_DON_NHAP HDN where MH.MAMH=CTN.MAMH and HDN.SOHDN=CTN.SOHDN and @SOHDN=HDN.SOHDN print 'Dang cap nhat MAMH:'+@SOHDN update HOA_DON_NHAP set TONGMH=@TONGMH where SOHDN=@SOHDN fetch next from cur_TMH into @SOHDN end close cur_TMH deallocate cur_TMH
-------------------------------------------------------- câu 4 --Thêm thuộc tính TongTT trong bang HoaDonXuat lưu thông tin tổng thành tiền trong hóa đơn xuất. Viết cursor cập nhật -- thông tin cho thuộc tính này. alter table HOA_DON_XUAT add TONGTT int declare @SOHDX char(6),@TONGTT int declare cur_TONGTT cursor dynamic for select distinct SOHDX from HOA_DON_XUAT open cur_TONGTT fetch next from cur_TONGTT into @SOHDX while @@fetch_status=0 begin select TONGTT=sum(DONGIAXUAT*SOLUONGXUAT) from CHI_TIET_XUAT where @SOHDX=SOHDX print 'Dang cap nhap phieu xuat'+@SOHDX update HOA_DON_XUAT set TONGTT=@TONGTT where @SOHDX=SOHDX print 'Dang doc' fetch next from cur_TONGTT into @SOHDX end close cur_TONGTT deallocate cur_TONGTT ----------------------------------------------------- Viet Giao Dich 1. Tạo một giao dịch đảm bảo thực hiện thành công 2 công việc: Xóa loại hàng ‘L01’ trong bảng tblLoaihang; thêm 1 bản ghi vào tblHanghoa. Nếu một trong bị lỗi thì hủy cả 2 công việc đi và thông báo lỗi. 2. Xây dựng giao dịch đảm bảo việc xuất của một hàng hóa bất kỳ không vượt quá số lượng tồn còn lại. -------------------------SP------------------------ Tuan5 -- cau 1: Nhập vào tháng và năm bất kỳ cho biết thông tin những mặt hàng bán trong tháng đó. drop proc sp_thangnam
create proc sp_thangnam @thang int, @nam int as select * from ChiTietHoaDon c, HoaDonXuat h, mathang mh where month(NgayHD)=@thang and year (NgayHD)=@nam and c.mahd =h.mahd and mh.mamh =c.mamh --------------------------------------------------- -- cau 2: nhap vao 1 quy cho biet nhung mat hang nao ban nhieu nhat trong qui do create proc sp_nhieunhat @quy tinyint as begin tran if @quy =all( select sum(soluong) from ChiTietHoaDon c, HoaDonXuat h, mathang mh where datepart("q",ngayHD)=@quy and c.mahd =h.mahd and mh.mamh =c.mamh group by mh.mamh ) else print 'ko co quy nay' if @@error 0 rollback tran else commit tran --------------------------------------------------- -- cau 3: Nhập vào SoHDB (số hóa đơn bán) cho biết thông tin những mặt hàng của
những hóa đơn bán đó. drop proc sp_SoHD create proc sp_SoHD @sohd char(5) as select * from ChiTietHoaDon c, HoaDonXuat h, mathang mh where h.mahd=@sohd and c.mahd =h.mahd and mh.mamh =c.mamh --------------------------------------------------- -- cau 4: -- Thêm mặt hàng vào bảng MatHang thỏa mãn các ràng buộc sau: -- a. Kiểm tra khóa chính -- b. Kiểm tra khóa ngoại liên quan -- c. Đơn vị tính là một trong những loại: “cái, thùng, chiếc, lon, chai, mét, kg, tấn”. create proc sp_themmh @mamh char(5), @tenmh varchar(30), @dvt char(7) as begin tran if exists (select * from mathang where mamh=@mamh) print 'ma hang ' + @mamh + ' da ton tai' else if @dvt not in ('cai','thung','chiec') print 'dvt phai nam trong nhung tu ' else insert into mathang values (@mamh,@tenmh,@dvt) if @@error 0 rollback tran else commit tran --------------------------------------------------- -- cau 5: Thêm vào bảng ChiTietHDB thỏa mãn các ràng buộc sau: a. Kiểm tra khóa chính. b. Kiểm tra khóa ngoại.
c. Số lượng hóa đơn bán phải dương và đồng thời nhỏ hơn hay bằng số lượng tồn trong kho. d. Đơn giá bán phải lớn hơn hay bằng đơn giá nhập lớn nhất của các lần nhập. --------------------------------------------------- -- Cau 6: Thêm vào bảng hóa đơn bán thỏa mãn các ràng buộc sau: a. Số hóa đơn là số thứ tự của các hóa đơn có trong hệ thống bắt đầu từ 1. Khi thêm mới phải kiểm tra có những số thứ tự bị xóa thì phải bổ sung vào những số thứ tự còn trống này; nếu không có (nghĩa là những số thứ tự liên tục với nhau) thì thêm với số thứ tự tiếp theo. b. Kiểm tra khóa ngoại. c. Ngày bán phải nhỏ hơn hay bằng ngày hiện tại. --------------------------------------------------- -- Cau 7: Cập nhật số lượng bán như sau: a. Nếu không còn hàng trong kho thì không cập nhật số lượng bán. b. Nếu còn hàng trong kho thì cập nhật cập nhật số lượng bán phải nhỏ hơn hay bằng hàng trong kho. --------------------------------------------------- -- Cau 8: Xóa hóa đơn bán hàng kiểm tra các ràng buộc như sau a. Hóa đơn không có mặt hàng nào bán thì xóa. b. Hóa đơn có mặt hàng rồi thì không xóa. -----------------------Funtion------------------- --tuan 6 -- cau1 -- Đầu vào: MaMH, ngày 1, ngày 2. Đầu ra: tổng số lượng hàng đã bán của mặt -- hàng đó trong khoảng từ ngày 1 đến ngày 2. create proc sp_slhang
@mamh char(10), @tungay datetime,@denngay datetime, @slhang int output as begin tran select @slhang=sum(soluong) from chitiethoadon ct,hoadonxuat hdx where ct.mahd=hdx.mahd and ngayhd between @tungay and @denngay and @mamh=mamh if @@error =0 commit tran else rollback tran select * from hoadonxuat declare @sl int exec sp_slhang'mh01','1/3/2010',@slhang=@sl print 'hang ban dc trong thang 1/3/2010 den 1/4/2010'+ @sl --------------------------------------------------- --cau 2 -- Đầu vào: Ten_NCC, kiểm tra nhà cung cấp đó có tồn tại không. Đầu ra: tổng số -- lượng mặt hàng mà nhà cung cấp đó đã cung ứng. create proc sp_slhang @mancc char(50), @slh int output as begin tran if(exists(select * from nha_cung_cap where mancc=@mancc) select @slh=sum(mh.mamh) from nha_cung_cap ncc,mathang mh where mh.mancc=ncc.mancc and ncc.mancc=@mancc if @@error=0 commit tran else rollback tran select *from chitiethoadon declare @sl int exec sp_slhang'mh01','1/3/2010',@slh=@sl
print 'nha cung cap da ton tai'+ @sl --------------------------------------------------- --cau 3 -- Đầu vào MAKH. Kiểm tra mã khách hàng đó có tồn tại không. Đầu ra, tổng số -- lần mua và tổng trị giá trên các lần mua hàng của khách hàng. select * from khachhang select * from hoadonxuat create proc sp_tonglanmua @makh char(5),@ttkh char(15), @slmua int output, @tongt int output as begin tran if(exists(select * from khachhang where makh=@makh))-- kiem tra ton tai select @slmua=count(hd.mahd),@tongt=sum(tongtien) from hoadonxuat hd,khachhang kh,chitiethoadon ct where hd.mahd=ct.mahd and kh.makh=hd.makh and @makh=kh.makh if @@error=0 commit tran else rollback tran --------------------------------------------------- --cau 4 -- Đầu vào: MaMH. Đầu ra: số lượng tồn của mặt hàng đó. -- select * from mathang -- select * from hoadonxuat -- select * from chitiethoadon create proc sp_slton @mamh char(5), @slton int output as begin tran select @slton from mathang mh where hd.mahd=ct.mahd
--------------------------------------------------- -- Cau :5. Đầu vào: MaMH. Đầu ra đơn giá nhập và đơn giá bán của mặt hàng đó. --------------------------------------------------- -- Cau 6: Đầu vào: tháng và năm bất kỳ. Kiểm tra tháng và năm đó phải nhỏ hơn hay bằng tháng năm hiện tại. Đầu ra, tính tiền lời cho thời gian đó (tiền lời = Sum(số lượng nhập * đơn giá nhập) – Sum(số lượng xuất * đơn giá xuất)) ------------------------------------------------------------------------------- Cau 7: Dùng giao tác viết lại các câu store procedure trên: thuc hang tuan 7 trigger --cau 1 moi mat hang phai co mot ma duy nhat de phan biet mat hang nay voi mat hang khac drop trigger trig_masp create trigger trig_masp on mat_hang for insert,update as if(select count(*) from inserted ins , mat_hang mh where ins.mamh = mh.mamh)>1 begin print' ma mat hang da ton tai' rollback tran end ----------------------------------------------------------------------------- --cau 2 nhan vien phai co tuoi >= 18. va neu nhan vien co so nam cong tac >=2 thi duoc tinh la nhan vien bien che con
for insert as if not exists (select * from nhan_vien nv,inserted d where nv.manv = d. manv and year(getdate()) - year(d.ngaysinh) > 18) begin print ' nhan vien khong du dieu kiem lam ' rollback tran end if exists (select * from inserted d where year(getdate()) - year(d.ngayvaolam) 2) if exists (select * from inserted where loaihinh ='hop dong ') begin print ' ban khong the nhap loai hinh la hop dong ' rollback tran return end ------------------------------------------------------------------------------------- --cau 3 don gia ban cua mot san pham phai lon hon don gia nhap cua san pham do trong thoi gian ngay gan nhat select * from hoa_don_nhap select * from chi_tiet_nhap drop trigger trig_dongia create trigger trig_dongia on chi_tiet_xuat for insert as if not exists (select * from chi_tiet_nhap ct, inserted d where ct.mamh = d.mamh) begin print ' san pham khong ton tai ' rollback tran end if not exists (select dongiaxuat from inserted where dongiaxuat >(
select dongianhap from hoa_don_nhap hd, chi_tiet_nhap ct, inserted i where hd.sohdn = ct.sohdn and ct.mamh =i.mamh and ngaynhap>= ( select max(ngaynhap) from hoa_don_nhap hd, chi_tiet_nhap ct, inserted i where hd.sohdn = ct.sohdn and ct.mamh =i.mamh))) begin print 'gia nhap khong dung' rollback tran end ----------------------------------------------------------------------------------------- --cau 4 mot mat hang phai thuoc mot loai hang nhat dinh select * from loai_hang alter table mat_hang drop constraint FK_MATHANG_NHACUNGCAP select * from mat_hang create trigger trig_thuocloaihang on mat_hang for insert as if not exists (select * from loai_hang lh, inserted d where d.maloai = lh.maloai) begin print ' ma loai hang nay chua ton tai ' rollback tran end -------------------------------------------------------------------------------------------- --cau 5 moi hoa don nhap phai co it nhat mot mat hang drop trigger trig_donhang create trigger trig_donhang on hoa_don_nhap as if not exists (select count(d.mamh)>0 from hoa_don_nhap dh,chi_tiet_nhap ct, inserted d where xt.mamh = d.mamh and hd.sohdn = ct.sohdn) begin print ' moi hoa don nhap phai co it nhat mot mat hang ' rollback tran end