-- USE MASTER drop database QLBH GO create database QLBH GO -- USE QLBH GO -- CREATE TABLE KHACHHANG ( MAKH VARCHAR(5) PRIMARY KEY NOT NULL, TENKH NVARCHAR (30) NOT NULL, DIACHI NVARCHAR (50) NULL, DT VARCHAR (11) CHECK( DT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR DT LIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR DT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR DT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), EMAIL VARCHAR (30) ) GO -- CREATE TABLE VATTU ( MAVT VARCHAR (5) PRIMARY KEY NOT NULL, TENVT NVARCHAR (30) NOT NULL, DVT NVARCHAR (20) NOT NULL, GIAMUA MONEY NULL CHECK(GIAMUA>0), SLTON INT NULL CHECK(SLTON>=0) ) GO -- CREATE TABLE HOADON ( MAHD VARCHAR (10) PRIMARY KEY NOT NULL, NGAY SMALLDATETIME NULL CHECK(NGAY<GETDATE()), MAKH VARCHAR(5) NOT NULL, TONGTG FLOAT NULL ) GO -- CREATE TABLE CTHD ( MAHD VARCHAR (10) NOT NULL, MAVT VARCHAR (5) NOT NULL, SL INT NULL CHECK(SL>0), KHUYENMAI FLOAT NULL, GIABAN FLOAT NULL PRIMARY KEY (MAHD,MAVT) ) GO -- ALTER TABLE HOADON ADD CONSTRAINT FK_HOADON_KHACHHANG FOREIGN KEY (MAKH) REFERENCES KHACHHANG(MAKH) ON UPDATE CASCADE GO -- ALTER TABLE CTHD ADD CONSTRAINT FK_CTHD_HOADON FOREIGN KEY (MAHD) REFERENCES HOADON(MAHD) ON UPDATE CASCADE, CONSTRAINT FK_CTHD_VATTU FOREIGN KEY (MAVT) REFERENCES VATTU(MAVT) ON UPDATE CASCADE GO -- INSERT [dbo].[VATTU] ([MAVT], [TENVT], [DVT], [GIAMUA], [SLTON]) VALUES (N'VT01', N'Xi măng', N'Bao', 50000.0000, 4870) INSERT [dbo].[VATTU] ([MAVT], [TENVT], [DVT], [GIAMUA], [SLTON]) VALUES (N'VT02', N'Cát', N'Khối', 45000.0000, 49935) INSERT [dbo].[VATTU] ([MAVT], [TENVT], [DVT], [GIAMUA], [SLTON]) VALUES (N'VT03', N'Gạch ống', N'Viên', 120.0000, 740000) INSERT [dbo].[VATTU] ([MAVT], [TENVT], [DVT], [GIAMUA], [SLTON]) VALUES (N'VT04', N'Gạch thẻ', N'Viên', 110.0000, 750000) INSERT [dbo].[VATTU] ([MAVT], [TENVT], [DVT], [GIAMUA], [SLTON]) VALUES (N'VT05', N'Đá lớn', N'Khối', 25000.0000, 99980) INSERT [dbo].[VATTU] ([MAVT], [TENVT], [DVT], [GIAMUA], [SLTON]) VALUES (N'VT06', N'Đá nhỏ', N'Khối', 33000.0000, 99985) INSERT [dbo].[VATTU] ([MAVT], [TENVT], [DVT], [GIAMUA], [SLTON]) VALUES (N'VT07', N'Lam gió', N'Cái', 15000.0000, 49980) GO INSERT [dbo].[KHACHHANG] ([MAKH], [TENKH], [DIACHI], [DT], [EMAIL]) VALUES (N'KH01', N'Nguyễn Thị Bé', N'Tân Bình', N'38457895', N'[email protected]') INSERT [dbo].[KHACHHANG] ([MAKH], [TENKH], [DIACHI], [DT], [EMAIL]) VALUES (N'KH02', N'Lê Hoàng Nam', N'Bình Chánh', N'39878987', N'[email protected]') INSERT [dbo].[KHACHHANG] ([MAKH], [TENKH], [DIACHI], [DT], [EMAIL]) VALUES (N'KH03', N'Trần Thị Chiêu', N'Tân Bình', N'38457895', NULL) INSERT [dbo].[KHACHHANG] ([MAKH], [TENKH], [DIACHI], [DT], [EMAIL]) VALUES (N'KH04', N'Mai Thị Quế Anh', N'Bình Chánh', NULL, NULL) INSERT [dbo].[KHACHHANG] ([MAKH], [TENKH], [DIACHI], [DT], [EMAIL]) VALUES (N'KH05', N'Lê Văn Sáng', N'Quận 10', NULL, N'[email protected]') INSERT [dbo].[KHACHHANG] ([MAKH], [TENKH], [DIACHI], [DT], [EMAIL]) VALUES (N'KH06', N'Trần Hoàng', N'Tân Bình', N'38457897', NULL) GO INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD001', CAST(N'2010-05-12 00:00:00' AS SmallDateTime), N'KH01', 560000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD002', CAST(N'2010-05-25 00:00:00' AS SmallDateTime), N'KH02', 1500000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD003', CAST(N'2010-05-25 00:00:00' AS SmallDateTime), N'KH01', 1100000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD004', CAST(N'2010-05-25 00:00:00' AS SmallDateTime), N'KH04', 9900000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD005', CAST(N'2010-05-26 00:00:00' AS SmallDateTime), N'KH04', 1165000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD006', CAST(N'2010-06-02 00:00:00' AS SmallDateTime), N'KH03', 1200000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD007', CAST(N'2010-06-22 00:00:00' AS SmallDateTime), N'KH04', 2500000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD008', CAST(N'2010-06-25 00:00:00' AS SmallDateTime), N'KH03', 6440000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD009', CAST(N'2010-08-15 00:00:00' AS SmallDateTime), N'KH04', 1200000) INSERT [dbo].[HOADON] ([MAHD], [NGAY], [MAKH], [TONGTG]) VALUES (N'HD010', CAST(N'2010-09-30 00:00:00' AS SmallDateTime), N'KH01', 1425000) GO INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD001', N'VT01', 5, 0, 52000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD001', N'VT05', 10, 0, 30000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD002', N'VT03', 10000, 15, 150) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD003', N'VT02', 20, 0, 55000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD004', N'VT03', 50000, 15, 150) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD004', N'VT04', 20000, 12, 120) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD005', N'VT05', 10, 0, 30000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD005', N'VT06', 15, 0, 35000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD005', N'VT07', 20, 0, 17000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD006', N'VT04', 10000, 12, 120) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD007', N'VT04', 20000, 12.5, 125) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD008', N'VT01', 100, 0, 55000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD008', N'VT02', 20, 0, 47000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD009', N'VT02', 25, 0, 48000) INSERT [dbo].[CTHD] ([MAHD], [MAVT], [SL], [KHUYENMAI], [GIABAN]) VALUES (N'HD010', N'VT01', 25, 0, 57000) GO SELECT * FROM KHACHHANG SELECT * FROM VATTU SELECT * FROM HOADON SELECT * FROM CTHD