Video

[Examination] Quản lý sản phẩm - Lập trình SQL Server

-- Tao database: ProductManagementSystem
create database ProductManagementSystem

-- Active database: ProductManagementSystem
use ProductManagementSystem

-- Create tables
---- table: tblUser
create table tblUser (
	UserID INT NOT NULL,
	UserName NVARCHAR(50)
)
-- (1, 'dieptv', '1988-08-08') <-> (2, 'dieptv', '1999-01-01')

-- table: tblOrder
create table tblOrder (
	OrderID INT NOT NULL,
	UserID INT NOT NULL,
	OrderDate DATETIME
)

-- table: tblProduct
create table tblProduct (
	ProductID INT NOT NULL,
	ProductName NVARCHAR(50),
	Quantity INT,
	Price MONEY,
	Description NTEXT
)

-- table: tblOrderDetail
create table tblOrderDetail (
	OrderID INT NOT NULL,
	ProductID INT NOT NULL,
	Quantity INT,
	Price MONEY
)

-- index
create clustered index CI_tblUser_UserID on tblUser (UserID)

-- alter
alter table tblUser
add BirthDate DATETIME

-- constraint: default
alter table tblOrder
add constraint DF_tblOrder_OrderDate default getdate() for OrderDate

-- constraint: primary key
alter table tblUser
add constraint PK_tblUser primary key (UserID)

alter table tblOrder
add constraint PK_tblOrder primary key (OrderID)

alter table tblProduct
add constraint PK_tblProduct primary key (ProductID)

alter table tblOrderDetail
add constraint PK_tblOrderDetail primary key (OrderID, ProductID)

-- constraint: foreign key
alter table tblOrder
add constraint FK_tblOrder_tblUser foreign key (UserID) references tblUser (UserID)

alter table tblOrderDetail
add constraint FK_tblOrderDetail_tblOrder foreign key (OrderID) references tblOrder (OrderID)

alter table tblOrderDetail
add constraint FK_tblOrderDetail_tblProduct foreign key (ProductID) references tblProduct (ProductID)

-- constraint: check
alter table tblOrder
add constraint CK_tblOrder_OrderDate check (OrderDate between '2000-01-01' and getdate())

-- constraint: unique
alter table tblUser
add constraint UN_tblUser_UserName unique (UserName)

-- Insert Data
---- insert: tblUser
insert into tblUser(UserID, UserName, BirthDate)
values
(1 ,'stevejobs', '1996-08-28'),
(2 ,'billgates', '1998-06-18'),
(3 ,'larry', '1997-05-25'),
(4 ,'mark', '1984-03-27'),
(5 ,'dell', '1955-08-15'),
(6 ,'eric', '1955-07-28')

---- insert: tblOrder
insert into tblOrder (OrderID, UserID, OrderDate)
values
(1, 2, '2002-12-01'),
(2, 3, '2000-03-02'),
(3, 2, '2004-08-03'),
(4, 1, '2001-05-12'),
(5, 4, '2002-10-04'),
(6, 6, '2002-03-08'),
(7, 5, '2002-05-02')

---- insert: tblProduct
insert into tblProduct (ProductID, ProductName, Quantity, Price, Description)
values
(1, 'Asus Zen', 2, 10, 'See what others can’t see.'),
(2, 'BPhone', 10, 20, 'The first flat-design smartphone in the world.'),
(3, 'iPhone', 13, 300, 'The only thing that’s changed is everything.'),
(4, 'XPeria', 7, 80, 'The world’s first 4K smartphone.'),
(5, 'Galaxy Note', 12, 120, 'Created to reflect your desire.')

---- insert: tblOrderDetail
insert into tblOrderDetail (OrderID, ProductID, Quantity, Price)
values
(1, 1, 10, 10),
(1, 2, 4, 20),
(2, 3, 5, 50),
(3, 3, 6, 80),
(4, 2, 21, 120),
(5, 2, 122, 300)
go

-- Query
select * from tblProduct

update tblProduct set Price = 0.9 * Price
where ProductID = 3
go

-- Query: UserID (tblUser), UserName (tblUser), OrderID (tblOrder), OrderDate (tblOrder), Quantity (tblOrderDetail), Price (tblOrderDetail), ProductName (tblProduct)
select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
from tblUser, tblOrder, tblOrderDetail, tblProduct
where tblProduct.ProductID = tblOrderDetail.ProductID
	and tblOrderDetail.OrderID = tblOrder.OrderID
	and tblOrder.UserID = tblUser.UserID
go

---- Hoc
---- Tao view: Coi như table tạm thời -> Được sinh ra khi chạy câu truy vấn select
create view vw_orders -- vw_orders: Nhu la 1 table
as
select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
from tblUser, tblOrder, tblOrderDetail, tblProduct
where tblProduct.ProductID = tblOrderDetail.ProductID
	and tblOrderDetail.OrderID = tblOrder.OrderID
	and tblOrder.UserID = tblUser.UserID
go

select * from vw_orders
go

select * from vw_orders
where Price > 100
go

-- Thay noi dung trong view -> lam nhu nao
alter view vw_orders -- vw_orders: Nhu la 1 table
as
select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
from tblUser, tblOrder, tblOrderDetail, tblProduct
where tblProduct.ProductID = tblOrderDetail.ProductID
	and tblOrderDetail.OrderID = tblOrder.OrderID
	and tblOrder.UserID = tblUser.UserID
	and tblOrderDetail.Price > 20
go

select * from vw_orders
go

-- xoa view di
drop view vw_orders
go

-- 7. Views
---- Query: ProductID (tblProduct), ProductName (tblProduct), Price (tblProduct), TotalQuantity (tblOrderDetail)
select tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price, sum(tblOrderDetail.Quantity) TotalQuantity
from tblProduct, tblOrderDetail
where tblProduct.ProductID = tblOrderDetail.ProductID
group by tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price
order by TotalQuantity desc
go

create view view_Top2Product
as
	select top(2) tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price, sum(tblOrderDetail.Quantity) TotalQuantity
	from tblProduct, tblOrderDetail
	where tblProduct.ProductID = tblOrderDetail.ProductID
	group by tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price
	order by TotalQuantity desc
go

select * from view_Top2Product
go

-- Proc (procedure) -> Function
---- Proc khong tham so
create proc proc_test
as
begin
	select * from tblUser
	select * from tblProduct
	select * from tblOrder
	select * from tblOrderDetail
end

exec proc_test
go

-- Edit proc
alter proc proc_test
as
begin
	select * from tblUser
end

-- delete proc
drop proc proc_test
go

-- proc co tham so
create proc proc_view_product
	@price int
as
begin
	select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
	from tblUser, tblOrder, tblOrderDetail, tblProduct
	where tblProduct.ProductID = tblOrderDetail.ProductID
		and tblOrderDetail.OrderID = tblOrder.OrderID
		and tblOrder.UserID = tblUser.UserID
		and tblOrderDetail.Price > @price
end

select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
from tblUser, tblOrder, tblOrderDetail, tblProduct
where tblProduct.ProductID = tblOrderDetail.ProductID
	and tblOrderDetail.OrderID = tblOrder.OrderID
	and tblOrder.UserID = tblUser.UserID
	and tblOrderDetail.Price > 10

-- change proc
exec proc_view_product 10

select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
from tblUser, tblOrder, tblOrderDetail, tblProduct
where tblProduct.ProductID = tblOrderDetail.ProductID
	and tblOrderDetail.OrderID = tblOrder.OrderID
	and tblOrder.UserID = tblUser.UserID
	and tblOrderDetail.Price > 20

-- change proc
exec proc_view_product 20

create proc proc_view_product2
	@price int,
	@userId int
as
begin
	select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
	from tblUser, tblOrder, tblOrderDetail, tblProduct
	where tblProduct.ProductID = tblOrderDetail.ProductID
		and tblOrderDetail.OrderID = tblOrder.OrderID
		and tblOrder.UserID = tblUser.UserID
		and tblOrderDetail.Price > @price
		and tblUser.UserID = @userId
end

exec proc_view_product2 20, 1
go

---- Proc: cung co du lieu tra ve -> return (int, nvarchar, float, ...)
create proc proc_view_product3
	@price int,
	@userId int,
	@count int output
as
begin
	select @count = count(*)
	from tblUser, tblOrder, tblOrderDetail, tblProduct
	where tblProduct.ProductID = tblOrderDetail.ProductID
		and tblOrderDetail.OrderID = tblOrder.OrderID
		and tblOrder.UserID = tblUser.UserID
		and tblOrderDetail.Price > @price
		and tblUser.UserID = @userId
	
	select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
	from tblUser, tblOrder, tblOrderDetail, tblProduct
	where tblProduct.ProductID = tblOrderDetail.ProductID
		and tblOrderDetail.OrderID = tblOrder.OrderID
		and tblOrder.UserID = tblUser.UserID
		and tblOrderDetail.Price > @price
		and tblUser.UserID = @userId
end

declare @resCount int
exec proc_view_product3 20, 1, @count = @resCount output
print @resCount
print N'Ket qua: ' + CONVERT(nvarchar(20), @resCount)

create proc sp_TimSanPham
	@GiaMua int,
	@count int output
as
begin
	select @count = count(*) from tblProduct
	where Price <= @GiaMua

	select * from tblProduct
	where Price <= @GiaMua
end

declare @count int
exec sp_TimSanPham 50, @count = @count output
print N'Tim thay ' + CONVERT(nvarchar(20), @count) + ' san pham'
go