Cho CSDL7 gồm:Mathang (mahang, tenhang, soluong)Nhatkybanhang (stt, ngay, nguoimua, mahang, soluong, giaban)

1. Định nghĩa trigger trg_nkbh_insert. Trigger này có chức năng tự động giảm số lượng hàng hiện có khi một mặt hàng nào đó được bán (tức là khi câu lệnh INSERT được thực thi trên bảng NHATKYBANHANG).create trigger trg_nkbh_inserton nhatkybanhangfor insertAS

update mathangset mathang.soluong=mathang.soluong-inserted.soluongfrom mathang, insertedwhere mathang.mahang=inserted.mahang

select * from mathang where mahang='H1'

insert into nhatkybanhang(ngay, nguoimua,mahang,soluong, giaban)values('2012/10/23','khoa','H1',3,5000)

2. Tạo trigger, được kích hoạt khi ta tiến hành cập nhật cột SOLUONG cho một bản ghi của bảng NHATKYBANHANG (chỉ cập nhật đúng một bản ghi)create trigger trg_nhatkybanhang_update_soluongon nhatkybanhangfor updateAS

if update(soluong)update mathangset mathang.soluong=mathang.soluong-(inserted.soluong-

deleted.soluong)from mathang, inserted, deletedwhere deleted.stt=inserted.sttand mathang.mahang=deleted.mahang

update nhatkybanhangset soluong=soluong+20where stt=1

3. Tạo trigger ngăn chặn mọi thao tác làm thay đổi dữ liệucreate trigger trg_mathang_deleteon mathangfor deleteAS rollback transaction

delete from mathangwhere mahang='H1'

Cho CSDL1 gồm:CGTRINH (STT_CTR, TEN_CTR, DIACHI_CTR, KINH_PHI, TEN_CHU, TEN_THAU, NGAY_BD)

CHUNHAN (TEN_CHU, DCHI_CHU)CHUTHAU (TEN_THAU, TEL, DCHI_THAU)CONGNHAN (HOTEN_CN, NAMS_CN, NAM_VAO_N, CH_MON)KTRUCSU (HOTEN_KTS, NAMS_KTS, PHAI, NOI_TN, DCHI_LL_KTS)TAM (HOTEN_CN, TongSoNgay)THAMGIA (HOTEN_CN, STT_CTR, NGAY_TGIA, SO_NGAY)THIETKE (HOTEN_KTS, STT_CTR, THU_LAO)

1. Tạo các thủ tục để thêm, cập nhật, xóa một công trình trong bảng CGTRINH. Trường hợp cập nhật và xóa là theo STT_CTR. --tao thu tuc de them vao bang CGTRINHcreate procedure CGTRINH_Add@STT_CTR as tinyint,@TEN_CTR as nvarchar(20),@DIACHI_CTR as nvarchar(20),@TINH_THANH as nvarchar(15),@KINH_PHI as int,@TEN_CHU as nvarchar(20),@TEN_THAU as nvarchar(20)ASINSERT INTO CGTRINH(STT_CTR, TEN_CTR, DIACHI_CTR, TINH_THANH, KINH_PHI, TEN_CHU, TEN_THAU, NGAY_BD)VALUES (@STT_CTR, @TEN_CTR, @DIACHI_CTR, @TINH_THANH, @KINH_PHI, @TEN_CHU, @TEN_THAU, Getdate())

drop procedure CGTRINH_Add

--tao thu tuc de cap nhat vao bang CGTRINHcreate procedure CGTRINH_Update@STT_CTR as tinyint,@TEN_CTR as nvarchar(20),@DIACHI_CTR as nvarchar(20),@TINH_THANH as nvarchar(15),@KINH_PHI as int,@TEN_CHU as nvarchar(20),@TEN_THAU as nvarchar(20),@NGAY_BD as smalldatetimeASUPDATE CGTRINH SETTEN_CTR=@TEN_CTR, DIACHI_CTR=@DIACHI_CTR, TINH_THANH=@TINH_THANH, KINH_PHI=@KINH_PHI, TEN_CHU=@TEN_CHU, TEN_THAU=@TEN_THAU, NGAY_BD=@NGAY_BDWHERE STT_CTR=@STT_CTR

--tao thu tuc de xoa vao bang CGTRINHcreate procedure CGTRINH_Delete@STT_CTR as tinyintASDELETE CGTRINH WHERE STT_CTR=@STT_CTR

CGTRINH_Add 9,'ks Ninh Kieu 2','dai lo hoa binh','can tho','5000','so thuong mai du lich','phong dich vu du lich'

CGTRINH_Update 9,'ks Ninh Kieu 2','dai lo hoa binh','can tho','5000','so thuong mai du lich','phong dich vu du lich','2009/10/2'

CGTRINH_Delete 9

Câu 1: 1.1 Tạo thủ tục CheckGender có tham số là tên. Thủ tục sẽ kiểm tra tiếp đầu ngữ của tên là ‘Ms.’ hay ‘Mr.’. Nếu bắt đầu là ‘Ms.’ hiển thị thông báo “Bạn đã nhập vào tên một phụ nữ ”. Nếu bắt đầu là ‘Mr.’, hiển thị thông báo “Bạn đã nhập vào tên một đàn ông”. 1.2 Thực thi thủ tục CheckGender với tham số là chuỗi “Ms. Olive Oyl”.create procedure CheckGender@ten as nvarchar(20)AS

if(left(@ten,3)='Ms.')print 'Ban da nhap ten 1 phu nu'else if(left(@ten,3)='Mr.')print 'Ban nhap vao ten 1 dan ong'

CheckGender 'Ms. Olive oyl'

Cho CSDL pubs gồm:Authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract)Discounts (discounttype, stor_id, lowqty, highqty, discount)Employee (emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)Jobs (job_id, job_desc, min_lvl, max_lvl)pub_info (pub_id, logo, pr_info)publishers (pub_id, pub_name, city, state, country)roysched (title_id, lorange, hirange, royalty)sales (stor_id, ord_num, ord_date, qty, payterms, title_id)stores (stor_id, stor_name, stor_address, city, state, zip)titleauthor (au_id, title_id, au_ord, royaltyper)titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)

Câu 2: Sử dụng cơ sở dữ liệu pubs. 2.1 Viết câu lệnh T-SQL để thay thế 3 ký tự đầu tiên của cột Titleid từ bảng Titles bằng ‘ABB’ với điều kiện kiểu (type) là ‘business’.DECLARE @var_title_id as nvarchar(40) DECLARE @var_title_id_thay_the as nvarchar(40) DECLARE Type_Cursor CURSOR FOR SELECT title_id FROM titles WHERE type='business' OPEN Type_Cursor FETCH NEXT FROM Type_Cursor INTO @var_title_id WHILE @@FETCH_STATUS = 0 BEGIN -- PRINT 'Author:' + @var_title_id set @var_title_id_thay_the=replace(@var_title_id,left(@var_title_id,3),'M')

update titles set title=@var_title_id_thay_the

where title_id=@var_title_id FETCH NEXT FROM Type_Cursor INTO @var_title_id END CLOSE Type_Cursor DEALLOCATE Type_Cursor

update titles set title_id='1'where title_id='TC7777'

select replace('MNB mbnv cnc',left('MNB mbnv cnc',3),'ABB')

2.2 Viết câu lệnh T-SQL để hiển thị các tên các nhà xuất bản khác nhau bằng chữ thường.DECLARE @var_pub_name as nvarchar(40) DECLARE Pub_name_Cursor CURSOR FOR SELECT pub_name FROM publishers OPEN Pub_name_Cursor FETCH NEXT FROM Pub_name_Cursor INTO @var_pub_name WHILE @@FETCH_STATUS = 0 BEGIN

PRINT 'Ten nha xuat ban '+LOWER(@var_pub_name) FETCH NEXT FROM Pub_name_Cursor INTO @var_pub_name END CLOSE Pub_name_Cursor DEALLOCATE Pub_name_Cursor

2.3 Tạo trigger Royalty_Per_Update không cho phép cập nhật cột royaltyper của bảng titleauthor.create trigger Royalty_Per_Update--ten triggeron titleauthor--bangfor updateASIf update(royaltyper)rollback transaction

update titleauthorset royaltyper=100where au_id='172-32-1176'and title_id='PS3333'

drop trigger Royalty_Per_Update

insert into titleauthorvalues('172-32-1176', 'TC7777', 1, 80)

delete titleauthorwhere au_id='172-32-1176'and title_id='TC7777'

Câu 3: Sử dụng cơ sở dữ liệu CSDL7. 3.1 Tạo hàm TongBan có tham số là mã mặt hàng trả về tổng số lượng hàng bán được

của mỗi mặt hàng.create function TongBan (@var_ma_mat_hang nvarchar(20))returns tableASreturn(

select nhatkybanhang.mahang,mathang.tenhang, sum(nhatkybanhang.soluong) as TongSoLuongBan

FROM mathang,nhatkybanhangWHERE mathang.mahang=@var_ma_mat_hang and

mathang.mahang=nhatkybanhang.mahangGROUP BY nhatkybanhang.mahang,mathang.tenhang

)

drop function TongBan

select * from TongBan ('H1')

3.2 Tạo thủ tục ThongKe có chức năng thống kê tổng số lượng hàng bán được của mỗi mặt hàng.create procedure ThongKeAS

select nhatkybanhang.mahang,mathang.tenhang, sum(nhatkybanhang.soluong) as TongSoLuongBan

FROM mathang,nhatkybanhangWHERE mathang.mahang=nhatkybanhang.mahangGROUP BY nhatkybanhang.mahang,mathang.tenhang

ThongKe

3.3 Tạo trigger InsertOnNKBH được kích hoạt khi câu lệnh INSERT được sử dụng để bổ sung một bản ghi mới cho bảng NHATKYBANHANG. Trong trigger này kiểm tra điều kiện hợp lệ của dữ liệu là số lượng hàng bán ra phải nhỏ hơn hoặc bằng số lượng hàng hiện có. Nếu điều kiện này không thoả mãn thì huỷ bỏ thao tác bổ sung dữ liệu.create trigger trg_nhatkybanhang_update_soluongon nhatkybanhangfor updateAS

if update(soluong)update mathangset mathang.soluong=mathang.soluong-(inserted.soluong-

deleted.soluong)from mathang, inserted, deletedwhere deleted.stt=inserted.sttand mathang.mahang=deleted.mahang

update nhatkybanhangset soluong=soluong+20where stt=1--roi toi trigger nay --ai bietcreate trigger InsertOnNKBHon nhatkybanhangfor insertAS

declare @var_so_luong_ban intdeclare @var_ma_hang nvarchar(5)declare @var_so_luong_ton intset @var_so_luong_ban=(select soluong from inserted)set @var_ma_hang=(select mahang from inserted)set @var_so_luong_ton=(select sum(soluong) from mathang

where mathang.mahang=@var_ma_hang)if(@var_so_luong_ton<0)

beginprint 'khong duoc'--print @var_so_luong_ton--print @var_ma_hang--print @var_so_luong_banrollback transaction

endelseprint 'ok'

drop trigger InsertOnNKBH

insert into nhatkybanhang(ngay, nguoimua,mahang,soluong, giaban)values('2012/10/3','khoa','H1',20,5000)

Cho CSDL5 gồm:CHUCDANH (MACD, TENCD, GIOCHUAN, TIEN_1TIET)Gd_0506 (MAGV, MONDAY, LOPDAY, SOTIET, SISO)GIAOVIEN (MAGV, HOTEN, MACD, PHAI)TAMUNG (MAGV, NGAY_TA, SOTIEN)4.1 Viết thủ tục GoiTen nhận vào tham số mã giáo viên. Hãy in ra tên của giáo viên đó cùng với danh xưng là 'Thầy' nếu giáo viên là nam, và 'Cô' nếu giáo viên là nữ.create procedure GoiTen@ten as nvarchar(20)AS

declare @var_phai as bitdeclare @var_magv as char(5)declare @var_hoten as nvarchar(25)declare Magv_Cursor cursor forselect giaovien.magv, giaovien.phai, giaovien.hoten from giaovien

where giaovien.hoten like '%'+@ten+'%'open Magv_CursorFETCH NEXT FROM Magv_Cursor INTO @var_magv, @var_phai, @var_hotenwhile @@fetch_status=0begin

if(@var_phai=0)Print 'Thay '+@var_hoten

elsePrint 'Co '+@var_hoten

FETCH NEXT FROM Magv_Cursor INTO @var_magv, @var_phai, @var_hoten

endclose Magv_Cursordeallocate Magv_Cursor

drop procedure GoiTen

GoiTen 'Binh'

4.2 Viết hàm TienTamUng nhận vào tham số mã giáo viên, hàm sẽ trả về tổng số tiền tạm ứng.create function TienTamUng (@var_magv nvarchar(4))returns tableASreturn(

select tamung.magv,sum(tamung.sotien) as TongtientamungFROM tamung where tamung.magv=@var_magvGROUP BY tamung.magv

)

drop function TienTamUng

select * from TienTamUng ('CT00')

select * from tamung where magv='ct00'

4.3 Viết hàm TongGioChuan nhận vào tham số tên lớp dạy, hàm sẽ trả về tổng số tiết chuẩn mà các giáo viên dạy lớp đó đã giảng dạy.create function TongGioChuan (@var_lop_day nvarchar(15))returns tableAS return(

select gd_0506.lopday, sum(gd_0506.sotiet) as Tongtietdayfrom gd_0506 where gd_0506.lopday=@var_lop_daygroup by gd_0506.lopday

)

drop function TongGioChuan

select * from TongGioChuan ('DT18')

select * from gd_0506 where lopday='DT18'

4.4 Viết hàm TamUngCN trả về họ tên của giáo viên có tạm ứng nhiều nhất từ trước đến nay.create function TamUngCN ()returns @bang table(

magv char(5),hoten nvarchar(25),tamung float

)ASbegin

declare @var_magv as char(5)declare @var_sotien as floatdeclare @var_hoten as nvarchar(25)declare @var_tien_cao_nhat as floatdeclare @var_magv_cao_nhat as char(5)declare @var_hoten_cao_nhat as nvarchar(25)

set @var_tien_cao_nhat=0declare Tamung_Cursor cursor forselect tamung.magv, sum(tamung.sotien) as tongtienTU,

giaovien.hoten from tamung, giaovien where giaovien.magv=tamung.magv group by tamung.magv,giaovien.hoten

open Tamung_CursorFETCH NEXT FROM Tamung_Cursor INTO @var_magv, @var_sotien,

@var_hotenwhile @@fetch_status=0begin

if(@var_sotien>@var_tien_cao_nhat)begin

set @var_tien_cao_nhat=@var_sotienset @var_magv_cao_nhat=@var_magvset @var_hoten_cao_nhat=@var_hoten

endFETCH NEXT FROM Tamung_Cursor INTO @var_magv, @var_sotien,

@var_hoten

endclose Tamung_Cursordeallocate Tamung_Cursor

--return (@var_tien_cao_nhat)insert into @bang (magv,hoten,tamung) values(@var_magv_cao_nhat, @var_hoten_cao_nhat, @var_tien_cao_nhat)returnend

drop function TamUngCN

select * from TamUngCN () -- 'tamung'

select * from tamung where magv='ct00'

4.5 Viết hàm NgayTamUngGanNhat trả về ngày có giáo viên tạm ứng gần đây nhất.create function NgayTamUngGanNhat ()returns tableASreturn(

select max(tamung.ngay_ta) as NgayTamUngGanNhat from tamung)

drop function NgayTamUngGanNhat

select * from NgayTamUngGanNhat ()

4.6 Viết thủ tục GVDay_Mon nhận vào tham số tên môn dạy, thủ tục in ra màn hìnhhọ tên giáo viên đã từng dạy môn đó. (xưng danh Thầy, hoặc Cô).create procedure GVDay_Mon@var_ten_mon_day as nvarchar(20)

ASdeclare @var_phai as bitdeclare @var_magv as char(5)declare @var_hoten as nvarchar(25)declare @var_monday as nvarchar(20)declare Magv_Cursor cursor forselect distinct giaovien.magv, giaovien.phai, giaovien.hoten,

gd_0506.monday from giaovien, gd_0506 where giaovien.magv=gd_0506.magv and gd_0506.monday = @var_ten_mon_dayopen Magv_CursorFETCH NEXT FROM Magv_Cursor INTO @var_magv, @var_phai, @var_hoten,

@var_mondaywhile @@fetch_status=0begin

if(@var_phai=0)beginPrint 'Thay '+@var_hotenPrint 'Mon ' +@var_mondayend

elsebeginPrint 'Co '+@var_hotenPrint 'Mon ' +@var_mondayend

FETCH NEXT FROM Magv_Cursor INTO @var_magv, @var_phai, @var_hoten, @var_monday

endclose Magv_Cursordeallocate Magv_Cursor

drop procedure GVDay_Mon

GVDay_Mon 'CT Du Lieu'

4.7 Viết thủ tục TongGioCDanh nhận vào tham số tên chức danh, thủ tục in ra họ tên GV cùng với tổng số giờ qui chuẩn của từng giáo viên thuộc chức danh đó.create procedure TongGioCD@var_ten_chuc_danh as nvarchar(20)AS

declare @var_giochuan as intdeclare @var_phai as bitdeclare @var_hoten as nvarchar(25)declare @var_tencd as nvarchar(20)declare Magv_Cursor cursor forselect distinct chucdanh.tencd, chucdanh.giochuan, giaovien.hoten,

giaovien.phaifrom giaovien, chucdanh where giaovien.macd=chucdanh.macd and chucdanh.tencd = @var_ten_chuc_danhopen Magv_CursorFETCH NEXT FROM Magv_Cursor INTO @var_tencd, @var_giochuan,

@var_hoten, @var_phaiwhile @@fetch_status=0begin

if(@var_phai=0)begin

Print 'Thay '+@var_hotenPrint 'Chuc Danh ' +@var_tencdPrint 'Gio Chuan ' print @var_giochuan--chu yend

elsebeginPrint 'Co '+@var_hotenPrint 'Chuc Danh ' +@var_tencdPrint 'Gio Chuan ' print @var_giochuan--chu yend

FETCH NEXT FROM Magv_Cursor INTO @var_tencd, @var_giochuan, @var_hoten, @var_phai

endclose Magv_Cursordeallocate Magv_Cursor

drop procedure TongGioCD

TongGioCD 'GV Chinh'

ÔN T P Cho CSDL:LOAIHANG(MALOAIHANG, TENLOAIHANG) MATHANG(MAHANG, TENHANG, MACONGTY, MALOAIHANG, SOLUONG, DONVITINH, GIAHANG) KHACHHANG(MAKHACHHANG, TENCONGTY, TENGIAODICH, DIACHI, EMAIL, DIENTHOAI, FAX) NHACUNGCAP(MACONGTY, TENCONGTY, TENGIAODICH, DIACHI, DIENTHOAI, FAX, EMAIL) NHANVIEN(MANHANVIEN, HO, TEN, NGAYSINH, NGAYLAMVIEC, DIACHI, DIENTHOAI, LUONGCOBAN, PHUCAP) DONDATHANG(SOHOADON, MAKHACHHANG, MANHANVIEN, NGAYDATHANG, NGAYGIAOHANG, NGAYCHUYENHANG, NOIGIAOHANG) CHITIETDATHANG(SOHOADON, MAHANG, GIABAN, SOLUONG, MUCGIAMGIA)

1. Cho biết danh sách các đối tác cung cấp hàng cho công ty.

select * from nhacungcap

2. Mã hàng, tên hàng, số lượng của các mặt hàng có trong công tyselect mathang.mahang, mathang.tenhang, mathang.soluong from mathang]

3. Cho biết lương công ty phải trả cho mỗi nhân viên (lương = lương cơ bản + phụ cấp)create procedure TienphaitraAS

declare @var_manhanvien as char(10)declare @var_ho as nvarchar(50)declare @var_ten as nvarchar(50)declare @var_luongcoban as moneydeclare @var_phucap as money

declare @var_luongduoctra as moneydeclare tienluong_Cursor cursor forselect distinct nhanvien.manhanvien,

nhanvien.ho,nhanvien.ten,nhanvien.luongcoban,nhanvien.phucapfrom nhanvienopen tienluong_CursorFETCH NEXT FROM tienluong_Cursor INTO @var_manhanvien, @var_ho,

@var_ten, @var_luongcoban, @var_phucapwhile @@fetch_status=0begin

set @var_luongduoctra=@var_luongcoban+@var_phucapprint 'MaNV '+@var_manhanvien+', ho '+@var_ho+', ten

'+@var_ten+', luong duoc tra '+cast(@var_luongduoctra as nvarchar(50))FETCH NEXT FROM tienluong_Cursor INTO @var_manhanvien,

@var_ho, @var_ten, @var_luongcoban, @var_phucapendclose tienluong_Cursordeallocate tienluong_Cursor

drop procedure Tienphaitra

--tim nhung nhan vien co cung ngay sinhcreate procedure cungngaysinhAS

declare @var_ngaysinh as datetimedeclare @var_tong as intdeclare @var_ten as nvarchar(50)declare @var_luongcoban as moneydeclare @var_phucap as moneydeclare @var_luongduoctra as moneydeclare ngaysinh_Cursor cursor forselect distinct nhanvien.ngaysinh, count(nhanvien.ngaysinh) as

tongfrom nhanvien group by nhanvien.ngaysinhopen ngaysinh_CursorFETCH NEXT FROM ngaysinh_Cursor INTO @var_ngaysinh,@var_tongwhile @@fetch_status=0begin

if(@var_tong>1)beginselect * from nhanvien where nhanvien.ngaysinh=@var_ngaysinhendFETCH NEXT FROM ngaysinh_Cursor INTO @var_ngaysinh,@var_tong

endclose ngaysinh_Cursordeallocate ngaysinh_Cursor

drop procedure cungngaysinh

--nhung mat hang chua tung duoc khach hang dat muaselect * from mathangwhere mathang.mahang not in (select distinct chitietdathang.mahang from chitietdathang)

--NHUNG NHAN VIEN CHUA TUNG LaP DON DAT HANGselect * from nhanvienwhere nhanvien.manhanvien not in (select distinct dondathang.manhanvien from dondathang)

--nhung nhan vien cua cong ty co luong co ban cao nhatselect * from nhanvien where nhanvien.luongcoban=(select max(nhanvien.luongcoban) from nhanvien)

-- thong ke trong nam 2011, moi mat hang trong moi thang--va ca nam ban duoc bao nhieucreate procedure thongke_thang_namasdeclare @var_mahang as char(10)declare @var_tenhang as nvarchar(50)declare @var_thang as intdeclare @var_nam as intdeclare @var_tongsoluong as intdeclare @var_tonggiaban as moneydeclare @var_tonggiabannam as moneydeclare @var_tongsoluongnam as intdeclare thongke_cursor cursor for

select mathang.mahang, mathang.tenhang, month(dondathang.ngaydathang) as thang, year(dondathang.ngaydathang) as nam,

sum(chitietdathang.soluong) as tongsoluong, sum(chitietdathang.giaban) as tonggiaban

from mathang,chitietdathang,dondathangwhere mathang.mahang=chitietdathang.mahangand chitietdathang.sohoadon=dondathang.sohoadonand year(dondathang.ngaydathang)=2011group by mathang.mahang,mathang.tenhang,

month(dondathang.ngaydathang),year(dondathang.ngaydathang)set @var_tongsoluongnam=0set @var_tonggiabannam=0open thongke_cursor

fetch next from thongke_cursor into @var_mahang,@var_tenhang,@var_thang,@var_nam,@var_tongsoluong,@var_tonggiaban

while @@fetch_status=0begin

print 'Mahang '+@var_mahang+', Tenhang '+@var_tenhang+', thang '+cast(@var_thang as nvarchar(20))+', nam '+cast(@var_nam as nvarchar(20))+', Soluong '+cast(@var_tongsoluong as nvarchar(50))+', giaban '+cast(@var_tonggiaban as nvarchar(50))

set @var_tonggiabannam=@var_tonggiabannam+@var_tonggiabanset @var_tongsoluongnam=@var_tongsoluongnam+@var_tongsoluongfetch next from thongke_cursor into

@var_mahang,@var_tenhang,@var_thang,@var_nam,@var_tongsoluong,@var_tonggiaban

end

print 'Tong so luong ban trong nam: '+cast(@var_tongsoluongnam as nvarchar(50))+', Tong gia ban trong nam: '+cast(@var_tonggiabannam as nvarchar(50))close thongke_cursordeallocate thongke_cursor

drop procedure thongke_thang_nam

--update--tang so luong hang cua nhung mat hang do vinamilk cung cap--len gap doiupdate mathangset mathang.soluong=(mathang.soluong*2)where mathang.macongty=(select nhacungcap.macongty from nhacungcap where nhacungcap.tencongty='VINAMILK')

-- tang luong len gap ruoi cho nhung nhan vien ban duoc hang --soluong tren 100 va trong nam 2011update nhanvien set nhanvien.luongcoban=(nhanvien.luongcoban*1.5)where nhanvien.manhanvien=(select dondathang.manhanvien from dondathang, chitietdathangwhere dondathang.sohoadon=chitietdathang.sohoadonand year(dondathang.ngaydathang)=2011group by dondathang.manhanvienhaving sum(chitietdathang.soluong)>100)

-- tang phu cap len bang 50% luong cho nhung--nhan vien ban duoc nhieu hang nhatdelete tamdrop table tam

select dondathang.manhanvien, sum(chitietdathang.soluong) as tongsoluong into tam

from dondathang, chitietdathangwhere dondathang.sohoadon=chitietdathang.sohoadongroup by dondathang.manhanvien

update nhanvienset nhanvien.phucap=(nhanvien.luongcoban*0.5)where nhanvien.manhanvien= (select tam.manhanvienfrom tamwhere tam.tongsoluong=(select max(tam.tongsoluong)from tam))

create function soluonglonnhat()returns @bang table(

soluongmax int,manhanvienmax char(10)

)as

begindeclare @var_manhanvien as char(10)declare @var_manhanvienmax as char(10)declare @var_soluong as intdeclare @var_soluongmax as intdeclare soluong_cursor cursor forselect dondathang.manhanvien, sum(chitietdathang.soluong) as

tongsoluongfrom dondathang, chitietdathangwhere dondathang.sohoadon=chitietdathang.sohoadongroup by dondathang.manhanvienset @var_soluongmax=0open soluong_cursorfetch next from soluong_cursor into @var_manhanvien,@var_soluongwhile @@fetch_status=0begin

if(@var_soluongmax<@var_soluong)beginset @var_soluongmax=@var_soluongset @var_manhanvienmax=@var_manhanvienendfetch next from soluong_cursor into

@var_manhanvien,@var_soluongendclose soluong_cursordeallocate soluong_cursorinsert into @bang(soluongmax,manhanvienmax)values (@var_soluongmax,@var_manhanvienmax)

returnend

update nhanvienset nhanvien.phucap=(nhanvien.luongcoban*0.5)where nhanvien.manhanvien=(

select manhanvienmax from soluonglonnhat())

create function soluonglonnhat()returns char(10)asbegin

declare @var_manhanvien as char(10)declare @var_manhanvienmax as char(10)declare @var_soluong as intdeclare @var_soluongmax as intdeclare soluong_cursor cursor forselect dondathang.manhanvien, sum(chitietdathang.soluong) as

tongsoluongfrom dondathang, chitietdathangwhere dondathang.sohoadon=chitietdathang.sohoadongroup by dondathang.manhanvienset @var_soluongmax=0open soluong_cursorfetch next from soluong_cursor into @var_manhanvien,@var_soluongwhile @@fetch_status=0begin

if(@var_soluongmax<@var_soluong)

beginset @var_soluongmax=@var_soluongset @var_manhanvienmax=@var_manhanvienendfetch next from soluong_cursor into

@var_manhanvien,@var_soluongendclose soluong_cursordeallocate soluong_cursor

return @var_manhanvienmaxend

drop function soluonglonnhatselect dbo.soluonglonnhat() as '# soluong'

update nhanvienset nhanvien.phucap=(nhanvien.luongcoban*0.5)where nhanvien.manhanvien=(

select dbo.soluonglonnhat())

--delete--13 xoa khoi bang mat hang nhung mat hang co so luong bang 0--va khong duoc dat mua trong bat ki don dat hang naoSELECT DISTINCT chitietdathang.mahang from chitietdathang

delete mathangwhere mathang.soluong=0or mathang.mahang not in (select distinct chitietdathang.mahang from chitietdathang)

--THU TUC-- tao thu tuc thong ke tong so luong hang ban duoc--cua mot mat hang co ma bat ki--ma mat hang can thong ke la tham so cua thu tuccreate procedure tongsoluong_mahang@mahang as char(10)asselect chitietdathang.mahang, sum(chitietdathang.soluong) as soluongbanduocfrom chitietdathangwhere chitietdathang.mahang=@mahanggroup by chitietdathang.mahang

drop procedure tongsoluong_mahang 'H2'

-- viet ham tra ve mot bang trong do cho biet --tong so luong hang ban duoc cua moi mat hang--thong ke xem so luong hang hien co va da ban--cua moi mat hang la bao nhieucreate function thongke_soluong_hienco_daban()returns tableasreturn (

select mathang.mahang, mathang.tenhang, mathang.soluong as soluonghienco, sum(chitietdathang.soluong) as soluongdaban

from mathang,chitietdathangwhere mathang.mahang=chitietdathang.mahanggroup by mathang.mahang, mathang.tenhang, mathang.soluong

)--khong duoc sum(mathang.soluong)

drop function thongke_soluong_hienco_daban

select * from thongke_soluong_hienco_daban()

--TRIGGER--viet trigger cho bang chitietdathang--sao cho chi chap nhan gia ban ra nho hon hoac bang gia goc--(gia cua mat hang trong bang mat hang)create trigger chitietdathang_updateon chitietdathang for insertasdeclare @var_giaban as moneydeclare @var_giahang as moneydeclare @var_mahang as char(10)set @var_mahang=(select inserted.mahang from inserted)set @var_giaban=(select inserted.giaban from inserted)set @var_giahang=(select mathang.giahang from mathang where mathang.mahang=@var_mahang)if(@var_giaban>@var_giahang)begin

print 'khog duoc'rollback transaction

endelsebegin

print 'ok'end

drop trigger chitietdathang_update

insert into chitietdathang(sohoadon, mahang, giaban, soluong, mucgiamgia)values('HD9', 'H1', 3000,2,0)