Homework answers / question archive / Hello Tutors, I would require help in a SQL queries and statements question

Hello Tutors, I would require help in a SQL queries and statements question

Computer Science

Share With

---

Hello Tutors, I would require help in a SQL queries and statements question. Here is the SQL database provided:

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)
);

 

The following questions require to write SQL queries and statements.

 

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

 

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

 

1. 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)With what SQL statement could you make a friendships table that allows for the representation of friendships and friend requests? Assume that users exists as above.

 

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

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

4)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?