2021-02-18 08:17:40

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

-- Query
----Update [Price] of all records in the table [tblProduct] with 10% discount, where [ProductID] is equal to 3.
update tblProduct set Price = Price * 0.7 where ProductID = 3
----Display
----C1: where
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
----C2: left join -> hay dung nhat.
select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
from tblOrder left join tblUser on tblUser.UserID = tblOrder.UserID
	left join tblOrderDetail on tblOrder.OrderID = tblOrderDetail.OrderID
	left join tblProduct on tblOrderDetail.ProductID = tblProduct.ProductID
----C3: inner join (right join)
select tblUser.UserID, tblUser.UserName, tblOrder.OrderID, tblOrder.OrderDate, tblOrderDetail.Quantity, tblOrderDetail.Price, tblProduct.ProductName
from tblOrder inner join tblUser on tblUser.UserID = tblOrder.UserID
	inner join tblOrderDetail on tblOrder.OrderID = tblOrderDetail.OrderID
	inner join tblProduct on tblOrderDetail.ProductID = tblProduct.ProductID

-- view: [view_Top2Product]
create view [view_Top2Product]
as
select top(2) tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price, sum(tblOrderDetail.Quantity) as TotalQuantity
from tblOrderDetail left join tblProduct on tblOrderDetail.ProductID = tblProduct.ProductID
group by tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price
order by TotalQuantity desc

-- Proc: [sp_TimSanPham]
create proc [sp_TimSanPham]
	@GiaMua money,
	@count int output
as
begin
	select tblProduct.ProductID, tblProduct.ProductName, tblProduct.Quantity, tblProduct.Price, tblProduct.Description
	from tblProduct
	where tblProduct.Price <= @GiaMua

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

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

2021-01-27 05:04:47

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),
	Quatity int ,
	Price money,
	Description ntext
)
create table tblOrderDetail(
	OrderID int not null,
	ProductID int not null,
	Quantity int ,
	Price money
)
create Clustered index Cl_tblUser_UserID
ON tblUser(UserID)

alter table tblUser 
add BirthDate date

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

alter table tblUser
add primary key (UserID)

alter table tblOrder
add primary key (OrderID)

alter table tblProduct
add primary key (ProductID)

alter table tblOrderDetail
add constraint PK_OrderDetail 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)

insert into tblUser(UserID, UserName, BirthDate)
values 
(1, 'stevejobs', '1996-08-28'),
(2, 'billgates', '1998-08-18'),
(3, 'larry', '1997-05-25'),
(4, 'mark', '1984-03-27'),
(5, 'dell', '1955-08-15'),
(6, 'eric', '1996-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, Quatity, 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)

update tblProduct
set Price = Price*90/100
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

create view [view_Top2Product] as
select top 2 tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price, SUM(tblOrderDetail.Quantity)'TotalQuantity'
from tblOrder, tblOrderDetail, tblProduct
where tblOrder.OrderID=tblOrderDetail.OrderID and tblOrderDetail.ProductID=tblProduct.ProductID
group by tblProduct.ProductID, tblProduct.ProductName, tblProduct.Price
order by TotalQuantity desc

drop view [view_Top2Product]

select * from [view_Top2Product]

2020-12-13 10:48:08

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
)
create clustered index CI_tblUser_UserID on tblUser(UserID)
alter table tblUser
add birtdate date

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)

insert into tblUser(UserID,UserName,birtdate)
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')
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 tblOrder.UserID = tblUser.UserID and tblOrderDetail.OrderID = tblOrder.OrderID
and tblOrderDetail.ProductID = tblProduct.ProductID

alter view view_Top2Product
as
select top 2 tblProduct.ProductID,tblProduct.ProductName,sum(tblOrderDetail.Quantity) as TotalQuantiy
from tblUser, tblOrder, tblOrderDetail, tblProduct
where tblOrder.UserID = tblUser.UserID and tblOrderDetail.OrderID = tblOrder.OrderID
and tblOrderDetail.ProductID = tblProduct.ProductID
group by tblProduct.ProductID,tblProduct.ProductName
order by TotalQuantiy desc

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 30, @count = @count OUTPUT
print N'tim thay' + convert(nvarchar(10), @count) + 'san pham'

create trigger TG_tblProduct_Update on tblProduct
for update 
as
begin 
if (select Price from inserted) < 10
begin 
print N'chiu chiu'
rollback transaction
end
end

create trigger TG_tblUser_Update on tblUser
for UPDATE
as
begin
if update(userName)
begin
print N'You don’t update the column UserName!'
rollback transaction
end
end

2020-12-06 14:15:49

create database quanlysanpham
use quanlysanpham

create table tblUser(
	userid int not null primary key,
	username nvarchar(50) unique
)

create table tblOrder(
	orderid int not null primary key,
	userid int not null references tblUser(userid),
	orderdate datetime
	check (orderdate between '2000-01-01' and '2020-12-02')
)

create table tblProduct(
	productid int not null primary key,
	productname nvarchar(50),
	quantity int,
	price money,
	description ntext
)

create table tblOrder_detail(
	orderid int not null references tblOrder(orderid),
	productid int not null references tblProduct(productid),
	quantity int,
	price money
	constraint pk_orderid_productid primary key (orderid, productid)
)

create clustered index ci_tbluser_userid on tblUser(userid)
drop index ci_tbluser_userid

alter table tblUser
add birthdate datetime

insert into tblUser
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 tblUser

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

select * from tblOrder

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

select * from tblProduct

insert into tblOrder_detail
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 tblOrder_detail

update tblProduct set price=price*0.9 where productid=3
select tblUser.userid, username, tblOrder.orderid, orderdate, tblOrder_detail.price, tblOrder_detail.quantity, productname 
from tblUser, tblOrder, tblOrder_detail, tblProduct
where tblOrder.userid=tblUser.userid and tblOrder_detail.orderid=tblOrder.orderid 
and tblOrder_detail.productid=tblProduct.productid

create view view_Top2Product
AS
SELECT tblProduct.productid, productname, tblOrder_detail.price, sum(tblOrder_detail.quantity) as 'Total Quantity'
FROM tblOrder_detail, tblProduct
WHERE tblOrder_detail.productid=tblProduct.productid
group by tblProduct.productid, productname, tblOrder_detail.price

select top 2 * from  view_Top2Product
order by 'Total Quantity' desc

CREATE PROC sp_timsanpham
	@giamua money, 
	@count int output
AS
BEGIN
	select * from tblProduct
	where price <= @giamua
	BEGIN
		SELECT @count = COUNT(*) FROM tblProduct
		where Price <= @GiaMua
	END
END
declare @count int
exec sp_timsanpham 50, @count = @count output
print N'Tim thay ' + CONVERT(nvarchar(10), @count) + ' san pham'

create trigger TG_tblUser_Update on tblUser
for update
as
begin
	if update(username)
	begin
		print N'You dont update this username column'
		rollback transaction
	end
end

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

2020-12-06 09:02:27

create database ProductManagementSystem
use ProductManagementSystem
create table tblUser(
UserID int not null,
UserName nvarchar(100)
)
go
create table tblOrder(
OrderID int not null,
UserID int not null,
OrderDate datetime
)
go
create table tblProduct(
ProductID int not null,
ProductName nvarchar(100),
Quantity int,
Price money,
)
go
create table tblOrderDetail(
OrderID int not null,
ProductID int not null,
Quantity int,
Price money
)
go
create index CI_tblUser_UserID1
on tblUser(UserID)
drop index CI_tblUser_UserID1
on tblUser
alter table tblUser
add BirthDate DATETIME
alter table tblOrder 
ADD CONSTRAINT DF_tblOrder_OrderDate
DEFAULT GETDATE() FOR OrderDate;
alter table tblUser
add primary key (UserID)
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 PK_tblOrder primary key (OrderID)
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 < '2020-12-02' and  OrderDate > '2000-01-01')
alter table tblUser
add constraint UN_tblUser_UserName UNIQUE (UserName)
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 tblUser
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 tblOrder
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.')
select * from tblProduct
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 tblOrderDetail
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
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

create proc sp_TimSanPham
  @GiaMua money,
  @count int out
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'

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

2020-12-04 09:43:55

create database ProductManagementSystem
use ProductManagementSystem
create table tblUser(
UserID int not null,
UserName nvarchar(100)
)
go
create table tblOrder(
OrderID int not null,
UserID int not null,
OrderDate datetime
)
go
create table tblProduct(
ProductID int not null,
ProductName nvarchar(100),
Quantity int,
Price money,
)
go
create table tblOrderDetail(
OrderID int not null,
ProductID int not null,
Quantity int,
Price money
)
go
create index CI_tblUser_UserID1
on tblUser(UserID)
drop index CI_tblUser_UserID1
on tblUser
alter table tblUser
add BirthDate DATETIME
alter table tblOrder 
ADD CONSTRAINT DF_tblOrder_OrderDate
DEFAULT GETDATE() FOR OrderDate;
alter table tblUser
add primary key (UserID)
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 PK_tblOrder primary key (OrderID)
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 < '2020-12-02' and  OrderDate > '2000-01-01')
alter table tblUser
add constraint UN_tblUser_UserName UNIQUE (UserName)
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 tblUser
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 tblOrder
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.')
select * from tblProduct
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 tblOrderDetail
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
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

create proc sp_TimSanPham
  @GiaMua money,
  @count int out
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'

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

2020-12-04 08:57:57

create database ProductManagementSystem

use ProductManagementSystem

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

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

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

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

create index CI_tblUser_UserID1
on tblUser(UserID)

drop index CI_tblUser_UserID1
on tblUser

alter table tblUser
add  BirthDate DATETIME

alter table tblOrder
ADD CONSTRAINT DF_tblOrder_OrderDate
DEFAULT GETDATE( ) FOR OrderDate;

alter table tblUser
add primary key (UserID)

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 PK_tblOrder primary key (OrderID)

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 < '2020-12-02' and  OrderDate > '2000-01-01')

alter table tblUser
add constraint UN_tblUser_UserName UNIQUE (UserName)

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 tblUser

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

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

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

create proc sp_TimSanPham
  @GiaMua money,
  @count int out
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'

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

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

2020-12-04 08:48:49

create database ProductManagementSystem
use ProductManagementSystem

---- table creation ---
create table tblUser (
UserID int identity(1,1) NOT NULL ,
UserName nvarchar(50),
birthdate datetime
)
go

insert into tblUser(UserName,birthdate)
values
('stevejobs','1996-08-28'),
('billgates','1998-06-18'),
('larry','1997-05-25'),
('mark','1984-03-27'),
('dell','1955-08-15'),
('eric','1955-07-28')

create table tblOrder (
OrderID int NOT NULL ,
UserID int NOT NULL,
OrderDate datetime
 )
 go
 
 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')
 
 create table tbl_Product(
 ProductID int identity(1,1) NOT NULL, 
 ProductName nvarchar(50),
 Quantity int,
 Price MONEY,
 Description ntext
 )
 go

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

 create table tblOrderDetail(
 OrderID int NOT NULL,
 ProductID int NOT NULL,
 Quantity int,
 Price Money
 )
 go

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

 create clustered index CI_tblUser_UserID
 on tblUser(UserID)

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

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

 --- inserting data ---
 select *from tblUser
 select *from tblOrder
 select *from tbl_Product
 select *from tblOrderDetail

 --- Query Operations ---
UPDATE tbl_Product
SET Price = Price * 0.9
WHERE ProductID = 3

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

---- view ---
CREATE VIEW view_Top2Product 
AS
SELECT tbl_Product.ProductID,tbl_Product.ProductName, tbl_Product.Price, 
WHERE tbl_Product.ProductID = tblOrderDetail.ProductID
group by tbl_Productt.ProductID, tbl_Product.ProductName, tbl_Product.Price

----store ---
create proc sp_timsanpham
@giamua money ,
@count int output 
as
begin
	select *from tbl_Product 
	where Price <=@giamua
end

declare @count int output 
exec sp_timsanpham 50, @count output

print N'Tim Thay ' +convert nvarchar(10) @count) + ' san pham'

-- Trigger
create trigger TG_tbl_Productt_Update on tbl_Product
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 tbl_Product

update tbl_Product 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 08:47:49

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
)

create clustered index CI_tblUser_UserID on tblUser (UserID)

alter table tblUser
drop clustered CI_tblUser_UserID

alter table tblUser
add birthday datetime;

---4. constraints
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_tblUser foreign key (UserID) references tblUser(UserID)

alter table tblOrder
add constraint CK_tblOder_Orderdate check (OrderDate between 2000-01-01 and getdate())

ALTER TABLE tblUser
ADD CONSTRAINT UC_tblUser_UserName UNIQUE (UserName);

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', 'cric','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'),
('4','2','21','120')
('5','2','122','300')

select*from tblProduct
UPDATE tblProduct
SET Price=0.9*Price
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

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

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

2020-12-04 08:17:04

create database ProductManagementSystem
use ProductManagementSystem

create table tbIUser (
	userId int not null,
	userName nvarchar(50)
)

create table tbIOrder (
	orderId int not null,
	userId int not null,
	orderDate datetime
)

create table tbIProduct (
	productId int not null,
	productName nvarchar(50),
	quantity int,
	price money,
	description ntext
)

create table tbIOrderDetail (
	orderId int not null,
	productId int not null,
	quantity int,
	price money
)

create clustered index CI_tbIUser_UserID on tbIUser (userId)
drop index CI_tbIUser_UserID on tbIUser

alter table tbIUser 
add birthDate datetime

alter table tbIOrder 
add constraint df_tbIOrder_orderDate 
default getdate() for orderdate

alter table tbIUser 
add constraint pk_tbIUser primary key (userId)

alter table tbIOrder
add constraint pk_tbIOrder primary key (orderID

alter table tbIProduct
add constraint pk_tbIProduct primary key (productId)

alter table tbIOrderDetail
add constraint pk_tbIOrderDetail primary key (orderId, productId)

alter table tbIOrder 
add constraint fk_tbIOrder_tbIUser 
foreign key (userId) references tbIUser(userId)

alter table tbIOrderDetail
add constraint fk_tbIOrderDetail_tbIOrder
foreign key (orderId) references tbIOrder(orderId)

alter table tbIOrderDetail
add constraint fk_tbIOrderDetail_tbIProduct
foreign key (productId) references tbIProduct(productId)

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)

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

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

insert into tbIProduct(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 tbIOrderDetail(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 tbIUser
select * from tbIOrder
select * from tbIProduct
select * from tbIOrderDetail

update tbIProduct set price=price*0.9 where productId=3

select tbIUser.userId, tbIUser.userName, tbIOrder.orderId, tbIOrder.orderDate, tbIOrderDetail.quantity, tbIOrderDetail.price, tbIProduct.productName
from tbIUser, tbIOrder, tbIOrderDetail, tbIProduct
where tbIUser.userId=tbIOrder.userId and tbIOrder.orderId=tbIOrderDetail.orderId and tbIOrderDetail.productId=tbIProduct.productId

create view view_top2product 
as
select tbIProduct.productId, tbIProduct.productName, tbIProduct.price, sum(tbIOrderDetail.quantity) 'Tatalquantity'
from tbIProduct, tbIOrderDetail
where tbIProduct.productId=tbIOrderDetail.productId
group by tbIProduct.productId, tbIProduct.productName, 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_tblProduct_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=8 where productId=1

 create trigger TG_tblUser_Update on tbIUser 
 for update
 as
 begin
	if update (userName)
	begin
		print N'You don’t update the column UserName!'
		rollback transaction
	end
 end

 select * from tbIUser
 update tbIUser set userName='Do Tuan Anh' where userId=1