12.1. An often-used Combination¶
In this brief interlude we will see a pattern that occurs fairly often in many queries. It combines all of the unary operators and Times. Try to study it to be certain that you understand what is in each result (I have made a note to help in one case). Note what an interesting result we can obtain.
The English query is:
How does each creature’s achieved skill proficiency compare to other creatures who have achieved the best proficiency at that skill?
Note here that we will need to know what the maximum score achieved per skill was (implying group). The words “compare to” in the query require us to interpret them in some way. In this case, I chose to compute the difference between each creature’s achievement proficiency at that skill and the maximum achieved for that skill by any creature (this implies a computed column, which we interesting will add to a reduce- check that out below). A value of zero means that creature achieved the maximum, and the higher the value, the farther from the maximum they were.
The Precedence Chart
Here is our longest precedence chart so far- it does get the job done and demonstrates the power of what you can do with relational algebra.
In this chart, I chose to show achievement twice at the top. It could be included once- it wouldn’t change the query. I chose this way of visualizing it because when we convert it into SQL, the top left portion and the top right portion before the times are best completed as separate queries where an intermediate relation is produced. The two intermediate relations then are used in the Times to create another intermediate relation.
Tip
Notice in the chart above that the result relation name after the Times gets long, and in is useful to put the A and B input relation names in parentheses. Then to the left of the entity I chose to shorten the name going forward in the chart. This is a practice that I use most of the time on bigger charts: show the detail inside the relation box just after the operation like Times, then shorten it going forward.
The longer SQL query with intermediate relations:
In the following SQL code tabs, the creation of the intermediate relations is in the second tab. Study this code carefully and compare it to the chart above. You could do this by downloading the SQL Intermediate code as a text file and viewing it your favorite editor.
Tip
The use of intermediate relations to complete a longer query like this makes the result possible to attain with fewer errors. It is sometimes possible to create single long complex SQL queries for some charts, but debugging them and reading them later is very hard. The use of intermediate relations that match points in the precedence chart like we have done here makes producing correct results the first time much easier.
achId |
creatureId |
skillCode |
proficiency |
achDate |
test_townId |
|---|---|---|---|---|---|
1 |
1 |
A |
3 |
2020-07-24 21:37:53 |
a |
2 |
1 |
E |
3 |
2017-09-15 15:35:00 |
d |
3 |
1 |
A |
3 |
2018-07-14 14:00:00 |
a |
4 |
1 |
E |
3 |
2020-07-24 21:37:53 |
d |
5 |
5 |
Z |
6 |
2016-04-12 15:42:30 |
t |
6 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
7 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
8 |
3 |
Z |
4 |
2018-07-15 00:00:00 |
be |
9 |
4 |
Z |
3 |
2018-06-10 00:00:00 |
a |
10 |
11 |
PK |
10 |
1998-08-15 00:00:00 |
le |
11 |
12 |
PK |
10 |
2016-05-24 00:00:00 |
le |
12 |
13 |
PK |
10 |
2012-08-06 00:00:00 |
le |
13 |
8 |
PK |
1 |
NULL |
t |
14 |
9 |
THR |
10 |
2018-08-12 14:30:00 |
mv |
15 |
10 |
THR |
10 |
2018-08-12 14:30:00 |
mv |
16 |
7 |
B2 |
19 |
2017-01-10 16:30:00 |
d |
17 |
9 |
B2 |
19 |
2017-01-10 16:30:00 |
d |
18 |
4 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
19 |
5 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
20 |
2 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
21 |
1 |
TR4 |
85 |
2012-07-30 00:00:00 |
le |
22 |
9 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
23 |
13 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |
24 |
7 |
D3 |
8 |
2020-07-24 16:37:53 |
sw |