· 8 years ago · Apr 06, 2018, 02:54 PM
1# ---------------
2# MySQL Exercises
3# ---------------
4#
5# Given the following table formats:
6#
7# Table:
8# orders
9# Fields:
10# orderNumber
11# customerOid
12# orderInformationOid
13# productOid
14# totalPremium
15# purchaseDateTime
16#
17# Table:
18# customerData
19# Fields:
20# customerOid
21# firstName
22# middleInitial
23# lastName
24# street1
25# street2
26# city
27# state
28# zip
29#
30# Table:
31# products
32# Fields:
33# productOid
34# productName
35# companyOid
36#
37# Table:
38# companies
39# Fields:
40# companyOid
41# companyName
42# street1
43# street2
44# city
45# stateCode
46# zip
47#
48#
49# (1) Write a query to report the name, address and order number of the
50# 50 most recent orders.
51#
52# (2) Write a query to report all products purchased by residents of
53# Rhode Island.
54#
55# (3) Suppose we add another table, as follows:
56#
57# Table:
58# options
59# Fields:
60# orderNumber
61# optionName
62#
63# (3) Revise the query you formulated in step (1) to bring in the optionName
64# from this table. Even if there are no entries in this table, we still
65# want the order to to be displayed.
66#
67# (4) Given the table added in question (3), let us assume that we want
68# to add the option "Rental Car Coverage" to all previously
69# purchased orders from company "Jim Bob's Insurance". Assume that
70# no orders have this coverage already. What query would you build
71# to accomplish this?
72
73USE IMT
74
75# First clean any existing database
76DROP PROCEDURE IF EXISTS latest_orders;
77DROP PROCEDURE IF EXISTS state_products;
78DROP PROCEDURE IF EXISTS add_option;
79DROP TABLE IF EXISTS options;
80DROP TABLE IF EXISTS companies;
81DROP TABLE IF EXISTS products;
82DROP TABLE IF EXISTS customerData;
83DROP TABLE IF EXISTS orders;
84DROP DATABASE IF EXISTS IMT;
85
86# Create database and use it
87CREATE DATABASE IMT;
88USE IMT;
89
90# Add initial tables
91
92CREATE TABLE orders (
93 orderNumber INT,
94 customerOid INT,
95 orderInformationOid INT,
96 productOid INT,
97 totalPremium DECIMAL,
98 purchaseDateTime DATETIME
99);
100
101CREATE TABLE customerData (
102 customerOid INT,
103 firstName VARCHAR(50),
104 middleInitial VARCHAR(1),
105 lastName VARCHAR(50),
106 street1 VARCHAR(100),
107 street2 VARCHAR(100),
108 city VARCHAR(50),
109 state VARCHAR(2),
110 zip VARCHAR(10)
111);
112
113CREATE TABLE products (
114 productOid INT,
115 productName VARCHAR(50),
116 companyOid INT
117);
118
119CREATE TABLE companies (
120 companyOid INT,
121 companyName VARCHAR(100),
122 street1 VARCHAR(100),
123 street2 VARCHAR(100),
124 city VARCHAR(50),
125 stateCode VARCHAR(2),
126 zip VARCHAR(10)
127);
128
129# Populate tables with realistic test data
130
131INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (1,"IMT","10 Elm St","Warwick",NULL,"RI","12345");
132INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (2,"Lapis","11 Birch Ln","Suite 22","Cranston","NY","67890");
133INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (3,"Progeny","12 Holly Rd",NULL,"E Greenwich","VT","23456");
134INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (4,"RITE Solutions","13 Ginkgo Ct","Apt 7","W. Greenwich","NH","78901");
135INSERT INTO companies (companyOid, companyName, street1, street2, city, stateCode, zip) VALUES (5,"Anteon","14 Hazelnut Cir",NULL,"Exeter","MA","89456");
136INSERT INTO companies (companyOid, companyName) VALUES (6, "Jim Bob's Insurance");
137
138INSERT INTO products (productOid, productName, companyOid) VALUES (1,"Widget",5);
139INSERT INTO products (productOid, productName, companyOid) VALUES (2,"Whatchamacallit",4);
140INSERT INTO products (productOid, productName, companyOid) VALUES (3,"Whosawhatsit",3);
141INSERT INTO products (productOid, productName, companyOid) VALUES (4,"Ball",2);
142INSERT INTO products (productOid, productName, companyOid) VALUES (5,"Pre-owned Lexus", 6);
143
144INSERT INTO customerData (customerOid, firstName, middleInitial, lastName, street1, street2, city, state, zip) VALUES (1,"Nate","J","Washor","1 Main St",NULL,"Coventry","RI","02816");
145INSERT INTO customerData (customerOid, firstName, middleInitial, lastName, street1, street2, city, state, zip) VALUES (2,"Sammy",NULL,"Wu","2 Side Rd","Apt 3","Keene","NH","03431");
146INSERT INTO customerData (customerOid, firstName, middleInitial, lastName, street1, street2, city, state, zip) VALUES (3,"Zane","Y","Roo","3 Hidden Court",NULL,"Plymouth","MA","02860");
147
148INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (1,1,11,1,1.33,"2007-4-22 0:00:00");
149INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (2,1,12,2,5.55,"2007-4-27 0:00:00");
150INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (3,3,13,3,27.55,"2007-5-2 0:00:00");
151INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (4,2,14,4,23.55,"2007-5-7 0:00:00");
152INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (5,2,15,1,32.55,"2007-5-12 0:00:00");
153INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (6,3,16,2,28.55,"2007-5-17 0:00:00");
154INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (7,2,17,3,24.55,"2007-5-22 0:00:00");
155INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (8,2,18,4,33.55,"2007-5-27 0:00:00");
156INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (9,3,19,1,29.55,"2007-6-1 0:00:00");
157INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (10,2,20,2,25.55,"2007-6-6 0:00:00");
158INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (11,2,21,3,34.55,"2007-6-11 0:00:00");
159INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (12,3,22,4,30.55,"2007-6-16 0:00:00");
160INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (13,2,23,1,26.55,"2007-6-21 0:00:00");
161INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (14,2,24,2,22.55,"2007-6-26 0:00:00");
162INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (15,3,25,3,31.55,"2007-7-1 0:00:00");
163INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (16,2,26,4,27.55,"2007-7-6 0:00:00");
164INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (17,2,27,1,23.55,"2007-7-11 0:00:00");
165INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (18,3,28,2,32.55,"2007-7-16 0:00:00");
166INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (19,2,29,3,28.55,"2007-7-21 0:00:00");
167INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (20,2,30,4,24.55,"2007-7-26 0:00:00");
168INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (21,3,31,1,33.55,"2007-7-31 0:00:00");
169INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (22,2,32,2,29.55,"2007-8-5 0:00:00");
170INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (23,2,33,3,25.55,"2007-8-10 0:00:00");
171INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (24,3,34,4,34.55,"2007-8-15 0:00:00");
172INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (25,2,35,1,30.55,"2007-8-20 0:00:00");
173INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (26,2,36,2,26.55,"2007-8-25 0:00:00");
174INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (27,3,37,3,22.55,"2007-8-30 0:00:00");
175INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (28,2,38,4,31.55,"2007-9-4 0:00:00");
176INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (29,2,39,1,27.55,"2007-9-9 0:00:00");
177INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (30,3,40,2,23.55,"2007-9-14 0:00:00");
178INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (31,2,41,3,32.55,"2007-9-19 0:00:00");
179INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (32,2,42,4,28.55,"2007-9-24 0:00:00");
180INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (33,3,43,1,24.55,"2007-9-29 0:00:00");
181INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (34,2,44,2,33.55,"2007-10-4 0:00:00");
182INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (35,2,45,3,29.55,"2007-10-9 0:00:00");
183INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (36,3,46,4,25.55,"2007-10-14 0:00:00");
184INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (37,2,47,1,34.55,"2007-10-19 0:00:00");
185INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (38,2,48,2,30.55,"2007-10-24 0:00:00");
186INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (39,3,49,3,26.55,"2007-10-29 0:00:00");
187INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (40,2,50,4,22.55,"2007-11-3 0:00:00");
188INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (41,2,51,1,31.55,"2007-11-8 0:00:00");
189INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (42,3,52,2,27.55,"2007-11-13 0:00:00");
190INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (43,2,53,3,23.55,"2007-11-18 0:00:00");
191INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (44,2,54,4,32.55,"2007-11-23 0:00:00");
192INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (45,3,55,1,28.55,"2007-11-28 0:00:00");
193INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (46,2,56,2,24.55,"2007-12-3 0:00:00");
194INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (47,2,57,3,33.55,"2007-12-8 0:00:00");
195INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (48,3,58,4,29.55,"2007-12-13 0:00:00");
196INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (49,2,59,1,25.55,"2007-12-18 0:00:00");
197INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (50,2,60,2,34.55,"2007-12-23 0:00:00");
198INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (51,3,61,3,30.55,"2007-12-28 0:00:00");
199INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (52,2,62,4,26.55,"2008-1-2 0:00:00");
200INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (53,2,63,1,22.55,"2008-1-7 0:00:00");
201INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (54,3,64,2,31.55,"2008-1-12 0:00:00");
202INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (55,2,65,3,27.55,"2008-1-17 0:00:00");
203INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (56,2,66,4,23.55,"2008-1-22 0:00:00");
204INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (57,3,67,1,22.55,"2008-1-27 0:00:00");
205INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (58,2,68,2,28.55,"2008-2-1 0:00:00");
206INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (59,2,69,3,24.55,"2008-2-6 0:00:00");
207INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (60,3,70,4,33.55,"2008-2-11 0:00:00");
208INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (61,2,71,1,29.55,"2008-2-16 0:00:00");
209INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (62,2,72,2,25.55,"2008-2-21 0:00:00");
210INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (63,3,73,3,34.55,"2008-2-26 0:00:00");
211INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (64,2,74,4,30.55,"2008-3-2 0:00:00");
212INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (65,2,75,1,26.55,"2008-3-7 0:00:00");
213INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (66,3,76,2,22.55,"2008-3-12 0:00:00");
214INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (67,2,77,3,31.55,"2008-3-17 0:00:00");
215INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (68,2,78,4,27.55,"2008-3-22 0:00:00");
216INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (69,3,79,1,23.55,"2008-3-27 0:00:00");
217INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (70,2,80,2,32.55,"2008-4-1 0:00:00");
218INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (71,2,81,3,28.55,"2008-4-6 0:00:00");
219INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (72,3,82,4,24.55,"2008-4-11 0:00:00");
220INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (73,2,83,1,33.55,"2008-4-16 0:00:00");
221INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (74,2,84,2,29.55,"2008-4-21 0:00:00");
222INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (75,3,85,3,25.55,"2008-4-26 0:00:00");
223INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (76,2,86,4,34.55,"2008-5-1 0:00:00");
224INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (77,2,87,1,30.55,"2008-5-6 0:00:00");
225INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (78,3,88,2,26.55,"2008-5-11 0:00:00");
226INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (79,2,89,3,22.55,"2008-5-16 0:00:00");
227INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (80,2,90,4,31.55,"2008-5-21 0:00:00");
228INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (81,3,91,1,27.55,"2008-5-26 0:00:00");
229INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (82,2,92,2,23.55,"2008-5-31 0:00:00");
230INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (83,2,93,3,32.55,"2008-6-5 0:00:00");
231INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (84,3,94,4,28.55,"2008-6-10 0:00:00");
232INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (85,2,95,1,24.55,"2008-6-15 0:00:00");
233INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (86,2,96,2,33.55,"2008-6-20 0:00:00");
234INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (87,3,97,3,29.55,"2008-6-25 0:00:00");
235INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (88,2,98,4,25.55,"2008-6-30 0:00:00");
236INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (89,2,99,1,34.55,"2008-7-5 0:00:00");
237INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (90,3,100,2,30.55,"2008-7-10 0:00:00");
238INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (91,2,101,3,26.55,"2008-7-15 0:00:00");
239INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (92,2,102,4,22.55,"2008-7-20 0:00:00");
240INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (93,3,103,1,31.55,"2008-7-25 0:00:00");
241INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (94,2,104,2,27.55,"2008-7-30 0:00:00");
242INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (95,2,105,3,23.55,"2008-8-4 0:00:00");
243INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (96,3,106,4,32.55,"2008-8-9 0:00:00");
244INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (97,2,107,1,28.55,"2008-8-14 0:00:00");
245INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (98,2,108,2,24.55,"2008-8-19 0:00:00");
246INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (99,3,109,3,33.55,"2008-8-24 0:00:00");
247INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (100,2,110,4,29.55,"2008-8-29 0:00:00");
248INSERT INTO orders (orderNumber, customerOid, orderInformationOid, productOid, totalPremium, purchaseDateTime) VALUES (101,3,111,5,30.55,"2008-8-30 0:00:00");
249
250# Solutions...
251# ---
252# (1) Write a query to report the name, address and order number of the
253# 50 most recent orders.
254
255DELIMITER //
256
257CREATE PROCEDURE latest_orders
258(IN inMax INT)
259BEGIN
260 SELECT orders.orderNumber,
261 CONCAT_WS(' ',
262 NULLIF(customerData.firstName, ''),
263 NULLIF(customerData.middleInitial, ''),
264 NULLIF(customerData.lastName, '')
265 ) AS customerName,
266 CONCAT_WS(', ',
267 NULLIF(customerData.street1, ''),
268 NULLIF(customerData.street2, ''),
269 NULLIF(customerData.city, ''),
270 NULLIF(customerData.state, ''),
271 NULLIF(customerData.zip, '')
272 ) AS customerAddress
273 FROM orders INNER JOIN customerData
274 ON orders.customerOid = customerData.customerOid
275 ORDER BY orders.purchaseDateTime DESC LIMIT inMax;
276END//
277
278DELIMITER ;
279
280# Test results
281CALL latest_orders(50);
282
283# ---
284# (2) Write a query to report all products purchased by residents of
285# Rhode Island.
286
287DELIMITER //
288
289CREATE PROCEDURE state_products
290(IN inStateAbbrev VARCHAR(2))
291BEGIN
292 SELECT DISTINCT products.productName
293 FROM products INNER JOIN orders
294 ON products.productOid = orders.productOid INNER JOIN customerData
295 ON customerData.customerOid = orders.customerOid AND customerData.state = inStateAbbrev;
296END//
297
298DELIMITER ;
299
300# Test results
301CALL state_products("RI");
302
303# ---
304# (3) Revise the query you formulated in step (1) to bring in the optionName
305# from this table. Even if there are no entries in this table, we still
306# want the order to to be displayed.
307
308CREATE TABLE options (
309 orderNumber INT,
310 optionName VARCHAR(50)
311);
312
313DELIMITER //
314
315DROP PROCEDURE IF EXISTS latest_orders;
316CREATE PROCEDURE latest_orders
317(IN inMax INT)
318BEGIN
319 SELECT orders.orderNumber,
320 CONCAT_WS(' ',
321 NULLIF(customerData.firstName, ''),
322 NULLIF(customerData.middleInitial, ''),
323 NULLIF(customerData.lastName, '')
324 ) AS customerName,
325 CONCAT_WS(', ',
326 NULLIF(customerData.street1, ''),
327 NULLIF(customerData.street2, ''),
328 NULLIF(customerData.city, ''),
329 NULLIF(customerData.state, ''),
330 NULLIF(customerData.zip, '')
331 ) AS customerAddress,
332 options.optionName
333 FROM orders INNER JOIN customerData
334 ON orders.customerOid = customerData.customerOid LEFT OUTER JOIN options
335 ON options.orderNumber = orders.orderNumber
336 ORDER BY orders.purchaseDateTime DESC LIMIT inMax;
337END//
338
339DELIMITER ;
340
341# Test results
342CALL latest_orders(50);
343
344# ---
345# (4) Given the table added in question (3), let us assume that we want
346# to add the option "Rental Car Coverage" to all previously
347# purchased orders from company "Jim Bob's Insurance". Assume that
348# no orders have this coverage already. What query would you build
349# to accomplish this?
350
351DELIMITER //
352
353CREATE PROCEDURE add_option_to_company_orders(IN inCompanyName VARCHAR(100), IN inOption VARCHAR(50))
354BEGIN
355INSERT INTO options (orderNumber, optionName)
356 SELECT orders.orderNumber, "Rental Car Coverage"
357 FROM orders INNER JOIN products
358 ON orders.productOid = products.productOid INNER JOIN companies
359 ON products.companyOid = companies.companyOid AND companies.companyName = "Jim Bob's Insurance"
360 WHERE NOT EXISTS (SELECT 1 FROM options WHERE options.orderNumber = orders.orderNumber);
361END//
362
363DELIMITER ;
364
365CALL add_option_to_company_orders("Jim Bob's Insurance", "Rental Car Coverage");
366
367# Call a few extra times to test no duplicate entries possible
368
369CALL add_option_to_company_orders("Jim Bob's Insurance", "Rental Car Coverage");
370CALL add_option_to_company_orders("Jim Bob's Insurance", "Rental Car Coverage");
371CALL add_option_to_company_orders("Jim Bob's Insurance", "Rental Car Coverage");
372
373SELECT * FROM options;
374
375# Let's check changes are reflected from (3) post (4)
376
377CALL latest_orders(50);