Cấp bậc tác giả:

DATABASE

Một số bài tập mẫu SQL(Phân II)

Được viết bởi QuangIT ngày 25/12/2012 lúc 04:16 PM
Một số bài mẫu về Thủ tục, trigger, function
  • 0
  • 39765

Một số bài tập mẫu SQL(Phân II)

Tiếp theo phần I

1.      Cho khung nhìn được định nghĩa như sau:

CREATE VIEW view_donhang

AS

SELECT dondathang.sohoadon,makhachhang,manhanvien,

ngaydathang,ngaygiaohang,ngaychuyenhang,

noigiaohang,mahang,

giaban,soluong,mucgiamgia

FROM dondathang INNER JOIN chitietdathang

ON dondathang.sohoadon = chitietdathang.sohoadon

a.      Có thể thông qua khung nhìn này để bổ sung dữ liệu cho bảng DONDATHANG được không?

b. Có thể thông qua khung nhìn này để bổ sung dữ liệu cho bảng CHITIETDATHANG được không?

Không

2.      Với khung nhìn được định nghĩa như sau:

CREATE VIEW view_donhang

AS

SELECT dondathang.sohoadon,makhachhang,manhanvien,

ngaydathang,ngaygiaohang,ngaychuyenhang,

noigiaohang,mahang,

giaban*soluong as thanhtien,

mucgiamgia

FROM dondathang INNER JOIN chitietdathang

ON dondathang.sohoadon = chitietdathang.sohoadon

a.      Có thể thông qua khung nhìn này để xoá hay cập nhật dữ liệu trong bảng DONDATHANG được không?

Có thể cập nhật nhưng không thể xóa

b. Có thể thông qua khung nhìn này để cập nhật dữ liệu trong bảng CHITIETDATHANG được không?

Có thể được

3.      Tạo thủ tục lưu trữ để thông qua thủ tục này có thể bổ sung thêm một bản ghi mới cho bảng MATHANG (thủ tục phải thực hiện kiểm tra tính hợp lệ của dữ liệu cần bổ sung: không trùng khoá chính và đảm bảo toàn vẹn tham chiếu)

 CREATE PROCEDURE sp_insert_mathang(

                  @mahang           NVARCHAR(10),

                  @tenhang          NVARCHAR(50),

                  @macongty         NVARCHAR(10) = NULL,

                  @maloaihang INT = NULL,

                  @soluong          INT = 0,

                  @donvitinh  NVARCHAR(20) = NULL,

                  @giahang    money = 0)

      AS

            IF NOT EXISTS(SELECT mahang FROM mathang

                        WHERE mahang=@mahang)

       IF (@macongty IS NULL OR EXISTS(SELECT macongty

                              FROM nhacungcap

                              WHERE macongty=@macongty))

                        AND

                              (@maloaihang IS NULL OR

                              EXISTS(SELECT maloaihang FROM loaihang

                              WHERE maloaihang=@maloaihang))     

                        INSERT INTO mathang

                        VALUES(@mahang,@tenhang,

                                @macongty,@maloaihang,

                                @soluong,@donvitinh,@giahang)

 

exec sp_insert_mathang '005','Bom B52','ct03',2,34,'VND',$500000

4.      Tạo thủ tục lưu trữ có chức năng thống kê tổng số lượng hàng bán được của một mặt hàng có mã bất kỳ (mã mặt hàng cần thống kê là tham số của thủ tục).

 Create PROCEDURE sp_thongkebanhang(@mahang NVARCHAR(10))

      AS

            SELECT mathang.mahang,tenhang,

                   SUM(chitietdathang.soluong) AS 'Tong so luong'

            FROM mathang LEFT JOIN chitietdathang

                  ON mathang.mahang=chitietdathang.mahang

            WHERE mathang.mahang=@mahang

            GROUP BY mathang.mahang,tenhang

 

exec sp_thongkebanhang '001'

an> EXISTS(SELECT macongty

                              FROM nhacungcap

                              WHERE macongty=@macongty))

                        AND

                              (@maloaihang IS NULL OR

                              EXISTS(SELECT maloaihang FROM loaihang

                              WHERE maloaihang=@maloaihang))     

                        INSERT INTO mathang

                        VALUES(@mahang,@tenhang,

                                @macongty,@maloaihang,

                                @soluong,@donvitinh,@giahang)

 

exec sp_insert_mathang '005','Bom B52','ct03',2,34,'VND',$500000

5.      Viết trigger cho bảng CHITIETDATHANG theo yêu cầu sau:

ü  Khi một bản ghi mới được bổ sung vào bảng này thì giảm số lượng hàng hiện có nếu số lượng hàng hiện có lớn hơn hoặc bằng số lượng hàng được bán ra. Ngược lại thì huỷ bỏ thao tác bổ sung.

create trigger trg_chitietdathang_insert

on chitietdathang

for insert

as

begin

declare @mahang nvarchar(10)

declare @soluongban int

declare @soluongcon int

select @mahang=mahang, @soluongban=soluong

from inserted

select @soluongcon=soluong from mathang

where mahang=@mahang

if @soluongcon>=@soluongban

update mathang set soluong=soluong-@soluongban

where mahang=@mahang

else

rollback transaction

end

ü  Khi cập nhật lại số lượng hàng được bán, kiểm tra số lượng hàng được cập nhật lại có phù hợp hay không (số lượng hàng bán ra không được vượt quá số lượng hàng hiện có và không được nhỏ hơn 1). Nếu dữ liệu hợp lệ thì giảm (hoặc tăng) số lượng hàng hiện có trong công ty, ngược lại thì huỷ bỏ thao tác cập nhật.

create trigger trg_chitietdathang_update

on chitietdathang

for update

as

if update(soluong)

begin

if exists(select sohoadon from inserted where soluong<0)

rollback transaction

else       

begin

update mathang

set soluong=soluong-

(select sum(inserted.soluong-deleted.soluong)

from inserted INNER JOIN deleted

on inserted.sohoadon=deleted.sohoadon AND

inserted.mahang=deleted.mahang

where inserted.mahang=mathang.mahang

group by inserted.mahang)

where mahang in (select DISTINCT mahang

                                 from inserted)

if exists(select mahang from mathang

where soluong<0)

rollback transaction

end

end

6.      Viết trigger cho bảng CHITIETDATHANG để sao cho chỉ chấp nhận giá hàng bán ra phải nhỏ hơn hoặc bằng giá gốc (giá của mặt hàng trong bảng MATHANG) 

CREATE TRIGGER trg_chitietdathang_giaban

ON chitietdathang

FOR INSERT,UPDATE

AS

IF UPDATE(giaban)

IF EXISTS(SELECT inserted.mahang

FROM mathang INNER JOIN inserted   

ON mathang.mahang=inserted.mahang

WHERE mathang.giahang>inserted.giaban)

ROLLBACK TRANSACTION


Nguồn bài viết: Dngaz.com

BÌNH LUẬN BÀI VIẾT

Bài viết mới nhất

LIKE BOX

Bài viết được xem nhiều nhất

HỌC HTML