Database MySQL 24.) Using tables below, perform a query that finds 10 users who tweeted at least one of the following hashtags: [CompSci,CS]. Return: -User's state -User's screen name Tables: *************************************************************** CREATE TABLE tUser ( screen_name VARCHAR(200) PRIMARY KEY, userName TINYTEXT, followers INTEGER, following INTEGER, political_party TINYTEXT, category TINYTEXT, state TINYTEXT ); CREATE TABLE tweet ( tweet_id BIGINT PRIMARY KEY, text_body TEXT, retweet_count INT DEFAULT NULL, year_posted INT DEFAULT NULL, month_posted INT DEFAULT NULL, day_posted INT DEFAULT NULL, posting_user VARCHAR(200), FOREIGN KEY (posting_user) REFERENCES tUser (screen_name) ON DELETE CASCADE ); CREATE TABLE tweetMentions ( tweet_id BIGINT, screen_name VARCHAR(200), PRIMARY KEY (tweet_id , screen_name), FOREIGN KEY (tweet_id) REFERENCES tweet (tweet_id), FOREIGN KEY (screen_name) REFERENCES tUser (screen_name) ); CREATE TABLE hashtagUsed ( tag_id BIGINT PRIMARY KEY AUTO_INCREMENT, tweet_id BIGINT, tag_name VARCHAR(500), FOREIGN KEY (tweet_id) REFERENCES tweet (tweet_id) ON DELETE CASCADE ); CREATE TABLE urlUsed ( url_id BIGINT PRIMARY KEY AUTO_INCREMENT, tweet_id BIGINT, url VARCHAR(500), FOREIGN KEY (tweet_id) REFERENCES tweet (tweet_id) ON DELETE CASCADE );
24.)
Using tables below, perform a query that finds 10 users who tweeted at least one of the following hashtags: [CompSci,CS].
Return:
-User's state
-User's screen name
Tables:
***************************************************************
CREATE TABLE tUser (
screen_name VARCHAR(200) PRIMARY KEY,
userName TINYTEXT,
followers INTEGER,
following INTEGER,
political_party TINYTEXT,
category TINYTEXT,
state TINYTEXT
);
CREATE TABLE tweet (
tweet_id BIGINT PRIMARY KEY,
text_body TEXT,
retweet_count INT DEFAULT NULL,
year_posted INT DEFAULT NULL,
month_posted INT DEFAULT NULL,
day_posted INT DEFAULT NULL,
posting_user VARCHAR(200),
FOREIGN KEY (posting_user)
REFERENCES tUser (screen_name)
ON DELETE CASCADE
);
CREATE TABLE tweetMentions (
tweet_id BIGINT,
screen_name VARCHAR(200),
PRIMARY KEY (tweet_id , screen_name),
FOREIGN KEY (tweet_id)
REFERENCES tweet (tweet_id),
FOREIGN KEY (screen_name)
REFERENCES tUser (screen_name)
);
CREATE TABLE hashtagUsed (
tag_id BIGINT PRIMARY KEY AUTO_INCREMENT,
tweet_id BIGINT,
tag_name VARCHAR(500),
FOREIGN KEY (tweet_id)
REFERENCES tweet (tweet_id)
ON DELETE CASCADE
);
CREATE TABLE urlUsed (
url_id BIGINT PRIMARY KEY AUTO_INCREMENT,
tweet_id BIGINT,
url VARCHAR(500),
FOREIGN KEY (tweet_id)
REFERENCES tweet (tweet_id)
ON DELETE CASCADE
);
***************************************************************
Step by step
Solved in 2 steps
