4

I am doind a free Stanford online course (which is pretty cool, you should check that out) and I've been racking my brains for the lest 2 days and can't find an answer to the following problem. Please help.

Question 4 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.

When I finally thought that I had the answer my query returned all the values from the table Friend.

This is the best I could come up with.

select h1.id, h1.name, h1.grade, h2.id, h2.name, h2.grade
from friend f1
join highschooler h1 on f1.id1 = h1.id
join highschooler h2 on f1.id2 = h2.id
where h1.grade = any (select h3.grade from friend f2
                    join highschooler h3 on f2.id1 = h3.id
                    where h3.id = f1.id1)

I'm required to run the query in SQL Lite. I'm using http://sqlfiddle.com to test my queries in SQL Lite and here is the sample data I'm using.

/* Create the schema for our tables */
create table Highschooler(ID int, name text, grade int);
create table Friend(ID1 int, ID2 int);
create table Likes(ID1 int, ID2 int);

/* Populate the tables with our data */
insert into Highschooler values (1510, 'Jordan', 9);
insert into Highschooler values (1689, 'Gabriel', 9);
insert into Highschooler values (1381, 'Tiffany', 9);
insert into Highschooler values (1709, 'Cassandra', 9);
insert into Highschooler values (1101, 'Haley', 10);
insert into Highschooler values (1782, 'Andrew', 10);
insert into Highschooler values (1468, 'Kris', 10);
insert into Highschooler values (1641, 'Brittany', 10);
insert into Highschooler values (1247, 'Alexis', 11);
insert into Highschooler values (1316, 'Austin', 11);
insert into Highschooler values (1911, 'Gabriel', 11);
insert into Highschooler values (1501, 'Jessica', 11);
insert into Highschooler values (1304, 'Jordan', 12);
insert into Highschooler values (1025, 'John', 12);
insert into Highschooler values (1934, 'Kyle', 12);
insert into Highschooler values (1661, 'Logan', 12);

insert into Friend values (1510, 1381);
insert into Friend values (1510, 1689);
insert into Friend values (1689, 1709);
insert into Friend values (1381, 1247);
insert into Friend values (1709, 1247);
insert into Friend values (1689, 1782);
insert into Friend values (1782, 1468);
insert into Friend values (1782, 1316);
insert into Friend values (1782, 1304);
insert into Friend values (1468, 1101);
insert into Friend values (1468, 1641);
insert into Friend values (1101, 1641);
insert into Friend values (1247, 1911);
insert into Friend values (1247, 1501);
insert into Friend values (1911, 1501);
insert into Friend values (1501, 1934);
insert into Friend values (1316, 1934);
insert into Friend values (1934, 1304);
insert into Friend values (1304, 1661);
insert into Friend values (1661, 1025);
insert into Friend select ID2, ID1 from Friend;

insert into Likes values(1689, 1709);
insert into Likes values(1709, 1689);
insert into Likes values(1782, 1709);
insert into Likes values(1911, 1247);
insert into Likes values(1247, 1468);
insert into Likes values(1641, 1468);
insert into Likes values(1316, 1304);
insert into Likes values(1501, 1934);
insert into Likes values(1934, 1501);
insert into Likes values(1025, 1101);

Thank you in advance.

Regards.

Cesar

Cesar Zapata
  • 196
  • 1
  • 7
  • 15
  • 1
    That's a great course, I did it last year. Didn't they have a forum where you can ask questions? – Andomar Feb 09 '13 at 12:02
  • Yeah, they do. But I could not find any help there. I guess many students are having a hard time on this one or they simply can't share the information or something. – Cesar Zapata Feb 09 '13 at 12:09

3 Answers3

4

So we want to find students for whom there are no students in other grades they have a friendship relationship, right? This is one way to express that:

select * from highschooler h
where not exists
(select 1 from highschooler h2 where h2.grade != h.grade and exists
(select 1 from friends f where (f.id1 = h.id or f.id2 = h.id) and (f.id1 = h2.id or f.id2 = h2.id)))
order by grade, name

EDIT: If you also require them to have at least one friend, you'll need to check for that too

Patashu
  • 21,443
  • 3
  • 45
  • 53
  • OMG man. You are a genius. You solved it at a glance. I wish I was that good at SQL. ;) Thank you very much. This is exactly what I was looking for. – Cesar Zapata Feb 09 '13 at 12:07
  • 1
    @Cesar Zapata The hard part is wrapping your head around all the ways you can use subqueries and the kinds of joins, then you're good to go - at least until you need to do temporary tables or cursors or pivots or connect or something :P – Patashu Feb 09 '13 at 12:09
  • It's still really hard for me to use sub-queries and all the different types of operators and I get stuck thinking in the way I'd do it with a programming logic where you just break the code down and move one step at a time, separate values, keep them in variables and manipulate the code step by step. I still have a hard time to understand how is SQL going to compare tuples or columns and all... Its still very confusing. But I'll get there. Thank you so much for your time and help. Regards from Brazil. Cesar. – Cesar Zapata Feb 09 '13 at 12:17
4

My solution:

SELECT name, grade
FROM Highschooler
WHERE ID NOT IN
(SELECT ID1
FROM Friend F1 JOIN Highschooler H1
ON H1.ID = F1.ID1
JOIN Highschooler H2
ON H2.ID = F1.ID2
WHERE H1.grade <> H2.grade)
ORDER BY grade, name

Essentially, the inner sub-query returns a relation of students with friends that have varying grades (where H1.grade <> to H2.grade). Then the outer query simply lists all students that don't feature in this inner relation.

amjo324
  • 353
  • 1
  • 3
  • 7
1

I prefer this solution because it uses only content seen in the course and also works if there are students without friends.

select distinct name, grade
from highschooler h join friend f on h.id = f.id1
where id not in
    (select id1
    from friend f join highschooler h2 on h2.id = f.id2
    where h.grade <> h2.grade)
order by grade, name;

One thing to notice about the question is a rule about the Friend table. Friendship is mutual, so if (123, 456) is in the Friend table, so is (456, 123).

SpinnerZ
  • 11
  • 1
  • 3