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

Bảng sử dụng trong CSDL
- Quản lý danh mục sản phẩm -> category
	- id int primary key -> identity(1, 1)
	- Tên danh mục -> name -> nvarchar(150) -> unique
- Quản lý sản phẩm -> product
	- id int primary key -> identity(1, 1)
	- Tên sản phẩm -> title -> nvarchar(150)
	- thumbnail -> nvarchar(500)
		1 sản phẩm có nhiều hình ảnh -> Về nhà nghiên cứu cách thiết
	- giá bán -> price -> float
	- giá sau giảm -> discount -> float
	- description -> longtext
	- created_at -> datetime
	- updated_at -> datetime
	- category_id: int -> foreign key category (id)
- Quản lý nhân viên -> staff
	- id int primary key -> identity(1, 1)
	- fullname -> nvarchar(50)
	- birthday -> date
	- gender -> nvarchar(20)
	- email -> nvarchar(150)
	- phone_number -> nvarchar(20)
	- salary -> float
- Quản lý khách hàng -> customer
	- id int primary key -> identity(1, 1)
	- fullname -> nvarchar(50)
	- birthday -> date
	- gender -> nvarchar(20)
	- email -> nvarchar(150)
	- phone_number -> nvarchar(16)
- Quản lý đơn hàng
	- customer_id: foreign key -> references customer (id)
	- Ho & ten
	- SDT
	- Email
	- Dia chi
	- Thong tin san pham
		- san pham a
		- so luong bao nhieu
		- gia tien
		A 2 1000
		B 1 3000

	orders
		id: int primary key -> identity(1,1)
		customer_id: int -> foreign key customer (id)
		staff_id: int -> foreign key staff (id)
		fullname: nvarchar(50)
		phone_number: nvarchar(16)
		email: nvarchar(150)
		address: nvarchar(200)
		order_date: datetime
		status: tinyint -> (0: pending, 1: approved, 2: shipping, 3: delivered, 4: canceled)
	order_detail:
		order_id: foreign key -> orders (id)
		product_id: foreign key -> product (id)
		num: int
		price: float
=========================================================================

-- Tao CSDL
create database BT1815
go

-- Kich hoat CSDL
use BT1815
go

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

create table product (
	id int primary key identity(1,1),
	title nvarchar(150),
	thumbnail nvarchar(500),
	price float,
	discount float,
	description ntext,
	created_at datetime,
	updated_at datetime
)
go

create table staff (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	email nvarchar(150),
	phone_number nvarchar(20),
	salary float
)
go

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

create table orders (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	email nvarchar(150),
	phone_number nvarchar(20),
	order_date datetime,
	status tinyint
)
go

create table order_detail (
	order_id int not null references orders (id),
	product_id int not null references product (id),
	num int,
	price float,
	primary key (order_id, product_id)
)
go

-- Bo sung column thieu
alter table product
add category_id int references category (id)
go

alter table orders
add staff_id int references staff (id)
go

alter table orders
add customer_id int references customer (id)
go

-- insert data
insert into category (name)
values
('Cafe'),
('Sinh To'),
('Nuoc Ngot')
go

insert into product(title, category_id, price, discount)
values
('San pham 1', 1, 29000, 22000),
('San pham 2', 1, 39000, 32000),
('San pham 3', 1, 39000, 32000),
('San pham 4', 2, 59000, 42000),
('San pham 5', 2, 69000, 52000),
('San pham 6', 3, 20000, 12000)
go

insert into staff (fullname, email, phone_number, birthday, salary)
values
('Nhan Vien 1', 'nv1@gmail.com', '23424324', '1999-02-19', 20000),
('Nhan Vien 2', 'nv2@gmail.com', '57567567', '2000-06-19', 25000),
('Nhan Vien 3', 'nv3@gmail.com', '67867867', '2002-04-19', 15000),
('Nhan Vien 4', 'nv4@gmail.com', '45645655', '2001-08-19', 20000),
('Nhan Vien 5', 'nv5@gmail.com', '23424324', '1998-03-19', 25000),
('Nhan Vien 6', 'nv6@gmail.com', '56765756', '1999-05-19', 20000)
go

insert into customer (fullname, email, phone_number, birthday)
values
('Khach Hang 1', 'nv1@gmail.com', '23424324', '1999-02-19'),
('Khach Hang 2', 'nv2@gmail.com', '57567567', '2000-06-19'),
('Khach Hang 3', 'nv3@gmail.com', '67867867', '2002-04-19'),
('Khach Hang 4', 'nv4@gmail.com', '45645655', '2001-08-19'),
('Khach Hang 5', 'nv5@gmail.com', '23424324', '1998-03-19'),
('Khach Hang 6', 'nv6@gmail.com', '56765756', '1999-05-19')
go

alter table orders
drop column birthday

insert into orders (fullname, email, phone_number, order_date, status)
values
('KH01', 'kh01@gmail.com', '234234234', '2022-02-12', 0),
('KH02', 'kh02@gmail.com', '234234234', '2021-06-12', 0),
('KH03', 'kh03@gmail.com', '234234234', '2021-05-12', 0),
('KH04', 'kh04@gmail.com', '234234234', '2022-03-12', 0),
('KH05', 'kh05@gmail.com', '234234234', '2021-08-12', 0),
('KH06', 'kh06@gmail.com', '234234234', '2022-09-12', 0)
go

insert into order_detail (order_id, product_id, num, price)
values
(1, 1, 2, 30000),
(1, 2, 1, 30000),
(2, 1, 2, 30000),
(2, 3, 1, 30000),
(3, 1, 2, 30000),
(4, 3, 5, 30000),
(5, 4, 2, 30000)
go

-- customer -> phone_number -> nonclustered index
create nonclustered index IX_customer_phone_number on customer (phone_number)
go

-- Hien thi: ten san pham, gia , giam gia, ten danh muc
select product.title, product.price, product.discount, category.name
from product, category
where product.category_id = category.id
go

create proc proc_find_product_by_category
	@categoryId int
as
begin
	select product.title, product.price, product.discount, category.name
	from product, category
	where product.category_id = category.id
		and category.id = @categoryId
end

exec proc_find_product_by_category 1
exec proc_find_product_by_category 2

--  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
---- ten khach hang (orders), so dien thoai (orders), dia chi (orders), ten san pham (product), gia ban (order_detail), so luong (order_detail), tong tien (gia ban * so luong)

create proc proc_view_product_by_order
	@orderId int
as
begin
	select orders.fullname, orders.phone_number, product.title, order_detail.price, order_detail.num, (order_detail.num * order_detail.price) 'Gia Tien'
	from product, orders, order_detail
	where product.id = order_detail.product_id
		and order_detail.order_id = orders.id
		and orders.id = @orderId
	group by orders.fullname, orders.phone_number, product.title, order_detail.price, order_detail.num
end

exec proc_view_product_by_order 1
exec proc_view_product_by_order 3

create proc proc_view_product_by_customer
	@customerId int
as
begin
	select orders.fullname, orders.phone_number, product.title, order_detail.price, order_detail.num, (order_detail.num * order_detail.price) 'Gia Tien'
	from product, orders, order_detail
	where product.id = order_detail.product_id
		and order_detail.order_id = orders.id
		and orders.customer_id= @customerId
	group by orders.fullname, orders.phone_number, product.title, order_detail.price, order_detail.num
end

exec proc_view_product_by_customer 2
exec proc_view_product_by_customer 1

select * from orders
update orders set customer_id = 1
update orders set staff_id = 1
update orders set customer_id = 2 where id > 3

-- 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_profit
	@startDate datetime,
	@endDate datetime
as
begin
	select sum(order_detail.num * order_detail.price) 'Tong Doanh Thu'
	from orders, order_detail
	where orders.id = order_detail.order_id
		and orders.order_date between @startDate and @endDate
end

exec proc_profit '2022-01-01', ' 2023-01-01'
exec proc_profit '2021-01-01', ' 2022-01-01'