Advertisement
Windsofthepast

Untitled

Mar 21st, 2016
177
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 6.05 KB | None | 0 0
  1. CREATE DATABASE Prints;
  2.  
  3. USE Prints;
  4.  
  5. CREATE TABLE Customers
  6. (customer_id INT PRIMARY KEY,
  7. customer_name CHAR(20),
  8. customer_add CHAR(50),
  9. customer_city CHAR(50),
  10. customer_state CHAR(2),
  11. customer_zip CHAR (5),
  12. customer_phone CHAR(12));
  13.  
  14. CREATE TABLE items
  15. (item_id INT PRIMARY KEY,
  16. title CHAR(50),
  17. artist CHAR(50),
  18. unit_price DECIMAL(6,2),
  19. retail_price DECIMAL(6,2),
  20. on_hand INT);
  21.  
  22. CREATE TABLE orders
  23. (order_id INT PRIMARY KEY,
  24. customer_id INT,
  25. order_date CHAR(10),
  26. ship_date CHAR(10));
  27.  
  28. CREATE TABLE orderline
  29. (order_id INT,
  30. item_id INT,
  31. order_qty INT);
  32.  
  33. INSERT INTO Customers
  34. VALUES
  35. ('1000', 'Cora Blanca', '1555 Seminole Ct.', 'Charlotte',
  36. 'NC', '28210', '704/552.1810');
  37.  
  38. INSERT INTO Customers
  39. VALUES
  40. ('1100', 'Yash Reed', '878 Madison Ave.', 'Greensboro',
  41. 'NC', '27407', '336/316-5434');
  42.  
  43. INSERT INTO Customers
  44. VALUES
  45. ('1200', 'John Mills', '4200 Olive Ave.', 'Columbia',
  46. 'SC', '29206', '803/432.6225');
  47.  
  48. INSERT INTO Customers
  49. VALUES
  50. ('1300', 'David Cox', '608 Old Post Rd.', 'Decatur',
  51.  'GA', '30030', '404/243.7379');
  52.  
  53. INSERT INTO Customers
  54. VALUES
  55. ('1400', 'Tina Evans', '235 Easton Ave.', 'Jacksonville',
  56.  'FL', '32221', '904/992-7234');
  57.  
  58. INSERT INTO Customers
  59. VALUES
  60. ('1500', 'Will Allen', '2508 W. Shaw Rd.', 'Raleigh',
  61. 'NC', '27542', '919/809.2546');
  62.  
  63. INSERT INTO Customers
  64. VALUES
  65. ('1600', 'James Boyd', '200 Pembury Ln.', 'Columbia',
  66. 'SC', '29206', '803/432-7600');
  67.  
  68. INSERT INTO Customers
  69. VALUES
  70. ('1700', 'Will Parsons', '4990 S. Pine St.', 'Raleigh',
  71. 'NC', '27545', '919/355/0034');
  72.  
  73. INSERT INTO Customers
  74. VALUES
  75. ('1800', 'Walter Kelly', '1200 Little St.', 'Columbia',
  76. 'SC', '29206', '803/432-1987');
  77.  
  78. INSERT INTO Customers
  79. VALUES
  80. ('1900', 'Ann Damian', '7822 N. ridge Rd.', 'Jacksonville',
  81. 'FL', '32216', '904/725-4672');
  82.  
  83. INSERT INTO Customers
  84. VALUES
  85. ('2000', 'Grace Hull', '4090 Caldweld St.', 'Charlotte',
  86. 'NC', '28205', '704/372/9000');
  87.  
  88.  
  89. INSERT INTO Customers
  90. VALUES
  91. ('2100', 'Jane Brown', '3320 W. Main St.', 'Charlotte',
  92. 'NC', '28210', '704/372/9000');
  93.  
  94. INSERT INTO Customers
  95. VALUES
  96. ('2200', 'Betty Draper', '1600 Sardis Rd.', 'Sarasota',
  97. 'FL', '32441', '918/941-9121');
  98.  
  99. INSERT INTO items
  100. VALUES
  101. ('100', 'Under the Sun', 'Donald Arley', '46.80', '93.6', '340');
  102.  
  103. INSERT INTO items
  104. VALUES
  105. ('200', 'Dark Lady', 'Keith Morris', '120.99', '241.98', '250');
  106.  
  107. INSERT INTO items
  108. VALUES
  109. ('300', 'Happy Days', 'Andrea Reid', '78.00', '156.00', '210');
  110.  
  111. INSERT INTO items
  112. VALUES
  113. ('350', 'Top of the Mountain', 'Janice Jones', '110.00', '220.00', '290');
  114.  
  115. INSERT INTO items
  116. VALUES
  117. ('400', 'Strees from Old', 'Sharon Brune', '123.00', '246.00', '320');
  118.  
  119. INSERT INTO items
  120. VALUES
  121. ('450', 'The Hunt', 'Walter Alford', '39.99', '79.98', '390');
  122.  
  123. INSERT INTO items
  124. VALUES
  125. ('600', 'Rainbow Row', 'Judy Ford', '46.00', '92.00', '350');
  126.  
  127. INSERT INTO items
  128. VALUES
  129. ('700', 'Skies Above', 'Alexander Wilson', '98.00', '196.00', '275');
  130.  
  131. INSERT INTO items
  132. VALUES
  133. ('800', 'The Seas and Moon', 'Susan Beeler', '67.81', '135.62', '235');
  134.  
  135. INSERT INTO items
  136. VALUES
  137. ('850', 'Greek Isles', 'Benjamin Caudle', '76.00', '152.00', '300');
  138.  
  139. INSERT INTO orders
  140. VALUES
  141. ('1', '1200', '2013-10-23', '2013-10-28');
  142.  
  143. INSERT INTO orders
  144. VALUES
  145. ('2', '1500', '2013-10-30', '2013-11-03');
  146.  
  147. INSERT INTO orders
  148. VALUES
  149. ('3', '1500', '2013-11-09', '2013-11-14');
  150.  
  151. INSERT INTO orders
  152. VALUES
  153. ('4', '2100', '2013-11-15', '2013-11-20');
  154.  
  155. INSERT INTO orders
  156. VALUES
  157. ('5', '1600', '2013-11-15', '2013-11-20');
  158.  
  159. INSERT INTO orders
  160. VALUES
  161. ('6', '1900', '2013-12-15', '2013-12-19');
  162.  
  163. INSERT INTO orders
  164. VALUES
  165. ('7', '2200', '2013-12-18', '2013-12-22');
  166.  
  167. INSERT INTO orders
  168. VALUES
  169. ('8', '1600', '2013-12-20', '2013-12-22');
  170.  
  171. INSERT INTO orders
  172. VALUES
  173. ('9', '1000', '2014-01-18', '2014-01-23');
  174.  
  175. INSERT INTO orders
  176. VALUES
  177. ('10', '2200', '2014-01-31', '2014-02-04');
  178.  
  179. INSERT INTO orders
  180. VALUES
  181. ('11', '1500', '2014-02-01', '2014-02-06');
  182.  
  183. INSERT INTO orders
  184. VALUES
  185. ('12', '1400', '2014-02-27', '2014-03-02');
  186.  
  187. INSERT INTO orders
  188. VALUES
  189. ('13', '1100', '2014-03-10', '2014-03-15');
  190.  
  191. INSERT INTO orders
  192. VALUES
  193. ('14', '1400', '2014-03-14', '2014-03-19');
  194.  
  195. INSERT INTO orderline
  196. VALUES
  197. ('1', '800', '2');
  198.  
  199. INSERT INTO orderline
  200. VALUES
  201. ('1', '600', '1');
  202.  
  203. INSERT INTO orderline
  204. VALUES
  205. ('2', '700', '3');
  206.  
  207. INSERT INTO orderline
  208. VALUES
  209. ('2', '300', '2');
  210.  
  211. INSERT INTO orderline
  212. VALUES
  213. ('3', '850', '1');
  214.  
  215. INSERT INTO orderline
  216. VALUES
  217. ('4', '200', '4');
  218.  
  219. INSERT INTO orderline
  220. VALUES
  221. ('4', '100', '1');
  222.  
  223. INSERT INTO orderline
  224. VALUES
  225. ('4', '850', '1');
  226.  
  227. INSERT INTO orderline
  228. VALUES
  229. ('5', '450', '1');
  230.  
  231. INSERT INTO orderline
  232. VALUES
  233. ('6', '800', '2');
  234.  
  235. INSERT INTO orderline
  236. VALUES
  237. ('7', '300', '2');
  238.  
  239. INSERT INTO orderline
  240. VALUES
  241. ('7', '600', '2');
  242.  
  243. INSERT INTO orderline
  244. VALUES
  245. ('8', '100', '1');
  246.  
  247. INSERT INTO orderline
  248. VALUES
  249. ('9', '100', '3');
  250.  
  251. INSERT INTO orderline
  252. VALUES
  253. ('10', '450', '6');
  254.  
  255. INSERT INTO orderline
  256. VALUES
  257. ('10', '600', '8');
  258.  
  259. INSERT INTO orderline
  260. VALUES
  261. ('10', '200', '4');
  262.  
  263. INSERT INTO orderline
  264. VALUES
  265. ('11', '700', '2');
  266.  
  267. INSERT INTO orderline
  268. VALUES
  269. ('12', '300', '3');
  270.  
  271. INSERT INTO orderline
  272. VALUES
  273. ('12', '700', '4');
  274.  
  275. INSERT INTO orderline
  276. VALUES
  277. ('13', '200', '2');
  278.  
  279. INSERT INTO orderline
  280. VALUES
  281. ('13', '600', '10');
  282.  
  283. INSERT INTO orderline
  284. VALUES
  285. ('13', '450', '4');
  286.  
  287. INSERT INTO orderline
  288. VALUES
  289. ('14', '700', '8');
  290.  
  291. INSERT INTO orderline
  292. VALUES
  293. ('14', '200', '6');
  294.  
  295. INSERT INTO orderline
  296. VALUES
  297. ('14', '800', '4');
  298.  
  299. INSERT INTO orderline
  300. VALUES
  301. ('14', '450', '2');
  302.  
  303. /*
  304.    Create a view named orders. It consists of
  305.    the item_id, title, artist, unit_price, and
  306.    order_qty for every print ordered in the range
  307.    of 2014-01-01 and 2014-02-28.
  308. */
  309.  
  310. DROP VIEW if exists orders_v;
  311.  
  312. CREATE VIEW orders_v AS
  313.   SELECT items.item_id, title, artist, unit_price,
  314.   order_qty
  315.   FROM items, orderline, orders
  316.   WHERE items.item_id = orderline.item_id
  317.   AND orderline.order_id = orders.order_id
  318.   AND orders.order_date like '2014-01%'
  319.   OR orders.order_date like '2014-02%'
  320.   GROUP BY items.item_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement