· 8 years ago · Nov 21, 2017, 08:36 AM
1CREATE DATABASE QUANLIKHACHHANG
2CREATE TABLE KHACHHANG
3(
4MAKH CHAR(4)NOT NULL,
5HOTEN VARCHAR(40),
6DCHI VARCHAR(50),
7SODT VARCHAR(20),
8NGSINH SMALLDATETIME,
9NGDK SMALLDATETIME,
10DOANHSO MONEY
11
12)
13CREATE TABLE NHANVIEN
14(
15MANV CHAR(4) NOT NULL,
16HOTEN VARCHAR(40),
17SODT VARCHAR(20),
18NGVL SMALLDATETIME
19)
20CREATE TABLE SANPHAM
21(
22MASP CHAR(4) NOT NULL,
23TENSP VARCHAR(40),
24DVT VARCHAR(20),
25NUOCSX VARCHAR(40),
26GIA MONEY
27)
28CREATE TABLE HOADON
29(
30SOHD INT ,
31NGHD SMALLDATETIME,
32MAKH CHAR(4) NOT NULL,
33MANV CHAR(4) NOT NULL,
34TRIGIA MONEY
35)
36CREATE TABLE CTHD
37(
38SOHD INT NOT NULL,
39MASP CHAR(4) NOT NULL,
40SL INT
41)
42ALTER TABLE KHACHHANG ADD
43CONSTRAINT KEY_KH PRIMARY KEY (MAKH)
44ALTER TABLE NHANVIEN ADD
45CONSTRAINT KAY_NV PRIMARY KEY (MANV)
46ALTER TABLE SANPHAM ADD
47CONSTRAINT KEY_SP PRIMARY KEY (MASP)
48ALTER TABLE CTHD ADD
49CONSTRAINT KEY_SOHD PRIMARY KEY (SOHD,MASP)
50ALTER TABLE HOADON ADD
51CONSTRAINT KEY_MKH PRIMARY KEY (MAKH,MANV)
52INSERT INTO NHANVIEN
53VALUES ( 'NV04' ,'Nguyen Van B', 0997047382, 27/4/2006)
54DELETE FROM NHANVIEN
55WHERE MANV='NV03'
56ALTER TABLE HOADON ADD
57FOREIGN KEY (MAKH) REFERENCES KHACHHANG(MAKH)
58ALTER TABLE HOADON ADD
59FOREIGN KEY (MANV) REFERENCES NHANVIEN(MANV)
60ALTER TABLE CTHD ADD
61FOREIGN KEY (SOHD) REFERENCES HOADON(SOHD)
62ALTER TABLE CTHD ADD
63FOREIGN KEY (MASP) REFERENCES SANPHAM(MASP)
64ALTER TABLE SANPHAM ADD
65GHICHU VARCHAR(20)
66ALTER TABLE KHACHHANG ADD
67LOAIKH TINYINT
68ALTER TABLE SANPHAM ALTER COLUMN GHICHU VARCHAR(100)
69ALTER TABLE KHACHHANG ALTER COLUMN LOAIKH VARCHAR(20)
70ALTER TABLE SANPHAM DROP COLUMN GHICHU
71ALTER TABLE KHACHHANG DROP CONSTRAINT LAOI_KH
72ALTER TABLE KHACHHANG ADD
73CONSTRAINT LAOI_KH CHECK (LOAIKH IN ('Vang lai', 'Thuong xuyen', 'Vip'))
74ALTER TABLE SANPHAM ADD CONSTRAINT DONVI CHECK (DVT IN('cay','hop','cai','quyen','chuc'))
75ALTER TABLE SANPHAM ADD CONSTRAINT GIABAN CHECK ( GIA>500)
76ALTER TABLE CTHD ADD CONSTRAINT SLMIN CHECK (SL>0)
77ALTER TABLE KHACHHANG ADD CONSTRAINT DKDK CHECK (NGDK > NGSINH)
78alter table HOADON
79drop constraint KEY_MKH
80ALTER TABLE HOADON ADD CONSTRAINT DKMUA CHECK (NGHD > NGDK)
81UPDATE HOADON
82SET TRIGIA=SANPHAM.GIA*CTHD.SL
83FROM SANPHAM, CTHD, HOADON
84UPDATE KHACHHANG
85SET DOANHSO=SUM(HOADON.TRIGIA)
86FROM HOADON, KHACHHANG
87WHERE HOADON.MAKH=KHACHHANG.MAKH
88SELECT HOADON.MAKH, DOANHSO= SUM(TRIGIA)
89FROM KHACHHANG, HOADON
90GROUP BY HOADON.MAKH, DOANHSO
91CREATE TRIGGER CHECK_HOADON
92ON HOADON
93FOR UPDATE, INSERT
94AS
95 IF UPDATE(NGHD)
96 BEGIN
97 DECLARE @NGDK SMALLDATETIME , @NGHD1 SMALLDATETIME
98 SET @NGHD1=(SELECT NGHD FROM inserted )
99 SET @NGDK=(SELECT NGDK FROM KHACHHANG , INSERTED WHERE KHACHHANG.MAKH=INSERTED.MAKH)
100 IF(@NGHD1<@NGDK)
101 BEGIN
102 PRINT 'NGAY MUA HANG LON HON NGAY DK'
103 ROLLBACK TRAN
104 END
105 END
106CREATE TRIGGER CHECK_NGAYNV
107ON HOADON
108FOR UPDATE,INSERT
109AS
110 IF UPDATE(NGHD)
111 BEGIN
112 DECLARE @NGHD SMALLDATETIME, @NGVL SMALLDATETIME
113 SET @NGHD=(SELECT NGHD FROM INSERTED)
114 SET @NGVL=(SELECT NGVL FROM NHANVIEN A,INSERTED B WHERE A.MANV=B.MANV)
115 IF(@NGHD<@NGVL)
116 BEGIN
117 PRINT 'NGHD PHAI LON HON NGVL'
118 ROLLBACK TRAN
119 END
120 END
121INSERT INTO KHACHHANG
122VALUES ('KH01','NGUYEN VAN','731 TRAN HUNG DAO', 042790364,22/07/2006,20/1/1960,4562,'Vip');
123INSERT INTO NHANVIEN
124VALUES ( 'NV05' ,'Nguyen Van B', 0997047382, 27/4/2006)
125SELECT * FROM KHACHHANG
126INSERT INTO KHACHHANG VALUES ('KH02','Tran Ngoc Han','731 Tran Hung Dao, Q5, TpHCM',8823451,22/10/1960,22/07/2006,1234,'Vip')
127INSERT INTO KHACHHANG VALUES ('KH03','Tran Ngoc Linh','23/5 Nguyen Trai, Q5, TpHCM',908256478,22/10/1960,30/07/2006,1234,'Vip')
128INSERT INTO KHACHHANG VALUES ('KH04','Tran Minh Long','45 Nguyen Canh Chan, Q1, TpHCM',938776266,22/10/1960,38845,1234,'Vip')
129INSERT INTO KHACHHANG VALUES ('KH05','Le Nhat Minh','50/34 Le Dai Hanh, Q10, TpHCM',917325476,22/10/1960,38758,1234,'Vip')
130INSERT INTO KHACHHANG VALUES ('KH06','Le Hoai Thuong','34 Truong Dinh, Q3, TpHCM','8246108',22/10/1960,28/10/2006,1234,'Vip')
131INSERT INTO KHACHHANG VALUES ('KH07','Nguyen Van Tam','227 Nguyen Van Cu, Q5, TpHCM','8631738',31/12/1981,24/11/2006,1234,'Vip')
132INSERT INTO KHACHHANG VALUES ('KH08','Phan Thi Thanh','32/3 Tran Binh Trong, Q5, TpHCM','916783565',22/10/1960,3/8/1999,1234,'Vip')
133INSERT INTO KHACHHANG VALUES ('KH09','Le Ha Vinh','45/2 An Duong Vuong, Q5, TpHCM','938435756',22/10/1960,13/12/2006,1234,'Vip')
134INSERT INTO KHACHHANG VALUES ('KH10','Ha Duy Lap','873 Le Hong Phong, Q5, TpHCM','8654763',22/10/1960,14/01/2007,1234,'Vip')
135SELECT *
136FROM CTHD
137INSERT INTO NHANVIEN VALUES('NV01','Nguyen Nhu Nhut','927345678',13/4/2006)
138INSERT INTO NHANVIEN VALUES('NV02','Le Thi Phi Yen','987567390',21/4/2006)
139INSERT INTO NHANVIEN VALUES('NV03','Nguyen Van B','997047382',27/4/2006)
140INSERT INTO NHANVIEN VALUES('NV04','Ngo Thanh Tuan','913758498',24/6/2006)
141INSERT INTO NHANVIEN VALUES('NV05','Nguyen Thi Truc Thanh','918590387',20/7/2006)
142INSERT INTO SANPHAM VALUES ('BC01','But chi','cay','Singapore','3000')
143INSERT INTO SANPHAM VALUES ('BC02','But chi','cay','Singapore','5000')
144INSERT INTO SANPHAM VALUES ('BC03','But chi','cay','Viet Nam','3500')
145INSERT INTO SANPHAM VALUES ('BC04','But chi','hop','Viet Nam','30000')
146INSERT INTO SANPHAM VALUES ('BB01','But bi','cay','Viet Nam','5000')
147INSERT INTO SANPHAM VALUES ('BB02','But bi','cay','Trung Quoc','7000')
148INSERT INTO SANPHAM VALUES ('BB03','But bi','hop','Thai Lan','100000')
149INSERT INTO SANPHAM VALUES ('TV01','Tap 100 giay mong','quyen','Trung Quoc','2500')
150INSERT INTO SANPHAM VALUES ('TV02','Tap 200 giay mong','quyen','Trung Quoc','4500')
151INSERT INTO SANPHAM VALUES ('TV03','Tap 100 giay tot','quyen','Viet Nam','3000')
152INSERT INTO SANPHAM VALUES ('TV04','Tap 200 giay tot','quyen','Viet Nam','5500')
153INSERT INTO SANPHAM VALUES ('TV05','Tap 100 trang','chuc','Viet Nam','23000')
154INSERT INTO SANPHAM VALUES ('TV06','Tap 200 trang','chuc','Viet Nam','53000')
155INSERT INTO SANPHAM VALUES ('TV07','Tap 100 trang','chuc','Trung Quoc','34000')
156INSERT INTO SANPHAM VALUES ('ST01','So tay 500 trang','quyen','Trung Quoc','40000')
157INSERT INTO SANPHAM VALUES ('ST02','So tay loai 1','quyen','Viet Nam','55000')
158INSERT INTO SANPHAM VALUES ('ST03','So tay loai 2','quyen','Viet Nam','51000')
159INSERT INTO SANPHAM VALUES ('ST04','So tay','quyen','Thai Lan','55000')
160INSERT INTO SANPHAM VALUES ('ST05','So tay mong','quyen','Thai Lan','20000')
161INSERT INTO SANPHAM VALUES ('ST06','Phan viet bang','hop','Viet Nam','5000')
162INSERT INTO SANPHAM VALUES ('ST07','Phan khong bui','hop','Viet Nam','7000')
163INSERT INTO SANPHAM VALUES ('ST08','Bong bang','cai','Viet Nam','1000')
164INSERT INTO SANPHAM VALUES ('ST09','But long','cay','Viet Nam','5000')
165INSERT INTO SANPHAM VALUES ('ST10','But long','cay','Trung Quoc','7000')
166
167INSERT INTO HOADON VALUES ('1001',23/07/2006,'KH01','NV01',320000)
168INSERT INTO HOADON VALUES ('1002',39059,'KH01','NV02',840000)
169INSERT INTO HOADON VALUES ('1003',23/08/2006,'KH02','NV01',100000)
170INSERT INTO HOADON VALUES ('1004',38726,'KH02','NV01',180000)
171INSERT INTO HOADON VALUES ('1005',20/10/2006,'KH01','NV02',3800000)
172INSERT INTO HOADON VALUES ('1006',16/10/2006,'KH01','NV03',2430000)
173INSERT INTO HOADON VALUES ('1007',28/10/2006,'KH03','NV03',510000)
174INSERT INTO HOADON VALUES ('1008',28/10/2006,'KH01','NV03',440000)
175INSERT INTO HOADON VALUES ('1009',28/10/2006,'KH03','NV04',200000)
176INSERT INTO HOADON VALUES ('1010',38728,'KH01','NV01',5200000)
177INSERT INTO HOADON VALUES ('1011',38818,'KH04','NV03',250000)
178INSERT INTO HOADON VALUES ('1012',30/11/2006,'KH05','NV03',21000)
179INSERT INTO HOADON VALUES ('1013',39063,'KH06','NV01',5000)
180INSERT INTO HOADON VALUES ('1014',31/12/2006,'KH03','NV02',3150000)
181INSERT INTO HOADON VALUES ('1015',39083,'KH06','NV01',910000)
182INSERT INTO HOADON VALUES ('1016',39083,'KH07','NV02',12500)
183INSERT INTO HOADON VALUES ('1017',39114,'KH08','NV03',35000)
184INSERT INTO HOADON VALUES ('1018',13/01/2007,'KH08','NV03',330000)
185INSERT INTO HOADON VALUES ('1019',13/01/2007,'KH01','NV03',30000)
186INSERT INTO HOADON VALUES ('1020',14/01/2007,'KH09','NV04',70000)
187INSERT INTO HOADON VALUES ('1021',16/01/2007,'KH10','NV03',67500)
188INSERT INTO HOADON VALUES ('1022',16/01/2007,'Null','NV03',7000)
189INSERT INTO HOADON VALUES ('1023',17/01/2007,'Null','NV01',330000)
190
191INSERT INTO CTHD VALUES ('1001','TV02','10')
192INSERT INTO CTHD VALUES ('1001','ST01','5')
193INSERT INTO CTHD VALUES ('1001','BC01','5')
194INSERT INTO CTHD VALUES ('1001','BC02','10')
195INSERT INTO CTHD VALUES ('1001','ST08','10')
196INSERT INTO CTHD VALUES ('1002','BC04','20')
197INSERT INTO CTHD VALUES ('1002','BB01','20')
198INSERT INTO CTHD VALUES ('1002','BB02','20')
199INSERT INTO CTHD VALUES ('1003','BB03','10')
200INSERT INTO CTHD VALUES ('1004','TV01','20')
201INSERT INTO CTHD VALUES ('1004','TV02','10')
202INSERT INTO CTHD VALUES ('1004','TV03','10')
203INSERT INTO CTHD VALUES ('1004','TV04','10')
204INSERT INTO CTHD VALUES ('1005','TV05','50')
205INSERT INTO CTHD VALUES ('1005','TV06','50')
206INSERT INTO CTHD VALUES ('1006','TV07','20')
207INSERT INTO CTHD VALUES ('1006','ST01','30')
208INSERT INTO CTHD VALUES ('1006','ST02','10')
209INSERT INTO CTHD VALUES ('1007','ST03','10')
210INSERT INTO CTHD VALUES ('1008','ST04','8')
211INSERT INTO CTHD VALUES ('1009','ST05','10')
212INSERT INTO CTHD VALUES ('1010','TV07','50')
213INSERT INTO CTHD VALUES ('1010','ST07','50')
214INSERT INTO CTHD VALUES ('1010','ST08','100')
215INSERT INTO CTHD VALUES ('1010','ST04','50')
216INSERT INTO CTHD VALUES ('1010','TV03','100')
217INSERT INTO CTHD VALUES ('1011','ST06','50')
218INSERT INTO CTHD VALUES ('1012','ST07','3')
219INSERT INTO CTHD VALUES ('1013','ST08','5')
220INSERT INTO CTHD VALUES ('1014','BC02','80')
221INSERT INTO CTHD VALUES ('1014','BB02','100')
222INSERT INTO CTHD VALUES ('1014','BC04','60')
223INSERT INTO CTHD VALUES ('1014','BB01','50')
224INSERT INTO CTHD VALUES ('1015','BB02','30')
225INSERT INTO CTHD VALUES ('1015','BB03','7')
226INSERT INTO CTHD VALUES ('1016','TV01','5')
227INSERT INTO CTHD VALUES ('1017','TV02','1')
228INSERT INTO CTHD VALUES ('1017','TV03','1')
229INSERT INTO CTHD VALUES ('1017','TV04','5')
230INSERT INTO CTHD VALUES ('1018','ST04','6')
231INSERT INTO CTHD VALUES ('1019','ST05','1')
232INSERT INTO CTHD VALUES ('1019','ST06','2')
233INSERT INTO CTHD VALUES ('1020','ST07','10')
234INSERT INTO CTHD VALUES ('1021','ST08','5')
235INSERT INTO CTHD VALUES ('1021','TV01','7')
236INSERT INTO CTHD VALUES ('1021','TV02','10')
237INSERT INTO CTHD VALUES ('1022','ST07','1')
238INSERT INTO CTHD VALUES ('1023','ST04','6')
239 --12
240SELECT SOHD
241FROM CTHD
242WHERE (MASP='BB01' OR MASP='BB02')AND SL>10 AND SL<20
243--13
244SELECT SOHD
245FROM CTHD
246WHERE (MASP='BB01')AND SL>10 AND SL<20
247INTERSECT
248SELECT SOHD
249FROM CTHD
250WHERE (MASP='BB02')AND SL>10 AND SL<20
251--14
252SELECT SANPHAM.MASP, TENSP
253FROM SANPHAM, HOADON, CTHD
254WHERE SANPHAM.MASP=CTHD.MASP AND CTHD.SOHD=HOADON.SOHD
255AND SANPHAM.NUOCSX='Trung Quoc' AND HOADON.NGHD=1/1/2007
256--15
257SELECT MASP , TENSP
258FROM SANPHAM
259WHERE MASP NOT IN (SELECT MASP FROM CTHD)
260--16
261SELECT MASP , TENSP
262FROM SANPHAM
263WHERE MASP NOT IN (SELECT MASP FROM CTHD, HOADON WHERE CTHD.SOHD=HOADON.SOHD AND YEAR(HOADON.NGHD)=2016)
264--17
265SELECT MASP , TENSP
266FROM SANPHAM
267WHERE SANPHAM.NUOCSX='Trung Quoc' AND MASP NOT IN (SELECT MASP FROM CTHD, HOADON WHERE CTHD.SOHD=HOADON.SOHD AND YEAR(HOADON.NGHD)=2016)
268--18
269SELECT X.SOHD
270FROM CTHD X
271WHERE NOT EXISTS (SELECT MASP FROM SANPHAM S WHERE NOT EXISTS (SELECT A.SOHD, A.MASP FROM CTHD A, HOADON WHERE A.SOHD=X.SOHD AND A.MASP=S.MASP AND HOADON.SOHD=A.SOHD AND S.NUOCSX='Singapore' AND YEAR(HOADON.NGHD)=2016))
272--19
273SELECT COUNT(MAKH)
274FROM HOADON
275WHERE MAKH=Null
276--20
277SELECT COUNT(CTHD.MASP)
278FROM HOADON, CTHD
279WHERE YEAR(HOADON.NGHD)='2006' AND HOADON.SOHD=CTHD.SOHD
280--21