By GokiSoft.com| 19:33 09/11/2022|
SQL Server/MySQL

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

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

Phan tich:
1) Quan ly danh muc -> category
	- id: int primary key -> identity(1,1)
	- name -> nvarchar(150)
2) Quan ly do duong -> product
	- id: int primary key -> identity(1,1)
	- title nvarchar(150)
	- price
	- thumbnail -> nvarchar(500)
	- description  -> ntext
	- created_at datetime
	- updated_at datetime
	- category_id -> int -> foreign key: Category (id)
3) Quan ly nhan vien -> staff
	- id: int primary key -> identity(1,1)
	- name: nvarchar(50)
	- phone: nvarchar(20)
4) Quan ly khach hang -> customer
	- id: int primary key -> identity(1,1)
	- name: nvarchar(50)
	- phone: nvarchar(20)
5) Quan ly don hang:
	Orders
		- id: int primary key -> identity(1,1)
		- staff_id -> int -> foreign key staff(id)
		- customer_id -> int -> foreign key customer(id)
		- order_date -> datetime
		- total_price -> int

	OrderDetail
		- order_id
		- product_id
		- num
		- price

-- Tao database
create database BT1815
go

-- Kich hoat CSDL
use BT1815
go

-- Create 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(200),
	thumbnail nvarchar(500),
	description text,
	price float,
	id_cat int references Category (id)
)
go

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

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

create table Orders (
	id int primary key identity(1,1),
	staff_id int references Staff(id),
	customer_id int references Customer(id),
	total_price float,
	order_date datetime,
	note nvarchar(500)
)
go

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

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

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)
go

select * from Product

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

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

insert into Orders(staff_id, customer_id, total_price, order_date, note)
values
(1, 1, 96000, '2021-02-26', ''),
(1, 1, 32000, '2021-02-25', ''),
(1, 1, 74000, '2021-02-27', '')
go

insert into OrderDetail(order_id, product_id, number, price, total_price)
values
(1, 1, 2, 32000, 64000),
(1, 2, 1, 32000, 32000),
(2, 1, 1, 32000, 32000),
(3, 1, 1, 32000, 32000),
(3, 5, 1, 42000, 42000)
go

-- Query
-- category id (category), name (category), title (product), price (product)
select Category.id, Category.name, Product.title, Product.price
from Category left join Product on Category.id = Product.id_cat
where Category.id = 1
go

create proc proc_show_product_by_category
	@idCat int
as
begin
	select Category.id, Category.name, Product.title, Product.price
	from Category left join Product on Category.id = Product.id_cat
	where Category.id = @idCat
end

exec proc_show_product_by_category 1
exec proc_show_product_by_category 2
go

-- category id (Category), category name (Category), product name (Product), price (OrderDetail), num (OrderDetail), total money (OrderDetail)
select Category.id 'Category ID', Category.name 'Category Name', Product.title 'Product Title',
	OrderDetail.price, OrderDetail.number, OrderDetail.total_price
from Category left join Product on Category.id = Product.id_cat
	join OrderDetail on Product.id = OrderDetail.product_id
where Category.id = 1
go

create proc proc_view_product_in_order_by_category
	@idCat int
as
begin
	select Category.id 'Category ID', Category.name 'Category Name', Product.title 'Product Title',
		OrderDetail.price, OrderDetail.number, OrderDetail.total_price
	from Category left join Product on Category.id = Product.id_cat
		join OrderDetail on Product.id = OrderDetail.product_id
	where Category.id = @idCat
end

exec proc_view_product_in_order_by_category 1

-- KH
select Customer.id, Customer.fullname, Customer.phone_number,
	Orders.order_date, Orders.total_price
from Customer join Orders on Customer.id = Orders.customer_id
where Customer.id = 1
go

create proc proc_benefit
	@startDate date,
	@endDate date,
	@money money output
as
begin
	select * from Orders
	where order_date between @startDate and @endDate

	select @money = sum(Orders.total_price) from Orders
	where order_date between @startDate and @endDate
end

declare @money money
exec proc_benefit '2000-01-01', '2023-01-01', @money = @money output
print N'Tong tien = ' + convert(nvarchar, @money)
go

Tags:



Phản hồi từ học viên

5

(Dựa trên đánh giá ngày hôm nay)

Đăng nhập để làm bài kiểm tra

Chưa có kết quả nào trước đó