SQL - Thiết kế CSDL quản lý bán hàng - Lập trình SQL Server

-- Tao CSDL: BT1776
create database BT1776
go

-- Kich hoat CSDL
use BT1776
go

-- Tao tables
create table product (
	id int primary key identity(1,1),
	title nvarchar(250),
	nsx nvarchar(50),
	madein nvarchar(30),
	price float,
	buy_price float,
	created_at date
)
go

create table orders (
	id int primary key identity(1,1),
	product_id int references product (id),
	num int,
	order_date date,
	note nvarchar(500)
)
go

-- insert data
insert into product(title, price, buy_price, madein, nsx, created_at)
values
('San pham 1', 3000, 1000, 'VN', 'ABC', '2021-02-10'),
('San pham 2', 5000, 3000, 'VN', 'ABC', '2021-02-10'),
('San pham 3', 3500, 2000, 'JP', 'ABSC', '2020-02-10'),
('San pham 4', 4000, 2000, 'VN', 'ABEC', '2021-02-10'),
('San pham 5', 3600, 3000, 'Lao', 'ABDC', '2022-02-12'),
('San pham 6', 8000, 5000, 'Singapore', 'ABC', '2020-02-10')
go

insert into orders(product_id, num, order_date)
values
(1, 2, '2022-02-20'),
(1, 1, '2022-01-20'),
(2, 2, '2022-02-20'),
(2, 5, '2022-01-20'),
(3, 2, '2022-01-20'),
(4, 3, '2022-02-20')
go

-- Thực hiện liệt kê tất cả các đơn hàng đã được bán ra
select product.title, product.madein, product.price, product.buy_price, orders.num, orders.order_date
from product join orders on product.id = orders.product_id
order by orders.order_date desc
go

select product.title, product.madein, product.price, product.buy_price, orders.num, orders.order_date
from product join orders on product.id = orders.product_id
where product.madein = 'VN'
order by orders.order_date desc
go

-- Thống kê tổng giá bán được cho từng mặt hàng.
select product.title, product.madein, product.price, product.buy_price, orders.num,
	product.price * orders.num 'Gross', 
	product.buy_price * orders.num 'Buy Gross',
	(product.price - product.buy_price) * orders.num 'NET',
	orders.order_date
from product left join orders on product.id = orders.product_id
order by product.title asc
go

select product.title, product.madein,
	sum(product.price * orders.num) 'Gross', 
	sum(product.buy_price * orders.num) 'Buy Gross',
	sum((product.price - product.buy_price) * orders.num) 'NET'
from product left join orders on product.id = orders.product_id
group by product.title, product.madein
order by product.title asc
go