/* 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);
select *
from Likes L
where L.ID2 not in (select ID1 from Likes);
select '--';
select *
from ( Highschooler H1 join Likes L1
on H1.ID = L1.ID1 ) as HL1
left join
( Highschooler H2 join Likes L2
on H2.ID = L2.ID1 ) as HL2
on HL1.ID2 = HL2.ID1
where HL2.ID is null;
select '--';
select *
from ( Highschooler H1
join
(Likes L1 left join Likes L2 on L1.ID2 = L2.ID1) as LL
on H1.ID = LL.ID1 ) as H1LL
left join
Highschooler H2
on H1LL.ID2 = H2.ID;
select '--';
select *
from ( Highschooler H1
join
( select L1.ID1 as L1ID1, L1.ID2 as L1ID2,
L2.ID1 as L2ID1, L2.ID1 as L2ID2
from Likes L1
left join
Likes L2
on L1.ID2 = L2.ID1
where L2.ID1 is null )
LL
on H1.ID = LL.ID1 );
LyogQ3JlYXRlIHRoZSBzY2hlbWEgZm9yIG91ciB0YWJsZXMgKi8KY3JlYXRlIHRhYmxlIEhpZ2hzY2hvb2xlcihJRCBpbnQsIG5hbWUgdGV4dCwgZ3JhZGUgaW50KTsKY3JlYXRlIHRhYmxlIEZyaWVuZChJRDEgaW50LCBJRDIgaW50KTsKY3JlYXRlIHRhYmxlIExpa2VzKElEMSBpbnQsIElEMiBpbnQpOwoKLyogUG9wdWxhdGUgdGhlIHRhYmxlcyB3aXRoIG91ciBkYXRhICovCmluc2VydCBpbnRvIEhpZ2hzY2hvb2xlciB2YWx1ZXMgKDE1MTAsICdKb3JkYW4nLCA5KTsKaW5zZXJ0IGludG8gSGlnaHNjaG9vbGVyIHZhbHVlcyAoMTY4OSwgJ0dhYnJpZWwnLCA5KTsKaW5zZXJ0IGludG8gSGlnaHNjaG9vbGVyIHZhbHVlcyAoMTM4MSwgJ1RpZmZhbnknLCA5KTsKaW5zZXJ0IGludG8gSGlnaHNjaG9vbGVyIHZhbHVlcyAoMTcwOSwgJ0Nhc3NhbmRyYScsIDkpOwppbnNlcnQgaW50byBIaWdoc2Nob29sZXIgdmFsdWVzICgxMTAxLCAnSGFsZXknLCAxMCk7Cmluc2VydCBpbnRvIEhpZ2hzY2hvb2xlciB2YWx1ZXMgKDE3ODIsICdBbmRyZXcnLCAxMCk7Cmluc2VydCBpbnRvIEhpZ2hzY2hvb2xlciB2YWx1ZXMgKDE0NjgsICdLcmlzJywgMTApOwppbnNlcnQgaW50byBIaWdoc2Nob29sZXIgdmFsdWVzICgxNjQxLCAnQnJpdHRhbnknLCAxMCk7Cmluc2VydCBpbnRvIEhpZ2hzY2hvb2xlciB2YWx1ZXMgKDEyNDcsICdBbGV4aXMnLCAxMSk7Cmluc2VydCBpbnRvIEhpZ2hzY2hvb2xlciB2YWx1ZXMgKDEzMTYsICdBdXN0aW4nLCAxMSk7Cmluc2VydCBpbnRvIEhpZ2hzY2hvb2xlciB2YWx1ZXMgKDE5MTEsICdHYWJyaWVsJywgMTEpOwppbnNlcnQgaW50byBIaWdoc2Nob29sZXIgdmFsdWVzICgxNTAxLCAnSmVzc2ljYScsIDExKTsKaW5zZXJ0IGludG8gSGlnaHNjaG9vbGVyIHZhbHVlcyAoMTMwNCwgJ0pvcmRhbicsIDEyKTsKaW5zZXJ0IGludG8gSGlnaHNjaG9vbGVyIHZhbHVlcyAoMTAyNSwgJ0pvaG4nLCAxMik7Cmluc2VydCBpbnRvIEhpZ2hzY2hvb2xlciB2YWx1ZXMgKDE5MzQsICdLeWxlJywgMTIpOwppbnNlcnQgaW50byBIaWdoc2Nob29sZXIgdmFsdWVzICgxNjYxLCAnTG9nYW4nLCAxMik7CgppbnNlcnQgaW50byBGcmllbmQgdmFsdWVzICgxNTEwLCAxMzgxKTsKaW5zZXJ0IGludG8gRnJpZW5kIHZhbHVlcyAoMTUxMCwgMTY4OSk7Cmluc2VydCBpbnRvIEZyaWVuZCB2YWx1ZXMgKDE2ODksIDE3MDkpOwppbnNlcnQgaW50byBGcmllbmQgdmFsdWVzICgxMzgxLCAxMjQ3KTsKaW5zZXJ0IGludG8gRnJpZW5kIHZhbHVlcyAoMTcwOSwgMTI0Nyk7Cmluc2VydCBpbnRvIEZyaWVuZCB2YWx1ZXMgKDE2ODksIDE3ODIpOwppbnNlcnQgaW50byBGcmllbmQgdmFsdWVzICgxNzgyLCAxNDY4KTsKaW5zZXJ0IGludG8gRnJpZW5kIHZhbHVlcyAoMTc4MiwgMTMxNik7Cmluc2VydCBpbnRvIEZyaWVuZCB2YWx1ZXMgKDE3ODIsIDEzMDQpOwppbnNlcnQgaW50byBGcmllbmQgdmFsdWVzICgxNDY4LCAxMTAxKTsKaW5zZXJ0IGludG8gRnJpZW5kIHZhbHVlcyAoMTQ2OCwgMTY0MSk7Cmluc2VydCBpbnRvIEZyaWVuZCB2YWx1ZXMgKDExMDEsIDE2NDEpOwppbnNlcnQgaW50byBGcmllbmQgdmFsdWVzICgxMjQ3LCAxOTExKTsKaW5zZXJ0IGludG8gRnJpZW5kIHZhbHVlcyAoMTI0NywgMTUwMSk7Cmluc2VydCBpbnRvIEZyaWVuZCB2YWx1ZXMgKDE5MTEsIDE1MDEpOwppbnNlcnQgaW50byBGcmllbmQgdmFsdWVzICgxNTAxLCAxOTM0KTsKaW5zZXJ0IGludG8gRnJpZW5kIHZhbHVlcyAoMTMxNiwgMTkzNCk7Cmluc2VydCBpbnRvIEZyaWVuZCB2YWx1ZXMgKDE5MzQsIDEzMDQpOwppbnNlcnQgaW50byBGcmllbmQgdmFsdWVzICgxMzA0LCAxNjYxKTsKaW5zZXJ0IGludG8gRnJpZW5kIHZhbHVlcyAoMTY2MSwgMTAyNSk7Cmluc2VydCBpbnRvIEZyaWVuZCBzZWxlY3QgSUQyLCBJRDEgZnJvbSBGcmllbmQ7CgppbnNlcnQgaW50byBMaWtlcyB2YWx1ZXMoMTY4OSwgMTcwOSk7Cmluc2VydCBpbnRvIExpa2VzIHZhbHVlcygxNzA5LCAxNjg5KTsKaW5zZXJ0IGludG8gTGlrZXMgdmFsdWVzKDE3ODIsIDE3MDkpOwppbnNlcnQgaW50byBMaWtlcyB2YWx1ZXMoMTkxMSwgMTI0Nyk7Cmluc2VydCBpbnRvIExpa2VzIHZhbHVlcygxMjQ3LCAxNDY4KTsKaW5zZXJ0IGludG8gTGlrZXMgdmFsdWVzKDE2NDEsIDE0NjgpOwppbnNlcnQgaW50byBMaWtlcyB2YWx1ZXMoMTMxNiwgMTMwNCk7Cmluc2VydCBpbnRvIExpa2VzIHZhbHVlcygxNTAxLCAxOTM0KTsKaW5zZXJ0IGludG8gTGlrZXMgdmFsdWVzKDE5MzQsIDE1MDEpOwppbnNlcnQgaW50byBMaWtlcyB2YWx1ZXMoMTAyNSwgMTEwMSk7CgoKc2VsZWN0ICoKZnJvbSAgIExpa2VzIEwKd2hlcmUgIEwuSUQyIG5vdCBpbiAoc2VsZWN0IElEMSBmcm9tIExpa2VzKTsKCnNlbGVjdCAnLS0nOwoKc2VsZWN0ICoKZnJvbSAgICggSGlnaHNjaG9vbGVyIEgxIGpvaW4gTGlrZXMgTDEKICAgICAgICAgb24gSDEuSUQgPSBMMS5JRDEgKSBhcyBITDEKICAgICAgIGxlZnQgam9pbgogICAgICAgKCBIaWdoc2Nob29sZXIgSDIgam9pbiBMaWtlcyBMMgogICAgICAgICBvbiBIMi5JRCA9IEwyLklEMSApIGFzIEhMMgogICAgICAgb24gSEwxLklEMiA9IEhMMi5JRDEKd2hlcmUgIEhMMi5JRCBpcyBudWxsOwoKc2VsZWN0ICctLSc7CgpzZWxlY3QgKgpmcm9tICAgKCBIaWdoc2Nob29sZXIgSDEKICAgICAgICAgam9pbiAKICAgICAgICAgKExpa2VzIEwxIGxlZnQgam9pbiBMaWtlcyBMMiBvbiBMMS5JRDIgPSBMMi5JRDEpIGFzIExMCiAgICAgICAgIG9uIEgxLklEID0gTEwuSUQxICkgYXMgSDFMTAogICAgICAgbGVmdCBqb2luIAogICAgICAgICBIaWdoc2Nob29sZXIgSDIKICAgICAgIG9uIEgxTEwuSUQyID0gSDIuSUQ7CgpzZWxlY3QgJy0tJzsKCnNlbGVjdCAqCmZyb20gICAoIEhpZ2hzY2hvb2xlciBIMQogICAgICAgICBqb2luIAogICAgICAgICAoIHNlbGVjdCBMMS5JRDEgYXMgTDFJRDEsIEwxLklEMiBhcyBMMUlEMiwKICAgICAgICAgICAgICAgICAgTDIuSUQxIGFzIEwySUQxLCBMMi5JRDEgYXMgTDJJRDIKICAgICAgICAgICBmcm9tICAgTGlrZXMgTDEKICAgICAgICAgICAgICAgICAgbGVmdCBqb2luCiAgICAgICAgICAgICAgICAgIExpa2VzIEwyCiAgICAgICAgICAgICAgICAgIG9uIEwxLklEMiA9IEwyLklEMQogICAgICAgICAgIHdoZXJlICBMMi5JRDEgaXMgbnVsbCApCiAgICAgICAgIExMCiAgICAgICAgIG9uIEgxLklEID0gTEwuSUQxICk7