Write SQL queries for the database and questions below. user (email, password, n

Question

Write SQL queries for the database and questions below.

user (email, password, name, date_of_birth, address, type)

celebrity (email, website, kind)

blurt (blurtid, email, text, location, time)

foreign key(email) references user(email)

hobby (email, hobby)

follow (follower,followee)

vendor (id, name)

vendor_ambassador (vendorid, email)

topic (id, description)

vendor_topics (vendorid,topicid)

blurt_analysis (email,blurtid,topicid,confidence,sentiment)

advertisement (id, content, vendorid)

user_ad (email,adid)

1) For each topic, find the total number of blurts that were analyzed as being

related to the topic. Order the result by topic id. Your SQL query should print

the topic id, topic description and the corresponding count.

2) For each celebrity user, find the total number of followers. Your SQL query

should print the name of the celebrity and the corresponding number of

followers.

3) For each celebrity, find the number of blurts. Order the result in decreasing

order of the number of blurts. Your query should print the name of the

celebrity and the associated count in decreasing order of the count.

4) Write an SQL query to print names of all celebrities who are not following

anyone!

5) Write an SQL query that gives the email of its brand ambassador and the

number users who are following the brand ambassador for each vendor. Your

SQL query should print vendor name, email and the total number of users who

are following it.

Explanation / Answer

1.
SELECT topic.topicid, topic.description, COUNT(blurt_analysis.blurted)
FROM blurt_analysis, topic
WHERE topic.topicid= blurt_analysis.topicid
GROUP BY topicid
ORDER BY topicid;

2.

SELECT user.name, count(follow.follower)
FROM follow, celebrity, user
WHERE follow.followee=user.email
AND follow.followee= celebrity.email
GROUP BY follow.followee;

3.

SELECT user.name, count(blurt.blurtid)
FROM celebrity, user,blurt
WHERE celebrity.email=user.email
AND celebrity.email= blurt.email
GROUP BY celebrity.email
ORDER BY count(blurt.blurtid) DESC;

4.

SELECT user.name
FROM celebrity, user
WHERE celebrity.email=user.email
AND celebrity.email NOT IN (SELECT follower FROM follow);

5.

SELECT user.name, user.email, count(follow.follower)
FROM vendor_ambassador, user, follow
WHERE vendor_ambassador.email=user.email
AND follow.followee= vendor_ambassador.email
Group by vendor_ambassador.email;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.