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

Thiết kế CSDL phục vụ trong việc quản lý quán cafe. Yêu cầu đề bài như sau

- Quản lý được danh mục sản phâm của quán cafe

- Quản lý loại đồ uống trong quán cafe

- Quản lý nhân viên của hàng

- Quản lý đơn hàng -> mỗi đơn hàng được nhập vào bởi một nhân viên (Nghĩa là có thể xem đc đơn hàng này do nhân viên nào nhập) - Chú ý : Mỗi đơn hàng có thể có nhiều loại đồ uống

- Quản lý được khách hàng tới của hàng -> Đơn hàng sẽ mapping được vs khách hàng đã đặt đồ uống.

Yêu cầu nghiệp vụ

- Thiết kế CSDL & Nhập dữ liệu cho mỗi bảng 5 bản ghi

- Đánh index trên ít nhất 1 bảng. Tự chọn cột để đánh index

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

- 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

- Hiển thị danh mục các đơn hàng theo mã KH.

- 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.

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

5

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

Nguyễn Tuấn Hùng

2020-12-07 08:02:00

#BT Test.sql

use QuanLyQuanCafe

create table SanPham (
	IDSP int primary key identity(1,1),
	TenSP nvarchar(Max) not null,
	DonGia int not null,
	LoaiSP nvarchar(max) not null,
	TonKho int null
)

create table Staff (
	IDNV int primary key not null identity(1,1),
	TenNV nvarchar(100) not null,
	QueQuan nvarchar(100) not null,
	SoDT int not null,
	SoNgayLam int null,
	SoNgayNghi int null
)

create table Older (
	IDDH int primary key not null identity(1,1),
	TenNV nvarchar(100) not null,
	TenKhachHang nvarchar(100) not null,
	TenSP nvarchar(max) not null,
	SoLuong int not null,
	DonGia int not null,
	ThanhTien int not null
)

create table KhachHang (
	IDKH int primary key identity(1,1) not null,
	TenKH nvarchar(100) not null,
	DD int not null
)

insert into SanPham(TenSP, DonGia, LoaiSP, TonKho)
values
('Cafe Sua', 27000, 'Cafe', 50),
('Cafe Phin', 35000, 'Cafe', 30),
('Tra Sua Matcha', 15000, 'TraSua', 150),
('Coca', 12000, 'NuocNgot', 200),
('Nuoc Dua', 20000, 'NuocHoaQUa', 20)

insert into Staff(TenNV, QueQuan, SoDT, SoNgayLam, SoNgayNghi)
values
('Nguyen Van A', 'Ha Noi', 1233214566547, 27, 0),
('Nguyen Van B', 'Bac Giang', 1233214566548, 24, 3),
('Nguyen Thi C', 'Ha Noi', 1233214566549, 25, 2),
('Nguyen Van D', 'Quang Ninh', 1233214566540, 27, 0),
('Nguyen Van E', 'Ha Noi', 1233214566541, 26, 1)

insert into Older(TenNV, TenKhachHang, TenSP, SoLuong, DonGia, ThanhTien)
values
('Nguyen Van A', 'Cafe Sua', 3, 27000, 81000),
('Nguyen Van B', 'Cafe Phin', 1, 35000, 35000),
('Nguyen Thi C', 'Tra Sua Matcha', 2, 15000, 30000),
('Nguyen Van D', 'Coca', 6, 12000, 72000),
('Nguyen Van E', 'Nuoc Dua', 1, 20000, 20000)

insert into KhachHang(TenKH, DD)
values
('Dang Van A', 123456789123),
('Dang Van B', 123456789124),
('Dang Van C', 123456789125),
('Dang Van D', 123456789126),
('Dang Van E', 123456789127)

Nguyễn Hữu Hiếu

2020-12-07 08:01:24

create database quanLyQuanCafe
use quanLyQuanCafe
create table danhMucSP (
	masp int primary key identity(1,1),
	tensp nvarchar(50),
	dongia float,
	soluong int
)
create table danhMucDoUong (
	madouong int primary key identity(1,1),
	tendouong nvarchar(50),
	dongia float,
)
create table nhanVien (
	manhanvien nvarchar(20) primary key,
	tennhanvien nvarchar(50),
	sodienthoai nvarchar(20),
	thoigianlam datetime,
	luong float,
	chucvu nvarchar(50)
)
drop table nhanVien
create table donHang (
	id_donhang int primary key identity(1,1),
	id_KH int references khachHang(maKH),
	id_manhanvien nvarchar(20) references nhanVien(manhanvien),
	id_madouong int references danhMucDoUong(madouong),
	numdouong int, 
	id_masp int references danhMucSP(masp),
	numsp int,
	thoigianbatdau datetime,
	thoigianketthuc datetime
)
create table khachHang(
	maKH int identity(1,1) primary key,
	tenKH nvarchar(50),
	phone nvarchar(20),
	id_sp int references danhMucSP(masp),
	soluongsp int,
	id_douong int references danhMucDoUong(madouong),
	soluongdouong int,
	giodat datetime,
	bandat nvarchar(10),
	ghichu nvarchar(500)
)
insert into danhMucSP (tensp, dongia, soluong)
values
('Cupcake', 25000, 20),
('Banh bong lan', 35000, 11),
('Icecream', 12000, 16),
('Dasani', 10000, 100),
('Banh my', 220000, 5)

select * from danhMucSP
select * from nhanVien
select * from donHang

insert into danhMucDoUong (tendouong, dongia)
values
('Cafe', 25000),
('Nuoc dua', 40000),
('Tra sen vang', 55000),
('Dimald', 15000),
('Admad', 30000)

insert into khachHang (tenKH, phone, id_sp, soluongsp, id_douong, soluongdouong, giodat, bandat, ghichu)
values
('Nguyen Phan Dong', '0912099111', 2, 1, 1, 2, '2020-09-12 15:02:22', 'A2', 'No tien cu chua tra'),
('Bui Tien Dung', '0912345111', 3, 2, 3, 1, '2020-09-11 11:03:22', 'A4', 'Khach quen'),
('Nguyen Thi Hien', '23543424', 4, 1, 4, 2, '2020-09-12 15:05:22', 'A1', 'Khach thich view dep'),
('Tran Van A', '0912096578', 2, 3, 2, 4, '2020-09-12 15:02:22', 'A3', 'Khach tri thuc'),
('Pham Hoang Hung', '0967243111', 5, 1, 2, 2, '2020-08-10 15:02:22', 'A3', 'Khach nuoi cho')

insert into nhanVien (manhanvien,	tennhanvien,	sodienthoai,	thoigianlam,	luong,	chucvu)
values
(1, 'Nguyen Van A', '0912777998', '2020-09-12 15:02:22', 3000000, 'Nhan vien pha che'),
(2, 'Nguyen Van B', '0912777998', '2020-09-12 15:02:22', 4000000, 'Nhan vien pha che'),
(3, 'Nguyen Van C', '0912777998', '2020-09-12 15:02:22', 5000000, 'Manager'),
(4, 'Nguyen Van D', '0912777998', '2020-09-12 15:02:22', 3000000, 'Bao ve'),
(5, 'Nguyen Van E', '0912777998', '2020-09-12 15:02:22', 4000000, 'Nhan vien don dep')



insert into donHang (id_KH, id_manhanvien, id_madouong, numdouong, id_masp, numsp, thoigianbatdau, thoigianketthuc)
values
(3, 2, 2, 3, 2, 2, '2020-09-12 15:02:22', '2020-09-12 17:02:22'),
(3, 1, 3, 2, 3, 3, '2020-09-12 15:02:22', '2020-09-12 17:02:22'),
(4, 3, 5, 3, 2, 2, '2020-09-12 15:02:22', '2020-09-12 17:02:22'),
(5, 1, 3, 5, 4, 2, '2020-09-12 15:02:22', '2020-09-12 17:02:22'),
(7, 4, 2, 1, 5, 4, '2020-09-12 15:02:22', '2020-09-12 17:02:22')

hainguyen

2020-12-07 08:01:06

create database QuaanLyQuanCaphe

use QuaanLyQuanCaphe

create table product (
	sanpham_id int primary key identity (1,1),
	name nvarchar(50),
	price float
)

create table drinks (
	drink_id int primary key identity (1,1),
	drink_name nvarchar(50),
	price float
)

create table nhanvien (
	Ma_nhanvien int identity (1,1),
	gender nvarchar(20),
	age int,
	name nvarchar(50)
)

create table bill (
	bill_id int primary key identity (1,1),
	name nvarchar(50),
	Ma_nhanvien int,
	price float,
	soluong int
)

create table khachhang (
	khachhang_id int primary key identity (1,1),
	name nvarchar(50),
	bill_id int
)

alter table nhanvien
add constraint TB_nhanvien primary key (Ma_nhanvien)

alter table khachhang
add constraint LK_khachhang_bill foreign key (bill_id) references bill(bill_id)

alter table bill
add constraint LK_bill_nhanvien foreign key (Ma_nhanvien) references nhanvien(Ma_nhanvien)

alter table drinks
add sanpham_id int

alter table drinks
add constraint LK_drinks_product foreign key (sanpham_id)  references product(sanpham_id)

insert into product(name, price)
values
('Ca Phe', 10),
('Juice', 15),
('Milk Tea', 20),
('Tra Chanh', 10),
('Huong Duong', 5)

insert into drinks(drink_name, price)
values
('Ca Phe Den', 10),
('Ca Phe Nau', 10),
('Hong Tra Sua', 15),
('Tra Sua Olong', 15),
('Nuoc Cam', 12)

insert into nhanvien(name, age, gender)
values
('Tran Van Diep', 30, 'Nam'),
('Tran Thi Diep', 29, 'Nu'),
('Tran Van A', 25, 'Gay'),
('Tran Van B', 19, 'Nam'),
('Lung Thi Linh', 25, 'Nu')

insert into bill(name, soluong, price, Ma_nhanvien)
values
('bill_1', 1, '', 1),
('bill_2', 1, '', 2),
('bill_3', 2, '', 3),
('bill_4', 3, '', 4),
('bill_5', 2, '', 5)

insert into khachhang(name, bill_id)
values
('Nguyen Thi Diep', 1),
('Nguyen Thi Diep Em', 2),
('Tran Van A', 3),
('Hiep Xong Che', 4),
('V', 5)

select * from product
select * from drinks
select * from nhanvien
select * from bill
select * from khachhang

create index CI_Sanpham on product(name)

create proc PK_product 
as
begin
	select product.sanpham_id, product.name, drinks.drink_name, drinks.price
	from product, drinks
	where product.sanpham_id = drinks.sanpham_id
end

exec PK_product

create proc PK_bill
as
begin
	select bill.bill_id, bill.name, bill.soluong, drinks.drink_name, drinks.price
	from 
end

Trần Văn Lâm

2020-12-07 08:00:58

create database quan_li_cafe
use quan_li_cafe 
create table CategoryProduct(
	id int primary key identity(1,1),
	name nvarchar(100)
)
create table drinks(
	id int primary key identity(1,1),
	title nvarchar(100),
	thumbnail nvarchar(500),
	content ntext,
	price money,
	num int,
	started_at datetime,
	end_at datetime,
	id_drinks int,
	constraint fk_id_drinks foreign key (id_drinks) references CategoryProduct(id)
)
create table Staff(
	id int primary key identity(1,1),
	fullname nvarchar(100),
	age int,
	address nvarchar(200),
	phoneNumber nvarchar(30)
)
select * from CategoryProduct
insert into CategoryProduct(name)
values
('Do uong co gas'),
('Coffe'),
('Nuoc ep'),
('Ruou'),
('Bia')
select * from drinks
insert into drinks(title,thumbnail,content,price,num,started_at,end_at,id_drinks)
values
('orange juice','urlxxxx','abcxyz',10,3,2020-10-11,2020-11-11,3),
('milk coffe','urlxxxx','abcxyz',20,2,2020-10-11,2020-11-11,2),
('Chivas','urlxxxx','abcxyz',30,3,2020-10-11,2020-11-11,4),
('coca cola','urlxxxx','abcxyz',15,3,2020-10-11,2020-11-11,1),
('apple juice','urlxxxx','abcxyz',25,3,2020-10-11,2020-11-11,2)
select * from Staff
insert into Staff(fullname,age,address,phoneNumber)
values
('Leo Messi',33,'Argentina','123456789'),
('Lam Dep Trai',18,'VietNam','123456789'),
('C.Ronaldo',35,'Portugal','123456789'),
('A.Grizman',28,'Fance','123456789'),
('Yasuo',25,'VietNam','123456789')

alter table Staff
	add constraint fk_id foreign key (id) references drinks(id)
create index Ci_phoneNumber on Staff(phoneNumber)
select CategoryProduct.name,drinks.title
from CategoryProduct,drinks
where CategoryProduct.id = drinks.id
create Proc loai_do_uong
as
begin
	select CategoryProduct.name,drinks.title
    from CategoryProduct,drinks
    where CategoryProduct.id = drinks.id
end
exec loai_do_uong
select CategoryProduct.name,drinks.title,drinks.thumbnail,drinks.content,drinks.price,drinks.num,drinks.started_at,drinks.end_at,drinks.id_drinks
from CategoryProduct,drinks
where drinks.title = 'coca cola'
     and CategoryProduct.id = drinks.id
select CategoryProduct.name,drinks.title,drinks.thumbnail,drinks.content,drinks.price,drinks.num,drinks.started_at,drinks.end_at,drinks.id_drinks
from CategoryProduct,drinks
where CategoryProduct.id = drinks.id

Nguyễn Xuân Mai

2020-12-07 08:00:50

create database quanlyquancafe
use quanlyquancafe

create table category(
	category_id int primary key identity(1,1) not null,
	category_name nvarchar(50)
)

insert into category(category_name)
values
('Coffee'),
('Smoothie'),
('Tea'),
('Ice blended'),
('Vietnamese Coffee')

select * from category

create table nhanvien(
	nhanvien_id int primary key identity(1,1) not null,
	nhanvien_name nvarchar(100),
	nhanvien_birthday date,
	nhanvien_gender nvarchar(50)
)

insert into nhanvien(nhanvien_name, nhanvien_birthday, nhanvien_gender)
values
('Nguyen A', '1997-02-12', 'Nam'),
('Nguyen B', '1999-12-07', 'Nam'),
('Nguyen C', '1997-02-14', 'Nam'),
('Nguyen D', '2000-10-10', 'Nu'),
('Nguyen E', '1998-11-05', 'Nu')

select * from nhanvien

create table drink(
	drink_id int primary key identity(1,1) not null,
	drink_name nvarchar(50),
	drink_price money,
	category_id int references category(category_id)
)

insert into drink(drink_name, drink_price, category_id)
values
('Banana Smoothie', '50000', '2'),
('Bac Siu', '60000', '5'),
('Americano', '60000', '1'),
('Earl Grey', '40000', '3'),
('Matcha Ice Blend', '50000', '4')

select * from drink

create table customer(
	customer_id int primary key identity(1,1) not null,
	customer_name nvarchar(100),
	customer_birthday date,
	customer_gender nvarchar(50)
)

insert into customer(customer_name, customer_birthday, customer_gender)
values
('Pham Hoang Minh', '2002-03-04', 'Nam'),
('Bach Bao Chau', '2003-11-22', 'Nu'),
('Phan Vu', '2004-06-05', 'Nam'),
('Ly Ngoc Khue', '2004-11-25', 'Nu'),
('Tran Ngan Giang', '2003-10-28', 'Nu')

select * from customer

create table donhang(
	donhang_id int primary key identity(1,1) not null,
	category_id int references category(category_id),
	drink_id int references drink(drink_id),
	nhanvien_id int references nhanvien(nhanvien_id),
	customer_id int references customer(customer_id),
	donhang_quantity int,
	donhang_price money,
	donhang_date date
)

insert into donhang(category_id, drink_id, nhanvien_id, customer_id, donhang_quantity, donhang_price, donhang_date)
values
(1, 3, 5, 2, 2, 120000, '2020-12-07'),
(5, 2, 2, 4, 1, 60000, '2020-12-03'),
(3, 4, 3, 1, 1, 40000, '2020-12-05'),
(2, 1, 1, 5, 2, 100000, '2020-12-07'),
(4, 5, 4, 3, 4, 200000, '2020-12-07')

select * from donhang

create nonclustered index CI_category_name on category(category_name)

select category.category_name, drink.drink_id, drink.drink_name, drink.drink_price
from category, drink
where category.category_id=drink.category_id and category.category_name = 'Coffee'
group by category.category_name, drink.drink_id, drink.drink_name, drink.drink_price

CREATE PROC proc_view_drink_info
	@categoryname nvarchar(50)
AS
BEGIN
	SELECT category.category_name, drink.drink_id, drink.drink_name, drink.drink_price, drink.category_id
	FROM category, drink
	WHERE category.category_id=drink.category_id
		AND category.category_name = @categoryname
	GROUP BY category.category_name, drink.drink_id, drink.drink_name, drink.drink_price, drink.category_id
END

EXEC proc_view_drink_info 'Smoothie'

CREATE PROC proc_view_order_info
AS
BEGIN
	SELECT category.category_name, drink.drink_name, drink.drink_price, donhang.donhang_quantity, donhang.donhang_price
	FROM category, drink, donhang
	WHERE category.category_id=drink.category_id and drink.drink_id=donhang.drink_id
	GROUP BY category.category_name, drink.drink_name, drink.drink_price, donhang.donhang_quantity, donhang.donhang_price
END

EXEC proc_view_order_info

CREATE PROC proc_view_order_info2
	@customerid int
AS
BEGIN
	SELECT donhang.customer_id, customer.customer_name, category.category_name, drink.drink_name, drink.drink_price, donhang.donhang_quantity, donhang.donhang_price
	FROM category, drink, donhang, customer
	WHERE category.category_id=drink.category_id and drink.drink_id=donhang.drink_id and customer.customer_id=donhang.customer_id and donhang.customer_id=@customerid
	GROUP BY donhang.customer_id, customer.customer_name, category.category_name, drink.drink_name, drink.drink_price, donhang.donhang_quantity, donhang.donhang_price
END

EXEC proc_view_order_info2 4

CREATE PROC proc_view_income_info
	@date1 date, @date2 date
AS
BEGIN
	SELECT sum(donhang.donhang_price) as 'Income'
	FROM donhang
	WHERE donhang.donhang_date between @date1 and @date2
END

EXEC proc_view_income_info '2020-12-01', '2020-12-31'