use Quanlygiaohang
CREATE TABLE LOAIMATHANG(
MALOAIMATHANG VARCHAR(10) PRIMARY KEY,
TENLOAIMATHANG VARCHAR(20)
)
CREATE TABLE DICHVU(
MADICHVU VARCHAR(10) PRIMARY KEY,
TENLOAIMATHANG TEXT
)

CREATE TABLE KHUVUC(
MAKHUVUC VARCHAR(10) PRIMARY KEY,
TENKHUVUC TEXT
)
CREATE TABLE KHOANGTHOIGIAN(
MAKHOANGTHOIGIANGIAOHANG VARCHAR(10) PRIMARY KEY,
MOTA VARCHAR(20)
)
CREATE TABLE KHACHHANG(
MAKHACHHANG VARCHAR(10) PRIMARY KEY,
MAKHUVUC VARCHAR(10),
TENKHACHHANG VARCHAR(20),
TENCUAHANG VARCHAR(20),
SODTKHACHHANG VARCHAR(20),
DIACHIEMAIL TEXT,
CONSTRAINT FK_KHACHANG_KHUVUC FOREIGN KEY(MAKHUVUC) REFERENCES KHUVUC(MAKHUVUC)
)
CREATE TABLE DANGKYGIAOHANG(
MATHANHVIENGIAOHANG VARCHAR(10) PRIMARY KEY,
MAKHOANGTHOIGIANGIAOHANG VARCHAR(10) UNIQUE,
CONSTRAINT FK_DANGKYGIAOHANG_KHOANGTHOIGIAN FOREIGN KEY(MAKHOANGTHOIGIANGIAOHANG) REFERENCES KHOANGTHOIGIAN(MAKHOANGTHOIGIANGIAOHANG)
)

CREATE TABLE THANHVIENGIAOHANG(
MATHANHVIENGIAOHANG VARCHAR(10) PRIMARY KEY,
TENTHANHVIENGIAOHANG VARCHAR(20),
NGAYSINH DATETIME,
GIOITINH VARCHAR(10) CHECK(GIOITINH IN ('NAM','NU')),
SODIENTHOAITHANHVIEN VARCHAR(20),
DIACHITHANHVIEN TEXT,
CONSTRAINT FK_THANHVIENGIAOHANG_DANGKYGIAOHANG FOREIGN KEY(MATHANHVIENGIAOHANG) REFERENCES DANGKYGIAOHANG(MATHANHVIENGIAOHANG)
)

CREATE TABLE DONHANG_GIAOHANG(
MADONHANGIAOHANG VARCHAR(10) PRIMARY KEY,
MAKHACHHANG VARCHAR(10),
MATHANHVIENGIAOHANG VARCHAR(10),
MADICHVU VARCHAR(10),
MAKHUVUC VARCHAR(10),
TENNGUOINHAN VARCHAR(20),
DIAGIAOHANG TEXT,
SODIENTHOAINGUOINHAN VARCHAR(20),
MAKHOANGTHOIGIANGIAOHANG VARCHAR(10),
NGAYGIAOHANG DATETIME,
PHUONGTHUCTHANHTOAN VARCHAR(20) CHECK(PHUONGTHUCTHANHTOAN IN ('TIENMAT','CHUYEN KHOAN')),
TRANGTHAIPHEDUYET VARCHAR(20) CHECK(TRANGTHAIPHEDUYET IN ('PHE DUYT ROI','CHUA PHE DUYET')),
TRANGTHAIGIAOHANG VARCHAR(20) CHECK(TRANGTHAIGIAOHANG IN ('DA GIAO HANG','CHUA GIAN HANG','NULL')),
CONSTRAINT FK_DONHANG_GIAOHANG_KHACHHANG FOREIGN KEY(MAKHUVUC) REFERENCES KHUVUC(MAKHUVUC),
CONSTRAINT FK_DONHANG_GIAOHANG_THANHVIENGIAOHANG FOREIGN KEY(MATHANHVIENGIAOHANG) REFERENCES THANHVIENGIAOHANG(MATHANHVIENGIAOHANG),
CONSTRAINT FK_DONHANG_GIAOHANG_DICHVU FOREIGN KEY(MADICHVU) REFERENCES DICHVU(MADICHVU),
CONSTRAINT FK_DONHANG_GIAOHANG_KHOANGTHOIGIAN FOREIGN KEY(MAKHOANGTHOIGIANGIAOHANG) REFERENCES KHOANGTHOIGIAN(MAKHOANGTHOIGIANGIAOHANG),
)

CREATE TABLE CHITIET_DONHANG(
MADONHANGIAOHANG VARCHAR(10) PRIMARY KEY,
TENSANPHAMDUOCGIAO VARCHAR(20) UNIQUE,
SOLUONG INT,
TRONGLUONG FLOAT,
MALOAIMATHANG VARCHAR(10),
TIENTHUHO INT,
CONSTRAINT FK_CHITIETDONHANG_DONHANG_GIAOHANG FOREIGN KEY(MADONHANGIAOHANG) REFERENCES DONHANG_GIAOHANG(MADONHANGIAOHANG),
CONSTRAINT FK_CHITIETDONHANG_DONHANG_LOAIMATHANG FOREIGN KEY(MALOAIMATHANG) REFERENCES LOAIMATHANG(MALOAIMATHANG),
)

INSERT INTO LOAIMATHANG VALUES('MH001','QUAN AO'),
('MH002','MY PHAM'),
('MH003','DO GIA DUNG'),
('MH004','DO DIEN TU')

INSERT INTO DICHVU VALUES('DV001','GIAO HANG NGUOI NHAN TRA TIEN PHI'),
('DV002','GIAO HANG NGUOI GUI TRA TIEN PHI'),
('DV003','GIAO HANG CONG ICH')

INSERT INTO KHOANGTHOIGIAN VALUES('TG001','7-9AM'),
('TG002','9-11AM'),
('TG003','1-3PM'),
('TG004','3-5PM'),
('TG005','7-9H30PM')

INSERT INTO KHUVUC VALUES('KV001','SON TRA'),
('KV002','LIENCHIEU'),
('KV003','NGU HANH SON'),
('KV004','HAI CHAU'),
('KV005','THANH KE')

INSERT INTO KHACHHANG VALUES('KH001','KV001','LE THI A','CUA HANG 1','090511111','A@GMAIL.COM','80 PHAN PHU THAI'),
('KH002','KV001','NGUYEN VAN B','CUA HANG 3','0905111112','BGMAIL.COM','100 PHAN TU'),
('KH003','KV002','LE THI B','CUA HANG 3','090511113','BGAI@GMAIL.COM','23 AN THUONG 18'),
('KH004','KV002','NGUYEN VAN C','CUA HANG 4','090511114','C@GMAIL.COM','67 NGO THI THAI'),
('KH005','KV001','LE THI D','CUA HANG 5','090511115','DGMAIL.COM','100VCHAU THI VINH TE')


INSERT INTO DANGKYGIAOHANG VALUES('TV001', 'TG002'),
('TV002', 'TG005'),
('TV003', 'TG004'),
('TV004', 'TG001'),
('TV005', 'TG003')

INSERT INTO THANHVIENGIAOHANG VALUES('TV001','NGUYEN VAN A',1995-11-20,'NAM','0905111111','23 ONG BICH KHIEM'),
('TV002','NGUYEN VAN B',1995-11-21,'NU','0905111112','23 TON DUC THANG'),
('TV003','NGUYEN VAN C',1995-11-22,'NU','0905111113','23 HOANG DIEU'),
('TV004','NGUYEN VAN D',1995-11-23,'NAM','0905111114','23 NGU HANH SON'),
('TV005','NGUYEN VAN E',1995-11-24,'NAM','0905111114','23 DINH THI DIEU')

INSERT INTO DONHANG_GIAOHANG VALUES
('DH001', 'KH001','TV001','DV001','KV001','PHAM VAN A','30 HOANG VAN THU','0906111111','TG004',2016-10-10,'TIENMAT','PHE DUYT ROI','DA GIAO HANG'),
('DH002', 'KH001','TV002','DV001','KV005','PHAM VAN B','31 HOANG VAN THU','0906111112','TG003',2016-10-11,'TIENMAT','PHE DUYT ROI','CHUA GIAN HANG'),
('DH003', 'KH002','TV003','DV001','KV005','PHAM VAN C','32 HOANG VAN THU','0906111113','TG002',2016-10-12,'TIENMAT','PHE DUYT ROI','DA GIAO HANG'),
('DH004', 'KH003','TV001','DV003','KV002','PHAM VAN D','33 HOANG VAN THU','0906111114','TG001',2016-10-13,'CHUYEN KHOAN','PHE DUYT ROI','DA GIAO HANG'),
('DH005', 'KH003','TV005','DV003','KV003','PHAM VAN E','34 HOANG VAN THU','0906111115','TG005',2016-10-14,'CHUYEN KHOAN','CHUA PHE DUYET','NULL')


ALTER TABLE KHACHHANG
ADD DIACHINHANHANG TEXT




SELECT TenThanhVienGiaoHang
FROM THANHVIENGIAOHANG
WHERE TenThanhVienGiaoHang LIKE 'N%' AND
LEN(TenThanhVienGiaoHang)>=1