Question

Consider a social network database that has two relations:

Person (pid, name)

Relationship (pid1, rel, pid2)

Person.pid is the key for Person, and Relationship.pid1 and Relationship.pid2 are foreign keys to Person. rel is a string representing the relationship type, and it can only be ’friend’ or ’enemy’. A tuple (1,’friend’,2), means that the person with id 1 considers the person with id 2 a friend. Note that the relationship is not symmetric: if A is friends with B, it does not imply that B is friend with A.

a) Create a query that contains pairs of people who share mutual feelings for each other (i.e., they consider each other friends or enemies). The query should return (name1,name2). Note that the query should not repeat the same pair of people in different order (e.g., (A,B) and (B,A)); your query should only keep the pair, such that the name with the lowest pid appears first.

Answer 1

a)

(Select P1.name,P2.name from Person P1 inner join Relationship in P1.pid = Relationship.pid1 inner join Person P2 on P2.pid = Relationship.pid2 and rel = 'friend' order by P1.pid) UNION (Select P1.name,P2.name from Person P1 inner join Relationship in P1.pid = Relationship.pid1 inner join Person P2 on P2.pid = Relationship.pid2 and rel = 'enemy' order by P1.pid)

Do ask if any doubt. Please upvote.