Given below is the schema for the data. There are a total of 12 tables, thus 12 CSV

files, each corresponding to a relational table.

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

primary key(email)

celebrity (email, website, kind)

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

primary key(blurtid,email)

foreign key(email) references user(email)

hobby (email, hobby)

primary key(email,hobby)

foreign key(email) references user(email))

follow (follower,followee)

primary key(follower,followee)

foreign key(follower) references user(email)

foreign key(followee) references user(email))

vendor (id, name)

primary key(id)

vendor_ambassador (vendorid, email)

primary key(vendorid)

foreign key(email) references user(email)

foreign key(vendorid) references vendor(id))

topic (id, description)

primary key(id)

vendor_topics (vendorid,topicid)

primary key(vendorid, topicid)

foreign key(vendorid) references vendor(id)

foreign key(topicid) references topic(id))

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

primary key(email, blurtid, topicid)

foreign key(email,blurtid) references blurt(email,blurtid)

foreign key(topicid) references topic(id)

constraint confidence >= 0 and confidence <=10

constraint sentiment >= -5 and sentiment <=5

advertisement (id, content, vendorid)primary key(id)

foreign key(vendorid) references vendor(id))

user_ad (email,adid)

primary key(email,adid)

foreign key(email) references user(email)

foreign key(adid) references advertisement(id))

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 the vendor name, email and the total number of users

who are following it.

6. Let us define the term "advertisement-gap" as the number of users who have

blurted about a topic that is of interest to a vendor but are not being shown in

any advertisements from the vendor. Write an SQL query that gives the vendor

name and the corresponding "advertisement-gap" in decreasing order of the

advertisement_gap.

7. Write an SQL query to find all pairs of users (A,B) such that both A and B

have blurted on a common topic but A is not following B. Your query should

print the names of A and B in that order.

8. You need to help users connect with other users. There could be there different

users A,B and C such that A follows B, B follows C but A does not follow C.

Write an SQL query to find all such triplets of A,B, and C. Your query should

print the emails of users A,B and C in that order.

9. For each topic, find the states (e.g., California) where the average sentiment

associated with the blurts related to the topic is negative. Your query should

print the topic id, topic name, state, total # of blurts and average sentiment for

each topic.

write the answer from 5- 9 in SQL plz