Khoá học lập trình SQL [Video] Bài tập - Quản lý quán cafe - Lập trình SQL Server - MySQL

- Danh mục : category
	- id : int -> primary key -> identity (1,1)
	- name : nvarchar(50)
- Sản phẩm : product
	- id : int -> primary key -> identity (1,1)
	- title : nvarchar(50)
	- price : float
	- description: text -> giới thiệu đồ uống
	- id_category: int -> foreign key -> category (id)
- Nhân viên : staff
	- id : int -> primary key -> identity (1,1)
	- fullname : nvarchar(50)
	- address : nvarchar(150)
	- gender : nvarchar(15)
	- birthday : date
	- phone_number : nvarchar(20)

- Khách hàng: customer
	- id: int => primary key => identity (1,1)
	- fullname: nvarchar(50)
	- phone_number: nvarchar(20)
	- email: nvarchar(150)
	
- Quản lý đơn hàng :
	Khách hàng : tên, số điện thoại, email
		- cafe
		- sinh tố
		- nước ép cam

	Order
		id : int => primary key => identity (1,1) => mã đơn hàng
		customer_id: int => foreign key => customer (id)
		staff_id: int => foreign key => staff (id)
		price_total: float

	OrderDetail
		id: int => primary key => identity (1,1) => mã chi tiết đơn hàng
		order_id: int => foreign key => order (id)
		product_id: int => foreign key => product (id)
		price: float
		num: int
		price_total: float

-- Tao database : QuanLyCafe
create database QuanLyCafe
go

-- Kich hoat database : QuanLyCafe
use QuanLyCafe
go

-- Tao bang Category
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(50) not null,
	description text,
	price float,
	category_id int references category(id)
)
go

create table Staff (
	id int primary key identity(1,1),
	fullname nvarchar(50) not null,
	address nvarchar(200),
	gender nvarchar(20),
	birthday date,
	phone_number nvarchar(20)
)
go

create table Customer (
	id int primary key identity(1,1),
	fullname nvarchar(50) not null,
	phone_number nvarchar(150),
	email nvarchar(50)
)
go

create table Orders (
	id int primary key identity(1,1),
	customer_id int references Customer (id),
	staff_id int references Staff (id),
	price_total float
)
go

alter table Orders
add created_at datetime

create table OrderDetail (
	id int primary key identity(1,1),
	product_id int references Product (id),
	price float,
	num int,
	price_total float,
	order_id int references Orders (id)
)
go

-- Nhap du lieu mau cho database
insert into Category (name)
values
('Cafe'),
('Nuoc Ep'),
('Sinh To')
go

select * from Category

insert into Product(title, price, category_id)
values
('Cafe Sua',32000, 1),
('Cafe Da', 29000, 1),
('Cafe Nong', 49000, 1),
('Nuoc Ep Oi', 59000, 2),
('Nuoc Ep Cam', 69000, 2)
go

insert into Staff(fullname, gender, birthday, phone_number, address)
values
('Tran Van A', 'Nam', '1999-02-06', '123456789', 'Ha Noi'),
('Tran Thi B', 'Nu', '1990-06-12', '1231321232', 'Ha Noi')
go

insert into Customer (fullname, email, phone_number)
values
('Nguyen Van A', 'a@gmail.com', '123'),
('Nguyen Van B', 'b@gmail.com', '123'),
('Nguyen Van C', 'c@gmail.com', '123'),
('Nguyen Van D', 'd@gmail.com', '123'),
('Nguyen Van E', 'e@gmail.com', '123')
go

insert into Orders (customer_id, staff_id, price_total)
values
(1, 1, 96000)
go
update Orders set created_at = '2020-08-20' where id = 1

insert into OrderDetail (order_id, product_id, price, num, price_total)
values
(1, 1, 32000, 3, 96000)


insert into Orders (customer_id, staff_id, price_total, created_at)
values
(1, 1, 133000, '2020-08-22')
go
update Orders set customer_id = 2 where id = 2

insert into OrderDetail (order_id, product_id, price, num, price_total)
values
(2, 1, 32000, 2, 64000),
(2, 5, 69000, 1, 69000)
go

-- 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 * from Category
select * from Product

----- Hien thi : CategoryName, ProductName, Price
select Category.name CategoryName, Product.title ProductName, Product.price Price
from Category, Product
where Category.id = Product.category_id
	and Category.id = 1

create proc proc_xem_menu_theo_danh_muc
	@id int
as
begin
	select Category.name CategoryName, Product.title ProductName, Product.price Price
	from Category, Product
	where Category.id = Product.category_id
		and Category.id = @id
end

exec proc_xem_menu_theo_danh_muc 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
--------- OrderId, StaffName, CustomerName, ProductName, Price, Num, PriceTotal, OrderDate
--------- Orders, OrderDetail, Staff, Customer, Product
select Orders.id OrderId, Staff.fullname StaffName, Customer.fullname CustomerName, Product.title ProductName, OrderDetail.price Price, OrderDetail.num Num, Orders.created_at OrderDate
from Orders, OrderDetail, Staff, Customer, Product
where Orders.id = OrderDetail.order_id
	and Orders.customer_id = Customer.id
	and Orders.staff_id = Staff.id
	and OrderDetail.product_id = Product.id
	and Orders.id = 2

select * from Orders
select * from Customer

create proc proc_xem_don_hang
	@orderId int
as
begin
	select Orders.id OrderId, Staff.fullname StaffName, Customer.fullname CustomerName, Product.title ProductName, OrderDetail.price Price, OrderDetail.num Num, Orders.created_at OrderDate
	from Orders, OrderDetail, Staff, Customer, Product
	where Orders.id = OrderDetail.order_id
		and Orders.customer_id = Customer.id
		and Orders.staff_id = Staff.id
		and OrderDetail.product_id = Product.id
		and Orders.id = @orderId
end

exec proc_xem_don_hang 2

create proc proc_xem_don_hang_theo_ma_kh
	@customerId int
as
begin
	select Orders.id OrderId, Staff.fullname StaffName, Customer.fullname CustomerName, Product.title ProductName, OrderDetail.price Price, OrderDetail.num Num, Orders.created_at OrderDate
	from Orders, OrderDetail, Staff, Customer, Product
	where Orders.id = OrderDetail.order_id
		and Orders.customer_id = Customer.id
		and Orders.staff_id = Staff.id
		and OrderDetail.product_id = Product.id
		and Orders.customer_id = @customerId
end

exec proc_xem_don_hang_theo_ma_kh 2

---- 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.
select Orders.id OrderId, Staff.fullname StaffName, Customer.fullname CustomerName, Product.title ProductName, OrderDetail.price Price, OrderDetail.num Num, Orders.created_at OrderDate
	from Orders, OrderDetail, Staff, Customer, Product
	where Orders.id = OrderDetail.order_id
		and Orders.customer_id = Customer.id
		and Orders.staff_id = Staff.id
		and OrderDetail.product_id = Product.id
		and Orders.created_at >= '2020-08-18'
		and Orders.created_at <= '2020-08-21'

select sum(price_total) 'Tong Doanh Thu'
from Orders
where Orders.created_at >= '2020-08-18'
	and Orders.created_at <= '2020-08-22'

create proc proc_doanh_thu
	@startDate date,
	@endDate date
as
begin
	select sum(price_total) 'Tong Doanh Thu'
	from Orders
	where Orders.created_at >= @startDate
		and Orders.created_at <= @endDate
end

exec proc_doanh_thu '2020-08-18', '2020-08-23'

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