CREATE TABLE follows (
    f1 NUMBER PRIMARY KEY,
    f2 NUMBER,
    FOREIGN KEY ("f2") REFERENCES user_profiles("uid"),
    FOREIGN KEY ("f1") REFERENCES user_profiles("uid")
);
CREATE TABLE tweets (
    id NUMBER PRIMARY KEY,
    uid NUMBER,
    text TEXT,
    createdate TIME,
    FOREIGN KEY ("uid") REFERENCES user_profiles("uid")
);
CREATE TABLE user_profiles (
    uid NUMBER PRIMARY KEY,
    name TEXT,
    email TEXT,
    partitionid NUMBER,
    followers NUMBER
);

Question: Find the emails of the user named "Mary".
SQL: SELECT email FROM user_profiles WHERE name  =  'Mary'

Question: What is the partition id of the user named "Iron Man".
SQL: SELECT partitionid FROM user_profiles WHERE name  =  'Iron Man'

Question: How many users are there?
SQL: SELECT count(*) FROM user_profiles

Question: How many followers does each user have?
SQL: SELECT count(*) FROM follows

Question: Find the number of followers for each user.
SQL: SELECT count(*) FROM follows GROUP BY f1

Question: Find the number of tweets in record.
SQL: SELECT count(*) FROM tweets

Question: Find the number of users who posted some tweets.
SQL: SELECT count(DISTINCT UID) FROM tweets

Question: Find the name and email of the user whose name contains the word ‘Swift’.
SQL: SELECT name ,  email FROM user_profiles WHERE name LIKE '%Swift%'

Question: Find the names of users whose emails contain ‘superstar’ or ‘edu’.
SQL: SELECT name FROM user_profiles WHERE email LIKE '%superstar%' OR email LIKE '%edu%'

Question: Return the text of tweets about the topic 'intern'.
SQL: SELECT text FROM tweets WHERE text LIKE '%intern%'

Question: Find the name and email of the users who have more than 1000 followers.
SQL: SELECT name ,  email FROM user_profiles WHERE followers  >  1000

Question: Find the names of the users whose number of followers is greater than that of the user named "Tyler Swift".
SQL: SELECT T1.name FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f1 GROUP BY T2.f1 HAVING count(*)  >  (SELECT count(*) FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f1 WHERE T1.name  =  'Tyler Swift')

Question: Find the name and email for the users who have more than one follower.
SQL: SELECT T1.name ,  T1.email FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f1 GROUP BY T2.f1 HAVING count(*)  >  1

Question: Find the names of users who have more than one tweet.
SQL: SELECT T1.name FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid  =  T2.uid GROUP BY T2.uid HAVING count(*)  >  1

Question: Find the id of users who are followed by Mary and Susan.
SQL: SELECT T2.f1 FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f2 WHERE T1.name  =  "Mary" INTERSECT SELECT T2.f1 FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f2 WHERE T1.name  =  "Susan"

Question: Find the id of users who are followed by Mary or Susan.
SQL: SELECT T2.f1 FROM user_profiles AS T1 JOIN follows AS T2 ON T1.uid  =  T2.f2 WHERE T1.name  =  "Mary" OR T1.name  =  "Susan"

Question: Find the name of the user who has the largest number of followers.
SQL: SELECT name FROM user_profiles ORDER BY followers DESC LIMIT 1

Question: Find the name and email of the user followed by the least number of people.
SQL: SELECT name ,  email FROM user_profiles ORDER BY followers LIMIT 1

Question: List the name and number of followers for each user, and sort the results by the number of followers in descending order.
SQL: SELECT name ,  followers FROM user_profiles ORDER BY followers DESC

Question: List the names of 5 users followed by the largest number of other users.
SQL: SELECT name FROM user_profiles ORDER BY followers DESC LIMIT 5

Question: List the text of all tweets in the order of date.
SQL: SELECT text FROM tweets ORDER BY createdate

Question: Find the name of each user and number of tweets tweeted by each of them.
SQL: SELECT T1.name ,  count(*) FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid  =  T2.uid GROUP BY T2.uid

Question: Find the name and partition id for users who tweeted less than twice.
SQL: SELECT T1.name ,  T1.partitionid FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid  =  T2.uid GROUP BY T2.uid HAVING count(*)  <  2

Question: Find the name of the user who tweeted more than once, and number of tweets tweeted by them.
SQL: SELECT T1.name ,  count(*) FROM user_profiles AS T1 JOIN tweets AS T2 ON T1.uid  =  T2.uid GROUP BY T2.uid HAVING count(*)  >  1

Question: Find the average number of followers for the users who do not have any tweet.
SQL: SELECT avg(followers) FROM user_profiles WHERE UID NOT IN (SELECT UID FROM tweets)

Question: Find the average number of followers for the users who had some tweets.
SQL: SELECT avg(followers) FROM user_profiles WHERE UID IN (SELECT UID FROM tweets)

Question: Find the maximum and total number of followers of all users.
SQL: SELECT max(followers) ,  sum(followers) FROM user_profiles