· 11 years ago · Jun 16, 2015, 05:20 AM
1/*******************************************************************************
2 Title: INFOSYS 222 / INFOMGMT 292 Assignment 02 - SQL Answer Script
3 Lastname: Gan
4 Firstname: Jonathan
5 AUID: 8064455
6 NetLogin: jgan174
7 Course: INFOSYS222
8 Date: 25 May 2015
9********************************************************************************/
10
11/*******************************************************************************
12Part 1
13********************************************************************************/
14
15--Question 1
16
17--Question 2
18
19DROP TABLE IF EXISTS [AddressType];
20
21DROP TABLE IF EXISTS [Address];
22
23DROP TABLE IF EXISTS [Customer];
24
25DROP TABLE IF EXISTS [Review];
26
27DROP TABLE IF EXISTS [ReviewType];
28
29DROP TABLE IF EXISTS [Order];
30
31DROP TABLE IF EXISTS [Employee];
32
33DROP TABLE IF EXISTS [OrderStatus];
34
35DROP TABLE IF EXISTS [OrderLine];
36
37DROP TABLE IF EXISTS [ProductSpecial];
38
39DROP TABLE IF EXISTS [Product];
40
41DROP TABLE IF EXISTS [Recipe];
42
43DROP TABLE IF EXISTS [Ingredient];
44
45CREATE TABLE [AddressType]
46(
47 [AddressType] VARCHAR(8) PRIMARY KEY NOT NULL
48);
49
50CREATE TABLE [Address]
51(
52 [AddressID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
53 [Street] VARCHAR(70) NOT NULL,
54 [Suburb] VARCHAR(40) NOT NULL,
55 [PostalCode] VARCHAR(10) NOT NULL,
56 [AddressType] VARCHAR(15) NOT NULL,
57 [CustomerId] VARCHAR(60) NOT NULL,
58 FOREIGN KEY ([AddressType]) REFERENCES [AddressType] ([AddressType])
59 ON DELETE NO ACTION ON UPDATE NO ACTION,
60 FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([Email])
61 ON DELETE NO ACTION ON UPDATE NO ACTION
62);
63
64CREATE TABLE [Customer]
65(
66 [Email] VARCHAR(60) PRIMARY KEY NOT NULL,
67 [FirstName] VARCHAR(40) NOT NULL,
68 [LastName] VARCHAR(20) NOT NULL,
69 [Gender] VARCHAR(6),
70 [BirthDate] DATE,
71 [Mobile] VARCHAR(24),
72 [Password] VARCHAR(24) NOT NULL
73);
74
75CREATE TABLE [Review]
76(
77 [ReviewNo] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
78 [RewiewDate] DATE,
79 [RewiewDesc] VARCHAR(300) NOT NULL,
80 [ReviewStar] NUMERIC(2,1) NOT NULL,
81 [ReviewPublic] VARCHAR(3) NOT NULL,
82 [CustomerId] VARCHAR(60),
83 [ProdNo] INTEGER,
84 [ReviewType] VARCHAR(7) NOT NULL,
85 FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([Email])
86 ON DELETE NO ACTION ON UPDATE NO ACTION,
87 FOREIGN KEY ([ProdNo]) REFERENCES [Product] ([ProdNo])
88 ON DELETE NO ACTION ON UPDATE NO ACTION,
89 FOREIGN KEY ([ReviewType]) REFERENCES [ReviewType] ([ReviewType])
90 ON DELETE NO ACTION ON UPDATE NO ACTION
91);
92
93CREATE TABLE [ReviewType]
94(
95 [ReviewType] VARCHAR(7) PRIMARY KEY NOT NULL
96);
97
98CREATE TABLE [Order]
99(
100 [OrderNo] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
101 [OrderDateTime] DATETIME NOT NULL,
102 [DeliveryName] VARCHAR(20) NOT NULL,
103 [DeliveryMobile] VARCHAR(24),
104 [OrderStatusCode] VARCHAR(10) NOT NULL,
105 [DeliveryAddressId] INTEGER NOT NULL,
106 [BillingAddressId] INTEGER NOT NULL,
107 [CustomerId] VARCHAR(60) NOT NULL,
108 [EmpId] INTEGER(60) NOT NULL,
109 FOREIGN KEY ([OrderStatusCode]) REFERENCES [OrderStatus] ([OrderStatusCode])
110 ON DELETE NO ACTION ON UPDATE NO ACTION,
111 FOREIGN KEY ([DeliveryAddressId]) REFERENCES [Address] ([AddressType])
112 ON DELETE NO ACTION ON UPDATE NO ACTION,
113 FOREIGN KEY ([BillingAddressId]) REFERENCES [Address] ([AddressType])
114 ON DELETE NO ACTION ON UPDATE NO ACTION,
115 FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([Email])
116 ON DELETE NO ACTION ON UPDATE NO ACTION,
117 FOREIGN KEY ([EmpId]) REFERENCES [Employee] ([EmpId])
118 ON DELETE NO ACTION ON UPDATE NO ACTION
119);
120
121CREATE TABLE [Employee]
122(
123 [EmpId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
124 [EmpName] VARCHAR(60)
125);
126
127CREATE TABLE [OrderStatus]
128(
129 [OrderStatusCode] VARCHAR(10) PRIMARY KEY NOT NULL
130);
131
132CREATE TABLE [OrderLine]
133(
134 [OrderNo] INTEGER NOT NULL,
135 [ProdNo] INTEGER NOT NULL,
136 [Quantity] INTEGER NOT NULL,
137 CONSTRAINT [PK_OrderProd] PRIMARY KEY ([OrderNo], [ProdNo]),
138 FOREIGN KEY ([OrderNo]) REFERENCES [OrderLine] ([OrderNo])
139 ON DELETE NO ACTION ON UPDATE NO ACTION,
140 FOREIGN KEY ([ProdNo]) REFERENCES [OrderLine] ([ProdNo])
141 ON DELETE NO ACTION ON UPDATE NO ACTION
142);
143
144CREATE TABLE [ProductSpecial]
145(
146 [StartDateTime] DATETIME NOT NULL,
147 [ProdNo] INTEGER NOT NULL,
148 [EndDateTime] DATETIME NOT NULL,
149 [Discount] NUMERIC NOT NULL,
150 CONSTRAINT [PK_StartDateTimeProdNo] PRIMARY KEY ([StartDateTime], [ProdNo]),
151 FOREIGN KEY ([ProdNo]) REFERENCES [Product] ([ProdNo])
152 ON DELETE NO ACTION ON UPDATE NO ACTION
153);
154
155CREATE TABLE [Product]
156(
157 [ProdNo] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
158 [ProdName] VARCHAR(20) NOT NULL,
159 [ProdDesc] VARCHAR(300) NOT NULL,
160 [ProdPrice] NUMERIC(6,2) NOT NULL,
161 [ProdParentNo] INTEGER,
162 FOREIGN KEY ([ProdParentNo]) REFERENCES [Product] ([ProdNo])
163);
164
165CREATE TABLE [Recipe]
166(
167 [ProdNo] INTEGER,
168 [IngrNo] INTEGER,
169 [UnitNeeded] NUMERIC(3,1),
170 CONSTRAINT [PK_ProdNoIngrNo] PRIMARY KEY ([ProdNo], [IngrNo]),
171 FOREIGN KEY ([ProdNo]) REFERENCES [Product] ([ProdNo])
172 ON DELETE NO ACTION ON UPDATE NO ACTION,
173 FOREIGN KEY ([IngrNo]) REFERENCES [Ingredient] ([IngrNo])
174 ON DELETE NO ACTION ON UPDATE NO ACTION
175);
176
177CREATE TABLE [Ingredient]
178(
179 [IngrNo] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
180 [IngName] VARCHAR(20) NOT NULL,
181 [IngrUnit] VARCHAR(12) NOT NULL
182);
183
184CREATE INDEX [IFK_AddressAddressType] ON [Address] ([AddressType]);
185
186CREATE INDEX [IFK_AddressCustomerId] ON [Address] ([CustomerId]);
187
188CREATE INDEX [IFK_OrderOrderStatusCode] ON [Order] ([OrderStatusCode]);
189
190CREATE INDEX [IFK_OrderDeliveryAddressId] ON [Order] ([DeliveryAddressId]);
191
192CREATE INDEX [IFK_OrderBillingAddressId] ON [Order] ([BillingAddressId]);
193
194CREATE INDEX [IFK_OrderCustomerId] ON [Order] ([CustomerId]);
195
196CREATE INDEX [IFK_OrderEmpId] ON [Order] ([EmpId]);
197
198CREATE INDEX [IFK_ReviewCustomerId] ON [Review] ([CustomerId]);
199
200CREATE INDEX [IFK_ReviewProdNo] ON [Review] ([ProdNo]);
201
202CREATE INDEX [IFK_ReviewReviewType] ON [Review] ([ReviewType]);
203
204CREATE INDEX [IFK_OrderLineOrderNo] ON [OrderLine] ([OrderNo]);
205
206CREATE INDEX [IFK_OrderLineProdNo] ON [OrderLine] ([ProdNo]);
207
208CREATE INDEX [IFK_ProductProdParentNo] ON [Product] ([ProdParentNo]);
209
210CREATE INDEX [IFK_ProductSpecialProdNo] ON [ProductSpecial] ([ProdNo]);
211
212CREATE INDEX [IFK_RecipeProdNo] ON [Recipe] ([ProdNo]);
213
214CREATE INDEX [IFK_RecipeIngrNo] ON [Recipe] ([IngrNo]);
215
216--Question 3
217
218INSERT INTO [AddressType] VALUES ("Billing");
219
220INSERT INTO [AddressType] VALUES ("Delivery");
221
222INSERT INTO [Address] VALUES (1, "1 ASD ROAD", "ASDLAND", "1234-43", "Billing", "asd@asd.com.de");
223
224INSERT INTO [Address] VALUES (2, "1 ASD ROAD", "ASDLAND", "1234-43", "Delivery", "asd@asd.com.de");
225
226INSERT INTO [Address] VALUES (3, "1 ASDF AVE", "ASDFTOWN", "4321-12", "Billing", "das@das.com.de");
227
228INSERT INTO [Address] VALUES (4, "1 ASDF AVE", "ASDFTOWN", "4321-12", "Delivery", "das@das.com.de");
229
230INSERT INTO [Customer] VALUES ("asd@asd.com.de", "Asd", "Fgh", "Male", "1991-01-01", 001122334455, "SWAG_BLAZE_IT_420");
231
232INSERT INTO [Customer] VALUES ("das@das.com.de", "Das", "Hgf", "Female", NULL, NULL, "xXx_NoSc0peMast3r_xXx");
233
234INSERT INTO [Order] VALUES (9, "2001-09-11 08:46:30", "Jet Fuel Can't Melt Steel Beams", 001122334455, "Delivered", 2, 1, "asd@asd.com.de", 1);
235
236INSERT INTO [Order] VALUES (4, "2012-12-12 04:20:00", "Jeff Euls Can't Melt Meal Seams", 123123123123, "Delivered", 4, 3, "das@das.com.de", 2);
237
238INSERT INTO [OrderStatus] VALUES ("Received");
239
240INSERT INTO [OrderStatus] VALUES ("Delivered");
241
242INSERT INTO [OrderStatus] VALUES ("InProgress");
243
244INSERT INTO [Review] VALUES (1, "2001-09-11", "911 was an inside job", 4.5, "Yes", "asd@asd.com.de", Null, "Company");
245
246INSERT INTO [Review] VALUES (200, "2012-12-12", "Ehh so so only lah", 0.5, "No", "das@das.com.de", 4, "Product");
247
248INSERT INTO [Employee] VALUES (1, "Usain Bolt");
249
250INSERT INTO [Employee] VALUES (2, "Swag Master Overlord Superme Mega Deluxe Blaze King");
251
252INSERT INTO [ReviewType] VALUES ("Company");
253
254INSERT INTO [ReviewType] VALUES ("Product");
255
256INSERT INTO [OrderLine] VALUES (9, 1, 1);
257
258INSERT INTO [OrderLine] VALUES (4, 4, 20);
259
260INSERT INTO [Product] VALUES (4, "Dank Meme", "Quality Dank, Much Memes", 999.95, Null);
261
262INSERT INTO [Product] VALUES (9, "Deez Nutz", "Got Emm", 7000.00, Null);
263
264INSERT INTO [ProductSpecial] VALUES ("2012-12-12 00:00:00", 9, "2012-12-12 23:59:59", 0.25);
265
266INSERT INTO [ProductSpecial] VALUES ("2001-01-01 00:00:00", 4, "2002-01-01 00:00:00", 0.01);
267
268INSERT INTO [Recipe] VALUES (9, 1, 1);
269
270INSERT INTO [Recipe] VALUES (4, 4, 20.2);
271
272INSERT INTO [Ingredient] VALUES (1, "Steel", "Beams");
273
274INSERT INTO [Ingredient] VALUES (4, "Sativa", "Ounce");
275
276/*******************************************************************************
277Part 2
278********************************************************************************/
279
280--Question 1
281
282SELECT name as "Name"
283FROM track
284WHERE name LIKE "love%" AND name LIKE "%love";
285
286/*******************************************************************************
287Name
288------------------------------
289Love, Hate, Love
290Love
291********************************************************************************/
292
293--Question 2
294
295UPDATE customer
296SET email = 'edfrancis@yahoo.ca'
297WHERE firstname = 'Edward' AND lastname = 'Francis';
298
299--Question 3
300
301SELECT m.firstname || ' ' || m.lastname AS 'Manager',
302m.title AS 'Manager TItle',
303e.firstname || ' ' || e.lastname AS 'Employee',
304e.title AS 'Employee Title'
305FROM Employee e, Employee m
306WHERE e.reportsto = m.employeeID
307ORDER BY Manager;
308
309/*******************************************************************************
310Manager Manager Title Employee Employee Title
311------------------------------ ------------------------------ ------------------------------ ------------------------------
312Andrew Adams General Manager Nancy Edwards Sales Manager
313Andrew Adams General Manager Michael Mitchell IT Manager
314Michael Mitchell IT Manager Robert King IT Staff
315Michael Mitchell IT Manager Laura Callahan IT Staff
316Nancy Edwards Sales Manager Jane Peacock Sales Support Agent
317Nancy Edwards Sales Manager Margaret Park Sales Support Agent
318Nancy Edwards Sales Manager Steve Johnson Sales Support Agent
319********************************************************************************/
320
321--Question 4
322
323SELECT upper(substr(substr(email,instr(email,'@')+1),
3241, instr(substr(email,instr(email,'@')+1),'.')-1)) AS "Email Provider",
325count(email) AS "No. of Customers"
326FROM customer
327GROUP BY "Email Provider"
328ORDER BY "No. of Customers" DESC, "Email Provider" ASC;
329
330/*******************************************************************************
331Email Provider No. of Customers
332------------------------------ ------------------------------
333YAHOO 19
334GMAIL 8
335APPLE 7
336HOTMAIL 4
337SHAW 3
338AOL 2
339SURFEU 2
340UOL 2
341COMCAST 1
342EMBRAER 1
343GOOGLE 1
344JETBRAINS 1
345JUBII 1
346MICROSOFT 1
347REDIFF 1
348RIOTUR 1
349ROGERS 1
350SAPO 1
351WOODSTOCK 1
352WP 1
353********************************************************************************/
354
355--Question 5
356
357SELECT strftime('%Y',invoicedate) AS 'Year', "$" || sum(total) AS 'Sales'
358FROM invoice
359GROUP BY strftime('%Y',invoicedate);
360
361/*******************************************************************************
362Year Sales
363------------------------------ ------------------------------
3642009 $449.46
3652010 $481.45
3662011 $469.58
3672012 $477.53
3682013 $450.58
369********************************************************************************/
370
371--Question 6
372
373SELECT g.name AS "Genre",
374count(*) AS "No. of Tracks",
375(SELECT round(((count(t.genreid)*100.0)/(count(trackid)*1.0)), 2)
376FROM track) AS "% of Tracks"
377FROM track t, genre g
378WHERE t.genreid = g.genreid
379GROUP BY "Genre"
380ORDER BY "No. of Tracks" DESC
381LIMIT 10;
382
383/*******************************************************************************
384Genre No. of Tracks % of Tracks
385------------------------------ ------------------------------ ------------------------------
386Rock 1297 37.03
387Latin 579 16.53
388Metal 374 10.68
389Alternative & Punk 332 9.48
390Jazz 130 3.71
391TV Shows 93 2.65
392Blues 81 2.31
393Classical 74 2.11
394Drama 64 1.83
395R&B/Soul 61 1.74
396********************************************************************************/
397
398--Question 7
399
400SELECT a.albumid AS "ID",
401a.Title AS "Album",
402ar.Name AS "Artist"
403FROM album a, artist ar
404WHERE a.ArtistId = ar.ArtistId
405EXCEPT
406SELECT t.albumid,
407a.Title,
408ar.Name
409FROM invoiceline il, track t, artist ar, album a
410WHERE il.trackid = t.trackid AND a.ArtistId = ar.ArtistId
411
412/*******************************************************************************
413ID Album Artist
414------------------------------ ------------------------------ ------------------------------
415226 Battlestar Galactica: The Stor Battlestar Galactica
416260 Cake: B-Sides and Rarities Cake
417262 Quiet Songs Aisha Duo
418264 Realize Karsh Kale
419267 Worlds Aaron Goldberg
420268 The Best of Beethoven Nicolaus Esterhazy Sinfonia
421272 Adorate Deum: Gregorian Chant Alberto Turco & Nova Schola Gr
422273 Allegri: Miserere Richard Marlow & The Choir of
423275 Vivaldi: The Four Seasons Anne-Sophie Mutter, Herbert Vo
424276 Bach: Violin Concertos Hilary Hahn, Jeffrey Kahane, L
425277 Bach: Goldberg Variations Wilhelm Kempff
426281 Sir Neville Marriner: A Celebr Academy of St. Martin in the F
427282 Mozart: Wind Concertos Berliner Philharmoniker, Claud
428284 Beethoven: Symhonies Nos. 5 & Orchestre R├®volutionnaire et
429285 A Soprano Inspired Britten Sinfonia, Ivor Bolton
430286 Great Opera Choruses Chicago Symphony Chorus, Chica
431289 Tchaikovsky: The Nutcracker London Symphony Orchestra & Si
432290 The Last Night of the Proms Barry Wordsworth & BBC Concert
433291 Puccini: Madama Butterfly - Hi Herbert Von Karajan, Mirella F
434293 Pavarotti's Opera Made Easy Luciano Pavarotti
435294 Great Performances - Barber's Leonard Bernstein & New York P
436295 Carmina Burana Boston Symphony Orchestra & Se
437296 A Copland Celebration, Vol. I Aaron Copland & London Symphon
438297 Bach: Toccata & Fugue in D Min Ton Koopman
439298 Prokofiev: Symphony No.1 Sergei Prokofiev & Yuri Temirk
440302 Mascagni: Cavalleria Rusticana James Levine
441305 Great Recordings of the Centur Gustav Mahler
442309 Palestrina: Missa Papae Marcel Choir Of Westminster Abbey & S
443311 Strauss: Waltzes Eugene Ormandy
444313 Bizet: Carmen Highlights Chor der Wiener Staatsoper, He
445315 Handel: Music for the Royal Fi English Concert & Trevor Pinno
446317 Mozart Gala: Famous Arias Sir Georg Solti, Sumi Jo & Wie
447318 SCRIABIN: Vers la flamme Christopher O'Riley
448319 Armada: Music from the Courts Fretwork
449328 Charpentier: Divertissements, Les Arts Florissants & William
450332 The Ultimate Relexation Album Charles Dutoit & L'Orchestre S
451336 Prokofiev: Symphony No.5 & Str Berliner Philharmoniker & Herb
452339 Great Recordings of the Centur Itzhak Perlman
453341 Great Recordings of the Centur Gerald Moore
454342 Locatelli: Concertos for Violi Mela Tenenbaum, Pro Musica Pra
455345 Monteverdi: L'Orfeo C. Monteverdi, Nigel Rogers -
456346 Mozart: Chamber Music Nash Ensemble
457347 Koyaanisqatsi (Soundtrack from Philip Glass Ensemble
458********************************************************************************/
459
460--Question 8
461
462SELECT e.Firstname || " " || e.Lastname AS "Employee Name",
463count(c.CustomerId) AS "No. of Accounts",
464(SELECT "$" || round(sum(i.total),2)
465FROM invoice i, customer c
466WHERE i.customerid = c.customerid
467AND c.SupportRepId = e.EmployeeId) AS "Total Revenue"
468FROM customer c, employee e
469WHERE e.EmployeeId = c.SupportRepId
470GROUP BY "employee name";
471
472/*******************************************************************************
473Employee Name No. of Customers Total Revenue
474------------------------------ ------------------------------ ------------------------------
475Jane Peacock 21 $833.04
476Margaret Park 20 $775.4
477Steve Johnson 18 $720.16
478********************************************************************************/
479
480--Question 9
481
482SELECT country AS "Country",
483count(country) - count(company) AS "Individuals",
484count(company) AS "Companies"
485FROM customer
486GROUP BY country
487ORDER BY country ASC;
488
489/*******************************************************************************
490Country Individuals Companies
491------------------------------ ------------------------------ ------------------------------
492Argentina 1 0
493Australia 1 0
494Austria 1 0
495Belgium 1 0
496Brazil 1 4
497Canada 6 2
498Chile 1 0
499Czech Republic 1 1
500Denmark 1 0
501Finland 1 0
502France 5 0
503Germany 4 0
504Hungary 1 0
505India 2 0
506Ireland 1 0
507Italy 1 0
508Netherlands 1 0
509Norway 1 0
510Poland 1 0
511Portugal 2 0
512Spain 1 0
513Sweden 1 0
514USA 10 3
515United Kingdom 3 0
516********************************************************************************/
517
518--Question 10
519
520SELECT Upper(t.Name) || " is a " || (t.Milliseconds/1000.0) ||
521" seconds long track in the album " || upper(a.title) ||
522" of " || ar.name ||
523"composed by " || IFNULL(t.composer, "an unknown composer") ||
524". It is available as a " || m.name ||
525" for $ " || t.UnitPrice ||
526", and it can be found in the following playlists: " ||
527group_concat(p.name, ", ")
528FROM track t,
529album a,
530artist ar,
531mediatype m,
532playlisttrack pt,
533playlist p
534WHERE t.AlbumId = a.AlbumId
535AND a.ArtistId = ar.ArtistId
536AND t.MediaTypeId = m.MediaTypeId
537AND t.trackid = pt.trackid
538AND pt.PlaylistId = p.PlaylistId
539GROUP BY t.trackid
540ORDER BY random()
541LIMIT 1;
542
543/*******************************************************************************
544VITAL E SUA MOTO is a 210 seconds long track in the album ARQUIVO OS PARALAMAS DO SUCESSO of Os Paralamas Do Sucessocomposed by an unknown composer. It is available as a MPEG audio file for $ 0.99, and it can be found in the following playlists: Music 1, Music 2
545********************************************************************************/