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

-- Tao CSDL
create database BT1801
go

-- Kich hoat CSDL
use BT1801
go

-- Tao tables
create table products (
	id int primary key identity(1,1),
	title nvarchar(150) not null,
	manufacturer_name nvarchar(200),
	madein nvarchar(50),
	buy_price float,
	sell_price float,
	created_at date
)
go

create table orders (
	id int primary key identity(1,1),
	id_hanghoa int references products (id),
	note nvarchar(500),
	sell_date date,
	amount int,
	price float
)
go

-- insert data
insert into products (title, manufacturer_name, madein, buy_price, sell_price, created_at)
values
('San Pham 1', 'ABC', 'Viet Nam', 2000, 4000, '2022-01-12'),
('San Pham 2', 'ABC', 'Viet Nam', 3000, 6000, '2022-02-12'),
('San Pham 3', 'ABC', 'Viet Nam', 1000, 3000, '2022-03-12'),
('San Pham 4', 'BBB', 'Viet Nam', 5000, 9000, '2022-02-12'),
('San Pham 5', 'BBB', 'Viet Nam', 6000, 10000, '2022-05-12'),
('San Pham 6', 'BBB', 'Viet Nam', 3000, 8000, '2022-06-12'),
('San Pham 7', 'BBB', 'Viet Nam', 4000, 8000, '2022-08-12'),
('San Pham 8', 'ABC', 'Viet Nam', 2000, 6000, '2022-01-12'),
('San Pham 9', 'ABC', 'Viet Nam', 8000, 12000, '2022-11-12')
go

insert into orders (id_hanghoa, amount, price, sell_date, note)
values
(1, 1, 4000, '2023-02-12', 'Ghi chu 1'),
(1, 2, 4000, '2023-03-12', 'Ghi chu 2'),
(2, 3, 6000, '2023-04-12', 'Ghi chu 3'),
(2, 1, 4000, '2023-05-12', 'Ghi chu 4'),
(2, 1, 4000, '2023-02-12', 'Ghi chu 5'),
(3, 5, 8000, '2023-03-12', 'Ghi chu 6'),
(4, 1, 4000, '2023-02-12', 'Ghi chu 7'),
(5, 8, 9000, '2023-05-12', 'Ghi chu 8'),
(6, 1, 4000, '2023-01-12', 'Ghi chu 9')
go

-- test data
select * from products
select * from orders
go

-- Thực hiện liệt kê tất cả các đơn hàng đã được bán ra -> Dùng view để thiết kế
---- MaDH (orders), ten san pham (products), so luong (orders), gia ban (orders), tong tien, ngay ban (orders)
select orders.id, products.title, orders.amount, orders.price, orders.amount * orders.price as 'TongTien', orders.sell_date
from orders, products
where orders.id_hanghoa = products.id
go

create view vw_show_orders
as
select orders.id, products.title, orders.amount, orders.price, orders.amount * orders.price as 'TongTien', orders.sell_date
from orders, products
where orders.id_hanghoa = products.id
go

select * from vw_show_orders

-- Liệt kê các đơn hàng được bán ra có xuất xứ -> yêu cầu viết procedure có tham số truyền vào là xuất xứ
create proc proc_find_orders_by_madein
	@madein nvarchar(50)
as
begin
	select orders.id, products.title, orders.amount, orders.price, orders.amount * orders.price as 'TongTien', orders.sell_date
	from orders, products
	where orders.id_hanghoa = products.id
		and products.madein = @madein
end

exec proc_find_orders_by_madein 'Viet Nam'

exec proc_find_orders_by_madein 'Japan'

--  Thống kê tổng giá bán được cho từng mặt hàng. -> viết procedure có tham số truyền vào là mặt hàng và tham số đấu già là total
create proc proc_calculate_money_by_product
	@productId int,
	@total float output
as
begin
	select @total = sum(amount * price)
	from orders
	where id_hanghoa = @productId
end

declare @total_out float
exec proc_calculate_money_by_product 1, @total = @total_out output
print @total_out