2021-02-27 10:07:08

Phân tích database
- Quản lý danh mục sản phẩm: Category
	id -> int -> primary key -> identity(1,1)
	name -> nvarchar(50)
- Quản lý menu đồ uống: Yêu cầu nghiệp hoặc tham khảo website tượng tự -> xem các thuộc tính cần thiết cho mục này => Product
	id -> int -> primary key -> identity(1,1)
	title -> nvarchar(200)
	thumbnail -> nvarchar(500)
	description -> text
	price -> float
	id_cat -> 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
	address -> nvarchar(200)
	email -> nvarchar(200)
- Quản lý khách hàng: Customer
	id -> int -> primary key -> identity(1,1)
	fullname -> nvarchar(50)
	birthday -> date
	phone_number -> nvarchar(20)
	email -> nvarchar(200)
	address -> nvarchar(200)

	('Tran Van A', '1999-01-01', '123456789', 'a@gmail.com', 'HN')

- Quản lý đơn hàng: Nhân viên đã order, khách hang nào, mỗi đơn hàng sẽ order được nhiều sản phẩm.
	Nhân a, khách hàng b mua 2 sản phẩm cafe nóng, sinh tố, chi phí tiền nong.
	Table: Order
		id -> int -> primary key -> identity(1,1)
		staff_id -> int -> Staff (id)
		customer_id -> int -> Customer (id)
		total_price -> float
		order_date -> date
		note -> varchar(500)
	Table: OrderDetail -> lưu thông tin sản phẩm mua
		id -> int -> primary key -> identity(1,1)
		order_id -> int -> Order (id)
		product_id -> int -> Product (id)
		number -> int
		price -> float
		total_price -> float

	# Nhân viên A -> 1
	# Khách hàng B -> 2
	# 	T1 (1) -> 20000 -> 2
	# 	T2 (2) -> 10000 -> 3
	# 
	# Order
	# 	1 1 2 70000
	# OrderDetail
	# 	1 1 2 20000 40000 1
	# 	2 2 3 10000 30000 1

2021-02-27 08:18:08

-- Create database: quan_ly_cafe
create database quan_ly_cafe

-- Active database
use quan_ly_cafe

-- Create tables
create table Category (
	id int primary key identity(1,1),
	name nvarchar(50) not null
)

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

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

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

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,  -- Sai -> thua -> Muc dich nghiep total_price -> xu ly query simple.
	order_date datetime,
	note nvarchar(500)
)

create table OrderDetail (
	id int primary key identity(1,1),
	product_id int references Product(id),
	number int,
	price float,
	total_price float, -- Sai -> thua -> Muc dich nghiep total_price -> xu ly query simple.
	order_id int references Orders(id)
)

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

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)

select * from Product

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

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

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

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)

-- Query & Proc
select Product.id, Product.title, Product.price, Category.name
from Product left join Category on Product.id_cat = Category.id
where Category.id = 1

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

exec proc_view_product_by_category 2

-- Order: query & store (proc)
select Product.id, Product.title, Category.name, OrderDetail.number, OrderDetail.price, OrderDetail.total_price
from Product left join Category on Product.id_cat = Category.id
	left join OrderDetail on OrderDetail.product_id = Product.id
where OrderDetail.order_id = 1

create proc proc_products_by_order
	@orderId int
as
begin
	select Product.id, Product.title, Category.name, OrderDetail.number, OrderDetail.price, OrderDetail.total_price
	from Product left join Category on Product.id_cat = Category.id
		left join OrderDetail on OrderDetail.product_id = Product.id
	where OrderDetail.order_id = @orderId
end

exec proc_products_by_order 1

-- Query: Xem thong tin don hang -> 1 khach hang da mua -> Customer, Order
select Customer.id, Customer.fullname, Customer.address, Staff.fullname, Orders.total_price, Orders.order_date
from Orders left join Customer on Orders.customer_id = Customer.id
	left join Staff on Staff.id = Orders.staff_id
where Customer.id = 1

create proc proc_view_order_by_customer
	@customerId int
as
begin
	select Customer.id, Customer.fullname, Customer.address, Staff.fullname, Orders.total_price, Orders.order_date
	from Orders left join Customer on Orders.customer_id = Customer.id
		left join Staff on Staff.id = Orders.staff_id
	where Customer.id = @customerId
end

exec proc_view_order_by_customer 1

-- Doanh thu
select * from Orders

select sum(total_price) 'Tong Doanh Thu' from Orders
where order_date between '2021-02-25' and '2021-02-26'

create proc proce_money
	@startDate date,
	@endDate date
as
begin
	select sum(total_price) 'Tong Doanh Thu' from Orders
	where order_date between @startDate and @endDate
end

exec proce_money '2021-02-25', '2021-02-27'

2020-12-09 07:32:04

-- Tao database
create database QuanLyCafe

-- Active database
use QuanLyCafe

-- Tao tables
create table category (
	id int primary key identity(1,1),
	name nvarchar(50) not null
)

create table product(
	id int primary key identity(1,1),
	title nvarchar(150),
	price money,
	thumbnail nvarchar(500),
	updated_at datetime,
	created_ad datetime,
	content text,
	id_cat int,
	constraint fk_id_cat foreign key (id_cat) references category(id)
)
create table staff(
	id int primary key identity(1,1),
	fullname nvarchar(100),
	birthday datetime,
	address nvarchar(200)
)
create table customer(
	id int primary key identity(1,1),
	fullname nvarchar(150),
	phone_number nvarchar(30),
	address nvarchar(200)
)
create table orders(
	id int primary key identity(1,1),
	staff_id int,
	constraint fk_staff_id foreign key (staff_id) references staff(id),
	customer_id int,
	constraint fk_customer_id foreign key (customer_id) references customer(id),
	total_price int,
	order_date datetime
)
create table order_detail(
	id int primary key identity(1,1),
	order_id int,
	product_id int,
	constraint fk_order_id foreign key (order_id) references orders(id),
	constraint fk_product_id foreign key (product_id) references product(id),
	amount int,
	price money,
	total_price int
)

-- Nhap du lieu cho tat ca cac tables
insert into category(name)
values
('tea'),
('coffee'),
('bia'),
('juice'),
('coca')

insert into product(title,price,thumbnail,updated_at,created_ad,content,id_cat)
values
('tra sua tran chau',10,'photo01.jpg','2020-10-10','2020-10-20','abcxyz',2),
('coffe da',20,'photo02.jpg','2020-10-10','2020-10-20','abcxyz',1),
('bia ha noi',30,'photo03.jpg','2020-10-10','2020-10-20','abcxyz',2),
('nuoc cam',40,'photo04.jpg','2020-10-10','2020-10-20','abcxyz',3),
('coca cola',50,'photo05.jpg','2020-10-10','2020-10-20','abcxyz',1)

insert into staff(fullname,birthday,address)
values
('Leo Messi','2020-05-05','Argentina'),
('C.Ronaldo','2020-05-05','Portugal'),
('T.V.Lam','2020-05-05','VietNam'),
('A.Grizman','2020-05-05','France'),
('H.Xavi','2020-05-10','Spain')

insert into customer(fullname,phone_number,address)
values
('Tran Van A','1234444','Hanoi'),
('Tran Van B','133243','Hanam'),
('Tran Van C','3223242','ThaiBinh'),
('Tran Van D','3414141','NamDinh'),
('Tran Van E','31213123','HaiPhong')

select * from staff

insert into orders(staff_id,customer_id,total_price,order_date)
values
(2,2,50,'2020-12-11'),
(2,1,60,'2020-12-11'),
(5,4,70,'2020-12-11'),
(3,2,55,'2020-12-11'),
(4,3,60,'2020-12-11')

select * from orders

insert into order_detail(order_id,product_id,amount,price,total_price)
values
(2,2,2,20,40),
(2,1,1,10,10),
(4,2,3,20,60),
(3,2,3,20,60),
(3,1,1,10,10)

-- Liet ke san pham theo 1 danh muc: id, title, price, thumbnail, updated_at, category_name
select * from category
select * from product

select product.id, product.title, product.price, product.thumbnail, product.updated_at, category.name as category_name
from product left join category on product.id_cat = category.id
where category.id = 2

create proc proc_view_product
	@idCat int
as
begin
	select product.id, product.title, product.price, product.thumbnail, product.updated_at, category.name as category_name
	from product left join category on product.id_cat = category.id
	where category.id = @idCat
end

exec proc_view_product 1
exec proc_view_product 2

-- Hien thi thong tin danh muc san pham trong don hang
select * from category
select * from product
select * from orders
select * from order_detail

select product.title, order_detail.amount, order_detail.price, order_detail.total_price
from product left join order_detail on product.id = order_detail.product_id
where order_detail.order_id = 2

create proc proc_view_order_detail
	@orderId int
as
begin
	select product.title, order_detail.amount, order_detail.price, order_detail.total_price
	from product left join order_detail on product.id = order_detail.product_id
	where order_detail.order_id = @orderId
end

exec proc_view_order_detail 2
exec proc_view_order_detail 1

-- xem thong tin chi tiet ve KH: nhan vien ban hang, khach hang, tong tien, ngay mua
select staff.fullname 'Ten Nhan Vien', customer.fullname 'Ten KH', orders.total_price, orders.order_date
from orders left join staff on orders.staff_id = staff.id
	left join customer on orders.customer_id = customer.id
where orders.customer_id = 2

create proc proc_view_order_by_customer
	@customerId int
as
begin
	select staff.fullname 'Ten Nhan Vien', customer.fullname 'Ten KH', orders.total_price, orders.order_date
	from orders left join staff on orders.staff_id = staff.id
		left join customer on orders.customer_id = customer.id
	where orders.customer_id = @customerId
end

exec proc_view_order_by_customer 2

-- Thong ke doanh thu
select sum(total_price) as 'Tong Doanh Thu'
from orders
where order_date >= '2020-12-10'
	and order_date <= '2020-12-30'

create proc proce_view_money
	@startDate date,
	@endDate date
as
begin
	select sum(total_price) as 'Tong Doanh Thu'
	from orders
	where order_date >= @startDate
		and order_date <= @endDate
end

exec proce_view_money '2020-12-10', '2020-12-30'

2020-12-09 07:04:18

create table category(
 id int primary key identity(1,1),
 name nvarchar(50)
)
go

create table product(
 id int primary key identity(1,1),
 title nvarchar(50),
 price money,
 cat_id int references category(id),
 thumnail nvarchar(500),
 update_at datetime,
 create_at datetime,
 content text
)
go

create table staff(
 id int primary key identity(1,1),
 fullname nvarchar(50),
 birth date,
 address nvarchar(500)
)
go

create table customer(
 id int primary key identity(1,1),
 fullname nvarchar(50),
 phone_number nvarchar(20),
 address nvarchar(100)
)
go

create table ordder(
 id int primary key identity(1,1),
 staff_it int foreign key (id) references staff(id),
 customer_id int foreign key (id) references customer(id),
 total_price int,
 order_date date
 )
 go

 create table ordder_detal(
   id int primary key identity(1,1),
   order_id int foreign key (order_id) references ordder(id),
   product_id int foreign key(product_id) references product(id),
   amuont int,
   price int,
   total_price int 
)
go

2020-12-09 06:47:15

create database Quan_Ly_Quan_CONGCafe
use Quan_Ly_Quan_CONGCafe

create table Danh_Muc_Sp (
       id int primary key identity(1,1),
	   name nvarchar(70)
)

create table Do_Uong (
       id int primary key identity(1,1),
	   name nvarchar(50),
       beveragcode nvarchar(50),
	   smell nvarchar(50),
	   price nvarchar(30),
	   size nvarchar(10)
)

create table Nhan_Vien (
       fullname nvarchar(50),
	   rollno nvarchar(100),
	   birthday date,
	   address nvarchar(200),
	   phonenumber nvarchar(20),
	   gender nvarchar(10)
)

create table Quan_ly_Khach_Hang (
       id int primary key identity(1,1),
	   fullname nvarchar (50),
	   phone_number nvarchar(20),
	   address nvarchar(50),
	   gender nvarchar(10)
)

create table Quan_Ly_Don_Hang (
       id int primary key identity(1,1),
	   staff_id int foreign key references staff(id),
	   customer_id int foreign key references customer(id),
	   total_price int(tong so tien cua don hang),
	   order_date datetime
)


insert into Danh_Muc_Sp(name)
values
('Cafe Cộng'),
('Cafe Cộng'),
('Cafe Cộng'),
('Cafe Cộng'),
('Cafe Cộng')

insert into Do_Uong(name,beveragcode,smell,price,size)
values
('Cafe Sữa Đá', '12345', 'Sữa Dừa','35,000 VNĐ', 'L'),
('Cafe Sữa Đá', '12345', 'Sữa Dừa','35,000 VNĐ', 'L'),
('Cafe Sữa Đá', '12345', 'Sữa Dừa','35,000 VNĐ', 'L'),
('Cafe Sữa Đá', '12345', 'Sữa Dừa','35,000 VNĐ', 'L'),
('Cafe Sữa Đá', '12345', 'Sữa Dừa','35,000 VNĐ', 'L')

insert into Nhan_Vien( fullname, rollno, birthday, address, phonenumber, gender)
values
('Nguyễn Anh Vũ', 'T2008A', '2020-12-09', 'Hà Nam', '0964657860', 'Nam'),
('Nguyễn Anh Vũ', 'T2008A', '2020-12-09', 'Hà Nam', '0964657860', 'Nam'),
('Nguyễn Anh Vũ', 'T2008A', '2020-12-09', 'Hà Nam', '0964657860', 'Nam'),
('Nguyễn Anh Vũ', 'T2008A', '2020-12-09', 'Hà Nam', '0964657860', 'Nam'),
('Nguyễn Anh Vũ', 'T2008A', '2020-12-09', 'Hà Nam', '0964657860', 'Nam')

insert into Quan_ly_Khach_Hang(fullname,phone_number,address,gender)
values
('Nguyen Phan Dong', '0911110001', '120 Tran Duy Hung', 'Nu'),
('Nguyen Phan Dong', '0911110001', '120 Tran Duy Hung', 'Nu'),
('Nguyen Phan Dong', '0911110001', '120 Tran Duy Hung', 'Nu'),
('Nguyen Phan Dong', '0911110001', '120 Tran Duy Hung', 'Nu'),
('Nguyen Phan Dong', '0911110001', '120 Tran Duy Hung', 'Nu')

select * from Danh_Muc_Sp
select * from Do_Uong
select * from Nhan_Vien
select * from Quan_ly_Khach_Hang

2020-12-09 06:38:44

create table category(
 id int primary key identity(1,1),
 name nvarchar(50)
)
go

create table product(
 id int primary key identity(1,1),
 title nvarchar(50),
 price money,
 cat_id int references category(id),
 thumnail nvarchar(500),
 update_at datetime,
 create_at datetime,
 content text
)
go

create table staff(
 id int primary key identity(1,1),
 fullname nvarchar(50),
 birth date,
 address nvarchar(500)
)
go

create table customer(
 id int primary key identity(1,1),
 fullname nvarchar(50),
 phone_number nvarchar(20),
 address nvarchar(100)
)
go

create table ordder(
 id int primary key identity(1,1),
 staff_it int foreign key (id) references staff(id),
 customer_id int foreign key (id) references customer(id),
 total_price int,
 order_date date
 )
 go

 create table ordder_detal(
   id int primary key identity(1,1),
   order_id int foreign key (order_id) references ordder(id),
   product_id int foreign key(product_id) references product(id),
   amuont int,
   price int,
   total_price int 
)
go

2020-12-08 15:45:35

create database coffe_vip
use coffe_vip
create table catagory(
id int primary key identity(1,1),
name nvarchar(10)
)
create table product(
id int primary key identity(1,1),
title nvarchar(150),
price money,
thumbnail nvarchar(500),
updated_at datetime,
created_ad datetime,
content text,
id_cat int,
constraint fk_id_cat foreign key (id_cat) references catagory(id)
)
create table staff(
id int primary key identity(1,1),
fullname nvarchar(100),
birthday datetime,
address nvarchar(200)
)
create table customer(
id int primary key identity(1,1),
fullname nvarchar(150),
phone_number nvarchar(30),
address nvarchar(200)
)
create table order_drinks(
id int primary key identity(1,1),
staff_id int,
constraint fk_staff_id foreign key (staff_id) references staff(id),
customer_id int,
constraint fk_customer_id foreign key (customer_id) references customer(id),
total_price int,
order_date datetime
)
create table order_detail(
id int primary key identity(1,1),
order_id int,
product_id int,
constraint fk_order_id foreign key (order_id) references order_drinks(id),
constraint fk_product_id foreign key (product_id) references product(id),
amount int,
price money,
total_price int
)
create index ci_name on catagory(name)
select * from catagory
insert into catagory(name)
values
('teamilk'),
('coffe'),
('bia'),
('juice'),
('coca')
select * from product
insert into product(title,price,thumbnail,updated_at,created_ad,content,id_cat)
values
('tra sua tran chau',10,'uelxxxxx',2020-10-10,2020-10-20,'abcxyz',2),
('coffe da',20,'uelxxxxx',2020-10-10,2020-10-20,'abcxyz',1),
('bia ha noi',30,'uelxxxxx',2020-10-10,2020-10-20,'abcxyz',2),
('nuoc cam',40,'uelxxxxx',2020-10-10,2020-10-20,'abcxyz',3),
('coca cola',50,'uelxxxxx',2020-10-10,2020-10-20,'abcxyz',1)
select * from staff
insert into staff(fullname,birthday,address)
values
('Leo Messi',2020-15-05,'Argentina'),
('C.Ronaldo',2020-15-05,'Portugal'),
('T.V.Lam',2020-15-05,'VietNam'),
('A.Grizman',2020-15-05,'France'),
('H.Xavi',2020-15-0,'Spain')
select * from customer
insert into customer(fullname,phone_number,address)
values
('Tran Van A','1234444','Hanoi'),
('Tran Van B','133243','Hanam'),
('Tran Van C','3223242','ThaiBinh'),
('Tran Van D','3414141','NamDinh'),
('Tran Van E','31213123','HaiPhong')
select * from order_drinks
insert into order_drinks(staff_id,customer_id,total_price,order_date)
values
(1,2,50,2020-12-11),
(1,1,60,2020-12-11),
(2,4,70,2020-12-11),
(3,2,55,2020-12-11),
(4,3,60,2020-12-11)
select * from order_detail
insert into order_detail(order_id,product_id,amount,price,total_price)
values
(1,2,2,20,40),
(1,1,1,10,10),
(4,2,3,20,60),
(2,2,3,20,60),
(2,1,1,10,10)
select catagory.name,product.title,product.price,product.id_cat
from catagory,product
where catagory.id = product.id_cat
     and catagory.name = 'coffe'
create proc lam_dz
as
begin
select catagory.name,product.title,product.price,product.id_cat
from catagory,product
where catagory.id = product.id_cat
     and catagory.name = 'coffe'
end
exec lam_dz
select * from product
select catagory.id,product.title,product.price,product.thumbnail,product.updated_at,product.created_ad,product.content,product.id_cat
from catagory,product
where catagory.id = product.id_cat
    and product.title = 'nuoc cam'
create proc lam_pro
as
begin
select catagory.id,product.title,product.price,product.thumbnail,product.updated_at,product.created_ad,product.content,product.id_cat
from catagory,product
where catagory.id = product.id_cat
    and product.title = 'nuoc cam'
end
exec lam_pr

2020-12-07 10:14:27

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(
	id int primary key identity(1,1) not null,
	nhanvien_id int references nhanvien(nhanvien_id),
	customer_id int references customer(customer_id),
	donhang_price money,
	donhang_date datetime
)

insert into donhang(nhanvien_id, customer_id, donhang_price, donhang_date)
values
(5, 2, 120000, '2020-12-07 12:30'),
(2, 4, 60000, '2020-12-03 17:40'),
(3, 1, 40000, '2020-12-05 20:37'),
(1, 5, 100000, '2020-12-07 12:00'),
(4, 3, 200000, '2020-12-07 09:30')

select * from donhang

create table order_detail(
	donhang_id int references donhang(id),
	category_id int references category(category_id),
	drink_id int references drink(drink_id),
	donhang_quantity int,
	price money
)

insert into order_detail(donhang_id, category_id, drink_id, donhang_quantity, price)
values
(1, 1, 3, 2, 120000),
(2, 5, 2, 1, 60000),
(3, 3, 4, 1, 40000),
(4, 2, 1, 1, 50000),
(4, 4, 5, 1, 50000),
(5, 2, 1, 1, 50000),
(5, 4, 5, 1, 40000),
(5, 3, 4, 1, 60000),
(5, 4, 5, 1, 50000)

select * from order_detail

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 order_detail.donhang_id, category.category_name, drink.drink_name, drink.drink_price, order_detail.donhang_quantity, order_detail.price
	FROM category, drink, order_detail
	WHERE category.category_id=drink.category_id and drink.drink_id=order_detail.drink_id
	GROUP BY order_detail.donhang_id, category.category_name, drink.drink_name, drink.drink_price, order_detail.donhang_quantity, order_detail.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, order_detail.donhang_quantity, order_detail.price
	FROM category, drink, donhang, customer, order_detail
	WHERE category.category_id=drink.category_id and drink.drink_id=order_detail.drink_id 
		and customer.customer_id=donhang.customer_id and order_detail.donhang_id=donhang.id 
		and donhang.customer_id=@customerid
	GROUP BY donhang.customer_id, customer.customer_name, category.category_name, drink.drink_name, drink.drink_price, order_detail.donhang_quantity, order_detail.price
END

EXEC proc_view_order_info2 3

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'

2020-12-07 09:42:31

- Bang danh muc san pham: category
	- id -> int -> primary key -> identity(1,1)
	- name -> nvarchar(50)

- Bang san pham: product
	- id -> int -> primary key -> identity(1,1)
	- title -> nvarchar(150)
	- price -> money
	- cat_id -> int -> references category(id)
	- num -> int
	- thumbnail -> nvarchar(500) -> link hinh anh
	- updated_at -> datetime
	- created_at -> datetime
	- content text

- Quan ly nhan vien: staff
	- id -> int -> primary key -> identity(1,1)
	- fullname -> nvarchar(50)
	- birthday -> date
	- address -> nvarchar(200)

- Quan ly khac hang: customer
	- id -> int -> primary key -> identity(1,1)
	- fullname -> nvarchar(50)
	- phone_number -> nvarchar(20)
	- address -> nvarchar(200)

- Quan ly don hang:
	- Order
		- id -> int -> primary key -> identity(1,1)
		- staff_id -> int -> foreign key -> references staff (id)
		- customer_id -> int -> foreign key -> references customer (id)
		- total_price -> int (tong so tien cua don hang)
		- order_date: datetime

	- order_detail
		- id -> int -> primary key -> identity(1,1)
		- order_id -> int -> foreign key -> references order (id)
		- product_id -> id -> foreign key -> references product (id)
		- amount
		- price
		- total_price

2020-12-07 09:03:16

--tao database :Quanlycafe
create database Quanlycafe
go
-- kich hoat database : Quanlycafe
use Quanlycafe
go
--Tao bang category
create table category (
   id int primary key indentity(1,1),
   name nvarchar(50) not null
)
go
create table product(
  id int primary key indentity(1,1),
  title nvachar(50) not null
  description text,
  price float,
  category_id  int references category(id)
)
go
create table staff (
  id int primary key indentity(1,1),
  full name  nvarchar(50) not null
  address nvarchar(200),
  gender nvarchar(20),
  birthday	data,
  phone_number  nvarchar (20)
)
go
create table customer(
   id int primary key indentity(1,1),
   full  name nvarchar(50) not null
   phone_ number nvarchar(150),
   email nvarchar(50)
)
go
 create table order(
     id int primary key indentity(1,1)
     customer_id int references customer (id),
	 staff_id int references staff(id),
	 price _total float
)
go
create table orderdetail(
   id int  primary key indentity (1,1),
   product_id int refere product (id),
   price float,
   num int,
   price_total float,
   order_id int refences orders (id)
)
go
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 nong' 52000, 3),
('cafe da'45000,2),
('nuoc ep oi ' 50000, 2),
('nuoc ep cam'50000,2),
('nuoc ep nho'70000,4),
go
insert into staff(fullname, gender , birthday, phone_number, address)
values
('tran van son','nam','22-03-1987','0366908296','ha noi'),
('nguyen dinh quan','nam','12-06-1895','066675889',' hai phong'),
('pham cong minh',' nam ','15-06-1975','0366908293','ha noi')
go
insert into customer (fullname, email, phone_number)
values
('la thi A','A@gmail.com','0366908296'),
('phan van B','b@gamil.com','0366908296'),
('nguyen thi C',' C@gmail.com','0366908296'),
('nguyen dinh D','D@gmail.com','0366908296quan'),
go
insert into orders( customer_id , staff_id,price_total)
values
(1,1,96000)
go
update orders set  created at='2020-08-02' where id =1
insert into orders( customer_id , staff_id,num ,price_total)
values
(1,1,32000 ,3,96000)

insert into orders( customer_id , staff_id,price_total , created_at)
values
(1,1,183000,'2020-08-03')
go
insert into orders( customer_id , staff_id,num ,price_total)
values
(3,1,32000 ,3,96000),
(2,4,80000,2,320000)
go 
select category , name categoryname,