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

DATABASE

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

Được viết bởi QuangIT ngày 26/12/2012 lúc 11:20 AM
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
  • 0
  • 5348

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
Chạy Query này để tạo bảng mẫu: 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SalesPerson]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SalesPerson](
[SlsPerID] [varchar](30) NOT NULL,
[Name] [varchar](30) NULL,
 CONSTRAINT [pk_salesperson] PRIMARY KEY CLUSTERED 
(
[SlsPerID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Inventory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Inventory](
[InvtID] [varchar](30) NOT NULL,
[Descr] [varchar](30) NULL,
[StkBasePrc] [varchar](30) NULL,
 CONSTRAINT [pk_inventory] PRIMARY KEY CLUSTERED 
(
[InvtID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fncQuy]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[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' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Customer](
[CustID] [varchar](30) NOT NULL,
[Name] [varchar](30) NULL,
 CONSTRAINT [pk_customer] PRIMARY KEY CLUSTERED 
(
[CustID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xswSalesOrd]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[xswSalesOrd](
[OrderNbr] [int] NOT NULL,
[OrderDate] [datetime] NULL,
[SlsPerID] [varchar](30) NULL,
[CustID] [varchar](30) NULL,
[OrdAmt] [money] NULL,
 CONSTRAINT [pk_ordernbr] PRIMARY KEY CLUSTERED 
(
[OrderNbr] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xswSlsOrdDet]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[xswSlsOrdDet](
[OrderNbr] [varchar](30) NOT NULL,
[InvtID] [varchar](30) NULL,
[LineQty] [int] NULL,
[LineAmt] [money] NULL,
[User3] [money] NULL,
 CONSTRAINT [pk_xswslsorddet] PRIMARY KEY CLUSTERED 
(
[OrderNbr] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Hienthi]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'create function [dbo].[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' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_HienThiDoanhSoCuaNhanVien2010]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create PROCEDURE [dbo].[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' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fncTinhChietKhau]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[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' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[danhsachkhachhang]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[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' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[danhsachmathang2010]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create proc [dbo].[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' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fncChietKhauCuaMatHang]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[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' 
END

GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fk1]') AND parent_object_id = OBJECT_ID(N'[dbo].[xswSalesOrd]'))
ALTER TABLE [dbo].[xswSalesOrd]  WITH CHECK ADD  CONSTRAINT [fk1] FOREIGN KEY([SlsPerID])
REFERENCES [dbo].[SalesPerson] ([SlsPerID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fk2]') AND parent_object_id = OBJECT_ID(N'[dbo].[xswSalesOrd]'))
ALTER TABLE [dbo].[xswSalesOrd]  WITH CHECK ADD  CONSTRAINT [fk2] FOREIGN KEY([CustID])
REFERENCES [dbo].[Customer] ([CustID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fk3]') AND parent_object_id = OBJECT_ID(N'[dbo].[xswSlsOrdDet]'))
ALTER TABLE [dbo].[xswSlsOrdDet]  WITH CHECK ADD  CONSTRAINT [fk3] FOREIGN KEY([InvtID])
REFERENCES [dbo].[Inventory] ([InvtID])
ON UPDATE CASCADE
ON DELETE CASCADE

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

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