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)