Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Social Network SQL
drop table if exists yuhaoz_highschooler; drop table if exists yuhaoz_friend; drop table if exists yuhaoz_likes; create table yuhaoz_highschooler("id" int, "name" varchar(30), "grade" int); create table yuhaoz_friend("id1" int, "id2" int); create table yuhaoz_likes("id1" int, "id2" int); insert into yuhaoz_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 yuhaoz_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 yuhaoz_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); --Q1 with friend as( select id1 as id from yuhaoz_friend where id2 in (select id from yuhaoz_highschooler where name = 'Gabriel') union distinct select id2 as id from yuhaoz_friend where id1 in (select id from yuhaoz_highschooler where name = 'Gabriel') ) select a.* from yuhaoz_highschooler a join friend f on a.id = f.id; --Q2 --slution1 with table1 as( select a.id1, b.name, b.grade as grade1, a.id2,c.name, c.grade as grade2 from yuhaoz_likes a join yuhaoz_highschooler b on a.id1=b.id join yuhaoz_highschooler c on a.id2=c.id ) select * from table1 where grade2 <= grade1 -2; --solution2 select b.name as like_name, b.grade as like_grade, c.name as liked_name, c.grade as liked_grade from yuhaoz_likes a join yuhaoz_highschooler b on a.id1 = b.id join yuhaoz_highschooler c on a.id2 = c.id where (b.grade - c.grade) >= 2; --Q3 select a.id1 as id1, b.id1 as id2 from yuhaoz_likes a join yuhaoz_likes b on a.id1 = b.id2 and a.id2=b.id1 where a.id1<b.id1; --solution1 select hs1.name as name1, hs2.name as name2 from yuhaoz_highschooler hs1 join(select a.id1 as id1, b.id1 as id2 from yuhaoz_likes a join yuhaoz_likes b on a.id1 = b.id2 and a.id2=b.id1 where a.id1<b.id1) as subq on hs1.id = subq.id1 join yuhaoz_highschooler hs2 on subq.id2=hs2.id order by 1,2; --solution2 select * from yuhaoz_likes where (id1, id2) in (select id2, id1 from yuhaoz_likes) and id1<id2; --Q4 --my solution with students as( select id1 as likes_id from yuhaoz_likes union distinct select id2 from yuhaoz_likes) select a.name, a.grade from yuhaoz_highschooler a left join students s on a.id = s.likes_id where likes_id is null; --solution select * from yuhaoz_highschooler where id not in ( select id1 from yuhaoz_likes union distinct select id2 from yuhaoz_likes); --Q5 select * from yuhaoz_highschooler where id in ( select a.id2 as ids from yuhaoz_likes a where a.id2 not in (select yuhaoz_likes.id1 from yuhaoz_likes) ); --Q6 with same_grade as( select a.id1, b.name as name1, b.grade as grade1, a.id2, c.name as name2, c.grade as grade2 from yuhaoz_friend a join yuhaoz_highschooler b on a.id1=b.id join yuhaoz_highschooler c on a.id2=c.id where b.grade = c.grade ), dif_grade as( select a.id1, b.name as name1, b.grade as grade1, a.id2, c.name as name2, c.grade as grade2 from yuhaoz_friend a join yuhaoz_highschooler b on a.id1=b.id join yuhaoz_highschooler c on a.id2=c.id where b.grade <> c.grade ) select sa.name1, sa.name2, sa.grade2 from same_grade sa where sa.id1 not in ( select dif.id1 from dif_grade dif union distinct select dif.id2 from dif_grade dif); --Q7 select * from yuhaoz_highschooler hs where id in( select id2 from yuhaoz_likes group by id2 having count(id2) >1)
run
|
edit
|
history
|
help
0
filme
Ecommerce Website Analysis by SQL
nested array to table
Food Delivery Website SQL Analysis
mandelbrot set
loggin_trigger-audit
1280. Students and Examinations
code1
test
Krug_test