13.12. Same Relation A and B, M - M match over cols Aid(D),Bid(D)¶

Now let’s look at matching over one of the columns in the creature relation that is not its identifier: reside_townId.

Here is the Creature information again:

Creature entity and its columns in the corresponding table

English Query

Find each ordered pair of creatures that reside in the same town.

Precedence Chart

In the example below, we must use the syntax that similar to the times from the set operator chapter, as given in the first tab. We add the WHERE clause to get the filter. We project by eliminating reside_townId from C2.

Note how this is an ordered creature pair.

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

13.12.1. Other Queries to try:¶

Try creating the precedence charts for these queries.

English Query:

Find each ordered pair of skills that originate in the same town.

Find each ordered pair of achievements that were tested in the same town.