API tools faq
paste
nht_cntt

QLBH

nht_cntt
Nov 30th, 2015
271
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 9.41 KB | None | 0 0
raw download clone embed print report
  1. CREATE DATABASE QLBH
  2. USE QLBH
  3. SET DATEFORMAT DMY
  4.  
  5. CREATE TABLE KHACHHANG
  6. (
  7.     MAKH CHAR(4) NOT NULL,
  8.     HOTEN VARCHAR(40),
  9.     DCHI VARCHAR(50),
  10.     SODT VARCHAR(20),
  11.     NGSINH smalldatetime,
  12.     DOANHSO money,
  13.     NGDK smalldatetime
  14.    
  15. )
  16.  
  17. ALTER TABLE KHACHHANG ADD
  18. CONSTRAINT PK_KH PRIMARY KEY (MAKH)
  19.  
  20. /* DROP TABLE KHACHHANG */
  21.  
  22.  
  23. CREATE TABLE NHANVIEN
  24. (
  25.     MANV CHAR(4) NOT NULL,
  26.     HOTEN VARCHAR(40),
  27.     DTHOAI VARCHAR(20),
  28.     NGVL smalldatetime
  29. )
  30.  
  31. ALTER TABLE NHANVIEN ADD
  32. CONSTRAINT PK_NV PRIMARY KEY (MANV)
  33.  
  34. CREATE TABLE SANPHAM
  35. (
  36.     MASP CHAR(4) NOT NULL,
  37.     TENSP VARCHAR(40),
  38.     DVT VARCHAR(20),
  39.     NUOCSX VARCHAR(40),
  40.     GIA money
  41. )
  42.  
  43. ALTER TABLE SANPHAM ADD
  44. CONSTRAINT PK_SP PRIMARY KEY (MASP)
  45.  
  46. CREATE TABLE HOADON
  47. (
  48.     SOHD INT NOT NULL,
  49.     NGHD smalldatetime,
  50.     MAKH CHAR(4),
  51.     MANV CHAR(4),
  52.     TRIGIA money
  53. )
  54.  
  55. ALTER TABLE HOADON ADD
  56. CONSTRAINT PK_HD PRIMARY KEY (SOHD)
  57.  
  58. CREATE TABLE CTHD
  59. (
  60.     SOHD INT NOT NULL,
  61.     MASP CHAR(4) NOT NULL,
  62.     SL INT
  63. )
  64.  
  65. ALTER TABLE CTHD ADD
  66. CONSTRAINT PK_CTHD PRIMARY KEY (SOHD, MASP)
  67.  
  68. ALTER TABLE HOADON ADD
  69. CONSTRAINT FK_HD_KH FOREIGN KEY (MAKH) REFERENCES KHACHHANG(MAKH),
  70. CONSTRAINT FK_HD_NV FOREIGN KEY (MANV) REFERENCES NHANVIEN(MANV)
  71. /*
  72. ALTER TABLE SANPHAM ADD GHICHU varchar(20)
  73.  
  74. ALTER TABLE KHACHHANG ADD LOAIKH tinyint
  75.  
  76. ALTER TABLE SANPHAM ALTER COLUMN GHICHU varchar(100)
  77.  
  78. ALTER TABLE SANPHAM DROP COLUMN GHICHU
  79.  
  80. ALTER TABLE KHACHHANG ALTER COLUMN LOAIKH varchar(100)
  81.  
  82. ALTER TABLE KHACHHANG DROP COLUMN LOAIKH
  83.  
  84. ALTER TABLE SANPHAM ADD
  85. CONSTRAINT Chck_DVT CHECK (DVT like '"cay", "hop", "cai", "quyen", "chuc"')
  86.  
  87. ALTER TABLE SANPHAM ADD
  88. CONSTRAINT Chck_Gia CHECK (GIA > 500)
  89.  
  90. ALTER TABLE CTHD ADD
  91. CONSTRAINT Chck_SL CHECK (SL >= 1)
  92.  
  93. ALTER TABLE KHACHHANG ADD
  94. CONSTRAINT Chck_NGDK CHECK (NGDK > NGSINH)
  95. */
  96.  
  97. /* BT1 - Buoi2 */
  98.  
  99. INSERT INTO NHANVIEN
  100. VALUES ('NV01', 'Nguyen Nhu Nhut', '0927345678', '13/4/2006')
  101. INSERT INTO NHANVIEN
  102. VALUES ('NV02', 'Le Thi Phi Yen', '0987567390', '21/4/2006')
  103. INSERT INTO NHANVIEN
  104. VALUES ('NV03', 'Nguyen Van B', '0997047382', '27/4/2006')
  105. INSERT INTO NHANVIEN
  106. VALUES ('NV04', 'Ngo Thanh Tuan', '0913758498', '24/6/2006')
  107. INSERT INTO NHANVIEN
  108. VALUES ('NV05', 'Nguyen Thi Truc Thanh', '0918590387', '20/7/2006')
  109.  
  110.  
  111.  
  112. INSERT INTO KHACHHANG(MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK)VALUES ('KH01',  'Nguyen Van A', '731 Tran Hung Dao, Q5, TpHCM', '08823451', '22/10/1960', 13060000 ,    '22/07/2006')
  113.  
  114. INSERT INTO KHACHHANG(MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK)VALUES ('KH02'   ,'Tran Ngoc Han','  23/5 Nguyen Trai, Q5, TpHCM','0908256478'   ,'03/04/1974',  280000  ,'30/07/2006')
  115.  
  116. INSERT INTO KHACHHANG(MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK)VALUES ('KH03',' Tran Ngoc Linh','   45 Nguyen Canh Chan, Q1, TpHCM',    '0938776266','12/06/1980', 3860000 ,'05/08/2006')
  117.  
  118. INSERT INTO KHACHHANG(MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK)VALUES('KH04','  Tran Minh Long','   50/34 Le Dai Hanh, Q10, TpHCM'  ,'0917325476','9/3/1965',   250000  ,'02/10/2006')
  119.  
  120. INSERT INTO KHACHHANG(MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK)VALUES('KH05','  Le Nhat Minh',' 34 Truong Dinh, Q3, TpHCM','08246108',  '10/3/1950', 21000 ,'28/10/2006')
  121.  
  122. INSERT INTO KHACHHANG (MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK) VALUES('KH06','Le Hoai Thuong','227 Nguyen Van Cu, Q5, TpHCM','08631738','31/12/1981', 915000  ,'24/11/2006')
  123.  
  124. INSERT INTO KHACHHANG (MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK)VALUES('KH07','Nguyen Van Tam','32/3 Tran Binh Trong, Q5, TpHCM','0916783565','6/4/1971',   12500   ,'01/12/2006')
  125.  
  126. INSERT INTO KHACHHANG (MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK) VALUES('KH08','Phan Thi Thanh','45/2 An Duong Vuong, Q5, TpHCM','0938435756','10/1/1971',  365000  ,'13/12/2006')
  127.  
  128. INSERT INTO KHACHHANG (MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK)VALUES('KH09','Le Ha Vinh','873 Le Hong Phong, Q5, TpHCM','08654763', '3/9/1979',   70000   ,'14/01/2007')
  129.  
  130. INSERT INTO KHACHHANG (MAKH,HOTEN,DCHI,SODT,NGSINH,DOANHSO,NGDK)VALUES('KH10','Ha Duy Lap','34/34B Nguyen Trai, Q1, TpHCM','08768904','2/5/1983',   67500   ,'16/01/2007')
  131.  
  132.  
  133.  
  134.  
  135. INSERT INTO SANPHAM VALUES('BC01', 'But chi', 'cay', 'Singapore', 3000)
  136. INSERT INTO SANPHAM VALUES('BC02', 'But chi', 'cay', 'Singapore', 5000)
  137. INSERT INTO SANPHAM VALUES('BC03', 'But chi', 'cay', 'Viet Nam', 3500)
  138. INSERT INTO SANPHAM VALUES('BC04', 'But chi', 'hop', 'Viet Nam', 30000)
  139. INSERT INTO SANPHAM VALUES('BB01', 'But bi', 'cay', 'Viet Nam', 5000)
  140. INSERT INTO SANPHAM VALUES('BB02', 'But bi', 'cay', 'Trung Quoc', 7000)
  141. INSERT INTO SANPHAM VALUES('BB03', 'But bi', 'hop', 'Thai Lan', 100000)
  142. INSERT INTO SANPHAM VALUES('TV01', 'Tap 100 giay mong', 'quyen', 'Trung Quoc', 2500)
  143. INSERT INTO SANPHAM VALUES('TV02', 'Tap 200 giay mong', 'quyen', 'Trung Quoc', 4500)
  144. INSERT INTO SANPHAM VALUES('TV03', 'Tap 100 giay tot', 'quyen', 'Viet Nam', 3000)
  145. INSERT INTO SANPHAM VALUES('TV04', 'Tap 200 giay tot', 'quyen', 'Viet Nam', 5500)
  146. INSERT INTO SANPHAM VALUES('TV05', 'Tap 100 trang', 'chuc', 'Viet Nam', 23000)
  147. INSERT INTO SANPHAM VALUES('TV06', 'Tap 200 trang', 'chuc', 'Viet Nam', 53000)
  148. INSERT INTO SANPHAM VALUES('TV07', 'Tap 100 trang', 'chuc', 'Trung Quoc', 34000)
  149. INSERT INTO SANPHAM VALUES('ST01', 'So tay 500 trang', 'quyen', 'Trung Quoc', 40000)
  150. INSERT INTO SANPHAM VALUES('ST02', 'So tay loai 1', 'quyen', 'Viet Nam', 55000)
  151. INSERT INTO SANPHAM VALUES('ST03', 'So tay loai 2', 'quyen', 'Viet Nam', 51000)
  152. INSERT INTO SANPHAM VALUES('ST04', 'So tay', 'quyen', 'Thai Lan', 55000)
  153. INSERT INTO SANPHAM VALUES('ST05', 'So tay mong', 'quyen', 'Thai Lan', 20000)
  154. INSERT INTO SANPHAM VALUES('ST06', 'Phan viet bang', 'hop', 'Viet Nam', 5000)
  155. INSERT INTO SANPHAM VALUES('ST07', 'Phan khong bui', 'hop', 'Viet Nam', 7000)
  156. INSERT INTO SANPHAM VALUES('ST08', 'Bong bang', 'cai', 'Viet Nam', 1000)
  157. INSERT INTO SANPHAM VALUES('ST09', 'But long', 'cay', 'Viet Nam', 5000)
  158. INSERT INTO SANPHAM VALUES('ST10', 'But long', 'cay', 'Trung Quoc', 7000)
  159.  
  160. INSERT INTO HOADON VALUES('1001', '23/07/2006', 'KH01', 'NV01', 320000)
  161. INSERT INTO HOADON VALUES('1002', '12/08/2006', 'KH01', 'NV02', 840000)
  162. INSERT INTO HOADON VALUES('1003', '23/08/2006', 'KH02', 'NV01', 100000)
  163. INSERT INTO HOADON VALUES('1004', '01/09/2006', 'KH02', 'NV01', 180000)
  164. INSERT INTO HOADON VALUES('1005', '20/10/2006', 'KH01', 'NV02', 3800000)
  165. INSERT INTO HOADON VALUES('1006', '16/10/2006', 'KH01', 'NV03', 2430000)
  166. INSERT INTO HOADON VALUES('1007', '28/10/2006', 'KH03', 'NV03', 510000)
  167. INSERT INTO HOADON VALUES('1008', '28/10/2006', 'KH01', 'NV03', 440000)
  168. INSERT INTO HOADON VALUES('1009', '28/10/2006', 'KH03', 'NV04', 200000)
  169. INSERT INTO HOADON VALUES('1010', '01/11/2006', 'KH01', 'NV01', 5200000)
  170. INSERT INTO HOADON VALUES('1011', '04/11/2006', 'KH04', 'NV03', 250000)
  171. INSERT INTO HOADON VALUES('1012', '30/11/2006', 'KH05', 'NV03', 21000)
  172. INSERT INTO HOADON VALUES('1013', '12/12/2006', 'KH06', 'NV01', 5000)
  173. INSERT INTO HOADON VALUES('1014', '31/12/2006', 'KH03', 'NV02', 3150000)
  174. INSERT INTO HOADON VALUES('1015', '01/01/2007', 'KH06', 'NV01', 910000)
  175. INSERT INTO HOADON VALUES('1016', '01/01/2007', 'KH07', 'NV02', 12500)
  176. INSERT INTO HOADON VALUES('1017', '02/01/2007', 'KH08', 'NV03', 35000)
  177. INSERT INTO HOADON VALUES('1018', '13/01/2007', 'KH08', 'NV03', 330000)
  178. INSERT INTO HOADON VALUES('1019', '13/01/2007', 'KH01', 'NV03', 30000)
  179. INSERT INTO HOADON VALUES('1020', '14/01/2007', 'KH09', 'NV04', 70000)
  180. INSERT INTO HOADON VALUES('1021', '16/01/2007', 'KH10', 'NV03', 67500)
  181. INSERT INTO HOADON VALUES('1022', '16/01/2007', 'Null', 'NV03', 7000)
  182. INSERT INTO HOADON VALUES('1023', '17/01/2007', 'Null', 'NV01', 330000)
  183.  
  184.  
  185. INSERT INTO CTHD VALUES(1001, 'TV02', 10)
  186. INSERT INTO CTHD VALUES(1001, 'ST01', 5)
  187. INSERT INTO CTHD VALUES(1001, 'BC01', 5)
  188. INSERT INTO CTHD VALUES(1001, 'BC02', 10)
  189. INSERT INTO CTHD VALUES(1001, 'ST08', 10)
  190. INSERT INTO CTHD VALUES(1002, 'BC04', 20)
  191. INSERT INTO CTHD VALUES(1002, 'BB01', 20)
  192. INSERT INTO CTHD VALUES(1002, 'BB02', 20)
  193. INSERT INTO CTHD VALUES(1003, 'BB03', 10)
  194. INSERT INTO CTHD VALUES(1004, 'TV01', 20)
  195. INSERT INTO CTHD VALUES(1004, 'TV02', 10)
  196. INSERT INTO CTHD VALUES(1004, 'TV03', 10)
  197. INSERT INTO CTHD VALUES(1004, 'TV04', 10)
  198. INSERT INTO CTHD VALUES(1005, 'TV05', 50)
  199. INSERT INTO CTHD VALUES(1005, 'TV06', 50)
  200. INSERT INTO CTHD VALUES(1006, 'TV07', 20)
  201. INSERT INTO CTHD VALUES(1006, 'ST01', 30)
  202. INSERT INTO CTHD VALUES(1006, 'ST02', 10)
  203. INSERT INTO CTHD VALUES(1007, 'ST03', 10)
  204. INSERT INTO CTHD VALUES(1008, 'ST04', 8)
  205. INSERT INTO CTHD VALUES(1009, 'ST05', 10)
  206. INSERT INTO CTHD VALUES(1010, 'TV07', 50)
  207. INSERT INTO CTHD VALUES(1010, 'ST07', 50)
  208. INSERT INTO CTHD VALUES(1010, 'ST08', 100)
  209. INSERT INTO CTHD VALUES(1010, 'ST04', 50)
  210. INSERT INTO CTHD VALUES(1010, 'TV03', 100)
  211. INSERT INTO CTHD VALUES(1011, 'ST06', 50)
  212. INSERT INTO CTHD VALUES(1012, 'ST07', 3)
  213. INSERT INTO CTHD VALUES(1013, 'ST08', 5)
  214. INSERT INTO CTHD VALUES(1014, 'BC02', 80)
  215. INSERT INTO CTHD VALUES(1014, 'BB02', 100)
  216. INSERT INTO CTHD VALUES(1014, 'BC04', 60)
  217. INSERT INTO CTHD VALUES(1014, 'BB01', 50)
  218. INSERT INTO CTHD VALUES(1015, 'BB02', 30)
  219. INSERT INTO CTHD VALUES(1015, 'BB03', 7)
  220. INSERT INTO CTHD VALUES(1016, 'TV01', 5)
  221. INSERT INTO CTHD VALUES(1017, 'TV02', 1)
  222. INSERT INTO CTHD VALUES(1017, 'TV03', 1)
  223. INSERT INTO CTHD VALUES(1017, 'TV04', 5)
  224. INSERT INTO CTHD VALUES(1018, 'ST04', 6)
  225. INSERT INTO CTHD VALUES(1019, 'ST05', 1)
  226. INSERT INTO CTHD VALUES(1019, 'ST06', 2)
  227. INSERT INTO CTHD VALUES(1020, 'ST07', 10)
  228. INSERT INTO CTHD VALUES(1021, 'ST08', 5)
  229. INSERT INTO CTHD VALUES(1021, 'TV01', 7)
  230. INSERT INTO CTHD VALUES(1021, 'TV02', 10)
  231. INSERT INTO CTHD VALUES(1022, 'ST07', 1)
  232. INSERT INTO CTHD VALUES(1023, 'ST04', 6)
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!