Key Questions

Some social networks, including Twitter and Instagram, allow one user (a “follower”) to follow another user (a “followee”). Let’s consider how such networks might represent data about their users. A social network might, for instance, have a SQL database with tables like the below.

CREATE TABLE users (
    id INTEGER,
    username TEXT UNIQUE,
    name TEXT,
    PRIMARY KEY(id)
);
CREATE TABLE followers (
    follower_id INTEGER,
    followee_id INTEGER,
    FOREIGN KEY (follower_id) REFERENCES users(id),
    FOREIGN KEY (followee_id) REFERENCES users(id)
);

Assume that Ileana is registered for this social network with a username of ileana, that Reese is registered with a username of reese, and that Max is registered with a username of max.

  1. (2 points.) Suppose that Max starts following Ileana. What (single) SQL statement should be executed?

  2. (2 points.) With what (single) SQL query could you select the usernames of every user that follows Reese and whom Reese also follows back?

  3. (2 points.) Suppose that Reese is looking for additional users to follow. One suggestion that a social network might provide is to suggest users who are “two degrees of separation” away: users who are followed by users whom Reese already follows. With what (single) SQL query could you select the usernames of users who are followed by users whom Reese follows?

Social networks like Twitter and Instagram support asymmetric relationships: Alice might follow Bob, but that does not mean that Bob also follows Alice. Other social networks, like Facebook, support symmetric relationships as well: in order for Alice and Bob to be “friends,” one of them must send a “friend request” to the other, which the other must then accept.

  1. (2 points.) With what SQL statement could you create a friendships table that allows for the representation of friendships and friend requests? Assume that users exists as above.

  2. (2 points.) Suppose that Max sends a friend request to Ileana. What (single) SQL statement should be executed?

  3. (2 points.) Suppose that Ileana accepts Max’s friend request. What (single) SQL query should be executed?

  4. (2 points.) Suppose that Reese deletes his account on this social network. What SQL statements should be executed in order to remove all traces of Reese, his friendships, and his followees?