SQL social network practice by Han Wang 20200720 - 2, PostgreSQL

SQL social network practice by Han Wang 20200720 - 2

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 as likes, a.grade, c.name as liked, 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 HanW_likes a 
WHERE (id1, id2) in
(
    SELECT id2 as id1, id1 as id2
    FROM HanW_likes
) and id1 < id2
;
-- 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
SELECT *
FROM HanW_highschooler
WHERE id NOT IN 
(
    SELECT id1 as id
    FROM HanW_likes
    UNION DISTINCT
    SELECT id2 as id
    FROM HanW_likes
    )
;
-- 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.
SELECT id1, id2
FROM HanW_likes
WHERE id2 not in 
(
    SELECT DISTINCT id1
    FROM HanW_likes
    )
;
-- Q6 Find names and grades of students who only have friends in the same grade. 
-- Return the result sorted by grade.
WITH samegrade as (
    SELECT f.id1, f.id2
    FROM HanW_highschooler a JOIN HanW_friend f on f.id1 = a.id JOIN HanW_highschooler b on b.id = f.id2
    WHERE a.grade = b.grade
    ),
diffgrade as (
    SELECT f.id1 as id
    FROM HanW_highschooler a JOIN HanW_friend f on f.id1 = a.id JOIN HanW_highschooler b on b.id = f.id2
    WHERE a.grade != b.grade
    UNION DISTINCT
    SELECT f.id2 as id
    FROM HanW_highschooler a JOIN HanW_friend f on f.id1 = a.id JOIN HanW_highschooler b on b.id = f.id2
    WHERE a.grade != b.grade
    )
    SELECT b.*
    FROM
    (
        SELECT id1 as id 
        FROM samegrade
        UNION DISTINCT
        SELECT id2 as id
        FROM samegrade
        ) a JOIN HanW_highschooler b on a.id = b.id
        WHERE b.id NOT IN (SELECT id 
                           FROM diffgrade
                          )
;
-- Q7 Find the name and grade of all students who are liked by more than one other student. 
SELECT name, grade
FROM
(
    SELECT id2, count(id2) as num_liked
    FROM HanW_likes
    GROUP BY id2
    ) a join HanW_highschooler b on a.id2 = b.id
WHERE num_liked > 1
;


run \| edit \| history \| help	0