2020-12-04 08:09:25

create database ProductManagementSystem

use ProductManagementSystem

create table tbIUser (
	Userid int primary key not null,
	Username nvarchar(50)
)
alter table tbIUser
add Birthdate date

create table tbIOrder (
	Orderid int primary key not null,
	Userid int references tbIUser(Userid) not null,
	Orderdate datetime
)

create table tbIProduct (
	Productid int primary key not null,
	Productname nvarchar(50),
	Quantity int,
	Price money,
	Description ntext
)

create table OrderDetail (
	Orderid int references tbIOrder(Orderid) not null,
	Productid int references tbIProduct(Productid) not null,
	Quantity int,
	Price money,
	constraint PK_OrderDetail primary key (Orderid, Productid)
)

insert into tbIUser(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')

select * from tbIUser

insert into tbIOrder(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')

select * from tbIOrder

insert into tbIProduct(Productid, Productname, Quantity, Price, Description)
values
(1, 'Asus Zen', 2, 10, 'See what other 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')

select * from tbIProduct

insert into OrderDetail(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)

select * from OrderDetail

alter table tbIOrder
add constraint DF_tbIOrder_Orderdate default getdate() for Orderdate

alter table tbIOrder
add constraint CK_tbIOrder_Orderdate check (Orderdate between '2000-01-01' and getdate())

alter table tbIUser
add constraint UN_tbIUser_Username unique (Username)

select * from tbIProduct

update tbIProduct set Price = Price * 0.9 where Productid = 3

select tbIUser.Userid, tbIUser.Username, tbIOrder.Orderid, tbIOrder.Orderdate, OrderDetail.Quantity, OrderDetail.Price, tbIProduct.Productname
from tbIUser, tbIOrder, tbIProduct, OrderDetail
where tbIUser.Userid = tbIOrder.Userid
	and tbIOrder.Orderid = OrderDetail.Orderid
	and tbIProduct.Productid = OrderDetail.Productid

create view view_Top2Product
as
select tbIProduct.Productname, tbIProduct.Productid, tbIProduct.Price, SUM(OrderDetail.Quantity) totalquantity
from tbIProduct, OrderDetail
where tbIProduct.Productid = OrderDetail.Productid
group by tbIProduct.Productname, tbIProduct.Productid, tbIProduct.Price

select * from view_Top2Product

create proc sp_TimSanPham
	@GiaMua money,
	@count int output

as
begin
	select * from tbIProduct
		where Price <= @GiaMua

	select @count = count(*) from tbIProduct
		where Price = @GiaMua
end

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

create trigger TG_tbIProduct_Update on tbIProduct
for update
as
begin
	if (select Price from inserted) < 10
	begin
		print N'You don`t update Price less than 10'
		rollback transaction
	end
end

select * from tbIProduct

update tbIProduct set Price = 50 where Productid = 1

create trigger TG_tblUser_Update on tbIUser
for update
as
begin
	if update(Username)
	begin
		if N'You don`t update Username column'
		rollback transaction
	end
end

select * from tbIUser

update tbIUser set Username = '123' where Userid = 1

2020-12-04 08:09:25

create database ProductManagementSystem

use ProductManagementSystem

create table tbIUser (
	Userid int primary key not null,
	Username nvarchar(50)
)
alter table tbIUser
add Birthdate date

create table tbIOrder (
	Orderid int primary key not null,
	Userid int references tbIUser(Userid) not null,
	Orderdate datetime
)

create table tbIProduct (
	Productid int primary key not null,
	Productname nvarchar(50),
	Quantity int,
	Price money,
	Description ntext
)

create table OrderDetail (
	Orderid int references tbIOrder(Orderid) not null,
	Productid int references tbIProduct(Productid) not null,
	Quantity int,
	Price money,
	constraint PK_OrderDetail primary key (Orderid, Productid)
)

insert into tbIUser(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')

select * from tbIUser

insert into tbIOrder(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')

select * from tbIOrder

insert into tbIProduct(Productid, Productname, Quantity, Price, Description)
values
(1, 'Asus Zen', 2, 10, 'See what other 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')

select * from tbIProduct

insert into OrderDetail(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)

select * from OrderDetail

alter table tbIOrder
add constraint DF_tbIOrder_Orderdate default getdate() for Orderdate

alter table tbIOrder
add constraint CK_tbIOrder_Orderdate check (Orderdate between '2000-01-01' and getdate())

alter table tbIUser
add constraint UN_tbIUser_Username unique (Username)

select * from tbIProduct

update tbIProduct set Price = Price * 0.9 where Productid = 3

select tbIUser.Userid, tbIUser.Username, tbIOrder.Orderid, tbIOrder.Orderdate, OrderDetail.Quantity, OrderDetail.Price, tbIProduct.Productname
from tbIUser, tbIOrder, tbIProduct, OrderDetail
where tbIUser.Userid = tbIOrder.Userid
	and tbIOrder.Orderid = OrderDetail.Orderid
	and tbIProduct.Productid = OrderDetail.Productid

create view view_Top2Product
as
select tbIProduct.Productname, tbIProduct.Productid, tbIProduct.Price, SUM(OrderDetail.Quantity) totalquantity
from tbIProduct, OrderDetail
where tbIProduct.Productid = OrderDetail.Productid
group by tbIProduct.Productname, tbIProduct.Productid, tbIProduct.Price

select * from view_Top2Product

create proc sp_TimSanPham
	@GiaMua money,
	@count int output

as
begin
	select * from tbIProduct
		where Price <= @GiaMua

	select @count = count(*) from tbIProduct
		where Price = @GiaMua
end

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

create trigger TG_tbIProduct_Update on tbIProduct
for update
as
begin
	if (select Price from inserted) < 10
	begin
		print N'You don`t update Price less than 10'
		rollback transaction
	end
end

select * from tbIProduct

update tbIProduct set Price = 50 where Productid = 1

create trigger TG_tblUser_Update on tbIUser
for update
as
begin
	if update(Username)
	begin
		if N'You don`t update Username column'
		rollback transaction
	end
end

select * from tbIUser

update tbIUser set Username = '123' where Userid = 1

2020-12-04 08:04:02

create database ProductManagementSystem
use ProductManagementSystem
create table tblUser(
	UserID int not null,
	UserName nvarchar(50)
)
create table tblOrder(
	OrderID int not null,
	UserID int not null,
	OrderDate datetime
)
create table tblProduct(
	ProductID int not null,
	ProductName nvarchar(50),
	Quantily int,
	price money,
	Description ntext
)
create table tblOrderDetail(
	OrderID int not null,
	ProductID int not null,
	Quantily int,
	price money
)
create clustered index CI_tblUser_UserID on tblUser(UserID)
alter table tblOrder
	add constraint DF_tblOrder_OrderDate default GETDATE() for OrderDate
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)

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)
alter table  tblUser
	add constraint UN_tblUser_UserName unique (UserName)
select * from tblUser
alter table tblUser
	add BirthDate datetime
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)
select * from 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)
select * from tblProduct
insert into tblProduct(ProductID,ProductName,Quantily,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 into tblOrderDetail(OrderID,ProductID,Quantily,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)
update tblProduct set price = price * 0.9 where ProductID = 3
select tblUser.UserID,tblUser.UserName,tblOrder.OrderID,tblOrder.OrderDate,tblOrderDetail.Quantily,tblOrderDetail.price,tblProduct.ProductName
from tblUser,tblOrder,tblProduct,tblOrderDetail
where tblUser.UserID = tblOrder.UserID
    and tblOrder.OrderID = tblOrderDetail.OrderID
	and tblProduct.ProductID = tblOrderDetail.ProductID
create view view_Top2Product
as
select tblProduct.ProductID,tblProduct.ProductName,tblProduct.price, Sum(tblOrderDetail.Quantily) TotalQuantiy
from tblProduct,tblOrderDetail
where tblProduct.ProductID = tblOrderDetail.ProductID
group by tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price
select * from view_Top2Product
create proc sp_Timsp
	@GiaMua money,
	@count int output
as
begin
	select * from tblProduct
	where price <= @GiaMua

	select @count = count(*) from tblProduct
	where price <= @GiaMua
end
declare @count int
exec sp_Timsp 50, @count = @count output
print N'Tim Thay' + convert(nvarchar(10), @count) + 'san pham'

2020-12-04 07:59:38

-- Tao database
create database ProductManagementSystemNew

-- Active database
use ProductManagementSystemNew

-- Create table
---- tblUser
create table tblUser (
	UserID INT NOT NULL,
	UserName NVARCHAR(50)
)

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

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

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

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

alter table tblUser
add BirthDate datetime

-- Constraint
alter table tblOrder
add constraint DF_tblOrder_OrderDate default GetDate() for OrderDate

---- 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)

---- 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)

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

alter table tblOrder
drop constraint CK_tblOrder_OrderDate

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

-- Insert Data
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')

select * from 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 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 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)

-- Query
select * from tblProduct

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

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

-- View
create view view_Top2Product
as
select tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price, SUM(tblOrderDetail.Quantity) TotalQuantiy
from tblOrderDetail, tblProduct
where tblProduct.ProductID = tblOrderDetail.ProductID
group by tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price

select top(2) * from view_Top2Product order by TotalQuantiy desc

-- Store
create proc sp_TimSanPham
	@GiaMua money,
	@count int output
as
begin
	select * from tblProduct
		where Price <= @GiaMua

	select @count = count(*) from tblProduct
		where Price <= @GiaMua
end

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

-- Trigger
create trigger TG_tblProduct_Update on tblProduct
for update
as
begin
	if (select Price from inserted) < 10
	begin
		print N'You don`t update price less than 10'
		rollback transaction
	end
end

select * from tblProduct

update tblProduct set Price = 50 where ProductID = 1

create trigger TG_tblUser_Update on tblUser
for update
as
begin
	if update(UserName)
	begin
		print N'You don`t update UserName column'
		rollback transaction
	end
end

select * from tblUser

update tblUser set UserName = '123' where UserID = 1

2020-12-04 03:39:06

create database ProductManagementSystem
use ProductManagementSystem

create table tblUser(
UserID int not null,
UserName nvarchar(50)
)

create table tblOrder(
OrderID int not null,
UserID int not null,
OrderDate datetime
)

create table tblProduct(
ProductID int not null,
ProductName nvarchar(50),
Quantity int,
Price money,
Description ntext
)

create table tblOrderDetail(
OrderID int not null,
ProductID int not null,
Quantity int,
Price money
)

--Danh index cot UserID bang User
create clustered Index CI_tblUser_UserID on tblUser(UserID)
--HUy Index
drop index CI_tblUser_UserID on tblUser

alter table tblUser
add BirthDate datetime

alter table tblOrder 
add constraint DF_tblOrder_OrderDate
default GETDATE() for OrderDate

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) 

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)

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

alter table tblUser
add constraint UN_tblUser_UserName UNIQUE (UserName)

--GHI DU LIEU
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 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 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 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')

select * from tblProduct
--Giam gia 10% tai bang product noi san pham co id =3
update tblProduct
set Price = Price*0.9 where ProductID = 3

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

select * from tblUser
select * from tblOrder
select * from tblOrderDetail
select * from tblProduct

--tAO View hien thi 2 san pham ban nhieu nhat

create view view_Top2Product
as
select tblOrderDetail.ProductID, tblProduct.ProductName, sum(tblOrderDetail.Quantity) as Tongsanpham
from tblProduct, tblOrderDetail where tblProduct.ProductID = tblOrderDetail.ProductID
group by tblOrderDetail.ProductID, tblProduct.ProductName

select * from view_Top2Product

--HIEN THI 2 san pham ban chay nhat]


select top 2 * from  view_Top2Product
order by Tongsanpham desc




select tblOrderDetail.ProductID, tblProduct.ProductName, sum(tblOrderDetail.Quantity) as Tongsanpham
from tblProduct, tblOrderDetail where tblProduct.ProductID = tblOrderDetail.ProductID
group by tblOrderDetail.ProductID, tblProduct.ProductName
ORDER BY sum(tblOrderDetail.Quantity) desc

select top (2) * from tblProduct

--Tao precedure
create PROC sp_TimSanPham 
  @GiaMua money,
  @count int OUTPUT
AS
BEGIN
	select * from tblProduct where Price < @GiaMua
	select @count =  count(Price) from tblProduct where Price < @GiaMua
END

declare @demsanpham int
declare @message nvarchar(50)
SET @message = N'So san pham tim dc:'
EXEC sp_TimSanPham '50', @count=@demsanpham output
PRINT  @message|@demsanpham 

--Tao trigger
create trigger TG_tblProduct_Update on tblProduct
for update 
AS
BEGIN
	if(select Price from inserted ) < 10
	begin
	print N'You don’t update this column UserName'
	rollback transaction 
	end
END

alter trigger TG_tblProduct_Update on tblProduct
for update 
AS
BEGIN
	if(select Price from inserted ) < 10
	begin
	print N'‘You don’t update price less than 10’.'
	rollback transaction 
	end
END

drop trigger TG_tblProduct_Update 

select * from tblProduct
update tblProduct set Price = '2' 

create trigger TG_tblUser_Update on tblUser
for update 
AS
BEGIN
	if update(UserName)
	begin
	print N'You don’t update this column UserName'
	rollback transaction
	end
END

select*from tblUser
update tblUser set UserName = 'duc' where UserID =1