samedi 6 juin 2015

Similar commands but totally different results

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.

One Commands are :

select distinct name, grade
from Highschooler s1
where not exists (select * from Highschooler s2 join Friend on (Friend.ID2 = s2.ID)
                  where s1.ID = Friend.ID1 
                  and s2.grade <> s1.grade)
 order by grade, name 

The second commands are:

select distinct name, grade
from Highschooler s1 join Friend on (Friend.ID1 = s1.ID)
where not exists (select * from Highschooler s2
                   where Friend.ID2 = s2.ID
                  and s2.grade <> s1.grade)
 order by grade, name 

Both of these commands says we want to find out Highschooler with friends only in the same grades. But the results are different. Which one is correct and WHAT IS THE OTHER ONE TELL US ABOUT? I will really appreciate it if some experts can provide some explanations,

Aucun commentaire:

Enregistrer un commentaire