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

Hôm trước chúng ta đã nghiên cứu phần II

Hôm nay tiếp theo phần III về function, transaction, curcor, nâng cao proc

Câu 1:

Hiển thị danh sách nhân viên bán hàng gồm:

-       Mã nhân viên bán hàng

-       Tên nhân viên bán hàng

-       Địa chỉ

-       Hiển thị “Không” nếu nhân viên bán hàng ko phải là khách hàng, “Có” nếu nhân viên bán hàng là khách hàng (có mua hàng của nhà phân phối).

 

Chú ý: viết user-function để nhận biết nhân viên và khách hàng.

 

create function Hienthi(@SlsPerID nvarchar(10))

returns nvarchar(10)

as

begin

declare @hienthi nvarchar(10)

declare @name nvarchar(50)

select @name = name from SalesPerson where @SlsPerID= SlsPerID

if exists (select 1 from Customer where Name = @name)

begin

set @hienthi = N'có'

end else begin

SET @hienthi = N'không'

end

return(@hienthi)

end

 

select SlsPerID, Name, dbo.Hienthi(SlsPerID) as Display from SalesPerson

 

Câu 2:

Viết Store-procedure hiển thị danh sách nhân viên bán hàng cùng doanh số bán hàng trong năm 2010, trường hợp có doanh số <=100 triệu thì hiển thị “Ko đạt yêu cầu doanh số”, ngược lại hiển thị “Đạt yêu cầu doanh số”.

 

Chú ý: sử dụng cursor.

create PROCEDURE sp_HienThiDoanhSoCuaNhanVien2010

AS

BEGIN

 

CREATE TABLE #temp(

SlsPerID nvarchar(10),

Name nvarchar(50),

OrdAmt money,

HienThi nvarchar(100)

)

DECLARE contro CURSOR FOR select s.SlsPerID, s.Name, isnull(sum(x.OrdAmt), 0) as OrdAmt from xswSalesOrd x right join SalesPerson s

on x.SlsPerID = s.SlsPerID

where Year(x.OrderDate) = 2010 or x.OrderDate IS NULL

group by s.SlsPerID, s.Name

OPEN contro DECLARE @SlsPerID NVARCHAR(10) DECLARE @Name NVARCHAR(50)

DECLARE @OrdAmt money declare @HienThi nvarchar(100)

/*Bắt đầu duyệt qua các dòng trong kết quả truy vấn*/

FETCH NEXT FROM contro INTO @SlsPerID,@Name,@OrdAmt WHILE @@FETCH_STATUS=0

BEGIN

if @OrdAmt <= 100000000

set @HienThi = N'Không Đạt Yêu Cầu Doanh Số'

else

set @HienThi = N'Đạt Yêu Cầu Doanh Số'

INSERT INTO #temp(SlsPerID, Name, OrdAmt, HienThi)

SELECT @SlsPerID, @Name, @OrdAmt, @HienThi

FETCH NEXT FROM contro INTO @SlsPerID,@Name,@OrdAmt

END

/*Đóng con trỏ và giải phóng vùng nhớ*/

CLOSE contro DEALLOCATE contro

select * from #temp drop table #temp

END

 

Câu 3:

Viết user-function để tính toán chiết khấu cho mã hàng trong tất cả các đơn hàng trong năm 2010, mã hàng được truyền vào thông qua tham số của function.

Cụ thể như sau:

-       Quý 1/2010 chiết khấu 10% giá trị mã hàng

-       Quý 2/2010 chiết khấu 15% giá trị mã hàng

-       Quý 3/2010 chiết khấu 9% giá trị mã hàng

-       Quý 4/2010 chiết khấu 20% giá trị mã hàng

 

Chú ý: giá trị chiết khấu sau khi tính toán được update vào trường User3 trong table xswSlsOrdDet. Sử dụng transaction.

 

--Them Column User3 vao table xswSlsOrdDet

ALTER TABLE xswSlsOrdDet ADD User3 MONEY

--Create func fncQuy

CREATE FUNCTION fncQuy(@ngay DATETIME) RETURNS INT AS BEGIN

 

DECLARE @thang INT, @quy INT SET @thang = month(@ngay)

IF @thang < 4

SET @quy = 1

ELSE IF @thang < 7

SET @quy = 2

ELSE IF @thang < 10

SET @quy = 3

ELSE

SET @quy = 4

RETURN @quy

END

 

--Create func fncTinhChietKhau

CREATE FUNCTION fncTinhChietKhau(@giaBan MONEY, @ngayBan DATETIME)

RETURNS MONEY

AS

BEGIN

DECLARE @chietKhau money

SET @chietKhau = CASE dbo.fncQuy(@ngayBan) WHEN 1 THEN @giaBan*0.1

WHEN 2 THEN @giaBan*0.15

WHEN 3 THEN @giaBan*0.9

ELSE @giaBan*0.2

END

RETURN @chietKhau

END

 

--Create func fncChietKhauCuaMatHang

CREATE FUNCTION fncChietKhauCuaMatHang(@maHang NVARCHAR(10), @maDonHang NVARCHAR(10))

RETURNS MONEY AS BEGIN

 

DECLARE @chietKhau MONEY,

@ngayBan DATETIME,

@giaBan MONEY

SELECT @ngayBan = OrderDate

FROM xswSalesOrd

WHERE OrderNbr = @maDonHang

SELECT @giaBan = LineAmt

FROM xswSlsOrdDet

WHERE OrderNbr = @maDonHang AND InvtID = @maHang

SET @chietKhau = dbo.fncTinhChietKhau(@giaBan, @ngayBan)

RETURN @chietKhau

END

 

-- Bắt đầu cập nhật BEGIN TRAN

UPDATE xswSlsOrdDet

SET User3 = dbo.fncChietKhauCuaMatHang(InvtID,OrderNbr)

WHERE OrderNbr IN(

SELECT OrderNbr

FROM xswSalesOrd

WHERE year(OrderDate) = 2010

) IF @@ERROR != 0 -- neu co loi xay ra, rollback

BEGIN

PRINT 'rollback' ROLLBACK TRAN

END

 

Câu 4:

Viết store-procedure liệt kê 10 mặt hàng có doanh số cao nhất trong khoảng thời gian từ 01-Jan-2010 đến hết 31-Mar-2010.

 

create proc danhsachmathang2010

as

begin

 

declare @Start datetime,

@End datetime

select @Start=Convert(datetime,'01-Jan-2010',103)

select @End=Convert(datetime,'31-Mar-2010',103)

select top 10 xswSlsOrdDet.InvtID, sum (LineAmt) as doanhso

from (xswSalesOrd inner join xswSlsOrdDet on xswSalesOrd.OrderNbr= xswSlsOrdDet.OrderNbr) where ((OrderDate) between @Start and @End)

group by xswSlsOrdDet.InvtID

order by sum(LineAmt) desc

end

 

Câu 5:

Viết store-procedure hiển thị danh sách những khách hàng có số lần mua hàng trong từng tháng bằng với giá trị truyền vào thông qua tham số của store-procedure trong khoảng thời gian từ ngày đến ngày cũng được truyền vào qua tham số của store-procedure.

 

create proc danhsachkhachhang (@startDate varchar(10)=null, @endDate varchar(10)=null)

as

begin

 

Declare @Start datetime,

@End datetime

select @Start=Convert(datetime,@startDate,103)

select @End=Convert(datetime,@endDate,103)

if @Start > @End

print 'Loi: Ngay bat dau lon hon ngay ket thuc.';

else

SELECT xswSalesOrd.CustID, month(OrderDate) AS Thang, COUNT(xswSalesOrd.CustID) AS SoLanMuaHang

FROM Customer INNER JOIN xswSalesOrd ON Customer.CustID=xswSalesOrd.CustID

WHERE ((xswSalesOrd.OrderDate) BETWEEN @Start AND @End)

GROUP BY xswSalesOrd.CustID,Month(OrderDate)

end