CREATE DATABASE QUANLYPIZZA
GO
USE QUANLYPIZZA
GO
--CREATE TABLES--
CREATE TABLE KHACHHANG
(
IDKHACHHANG VARCHAR(50) NOT NULL,
TENKHACHHANG NVARCHAR(30) NOT NULL,
EMAIL VARCHAR(30),
SODIENTHOAI VARCHAR(11) CHECK(LEN(SODIENTHOAI) <= 11),
STK NCHAR(20) NOT NULL,
MATKHAU VARCHAR(100) NOT NULL,
DIACHI VARCHAR(50)
)
GO
CREATE TABLE DONHANG
(
IDDONHANG VARCHAR(50) NOT NULL,
IDKHACHHANG VARCHAR(50),
IDDICHVU VARCHAR(50),
NGAYDATHANG DATETIME,
NGAYGIAOHANG DATETIME,
DIACHI VARCHAR(50),
QUANGDUONG FLOAT DEFAULT 0,
TINHTRANGDONHANG VARCHAR(50) DEFAULT 'DANGXULY',
)
GO
CREATE TABLE PIZZA
(
IDPIZZA VARCHAR(50) NOT NULL,
LOAIPIZZA NVARCHAR(30),
)
GO
CREATE TABLE CHITIETPIZZA
(
IDPIZZA VARCHAR(50) NOT NULL,
LOAIPIZZA NVARCHAR(30),
GIATIEN INT NOT NULL,
SIZEPIZZA VARCHAR(50),
LOAIDE VARCHAR(50) NOT NULL,
LOAICOMBO VARCHAR(50) NOT NULL,
TOPPING VARCHAR(50)
)
GO
CREATE TABLE CHITIETDONHANG
(
IDPIZZA VARCHAR(50) NOT NULL,
IDDONHANG VARCHAR(50) NOT NULL,
SOLUONG INT CHECK (SOLUONG>0),
)
GO
CREATE TABLE DICHVU
(
IDDICHVU VARCHAR(50) NOT NULL,
TENDICHVU VARCHAR(50) NOT NULL,
CHUONGTRINHKHUYENMAI VARCHAR(50) NOT NULL,
)
GO
CREATE TABLE THANHTOAN
(
IDTHANHTOAN VARCHAR(50) NOT NULL PRIMARY KEY,
IDDONHANG VARCHAR(50) NOT NULL,
THANHTIEN VARCHAR(50) NOT NULL DEFAULT '0VND',
HINHTHUCTHANHTOAN VARCHAR(50),
)
GO
-- PRIMARY KEY
ALTER TABLE KHACHHANG
ADD CONSTRAINT PK_KHACHHANG
PRIMARY KEY (IDKHACHHANG)
GO
ALTER TABLE DONHANG
ADD CONSTRAINT PK_DONHANG
PRIMARY KEY (IDDONHANG)
GO
ALTER TABLE DICHVU
ADD CONSTRAINT PK_DICHVU
PRIMARY KEY(IDDICHVU)
GO
ALTER TABLE PIZZA
ADD CONSTRAINT PK_PIZZA
PRIMARY KEY(IDPIZZA)
GO
--FOREIGN KEY
ALTER TABLE CHITIETDONHANG
ADD CONSTRAINT FK_CHITITETDONHANG_DONHANG
FOREIGN KEY (IDDONHANG)
REFERENCES DONHANG(IDDONHANG)
GO
ALTER TABLE CHITIETDONHANG
ADD CONSTRAINT FK_CHITIETDONHANG_PIZZA
FOREIGN KEY (IDPIZZA)
REFERENCES PIZZA(IDPIZZA)
GO
ALTER TABLE DONHANG
ADD CONSTRAINT FK_DONHANG_KHACHHANG
FOREIGN KEY (IDKHACHHANG)
REFERENCES KHACHHANG(IDKHACHHANG)
GO
ALTER TABLE DONHANG
ADD CONSTRAINT FK_DONHANG_DICHVU
FOREIGN KEY (IDDICHVU)
REFERENCES DICHVU(IDDICHVU)
GO
ALTER TABLE THANHTOAN
ADD CONSTRAINT FK_THANHTOAN_DONHANG
FOREIGN KEY (IDDONHANG)
REFERENCES DONHANG(IDDONHANG)
GO
ALTER TABLE CHITIETPIZZA
ADD CONSTRAINT FK_CHITIETPIZZA_PIZZA
FOREIGN KEY (IDPIZZA)
REFERENCES PIZZA(IDPIZZA)
--DATA INPUT--
INSERT INTO PIZZA(IDPIZZA,LOAIPIZZA)
VALUES('PZ001','HAISAN'), ('PZ002','BO'), ('PZ003','XUCXICH'), ('PZ004','PHOMAI');
INSERT INTO CHITIETPIZZA(IDPIZZA,LOAIPIZZA,GIATIEN,SIZEPIZZA,LOAIDE,LOAICOMBO,TOPPING)
VALUES('PZ001', 'HAISAN',150000,'S','MONG','None','MUC,TOM,SO DIEP'),
('PZ001','HAISAN',190000,'S','DAY','None','MUC,TOM,SO DIEP'),
('PZ001','HAISAN',210000,'M','MONG','None','MUC,TOM,SO DIEP'),
('PZ001','HAISAN',250000,'M','DAY','None','MUC,TOM,SO DIEP'),
('PZ001','HAISAN',270000,'L','MONG','None','MUC,TOM,SO DIEP'),
('PZ001','HAISAN',300000,'L','DAY','None','MUC,TOM,SO DIEP'),
('PZ002','BO',150000,'S','MONG','None','BO'),
('PZ002','BO',190000,'S','DAY','None','BO'),
('PZ002','BO',210000,'M','MONG','None','BO'),
('PZ002','BO',250000,'M','DAY','None','BO'),
('PZ002','BO',270000,'L','MONG','None','BO'),
('PZ002','BO',300000,'L','DAY','None','BO'),
('PZ003','XUCXICH',150000,'S','MONG','None','XUCXICH'),
('PZ003','XUCXICH',190000,'S','DAY','None','XUCXICH'),
('PZ003','XUCXICH',210000,'M','MONG','None','XUCXICH'),
('PZ003','XUCXICH',250000,'M','DAY','None','XUCXICH'),
('PZ003','XUCXICH',270000,'L','MONG','None','XUCXICH'),
('PZ003','XUCXICH',300000,'L','DAY','None','XUCXICH'),
('PZ004','PHOMAI',150000,'S','MONG','None','PHOMAI'),
('PZ004','PHOMAI',190000,'S','DAY','None','PHOMAI'),
('PZ004','PHOMAI',210000,'M','MONG','None','PHOMAI'),
('PZ004','PHOMAI',250000,'M','DAY','None','PHOMAI'),
('PZ004','PHOMAI',270000,'L','MONG','None','PHOMAI'),
('PZ004','PHOMAI',300000,'L','DAY','None','PHOMAI');
INSERT INTO DICHVU(IDDICHVU,TENDICHVU,CHUONGTRINHKHUYENMAI)
VALUES('DV001','BLACK FRIDAY', 'FREE SHIP'),
('DV002','GIANG SINH', 'FREE SHIP'),
('DV003','VALENTINE', 'FREE SHIP'),
('DV004','NEW YEAR', 'FREE SHIP');
-- INSERT DATA KHACHHANG
INSERT INTO KHACHHANG(IDKHACHHANG,TENKHACHHANG,EMAIL,SODIENTHOAI,STK,MATKHAU,DIACHI)
VALUES
('KH001','NGUYENTRANDUYLONG','nguyentranduylong@gmail.com','0346785905','4306281003242','345678','TP.THUDUC'),
('KH002','PHANTHIMAILINH','mailinhpt3082003@gmail.com','0356749475','4306281003245','3082003','TPTHUDUC'),
('KH003','HUYNHTHICAMGIANG','camgiangww@gmail.com','0393312469','0364246443','234580','QUAN12'),
('KH004','TRUONGNGOCBICH','truongngocbich@gmail.com','0365785421','0000658258370','659002','QUANBINHTHANH'),
('KH005','NGUYENTHUYMYHAN','nguyenthuymyhan@gmail.com','0868453280','430628100457','2832003','TP.THUDUC'),
('KH006','HUYNHHIEUDAN','huynhhieudan@gmail.com','0363312749','0000954885334','975402','QUAN1'),
('KH007','NGUYENTHIHONGNHUNG','hongnhungnguyen@gmail.com','0373202451','1021302340','003490','TPTHUDUC'),
('KH008','VOTRANMINHTRUC','minhtrucvo@gmail.com','0383315794','430628100351','1815300','QUAN10');
-- INSERT DATA THIET LAP DON HANG
INSERT INTO DONHANG(IDDONHANG,IDKHACHHANG,IDDICHVU,NGAYDATHANG,NGAYGIAOHANG,DIACHI,QUANGDUONG,TINHTRANGDONHANG)
VALUES
('DH001','KH001','DV001','2020-12-12','2020-12-12','TP.THUDUC',5,'DANGXULY'),
('DH002','KH002','DV002','2020-12-15','2020-12-15','TP.THUDUC',10,'DANGXULY'),
('DH003','KH003','DV001','2020-12-11','2020-12-11','TP.THUDUC',2.5,'DANGXULY'),
('DH004','KH004','DV002','2020-12-12','2020-12-12','QUANBINHTHANH',3.7,'DANGXULY'),
('DH005','KH005','DV001','2020-12-13','2020-12-13','TP.THUDUC',5.2,'DANGXULY');
--QUERY--
--LAP DON HANG QUA MAY TINH--
SELECT * FROM KHACHHANG WHERE IDKHACHHANG = 'KH001';
--TIM KIEM PIZZA THEO MA SO HOAC THANH PHAN BANH--
SELECT * FROM CHITIETPIZZA WHERE IDPIZZA= 'PZ001';
SELECT * FROM CHITIETPIZZA WHERE LOAIPIZZA='PHOMAI';
SELECT * FROM CHITIETPIZZA WHERE SIZEPIZZA='S';
SELECT * FROM CHITIETPIZZA WHERE TOPPING='BO';
SELECT * FROM CHITIETPIZZA WHERE GIATIEN='300000';
-- THANH TOAN DON HANG --
INSERT INTO THANHTOAN(IDTHANHTOAN,IDDONHANG,THANHTIEN,HINHTHUCTHANHTOAN)
VALUES
('TT001','DH001','300000VND','FREE SHIP'),
('TT002','DH002','300000VND','FREE SHIP'),
('TT003','DH003','300000VND','FREE SHIP'),
('TT004','DH004','300000VND','FREE SHIP'),
('TT005','DH005','300000VND','FREE SHIP');
-- CHECK DICH VU CO CHUONG TRINH KHUYEN MAI --
SELECT * FROM DICHVU
WHERE IDDICHVU = 'DV001';
-- CAP NHAT THANH TOAN --
UPDATE THANHTOAN
SET THANHTIEN = '300000VND'
WHERE IDTHANHTOAN = 'TT001';
-- XEM DON TONG TIEN --
SELECT * FROM THANHTOAN
WHERE IDTHANHTOAN = 'TT001';
-- NEU QUANG DUONG LON HON 5 KM THI TANG NGAY GIAO HANG LEN 1 NGAY --
DECLARE @QUANGDUONG SELECT QUANGDUONG FROM DONHANG WHERE IDDONHANG = 'DH001'
IF @QUANGDUONG > 5
BEGIN
    UPDATE DONHANG SET NGAYGIAOHANG = NGAYGIAOHANG + 1 WHERE IDDONHANG = 'DH001'
END