API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 25th, 2018
170
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.60 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. -- ***
  25. CREATE TABLE employee_types (
  26. emp_id INTEGER,
  27. role_literal VARCHAR(128) NOT NULL,
  28. PRIMARY KEY(emp_id)
  29. );
  30.  
  31. INSERT INTO employee_types (emp_id, role_literal)
  32. VALUES (0001, 'Manager')
  33. ,(0002, 'Sales')
  34. ,(0003, 'Support')
  35. ;
  36.  
  37. -- ***
  38. CREATE TABLE customers (
  39. cust_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 10000, INCREMENT BY 1),
  40. cust_firstname VARCHAR(128) NOT NULL,
  41. cust_lastname VARCHAR(128) NOT NULL,
  42. PRIMARY KEY(cust_id)
  43. );
  44.  
  45. INSERT INTO customers (cust_firstname, cust_lastname)
  46. VALUES ('Johnny', 'Morris')
  47. ,('Jack', 'Johnson')
  48. ,('Billy', 'Williams')
  49. ,('BB', 'King')
  50. ,('Robert', 'Plant')
  51. ,('Jimmy', 'Falon')
  52. ,('Wycleff', 'Jean')
  53. ,('Tim', 'Robbins')
  54. ,('Bruce', 'Willis')
  55. ,('Mel', 'Gibson')
  56. ;
  57.  
  58. -- ^^
  59. CREATE TABLE addresses (
  60. add_id INTEGER,
  61. add_type_id INTEGER NOT NULL,
  62. city VARCHAR(128) NOT NULL,
  63. state VARCHAR(128) NOT NULL,
  64. zip VARCHAR(11) NOT NULL,
  65. residential CHAR(1) NOT NULL,
  66. CONSTRAINT fk_add_type_id FOREIGN KEY (add_type_id) REFERENCES address_types(add_type_id),
  67. PRIMARY KEY(add_id)
  68. );
  69.  
  70. INSERT INTO addresses (add_id, add_type_id, city, state, zip, residential)
  71. VALUES (1111, 4321, 'Springfield', 'Missouri', '65804', '0')
  72. ,(1212, 4444, 'Springfield', 'Missouri', '65804', '1')
  73. ,(1313, 4444, 'Springfield', 'Missouri', '65804', '1')
  74. ,(1414, 4444, 'Springfield', 'Missouri', '65804', '1')
  75. ,(1515, 4444, 'Springfield', 'Illinois', '47895', '1')
  76. ,(1616, 4444, 'Springfield', 'Missouri', '65804', '1')
  77. ,(1717, 4444, 'Springfield', 'Missouri', '65804', '1')
  78. ,(1818, 4444, 'Springfield', 'Illinois', '47895', '1')
  79. ,(1919, 4444, 'Springfield', 'Missouri', '65804', '1')
  80. ,(2121, 4444, 'Springfield', 'Illinois', '47895', '1')
  81. ,(2222, 4444, 'Springfield', 'Missouri', '65804', '1')
  82. ,(2323, 4444, 'Springfield', 'Missouri', '65804', '1')
  83. ,(2424, 4444, 'Springfield', 'Illinois', '47895', '1')
  84. ,(2525, 4444, 'Springfield', 'Missouri', '65804', '1')
  85. ,(2626, 4444, 'Springfield', 'Missouri', '65804', '1')
  86. ,(2727, 4444, 'Springfield', 'Illinois', '47895', '1')
  87. ,(2828, 4444, 'Springfield', 'Missouri', '65804', '1')
  88. ,(2929, 4444, 'Springfield', 'Massachusettes', '12345', '1')
  89. ,(3030, 4444, 'Springfield', 'Missouri', '65804', '1')
  90. ,(3131, 4444, 'Springfield', 'Missouri', '65804', '1')
  91. ;
  92.  
  93. -- ^^
  94. CREATE TABLE stores (
  95. store_id INTEGER,
  96. store_name VARCHAR(128) NOT NULL,
  97. store_add_id INTEGER NOT NULL,
  98. tax_percent DECIMAL(3, 2) NOT NULL,
  99. CONSTRAINT fk_store_add_id FOREIGN KEY (store_add_id) REFERENCES addresses(add_id),
  100. CONSTRAINT ck_store_name CHECK (LENGTH(store_name) > 3),
  101. PRIMARY KEY(store_id)
  102. );
  103.  
  104. CREATE INDEX store_name_idx
  105. ON stores(store_name);
  106.  
  107. INSERT INTO stores (store_id, store_name, store_add_id, tax_percent)
  108. VALUES (622, 'Bike Store', 1111, 0.12);
  109.  
  110. -- vv
  111. CREATE TABLE employees (
  112. emp_id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1000, INCREMENT BY 1),
  113. emp_role_id INTEGER NOT NULL,
  114. emp_store_id INTEGER NOT NULL,
  115. emp_firstname VARCHAR(128) NOT NULL,
  116. emp_lastname VARCHAR(128) NOT NULL,
  117. emp_phone VARCHAR(128) NOT NULL,
  118. emp_email VARCHAR(128) UNIQUE NOT NULL,
  119. emp_address INTEGER NOT NULL,
  120. CONSTRAINT fk_emp_role_id FOREIGN KEY (emp_role_id) REFERENCES employee_types(emp_id),
  121. CONSTRAINT fk_emp_store_id FOREIGN KEY (emp_store_id) REFERENCES stores(store_id),
  122. CONSTRAINT fk_emp_address FOREIGN KEY (emp_address) REFERENCES addresses(add_id),
  123. PRIMARY KEY (emp_id)
  124. );
  125.  
  126. INSERT INTO employees (emp_role_id, emp_store_id, emp_firstname, emp_lastname, emp_phone, emp_email, emp_address)
  127. VALUES (0001, 622, 'Jody', 'Pretz', '234-1234', 'pretz@gmail.com', 1212)
  128. ,(0002, 622, 'Bob', 'Smith', '563-3321', 'smith@gmail.com', 1313)
  129. ,(0002, 622, 'Joe', 'Ledger', '123-5134', 'wow@gmail.com', 1414)
  130. ,(0002, 622, 'July', 'Cobb', '753-7753', 'yikes@gmail.com', 1515)
  131. ,(0002, 622, 'Sindey', 'Rock', '714-7765', 'aaah@gmail.com', 1616)
  132. ,(0002, 622, 'Mike', 'Pocket', '582-2334', 'rough@gmail.com', 1717)
  133. ,(0002, 622, 'Bobby', 'Walt', '678-5689', 'dewd@gmail.com', 1818)
  134. ,(0002, 622, 'Allard', 'Free', '333-2221', 'woah@gmail.com', 1919)
  135. ,(0002, 622, 'Fred', 'Walder', '124-5534', 'aased@gmail.com', 2121)
  136. ,(0002, 622, 'Mark', 'Mahan', '675-1321', 'mailacc@gmail.com', 2222)
  137. ,(0002, 622, 'Mitch', 'Traush', '367-5356', 'yeet@gmail.com', 2323)
  138. ,(0002, 622, 'Clyde', 'Maky', '332-4621', 'guyr@gmail.com', 2424)
  139. ,(0002, 622, 'Klyde', 'Loore', '123-5555', 'leet@gmail.com', 2525)
  140. ,(0002, 622, 'Rich', 'Quetz', '770-1111', 'ajax@gmail.com', 2626)
  141. ,(0002, 622, 'Ron', 'Well', '923-3351', 'aasey@gmail.com', 2727)
  142. ,(0002, 622, 'Tim', 'Pool', '122-6678', 'Looper@gmail.com', 2828)
  143. ,(0002, 622, 'Shawnda', 'Zed', '111-1112', 'profane@gmail.com', 2929)
  144. ,(0003, 622, 'Precious', 'Yeary', '222-2238', 'rocky@gmail.com', 3030)
  145. ,(0003, 622, 'Trudy', 'Green', '333-1987', 'loik@gmail.com', 3131)
  146. ;
  147.  
  148. -- vv
  149. CREATE TABLE inventory (
  150. inv_vin CHAR(17),
  151. inv_store_id INTEGER NOT NULL,
  152. inv_year CHAR(4) NOT NULL,
  153. inv_make VARCHAR(128) NOT NULL,
  154. inv_model VARCHAR(128) NOT NULL,
  155. inv_color VARCHAR(128) NOT NULL,
  156. inv_acquired_date DATE NOT NULL,
  157. inv_mileage INTEGER NOT NULL,
  158. CONSTRAINT fk_inv_store_id FOREIGN KEY (inv_store_id) REFERENCES stores(store_id),
  159. PRIMARY KEY (inv_vin)
  160. );
  161.  
  162. INSERT INTO inventory (inv_vin, inv_store_id, inv_year, inv_make, inv_model, inv_color, inv_acquired_date, inv_mileage)
  163. VALUES ('1ZVBP8AM0D5265429', 622, '2018', 'Ford', 'Mustang GT', 'Black', '2017-01-02', 1234)
  164. ,('4S3BK6354S6355265', 622, '2008', 'Inifiniti', 'G35', 'Crimson Red', '2017-02-03', 123000)
  165. ,('1ZVFT80N475211367', 622, '2013', 'Cheverolet', 'Camaro', 'Black and White', '2017-03-04', 45020)
  166. ,('JS3TX92V364102160', 622, '1995', 'Acura', 'NSX', 'White', '2017-04-05', 700)
  167. ,('YV1LZ5647W2469314', 622, '2008', 'Pontiac', 'G8 GXP', 'Maroon', '2017-05-06', 83400)
  168. ,('1J4BA3H10AL171412', 622, '2019', 'Cheverolet', 'Corvette ZR1', 'Midnight Blue', '2017-06-07', 25)
  169. ,('1G4GJ11Y9HP422546', 622, '2018', 'Dodge', 'Challenger Demon', 'Red', '2017-07-08', 123)
  170. ,('JH4KA7532NC036794', 622, '2012', 'Maserati', 'Gran Turismo', 'Blue', '2017-08-09', 24000)
  171. ,('JG1MR215XJK752025', 622, '1965', 'Ferrari', '250 GTO', 'Rosso Corsa', '2017-09-10', 120)
  172. ,('1N6DD21S4WC303918', 622, '1999', 'McLaren', 'F1', 'Yellow', '2017-10-11', 1230)
  173. ,('JN8AZ2NE5C9016953', 622, '2005', 'Porsche', 'Carrera GT', 'Silver', '2017-11-12', 5600)
  174. ,('4F4ZR17V7XTM07477', 622, '2012', 'Ferrari', '458 Italia', 'Charcoal Black', '2017-12-13', 12340)
  175. ,('JH4KA7650PC002520', 622, '2017', 'Lamborghini', 'Aventador S', 'Lime Green', '2018-01-01', 653)
  176. ,('WP0AA0941HN450519', 622, '2015', 'Porsche', '911 Turbo S', 'Silver', '2018-02-02', 123)
  177. ,('1HGEM21991L005461', 622, '2016', 'Mercedes', 'AMG GT-R', 'Crazy Green', '2018-03-03', 9034)
  178. ,('4S3BD4353T7209207', 622, '2014', 'Dodge', 'Viper ACR', 'Black and White', '2018-04-04', 9880)
  179. ,('JH4DC4433RS801008', 622, '2019', 'Ford', 'Mustang GT350', 'Blue', '2018-05-05', 8830)
  180. ,('WP0CD2A91BS773285', 622, '2008', 'Lotus', 'Elise', 'Orange', '2018-06-06', 7188)
  181. ,('5HD1CT3157K417590', 622, '2018', 'BMW', 'M4 GTS', 'Red', '2018-01-02', 9777)
  182. ,('YS3DF78K527013330', 622, '2013', 'Jaguar', 'F-Type SVR', 'Wild Blue', '2018-01-12', 6554)
  183. ;
  184.  
  185. -- -
  186. CREATE TABLE purchases (
  187. purch_id INTEGER,
  188. purch_store_id INTEGER NOT NULL,
  189. purch_salesperson INTEGER NOT NULL,
  190. purch_vin CHAR(17) NOT NULL,
  191. purch_cust_id INTEGER NOT NULL,
  192. purch_price DECIMAL(10,2) NOT NULL,
  193. purch_tax DECIMAL(10,2),
  194. purch_title DECIMAL(10,2),
  195. purch_license DECIMAL(10,2),
  196. purch_sold_mileage INTEGER NOT NULL,
  197. purch_date DATE NOT NULL DEFAULT CURRENT_DATE,
  198. CONSTRAINT fk_purch_store_id FOREIGN KEY (purch_store_id) REFERENCES stores(store_id),
  199. CONSTRAINT fk_purch_salesperson FOREIGN KEY (purch_salesperson) REFERENCES employees(emp_id),
  200. CONSTRAINT fk_purch_vin FOREIGN KEY (purch_vin) REFERENCES inventory(inv_vin),
  201. CONSTRAINT fk_purch_cust_id FOREIGN KEY (purch_cust_id) REFERENCES customers(cust_id),
  202. PRIMARY KEY(purch_id)
  203. );
  204.  
  205. CREATE INDEX purch_date_idx
  206. ON purchases(purch_date);
  207.  
  208.  
  209. 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)
  210. VALUES (1443, 622, 1007, '1ZVBP8AM0D5265429', 10001, 45000, 23.12, 134.23, 134.23, 1236, '2017-01-01')
  211. ,(1798, 622, 1003, '4S3BK6354S6355265', 10009, 12000, 34.12, 34.23, 34.23, 123002, '2017-02-01')
  212. ,(1427, 622, 1004, '1ZVFT80N475211367', 10009, 23000, 73.12, 445.23, 445.23, 45025, '2017-03-01')
  213. ,(1518, 622, 1003, 'JS3TX92V364102160', 10007, 550000, 5432.12, 2324.23, 2324.23, 7023, '2017-04-01')
  214. ,(1536, 622, 1006, 'YV1LZ5647W2469314', 10006, 14000, 234.12, 2234.23, 2234.23, 83406, '2017-05-01')
  215. ,(1446, 622, 1007, '1J4BA3H10AL171412', 10008, 125000, 343.12, 6234.23, 6234.23, 28, '2017-06-01')
  216. ,(1938, 622, 1008, '1G4GJ11Y9HP422546', 10005, 90000, 654.12, 4234.23, 4234.23, 129, '2017-07-01')
  217. ,(1710, 622, 1003, 'JH4KA7532NC036794', 10009, 150000, 124.12, 3234.23, 3234.23, 24002, '2017-08-01')
  218. ,(1685, 622, 1010, 'JG1MR215XJK752025', 10009, 65000000, 42344.12, 91234.23, 91234.23, 121, '2017-09-01')
  219. ,(1943, 622, 1007, '1N6DD21S4WC303918', 10008, 12000000, 2345.12, 71234.23, 71234.23, 1232, '2017-10-01')
  220. ,(1911, 622, 1012, 'JN8AZ2NE5C9016953', 10007, 1500000, 1254.12, 21234.23, 21234.23, 5604, '2017-11-01')
  221. ,(1092, 622, 1013, '4F4ZR17V7XTM07477', 10006, 360000, 43.12, 1634.23, 1634.23, 12343, '2017-12-01')
  222. ,(1689, 622, 1007, 'JH4KA7650PC002520', 10005, 430000, 654.12, 1134.23, 1134.23, 656, '2018-01-01')
  223. ,(1089, 622, 1015, 'WP0AA0941HN450519', 10004, 185000, 123.12, 4234.23, 4234.23, 127, '2018-03-01')
  224. ,(1842, 622, 1007, '1HGEM21991L005461', 10003, 160000, 32.12, 2234.23, 2234.23, 9038, '2018-05-01')
  225. ,(1447, 622, 1007, '4S3BD4353T7209207', 10002, 95000, 366.12, 1234.23, 1234.23, 9889, '2018-07-01')
  226. ;
  227.  
  228. -- #6: All cars acquired in 2017
  229. SELECT i.inv_vin AS vin,
  230. i.inv_make AS make,
  231. i.inv_model AS model,
  232. i.inv_year AS model_year,
  233. i.inv_color AS color,
  234. i.inv_mileage AS mileage,
  235. i.inv_acquired_date AS acquired,
  236. i.inv_store_id AS store_id,
  237. s.store_name,
  238. s.tax_percent AS store_tax,
  239. s.store_add_id AS address_id,
  240. a.city,
  241. a.state,
  242. a.zip
  243. FROM inventory i
  244. JOIN stores s
  245. ON i.inv_store_id = s.store_id
  246. JOIN addresses a
  247. ON s.store_add_id = a.add_id
  248. JOIN address_types a_t
  249. ON a.add_type_id = a_t.add_type_id
  250. WHERE i.inv_acquired_date BETWEEN '2017-01-01' AND '2017-12-31'
  251. ;
  252.  
  253. -- #7: All cars sold in 2018, 1st quarter
  254. SELECT *
  255. FROM purchases p
  256. JOIN inventory i
  257. ON p.purch_vin = i.inv_vin
  258. JOIN stores s
  259. ON s.store_id = p.purch_store_id
  260. JOIN customers c
  261. ON c.cust_id = p.purch_cust_id
  262. WHERE p.purch_date BETWEEN '2018-01-01' AND '2018-03-31'
  263. ;
  264.  
  265. -- #8: All cars that have not been sold
  266. SELECT i.inv_vin AS vin,
  267. i.inv_make AS make,
  268. i.inv_model AS model,
  269. i.inv_year AS modelyear,
  270. i.inv_color AS color,
  271. i.inv_mileage AS mileage,
  272. i.inv_acquired_date AS acquired,
  273. i.inv_store_id AS store_id,
  274. s.store_name,
  275. s.tax_percent AS store_tax,
  276. s.store_add_id AS address_id,
  277. a.city,
  278. a.state,
  279. a.zip
  280. FROM inventory i
  281. JOIN stores s
  282. ON i.inv_store_id = s.store_id
  283. JOIN addresses a
  284. ON s.store_add_id = a.add_id
  285. JOIN address_types a_t
  286. ON a.add_type_id = a_t.add_type_id
  287. LEFT JOIN purchases p
  288. ON p.purch_vin = i.inv_vin
  289. WHERE p.purch_vin IS NULL
  290. ;
  291.  
  292. -- #9: Sales Report
  293. SELECT e.emp_id AS employee_id,
  294. e_t.role_literal AS employee_role,
  295. e.emp_firstname AS employee_firstname,
  296. e.emp_lastname AS employee_lastname,
  297. e.emp_phone AS employee_phone,
  298. e.emp_email AS employee_email,
  299. e.emp_address AS employee_address_id,
  300. e_a.city AS employee_city,
  301. e_a.state AS employee_state,
  302. e_a.zip AS employee_zip,
  303. e.emp_store_id AS store_id,
  304. s.store_name AS store_name,
  305. s.store_add_id AS store_address_id,
  306. s_a.city AS store_city,
  307. s_a.state AS store_state,
  308. s_a.zip AS store_zip,
  309. p.purch_id AS sale_id,
  310. i.inv_acquired_date AS acquired,
  311. p.purch_date AS sale_date,
  312. p.purch_vin AS sold_vin,
  313. i.inv_year AS model_year,
  314. i.inv_make AS make,
  315. i.inv_model AS model,
  316. i.inv_color AS color,
  317. p.purch_sold_mileage AS mileage,
  318. p.purch_price AS sold_for,
  319. p.purch_tax AS sale_tax,
  320. p.purch_title AS sale_title,
  321. p.purch_license AS sale_license,
  322. c.cust_id AS customer_id,
  323. c.cust_firstname AS customer_firstname,
  324. c.cust_lastname AS customer_lastname
  325. FROM employees e
  326. JOIN employee_types e_t
  327. ON e.emp_role_id = e_t.emp_id
  328. JOIN stores s
  329. ON e.emp_store_id = s.store_id
  330. JOIN addresses s_a
  331. ON s.store_add_id = s_a.add_id
  332. JOIN addresses e_a
  333. ON e.emp_address = e_a.add_id
  334. JOIN address_types sa_t
  335. ON s_a.add_type_id = sa_t.add_type_id
  336. JOIN address_types ea_t
  337. ON e_a.add_type_id = ea_t.add_type_id
  338. JOIN purchases p
  339. ON p.purch_salesperson = e.emp_id
  340. AND p.purch_store_id = s.store_id
  341. JOIN inventory i
  342. ON p.purch_vin = i.inv_vin
  343. AND s.store_id = i.inv_store_id
  344. JOIN customers c
  345. ON p.purch_cust_id = c.cust_id
  346. ;
  347.  
  348. -- #10: Salespersons who have not sold a car
  349. SELECT e.*
  350. FROM employees e LEFT OUTER JOIN purchases p
  351. ON purch_salesperson = emp_id WHERE purch_salesperson IS NULL AND emp_role_id = 0002;
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!