Bài tập - Quản lý quán cafe - Lập trình SQL Server - MySQL

-- Tao database
create database quan_ly_quan_cafe

-- Kich hoat database
use quan_ly_quan_cafe

-- Tao tables
create table category (
	id int primary key identity(1,1),
	name nvarchar(50)
)

create table product (
	id int primary key identity(1,1),
	title nvarchar(150) not null,
	price float,
	description text,
	thumbnail nvarchar(500)
)
alter table product
add id_cat int references Category (id)

create table Staff (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	address nvarchar(200),
	phone_number nvarchar(20),
	email nvarchar(150),
	bod date
)

create table Customer (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	address nvarchar(200),
	phone_number nvarchar(20),
	email nvarchar(150),
	bod date
)

create table Orders (
	id int primary key identity(1,1),
	id_staff int references Staff (id),
	id_customer int references Customer (id),
	order_date date,
	note varchar(500)
)

create table OrderDetail (
	id int primary key identity(1,1),
	id_order int references Orders (id),
	id_product int references Product (id),
	num int,
	price float
)

-- Insert Data
insert into Category (name)
values
('Cafe'),
('Sinh To')

select * from Category

insert into Product (title, thumbnail, description, price, id_cat)
values
('Cafe nong', 'Thumbnail_1', 'Noi dung 1', 32000, 1),
('Cafe da', 'Thumbnail_2', 'Noi dung 2', 32000, 1),
('Cafe sua', 'Thumbnail_3', 'Noi dung 3', 32000, 1),
('Sinh to bo', 'Thumbnail_4', 'Noi dung 4', 42000, 2),
('Sinh to mang cau', 'Thumbnail_5', 'Noi dung 5', 42000, 2)

select * from Product

insert into Staff(fullname, bod, email, address)
values
('TRAN VAN A', '1999-01-20', 'tranvana@gmail.com', 'Ha Noi')

insert into Customer(fullname, bod, email, address, phone_number)
values
('TRAN VAN B', '1990-01-20', 'tranvanb@gmail.com', 'Ha Noi', '1234567890')

insert into Orders(id_staff, id_customer, order_date, note)
values
(1, 1, '2021-02-26', ''),
(1, 1, '2021-02-25', ''),
(1, 1, '2021-02-27', '')

insert into OrderDetail(id_order, id_product, num, price)
values
(1, 1, 2, 32000),
(1, 2, 1, 32000),
(2, 1, 1, 32000),
(3, 1, 1, 32000),
(3, 5, 1, 42000)

-- Hiển thị danh sách loại đồ uống theo một danh mục -> yêu cầu viết truy vấn sql, tạo store (phần này làm 2 ý tách biết)
select product.*, category.name 'cat_name'
from product left join category on product.id_cat = category.id
where category.id = 1

create proc proc_view_product
	@catId int
as
begin 
	select product.*, category.name 'cat_name'
	from product left join category on product.id_cat = category.id
	where category.id = @catId
end

exec proc_view_product 1

-- Hiển thị danh mục sản phẩm trong 1 đơn hàng -> yêu cầu viết truy vấn sql và tạo 1 store cho chức năng này
---- Orders, OrderDetail, Product
select Orders.id 'OrderId', product.title, Orders.order_date, OrderDetail.price, OrderDetail.num, OrderDetail.price * OrderDetail.num as 'Total Price'
from Orders, product, OrderDetail
where Orders.id = OrderDetail.id_order
	and OrderDetail.id_product = product.id
	and Orders.id = 1

create proc proc_view_product_by_order
	@orderId int
as
begin
	select Orders.id 'OrderId', product.title, Orders.order_date, OrderDetail.price, OrderDetail.num, OrderDetail.price * OrderDetail.num as 'Total Price'
	from Orders, product, OrderDetail
	where Orders.id = OrderDetail.id_order
		and OrderDetail.id_product = product.id
		and Orders.id = @orderId
end

exec proc_view_product_by_order 1

-- Hiển thị danh mục các đơn hàng theo mã KH.
select Orders.id 'OrderId', Customer.id 'Cust No', Customer.fullname, Customer.address, Customer.phone_number, sum(OrderDetail.num * OrderDetail.price) 'TotalPrice'
from Orders, OrderDetail, Customer
where Orders.id_customer = Customer.id
	and Orders.id = OrderDetail.id_order
	and Customer.id = 1
group by Orders.id, Customer.id, Customer.fullname, Customer.address, Customer.phone_number

create proc view_all_orders_by_customer
	@custNo int
as
begin
	select Orders.id 'OrderId', Customer.id 'Cust No', Customer.fullname, Customer.address, Customer.phone_number, sum(OrderDetail.num * OrderDetail.price) 'TotalPrice'
	from Orders, OrderDetail, Customer
	where Orders.id_customer = Customer.id
		and Orders.id = OrderDetail.id_order
		and Customer.id = @custNo
	group by Orders.id, Customer.id, Customer.fullname, Customer.address, Customer.phone_number
end

exec view_all_orders_by_customer 1

-- Hiển thị doanh thu theo ngày bắt đầu và ngày kết thức -> yêu cầu viết theo store.
create proc proc_view_profit
	@startDate date,
	@endDate date
as
begin
	select sum(OrderDetail.num * OrderDetail.price) as 'Profit'
	from Orders, OrderDetail
	where Orders.id = OrderDetail.id_order
		and Orders.order_date between @startDate and @endDate
end

exec proc_view_profit '2020-01-01', '2022-01-01'