The database is:
Highschooler ( ID, name, grade ) English: There is a high school student with unique ID and a given first name in a certain grade.
Friend ( ID1, ID2 ) English: The student with ID1 is friends with the student with ID2. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123).
Likes ( ID1, ID2 ) English: The student with ID1 likes the student with ID2. Liking someone is not necessarily mutual, so if (123, 456) is in the Likes table, there is no guarantee that (456, 123) is also present.
The task is to: Find names and grades of students who only have friends in the same grade. Return the result sorted by grade, then by name within each grade.
These two commands seem should generate same result, but the fact is not.
The correct command is:
select distinct name, grade
from Highschooler s1
where not exists (select * from Highschooler s2, Friend
where Friend.ID2 = s2.ID
and s1.ID = Friend.ID1
and s2.grade <> s1.grade)
order by grade, name
But I also think the following command which only change the position of Friend also make sense:
select distinct name, grade
from Highschooler s1, Friend
where not exists (select * from Highschooler s2
where Friend.ID2 = s2.ID
and s1.ID = Friend.ID1
and s2.grade <> s1.grade)
order by grade, name
But the latter one only return all the value in table Highschooler.
I really thinks that change the position of Friend won't make a difference in the result but it seems that the result is different.
This phenomenon is confusing and I really appreciate it if some experts can provide some help.
Aucun commentaire:
Enregistrer un commentaire