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

- Category: Quan ly dc danh muc san pham
	- id: int -> primary key -> identity(1,1)
	- name: nvarchar(50) -> not null
	- created_at: datetime -> xac dinh ban ghi dc them vao thoi diem nao -> Bo trong thiet ke nay
	- updated_at: datetime -> xac dinh ban ghi dc sua vao ngay nao -> Bo trong thiet ke nay
- Product: Quản lý loại đồ uống trong quán cafe
	- id: int -> primary key -> identity(1,1)
	- title: nvarchar(50) -> not null
	- thumbnail: nvarchar(500) -> hien thi hinh anh cua san pham
	- price: float
	- description: text
	- id_category: int -> references -> Category(id)
	- created_at: datetime -> xac dinh ban ghi dc them vao thoi diem nao -> Bo trong thiet ke nay
	- updated_at: datetime -> xac dinh ban ghi dc sua vao ngay nao -> Bo trong thiet ke nay
- Staff: Quan ly nhan vien
	- id: int -> primary key -> identity(1,1)
	- fullname: nvarchar(50)
	- birthday: date,
	- address: nvarchar(200)
	- email: nvarchar(150)
	- gender: nvarchar(16)
	- phone_number: nvarchar(16)
	- created_at: datetime -> xac dinh ban ghi dc them vao thoi diem nao -> Bo trong thiet ke nay
	- updated_at: datetime -> xac dinh ban ghi dc sua vao ngay nao -> Bo trong thiet ke nay
- Customer: Quan ly thong tin KH
	- id: int -> primary key -> identity(1,1)
	- fullname: nvarchar(50)
	- birthday: date,
	- address: nvarchar(200)
	- email: nvarchar(150)
	- gender: nvarchar(16)
	- phone_number: nvarchar(16)
	- created_at: datetime -> xac dinh ban ghi dc them vao thoi diem nao -> Bo trong thiet ke nay
	- updated_at: datetime -> xac dinh ban ghi dc sua vao ngay nao -> Bo trong thiet ke nay
- Quan ly don hang:
	Phan tich:
		- id: int -> primary key -> identity(1,1)
		- id_staff -> int -> references Staff(id)
		- id_customer -> int -> references Customer(id)
		- order_date: datetime
		- Thong tin san pham
			- id_product: int -> references Product (id)
			- price: int -> luu gia tai thoi diem mua hang
			- amount: int -> so luong hang mua
		-> Luu y: 1 don hang -> gom nhieu san pham -> Thiet ke nhu nao???
		1 1 1 '2022-01-06 16:30:00' -> 1 20000 1 -> 1 record -> 1 ban ghi
								    -> 2 30000 3 -> 1 record -> 1 ban ghi

		-> Orders:
			1 1 1 '2022-01-06 16:30:00' -> 1 ban ghi
		-> Order_Detail:
			1 1 1 20000 1
			2 1 2 30000 3
	Quan ly du lieu bang 2 bang
	1) Orders
		- id: int -> primary key -> identity(1,1)
		- id_staff -> int -> references Staff(id)
		- id_customer -> int -> references Customer(id)
		- order_date: datetime
		- total_price: float -> tinh tong tien cua don -> easy -> redundant (sai)
	2) Order_Detail
		- id: int -> primary key -> identity(1,1)
		- id_order -> int -> references Orders (id)
		- id_product: int -> references Product (id)
		- price: int -> luu gia tai thoi diem mua hang
		- amount: int -> so luong hang mua
		- total_price: float -> tinh tong gia cua san pham nay -> easy -> redundant (sai)

		update order_detail set total_price = price * amount
=> Hoan thanh phan tich CSDL:

-- Tao CSDL: BT1815
create database BT1815
go

-- Kich hoat CSDL
use BT1815
go

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

create table product (
	id int primary key identity(1,1),
	title nvarchar(150) not null,
	thumbnail nvarchar(500),
	description text,
	price float,
	id_category int references category (id)
)
go

create table staff (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	address nvarchar(200),
	email nvarchar(150),
	gender nvarchar(16),
	phone_number nvarchar(16)
)
go

create table customer (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	address nvarchar(200),
	email nvarchar(150),
	gender nvarchar(16),
	phone_number nvarchar(16)
)
go

create table orders (
	id int primary key identity(1,1),
	id_staff int references staff (id),
	id_customer int references customer (id),
	order_date datetime
)
go

create table order_detail (
	id int primary key identity(1,1),
	id_order int references orders (id),
	id_product int references product (id),
	price float,
	amount int
)
go

-- Insert du lieu
insert into category (name)
values
('A'),
('B')
go

insert into staff(fullname, birthday, gender, phone_number, address)
values
('NV A', '1999-02-06', 'Nam', '123456789', 'Ha Noi'),
('NV B', '1999-02-06', 'Nam', '123456789', 'Ha Noi')
go

insert into customer(fullname, birthday, gender, phone_number, address)
values
('KH A', '1999-02-06', 'Nam', '123456789', 'Ha Noi'),
('KH B', '1999-02-06', 'Nam', '123456789', 'Ha Noi')
go

insert into product (id_category, title, price)
values
(1, 'SP A', 10000),
(1, 'SP B', 20000),
(1, 'SP C', 30000),
(2, 'SP D', 15000),
(2, 'SP E', 60000)
go

insert into orders(id_staff, id_customer, order_date)
values
(1, 1, '2021-01-06 09:30:00'),
(1, 2, '2022-06-06 09:30:00'),
(2, 1, '2022-03-06 09:30:00')
go

insert into order_detail (id_order, id_product, price, amount)
values
(1, 6, 20000, 2),
(2, 6, 10000, 1),
(2, 7, 25000, 1),
(3, 6, 20000, 1),
(3, 8, 25000, 2),
(3, 9, 30000, 3)
go

select * from product
select * from orders
select * from order_detail

--  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
select category.name 'Category Name', product.title 'Product Name', product.price
from category join product on category.id = product.id_category

create proc proc_view_product_by_cat
	@catId int
as
begin
	select category.name 'Category Name', product.title 'Product Name', product.price
	from category join product on category.id = product.id_category
	where category.id = @catId
end

exec proc_view_product_by_cat 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
---- id don hang, ten nhan vien, ten khach hang, ten san pham, danh muc sp, ngay ban, gia ban, so luong, tong tien
select orders.id, staff.fullname 'Ten NV', customer.fullname 'Ten KH', product.title 'Ten SP',
	category.name 'Danh Muc SP', orders.order_date, order_detail.price, order_detail.amount,
	order_detail.price * order_detail.amount 'Tong Tien'
from orders, staff, customer, order_detail, product, category
where customer.id = orders.id_customer
	and staff.id = orders.id_staff
	and orders.id = order_detail.id_order
	and order_detail.id_product = product.id
	and product.id_category = category.id
go

create proc proc_view_order_detail
	@orderId int
as
begin
	select orders.id, staff.fullname 'Ten NV', customer.fullname 'Ten KH', product.title 'Ten SP',
		category.name 'Danh Muc SP', orders.order_date, order_detail.price, order_detail.amount,
		order_detail.price * order_detail.amount 'Tong Tien'
	from orders, staff, customer, order_detail, product, category
	where customer.id = orders.id_customer
		and staff.id = orders.id_staff
		and orders.id = order_detail.id_order
		and order_detail.id_product = product.id
		and product.id_category = category.id
		and orders.id = @orderId
end

exec proc_view_order_detail 2

-- Hiển thị danh mục các đơn hàng theo mã KH
create proc proc_view_order_detail_by_customer
	@cusId int
as
begin
	select orders.id, staff.fullname 'Ten NV', customer.fullname 'Ten KH', product.title 'Ten SP',
		category.name 'Danh Muc SP', orders.order_date, order_detail.price, order_detail.amount,
		order_detail.price * order_detail.amount 'Tong Tien'
	from orders, staff, customer, order_detail, product, category
	where customer.id = orders.id_customer
		and staff.id = orders.id_staff
		and orders.id = order_detail.id_order
		and order_detail.id_product = product.id
		and product.id_category = category.id
		and orders.id_customer = @cusId
end

exec proc_view_order_detail_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
---- Tinh tong tien cua tung don hang
---- id don hang, ten nv, ten kh, ngay dat hang, tong tien
select orders.id, staff.fullname 'Ten NV', customer.fullname 'Ten KH', 
	orders.order_date, sum(order_detail.price * order_detail.amount) 'Tong Tien'
from orders, staff, customer, order_detail
where customer.id = orders.id_customer
	and staff.id = orders.id_staff
	and orders.id = order_detail.id_order
group by orders.id, staff.fullname, customer.fullname, orders.order_date
go

---- Tinh tong doanh thu
select sum(order_detail.price * order_detail.amount) 'Tong Tien'
from orders, order_detail
where orders.id = order_detail.id_order
go

---- Tham khao: de hieu ve ban chat price trong order_detail & product
select sum(product.price * order_detail.amount) 'Tong Tien'
from orders, order_detail, product
where orders.id = order_detail.id_order
	and product.id = order_detail.id_product
go

---- Store
create proc proc_money
	@startDate date,
	@endDate date
as
begin
	select sum(order_detail.price * order_detail.amount) 'Tong Tien'
	from orders, order_detail
	where orders.id = order_detail.id_order
		and orders.order_date >= @startDate
		and orders.order_date <= @endDate
end

select * from orders

exec proc_money '2022-01-01', '2022-12-30'
exec proc_money '2021-01-01', '2022-12-30'