– —————————————– – MySQL version of small creature database – —————————————– use smallCreature;

– since mysql enforces referential integrity of the foreign keys, – we need to drop the tables in a particular order

DROP TABLE IF EXISTS aspiredContribution; DROP TABLE IF EXISTS contribution; DROP TABLE IF EXISTS role; DROP TABLE IF EXISTS aspiration; DROP TABLE IF EXISTS achievement; DROP TABLE IF EXISTS teamSkill; DROP TABLE IF EXISTS skill; DROP TABLE IF EXISTS creature; DROP TABLE IF EXISTS town;

select ‘dropped the tables’ as ‘’; – —————— town – ——————————-

CREATE TABLE town ( townId VARCHAR(3) NOT NULL PRIMARY KEY, townName VARCHAR(20), State VARCHAR(20), Country VARCHAR(20), townNickname VARCHAR(80), townMotto VARCHAR(80) );

– order matches table creation: – id name state country – nickname motto INSERT INTO town VALUES (‘p’, ‘Philadelphia’, ‘PA’, ‘United States’,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 36)

Unexpected indentation.

‘Philly’, ‘Let brotherly love endure’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 37)

Block quote ends without a blank line; unexpected unindent.

INSERT INTO town VALUES (‘a’, ‘Anoka’, ‘MN’, ‘United States’,

‘Halloween Capital of the world’, NULL);

INSERT INTO town VALUES (‘be’, ‘Blue Earth’, ‘MN’, ‘United States’,

‘Beyond the Valley of the Jolly Green Giant’, ‘Earth so rich the city grows!’);

INSERT INTO town VALUES (‘b’, ‘Bemidji’, ‘MN’, ‘United States’,

‘B-town’, ‘The first city on the Mississippi’);

INSERT INTO town VALUES (‘d’, ‘Duluth’, ‘MN’, ‘United States’,

‘Zenith City’, NULL);

INSERT INTO town VALUES (‘g’, ‘Greenville’, ‘MS’, ‘United States’,

‘The Heart & Soul of the Delta’, ‘The Best Food, Shopping, & Entertainment In The South’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 49)

Definition list ends without a blank line; unexpected unindent.

INSERT INTO town VALUES (‘t’, ‘Tokyo’, ‘Kanto’, ‘Japan’, NULL, NULL); INSERT INTO town VALUES (‘as’, ‘Asgard’, NULL, NULL,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 51)

Unexpected indentation.

‘Home of Odin’’s vault’, ‘Where magic and science are one in the same’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 53)

Block quote ends without a blank line; unexpected unindent.

INSERT INTO town VALUES (‘mv’, ‘Metroville’, NULL, NULL,

‘Home of the Incredibles’, ‘Still Standing’);

INSERT INTO town VALUES (‘le’, ‘London’, ‘England’, ‘United Kingdom’,

‘The Smoke’, ‘Domine dirige nos’);

INSERT INTO town VALUES (‘sw’, ‘Seattle’, ‘Washington’, ‘United States’,

‘The Emerald City’, ‘The City of Goodwill’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 62)

Definition list ends without a blank line; unexpected unindent.

– fake print select ‘town inserts done.’ as ‘’;

– —————— creature – ——————————-

CREATE TABLE creature ( creatureId INTEGER NOT NULL PRIMARY KEY, creatureName VARCHAR(20), creatureType VARCHAR(20), reside_townId VARCHAR(3) REFERENCES town(townId), – foreign key idol_creatureId INTEGER );

INSERT INTO creature VALUES (1,’Bannon’,’person’,’p’,10); INSERT INTO creature VALUES (2,’Myers’,’person’,’a’,9); INSERT INTO creature VALUES (3,’Neff’,’person’,’be’,NULL); INSERT INTO creature VALUES (4,’Neff’,’person’,’b’,3); INSERT INTO creature VALUES (5,’Mieska’,’person’,’d’, 10); INSERT INTO creature VALUES (6,’Carlis’,’person’,’p’,9); INSERT INTO creature VALUES (7,’Kermit’,’frog’,’g’,8); INSERT INTO creature VALUES (8,’Godzilla’,’monster’,’t’,6); INSERT INTO creature VALUES (9,’Thor’,’superhero’,’as’,NULL); INSERT INTO creature VALUES (10,’Elastigirl’,’superhero’,’mv’,13); INSERT INTO creature VALUES (11,’David Beckham’,’person’,’le’,9); INSERT INTO creature VALUES (12,’Harry Kane’,’person’,’le’,11); INSERT INTO creature VALUES (13,’Megan Rapinoe’,’person’,’sw’,10);

– instances must exist before enforcing foreign key ALTER TABLE creature ADD FOREIGN KEY(idol_creatureId) REFERENCES creature(creatureId);

– fake print select ‘creature inserts done.’ as ‘’;

– —————— skill – ——————————-

CREATE TABLE skill ( skillCode VARCHAR(3) NOT NULL PRIMARY KEY, skillDescription VARCHAR(40), maxProficiency INTEGER, – max score that can be achieved for this skill minProficiency INTEGER, – min score that can be achieved for this skill origin_townId VARCHAR(3) REFERENCES town(townId) – foreign key );

INSERT INTO skill VALUES (‘A’, ‘float’, 10, -1,’b’); INSERT INTO skill VALUES (‘E’, ‘swim’, 5, 0,’b’); INSERT INTO skill VALUES (‘O’, ‘sink’, 10, -1,’b’); INSERT INTO skill VALUES (‘U’, ‘walk on water’, 5, 1,’d’); INSERT INTO skill VALUES (‘Z’, ‘gargle’, 5, 1,’a’); INSERT INTO skill VALUES (‘B2’, ‘2-crew bobsledding’, 25, 0,’d’); INSERT INTO skill VALUES (‘TR4’, ‘4x100 meter track relay’, 100, 0,’be’); INSERT INTO skill VALUES (‘C2’, ‘2-person canoeing’, 12, 1,’t’); INSERT INTO skill VALUES (‘THR’, ‘three-legged race’, 10, 0,’g’); INSERT INTO skill VALUES (‘D3’, ‘Australasia debating’, 10, 1,NULL); INSERT INTO skill VALUES (‘PK’, ‘soccer penalty kick’, 10, 1, ‘le’); – Note that no skill originates in Philly or Metroville or Asgaard

– fake print select ‘skill inserts done.’ as ‘’;

– —————— teamSkill – ——————————-

CREATE TABLE teamSkill ( skillCode VARCHAR(3) NOT NULL PRIMARY KEY references skill (skillCode), teamSize INTEGER );

INSERT INTO teamSkill VALUES (‘B2’, 2); INSERT INTO teamSkill VALUES (‘TR4’, 4); INSERT INTO teamSkill VALUES (‘C2’, 2); INSERT INTO teamSkill VALUES (‘THR’, 2); INSERT INTO teamSkill VALUES (‘D3’, 3);

– fake print select ‘teamSkill inserts done.’ as ‘’;

– —————— achievement – ——————————-

CREATE TABLE achievement ( achId INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, – mysql needs the _ creatureId INTEGER, skillCode VARCHAR(3), proficiency INTEGER, achDate DATETIME, test_townId VARCHAR(3) REFERENCES town(townId), – foreign key FOREIGN KEY (creatureId) REFERENCES creature (creatureId), FOREIGN KEY (skillCode) REFERENCES skill (skillCode) );

– Bannon floats in Anoka (where he aspired) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 153)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 154)

Block quote ends without a blank line; unexpected unindent.

VALUES (1, ‘A’, 3, CURDATE(), ‘a’);

– Bannon swims in Duluth (he aspired in Bemidji) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 158)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 159)

Block quote ends without a blank line; unexpected unindent.

VALUES (1, ‘E’, 3, ‘2017-09-15 15:35’, ‘d’);

– Bannon floats in Anoka (where he aspired) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 163)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 164)

Block quote ends without a blank line; unexpected unindent.

VALUES (1, ‘A’, 3, CURDATE(), ‘a’);

– Bannon swims in Duluth (he aspired in Bemidji) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 168)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 169)

Block quote ends without a blank line; unexpected unindent.

VALUES (1, ‘E’, 3, ‘2017-09-15 15:35’, ‘d’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 170)

Block quote ends without a blank line; unexpected unindent.

– Bannon doesn’t gargle – Mieska gargles in Tokyo (had no aspiration to) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 173)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 174)

Block quote ends without a blank line; unexpected unindent.

VALUES (5, ‘Z’, 6, ‘2016-04-12 15:42:30’, ‘t’);

– Neff #3 gargles in Blue Earth (but not to his aspired proficiency) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 178)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 179)

Block quote ends without a blank line; unexpected unindent.

VALUES (3, ‘Z’, 4, ‘2018-07-15’, ‘be’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 180)

Block quote ends without a blank line; unexpected unindent.

– Neff #3 gargles in Blue Earth (but not to his aspired proficiency) – on same day at same proficiency, signifying need for arbitrary id INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 183)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 184)

Block quote ends without a blank line; unexpected unindent.

VALUES (3, ‘Z’, 4, ‘2018-07-15’, ‘be’);

– Neff #3 keeps trying to gargle on the same day, with varying results
INSERT INTO achievement (creatureId, skillCode, proficiency,

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 189)

Block quote ends without a blank line; unexpected unindent.

VALUES (3, ‘Z’, 4, ‘2018-07-15’, ‘be’);

– Neff #4 gargles in Anoka INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 193)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 194)

Block quote ends without a blank line; unexpected unindent.

VALUES (4, ‘Z’, 3, ‘2018-06-10’, ‘a’);

– Beckham achieves PK in London INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 198)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 199)

Block quote ends without a blank line; unexpected unindent.

VALUES (11, ‘PK’, 10, ‘1998-08-15’, ‘le’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 200)

Block quote ends without a blank line; unexpected unindent.

– Kane achieves PK in London INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 202)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 203)

Block quote ends without a blank line; unexpected unindent.

VALUES (12, ‘PK’, 10, ‘2016-05-24’, ‘le’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 204)

Block quote ends without a blank line; unexpected unindent.

– Rapinoe achieves PK in London INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 206)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 207)

Block quote ends without a blank line; unexpected unindent.

VALUES (13, ‘PK’, 10, ‘2012-08-06’, ‘le’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 208)

Block quote ends without a blank line; unexpected unindent.

– Godizilla achieves PK in Tokyo poorly with no date – had not aspiration to do so- did it on a dare ;) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 211)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 212)

Block quote ends without a blank line; unexpected unindent.

VALUES (8, ‘PK’, 1, NULL, ‘t’);

– ——————– ——————– ——————- – Thor achieves three-legged race in Metroville (with Elastigirl) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 218)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 219)

Block quote ends without a blank line; unexpected unindent.

VALUES (9, ‘THR’, 10, ‘2018-08-12 14:30’, ‘mv’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 220)

Block quote ends without a blank line; unexpected unindent.

– Elastigirl achieves three-legged race in Metroville (with Thor) INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 222)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 223)

Block quote ends without a blank line; unexpected unindent.

VALUES (10, ‘THR’, 10, ‘2018-08-12 14:30’, ‘mv’);

– Kermit ‘pilots’ 2-person bobsledding (pilot goes into contribution) – with Thor as brakeman (brakeman goes into contribution) in Duluth, – achieve at 76% of maxProficiency INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 229)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 230)

Block quote ends without a blank line; unexpected unindent.

VALUES (7, ‘B2’, 19, ‘2017-01-10 16:30’, ‘d’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 231)

Block quote ends without a blank line; unexpected unindent.

INSERT INTO achievement (creatureId, skillCode, proficiency,

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 233)

Block quote ends without a blank line; unexpected unindent.

VALUES (9, ‘B2’, 19, ‘2017-01-10 16:30’, ‘d’);

– 4 people form track realy team in London: – Neff #4, Mieska, Myers, Bannon – achieve at 85% of maxProficiency INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 239)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 240)

Block quote ends without a blank line; unexpected unindent.

VALUES (4, ‘TR4’, 85, ‘2012-07-30’, ‘le’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 241)

Block quote ends without a blank line; unexpected unindent.

INSERT INTO achievement (creatureId, skillCode, proficiency,

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 243)

Block quote ends without a blank line; unexpected unindent.

VALUES (5, ‘TR4’, 85, ‘2012-07-30’, ‘le’);

INSERT INTO achievement (creatureId, skillCode, proficiency,

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 246)

Block quote ends without a blank line; unexpected unindent.

VALUES (2, ‘TR4’, 85, ‘2012-07-30’, ‘le’);

INSERT INTO achievement (creatureId, skillCode, proficiency,

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 249)

Block quote ends without a blank line; unexpected unindent.

VALUES (1, ‘TR4’, 85, ‘2012-07-30’, ‘le’);

– Thor, Rapinoe, and Kermit form debate team in Seattle, WA and – achieve at 80% of maxProficiency INSERT INTO achievement (creatureId, skillCode, proficiency,

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 254)

Unexpected indentation.

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 255)

Block quote ends without a blank line; unexpected unindent.

VALUES (9, ‘D3’, 8, CURDATE(), ‘sw’);

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 256)

Block quote ends without a blank line; unexpected unindent.

INSERT INTO achievement (creatureId, skillCode, proficiency,

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 258)

Block quote ends without a blank line; unexpected unindent.

VALUES (13, ‘D3’, 8, CURDATE(), ‘sw’);

INSERT INTO achievement (creatureId, skillCode, proficiency,

achDate, test_townId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 261)

Block quote ends without a blank line; unexpected unindent.

VALUES (7, ‘D3’, 8, CURDATE(), ‘sw’);

– no 2-person canoeing achievements, but some have aspirations – fake print select ‘achievement inserts done.’ as ‘’;

– —————— aspiration – ——————————-

CREATE TABLE aspiration ( – foreign key

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 271)

Unexpected indentation.

creatureId INTEGER NOT NULL REFERENCES creature(creatureId), – foreign key skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode), aspiredProficiency INTEGER, desired_townId VARCHAR(3) REFERENCES town(townId), – foreign key PRIMARY KEY (creatureId, skillCode)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 277)

Block quote ends without a blank line; unexpected unindent.

);

– Bannon aspires float in Anoka with proficiency of 3 INSERT INTO aspiration VALUES (1,’A’,3,’a’); – Bannon aspires swim in Bemidji with proficiency of 4 INSERT INTO aspiration VALUES (1,’E’,4,’b’); – Bannon aspires gargling in Blue Earth with proficiency of 3 INSERT INTO aspiration VALUES (1,’Z’,3,’be’); – Myers aspires float with proficiency of 3 INSERT INTO aspiration VALUES (2,’A’,3,NULL); – Neff #3 aspires float in Bemidji with proficiency of 8 INSERT INTO aspiration VALUES (3,’A’,8,’b’); – Neff #3 aspires gargling in Blue Earth with proficiency of 5 INSERT INTO aspiration VALUES (3,’Z’,5,’be’); – Neff #4 aspires swim in Greenville with proficiency of 3 INSERT INTO aspiration VALUES (4,’E’,3,’g’); – Mieska aspires gargling in Duluth with proficiency of INSERT INTO aspiration VALUES (5,’Z’,10,’d’); – Carlis aspires gargling in London with proficiency of INSERT INTO aspiration VALUES (6,’Z’,3,’le’); – Kermit aspires swim in Bemidji with proficiency of INSERT INTO aspiration VALUES (7,’E’,3,’b’); – Godzilla aspires sink in Tokyo with proficiency of INSERT INTO aspiration VALUES (8,’O’,4,’t’);

– Beckham, Kane, and Rapinoe aspire to achieve PK at maxProficiency in London INSERT INTO aspiration VALUES (11,’PK’,10,’le’); INSERT INTO aspiration VALUES (12,’PK’,10,’le’); INSERT INTO aspiration VALUES (13,’PK’,10,’le’); – Kermit aspires to achieve 2-person bobsledding at proficiency 20 in Duluth INSERT INTO aspiration VALUES (7,’B2’,20,’d’); – Bannon and Mieska aspire to achieve 4x100 meter track relay at – proficiency of 85 in Seattle, WA. INSERT INTO aspiration VALUES (1,’TR4’,85,’sw’); INSERT INTO aspiration VALUES (5,’TR4’,85,’sw’);

– Thor, Rapinoe, and Kermit form debate team in Seattle, WA and – asppire to achieve at 80% of maxProficiency INSERT INTO aspiration VALUES (9,’D3’,8,’sw’); INSERT INTO aspiration VALUES (13,’D3’,8,’sw’); INSERT INTO aspiration VALUES (7,’D3’,8,’sw’);

– no 2-person canoeing achievements, but some have aspirations

– Carlis and Bannon aspire to achieve 2-person canoeing in Bemidji – with proficiency of 9 INSERT INTO aspiration VALUES (6,’C2’,9,’b’); INSERT INTO aspiration VALUES (1,’C2’,9,’b’);

– Thor, Elastigirl do not aspire to anything

– fake print select ‘aspiration inserts done.’ as ‘’;

– —————— role – ——————————- CREATE TABLE role (

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 335)

Unexpected indentation.

roleName VARCHAR(20) NOT NULL PRIMARY KEY

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 336)

Block quote ends without a blank line; unexpected unindent.

);

INSERT INTO role VALUES (‘first leg’); – 4x100 track INSERT INTO role VALUES (‘second leg’); – 4x100 track INSERT INTO role VALUES (‘third leg’); – 4x100 track INSERT INTO role VALUES (‘anchor leg’); – 4x100 track INSERT INTO role VALUES (‘pilot’); – 2-crew bobsled INSERT INTO role VALUES (‘brakeman’); – 2-crew bobsled INSERT INTO role VALUES (‘right leg’); – 3-legged race INSERT INTO role VALUES (‘left leg’); – 3-legged race INSERT INTO role VALUES (‘stern paddler’); – 2-person canoeing INSERT INTO role VALUES (‘bow paddler’); – 2-person canoeing INSERT INTO role VALUES (‘first speaker’); – Australasia debating INSERT INTO role VALUES (‘second speaker’);– Australasia debating INSERT INTO role VALUES (‘team captain’); – Australasia debating

– fake print select ‘role inserts done.’ as ‘’;

– —————— contribution – ——————————- CREATE TABLE contribution (

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 357)

Unexpected indentation.

creatureId INTEGER NOT NULL REFERENCES creature(creatureId), achId INTEGER NOT NULL REFERENCES achievement(achId), skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode), roleName VARCHAR(20) REFERENCES role(roleName), PRIMARY KEY (creatureId, achId)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 362)

Block quote ends without a blank line; unexpected unindent.

);

– Thor (right leg) achieves three-legged race in Metroville (with Elastigirl (left leg)) INSERT INTO contribution VALUES (9, 12, ‘THR’, ‘right leg’); INSERT INTO contribution VALUES (10, 13, ‘THR’, ‘left leg’); – Kermit ‘pilots’ 2-crew bobsledding – with Thor as brakeman INSERT INTO contribution VALUES (7, 14, ‘B2’, ‘pilot’); INSERT INTO contribution VALUES (9, 15, ‘B2’, ‘brakeman’); – – keep track relay, have 4 people: – Neff #4 (first leg), Mieska(second leg), Myers (third leg), Bannon (anchor leg) INSERT INTO contribution VALUES (4, 16, ‘TR4’, ‘first leg’); INSERT INTO contribution VALUES (5, 17, ‘TR4’, ‘second leg’); INSERT INTO contribution VALUES (2, 18, ‘TR4’, ‘third leg’); INSERT INTO contribution VALUES (1, 19, ‘TR4’, ‘anchor leg’); – Thor (second speaker), Rapinoe (team captain), and Kermit (first speaker) form debate team INSERT INTO contribution VALUES (7, 22, ‘D3’, ‘first speaker’); INSERT INTO contribution VALUES (9, 20, ‘D3’, ‘second speaker’); INSERT INTO contribution VALUES (13, 21, ‘D3’, ‘team captain’);

– – no 2-person canoeing contributions, but some have aspirations

– fake print select ‘contribution inserts done.’ as ‘’;

– —————— aspiredContribution – ——————————- CREATE TABLE aspiredContribution (

System Message: ERROR/3 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 392)

Unexpected indentation.

creatureId INTEGER NOT NULL REFERENCES creature(creatureId), skillCode VARCHAR(3) NOT NULL REFERENCES skill(skillCode), roleName VARCHAR(20) REFERENCES role(roleName), PRIMARY KEY (creatureId, skillCode)

System Message: WARNING/2 (/home/bmiller/Runestone/books/MasteringDatabases/_sources/08TinyDB/creatureDB_mysql.sql, line 396)

Block quote ends without a blank line; unexpected unindent.

);

– no 2-person canoeing contributions, but Carlis and Bannon have aspirations INSERT INTO aspiredContribution VALUES (6, ‘C2’, ‘stern paddler’); INSERT INTO aspiredContribution VALUES (1, ‘C2’, ‘bow paddler’);

– Bannon and Mieska aspire to contribute to achieve 4x100 meter track relay – Bannon contributed in his aspired to role, Mieska had a different – aspired to role than he ultimately contributed to INSERT INTO aspiredContribution VALUES (1, ‘TR4’, ‘anchor leg’); INSERT INTO aspiredContribution VALUES (5, ‘TR4’, ‘third leg’);

– Kermit aspires to contribute to piloting bobsled INSERT INTO aspiredContribution VALUES (7, ‘B2’, ‘pilot’);

– Thor, Rapinoe and Kermit aspire to contribute to debate INSERT INTO aspiredContribution VALUES (7, ‘D3’, ‘first speaker’); INSERT INTO aspiredContribution VALUES (9, ‘D3’, ‘second speaker’); INSERT INTO aspiredContribution VALUES (13, ‘D3’, ‘team captain’);

– Elastigirl, others not aspiring to contribute to anything

– fake print select ‘aspiredContribution inserts done.’ as ‘’;

You have attempted of activities on this page