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