Author 
Topic 

mikkel
Starting Member
Norway
1 Posts 
Posted  03/01/2011 : 11:23:23

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 

MIK_2008
Flowing Fount of Yak Knowledge
Pakistan
1054 Posts 
Posted  03/01/2011 : 11:44:10

come up with sample data of your table, identify the primary key and foreign key relationship/column of both and the desired output you are looking for in light of your sample info.
Cheers MIK 


chriscairns
Starting Member
2 Posts 
Posted  05/30/2012 : 03:50:03

Facebook is a great way to stay in touch with those you care about, but it is also a great tool you can use to market your business.
unspammed 


visakh16
Very Important crosS Applying yaK Herder
India
52325 Posts 
Posted  05/30/2012 : 16:05:29

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
your query is not correct
it should be
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'
 SQL Server MVP http://visakhm.blogspot.com/



visakh16
Very Important crosS Applying yaK Herder
India
52325 Posts 
Posted  05/30/2012 : 16:05:58

try similarly for other queries
 SQL Server MVP http://visakhm.blogspot.com/




Topic 


