Bài thực hành 1: Script
use Northwind
go
--1
declare @ns datetime, @tuoi int
set dateformat dmy
set @ns = '29/01/2002'
set @tuoi = DATEDIFF(day,@ns,getdate())/365
print 'Tuoi cua ban la ' + convert(nvarchar(3),@tuoi)
--2
Select EmployeeID, count(OrderID) As SoLuong,
case
when count(OrderID) < 3 then N'Sốlượng đơn hàng quá ít!'
else 'Okey'
end Trangthai
From Orders
Where Year (OrderDate) = 1997
Group by EmployeeID
--3
declare @maHD int
set @maHD = 10248
select [Link], [Link]
from [Order Details] o, Products p
where OrderID = @maHD and [Link] = [Link]
--4
Select * from Employees
declare @tenNV nvarchar(10)
set @tenNV = N'Thanh An'
if not exists (select FirstName from Employees where FirstName = @tenNV)
insert into Employees (FirstName, LastName)
values ('a',@tenNV)
Select @@ROWCOUNT
--5
select [Link], COUNT(distinct [Link]) as SLDH, SUM(Quantity) as SLSP
from Customers c, Orders o, [Order Details] d
where [Link] = [Link] and [Link] = [Link]
group by [Link]
having COUNT(distinct [Link]) =1 and SUM(Quantity) > 5
declare @TKKH table (MAKH nchar(5), SLDH int, SLSP int)
insert into @TKKH
select [Link], COUNT(distinct [Link]) as SLDH, SUM(Quantity) as
SLSP
from Customers c, Orders o, [Order Details] d
where [Link]=[Link] and [Link]=[Link]
group by [Link]
select *from @TKKH
where SLDH = 1 and SLSP > 5
--Câu 6
Declare @year nvarchar(50)
Set @year = 2002
Select [Link], [Link],[Link], SUM([Link]) As
'TongDoanhThu'
From [Link] soh, [Link] con
Where [Link] = [Link]
AND YEAR([Link]) = @year
Group By [Link], [Link] , [Link]
Having SUM([Link]) > (Select AVG(TotalDue)
From [Link]
Where YEAR(OrderDate) = @year)
Order By [Link] ASC
Go
--cau7
declare @NgayDH datetime, @slnv int, @tongDH int, @TBDH float
set dateformat dmy
set @NgayDH ='5/7/1996'
select @slnv = COUNT(*)
from Employees
select @tongDH = COUNT(*)
from Orders
set @TBDH = ROUND (@tongDH/@slnv,0)
select [Link], [Link] + ' ' + [Link] as Hoten, COUNT(OrderID) as
SLDHDL,
'ChenhLech' = @TBDH - COUNT(OrderID)
from Employees e, Orders o
where [Link] = [Link] and YEAR(OrderDate) = YEAR(@NgayDH)
group by [Link], [Link] + ' ' + [Link]
select *from Orders
Bài thực hành 2: Stored Procedure
--1) Viế t stored- procedure xuất danh sách các danh mục chưa có sản phẩ
m nào.
CREATE PROC spCau1
AS
BEGIN
SELECT *
FROM Categories
WHERE CategoryID NOT IN
(SELECT DISTINCT CategoryID
FROM Products)
END
INSERT INTO Categories(CategoryName)
VALUES (N'Nước giải khát')
EXEC spCau1
-- 2)Viết stored- procedure xuất danh sách khách hàng có đơn đặt hàng chưa giao
với sốlượng sản phẩ m mua > 1.
--Thủ tục có tham sốvào
go
CREATE PROC spCau2
AS
BEGIN
SELECT [Link]
FROM Customers c, Orders o, [Order Details] d
WHERE [Link] is null and [Link] = [Link] and [Link] =
[Link]
group by [Link], [Link]
having sum([Link]) > 10
end
exec spCau2
-- 3) Viế
t stored-procedure truyề
n vào mã sản phẩ
m, xuấ
t ra thông tin sản phẩm.
Go
CREATE PROC spCau3 @MaSP int
AS
BEGIN
SELECT *
FROM Products
WHERE ProductID = @MaSP
END
DECLARE @MaSanPham int
SET @MaSanPham=2
EXEC spCau3 @MaSanPham
--Cau4
go
Create proc spCau4 @NgayBD datetime, @NgayKT datetime
As
begin
if @NgayBD is null
Set @NgayBD = DATEADD (dd,+(Day(GEtDate())-1), getDAte())
select *
from Products
where ProductID in
(select ProductID
from [Order Details]
where OrderID in (
select OrderID
from Orders
where OrderDate between @NgayBD and @NgayKT)
)
end
set dateformat dmy
declare @NgayBatDau datetime, @NgayKetThuc datetime
set @NgayBatDau = '25/6/1996'
set @NgayKetThuc = '28/8/1996'
exec spCau4 @NgayBatDau, @NgayKetThuc
--Cau 6
go
create proc spCau6 @TenSP nvarchar(40), @DG money, @Sl int, @TT bit, @KetQua int
output
as
begin
if exists (select ProductName from Products where ProductName = @TenSP)
set @KetQua = -2
else
begin
insert into Products(ProductName, UnitPrice, QuantityPerUnit,Discontinued)
values (@TenSP, @DG, @Sl, @TT)
if @@ROWCOUNT > 0
set @KetQua = 1
else
set @KetQua = -1
end
end
declare @Kq int
exec spCau6 'Cocacola1',10000,2,-1, @Kq output
print @Kq
go
create function fnKTKH (@MaKH nchar(5))
returns bit
as
begin
declare @kq bit
if exists (select CompanyName from Customers where CustomerID = @MaKH)
set @kq = 1
else
set @kq = 0
return @kq
end
alter proc spThemDH @MaKH nchar(5)
as
begin
if [dbo].[fnKTKH] (@MaKH) = 1
begin
insert into Orders(CustomerID, OrderDate)
values (@MaKH, GETDATE())
print 'Dat Hang Thanh Cong'
end
else
print 'Loi'
end
exec spThemDH ANTON