VJ8cftsu

1# Drop tables from previous exercises
2 
3SET FOREIGN_KEY_CHECKS=0;
4DROP TABLE IF EXISTS Track;
5DROP TABLE IF EXISTS CD;
6DROP TABLE IF EXISTS Artist;
7SET FOREIGN_KEY_CHECKS=1;
8 
9# Create new tables
10 
11CREATE TABLE Artist (
12       artID INT NOT NULL AUTO_INCREMENT,
13       artName VARCHAR(255) NOT NULL,
14       CONSTRAINT pk_artist PRIMARY KEY (artID),
15       CONSTRAINT ck_artist UNIQUE (artName)   
16);
17   
18CREATE TABLE CD (
19       cdID INT NOT NULL AUTO_INCREMENT,
20       artID INT NOT NULL,
21       cdTitle VARCHAR(255) NOT NULL,
22       cdPrice REAL NOT NULL,
23       cdGenre VARCHAR(255) NULL,
24       CONSTRAINT pk_cd PRIMARY KEY (cdID),
25       CONSTRAINT fk_cd_art FOREIGN KEY (artID) REFERENCES Artist (artID) ON DELETE CASCADE
26);
27 
28CREATE TABLE TRACK (
29       trackID INT NOT NULL AUTO_INCREMENT,
30       cdID INT NOT NULL,
31       trackNumber INT NOT NULL,
32       trackTitle VARCHAR(255) NOT NULL,
33       trackDuration INT NULL,
34       CONSTRAINT pk_track PRIMARY KEY (trackID),
35       CONSTRAINT fk_track_cd FOREIGN KEY (cdID) REFERENCES CD (cdID) ON DELETE CASCADE
36);
37 
38# Fill the tables up
39 
40# Artist
41 
42INSERT INTO Artist (artName)
43VALUES  ('Muse'),
44        ('Mr. Scruff'),
45        ('DeadMau5'),
46        ('Mark Ronson'),
47        ('Mark Ronson & The Business Intl'),
48        ('Animal Collective'),
49        ('Kings of Leon'),
50        ('Maroon 5');
51 
52# CD
53 
54INSERT INTO CD (artID, cdTitle, cdGenre, cdPrice) VALUES
55((SELECT artID from Artist WHERE artName = 'Muse'), 'Black Holes and Revelations', 'Rock', 9.99),
56((SELECT artID from Artist WHERE artName = 'Muse'), 'The Resistance', 'Rock', 11.99),
57((SELECT artID from Artist WHERE artName = 'Mr. Scruff'), 'Ninja Tuna', 'Electronica', 9.99),
58((SELECT artID from Artist WHERE artName = 'Deadmau5'), 'For Lack of a Better Name', 'Electro House', 9.99),
59((SELECT artID from Artist WHERE artName = 'Mark Ronson'), 'Version', 'Pop', 12.99),
60((SELECT artID from Artist WHERE artName = 'Mark Ronson & The Business Intl'), 'Record Collection', 'Alternative Rock', 11.99),
61((SELECT artID from Artist WHERE artName = 'Animal Collective'), 'Merriweather Post Pavilion', 'Electronica', 12.99),
62((SELECT artID from Artist WHERE artName = 'Kings of Leon'), 'Only By The Night', 'Rock', 9.99),
63((SELECT artID from Artist WHERE artName = 'Kings of Leon'), 'Come Around Sundown', 'Rock', 12.99),
64((SELECT artID from Artist WHERE artName = 'Maroon 5'), 'Hands All Over', 'Pop', 11.99);
65 
66# TRACK
67 
68INSERT INTO Track VALUES (0,1,1,"Take a Bow",275);
69INSERT INTO Track VALUES (0,1,2,"Starlight",240);
70INSERT INTO Track VALUES (0,1,3,"Supermassive Black Hole",209);
71INSERT INTO Track VALUES (0,1,4,"Map of the Problematique",259);
72INSERT INTO Track VALUES (0,1,5,"Soldier's Poem",124);
73INSERT INTO Track VALUES (0,1,6,"Invincible",300);
74INSERT INTO Track VALUES (0,1,7,"Assassin",211);
75INSERT INTO Track VALUES (0,1,8,"Exo-Politics",233);
76INSERT INTO Track VALUES (0,1,9,"City of Delusion",289);
77INSERT INTO Track VALUES (0,1,10,"Hoodoo",223);
78INSERT INTO Track VALUES (0,1,11,"Knights of Cydonia",367);
79INSERT INTO Track VALUES (0,1,12,"Glorious",281);
80INSERT INTO Track VALUES (0,2,1,"Uprising",303);
81INSERT INTO Track VALUES (0,2,2,"Resistance",347);
82INSERT INTO Track VALUES (0,2,3,"Undisclosed Desires",236);
83INSERT INTO Track VALUES (0,2,4,"United States of Eurasia / Collateral Damage",348);
84INSERT INTO Track VALUES (0,2,5,"Guiding Light",253);
85INSERT INTO Track VALUES (0,2,6,"Unnatural Selection",415);
86INSERT INTO Track VALUES (0,2,7,"MK Ultra",246);
87INSERT INTO Track VALUES (0,2,8,"I Belong to You / Mon cœur s’ouvre à ta voix",339);
88INSERT INTO Track VALUES (0,2,9,"Exogenesis: Symphony,  Part 1: Overture",258);
89INSERT INTO Track VALUES (0,2,10,"Exogenesis: Symphony, Part 2: Cross-Pollination",236);
90INSERT INTO Track VALUES (0,2,11,"Exogenesis: Symphony,  Part 3: Redemption",277);
91INSERT INTO Track VALUES (0,3,1,"Test the Sound",101);
92INSERT INTO Track VALUES (0,3,2,"Music Takes Me Up",328);
93INSERT INTO Track VALUES (0,3,3,"Donkey Ride",314);
94INSERT INTO Track VALUES (0,3,4,"Hairy Bumpercress",391);
95INSERT INTO Track VALUES (0,3,5,"Whiplash",361);
96INSERT INTO Track VALUES (0,3,6,"Nice Up the Function",236);
97INSERT INTO Track VALUES (0,3,7,"Bang the Floor",223);
98INSERT INTO Track VALUES (0,3,8,"Get on Down",354);
99INSERT INTO Track VALUES (0,3,9,"Hold On",303);
100INSERT INTO Track VALUES (0,3,10,"Give Up to Get",405);
101INSERT INTO Track VALUES (0,3,11,"Kalimba",351);
102INSERT INTO Track VALUES (0,3,12,"This Way",332);
103INSERT INTO Track VALUES (0,3,13,"Stockport Carnival",329);
104INSERT INTO Track VALUES (0,4,1,"FML",419);
105INSERT INTO Track VALUES (0,4,2,"Moar Ghosts 'n' Stuff",297);
106INSERT INTO Track VALUES (0,4,3,"Ghosts 'n' Stuff - Original Mix",328);
107INSERT INTO Track VALUES (0,4,4,"Hi Friend (0,original mix);",392);
108INSERT INTO Track VALUES (0,4,5,"Bot",393);
109INSERT INTO Track VALUES (0,4,6,"Word Problems",509);
110INSERT INTO Track VALUES (0,4,7,"Soma",473);
111INSERT INTO Track VALUES (0,4,8,"Lack of a Better Name",494);
112INSERT INTO Track VALUES (0,4,9,"The 16th Hour",592);
113INSERT INTO Track VALUES (0,4,10,"Strobe",633);
114INSERT INTO Track VALUES (0,5,1,"God Put a Smile on Your Face",192);
115INSERT INTO Track VALUES (0,5,2,"Oh My God",216);
116INSERT INTO Track VALUES (0,5,3,"Stop Me",234);
117INSERT INTO Track VALUES (0,5,4,"Toxic",245);
118INSERT INTO Track VALUES (0,5,5,"Valerie",219);
119INSERT INTO Track VALUES (0,5,6,"Apply Some Pressure",217);
120INSERT INTO Track VALUES (0,5,7,"Inversion",107);
121INSERT INTO Track VALUES (0,5,8,"Pretty Green",197);
122INSERT INTO Track VALUES (0,5,9,"Just",320);
123INSERT INTO Track VALUES (0,5,10,"Amy",212);
124INSERT INTO Track VALUES (0,5,11,"The Only One I Know",239);
125INSERT INTO Track VALUES (0,5,12,"Diversion",79);
126INSERT INTO Track VALUES (0,5,13,"L.S.F. (0,version Revisited);",210);
127INSERT INTO Track VALUES (0,5,14,"Outversion",110);
128INSERT INTO Track VALUES (0,5,15,"Pistol of Fire",177);
129INSERT INTO Track VALUES (0,5,16,"No One Knows",279);
130INSERT INTO Track VALUES (0,5,17,"You’re All I Need to Get By",189);
131INSERT INTO Track VALUES (0,6,1,"Bang Bang Bang",234);
132INSERT INTO Track VALUES (0,6,2,"Lose It (0,In the End);",146);
133INSERT INTO Track VALUES (0,6,3,"The Bike Song",265);
134INSERT INTO Track VALUES (0,6,4,"Somebody to Love Me",298);
135INSERT INTO Track VALUES (0,6,5,"You Gave Me Nothing",241);
136INSERT INTO Track VALUES (0,6,6,"The Colour of Crumar",89);
137INSERT INTO Track VALUES (0,6,7,"Glass Mountain Trust",226);
138INSERT INTO Track VALUES (0,6,8,"Circuit Breaker",264);
139INSERT INTO Track VALUES (0,6,9,"Introducing the Business",222);
140INSERT INTO Track VALUES (0,6,10,"Record Collection",230);
141INSERT INTO Track VALUES (0,6,11,"Selector",66);
142INSERT INTO Track VALUES (0,6,12,"Hey Boy",214);
143INSERT INTO Track VALUES (0,6,13,"Missing Words",90);
144INSERT INTO Track VALUES (0,6,14,"The Night Last Night",265);
145INSERT INTO Track VALUES (0,7,1,"In the Flowers",322);
146INSERT INTO Track VALUES (0,7,2,"My Girls",341);
147INSERT INTO Track VALUES (0,7,3,"Also Frightened",314);
148INSERT INTO Track VALUES (0,7,4,"Summertime Clothes",270);
149INSERT INTO Track VALUES (0,7,5,"Daily Routine",346);
150INSERT INTO Track VALUES (0,7,6,"Bluish",314);
151INSERT INTO Track VALUES (0,7,7,"Guys Eyes",271);
152INSERT INTO Track VALUES (0,7,8,"Taste",233);
153INSERT INTO Track VALUES (0,7,9,"Lion in a Coma",252);
154INSERT INTO Track VALUES (0,7,10,"No More Runnin",263);
155INSERT INTO Track VALUES (0,7,11,"Brother Sport",359);
156INSERT INTO Track VALUES (0,8,1,"Closer",237);
157INSERT INTO Track VALUES (0,8,2,"Crawl",246);
158INSERT INTO Track VALUES (0,8,3,"Sex on Fire",204);
159INSERT INTO Track VALUES (0,8,4,"Use Somebody",230);
160INSERT INTO Track VALUES (0,8,5,"Manhattan",204);
161INSERT INTO Track VALUES (0,8,6,"Revelry",201);
162INSERT INTO Track VALUES (0,8,7,"17",185);
163INSERT INTO Track VALUES (0,8,8,"Notion",180);
164INSERT INTO Track VALUES (0,8,9,"I Want You",307);
165INSERT INTO Track VALUES (0,8,10,"Be Somebody",227);
166INSERT INTO Track VALUES (0,8,11,"Cold Desert",334);
167INSERT INTO Track VALUES (0,8,12,"King of the Rodeo",168);
168INSERT INTO Track VALUES (0,8,13,"Slow Night, So Long",189);
169INSERT INTO Track VALUES (0,8,14,"Fans",242);
170INSERT INTO Track VALUES (0,8,15,"Molly's Chambers",145);
171INSERT INTO Track VALUES (0,8,16,"My Party",241);
172INSERT INTO Track VALUES (0,8,17,"Arizona",289);
173INSERT INTO Track VALUES (0,8,18,"Charmer",228);
174INSERT INTO Track VALUES (0,9,1,"The End",264);
175INSERT INTO Track VALUES (0,9,2,"Radioactive",206);
176INSERT INTO Track VALUES (0,9,3,"Pyro",250);
177INSERT INTO Track VALUES (0,9,4,"Mary",205);
178INSERT INTO Track VALUES (0,9,5,"The Face",208);
179INSERT INTO Track VALUES (0,9,6,"The Immortals",208);
180INSERT INTO Track VALUES (0,9,7,"Back Down South",241);
181INSERT INTO Track VALUES (0,9,8,"Beach Side",170);
182INSERT INTO Track VALUES (0,9,9,"No Money",185);
183INSERT INTO Track VALUES (0,9,10,"Pony Up",184);
184INSERT INTO Track VALUES (0,9,11,"Birthday",195);
185INSERT INTO Track VALUES (0,9,12,"Mi Amigo",246);
186INSERT INTO Track VALUES (0,9,13,"Pickup Truck",284);
187INSERT INTO Track VALUES (0,10,1,"Misery",216);
188INSERT INTO Track VALUES (0,10,2,"Give a Little More",180);
189INSERT INTO Track VALUES (0,10,3,"Stutter",197);
190INSERT INTO Track VALUES (0,10,4,"Don't Know Nothing",199);
191INSERT INTO Track VALUES (0,10,5,"Never Gonna Leave This Bed",197);
192INSERT INTO Track VALUES (0,10,6,"I Can't Lie",211);
193INSERT INTO Track VALUES (0,10,7,"Hands All Over",193);
194INSERT INTO Track VALUES (0,10,8,"How",217);
195INSERT INTO Track VALUES (0,10,9,"Get Back in My Life",218);
196INSERT INTO Track VALUES (0,10,10,"Just a Feeling",227);
197INSERT INTO Track VALUES (0,10,11,"Runaway",181);
198INSERT INTO Track VALUES (0,10,12,"Out of Goodbyes",197);
199INSERT INTO Track VALUES (0,10,13,"Last Chance",190);
200INSERT INTO Track VALUES (0,10,14,"No Curtain Call",226);
201INSERT INTO Track VALUES (0,10,15,"Never Gonna Leave This Bed (0,acoustic);",203);
202INSERT INTO Track VALUES (0,10,16,"Misery (0,Cutmore Radio Edit);",227);
203INSERT INTO Track VALUES (0,10,17,"If I Ain't Got You (0,live);",241);
204INSERT INTO Track VALUES (0,10,18,"Crazy Little Thing Called Love (0,acoustic);",193);
205INSERT INTO Track VALUES (0,10,19,"Wake Up Call (0,live);",244);