Key Questions

Problem 1

INSERT INTO followers (follower_id, followee_id)
VALUES (
    (SELECT id FROM users WHERE username = "max"),
    (SELECT id FROM users WHERE username = "ileana")
);

Problem 2

SELECT username FROM users
WHERE id IN (SELECT follower_id FROM followers WHERE followee_id = (SELECT id FROM users WHERE username = "reese"))
AND id IN (SELECT followee_id FROM followers WHERE follower_id = (SELECT id FROM users WHERE username = "reese"));

Problem 3

SELECT username FROM users
WHERE id IN
    (SELECT followee_id FROM followers
     WHERE follower_id IN
        (SELECT followee_id FROM followers
         WHERE follower_id =
            (SELECT id FROM users WHERE username = "reese")
        )
    );

Problem 4

CREATE TABLE friendships (
    requester_id INTEGER,
    requestee_id INTEGER,
    accepted BOOLEAN,
    FOREIGN KEY (requester_id) REFERENCES users(id),
    FOREIGN KEY (requestee_id) REFERENCES users(id)
);

Problem 5

INSERT INTO friendships (requester_id, requestee_id, accepted) VALUES (
    (SELECT id FROM users WHERE username = "max"),
    (SELECT id FROM users WHERE username = "ileana"),
    false
);

Problem 6

UPDATE friendships
SET accepted = true
WHERE requester_id = (SELECT id FROM users WHERE username = "max")
AND requestee_id = (SELECT id FROM users WHERE username = "ileana");

Problem 7

DELETE FROM friendships WHERE requester_id = (SELECT id FROM users WHERE username = "reese");
DELETE FROM friendships WHERE requestee_id = (SELECT id FROM users WHERE username = "reese");
DELETE FROM followers WHERE follower_id = (SELECT id FROM users WHERE username = "reese");
DELETE FROM followers WHERE followee_id = (SELECT id FROM users WHERE username = "reese");
DELETE FROM users WHERE username = "reese";