--39. Giam 25% luong cua nhung nhan vien trong nam 2008 khong lap bat ky hoa don nao

Update NhanVien

Set PhuCap=0.25*phucap

Where manhanvien = any (

Select manhanvien

From nhanvien

Where not exists (

Select *

From dondathang

Where dondathang.manhanvien=nhanvien.manhanvien) )

--2.Thong ke tong so luong ban hang cua mot ma hang

Create Proc sp_2

As

Begin

Select mahang as 'Ma Hang', sum(soluong) as 'So Luong'

From chitietdathang, dondathang

Where chitietdathang.sohoadon=dondathang.sohoadon

Group By mahang

End;

Drop Proc sp_2

Execute sp_2

--3.

Create Function Fun_3

Return nvarchar(5)

Begin

Select mahang from mahang

End;

Select * from chitietdathang

where soluong in(200,400,500)

Select * from nhanvien

where manhanvien in ('NV001','NV003','NV002','NV000')

select manhanvien

From dondathang join chitietdathang on dondathang.sohoadon = chitietdathang.sohoadon

group by manhanvien

having sum(soluong) = any (

select sum(soluong)

From dondathang join chitietdathang on dondathang.sohoadon = chitietdathang.sohoadon

group by manhanvien )

Select sum(soluong)

From mahang

where mahang='MH001'

Create Function Fun_sl(@date datetime)

returns int

as

begin

Declare @getdate int

Set @getdate = year(@date)

return @getdate

end

Drop Function Fun_sl

Select * From Fun_sl '2/8/1989'

--Su dung ham noi tuyen inline

Create Function Fun_inline()

returns table

as

return (Select mahang, count(maloaihang),sum(soluong) from mahang

Group By mahang)

Drop Function Fun_inline

Select * from Fun_inline()

(chú ý: CSDL QuanLyBanHang có bảng MatHang, ở CSDL khi các em tạo ra, ghi chệch thành MaHang, nên em sửa lại tên bảng trong đoạn code dưới khi chạy thử ở CSDL của mình)

-- Bài 4: Viết trigger cho bảng chitietdathang

-- a. Khi có 1 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ì hủy bỏ thao tác bổ sung

Create trigger trg_chitietdathang_insert

on chitietdathang

for insert

as

begin

-- khai báo các biến sử dụng trong trigger

declare @mahang nvarchar (10) -- biến chứa mã hàng được cập nhật

declare @sl_con int -- biến chứa số lượng hàng còn

declare @sl_ban int -- biến chứa số lượng hàng bán ra

-- Lấy mã hàng, số lượng bán ra từ bảng inserted và gán vào các biến tương ứng

Select @mahang = mahang, @sl_ban = soluong from inserted

-- Lấy số lượng hàng con từ bảng mathang và gán vào các biến tương ứng

select @sl_con = soluong from mathang where mahang=@mahang

-- so sánh và cập nhật nếu thỏa mãn điều kiện

if @sl_con >= @sl_ban

update mathang set soluong = soluong - @sl_ban where mahang = @mahang

else

rollback transaction

end

-- thêm vào bảng chitietdathang để test trigger

insert into chitietdathang values (1,'MH001',120000,30,0)

drop trigger trg_chitietdathang_insert_cursor

-----------------------------------------------------------------------------------------

-- b. 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ì bỏ thao tác cập nhật

create trigger trg_chitietdathang_update_soluong

on chitietdathang

for update

as

if update(soluong)

begin

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

rollback transaction

else

begin

update mathang

-- chú ý: vì đây là trường hợp số lượng bản ghi cập nhật >1, nên cần tính tổng số lượng của các bản ghi được cập nhật

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 < 1)

rollback transaction

end

end

drop trigger trg_chitietdathang_update_soluong

--------------------------

Create Trigger trg_chitietdathang_giaban

On Chitietdathang

For Insert, update

As

if update (giaban)

if ( Exists (Select inserted.mahang From Inserted, Mathang

where Inserted.MaHang=MatHang.MaHang

and Mathang.giahang > inserted.giaban) )

Begin

Print'Ko hop le' RollBack Transaction end

(chú ý: CSDL QuanLyBanHang có bảng MatHang, ở CSDL khi các em tạo ra, ghi chệch thành MaHang, nên em sửa lại tên bảng trong đoạn code dưới khi chạy thử ở CSDL của mình)

-- Bài 4: Viết trigger cho bảng chitietdathang

-- a. Khi có 1 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ì hủy bỏ thao tác bổ sung

Create trigger trg_chitietdathang_insert

on chitietdathang

for insert

as

begin

-- khai báo các biến sử dụng trong trigger

declare @mahang nvarchar (10) -- biến chứa mã hàng được cập nhật

declare @sl_con int -- biến chứa số lượng hàng còn

declare @sl_ban int -- biến chứa số lượng hàng bán ra

-- Lấy mã hàng, số lượng bán ra từ bảng inserted và gán vào các biến tương ứng

Select @mahang = mahang, @sl_ban = soluong from inserted

-- Lấy số lượng hàng con từ bảng mathang và gán vào các biến tương ứng

select @sl_con = soluong from mathang where mahang=@mahang

-- so sánh và cập nhật nếu thỏa mãn điều kiện

if @sl_con >= @sl_ban

update mathang set soluong = soluong - @sl_ban where mahang = @mahang

else

rollback transaction

end

-- thêm vào bảng chitietdathang để test trigger

insert into chitietdathang values (1,'MH001',120000,30,0)

drop trigger trg_chitietdathang_insert_cursor

-----------------------------------------------------------------------------------------

-- b. 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ì bỏ thao tác cập nhật

create trigger trg_chitietdathang_update_soluong

on chitietdathang

for update

as

if update(soluong)

begin

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

rollback transaction

else

begin

update mathang

-- chú ý: vì đây là trường hợp số lượng bản ghi cập nhật >1, nên cần tính tổng số lượng của các bản ghi được cập nhật

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 < 1)

rollback transaction

end

end

drop trigger trg_chitietdathang_update_soluong

--------------------------

Create Trigger trg_chitietdathang_giaban

On Chitietdathang

For Insert, update

As

if update (giaban)

if ( Exists (Select inserted.mahang From Inserted, Mathang

where Inserted.MaHang=MatHang.MaHang

and Mathang.giahang > inserted.giaban) )

Begin

Print'Ko hop le'

RollBack Transaction

End