11.3. Complex Examples of Union, Intersect, and Minus¶

In the last section we used two simple single-column relations to visualize the set operations. Now let’s see just a bit interest that can be added to queries, making them behave more ‘relationally’.

Suppose now we look at two different relations that we can derive as result relations in this database that have the same base. Let’s recall the schema for this database and introduce a relation that we haven’t used yet.

Find the entity named Aspiration, which maps to a relation also named Aspiration. This data represents creatures who have aspired to achieve a skill in a particular town with a particular proficiency (they may or may not have achieved it with the same proficiency or in the same town). A point to note about the schema is what is the same and different about the Achievement and Aspiration entities and their corresponding relations. Note that they have four columns in common:

creatureId,
skillCode,
proficiency or aspiredProficiency, and
test_townId or desired_townId.

Here is the Aspiration data:

**Aspiration**¶
creatureId	skillCode	aspiredProficiency	desired_townId
1	A	3	a
1	C2	9	b
1	E	4	b
1	TR4	85	sw
1	Z	3	be
2	A	3	NULL
3	A	8	b
3	Z	5	be
4	E	3	g
5	TR4	85	sw
5	Z	10	d
6	C2	9	b
6	Z	3	le
7	B2	20	d
7	D3	8	sw
7	E	3	b
8	O	4	t
9	D3	8	sw
11	PK	10	le
12	PK	10	le
13	D3	8	sw
13	PK	10	le

To compare data in Aspiration and Achievement, we need relations with the same number of columns, with data in each column that can be compared. Let’s use a Reduce on Achievement to get the same four columns with like types and similar data and nearly identical names. Here is a chart for that:

Now we can use this achieved skill data to compare to the aspired skill data in Aspiration, which has the same columns with like data that can be compared.

The SQL to create and keep the result relation from the above Reduce operation is in the second tab below. Run the first tab to see what the data looks like.

DROP TABLE IF EXISTS achievement;

CREATE TABLE achievement (
 achId              INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 creatureId         INTEGER,
 skillCode          VARCHAR(3),
 proficiency        INTEGER,
 achDate            TEXT,
 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) [he did not improve]
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (1, 'A', 3, datetime('now'), 'a');
 -- Bannon floats in Anoka (where he aspired)
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (1, 'A', 3, datetime('2018-07-14 14:00'), 'a');

-- Bannon swims in Duluth (he aspired in Bemidji) [he improved]
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (1, 'E', 4, datetime('now'), 'd');
 -- Bannon swims in Duluth (he aspired in Bemidji)
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (1, 'E', 3, datetime('2017-09-15 15:35'), 'd');

-- Bannon doesn't gargle
 -- Mieska gargles in Tokyo (had no aspiration to)
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (5, 'Z', 6, datetime('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,
                          achDate, test_townId)
                 VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be');
 -- 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,
                          achDate, test_townId)
                 VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be');

-- Beckham achieves PK in London
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (11, 'PK', 10, datetime('1998-08-15'), 'le');
 -- Kane achieves PK in London
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (12, 'PK', 10, datetime('2016-05-24'), 'le');
 -- Rapinoe achieves PK in London
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (13, 'PK', 10, datetime('2012-08-06'), 'le');
 -- 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,
                          achDate, test_townId)
                 VALUES (8, 'PK', 1, NULL, 't');

-- Thor achieves three-legged race in Metroville (with Elastigirl)
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (9, 'THR', 10, datetime('2018-08-12 14:30'), 'mv');
 -- Elastigirl achieves three-legged race in Metroville (with Thor)
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (10, 'THR', 10, datetime('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,
                          achDate, test_townId)
                 VALUES (7, 'B2', 19, datetime('2017-01-10 16:30'), 'd');
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (9, 'B2', 19, datetime('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,
                          achDate, test_townId)
                 VALUES (4, 'TR4', 85, datetime('2012-07-30'), 'le');
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (5, 'TR4', 85, datetime('2012-07-30'), 'le');
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (2, 'TR4', 85, datetime('2012-07-30'), 'le');
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (1, 'TR4', 85, datetime('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,
                          achDate, test_townId)
                 VALUES (9, 'D3', 8, datetime('now', 'localtime'), 'sw');
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (13, 'D3', 8, datetime('now', 'localtime'), 'sw');
 INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                 VALUES (7, 'D3', 8, datetime('now', 'localtime'), 'sw');

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

DROP TABLE IF EXISTS aspiration;

CREATE TABLE aspiration
( -- foreign key
  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)
);

-- 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

Now we have two relations that are compatible for use with the set operators Union, Intersect, and Minus, because they have the same four columns of data that can be compared:

The result relation from the Reduce on Achievement, with the rather long, yet descriptive name AchievedSkillInTownWithProficiency. When you run the query above, it produced 20 instances/rows in the result.
The Aspiration relation. This has 22 different instances/rows in it.

The first represents when a creature achieved a skill at a proficiency in a town. The second represents that a creature aspires to achieve that skill at a desired proficiency in a particular town.

Let’s examine what we can do with this.

11.3.1. Queries using all columns¶

One of the most restrictive type of English queries that we can ask from these two relations is like this:

Find each (creatureId, skillCode, proficiency, and townId) combination where a creature achieves a skill at a proficiency in a town AND that creature aspired to achieve that skill at that proficiency in that town.

The word AND in the above query is highlighted to indicate that this is an Intersection query.

When this Intersection is performed, each row is treated as one instance in each relation. Each row in AchievedSkillInTownWithProficiency is compared to each row in Aspiration, and all four values in each column must match for the row to be included in the result relation.

11.3.1.1. Exercise¶

Experiment with changing the keyword INTERSECT to UNION and also to EXCEPT in the above SQL. What are the corresponding English queries for each of these and how does the name of the result relation change? Practice drawing the precedence charts for these queries with a drawing tool.

11.3.2. Queries with some, but not all columns¶

With the same original input relations, AchievedSkillInTownWithProficiency, and Aspiration, each of which are identified by creatureId and skillCode. This makes them effectively the same base, because they are both Creature-Skill Pairs. We can ask other queries by first using a Project on each relation and then using the set operators. For example, we can ask the following, regardless of town or proficiency score.

Find each creatureId and skillCode of Creature who has achieved AND aspired to achieve that Skill.

Here is SQL for this query:

11.3.3. Additional Exercises¶

First, try sketching out the precedence chart for any of the queries that look interesting to you or you think you could use some practice with. Note that you may wish to re-phrase the query to make it easier to draw out the chart.

Recall this summary from the beginning of this chapter for which operations go with words used in the queries:

Input	Operator	Input	Generic Phrase
A	Union	B	A or B
A	Intersect	B	A and B
A	Minus	B	A but not B
B	Minus	A	B but not A

Tip

Hopefully by now you realize that making a note of this table and having it nearby is helpful in guiding your way towards mastery.

English Queries

Find each creatureId and skillCode of Creature who has achieved OR aspired to achieve that Skill.

Find each creatureId and skillCode of Creature who has achieved BUT NOT aspired to achieve that Skill.

Find each creatureId and skillCode of Creature who has aspired to achieve BUT NOT achieved that Skill.

Find each skillCode of skill that is neither achieved nor aspired to.

Find each skillCode of skill that is is not both achieved and aspired to.

Find each unachieved skill by frog creatures.

Find each creature who has achieved more than one skill.

Find each creature whose average skill level is greater than 1.

Find each creature whose minimum skill level achieved is >=2.

Find creatureId of each creature who has achieved floating and gargling.

Find creatureId of each creature who has achieved floating but not gargling.

Once you have a chart, then use the code area below to try the SQL.

-- ------------------   town -- -------------------------------

DROP TABLE IF EXISTS 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',
                          'Philly', 'Let brotherly love endure');
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');
INSERT INTO town VALUES ('t', 'Tokyo', 'Kanto', 'Japan', NULL, NULL);
INSERT INTO town VALUES ('as', 'Asgard', NULL, NULL,
                          'Home of Odin''s vault',
                          'Where magic and science are one in the same');
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');

-- ------------------   creature -- -------------------------------
DROP TABLE IF EXISTS 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,
FOREIGN KEY(idol_creatureId) REFERENCES creature(creatureId)
);

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);

-- ------------------   skill -- -------------------------------
DROP TABLE IF EXISTS 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

-- ------------------  teamSkill  -- -------------------------------
DROP TABLE IF EXISTS 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);

-- ------------------  achievement  -- -------------------------------
DROP TABLE IF EXISTS achievement;

CREATE TABLE achievement (
achId              INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
creatureId         INTEGER,
skillCode          VARCHAR(3),
proficiency        INTEGER,
achDate            TEXT,
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,
                          achDate, test_townId)
                VALUES (1, 'A', 3, datetime('now'), 'a');

-- Bannon swims in Duluth (he aspired in Bemidji)
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (1, 'E', 3, datetime('2017-09-15 15:35'), 'd');
-- Bannon floats in Anoka (where he aspired)
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (1, 'A', 3, datetime('2018-07-14 14:00'), 'a');

-- Bannon swims in Duluth (he aspired in Bemidji)
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (1, 'E', 3, datetime('now'), 'd');
-- Bannon doesn't gargle
-- Mieska gargles in Tokyo (had no aspiration to)
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (5, 'Z', 6, datetime('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,
                          achDate, test_townId)
                VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be');
-- 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,
                          achDate, test_townId)
                VALUES (3, 'Z', 4, datetime('2018-07-15'), 'be');

-- Beckham achieves PK in London
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (11, 'PK', 10, datetime('1998-08-15'), 'le');
-- Kane achieves PK in London
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (12, 'PK', 10, datetime('2016-05-24'), 'le');
-- Rapinoe achieves PK in London
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (13, 'PK', 10, datetime('2012-08-06'), 'le');
-- Godzilla 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,
                          achDate, test_townId)
                VALUES (8, 'PK', 1, NULL, 't');

-- -------------------- -------------------- -------------------
-- Thor achieves three-legged race in Metroville (with Elastigirl)
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (9, 'THR', 10, datetime('2018-08-12 14:30'), 'mv');
-- Elastigirl achieves three-legged race in Metroville (with Thor)
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (10, 'THR', 10, datetime('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,
                          achDate, test_townId)
                VALUES (7, 'B2', 19, datetime('2017-01-10 16:30'), 'd');
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (9, 'B2', 19, datetime('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,
                          achDate, test_townId)
                VALUES (4, 'TR4', 85, datetime('2012-07-30'), 'le');
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (5, 'TR4', 85, datetime('2012-07-30'), 'le');
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (2, 'TR4', 85, datetime('2012-07-30'), 'le');
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (1, 'TR4', 85, datetime('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,
                          achDate, test_townId)
                VALUES (9, 'D3', 8, datetime('now', 'localtime'), 'sw');
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (13, 'D3', 8, datetime('now', 'localtime'), 'sw');
INSERT INTO achievement (creatureId, skillCode, proficiency,
                          achDate, test_townId)
                VALUES (7, 'D3', 8, datetime('now', 'localtime'), 'sw');

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

-- ------------------  aspiration  -- -------------------------------
DROP TABLE IF EXISTS aspiration;

-- 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

-- ------------------  role  -- -------------------------------
DROP TABLE IF EXISTS role;
CREATE TABLE role
(
  roleName VARCHAR(20)   NOT NULL PRIMARY KEY
);

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

-- ------------------  contribution  -- -------------------------------
DROP TABLE IF EXISTS contribution;
CREATE TABLE contribution (
    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)
);

-- 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

-- ------------------  aspiredContribution  -- -------------------------------
DROP TABLE IF EXISTS aspiredContribution;
CREATE TABLE aspiredContribution (
    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)
);

-- 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

You have attempted of activities on this page