API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 22nd, 2018
326
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.71 KB | None | 0 0
raw download clone embed print report
  1. /*
  2. DROP TABLE purchases;
  3. DROP TABLE inventory;
  4. DROP TABLE employees;
  5. DROP TABLE stores;
  6. DROP TABLE addresses;
  7. DROP TABLE customers;
  8. DROP TABLE employee_types;
  9. DROP TABLE address_types;
  10. */
  11. -- ***
  12. CREATE TABLE address_types (
  13. add_type_id INTEGER,
  14. add_literal VARCHAR(128) UNIQUE NOT NULL,
  15. PRIMARY KEY (add_type_id)
  16. );
  17.  
  18. INSERT INTO address_types (add_type_id, add_literal)
  19. VALUES (4321, 'Store')
  20. ,(4444, 'Employee')
  21. ,(4567, 'Customer');
  22.  
  23. -- ***
  24. CREATE TABLE employee_types (
  25. emp_id INTEGER,
  26. role_literal VARCHAR(128) NOT NULL,
  27. PRIMARY KEY(emp_id)
  28. );
  29.  
  30. INSERT INTO employee_types (emp_id, role_literal)
  31. VALUES (0001, 'Manager'),
  32. (0002, 'Sales'),
  33. (0003, 'Support');
  34.  
  35. -- ***
  36. CREATE TABLE customers (
  37. cust_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 10000, INCREMENT BY 1),
  38. cust_firstname VARCHAR(128) NOT NULL,
  39. cust_lastname VARCHAR(128) NOT NULL,
  40. PRIMARY KEY(cust_id)
  41. );
  42.  
  43. INSERT INTO customers (cust_firstname, cust_lastname)
  44. VALUES ('Johnny', 'Morris'),
  45. ('Jack', 'Johnson'),
  46. ('Billy', 'Williams'),
  47. ('BB', 'King'),
  48. ('Robert', 'Plant'),
  49. ('Jimmy', 'Falon'),
  50. ('Wycleff', 'Jean'),
  51. ('Tim', 'Robbins'),
  52. ('Bruce', 'Willis'),
  53. ('Mel', 'Gibson')
  54. ;
  55.  
  56. -- ^^
  57. CREATE TABLE addresses (
  58. add_id INTEGER,
  59. add_type_id INTEGER NOT NULL,
  60. city VARCHAR(128) NOT NULL,
  61. state VARCHAR(128) NOT NULL,
  62. zip VARCHAR(11) NOT NULL,
  63. residential CHAR(1) NOT NULL,
  64. CONSTRAINT fk_add_type_id FOREIGN KEY (add_type_id) REFERENCES address_types(add_type_id),
  65. PRIMARY KEY(add_id)
  66. );
  67.  
  68. INSERT INTO addresses (add_id, add_type_id, city, state, zip, residential)
  69. VALUES (1111, 4321, 'Springfield', 'Missouri', '65804', '0')
  70. ,(1212, 4444, 'Springfield', 'Missouri', '65804', '1')
  71. ,(1313, 4444, 'Springfield', 'Missouri', '65804', '1')
  72. ,(1414, 4444, 'Springfield', 'Missouri', '65804', '1')
  73. ,(1515, 4444, 'Springfield', 'Illinois', '47895', '1')
  74. ,(1616, 4444, 'Springfield', 'Missouri', '65804', '1')
  75. ,(1717, 4444, 'Springfield', 'Missouri', '65804', '1')
  76. ,(1818, 4444, 'Springfield', 'Illinois', '47895', '1')
  77. ,(1919, 4444, 'Springfield', 'Missouri', '65804', '1')
  78. ,(2121, 4444, 'Springfield', 'Illinois', '47895', '1')
  79. ,(2222, 4444, 'Springfield', 'Missouri', '65804', '1')
  80. ,(2323, 4444, 'Springfield', 'Missouri', '65804', '1')
  81. ,(2424, 4444, 'Springfield', 'Illinois', '47895', '1')
  82. ,(2525, 4444, 'Springfield', 'Missouri', '65804', '1')
  83. ,(2626, 4444, 'Springfield', 'Missouri', '65804', '1')
  84. ,(2727, 4444, 'Springfield', 'Illinois', '47895', '1')
  85. ,(2828, 4444, 'Springfield', 'Missouri', '65804', '1')
  86. ,(2929, 4444, 'Springfield', 'Massachusettes', '12345', '1')
  87. ,(3030, 4444, 'Springfield', 'Missouri', '65804', '1')
  88. ,(3131, 4444, 'Springfield', 'Missouri', '65804', '1');
  89.  
  90. -- ^^
  91. CREATE TABLE stores (
  92. store_id INTEGER,
  93. store_name VARCHAR(128) NOT NULL,
  94. store_add_id INTEGER NOT NULL,
  95. tax_percent DECIMAL(3, 2) NOT NULL,
  96. CONSTRAINT fk_store_add_id FOREIGN KEY (store_add_id) REFERENCES addresses(add_id),
  97. CONSTRAINT ck_store_name CHECK (LENGTH(store_name) > 3),
  98. PRIMARY KEY(store_id)
  99. );
  100.  
  101. CREATE INDEX store_name_idx
  102. ON stores(store_name);
  103.  
  104. INSERT INTO stores (store_id, store_name, store_add_id, tax_percent)
  105. VALUES (622, 'Bike Store', 1111, 0.12);
  106.  
  107. -- vv
  108. CREATE TABLE employees (
  109. emp_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000, INCREMENT BY 1),
  110. emp_role_id INTEGER NOT NULL,
  111. emp_store_id INTEGER NOT NULL,
  112. emp_firstname VARCHAR(128) NOT NULL,
  113. emp_lastname VARCHAR(128) NOT NULL,
  114. emp_phone VARCHAR(128) NOT NULL,
  115. emp_email VARCHAR(128) UNIQUE NOT NULL,
  116. emp_address INTEGER NOT NULL,
  117. CONSTRAINT fk_emp_role_id FOREIGN KEY (emp_role_id) REFERENCES employee_types(emp_id),
  118. CONSTRAINT fk_emp_store_id FOREIGN KEY (emp_store_id) REFERENCES stores(store_id),
  119. CONSTRAINT fk_emp_address FOREIGN KEY (emp_address) REFERENCES addresses(add_id),
  120. PRIMARY KEY (emp_id)
  121. );
  122.  
  123. INSERT INTO employees (emp_role_id, emp_store_id, emp_firstname, emp_lastname, emp_phone, emp_email, emp_address)
  124. VALUES (0001, 622, 'Jody', 'Pretz', '234-1234', 'pretz@gmail.com', 1212)
  125. ,(0002, 622, 'Bob', 'Smith', '563-3321', 'smith@gmail.com', 1313)
  126. ,(0002, 622, 'Joe', 'Ledger', '123-5134', 'wow@gmail.com', 1414)
  127. ,(0002, 622, 'July', 'Cobb', '753-7753', 'yikes@gmail.com', 1515)
  128. ,(0002, 622, 'Sindey', 'Rock', '714-7765', 'aaah@gmail.com', 1616)
  129. ,(0002, 622, 'Mike', 'Pocket', '582-2334', 'rough@gmail.com', 1717)
  130. ,(0002, 622, 'Bobby', 'Walt', '678-5689', 'dewd@gmail.com', 1818)
  131. ,(0002, 622, 'Allard', 'Free', '333-2221', 'woah@gmail.com', 1919)
  132. ,(0002, 622, 'Fred', 'Walder', '124-5534', 'aased@gmail.com', 2121)
  133. ,(0002, 622, 'Mark', 'Mahan', '675-1321', 'mailacc@gmail.com', 2222)
  134. ,(0002, 622, 'Mitch', 'Traush', '367-5356', 'yeet@gmail.com', 2323)
  135. ,(0002, 622, 'Clyde', 'Maky', '332-4621', 'guyr@gmail.com', 2424)
  136. ,(0002, 622, 'Klyde', 'Loore', '123-5555', 'leet@gmail.com', 2525)
  137. ,(0002, 622, 'Rich', 'Quetz', '770-1111', 'ajax@gmail.com', 2626)
  138. ,(0002, 622, 'Ron', 'Well', '923-3351', 'aasey@gmail.com', 2727)
  139. ,(0002, 622, 'Tim', 'Pool', '122-6678', 'Looper@gmail.com', 2828)
  140. ,(0002, 622, 'Shawnda', 'Zed', '111-1112', 'profane@gmail.com', 2929)
  141. ,(0003, 622, 'Precious', 'Yeary', '222-2238', 'rocky@gmail.com', 3030)
  142. ,(0003, 622, 'Trudy', 'Green', '333-1987', 'loik@gmail.com', 3131);
  143.  
  144. -- vv
  145. CREATE TABLE inventory (
  146. inv_vin CHAR(17),
  147. inv_store_id INTEGER NOT NULL,
  148. inv_year CHAR(4) NOT NULL,
  149. inv_make VARCHAR(128) NOT NULL,
  150. inv_model VARCHAR(128) NOT NULL,
  151. inv_color VARCHAR(128) NOT NULL,
  152. inv_acquired_date DATE NOT NULL,
  153. inv_mileage INTEGER NOT NULL,
  154. CONSTRAINT fk_inv_store_id FOREIGN KEY (inv_store_id) REFERENCES stores(store_id),
  155. PRIMARY KEY (inv_vin)
  156. );
  157.  
  158. INSERT INTO inventory (inv_vin, inv_store_id, inv_year, inv_make, inv_model, inv_color, inv_acquired_date, inv_mileage)
  159. VALUES ('1ZVBP8AM0D5265429', 622, '2018', 'Ford', 'Mustang GT', 'Black', '2017-01-02', 1234),
  160. ('4S3BK6354S6355265', 622, '2008', 'Inifiniti', 'G35', 'Crimson Red', '2017-02-03', 123000),
  161. ('1ZVFT80N475211367', 622, '2013', 'Cheverolet', 'Camaro', 'Black and White', '2017-03-04', 45020),
  162. ('JS3TX92V364102160', 622, '1995', 'Acura', 'NSX', 'White', '2017-04-05', 700),
  163. ('YV1LZ5647W2469314', 622, '2008', 'Pontiac', 'G8 GXP', 'Maroon', '2017-05-06', 83400),
  164. ('1J4BA3H10AL171412', 622, '2019', 'Cheverolet', 'Corvette ZR1', 'Midnight Blue', '2017-06-07', 25),
  165. ('1G4GJ11Y9HP422546', 622, '2018', 'Dodge', 'Challenger Demon', 'Red', '2017-07-08', 123),
  166. ('JH4KA7532NC036794', 622, '2012', 'Maserati', 'Gran Turismo', 'Blue', '2017-08-09', 24000),
  167. ('JG1MR215XJK752025', 622, '1965', 'Ferrari', '250 GTO', 'Rosso Corsa', '2017-09-10', 120),
  168. ('1N6DD21S4WC303918', 622, '1999', 'McLaren', 'F1', 'Yellow', '2017-10-11', 1230),
  169. ('JN8AZ2NE5C9016953', 622, '2005', 'Porsche', 'Carrera GT', 'Silver', '2017-11-12', 5600),
  170. ('4F4ZR17V7XTM07477', 622, '2012', 'Ferrari', '458 Italia', 'Charcoal Black', '2017-12-13', 12340),
  171. ('JH4KA7650PC002520', 622, '2017', 'Lamborghini', 'Aventador S', 'Lime Green', '2018-01-01', 653),
  172. ('WP0AA0941HN450519', 622, '2015', 'Porsche', '911 Turbo S', 'Silver', '2018-02-02', 123),
  173. ('1HGEM21991L005461', 622, '2016', 'Mercedes', 'AMG GT-R', 'Crazy Green', '2018-03-03', 9034),
  174. ('4S3BD4353T7209207', 622, '2014', 'Dodge', 'Viper ACR', 'Black and White', '2018-04-04', 9880),
  175. ('JH4DC4433RS801008', 622, '2019', 'Ford', 'Mustang GT350', 'Blue', '2018-05-05', 8830),
  176. ('WP0CD2A91BS773285', 622, '2008', 'Lotus', 'Elise', 'Orange', '2018-06-06', 7188),
  177. ('5HD1CT3157K417590', 622, '2018', 'BMW', 'M4 GTS', 'Red', '2018-01-02', 9777),
  178. ('YS3DF78K527013330', 622, '2013', 'Jaguar', 'F-Type SVR', 'Wild Blue', '2018-01-12', 6554)
  179. ;
  180.  
  181. -- -
  182. CREATE TABLE purchases (
  183. purch_id INTEGER,
  184. purch_store_id INTEGER NOT NULL,
  185. purch_salesperson INTEGER NOT NULL,
  186. purch_vin CHAR(17) NOT NULL,
  187. purch_cust_id INTEGER NOT NULL,
  188. purch_price DECIMAL(10,2) NOT NULL,
  189. purch_tax DECIMAL(10,2),
  190. purch_title DECIMAL(10,2),
  191. purch_license DECIMAL(10,2),
  192. purch_sold_mileage INTEGER NOT NULL,
  193. purch_date DATE NOT NULL DEFAULT CURRENT_DATE,
  194. CONSTRAINT fk_purch_store_id FOREIGN KEY (purch_store_id) REFERENCES stores(store_id),
  195. CONSTRAINT fk_purch_salesperson FOREIGN KEY (purch_salesperson) REFERENCES employees(emp_id),
  196. CONSTRAINT fk_purch_vin FOREIGN KEY (purch_vin) REFERENCES inventory(inv_vin),
  197. CONSTRAINT fk_purch_cust_id FOREIGN KEY (purch_cust_id) REFERENCES customers(cust_id),
  198. PRIMARY KEY(purch_id)
  199. );
  200.  
  201. CREATE INDEX purch_date_idx
  202. ON purchases(purch_date);
  203.  
  204.  
  205. INSERT INTO purchases (purch_id, purch_store_id, purch_salesperson, purch_vin, purch_cust_id, purch_price, purch_tax, purch_title, purch_license, purch_sold_mileage, purch_date)
  206. VALUES (1443, 622, 1002, '1ZVBP8AM0D5265429', 10001, 45000, 23.12, 134.23, 134.23, 1236, '2017-01-01'),
  207. (1798, 622, 1003, '4S3BK6354S6355265', 10009, 12000, 34.12, 34.23, 34.23, 123002, '2017-02-01'),
  208. (1427, 622, 1004, '1ZVFT80N475211367', 10009, 23000, 73.12, 445.23, 445.23, 45025, '2017-03-01'),
  209. (1518, 622, 1005, 'JS3TX92V364102160', 10007, 550000, 5432.12, 2324.23, 2324.23, 7023, '2017-04-01'),
  210. (1536, 622, 1006, 'YV1LZ5647W2469314', 10006, 14000, 234.12, 2234.23, 2234.23, 83406, '2017-05-01'),
  211. (1446, 622, 1007, '1J4BA3H10AL171412', 10008, 125000, 343.12, 6234.23, 6234.23, 28, '2017-06-01'),
  212. (1938, 622, 1008, '1G4GJ11Y9HP422546', 10005, 90000, 654.12, 4234.23, 4234.23, 129, '2017-07-01'),
  213. (1710, 622, 1009, 'JH4KA7532NC036794', 10009, 150000, 124.12, 3234.23, 3234.23, 24002, '2017-08-01'),
  214. (1685, 622, 1010, 'JG1MR215XJK752025', 10009, 65000000, 42344.12, 91234.23, 91234.23, 121, '2017-09-01'),
  215. (1943, 622, 1011, '1N6DD21S4WC303918', 10008, 12000000, 2345.12, 71234.23, 71234.23, 1232, '2017-10-01'),
  216. (1911, 622, 1012, 'JN8AZ2NE5C9016953', 10007, 1500000, 1254.12, 21234.23, 21234.23, 5604, '2017-11-01'),
  217. (1092, 622, 1013, '4F4ZR17V7XTM07477', 10006, 360000, 43.12, 1634.23, 1634.23, 12343, '2017-12-01'),
  218. (1689, 622, 1014, 'JH4KA7650PC002520', 10005, 430000, 654.12, 1134.23, 1134.23, 656, '2018-01-01'),
  219. (1089, 622, 1015, 'WP0AA0941HN450519', 10004, 185000, 123.12, 4234.23, 4234.23, 127, '2018-03-01'),
  220. (1842, 622, 1016, '1HGEM21991L005461', 10003, 160000, 32.12, 2234.23, 2234.23, 9038, '2018-05-01'),
  221. (1447, 622, 1017, '4S3BD4353T7209207', 10002, 95000, 366.12, 1234.23, 1234.23, 9889, '2018-07-01')
  222. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!