CREATE DATABASE QLQL
-- delete database qlbvt
-- tạo đường dẫn cho database của mình
-- gửi mail [You must be registered and logged in to see this link.]
-- tiêu đề mail 11ht02_08050287_b1
-- HỌ TÊN: ĐẶNG NGỌC CƯỜNG
-- MSSV: 08050287
-- LỚP 11TH02
GO
USE QLQL
-- tạo bảng
CREATE TABLE VATTU
(
   MAVT CHAR(4),-- PRIMARY KEY,
   TENVT NVARCHAR(100) NOT NULL,
   DVTINH NVARCHAR(10) NOT NULL,
   PHANTRAM REAL NOT NULL
   CONSTRAINT PK_VATTU_MAVT PRIMARY KEY(MAVT),
   CONSTRAINT CK_VATTU_PHANTRAM CHECK(PHANTRAM > =0 AND PHANTRAM <=100)
)
GO
CREATE TABLE NHACC
(
   manhacc CHAR(4),
   tennhacc NVARCHAR(100),
   diachi NVARCHAR(100),
   dienthoai VARCHAR(20),
   CONSTRAINT PK_VATTU_NHACC PRIMARY KEY(MANHACC)
)
GO
CREATE TABLE DONDH
(
   sohd CHAR(4),
   ngaydh DATETIME,
   manhacc CHAR(4),
   CONSTRAINT P_VATTU_DONDH PRIMARY KEY(SOHD),
   CONSTRAINT FK_ĐONH_NHACC FOREIGN KEY(MANHACC) REFERENCES NHACC(MANHACC)
)
GO
CREATE TABLE CTDONDH
(
   sohd CHAR(4),
   mavt CHAR(4),
   sldat INT,
   CONSTRAINT PK_VATTU_CTDONDH PRIMARY KEY(sohd,mavt),
   CONSTRAINT FK_CTDONDH_MAVT FOREIGN KEY(MAVT) REFERENCES VATTU(MAVT),
   CONSTRAINT FK_CTDONDH_SOHD FOREIGN KEY(SOHD) REFERENCES DONDH(SOHD),
   CONSTRAINT CK_CTDONDH_SLDAT CHECK(SLDAT > 0)
)
GO
CREATE TABLE PNHAP
(
   sopn CHAR(4),
   ngaynhap DATETIME,
   sohd CHAR(4)   
   CONSTRAINT PK_VATTU_PNHAP PRIMARY KEY(SOPN),
   CONSTRAINT FK_PNHAP_SOHD FOREIGN KEY(SOHD) REFERENCES DONDH(SOHD)
)
GO
CREATE TABLE CTPNHAP
(
   sopn CHAR(4),
   mavt CHAR(4),
   slnhap INT,
   dgnhap MONEY
   CONSTRAINT PK_VATTU_CTPNHAP PRIMARY KEY(SOPN,MAVT),
   CONSTRAINT FK_CTPNHAP_MAVT FOREIGN KEY(MAVT) REFERENCES VATTU(MAVT),
   CONSTRAINT FK_CTPNHAP_SOPN FOREIGN KEY(SOPN) REFERENCES PNHAP(SOPN),
   CONSTRAINT CK_CTPNHAP_SLNHAP_DGNHAP CHECK(SLNHAP > 0 AND DGNHAP > 0)
   
)
GO
CREATE TABLE PXUAT
(
   sopx CHAR(4),
   ngayxuat DATETIME,
   tenkh NVARCHAR(100)
   CONSTRAINT PK_VATTU_PXUAT PRIMARY KEY(SOPX) NOT NULL
)
GO
CREATE TABLE CTPXUAT
(
   sopx CHAR(4),
   mavt CHAR(4),
   slxuat INT,
   dgxuat MONEY
   CONSTRAINT PK_VATTU_CTPXUAT PRIMARY KEY(SOPX,MAVT),
   CONSTRAINT FK_CTPXUAT_MAVT FOREIGN KEY(MAVT) REFERENCES VATTU(MAVT),
   CONSTRAINT FK_CTPXUAT_SOPX FOREIGN KEY(SOPX) REFERENCES PXUAT(SOPX),
   CONSTRAINT CK_XTPXUAT_SLXUAT_DGXUAT CHECK(SLXUAT > 0 AND DGXUAT > 0)
)
GO
CREATE TABLE TONKHO
(
   namthang CHAR(6),
   mavt CHAR(4),
   sldau INT,
   tongsln INT,
   tongslx INT,
   slcuoi INT,
   CONSTRAINT PK_VATTU_TONKHO PRIMARY KEY(NAMTHANG,MAVT),
   CONSTRAINT FK_TONKHO_MAVT FOREIGN KEY(MAVT) REFERENCES VATTU(MAVT),
   CONSTRAINT CK_TONKHO_ALL_SO CHECK(SLDAU >= 0 AND TONGSLN >= 0 AND TONGSLX >= 0 AND SLCUOI >= 0)
)
GO

--yyyy-mm-dd
--
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('C001',N'Cát',N'Khối',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('D001',N'Đá',N'Xe',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('G001',N'Gạch bốn lỗ',N'Viên',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('G002',N'Gạch thẻ',N'Viên',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('G003',N'Gạch tàu',N'Viên',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('G004',N'Gạch men',N'Viên',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('G005',N'Gạch lót nền',N'Thùng',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('S001',N'Sắt',N'KG',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('S002',N'Sắt tròn',N'KG',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('SN01',N'Sơn nước',N'Thùng',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('SN02',N'Sơn trét',N'Thùng',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('SN03',N'Sơn chống rỉ',N'Thùng',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('T001',N'Thép phi 18',N'Tấn',10)
GO
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('T002',N'Tole 0,3mm',N'Tấm',10)
GO
-- nhập vật tư
INSERT INTO vattu(mavt,tenvt,dvtinh,phantram)VALUES('T003',N'Tole lạnh 5mm',N'Tấm',10)




GO
--- Giờ tới nhà cung cấp nè


INSERT INTO NHACC(manhacc,tennhacc,diachi,dienthoai)VALUES('A001',N'Hồng Phương',N'234 Lê Lợi Q1 THPHCM','0125487625')
INSERT INTO NHACC(manhacc,tennhacc,diachi,dienthoai)VALUES('A002',N'Minh Trang',N'234 Lê Lợi Q1 THPHCM','0125487625')
INSERT INTO NHACC(manhacc,tennhacc,diachi,dienthoai)VALUES('A003',N'Nhật Thắng',N'234 Lê Lợi Q1 THPHCM','0125487625')
INSERT INTO NHACC(manhacc,tennhacc,diachi,dienthoai)VALUES('A012',N'Nguyệt Quế',N'234 Lê Lợi Q1 THPHCM','0125487625')
INSERT INTO NHACC(manhacc,tennhacc,diachi,dienthoai)VALUES('B001',N'Thành Trung',N'234 Lê Lợi Q1 THPHCM','0125487625')
INSERT INTO NHACC(manhacc,tennhacc,diachi,dienthoai)VALUES('C001',N'Minh Thạnh',N'234 Lê Lợi Q1 THPHCM','0125487625')
GO

-- Đơn đặt hàng
INSERT INTO DONDH(sohd,ngaydh,manhacc)VALUES ('D001','2011-01-05','A003')
INSERT INTO DONDH(sohd,ngaydh,manhacc)VALUES ('D002','2011-01-17','A012')
INSERT INTO DONDH(sohd,ngaydh,manhacc)VALUES ('D003','2011-01-20','C001')

GO
---Chi tiết đơn đặt hàng
INSERT INTO CTDONDH(sohd,mavt,sldat)VALUES ('D001','G001',1300)
INSERT INTO CTDONDH(sohd,mavt,sldat)VALUES ('D001','SN01',60)
INSERT INTO CTDONDH(sohd,mavt,sldat)VALUES ('D002','S001',1000)
INSERT INTO CTDONDH(sohd,mavt,sldat)VALUES ('D002','T001',1000)
INSERT INTO CTDONDH(sohd,mavt,sldat)VALUES ('D003','T003',100)

-- PHIẾU NHẬP
GO
INSERT INTO PNHAP(sopn,ngaynhap,sohd)VALUES ('N001','2011-01-07','D001')
INSERT INTO PNHAP(sopn,ngaynhap,sohd)VALUES ('N002','2011-01-20','D002')
INSERT INTO PNHAP(sopn,ngaynhap,sohd)VALUES ('N003','2011-01-22','D001')
GO
-- CT PHIẾU NHẬP
INSERT INTO CTPNHAP(sopn,mavt,slnhap,dgnhap)VALUES ('N001','G001',1000,500)
INSERT INTO CTPNHAP(sopn,mavt,slnhap,dgnhap)VALUES ('N001','SN01',55,120000)
INSERT INTO CTPNHAP(sopn,mavt,slnhap,dgnhap)VALUES ('N002','S001',800,9500)
INSERT INTO CTPNHAP(sopn,mavt,slnhap,dgnhap)VALUES ('N002','T001',1000,15000)
INSERT INTO CTPNHAP(sopn,mavt,slnhap,dgnhap)VALUES ('N003','G001',300,800)
INSERT INTO CTPNHAP(sopn,mavt,slnhap,dgnhap)VALUES ('N003','SN01',5,120000)

-- PHIẾU XUẤT
GO
INSERT INTO PXUAT(sopx,ngayxuat,tenkh)VALUES ('X001','2001-01-09',N'NGUYỄN VĂN SANH')
INSERT INTO PXUAT(sopx,ngayxuat,tenkh)VALUES ('X002','2001-01-28',N'PHẠM THÁI HÒA')
-- CHI TIẾT PHIẾU XUẤT
GO
INSERT INTO CTPXUAT(sopx,mavt,slxuat,dgxuat)VALUES ('X001','G001',300,600)
INSERT INTO CTPXUAT(sopx,mavt,slxuat,dgxuat)VALUES ('X001','SN01',50,132000)
INSERT INTO CTPXUAT(sopx,mavt,slxuat,dgxuat)VALUES ('X002','G001',200,900)
INSERT INTO CTPXUAT(sopx,mavt,slxuat,dgxuat)VALUES ('X002','S001',300,10500)
INSERT INTO CTPXUAT(sopx,mavt,slxuat,dgxuat)VALUES ('X002','T001',400,17500)
-- TỒN KHO
GO
INSERT INTO TONKHO(namthang,mavt,sldau,tongsln,tongslx,slcuoi)VALUES('201101','G001',0,1300,500,800)
INSERT INTO TONKHO(namthang,mavt,sldau,tongsln,tongslx,slcuoi)VALUES('201101','S001',0,800,300,500)
INSERT INTO TONKHO(namthang,mavt,sldau,tongsln,tongslx,slcuoi)VALUES('201101','SN01',0,60,50,10)
INSERT INTO TONKHO(namthang,mavt,sldau,tongsln,tongslx,slcuoi)VALUES('201101','T001',0,1000,400,600)


--- KIỂM TRA RÀNG BUỘC MIỀN GIÁ TRỊ
-- 1. CỘT % TRONG BẢNG VATTU NẰM TỪ 0 ĐẾN 100
GO
-- BÀI TẬP VỀ NHÀ
-- CÂU 1:
-- HIỂN THỊ TẤT CẢ DANH SÁCH CÁC VẬT TƯ TRONG BẢNG VATTU SẮP XẾP TÊN VẬT TƯ THEO THỨ TỰ GIẢM DẦN
SELECT * FROM VATTU
ORDER BY TENVT DESC

-- CÂU 2:
-- LIKE: SO SÁNH GIỮA KÍ TỰ VỚI KÍ TỰ
-- HIỂN THỊ DANH SÁCH CÁC NHÀ CUNG CẤP TRONG BẢNG NHACC CÓ ĐỊA CHỈ Ở Q1 HCM, SẮP XẾP TÊN NHACC THEO THỨ TỰ TĂNG DẦN
GO
SELECT * FROM NHACC
WHERE DIACHI LIKE '%Q1 THPHCM%'
ORDER BY TENNHACC ASC
GO
-- CÂU 3:
-- SQL: THAY THẾ NHIỀU KÍ TỰ '%', _ 1 KÍ TỰ
-- HIỂN THỊ DANH SÁCH CÁC THÔNG TIN TRONG BẢN CTPNHAP VÀ CÓ THÊM CỘT THÀNH TIỀN BIẾT RẰNG
--               THÀNH TIỀN = SLNHAP*DGNHAP
SELECT *,(SLNHAP * DGNHAP) AS THANHTIEN FROM CTPNHAP
DROP DATABASE QLBVT