<div class="notebook">
<div class="nb-cell markdown" name="md1">
# An introduction to Prolog for SQL programers Part 2
*By Robert Laing*
Following from [Part 1](https://swish.swi-prolog.org/p/sql2prolog.swinb), I've translated the Social-Network quiz questions from [Databases: Relational Databases and SQL](https://www.edx.org/course/databases-5-sql?utm_source=sailthru&utm_medium=email&utm_campaign=triggered_shareit) into Prolog.
An image of the graph is at https://courses.edx.org/assets/courseware/v1/db2bd56806a9ee925f626a01600a97f9/asset-v1:StanfordOnline+SOE.YDB-SQL0001+2T2020+type@asset+block/social.png
My SQL solutions for some of these problems devolved into subquery hell, so Prolog does this much better.
One little trap is the friend relationship is reciprocal as in `friend(A, B) :- friend(B, A).` meaning cycles. To avoid friend queries hanging the computer, I needed `:- table friend/2.` as explained in [Tabled execution](https://www.swi-prolog.org/pldoc/man?section=tabling).
</div>
<div class="nb-cell program" data-background="true" name="p1">
:- table friend/2.
:- dynamic highschooler/3, likes/2.
%! highschooler(?ID:int, ?Name:text, ?Grade:int) is nondet
highschooler(1510, 'Jordan', 9).
highschooler(1689, 'Gabriel', 9).
highschooler(1381, 'Tiffany', 9).
highschooler(1709, 'Cassandra', 9).
highschooler(1101, 'Haley', 10).
highschooler(1782, 'Andrew', 10).
highschooler(1468, 'Kris', 10).
highschooler(1641, 'Brittany', 10).
highschooler(1247, 'Alexis', 11).
highschooler(1316, 'Austin', 11).
highschooler(1911, 'Gabriel', 11).
highschooler(1501, 'Jessica', 11).
highschooler(1304, 'Jordan', 12).
highschooler(1025, 'John', 12).
highschooler(1934, 'Kyle', 12).
highschooler(1661, 'Logan', 12).
%! friend(?ID1:int, ?ID2:int) is nondet
friend(1510, 1381).
friend(1510, 1689).
friend(1689, 1709).
friend(1381, 1247).
friend(1709, 1247).
friend(1689, 1782).
friend(1782, 1468).
friend(1782, 1316).
friend(1782, 1304).
friend(1468, 1101).
friend(1468, 1641).
friend(1101, 1641).
friend(1247, 1911).
friend(1247, 1501).
friend(1911, 1501).
friend(1501, 1934).
friend(1316, 1934).
friend(1934, 1304).
friend(1304, 1661).
friend(1661, 1025).
friend(A, B) :- friend(B, A).
%! likes(?ID1:int, ?ID2:int) is nondet
likes(1689, 1709).
likes(1709, 1689).
likes(1782, 1709).
likes(1911, 1247).
likes(1247, 1468).
likes(1641, 1468).
likes(1316, 1304).
likes(1501, 1934).
likes(1934, 1501).
likes(1025, 1101).
</div>
<div class="nb-cell markdown" name="md2">
> Find the names of all students who are friends with someone named Gabriel.
</div>
<div class="nb-cell query" data-chunk="20" data-tabled="true" name="q1">
distinct(Name,
( highschooler(_ID1, 'Gabriel', _),
friend(_ID1, _ID2),
highschooler(_ID2, Name, _) )).
</div>
<div class="nb-cell markdown" name="md3">
> 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.
</div>
<div class="nb-cell query" data-chunk="10" data-tabled="true" name="q2">
highschooler(_ID1, Name1, Grade1),
highschooler(_ID2, Name2, Grade2),
likes(_ID1, _ID2),
_X is Grade1 - Grade2,
_X > 1.
</div>
<div class="nb-cell markdown" name="md4">
> 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.
</div>
<div class="nb-cell query" data-chunk="7" data-tabled="true" name="q3">
highschooler(_ID1, Name1, Grade1),
highschooler(_ID2, Name2, Grade2),
likes(_ID1, _ID2),
likes(_ID2, _ID1),
Name1 @< Name2.
</div>
<div class="nb-cell markdown" name="md5">
> 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.
</div>
<div class="nb-cell query" data-chunk="6" data-tabled="true" name="q4">
order_by([asc(Grade), asc(Name)],
( highschooler(_ID, Name, Grade),
\+( likes(_ID, _) ; likes(_, _ID) ) )).
</div>
<div class="nb-cell markdown" name="md6">
> 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.
</div>
<div class="nb-cell query" data-chunk="8" data-tabled="true" name="q5">
highschooler(_ID1, Name1, Grade1),
highschooler(_ID2, Name2, Grade2),
likes(_ID1, _ID2),
\+likes(_ID2, _).
</div>
<div class="nb-cell markdown" name="md7">
> 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.
</div>
<div class="nb-cell query" data-chunk="20" data-tabled="true" name="q6">
distinct(Name, order_by([asc(Grade), asc(Name)],
( highschooler(_ID1, Name, Grade),
highschooler(_ID2, _, Grade),
friend(_ID1, _ID2),
\+ ( highschooler(_ID3, _, _Grade3),
friend(_ID1, _ID3),
Grade \== _Grade3
)
))).
</div>
<div class="nb-cell markdown" name="md8">
> 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.
</div>
<div class="nb-cell query" data-chunk="11" data-tabled="true" name="q7">
highschooler(_IDA, NameA, GradeA),
highschooler(_IDB, NameB, GradeB),
highschooler(_IDC, NameC, GradeC),
likes(_IDA, _IDB),
\+friend(_IDA, _IDB),
friend(_IDA, _IDC),
friend(_IDB, _IDC).
</div>
<div class="nb-cell markdown" name="md9">
> Find the difference between the number of students in the school and the number of different first names.
</div>
<div class="nb-cell query" name="q8">
aggregate_all(count, ID, highschooler(ID, _, _), _Students),
aggregate_all(count, Name, highschooler(_, Name, _), _Names),
Ans is _Students - _Names.
</div>
<div class="nb-cell markdown" name="md10">
> Find the name and grade of all students who are liked by more than one other student.
</div>
<div class="nb-cell query" data-chunk="10" data-tabled="true" name="q9">
highschooler(_ID2, Name, Grade),
aggregate_all(count, likes(_, _ID2), _Likes), _Likes > 1.
</div>
<div class="nb-cell markdown" name="md11">
> For every situation where student A likes student B, but student B likes a different student C, return the names and grades of A, B, and C.
</div>
<div class="nb-cell query" data-chunk="12" data-tabled="true" name="q10">
highschooler(_IDA, NameA, GradeA),
highschooler(_IDB, NameB, GradeB),
highschooler(_IDC, NameC, GradeC),
likes(_IDA, _IDB),
likes(_IDB, _IDC),
_IDC \== _IDA.
</div>
<div class="nb-cell markdown" name="md12">
> Find those students for whom all of their friends are in different grades from themselves. Return the students' names and grades.
</div>
<div class="nb-cell query" data-chunk="12" data-tabled="true" name="q11">
distinct(Name,
( highschooler(_ID1, Name, Grade),
highschooler(_ID2, _, _Grade2),
friend(_ID1, _ID2),
Grade \== _Grade2,
\+ ( highschooler(_ID3, _, Grade),
friend(_ID1, _ID3)
)
)).
</div>
<div class="nb-cell markdown" name="md13">
> What is the average number of friends per student? (Your result should be just one number.)
</div>
<div class="nb-cell query" name="q12">
aggregate_all((sum(Friends), count),
( highschooler(ID, _, _),
aggregate_all(count, friend(ID, _), Friends)
), (_T, _L)),
Averge is _T/_L.
</div>
<div class="nb-cell markdown" name="md14">
> Find the number of students who are either friends with Cassandra or are friends of friends of Cassandra. Do not count Cassandra, even though technically she is a friend of a friend.
</div>
<div class="nb-cell query" name="q13">
highschooler(_ID1, 'Cassandra', _),
aggregate_all(count, friend(_ID1, _ID2), _T1),
aggregate_all(count, _ID3,
( friend(_ID1, _ID2),
friend(_ID2, _ID3),
_ID3 \== _ID1
), _T2),
Ans is _T1 + _T2.
</div>
<div class="nb-cell markdown" name="md15">
> Find the name and grade of the student(s) with the greatest number of friends.
</div>
<div class="nb-cell query" data-chunk="20" data-tabled="true" name="q14">
aggregate_all(max(_Friends),
( highschooler(_ID1, _, _),
aggregate_all(count, friend(_ID1, _), _Friends)
), _Max),
highschooler(_ID1, Name, Grade),
aggregate_all(count, friend(_ID1, _), _Max).
</div>
<div class="nb-cell markdown" name="md16">
> It's time for the seniors to graduate. Remove all 12th graders from Highschooler.
</div>
<div class="nb-cell query" data-chunk="20" data-tabled="true" name="q15">
forall(highschooler(_SID, Name, 12),
retract(highschooler(_SID, Name, 12))),
highschooler(ID, Name, Grade).
</div>
<div class="nb-cell markdown" name="md18">
> If two students A and B are friends, and A likes B but not vice-versa, remove the Likes tuple.
</div>
<div class="nb-cell query" data-chunk="20" data-tabled="true" name="q16">
forall(( friend(_ID1, _ID2),
likes(_ID1, _ID2),
\+likes(_ID2, _ID1)
), retract(likes(_ID1, _ID2))),
highschooler(_A, Name1, Grade1),
highschooler(_B, Name2, Grade2),
likes(_A, _B).
</div>
<div class="nb-cell markdown" name="md17">
> For all cases where A is friends with B, and B is friends with C, add a new friendship for the pair A and C. Do not add duplicate friendships, friendships that already exist, or friendships with oneself.
Here I hit the snag that new statements can't be added to friend/2 after it has been [tabled](https://www.swi-prolog.org/pldoc/man?section=tabling) (ie the `:- table friend/2.` declaration has been made, required to avoid `Probable infinite recursion (cycle)` errors.
A workaround is to copy friend/2 to an _untabled_ relation, friended/2 in this example, and then inserting the new entries into it.
Many thanks again to SWI Prolog's chief developer Jan Wielemaker for explaining in this [Discourse](https://swi-prolog.discourse.group/t/dynamic-table-woes/3674/2?u=joeblog) posting.
</div>
<div class="nb-cell program" name="p4">
:- dynamic friended/2.
friended(ID1, ID2) :- friend(ID1, ID2).
friended(ID1, ID3) :-
distinct([ID1, ID3], ( friend(ID1, ID2),
friend(ID2, ID3),
\+friend(ID1, ID3),
ID1 \== ID3
)).
</div>
<div class="nb-cell query" data-chunk="20" data-tabled="true" name="q17">
order_by([asc(ID1)], ( highschooler(ID1, Name, Grade),
aggregate_all(count, friended(ID1, _), Friends)
)).
</div>
<div class="nb-cell markdown" name="md19">
I've continued with recursion examples in [part 3](https://swish.swi-prolog.org/p/sql2prolog3.swinb).
</div>
</div>