quote:

---

Originally posted by mikkel

i have task in sql, but i am not sure that is correct...

can you help me with seeing about this correct

A very simple model for social networking like for instance Facebook could be modeled by these two relations:

person(name, age, gender)
relto(name1, name2, kind)

where we assume that a name uniquely identify persons and persons can be related to each other in different kinds of relationships, i.e. friends, married, colleagues, schoolmates, etc.

qeustion 1

Find the name and age of all John’s cousins. Order the list by age with the oldest first.

my answer

SELECT name, age
FROM person
WHERE kind = cousins;

I have som more qeustions you could help me with

Find each friend of John that is not a colleague.

Find the number of relations for each kind of relationship for John, Joe, and Nelly.

What is the average number, taken over all persons, of relations in each kind of relationship?

mikkel

---

SELECT p1.name, p1.age
FROM person p
inner join relto r
on r.name1 = p.name
inner join person p1
on p1.name = r.name2
WHERE r.kind = 'cousins'

union all

SELECT p1.name, p1.age
FROM person p
inner join relto r
on r.name2 = p.name
inner join person p1
on p1.name = r.name1
WHERE r.kind = 'cousins'