Students at your hometown high school have decided to organize their social network using databases. So far, they have collected information about sixteen students in four grades, 9-12.
-- Database of social networking database
/* Delete the tables if they already exist */
drop table if exists Highschooler;
drop table if exists Friend;
drop table if exists Likes;
/* 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);Problem: Find the names of all students who are friends with someone named Gabriel.
-- Find the names of all students who are friends with someone named Gabriel.
SELECT name
FROM Highschooler
INNER JOIN FRIEND ON Highschooler.ID = Friend.ID1
WHERE ID2 IN (SELECT ID FROM HIGHSCHOOLER WHERE name = 'Gabriel')## name
## 1 Jordan
## 2 Tiffany
## 3 Tiffany
## 4 Cassandra
## 5 Cassandra
## 6 Andrew
## 7 Kris
## 8 Alexis
## 9 Alexis
## 10 Austin
## 11 Jessica
## 12 Jordan
## 13 KyleProblem: 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.
-- 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 t1.name, t1.grade, t2.name, t2.grade
FROM (
(SELECT * FROM highschooler h, likes l WHERE h.ID = l.ID1) t1
JOIN
(SELECT * FROM highschooler h, likes l WHERE h.ID = l.ID2) t2
ON t1.ID2 = t2.ID2
)
WHERE t1.grade-t2.grade >= 2;## name grade name grade
## 1 John 12 Haley 10Problem: 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.
-- 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 t1.name, t1.grade, t2.name, t2.grade FROM (
(SELECT id1, name, grade, id2 FROM highschooler, likes WHERE ID = ID1) t1
JOIN
(SELECT id1, name, grade, id2 FROM highschooler, likes WHERE ID = ID1) t2
ON t1.id1 = t2.id2
) group by t1.name, t2.name, t1.grade, t2.grade, t1.id1, t1.id2, t2.id1 having t1.id2 = t2.id1 AND t1.name < t2.name;## name grade name grade
## 1 Cassandra 9 Gabriel 9
## 2 Jessica 11 Kyle 12Problem: 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.
-- 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 DISTINCT name, grade FROM highschooler, likes
WHERE id NOT IN (SELECT id1 FROM likes) AND id NOT IN (SELECT id2 FROM likes)
ORDER BY grade, name;## name grade
## 1 Jordan 9
## 2 Tiffany 9
## 3 Logan 12Problem: 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.
-- 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 t1.name, t1.grade, t2.name, t2.grade
FROM (SELECT * FROM highschooler, likes WHERE id = id1 AND id2 NOT IN (SELECT id1 FROM likes)) t1
JOIN highschooler t2 ON t2.id = t1.id2;## name grade name grade
## 1 John 12 Haley 10
## 2 Austin 11 Jordan 12
## 3 Alexis 11 Kris 10
## 4 Brittany 10 Kris 10Problem: 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.
-- 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
WHERE id NOT IN
(SELECT t1.id FROM (SELECT * FROM highschooler, friend WHERE id = id1) t1
JOIN
highschooler t2 ON t1.id2 = t2.id WHERE t1.grade != t2.grade)
ORDER BY grade, name;## name grade
## 1 Jordan 9
## 2 Brittany 10
## 3 Haley 10
## 4 Kris 10
## 5 Gabriel 11
## 6 John 12
## 7 Logan 12Problem: 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.
-- 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 h1.name, h1.grade, h2.name, h2.grade, h3.name, h3.grade
FROM highschooler h1, highschooler h2, highschooler h3,
(SELECT l.id1 AS lid1, l.id2 AS lid2, f2.id1 AS f2id1 FROM likes l, friend f2, friend f3 WHERE
not exists (SELECT f.id1, f.id2 FROM friend f WHERE f.id1 = l.id1 AND f.id2 = l.id2)
AND f2.id2 = l.id1 AND f3.id2 = l.id2 AND f2.id1 = f3.id1) AS t
WHERE h1.id = t.lid1 AND h2.id = t.lid2 AND h3.id = f2id1;## name grade name grade name grade
## 1 Andrew 10 Cassandra 9 Gabriel 9
## 2 Austin 11 Jordan 12 Andrew 10
## 3 Austin 11 Jordan 12 Kyle 12Problem: Find the difference between the number of students in the school and the number of different first names.
-- Find the difference between the number of students in the school and the number of different first names.
SELECT (SELECT count(id)
FROM Highschooler)-(SELECT count(DISTINCT name) FROM Highschooler);## ?column?
## 1 2Problem: Find the name and grade of all students who are liked by more than one other student.
-- Find the name and grade of all students who are liked by more than one other student.
SELECT name, grade
FROM
(SELECT id2 FROM highschooler, likes WHERE id = id1 GROUP BY id2 HAVING count(id2) > 1) t1, highschooler t2
WHERE t1.id2 = t2.id;## name grade
## 1 Cassandra 9
## 2 Kris 10