Question (2) (Total: 15+5+5+5+10+15 = 55 points) All exercises of this question concern the following database. (You may use “music.sql” code available on blackboard in order that you could answer question – 2.) (Note: “Music.sql” is not Oracle SQL code; it is other SQL code. You may use in oracle just by changing few things while you execute at Oracle SQL prompt) 4.11. The head (a) (15 points) You’ve decided to store more information about artists and albums. Specifically, for artists, you want to store the names of people who have worked with the artist (for example, vocalists, guitarists, trumpeters, and drummers), when they began working with the artist, and when they stopped working with the artist (if they have done so). For albums, you want to store the name of the album producer, when the album was released, and where the album was recorded. Update your SQL tables or columns that can store this information. Choose the appropriate column data types as per need. You need to submit updated Oracle SQL code in separate file (sql) and also include your command line prompt screen shot after the execution of the code. (5 points) Write a join query that displays the name of each artist and the albums they’ve made. Alongside the album, display the number of tracks on the album. (c) (5 points) Repeat part (b), but now display only those albums that have more than 10 tracks. (d) (5 points) Repeat part (b) but write it as a nested query. (a) (15 points) What queries (design yourself) on “MUSIC database so that you could use following keywords in each query, selecting from ANY, ALL, EXISTS (or NOT EXISTS), and IN (or NOT IN). Show transcribed image text Question (2) (Total: 15+5+5+5+10+15 = 55 points) All exercises of this question concern the following database. (You may use “music.sql” code available on blackboard in order that you could answer question – 2.) (Note: “Music.sql” is not Oracle SQL code; it is other SQL code. You may use in oracle just by changing few things while you execute at Oracle SQL prompt) 4.11. The head (a) (15 points) You’ve decided to store more information about artists and albums. Specifically, for artists, you want to store the names of people who have worked with the artist (for example, vocalists, guitarists, trumpeters, and drummers), when they began working with the artist, and when they stopped working with the artist (if they have done so). For albums, you want to store the name of the album producer, when the album was released, and where the album was recorded. Update your SQL tables or columns that can store this information. Choose the appropriate column data types as per need. You need to submit updated Oracle SQL code in separate file (sql) and also include your command line prompt screen shot after the execution of the code. (5 points) Write a join query that displays the name of each artist and the albums they’ve made. Alongside the album, display the number of tracks on the album. (c) (5 points) Repeat part (b), but now display only those albums that have more than 10 tracks. (d) (5 points) Repeat part (b) but write it as a nested query. (a) (15 points) What queries (design yourself) on “MUSIC database so that you could use following keywords in each query, selecting from ANY, ALL, EXISTS (or NOT EXISTS), and IN (or NOT IN).
Expert Answer
Answer to Question (2) (Total: 15+5+5+5+10+15 = 55 points) All exercises of this question concern the following database. (You may…