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