Simple SQL question with subqueries

I'm refreshing my SQL with the online Stanford database class exercises, found here.开发者_Go百科 Here is the problem:

"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."

We have a highschooler table, with the attributes name, grade, id. Also, the likes table has attributes id1 and id2. id1 and id2 in likes correspond to id in highschooler.

Based on the problem section this comes from, I can tell that I'll need to use subqueries, but I'm not sure where. How should I approach this problem? None of the currently suggested solutions work.

Here is my current SQL statement, that is not working correctly (ignoring sorting):

select distinct 
  student1.id, 
  student1.name, 
  student1.grade 
from 
  highschooler student1, 
  highschooler student2, 
  friend 
where not exists (select * 
                  from friend 
                  where student1.id = id1 
                    and student2.id = id2 
                    and student1.grade = student2.grade 
                    and student1.id <> student2.id);

I assumed that, if A was B's Friend, it's equal to B was A's friend.

CREATE VIEW Temp
AS
    SELECT id,name,grade,id2,[grd2] FROM highschooler 
        INNER JOIN Likes ON highschooler.id = Likes.id1         
        INNER JOIN (SELECT id as [id2t], grade as [grd2] from highschooler) a ON a.id2t = Likes.id2             
    UNION ALL 
    SELECT id,name,grade,[id1] as [id2],[grd2] FROM highschooler 
        INNER JOIN Likes ON highschooler.id = Likes.id2         
        INNER JOIN (SELECT id as [id2t], grade as [grd2] from highschooler) a ON a.id2t = Likes.id1     

The temp view let me have all the info i need.

CREATE VIEW PlayWithClassMate 
AS 
    SELECT distinct id FROM Temp WHERE grade = grd2

This PlayWithClassMate view let me have all student who play with her/his classmate (some how, i think a person can play, with all his/her friend not their classmate).

CREATE VIEW IDResult 
AS 
    SELECT id FROM (
        SELECT id, COUNT(GRD2) as c FROM TEMP 
            WHERE id in (SELECT id FROM PlayWithClassMate)
            GROUP BY ID) A
    WHERE C>1

this IDResult view have all the id the question ask you. Now, select whatever you need, inwhich its ID in IDResult

i think it's not the best, or it may be the worst, but it work. (srr abt terribe grammar)

This is harder than it looks, because it requires preparing sets sequentially. But, there are a few ways to solve this one. Here's what quickly comes to mind:

First, find the friend-of-friend for everybody by grade producing something like:

[ID], [FoF ID], [Grade of FoF]

You really don't need [FoF ID], but it might help when debugging.

Then, as a second-order operation, you'll need to produce a list of [ID]s where [Grade of FoF] is equal to both the MAX() and MIN():

SELECT [ID], MAX(Grade of FoF) as A, MIN(Grade of FoF) as B FROM [the above] WHERE A=B

UPDATE: I realized that I should also add that in the final qry: A=B and A=Grade. Then this solution works. Keep in mind: it only answers the question "Find names and grades of students who only have friends in the same grade." and it assumes friendship is one-directional. (Sorry, I had to leave something undone.)

For those that need to see some SQL, here you are. It's written for MS Access, but easily ported (start by removing the "()" in the inner-most query) to MySQL, PGSQL, or Oracle. Better still, no procedural extensions and no temp tables.

SELECT name FROM ( SELECT ID ,name ,grade ,min( friend_grade) as min_friend_grade ,max( friend_grade) as max_friend_grade FROM ( SELECT hs1.ID ,hs1.name ,hs1.grade ,l.ID2 as friend_id ,hs2.name as friend_name ,hs2.grade as friend_grade

FROM ( highschooler hs1 INNER JOIN likes l ON (hs1.ID = l.ID1) ) INNER JOIN highschooler hs2 ON (l.ID2 = hs2.ID)

)FoF

GROUP BY ID ,name ,grade

)FoF_max_min

WHERE grade=min_friend_grade AND min_friend_grade=max_friend_grade