SQL social network practice by Han Wang 20200720, PostgreSQL

Language: Layout:

drop table if exists HanW_highschooler;
drop table if exists HanW_friend;
drop table if exists HanW_likes;

create table HanW_highschooler("id" int, "name" varchar(30), "grade" int);
create table HanW_friend("id1" int, "id2" int);
create table HanW_likes("id1" int, "id2" int);

insert into HanW_highschooler("id","name","grade") 
values 
(1510, 'Jordan', 9),
(1689, 'Gabriel', 9),
(1381, 'Tiffany', 9),
(1709, 'Cassandra', 9),
(1101, 'Haley', 10),
(1782, 'Andrew', 10),
(1468, 'Kris', 10),
(1641, 'Brittany', 10),
(1247, 'Alexis', 11),
(1316, 'Austin', 11),
(1911, 'Gabriel', 11),
(1501, 'Jessica', 11),
(1304, 'Jordan', 12),
(1025, 'John', 12),
(1934, 'Kyle', 12),
(1661, 'Logan', 12);

insert into HanW_friend("id1","id2") 
values 
(1510, 1381),
(1101, 1501),
(1510, 1689),
(1689, 1709),
(1381, 1247),
(1709, 1247),
(1689, 1782),
(1782, 1468),
(1782, 1316),
(1782, 1304),
(1468, 1101),
(1468, 1641),
(1101, 1641),
(1247, 1911),
(1247, 1501),
(1911, 1501),
(1501, 1934),
(1316, 1934),
(1934, 1304),
(1304, 1661),
(1661, 1025),
(1689, 1709),
(1709, 1689),
(1782, 1709),
(1911, 1247),
(1247, 1468),
(1641, 1468),
(1316, 1304),
(1501, 1934),
(1934, 1501),
(1025, 1101);

insert into HanW_likes("id1","id2")
values 
(1689, 1709),
(1709, 1689),
(1782, 1709),
(1911, 1247),
(1247, 1468),
(1641, 1468),
(1316, 1304),
(1501, 1934),
(1934, 1501),
(1025, 1101),
(1101, 1381),
(1025, 1641);
-- Schema HanW_highschooler("id" "name" "grade"); HanW_friend("id1" "id2"); HanW_likes("id1" "id2");
-- Q1 Find the names of all students who are friends with someone named Gabriel.
with cte as 
(SELECT id1 id
FROM HanW_friend
WHERE id2 in
    (SELECT id
     FROM HanW_highschooler
     WHERE name = 'Gabriel')
UNION DISTINCT
SELECT id2 id
FROM HanW_friend
WHERE id1 in
    (SELECT id
     FROM HanW_highschooler
     WHERE name = 'Gabriel'))
SELECT a.*
FROM HanW_highschooler a join cte on a.id = cte.id
;
-- Q2 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 a.name, a.grade, c.name, c.grade
FROM HanW_highschooler a join HanW_likes b on a.id = b.id1 join HanW_highschooler c on b.id2 = c.id
WHERE (a.grade - c.grade) >= 2
;
-- Q3 For every pair of students who both like each other, return the name and grade of both students.
--    Including each pair only once, with the two names in alphabetical order.
SELECT
FROM
;
-- Q4 Find all students who do not appear in the likes table(as a student who likes or is liked) and return their name and grades
-- Q5 For every situation where student A likes student B, but we have no information about whom B likes, return A and B's name and grades.
-- Q6 Find names and grades of students who only have friends in the same grade. Return the result sorted by grade.
-- Q7 Find the name and grade of all students who are liked by more than one other student.


absolute service time: 0,45 sec