Perform all the following steps in order. Write (and run) SQL queries to: 1. Create a junction table with artist_id and rec_id as fields. Call this new table rec_artist. Create a foreign key constraint for each field, artist_id and rec_id, to reference artists and recordings respectively. Make the primary key of the rec_artist table a composite key, made up of both fields. 2. Show the structure of the rec_artist table (use describe). 3. Insert data in the rec_artist table, to match the artist_id and rec_id for all recordings in the recordings table. 4. Do the following as a transaction: Delete the column artist_id from the recordings table. Show the structure of the recordings table (use describe). Show all data in the recordings table to check that everything is okay. 5. Insert more data in your tables. Use recordings and artists of your choice, but you must have at least: 3 recordings without artists. 3 artists without recordings. 3 recordings with multiple artists. 3 artists with multiple recordings. 6. Show all the data in the artists table. 7. Show all data in the recordings table. 8. Show all data in the rec_artist table. 9. List the titles of all recordings and the names of their respective artists. Do not include NULL titles or artist names. Your result set must have 2 columns (use only one SQL statement). 10. List the titles of all recordings and the names of their respective artists. Include all the recordings, even those without artists, but list the artist name as 'unknown' instead of NULL. Do not include artists without recordings. Your result set must have 2 columns (use only one SQL statement). 11. List the names of all artists and the titles of their respective recordings. Include all the artists, even those without recordings, but list the recording title as 'none' instead of NULL. Do not include recordings without artists. Your result set must have 2 columns (use only one SQL statement). 12. List the titles of all recordings and the names of their respective artists. Include all the recordings (even those without artists), and all the artists (even those without recordings). Your result set must have 2 columns (use only one statement). 13. List the names of all artists that start with the letter 'Q', and the titles of their respective recordings. Your query must return all the artists that start with Q, even those without recordings. Your result set must have 2 columns (use only one SQL statement). Remember that all your queries must work in my music database (which may have artist names starting with "Q" with no recordings). 14. List the titles of all recordings, the names of their respective artists, and the name of the recording's genre. Do not include NULL titles, genres, or artist names. Your result set must have 3 columns (use only one SQL statement). 15. List each artist's name and the number of recordings done by that artist, even if that number is 0. 16. List the name of all artists and the number of recordings done by that artist. Only include artists who have less than 3 recordings. Sort the results by the number of recordings (artists with 2 recordings first, then artists with 1 recording, than artists with no recordings). Your result set must have 2 columns (use only one SQL statement). 17. List pairs of artists that have at least one recording in common. Your result set must have 3 columns: both artists' names and the recording_id they have in common (use only one SQL statement). Only unique pairs should be displayed. Do not pair an artist with himself/herself. 18. List the name of all artists and whether they have more than 3.5 (million) in sales or not (yeso). Sort alphabetically by the artist's name. Give an alias (Hit Maker) to the yeso column. For example, your result set might look like this: +--------------------------------------------+-------------+ | name | Hit Maker | +--------------------------------------------+-------------+ | Barrymoore Chamber Orchestra | no | | Beatles | yes | | Goo Goo Dolls | no | | Lee Ann Womack | no | | London Philharmonic Orchestra | no | | Mana | no | | Maroon 5 | yes | | Michel Telo | no | | Nelly Furtado | no | | Queen | yes | | Strauss | no | +--------------------------------------------+--------------+ 11 rows in set (0.01 sec) 19. Create a view with the titles and sales of all recordings, the names of their respective artists, and the name of the recording's genre. Sort alphabetically by the name of the genre. Within the same genre, sort alphabetically by the name of the artist. Within the same artist, sort by sales (highest first). Do not include NULL titles, genres, or artist names. Your view must have 4 columns. 20. Show the structure of the view you just created (use desc).

21. Show all the data in the view you just created.

mysql> use music; Database changed mysql SHOW TABLES l Tables in nusic I l artists I genres l recordings 3 rows in set 10.13 sec) mysqla DESC artistsi l Field 1 Type I Null Key DefaultI Extra I id int 111) l name varchar 50 I YESI I NO PRI NULL auto inerement i I NULL 2 rows in set :0.06 sec] mysql> SELECTFRC artists l id I name 1 1I Magical Thinker and Stephen Wrabel I I 21Camila Mora 1 3I Liz Huett 1 4IQueen 1 5IStrau55 I 6I Maroon 5 1 1 The Mighty Mighty Bosstones I IKansascali 1 9Nelly Furtado 1 10IBarrymoore Chamber orchestra | 11ILondon Philharmonic Orchestra 11 rows in set 0.00 8eci mysql> DESC genresi l Field I Type l id1 int111) I Null I Key Defaulz Extra I NO IPRI I NULL auto increment I l name varchar 20 YESI 2 rows in set 10.00 sec) mysql> SELECT FRC genrea, l id I name I NULL 1 1 Pop 1 2I Rock 1 3IClassical I 3 rows in set 10, 00 sec mysqla DESC recordingi lFicld l rec idint 11 I Type I Null I Key DefaultExtra I NO PRI I NULL I autoincrement NO I I NO I MUL I NULL - l rec title varchar(60 NO I NULLI ength time l sales l artist id I int 11! I genre_id I int (i1 NULI I 0.00 I I decimal (5,2} I NO 1 I NO I MUL I NULL I 6 rows in set 10.00 sec)