need help with the last few question on MySQL. also for this particular question

Question

need help with the last few question on MySQL.

also for this particular question I got this....

2 . Print the tag and name of the bird that uttered the most positive chirp about the topic
‘battery’

/////// this is what I got but I know Is wrong since I get 5 results instead of one ///////////////////////////

SELECT distinct bird.btag, bird.first_name, bird.last_name
FROM topic JOIN about ON about.id = topic.id JOIN chirp ON chirp.cno = about.cno JOIN bird ON bird.btag = chirp.btag
WHERE topic.name = 'battery' and chirp.sentiment >= 1 ;

3 b) Print the tags and business names of watchers who own no ads but are listening for
chirps from at least one male bird.
SQL Query:

c. Compute and print the number of topics that appear to be more interesting than the
topic “Sprint” – i.e., topics with at least one user-specified interest level that’s higher than any of
those for the topic “Sprint”. SQL Query:

d) To help in identifying potential Russian bot-birds, print the tags and email addresses
of those birds who have done nothing but parrot chirps from bird “realDonaldTrump” (i.e., birds
all of whose chirps are parroted Trump chirps).
SQL Query:

4. a) Print the maximum watcher fee among watchers who have specified the highest
possible level of interest (i.e., level 5) in two or more different topics.
SQL Query:


b) Find the 5 biggest parrots (i.e., the top 5 birds based on their parroted chirp counts)
and print their bird tags and the associated parrot counts in descending order.
a) SQL Query:

5. [10pts EXTRA CREDIT] Find the 5 quickest acts of parroting (i.e., the top 5 cases of parroting
based on the shortness of the time delay between the appearance of a chirp and the parroting
of that chirp) and print the associated time delays, the chirper and parrot bird tags and chirp
numbers, and also the chirp text, in ascending time delay order.
a) SQL Query:

variety income Peacock bdate Owns first name Bird bname latitude longitude ast locati Cno senti ment Utter Chirp 1 (Original M (Chirper) N (Copy) Parrots About Bird level Listen name N (Listener M interests Topics bsecto date User Topics Listen Watcher Signup date password emai add State country For Bird, Watcher ISA User: picture street Bird AND Watcher COVER User number mailcode

Explanation / Answer

1.SELECT distinct bird.btag, bird.first_name, bird.last_name
FROM topic JOIN about ON about.id = topic.id JOIN chirp ON chirp.cno = about.cno JOIN bird ON bird.btag = chirp.btag
WHERE topic.name = 'battery';



SOLUTION     
SELECT distinct bird.btag, bird.first_name, bird.last_name
from topic inner join about on
topic.id=about.id inner join
chirp on chirp.cno = about.cno inner join
bird on bird.btag = chirp.btag;

2. b) Print the tags and business names of watchers who own no ads but are listening for
chirps from at least one male bird.
SOLUTION:
Select TopicListen.tag,Watcher.bname
from TopicListen join Watcher
ON TOPIC.NAME = WATCHER.BNAME
WHERE Watcher.owns.ad = '0' and Watcher.ISA.Bird="gender";

3.Compute and print the number of topics that appear to be more interesting than the
topic “Sprint” – i.e., topics with at least one user-specified interest level that’s higher than any of
those for the topic “Sprint”. SQL Query:
SOLUTION:
select count(topic.name)
from Topic
where topic.name <> "Sprint"
compute sum(topic.name)

4.
Print the maximum watcher fee among watchers who have specified the highest
possible level of interest (i.e., level 5) in two or more different topics.
SOLUTION
Select MAX(watcher.fee)
from watcher
where Intrest.level='5'

5.Find the 5 biggest parrots (i.e., the top 5 birds based on their parroted chirp counts)
and print their bird tags and the associated parrot counts in descending order.
SOLUTION
Select Bird.first_name,Bird.last_name
from Bird join chirp
on Bird.btag=Chirp.btag
LIMIT 5

Home+stay+Rent

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

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