Untitled
use master go create database QLBanHang on primary( name='QLBanHang_dat', filename='D:\QLBanHang.mdf', size =20MB, maxsize = 500MB, filegrowth = 10MB ) log on( name='QLBanHang_log', filename='D:\QLBanHang.ldf', size =5MB, maxsize = 100MB, filegrowth = 20% ) go use QLBanHang go create table SanPham( MaSP nchar(10) not null primary key, MaHangSX nchar(10) not null, TenSP nvarchar(50) not null, SoLuong int, MauSac nchar(15), GiaBan money, DonViTinh nchar(10), MoTa ntext ) create table HangSX( MaHangSX nchar(10) not null primary key, TenHang nvarchar(20) not null, DiaChi nvarchar(50), SoDT nchar(15), Email nvarchar(30) ) create table NhanVien( MaNV nchar(10) not null primary key, TenNV nvarchar(30) not null, GioiTinh nchar(10), DiaChi nvarchar(30), SoDT nchar(10), Email nvarchar(30), TenPhong nvarchar(20) ) create table Nhap( SoHDN nchar(10) not null, MaSP nchar(10) not null, SoLuongN int, DonGiaN money ) create table PNhap( SoHDN nchar(10) not null primary key, NgayNhap date, MaNV nchar(10) not null ) create table Xuat( SoHDX nchar(10) not null, MaSP nchar(10) not null, SoLuongX int ) create table PXuat( SoHDX nchar(10) not null primary key, NgayXuat date, MaNV nchar(10) not null ) alter table sanpham add constraint fk_SP_HSX foreign key(MaHangSX) references HangSX(MaHangSX) alter table Nhap add constraint pk_Nhap primary key(SoHDN,MaSP) alter table Nhap add constraint fk_Nhap_PNhap foreign key(SoHDN) references PNhap(SoHDN) alter table Nhap add constraint fk_Nhap_SP foreign key(MaSP) references SanPham(MaSP) alter table Xuat add constraint pk_Xuat primary key(SoHDX,MaSP) alter table Xuat add constraint fk_Xuat_PXuat foreign key(SoHDX) references PXuat(SoHDX) alter table Xuat add constraint fk_Xuat_SP foreign Key(MaSP) references SanPham(MaSP) alter table PNhap add constraint fk_PN_NV foreign key(MaNV) references NhanVien(MaNV) alter table PXuat add constraint fk_PX_NV foreign key(MaNV) references NhanVien(MaNV) go insert into HangSX values('H01', N'SamSung', N'Korea', '011-082', 'ss@gmail.com.kr') insert into HangSX values('H03', N'Vinfone', N'Viet Nam', '084-0982', 'vf@gmail.com.kr') insert into HangSX values('H02', N'OPPO', N'China', '081-08626262', 'oppo@gmail.com.cn') insert into NhanVien values ('NV01' , N'Nguyễn Thị Thu' , N'Nữ', N'Hà Nội', '0982626521', 'thu@gmail.com', N'Kế toán') insert into NhanVien values ('NV02' , N'Lê Văn Nam' , N'Nam', N'Bắc Ninh', '0972525252', 'nam@gmail.com', N'Vật tư') insert into NhanVien values ('NV03' , N'Trần Hòa Bình' , N'Nữ', N'Hà Nội', '0328388388', 'hb@gmail.com', N'Kế toán') insert into SanPham values ('SP01' , 'H02' , N'F1 Plus', 100 , N'Xám', 7000000, N'Chiếc', N'Hàng cận cao cấp') insert into SanPham values ('SP02' , 'H01' , N'Galaxy Note 11',50 , N'Đỏ', 19000000, N'Chiếc', N'Hàng cao cấp') insert into SanPham values ('SP03' , 'H02' , N'F3 lite', 200 , N'Nâu', 3000000, N'Chiếc', N'Hàng phổ thông') insert into SanPham values ('SP04' , 'H03' , N'Vjoy3', 200 , N'Xám', 1500000, N'Chiếc', N'Hàng phổ thông') insert into SanPham values ('SP05' , 'H01' , N'Galaxy V21', 500 , N'Nâu', 8000000, N'Chiếc', N'Hàng cận cao cấp') insert into PNhap values ('N01' , '02-05-2019' , 'NV01') insert into PNhap values ('N02' , '04-07-2020' , 'NV02') insert into PNhap values ('N03' , '05-17-2020' , 'NV02') insert into PNhap values ('N04' , '03-22-2020' , 'NV03') insert into PNhap values ('N05' , '07-07-2020' , 'NV01') insert into Nhap values ('N01' , 'SP02', 10 , 17000000) insert into Nhap values ('N02' , 'SP01', 30 , 6000000) insert into Nhap values ('N03' , 'SP04', 20 , 1200000) insert into Nhap values ('N04' , 'SP01', 10 , 6200000) insert into Nhap values ('N05' , 'SP05', 20 , 7000000) insert into PXuat values ('X01' , '06-14-2020', 'NV02') insert into PXuat values ('X02' , '03-05-2029', 'NV03') insert into PXuat values ('X03' , '12-12-2020', 'NV01') insert into PXuat values ('X04' , '06-02-2020', 'NV02') insert into PXuat values ('X05' , '05-18-2020', 'NV01') insert into Xuat values ('X01' , 'SP03', 5) insert into Xuat values ('X02' , 'SP01', 3) insert into Xuat values ('X03' , 'SP02', 1) insert into Xuat values ('X04' , 'SP03', 2) insert into Xuat values ('X05' , 'SP05', 1) select * from HangSX select * from NhanVien select * from SanPham select * from PNhap select * from Nhap select * from PXuat select * from Xuat
Leave a Comment