Bài tập - Chương trình quản lý bán hàng - Lập trình SQL Server

-- Tao database BT1801
create database BT1801
go

-- Kich hoat database
use BT1801
go

-- Bang hang hoa
create table product (
	id int primary key identity(1,1),
	title nvarchar(200),
	producer_name nvarchar(200),
	madein nvarchar(50),
	buyer_price float,
	sell_price float,
	created_at date
)
go

-- Bang ban hang
create table orders (
	id int primary key identity(1,1),
	product_id int references product (id),
	number int,
	price float,
	note nvarchar(500)
)
go

-- Insert into database
insert into product (title, producer_name, madein, buyer_price, sell_price, created_at)
values
('San pham 1', 'ABC', 'Viet Nam', 20000, 50000, '2021-01-05'),
('San pham 2', 'ABC', 'Viet Nam', 10000, 50000, '2021-01-05'),
('San pham 3', 'GokiSoft', 'Viet Nam', 40000, 50000, '2021-01-05'),
('San pham 4', 'ABC', 'JP', 20000, 50000, '2021-01-05'),
('San pham 5', 'GokiSoft', 'Viet Nam', 20000, 50000, '2021-01-05'),
('San pham 6', 'GokiSoft', 'JP', 30000, 50000, '2021-01-05'),
('San pham 7', 'GokiSoft', 'Viet Nam', 20000, 50000, '2021-01-05'),
('San pham 8', 'ABC', 'JP', 20000, 50000, '2021-01-05'),
('San pham 9', 'ABC', 'JP', 20000, 50000, '2021-01-05'),
('San pham 10', 'GokiSoft', 'Lao', 20000, 50000, '2021-01-05')
go

insert into orders(product_id, number, price)
values
(1, 3, 50000),
(2, 2, 50000),
(2, 7, 50000),
(1, 2, 50000),
(3, 8, 50000),
(3, 3, 50000),
(1, 3, 50000),
(5, 5, 50000),
(5, 3, 50000),
(5, 3, 50000)
go

-- Tao view xem thong tin don hang: masx, ten sp, gia tien, so luong, tong gia
create view vw_order_list
as
select product.id, product.title, orders.price, orders.number, orders.price * orders.number 'Tong Gia'
from product, orders
where product.id = orders.product_id
go

select * from vw_order_list
order by id asc
go

create proc proc_view_order_by_madein
	@madein nvarchar(20)
as
begin
	select product.id, product.title, product.madein, orders.price, orders.number, orders.price * orders.number 'Tong Gia'
	from product, orders
	where product.id = orders.product_id
		and product.madein = @madein
end

exec proc_view_order_by_madein 'Viet Nam'
exec proc_view_order_by_madein 'JP'

create proc proc_view_order_by_benefit
	@id int
as
begin
	select product.id, product.title, product.madein, sum(orders.price * orders.number) 'Tong Tien'
	from product, orders
	where product.id = orders.product_id
		and product.id = @id
	group by product.id, product.title, product.madein
end

exec proc_view_order_by_benefit 1
exec proc_view_order_by_benefit 2
exec proc_view_order_by_benefit 3


create proc proc_view_order_by_benefit2
	@id int,
	@benefit int output
as
begin
	select product.id, product.title, product.madein, sum(orders.price * orders.number) 'Tong Tien'
	from product, orders
	where product.id = orders.product_id
		and product.id = @id
	group by product.id, product.title, product.madein

	select @benefit = sum(orders.price * orders.number)
	from product, orders
	where product.id = orders.product_id
		and product.id = @id
	group by product.id, product.title, product.madein
end

declare @benefit int
exec proc_view_order_by_benefit2 1, @benefit = @benefit output
print N'Tong doanh thu ' + convert(nvarchar, @benefit)
go