SQL - Social-Network Query Exercises

core-set.sql

-- 1. Find the names of all students who are friends with someone named Gabriel. 

SELECT H1.name
FROM Highschooler H1
INNER JOIN Friend ON H1.ID = Friend.ID1
INNER JOIN Highschooler H2 ON H2.ID = Friend.ID2
WHERE H2.name = "Gabriel";


-- 2. For every student who likes someone 2 or more grades younger than themselves, return that student's name and grade, and the name and grade of the student they like. 

SELECT H1.name, H1.grade, H2.name, H2.grade
FROM Highschooler H1
INNER JOIN Likes ON H1.ID = Likes.ID1
INNER JOIN Highschooler H2 ON H2.ID = Likes.ID2
WHERE (H1.grade - H2.grade) >= 2;


-- 3. For every pair of students who both like each other, return the name and grade of both students. Include each pair only once, with the two names in alphabetical order.

SELECT H1.name, H1.grade, H2.name, H2.grade
FROM Highschooler H1, Highschooler H2, Likes L1, Likes L2
WHERE (H1.ID = L1.ID1 AND H2.ID = L1.ID2) AND (H2.ID = L2.ID1 AND H1.ID = L2.ID2) AND H1.name < H2.name
ORDER BY H1.name, H2.name;


-- 4. Find all students who do not appear in the Likes table (as a student who likes or is liked) and return their names and grades. Sort by grade, then by name within each grade.

SELECT name, grade
FROM Highschooler
WHERE ID NOT IN (
  SELECT DISTINCT ID1
  FROM Likes
  UNION
  SELECT DISTINCT ID2
  FROM Likes
)
ORDER BY grade, name;


-- 5. For every situation where student A likes student B, but we have no information about whom B likes (that is, B does not appear as an ID1 in the Likes table), return A and B's names and grades. 

SELECT H1.name, H1.grade, H2.name, H2.grade
FROM Highschooler H1
INNER JOIN Likes ON H1.ID = Likes.ID1
INNER JOIN Highschooler H2 ON H2.ID = Likes.ID2
WHERE (H1.ID = Likes.ID1 AND H2.ID = Likes.ID2) AND H2.ID NOT IN (
  SELECT DISTINCT ID1
  FROM Likes
);


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

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


-- 7. For each student A who likes a student B where the two are not friends, find if they have a friend C in common (who can introduce them!). For all such trios, return the name and grade of A, B, and C. 

SELECT DISTINCT H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade
FROM Highschooler H1, Highschooler H2, Highschooler H3, Likes L, Friend F1, Friend F2
WHERE (H1.ID = L.ID1 AND H2.ID = L.ID2) AND H2.ID NOT IN (
  SELECT ID2
  FROM Friend
  WHERE ID1 = H1.ID
) AND (H1.ID = F1.ID1 AND H3.ID = F1.ID2) AND (H2.ID = F2.ID1 AND H3.ID = F2.ID2);


-- 8. Find the difference between the number of students in the school and the number of different first names. 

SELECT COUNT(*) - COUNT(DISTINCT name)
FROM Highschooler;


-- 9. Find the name and grade of all students who are liked by more than one other student. 

SELECT name, grade
FROM Highschooler
INNER JOIN Likes ON Highschooler.ID = Likes.ID2
GROUP BY ID2
HAVING COUNT(*) > 1;

extras.sql

-- 1. For every situation where student A likes student B, but student B likes a different student C, return the names and grades of A, B, and C.

SELECT H1.name, H1.grade, H2.name, H2.grade, H3.name, H3.grade
FROM Highschooler H1, Highschooler H2, Highschooler H3, Likes L1, Likes L2
WHERE H1.ID = L1.ID1 AND H2.ID = L1.ID2 AND (H2.ID = L2.ID1 AND H3.ID = L2.ID2 AND H3.ID <> H1.ID);


-- 2. Find those students for whom all of their friends are in different grades from themselves. Return the students' names and grades. 

SELECT name, grade
FROM Highschooler H1
WHERE grade NOT IN (
  SELECT H2.grade
  FROM Friend, Highschooler H2
  WHERE H1.ID = Friend.ID1 AND H2.ID = Friend.ID2
);


-- 3. What is the average number of friends per student? (Your result should be just one number.) 

SELECT AVG(count)
FROM (
  SELECT COUNT(*) AS count
  FROM Friend
  GROUP BY ID1
);


-- 4. Find the number of students who are either friends with Cassandra or are friends of friends of Cassandra. Do not count Cassandra, even though technically she is a friend of a friend. 

SELECT COUNT(*)
FROM Friend
WHERE ID1 IN (
  SELECT ID2
  FROM Friend
  WHERE ID1 IN (
    SELECT ID
    FROM Highschooler
    WHERE name = 'Cassandra'
  )
);


-- 5. Find the name and grade of the student(s) with the greatest number of friends. 

SELECT name, grade
FROM Highschooler
INNER JOIN Friend ON Highschooler.ID = Friend.ID1
GROUP BY ID1
HAVING COUNT(*) = (
  SELECT MAX(count)
  FROM (
    SELECT COUNT(*) AS count
    FROM Friend
    GROUP BY ID1
  )
);

modification.sql

-- 1. It's time for the seniors to graduate. Remove all 12th graders from Highschooler.

DELETE FROM Highschooler
WHERE grade = 12;


-- 2. If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple.

DELETE FROM Likes
WHERE ID2 IN (
  SELECT ID2
  FROM Friend
  WHERE Friend.ID1 = Likes.ID1
) AND ID2 NOT IN (
  SELECT L.ID1
  FROM Likes L
  WHERE L.ID2 = Likes.ID1
);

DELETE FROM Likes
WHERE ID1 IN (
  SELECT Likes.ID1 
  FROM Friend
  INNER JOIN Likes USING(ID1) 
  WHERE Friend.ID2 = Likes.ID2
) AND ID2 NOT IN (
  SELECT Likes.ID1 
  FROM Friend
  INNER JOIN Likes USING(ID1) 
  WHERE Friend.ID2 = Likes.ID2
);


-- 3. For all cases where A is friends with B, and B is friends with C, add a new friendship for the pair A and C. Do not add duplicate friendships, friendships that already exist, or friendships with oneself.

INSERT INTO Friend
SELECT DISTINCT F1.ID1, F2.ID2
FROM Friend F1, Friend F2
WHERE F1.ID2 = F2.ID1 AND F1.ID1 <> F2.ID2 AND F1.ID1 NOT IN (
  SELECT F3.ID1
  FROM Friend F3
  WHERE F3.ID2 = F2.ID2
);

INSERT INTO Friend
SELECT F1.ID1, F2.ID2
FROM Friend F1
INNER JOIN Friend F2 ON F1.ID2 = F2.ID1
WHERE F1.ID1 <> F2.ID2
EXCEPT
SELECT * FROM Friend;

Thanks for saving my lab exam

For the question #4, why do ID1 and ID2 have to be distinct?

Thanks for the great resource. Your solutions are a lot more elegant than what I've been coming up with.
I'm not so sure on Extras Q4. I might be missing some subtleties but it seems like your solution doesn't count friends of Cassandra (only friends of friends) and counts Cassandra herself twice.

This is great! It was very helpful but I would like to note that the some of the group by statements don't work when sql_mode = 'ONLY_FULL_GROUP_BY' and that I agree with coroche, the answer for #4 is not really answering the question even though the output is the same.

This was the answer that I came up with. I had to replace the @cassandra variable in the main query in order to work with the SQLite program.

SET @cassandra=(select id from highschooler where name='Cassandra');
select count(*) from (
select f1.id1
from friend f1
where f1.id2 = @cassandra
and f1.id1 != @cassandra
union
select f2.id2
from friend f2
where f2.id1 in (select f1.id1 from friend f1 where f1.id2 = @cassandra)
and f2.id2 != @cassandra
) a

@backpackerhh Savior!
@ibadlisham... I tried this in Oracle for Extra # 4:

select count(distinct f1.ID2) + count(distinct f2.ID2)
from friend f1
inner join friend f2
on f1.ID2 = f2.ID1

inner join highschooler hs
on hs.id = f1.id1

inner join highschooler hs2
on f2.id2 = hs2.id

where f1.ID1 <> f2.ID2
and hs.name = 'Cassandra';

We must receive knowledge from unique sources. These may be books or educational resources. People often confuse the Internet and educational content. Content is the soul of the Internet but not its essence. I recently read an article about the possibilities of a child’s cognitive education. You can use the game form to memorize words and sentences.

Here is my solution for Q4:
SELECT COUNT(ID2) FROM Friend WHERE ID1 IN (SELECT ID2 FROM Friend WHERE ID1 = (SELECT ID FROM Highschooler WHERE name = 'Cassandra') AND ID2 <> ID1);

But I am not sure how come the solution in Extras Q2 works. It seems that the SELECT statement in WHERE clause pulls out the grades of those who HAVE friends. How is this going to find out students whose friends are all from different grades?

Does anyone have the same misgiving? Thanks for any info or explanation!

(H1.ID = Likes.ID1 AND H2.ID = Likes.ID2) is redundant in 5th ques, because join already took care of this case.

Here is my solution for Q4:
SELECT COUNT(ID2) FROM Friend WHERE ID1 IN (SELECT ID2 FROM Friend WHERE ID1 = (SELECT ID FROM Highschooler WHERE name = 'Cassandra') AND ID2 <> ID1);

But I am not sure how come the solution in Extras Q2 works. It seems that the SELECT statement in WHERE clause pulls out the grades of those who HAVE friends. How is this going to find out students whose friends are all from different grades?

Does anyone have the same misgiving? Thanks for any info or explanation!

your solution for Q4 is no difference than all other. even is sql is not answering, you could assume it does. if you dont change schema and keep inserting info, the result wont change. because cassanda is always is going to be friend of cassandra's fried. That's way answer is kind of short cut coorect. but if question was asking name of friends instead of numbe of friends, then it would change all thing. stil your solution not right thou

Here is my solution for Q9:

SELECT name, grade
FROM Highschooler
INNER JOIN Likes ON Highschooler.ID = Likes.ID2
GROUP BY ID2
HAVING COUNT("star symbol") IN (SELECT MAX(likee)
FROM (SELECT ID2, count(*) AS likee
FROM Likes
GROUP BY ID2));

how to find table list with last when it was used by someone and in last 5-6 months how many times that table has been used in queries in redshift

Here's my answer to the Cassandra question (Q4). In other solutions I've seen it seems they don't actually account for taking Cassandra out of the count of friends of friends. That said, this query is super long and ugly so I'm sure there is a more succinct way to write it, curious to see if anyone has feedback/ what other people have come up with!

SELECT ff + f
FROM (
	-- find the number of friends of friends of Cassandra
	SELECT COUNT(*) ff
	FROM Friend 
	WHERE ID1 IN (
		SELECT ID2
		FROM Friend
		WHERE ID1 = (
			SELECT ID
			FROM Highschooler
			WHERE name = 'Cassandra'
		)
	) 
	-- exclude counting Cassandra as a friend of a friend
	AND ID2 <> (
			SELECT ID
			FROM Highschooler
			WHERE name = 'Cassandra'
	)
)
JOIN (
	-- count friends of Cassandra
	SELECT COUNT(ID2) f
	FROM Friend
	WHERE ID1 = (
		SELECT ID
		FROM Highschooler
		WHERE name = 'Cassandra')
);

(edited to fix formatting)

Answer to Q4 (Cassandra) is wrong.
As said in the introduction to the questions:

'even if your solution is marked as correct, it is possible that your query does not correctly reflect the problem at hand' .

Just submitting this query, we can see why is wrong.
Removing COUNT we can see that the relation (table) it produces has 7 tuples (rows), which (again) by chance just happens to be the response the system expects:

SELECT *
FROM Friend
WHERE ID1 IN (
  SELECT ID2
  FROM Friend
  WHERE ID1 IN (
    SELECT ID
    FROM Highschooler
    WHERE name = 'Cassandra'
  )
)

Two of the Cassandra's indirect friends has 1709 as ID, which is Cassandra's ID.
So that query is not even considering the constraints posed in the statement of the exercise.
It is pure chance that this answer is the same as the one that the evaluation system considers as valid.
Even this query...

SELECT count(distinct f.id2) + count(distinct f2.id2)
FROM highschooler hs
JOIN friend f ON hs.id = f.id1 
JOIN friend f2 ON f.id2 = f2.id1
WHERE hs.name = 'Cassandra' AND hs.id != f2.id2
;

...is evaluated as 'correct', because it returns '7', which is the 'right' answer.
But it's not entirely correct because it doesn't rule out the possibility that one of the level 1 friends has one of the other level 1 friends as a level 2 friend (which doesn't happen in this case, but could happen, which would generate duplicates and alter the result).

The next query also considers that corner case.
May be it could be written in a cleaner, shorter way, but at least I think this query is correct:

SELECT count(distinct f.id2) + count(distinct f2.id2)
FROM highschooler hs
JOIN friend f ON hs.id = f.id1 
JOIN friend f2 ON f.id2 = f2.id1
WHERE hs.name = 'Cassandra'
AND hs.id != f2.id2
AND f2.id2 NOT IN (SELECT distinct(f.id2)
                   FROM highschooler hs
                   JOIN friend f ON hs.id = f.id1 
                   WHERE hs.name = 'Cassandra')

-- (why does indentation not work in comments???)

It does.
"To format code or text into its own distinct block, use triple backticks."
"You can add an optional language identifier to enable syntax highlighting in your fenced code block.
Syntax highlighting changes the color and style of source code to make it easier to read.
For example, to syntax highlight SQL code:

```sql 
(here your code)
```

The next query also considers that corner case. May be it could be written in a cleaner, shorter way, but at least I think this query is correct:

This is the best solution I've seen so far! Looks great. And thanks for the help with the formatting

Can anyone please help me explain the approach for question extra 2, especially this part:
WHERE grade NOT IN (
SELECT H2.grade
FROM Friend, Highschooler H2
WHERE H1.ID = Friend.ID1 AND H2.ID = Friend.ID2

How does it know to compare ID1's grade to only ID1's friend's grades?
Would this:

SELECT H2.grade
FROM Friend, Highschooler H2
WHERE H1.ID = Friend.ID1 AND H2.ID = Friend.ID2

returns a column of all ID2's grades?