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