Tạo bảng dữ liệu
Loại nước
Sẽ tiếp tục cập nhật hè hè
- Code:
create database DEAN
use DEAN
create table NuocGiaiKhat
(
MaNGK nvarchar(10) constraint pk_NuocGiaiKhat_MaNGk primary key,
MaLoai nvarchar(10),
TenNGK nvarchar(50),
MaHSX nvarchar(10),
DVT nvarchar(10),
SLTon numeric,
GiaBanLe float,
MaDVCU nvarchar(10)
)
create table LoaiNuoc
(
MaLoai nvarchar(10) constraint pk_LoaiNuoc_MaLoai primary key,
TenLoai nvarchar(50) not null
)
create table HangSX
(
MaHSX nvarchar(10) constraint pk_HangSX_MaHSX primary key,
TenHSX nvarchar(50) not null
)
create table DVCungUng
(
MaDVCU nvarchar(10) constraint pk_DVCungUng_MaDVCU primary key,
TenDVCU nvarchar(50) not null,
DiaChi nvarchar(50),
DienThoai nvarchar(15)
)
create table KhachHang
(
MaKH nvarchar(10) constraint pk_KhachHang_MaKH primary key,
TenKH nvarchar(50) not null,
DiaChiKH nvarchar(50),
SoDienThoai nvarchar(15)
)
create table PhieuDatHang
(
SoPhieuDatHang nvarchar(10) constraint pk_PhieuDatHang_SoPhieuDatHang primary key,
NgayDH date
)
create table PhieuGiaoHang
(
SoPhieuGiaoHang nvarchar(10) constraint pk_PhieuGiaoHang_SoPhieuGiaoHang primary key,
NgayGH date,
SoPhieuDatHang nvarchar(10),
TongTien float
)
create table HoaDon
(
SoHD nvarchar(10) constraint pk_HoaDon_SoHD primary key,
NgayHD date,
TriGia float,
NgayThanhToan date,
MaKH nvarchar(10)
)
create table DatHang
(
MaNGK nvarchar(10),
SoPhieuDatHang nvarchar(10),
SoLuongDH numeric
)
create table GiaoHang
(
MaNGK nvarchar(10),
SoPhieuGiaoHang nvarchar(10),
SLDat numeric,
SLDaGiao numeric,
SLGiao numeric,
DonGia float,
ThanhTien float
)
create table BanHang
(
SoHD nvarchar(10),
MaNGK nvarchar(10),
SLBan numeric,
Tien float
)
alter table nuocgiaikhat add constraint fk_NuocGiaiKhat_MaLoai foreign key (MaLoai) references LoaiNuoc(MaLoai)
alter table nuocgiaikhat add constraint fk_NuocGiaiKhat_MaHSX foreign key (MaHSX) references HangSX(MaHSX)
alter table nuocgiaikhat add constraint fk_NuocGiaiKhat_MaDVCU foreign key (MaLoai) references DVCungUng(MaDVCU)
alter table banhang add constraint fk_BanHang_SoHD foreign key (SoHD) references HoaDon(SoHD)
alter table banhang add constraint fk_BanHang_MaNGK foreign key (MaNGK) references NuocGiaiKhat(MaNGK)
alter table hoadon add constraint fk_HoaDon_MaKH foreign key (MaKH) references KhachHang(MaKH)
alter table dathang add constraint fk_DatHang_SoPhieuDH foreign key(SoPhieuDatHang) references PhieuDatHang(SoPhieuDatHang)
alter table phieugiaohang add constraint fk_PhieuGiaoHang_SoPhieuDH foreign key(SoPhieuDatHang) references PhieuDatHang(SoPhieuDatHang)
alter table giaohang add constraint fk_GiaoHang_MaNGK foreign key(MaNGK) references NuocGiaiKhat(MaNGK)
alter table giaohang add constraint fk_GiaoHang_SoPhieuGH foreign key(SoPhieuGiaoHang) references PhieuGiaoHang(SoPhieuGiaoHang)
Loại nước
- Code:
INSERT INTO LOAINUOC VALUES('RUOU',N'CÁC LOẠI RƯỢU')
INSERT INTO LOAINUOC VALUES('BIA',N'CÁC LOẠI BIA')
INSERT INTO LOAINUOC VALUES('TANGLUC',N'NƯỚC TĂNG LỰC')
INSERT INTO LOAINUOC VALUES('HUONGLIEU',N'NƯỚC UỐNG HƯƠNG LIỆU')
INSERT INTO LOAINUOC VALUES('THIENNHIEN',N'NƯỚC UỐNG THIÊN NHIÊN')
INSERT INTO LOAINUOC VALUES('TONGHOP',N'NƯỚC NGỌT TỔNG HỢP')
INSERT INTO LOAINUOC VALUES('EPTRAICAY',N'NƯỚC ÉP TRÁI CÂY')
INSERT INTO LOAINUOC VALUES('CAFE',N'CÁC LOẠI CÀ PHÊ')
SELECT *FROM LOAINUOC
Sẽ tiếp tục cập nhật hè hè