CSCI 343 SQL examples

Using the social database shown in the this picture and created with this SQL statement.

Finding out what’s in the database

.tables

SELECT * FROM Highschooler ;

SELECT * FROM Likes ;

Testing friendship

Listing pairs of friends

SELECT H1.name, H2.name
FROM Highschooler H1, Highschooler H2, Friend F
WHERE H1.ID = F.ID1 AND H2.ID = F.ID2 AND H1.name < H2.name
ORDER BY H1.name, H2.name ;

Friends of Kris and their grades

SELECT H.name, H.grade
FROM Highschooler H, Highschooler Hkris, Friend F
WHERE Hkris.name = 'Kris' AND Hkris.ID = F.ID1 AND H.ID = F.ID2 ;

Friends whose names start with the letter ‘A’

SELECT H1.name, H2.name
FROM Highschooler H1, Highschooler H2, Friend F
WHERE H1.ID = F.ID1 AND H2.ID = F.ID2 AND H1.name < H2.name
      AND H1.name LIKE 'A%' AND H2.name LIKE 'A%' ;

Highest grade in which each student has a friend

SELECT H.name, MAX(Hfriend.grade)
FROM Highschooler H, Highschooler Hfriend, Friend F
WHERE H.ID = F.ID1 AND Hfriend.ID = F.ID2
GROUP by H.ID
ORDER BY H.name ;

Students with more than two friends

SELECT H.name, COUNT(*)
FROM Highschooler H, Highschooler Hfriend, Friend F
WHERE H.ID = F.ID1 AND Hfriend.ID = F.ID2
GROUP BY H.ID
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC, H.name ;

Finding out who likes whom

Listing Students who like each other

SELECT H1.name, H2.name
FROM Highschooler H1, Highschooler H2, Likes L1, Likes L2
WHERE H1.ID = L1.ID1 AND H2.ID = L1.ID2
      AND H1.ID = L2.ID2 AND H2.ID = L2.ID1 
      AND H1.name < H2.name ;

Students with unrequited likes

SELECT H1.name, H2.name
FROM Highschooler H1, Highschooler H2, Likes L1to2
WHERE H1.ID = L1to2.ID1 AND H2.ID = L1to2.ID2
and not exists(
    SELECT *
    FROM Likes L2to1
    WHERE H2.ID = L2to1.ID1 AND H1.ID = L2to1.ID2) ;

Generating and using ID pairs of mutual likes

SELECT L.ID1, L.ID2
FROM Likes L, Likes Lrev
WHERE L.ID1 = Lrev.ID2 AND L.ID2 = Lrev.ID1 ;

SELECT H1.name, H2.name
FROM Highschooler H1, Highschooler H2,
  (SELECT L.ID1, L.ID2
   FROM Likes L, Likes Lrev
   WHERE L.ID1 = Lrev.ID2 AND L.ID2 = Lrev.ID1) ML
WHERE ML.ID1 = H1.ID AND ML.ID2 = H2.ID AND H1.name < H2.name ;

All friends are in the same grade

SELECT H.name, H.grade
FROM Highschooler H
WHERE
  H.ID NOT IN (
      SELECT H1.ID
      FROM Highschooler H1, Highschooler H2, Friend F
      WHERE H1.ID = F.ID1 AND H2.ID = F.ID2 AND H1.grade <> H2.grade
     )
 AND
  H.ID IN (
      SELECT H1.ID
      FROM Highschooler H1, Highschooler H2, Friend F
      WHERE H1.ID = F.ID1 AND H2.ID = F.ID2 AND H1.grade = H2.grade
     )
ORDER BY H.grade, H.name ;

SELECT DISTINCT H.name, H.grade
FROM Highschooler H, Highschooler Hsame, Friend F
WHERE H.ID = F.ID1 AND Hsame.ID = F.ID2 AND H.ID <> Hsame.ID
      AND H.grade = Hsame.grade
      AND H.ID NOT IN (
          SELECT H1.ID
          FROM Highschooler H1, Highschooler H2, Friend F
          WHERE H1.ID = F.ID1 AND H2.ID = F.ID2 AND H1.grade <> H2.grade
    ) ;