need help with data base plz using mySQL 1 a Print the name

need help with data base plz using mySQL

1. a) Print the name, gender, and birthdate of birds who live on “Alicia Pass” street.
SQL Query:

b)Print the contact information (email and complete address) for users who have
uttered at least one highly positive chirp (i.e., at least one chirp with a sentiment of 1.0).

SQL Query:

c) Print the tag and name of the bird that uttered the most positive chirp about the topic
‘battery’.
SQL Query:

d) Print the tag, gender, and birthdate of those birds who have chirped about either of
the topics “Surface” or “Kindle”.
SQL Query:


2. a) Print the tag, gender, and birthdate of those birds who have chirped about both of the
topics “iPhone” and “gadget”.
SQL Query:

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:

3. 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:

4. [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:

first cno name gender Utter bdate Chirp ncome 1 (Original Bird M (Chirper) (Copy) Parrots About Bird Listen (Listener) M Interests Topic Peacock bname fdate User Topic Listen Watcher signup Owns addr id caption Ad county For Bird, Watcher ISA User: picture Bird AND Watcher COVER User

Solution

1.   a) SELECT concat(`bird`.`first_name`,\' \',`bird.last_name`) as name,`bird`.`gender`,`bird`.`birthdate` FROM `bird` JOIN `birdlisten` ON `bird`.`btag` = `birdlisten`.`btag` JOIN `user` ON `birdlisten`.`tag` = `user`.`tag` WHERE `user`.`street` = \'Alicia Pass\';

   b)   SELECT `user`.`email`, CONCAT(`user`.`address_number`,\' \',`user`.`address_street`,\' \',`user`.`address_city`,\' \',`user`.`address_state`,\' \',`user`.`address_country`,\' \',`user`.`address_mailcode`) AS address FROM `user` JOIN `birdlisten` ON `birdlisten`.`tag` = `user`.`tag` JOIN `chirp` ON `chirp`.`btag` = `birdlisten`.`btag` WHERE `chirp`.`sentiment` > 1.0;

   c)   SELECT `bird`.`tag`, CONCAT(`bird`.`first_name`,\' \',`bird.last_name`) AS 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`.`tag` = \'battery\';

   d)   SELECT `bird`.`tag`, `bird`.`gender`, `bird`.`birthdate` FROM `bird` JOIN `chirp` ON `bird`.`btag` = `chirp`.`btag` JOIN `about` ON `chirp`.`cno` = `about`.`cno` JOIN `topic` ON `about`.`id` = `topic`.`id` WHERE `topic`.`tag` IN (\'Surface\',\'Kindle\')
  

2. a) SELECT alldata.* FROM ( SELECT `bird`.`tag`, `bird`.`gender`, `bird`.`birthdate` FROM `bird` JOIN `chirp` ON `bird`.`btag` = `chirp`.`btag` JOIN `about` ON `chirp`.`cno` = `about`.`cno` JOIN `topic` ON `about`.`id` = `topic`.`id` WHERE `topic`.`tag` = \'iPhone\' ) AS alldata JOIN ( SELECT `bird`.`tag`, `bird`.`gender`, `bird`.`birthdate` FROM `bird` JOIN `chirp` ON `bird`.`btag` = `chirp`.`btag` JOIN `about` ON `chirp`.`cno` = `about`.`cno` JOIN `topic` ON `about`.`id` = `topic`.`id` WHERE `topic`.`tag` = \'iPhone\' ) AS alldata2 ON `alldata`.`tag` = `alldata2`.`tag`;