The music database consists of 9 tables (where keys are underlined): album_country country, album_id) album_genre (album_id, genre_id) album track (album_id, position, sang_id) albums (album id, title, released) artist_album (artist id, album id) artists (id, name) countries (code, nane, continent) genres (genre id, genre) songs (song id, title, duration) album_country.country references countries.code album_country.album_id references albums.album_id album_genre.album_id references albums.album_id album genre.genre_id references genres.genre_id album track.album id references albums. album id album track.song_id references songs.song_id artist_album. artist_id references artists.artist_id artist_album.album_id references albums.album_id 1. (2p) Find all albums released by your favorite artist (among the artists in our database). Your query should return album title and the year when it was released. 2. (3p) Find all artists in the database who released a 'Rock' or a 'Pop' album in a European country. Your query should return artist name, album title, album genre, and album country. 3. (2p) List the names of all the artists who released a song that has 'alien'in its title. Sort the results in alphabetical order by artist name. 4. (3p) Find the album(s) which had both 'Alice Deejay' and 'DJ Jurgen' as artists. Return album's title and the year when it was released. 5. (2p) Find the number of albums by genre, for albums released between 2005 and 2010. Sort the results in decreasing order of the number of albums. 6. (2p) Find the artists with the highest number of albums. Return artist names and the number of albums by the artist. 7. (3p) Find the number of albums by continent and decade, e.g. how many movies were released in Europe between (2000 and 2010]. (1990, 2000), etc. 8. (3p) Find the songs that appear in the largest number of albums. Return the song title and a comma-separated list of titles of albums in which the song appears (this list will be your second column). You can use function group concat() to obtain an aggregated comma-separated list of strings. 9. (3p) Find the albums that contain at least one song for which the duration is not known in the database. Return the album name sorted alphabetically. 10.(2p) Find the artists that produced albums for which the year when the album was released is not known. Return the name of the artist and the name of the qualifying album(s). What to turn it: A text file with all your SQL queries and the result of each query (if there are too many tuples in a result, show only the first five tuples in your result). Submit this
