2020-12-07 08:24:52

create database quanlycafe
use quanlycafe
create table product(
id int primary key identity(1,1),
food nvarchar(100),
Ban int,
foodCategory nvarchar(100),
account nvarchar(100),
bill nvarchar(100),
)
create table Douong(
id int primary key identity(1,1),
beveragecode nvarchar(100),
smell nvarchar(100),
price nvarchar(100),
bill nvarchar(100),
size nvarchar(50)
)
create table qlNhanVien(
id int primary key identity(1,1),
StaffName nvarchar(100),
BirthDate datetime,
rollno nvarchar(100),
address nvarchar(100),
phonenumber nvarchar(20),
gerden nvarchar(100)
)
insert into product(food, Ban, foodCategory, account, bill)
values
('thit cho', '1', 'rau muong', '1013224095', '500.000VNĐ'),
('thit de', '2', 'rau cai', '1013224095', '900.000VNĐ'),
('thit ho', '3', 'rau xanh', '1013224095', '700.000VNĐ'),
('thit lon', '4', 'rau mung toi', '1013224095', '800.000VNĐ'),
('thit meo', '5', 'rau ngot', '1013224095', '1.100.000VNĐ')
select * from product
insert into Douong(beveragecode, smell, price, bill, size)
values
('506', 'dau', '20.000VNĐ','0', 'M'),
('507', 'socola', '25.000VNĐ','0', 'L'),
('508', 'dua', '30.000VNĐ','0', 'L'),
('504', 'matcha', '60.000VNĐ','0', 'M'),
('509', 'cafe', '40.000VNĐ','0', 'L')
select * from Douong
insert into qlNhanVien(StaffName, rollno, address, phonenumber, gerden)
values
('Bui Van A', '2002', 'Ha Noi', '0915094500', 'nam'),
('Bui Van B', '2002', 'Ha Noi', '0915094500', 'nu'),
('Bui Van C', '2002', 'Ha Noi', '0915094500', 'nu'),
('Bui Van D', '2002', 'Ha Noi', '0915094500', 'nu'),
('Bui Van G', '2002', 'Ha Noi', '0915094500', 'nam')
select * from qlNhanVien

2020-12-07 08:23:26

create database QuanLiCafe
use QuanLiCafe

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

insert into category(category_name)
values
('Ca Phe'),
('Sinh To'),
('Tra'),
('Da Xay'),
('Cacao')

select * from category

create table nhanvien(
	nv_id int primary key identity(1,1) not null,
	nv_name nvarchar(200),
	nv_birthday date,
	nv_gender nvarchar(200)
)

insert into nhanvien(nv_name, nv_birthday, nv_gender)
values
('aa', '1991-02-12', 'Nam'),
('bb', '1992-12-07', 'Nu'),
('cc', '1993-02-14', 'Nam'),
('dd', '2004-10-10', 'Nam'),
('ee', '1995-11-05', 'Nu')

select * from nhanvien

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

insert into drink(drink_name, drink_price, category_id)
values
('abc', '50000', '2'),
('abcabc', '60000', '5'),
('aaaaaa', '60000', '1'),
('bbbbbb', '40000', '3'),
('bcabca', '50000', '4')

select * from drink

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

insert into customer(customer_name, customer_birthday, customer_gender)
values
('aaaa', '2003-03-14', 'Nam'),
('bbbb', '2003-12-22', 'Nu'),
('cccc', '2003-03-05', 'Nu'),
('dddd', '2005-11-25', 'Nu'),
('eeee', '2006-04-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(nv_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, 160000, '2020-02-07'),
(5, 2, 2, 4, 1, 64000, '2020-03-03'),
(3, 4, 3, 1, 1, 47000, '2020-06-07'),
(2, 1, 1, 5, 2, 1220000, '2020-09-17'),
(4, 5, 4, 3, 4, 2022000, '2020-12-17')

select * from donhang

2020-12-07 08:12:48

create database QuanCoffee
use QuanCoffee
create table danhmucsp(
SanphamID int NOT NULL primary key,
TenSp nvarchar(50),
Num int,
GiaSp int,
GiaNhap int,
GiaBan int,
)
create table menudouong(
DoUongId int NOT NULL primary key,
TenDoUong nvarchar(50),
Num int,
GiaDoUong int
)
create table QuanLyNhanVien(
NhanVienID int NOT NULL,
TenNhanVien nvarchar(50),
SoDienThoai int,
DiaChi nvarchar(200)
)
create table QuanLyDonHang(
DonHangId int primary key NOT NULL identity(1,1),
TenDonHang nvarchar(50),
ChuThich text,
DoUong nvarchar(50),
SoLuong int,
TenNhanVien nvarchar(100),
NhanVienID int NOT NULL,
)
create table QuanLyKH(
ID int primary key NOT NULL identity(1,1),
TenKH nvarchar(100),
DonHangID int references QuanLyDonHang(DonHangID),
ThoiGianDonHang datetime,
)
insert into danhmucsp(SanphamID, TenSp, Num, GiaSp, GiaNhap, GiaBan)
values
('6','banh plan', '100', '25000', '15000', '22000'),
('7','banh mi', '220', '15000', '10000', '12000'),
('8','mi bo', '140', '35000', '28000', '33000'),
('9','mi xao', '250', '35000', '26000', '33000'),
('10','banh trung', '400', '20000', '12000', '16000')
select * from danhmucsp

insert into menudouong(DoUongId, TenDoUong, Num, GiaDoUong)
values
(6,'coffe chon', 120, 150000),
(7,'coffe capuchino', 200, 64000),
(8,'bac xiu', 220, 25000),
(9,'den da', 130, 20000),
(10,'nau da', 110, 22000)
select * from menudouong

2020-12-07 08:06:03

CREATE DATABASE QuanliCafe

USE QuanliCafe

CREATE TABLE Menu(
	IDSanPham INT IDENTITY (1,1),
	TenSanPham NVARCHAR(50) 
)

ALTER TABLE dbo.Menu ADD CONSTRAINT PK_Menu PRIMARY KEY(IDSanPham)

INSERT dbo.Menu ( TenSanPham )
VALUES 
 (N'Cà phê'),
 (N'Sinh tố'),
 (N'Bia')

CREATE TABLE CaPhe(
	IDSanPham INT NOT NULL,
	LoaiCafe NVARCHAR(50)
	CONSTRAINT FK_Caphe FOREIGN KEY(IDSanPham) REFERENCES dbo.Menu(IDSanPham) 
)

CREATE TABLE Sinhto(
	IDSanPham INT NOT NULL,
	LoaiSinhTo NVARCHAR(50)
	CONSTRAINT FK_Sinhto FOREIGN KEY(IDSanPham) REFERENCES dbo.Menu(IDSanPham) 
)

CREATE TABLE Bia(
	IDSanPham INT NOT NULL,
	LoaiBia NVARCHAR(50)
	CONSTRAINT FK_Bia FOREIGN KEY(IDSanPham) REFERENCES dbo.Menu(IDSanPham) 
)

INSERT dbo.CaPhe
        ( IDSanPham, LoaiCafe )
VALUES  ( 1, -- IDSanPham - int
          N'Nâu đá'  -- LoaiCafe - nvarchar(50)
          ),
		( 1, -- IDSanPham - int
          N'Đen đá'  -- LoaiCafe - nvarchar(50)
          ),
		( 1, -- IDSanPham - int
          N'Bạc xỉu'  -- LoaiCafe - nvarchar(50)
          )

INSERT dbo.Sinhto
        ( IDSanPham, LoaiSinhTo )
VALUES  ( 2, -- IDSanPham - int
          N'Dưa hấu'  -- LoaiSinhTo - nvarchar(50)
          ),
		( 2, -- IDSanPham - int
          N'Xoài'  -- LoaiSinhTo - nvarchar(50)
          ),
		( 2, -- IDSanPham - int
          N'Hồng Xiêm'  -- LoaiSinhTo - nvarchar(50)
          )

INSERT dbo.Bia
        ( IDSanPham, LoaiBia )
VALUES  ( 3, -- IDSanPham - int
          N'Heneiken'  -- LoaiBia - nvarchar(50)
          ),
		( 3, -- IDSanPham - int
          N'Hà Nội'  -- LoaiBia - nvarchar(50)
          ),
		( 3, -- IDSanPham - int
          N'333'  -- LoaiBia - nvarchar(50)
          )

CREATE TABLE NhanVien(
	IDNhanvien INT IDENTITY(1,1),
	TenNhanVien NVARCHAR(50)
	CONSTRAINT PK_Nhanvien PRIMARY KEY(IDNhanvien)
)

INSERT dbo.NhanVien
        ( TenNhanVien )
VALUES  (N'Đỗ Trung Đức'),
		(N'Nguyễn Tiến Đạt'),
		(N'Trần Văn Điệp')

2020-12-07 08:05:47

create database quanlicafe

use quanlicafe

create table danhmucsanphamcafe(
 maSp int not null primary key,
 tenSp nvarchar(50),
 num int,
 gia int,
 ngaynhap datetime,
 ngayban datetime
)
go

create table douong(
 madouong int primary key not null,
 tendouong nvarchar(50),
 num int,
 gia int,
 ngaynhap datetime,
 ngayban datetime
)
go

create table qlNhanVien(
 maNhanVien int primary key not null identity(1,1),
 tenNhanVien nvarchar(100),
 chucvu nvarchar(50),
 birth datetime,
 donviquanli int
)
go


create table quanDonHang(
 maDonHang int primary key not null identity(1,1),
 tenDonHang nvarchar(50),
 note text,
 sanpham nvarchar(100),
 maSp int references danhmucsanphamcafe(maSp),
 soluong int,
 tenNhanVien nvarchar(100),
 maNhanVien int references qlNhanVien(maNhanVien),
 thoigianketthuc datetime
)
go




create table qlKhachHang(
 id int primary key not null identity(1,1),
 tenKhachhang nvarchar(100),
 maDonHang int references quanDonHang(maDonHang),
 thoigianketthut datetime 
)
go

insert into danhmucsanphamcafe(maSp,tenSp,num,gia,ngaynhap,ngayban)
values
('1','Banhmy','200','10000','2020-1-1','2020-1-10'),
('2','cafe den','300','20000','2020-2-1','2020-2-10'),
('3','cafe chon','220','50000','2020-1-10','2020-1-19'),
('4','cokki','100','50000','2020-1-19','2020-1-22'),
('5','calem','40','20000','2020-1-19','2020-1-20')

insert into douong(madouong,tendouong,num,gia,ngaynhap,ngayban)
values
('1','cafe chon','40','20000','2020-1-19','2020-1-20'),
('2','cafe dang','40','20000','2020-2-19','2020-2-20'),
('3','cafe duong','40','20000','2020-2-19','2020-2-20'),
('4','cafe chan chau','40','20000','2020-1-19','2020-2-2'),
('5','cafe macha','40','20000','2020-1-31','2020-2-2')

insert into qlNhanVien(tenNhanVien,chucvu,birth,donviquanli)
values
('Do Mac Nam','bao ve','2020-1-1','1'),
('Nguyen Anh Vu','ban hang','2020-1-2','2'),
('Bui Van Manh','ban hang','2020-2-19','1'),
('Nguyen Ba Hai','bao ve','2020-1-9','1'),
('Lam Dong','sales','2020-11-19','1')

insert into quanDonHang(tenDonHang,note,sanpham,maSp,soluong,tenNhanVien,maNhanVien,thoigianketthuc)
values
('e100','giao trc 14h','cafe chon','1','1','Nguyen Anh Vu','2','2020-3-10'),
('e101','','cafe duong','3','1','Nguyen Anh Vu','2','2020-3-10'),
('e102','de trc cua nha','cafe chon','3','1','Nguyen Anh Vu','2','2020-3-10'),
('e103','','cafe macha','5','3','Nguyen Anh Vu','2','2020-3-10'),
('e106','','cafe dang','2','1','Nguyen Anh Vu','2','2020-3-10')

insert into qlKhachHang(tenKhachhang,maDonHang,thoigianketthut)
values
('Nguyen Phan Dong',1,'2020-3-10'),
('Nga Phan Dong',2,'2020-3-10'),
('Vu Quan Dong',3,'2020-3-10'),
('Luon Phan Dong',4,'2020-3-10'),
('Vu Phan Dong',5,'2020-3-10')

create index in_danhsachsanpham on danhmucsanphamcafe(tenSp)

select * from douong

CREATE PROCEDURE 

2020-12-07 08:05:19

create database cafe_manager
use cafe_manager

----  Quản lý được danh mục sản phâm của quán cafe ----
create table product_list(
productID int identity (1,1) primary key,
name_product nvarchar (100),
price money
)

insert into product_list(name_product,price)
values
('coffe','50'),
('Orange juice','60'),
('Apple juice','70'),
('grape juice','80'),
('peach juice','90')

 create table drink(
 drinkID int identity (1,1) primary key,
 drink_name nvarchar (100),
 price_drink money,
 )
 go
 insert into drink(drink_name,price_drink)
 values
 ('cocacola','50'),
 ('RedBull','60'),
 ('Sting','70'),
 ('Pepsi','80'),
 ('7Up','90')

 create table Staff_store(
 StaffID int identity (1,1) primary key,
 Staff_name nvarchar (100),
 birthday date,
 address nvarchar (100)
 )
 go
 insert into Staff_store(Staff_name, birthday, address)
 values
 ('ABC','2002-01-02','Ha Noi'),
 ('BCD','2002-02-03','Thai Binh'),
 ('CDE','2002-04-05','Lai Chau'),
 ('EFG','2002-06-07','Bac Giang'),
 ('HIK','2002-08-09','Thanh Hoa'),
 ('LMA','2002-10-11','Son Tay')

 create table Order_management(
 orderID int ,
 StaffID int,
 drinkID int,
 price money
 )
 go
 insert into Order_management(orderID,StaffID, drinkID, price)
 values
 ('1','1','1','100'),
 ('2','2','2','200'),
 ('3','3','3','200'),
 ('4','4','4','300'),
 ('5','5','5','400')


 create table Customer_management(
 CustomerID int identity (1,1) primary key,
 Customer_name nvarchar(100),
 dirink_name nvarchar(50),
 price money
 )
 go

 insert into Customer_management(Customer_name,dirink_name,price)
 values
 ('AAA','Milo','100'),
 ('BBB','Cocacola','200'),
 ('CCC','Sting','300'),
 ('DDD','coffe','400'),
 ('EEE','Sinh to','500')
 
 ---- inserting data ----
 select *from product_list
 select *from drink
 select *from Staff_store
 select *from Order_management
 select *from Customer_management

---  Đánh index ---
 CREATE INDEX product_list ON product_list (productID,name_product,price);

 --- foreign key ---
 alter table product_list
 add constraint fk_productID foreign key (productID) references product_list(productID)

 alter table drink
 add constraint fk_drinkID foreign key (drinkID) references drink(drinkID)

 alter table Staff_store
 add constraint fk_StaffID foreign key (StaffID) references Staff_store(StaffID)

 alter table Customer_management
 add constraint fk_CustomerID foreign key (CustomerID) references Customer_management(CustomerID)

 ----  Hiển thị danh sách loại đồ uống theo một danh mục  ----

2020-12-07 08:04:22

create database baitest
use baitest 

create table danhmucsp (
	id_sanpham int primary key identity(1, 1),
	ten_sp nvarchar(50),
	gia money
)

create table do_uong (
	id_DO int primary key identity(1, 1),
	ten_DO nvarchar(50),
	gia money
)

create table nhan_vien (
	id_NV int primary key identity(1, 1),
	ten_NV nvarchar(50),
	tuoi int,
	dia_chi nvarchar(100),
	SDT nvarchar(16),
	gioi_tinh nvarchar(10)
)

create table don_hang (
	id_DH int primary key identity(1, 1),
	id_DO int foreign key references do_uong (id_DO),
	id_NV int foreign key references nhan_vien (id_NV),
	id_khach int foreign key references khach_hang (id_khach),
	ngayban datetime,
	gia money
)

create table khach_hang (
	id_khach int primary key identity(1, 1),
	ten_KH nvarchar(50),
	dc_KH nvarchar(100),
	SDT_KH nvarchar(50),
	id_DH int,
	
)

insert into danhmucsp(ten_sp, gia)
values
('cafe', '1000'),
('tra sua', '2000'),
('banh ngot', '3000'),
('huong duong', '4000'),
('nuoc ngot', '4000')

insert into do_uong( ten_DO, gia)
values
('cafe den', '1000'),
('tra sua tran chau', '2000'),
('bo huc', '4000'),
('cafe nau', '1000'),
('sting', '4000')

insert into nhan_vien (ten_NV, tuoi, dia_chi, SDT, gioi_tinh)
values	
('TRAN VAN A', 19, 'Ha noi', '0987654321', 'nam'),
('TRAN VAN B', 20, 'Ha noi', '0789234567', 'nam'),
('NGUYEN THI C', 19, 'Ha noi', '0878987654', 'nu'),
('NGUYEN THI D', 21, 'Ha noi', '0967456789', 'nu'),
('TRAN VAN E', 20, 'Ha noi', '9879245345', 'nam')

insert into khach_hang(ten_KH, dc_KH, SDT_KH, id_DH)
values
('NGUYEN THI A', 'ha noi', '0986548246', 1),
('TRAN THI A', 'Ha noi', '0985423456', 2),
('LE THI B', 'Ha noi', '0898343264', 3),
('LE THI C', 'Ha noi', '0969878545', 4),
('LE THI D', 'Ha noi', '0253467987', 5)

insert into don_hang(id_DO, id_NV, id_khach, ngayban, gia)
values
(2, 3, 1, '2020-12-07', '10000'),
(1, 2, 2, '2020-12-07', '5000'),
(4, 4, 3, '2020-12-07', '5000'),
(3, 1, 4, '2020-12-07', '8000'),
(5, 5, 5, '2020-12-07', '8000')

select * from danhmucsp
select * from do_uong
select * from nhan_vien
select * from khach_hang
select * from don_hang

2020-12-07 08:03:50

create database cafe_manager
use cafe_manager

----bang quản lý danh mục sản phâm của quán cafe
create table product_list(
product_id int identity(1,1) primary key,
product_name nvarchar(100)
)

create table type_of_drink(
drinks_id int identity(1,1) primary key,
drinks_name nvarchar(100),
price money,
product_id int
)

create table Staff (
Staff_id int identity(1,1) primary key,
Staff_name nvarchar(100),
brith_day date,
Staff_address nvarchar(200)
)

create table beverage_order (
order_id int primary key,
drinks_id int,
price money,
Staff_id int
)
alter table customer
add order_id int
create table customer (
customer_id int identity(1,1) primary key,
customer_name nvarchar (100),
drinks_id int
)
create clustered index ci_do_uong on type_of_drink(drinks_name)

insert into product_list (product_name)
values
('cafe'),
('sinh to'),
('tra'),
('nuoc ep'),
('do an')

select * from product_list

insert into type_of_drink (drinks_name,price,product_id)
values 
('cafe nau da','25','1'),
('nuoc ep dua hau','20','4'),
('sin to bo','30','2'),
('banh ngot','55','5'),
('hong tra','25','3')

select * from type_of_drink

insert into Staff (Staff_name,brith_day,Staff_address)
values
('A','2008-08-09','Ha Noi'),
('S','2002-08-09','Thai Binh'),
('B','2003-08-09','Ha Noi'),
('C','2004-08-09','Hai Phong'),
('D','2004-08-09','Ha Noi')

select * from Staff

insert into  beverage_order (order_id,drinks_id,price,Staff_id)
values
('1','2','20','5'),
('2','5','25','2'),
('3','1','25','4'),
('4','2','20','3'),
('5','3','30','1')

select * from beverage_order

insert into customer (order_id ,customer_name,drinks_id)
values 
('1','AA','5'),
('2','TT','2'),
('3','AE','4'),
('4','FF','3'),
('5','BB','1')

select * from customer

alter table type_of_drink
add constraint fk_product_id foreign key (product_id) references product_list(product_id)


alter table beverage_order
add constraint fk_drink_id foreign key (drinks_id) references type_of_drink(drinks_id)

alter table beverage_order
add constraint fk_Staff_id foreign key (Staff_id) references Staff(Staff_id)

alter table customer
add constraint fk_order_id  foreign key (order_id ) references  beverage_order(order_id )

alter table customer
add constraint fk_customer_drinks_id  foreign key (drinks_id ) references  type_of_drink(drinks_id)

 ----Hiển thị danh sách loại đồ uống theo một danh mục
 ---cach 1
 select type_of_drink.drinks_name,type_of_drink.price,type_of_drink.product_id
 from customer,beverage_order,Staff,type_of_drink,product_list
 where type_of_drink.product_id = product_list.product_id and beverage_order.drinks_id = type_of_drink.drinks_id
 and Staff.Staff_id = beverage_order.Staff_id and customer.drinks_id = type_of_drink.drinks_id and product_list.product_id = '1'

 create proc sp_don_hang 
 @product int 
 as
 select type_of_drink.drinks_name,type_of_drink.price,type_of_drink.product_id
 from customer,beverage_order,Staff,type_of_drink,product_list
 where type_of_drink.product_id = product_list.product_id and beverage_order.drinks_id = type_of_drink.drinks_id
 and Staff.Staff_id = beverage_order.Staff_id and customer.drinks_id = type_of_drink.drinks_id and product_list.product_id = @product

 exec sp_don_hang  1

 ------hien thi damh muc sp
 select type_of_drink.drinks_name,type_of_drink.price,beverage_order.order_id
 from customer,beverage_order,Staff,type_of_drink,product_list
 where type_of_drink.product_id = product_list.product_id and beverage_order.drinks_id = type_of_drink.drinks_id
 and Staff.Staff_id = beverage_order.Staff_id and customer.drinks_id = type_of_drink.drinks_id and beverage_order.order_id = '1'

  create proc sp_ddh 
 @order int 
 as
  select type_of_drink.drinks_name,type_of_drink.price,beverage_order.order_id
 from customer,beverage_order,Staff,type_of_drink,product_list
 where type_of_drink.product_id = product_list.product_id and beverage_order.drinks_id = type_of_drink.drinks_id
 and Staff.Staff_id = beverage_order.Staff_id and customer.drinks_id = type_of_drink.drinks_id and beverage_order.order_id =  @order

 exec sp_ddh 2

 -------Hiển thị danh mục các đơn hàng theo mã KH.
  create proc sp_donhang_khach_hang
 @kh int 
 as
  select customer_name,type_of_drink.drinks_name,type_of_drink.price,beverage_order.order_id
 from customer,beverage_order,Staff,type_of_drink,product_list
 where type_of_drink.product_id = product_list.product_id and beverage_order.drinks_id = type_of_drink.drinks_id
 and Staff.Staff_id = beverage_order.Staff_id and customer.drinks_id = type_of_drink.drinks_id and customer_id =  @kh

 exec sp_donhang_khach_hang 1

alter proc sp_doanh_thu
 as
  select type_of_drink.drinks_name,type_of_drink.price,beverage_order.order_id,sum(beverage_order.price) as doanh_thu
 from customer,beverage_order,Staff,type_of_drink,product_list
 where type_of_drink.product_id = product_list.product_id and beverage_order.drinks_id = type_of_drink.drinks_id
 and Staff.Staff_id = beverage_order.Staff_id and customer.drinks_id = type_of_drink.drinks_id 
 group by type_of_drink.drinks_name,type_of_drink.price,beverage_order.order_id
 exec sp_doanh_thu

2020-12-07 08:03:33

create database QuanLyQuanCafe
use QuanLyQuanCafe

--bang quan ly danh muc san pham--
create table product_list(
  product_id int primary key identity(1,1),
  product_name nvarchar(50)
)
--bang quan ly loai do uong--
create table type_of_drink(
  drink_id int primary key identity(1,1),
  drink_name nvarchar(50),
  price money
)
--bang quan ly nhan vien--
create table Staff(
  staff_id int primary key identity(1,1),
  staff_name nvarchar(50),
  age int,
  address nvarchar(100)
)
--quan ly khach hang--
create table customer(
  id int primary key identity(1,1),
  fullname nvarchar(50),
  phone_number nvarchar(15),
  email nvarchar(50)
)
create table orders(
  orders_id int primary key identity(1,1),
  customer_id int references customer(id),
  staff_id int references staff(id),
  price_total money
)
create table OrderDetail (
  id int primary key identity(1,1),
  type_of_drink_id int references type_of_drink(id),
  price money,
  num int,
  price_total money,
  orderS_id int references orders(id)
)
insert into product_list(product_id, product_name)
values
(1,'sinh to')
(2,'nuoc ep')

insert into type_of_drink( drink_id, drink_name, price)
values
()
Aa

2020-12-07 08:03:24

create database CaffeManagementSystem

Use CaffeManagementSystem

create table ProductList(

stt int primary key,

listname nvarchar(100)

)

create table BeverageType(

stt int,

beveragename nvarchar (100)

)

Create table Staff(

stt int,

staffname nvarchar(50)

)

Create table Bills(

Stt_Don_Hang int,

Staffname_Order nvarchar(50)

)

create table Customer(

Customername nvarchar(50),

Staffname_Order nvarchar(50),

)

insert into ProductList(stt, listname)

values(1,'dm1'),

(2,'dm2'),

(3,'dm3'),

(4,'dm4'),

(5,'dm5')

insert into BeverageType(stt, beveragename)

values(1,'cafe1','dm1'),

(2,'cafe2','dm3'),

(3,'cafe3','dm4'),

(4,'cafe4','dm2'),

(5,'cafe5','dm5')

insert into Staff(stt, staffname)

values(1,'Tran Van A'),

(2,'Tran Van B'),

(3,'Tran Van C'),

(4,'Tran Van D'),

(5,'Tran Van E')

insert into Bills(Stt_Don_Hang, Staffname_Order)

values(1, 'Tran Van D'),

(2, 'Tran Van C'),

(3, 'Tran Van E'),

(4, 'Tran Van B'),

(5, 'Tran Van A')

insert into Customer(Customername, Staffname_Order)

values('Nguyen Hoang A', 'Tran Van A'),

('Nguyen Hoang B', 'Tran Van B'),

('Nguyen Hoang C', 'Tran Van C'),

('Nguyen Hoang D', 'Tran Van C'),

('Nguyen Hoang E', 'Tran Van D')

create clustered index CI_Bills_StaffnameOrder on Bills (Staffname_Order)