Answer the questions below based on the database model found

Answer the questions below based on the database model found on the next page: 2. What titles are available in Digital Video format? 3. What titles and years are available for the Pop-Alternative genre? What is the result of the SQL statement below? SELECT from USIC where 4. MEDIA FOorrat \"Analog\" s. Which is the correct SQL statement to obtain a list of all music (titles and artists) in CD format? (select one) a. SELECT Artist, Title from MEDIA where Mediaype CD b. SELECT Artist, Title from MUSIC where Meaanp9,“ \"CD\" c. SELECT MUSIC where xediatse *CD Write valid SQL statements for the following actions: 6. Retrieve all the free Titles from the MUSIC relation: 7. Remove the Cassette Tape row from the MEDIA table: 8. Change the Year on \"Somebody 1 Used to Know to be 2012: 9. Add a new Artist row to the ARTIST Table (add any Artist and Genre you like):

Solution

2. From media relation:

MP4 corresponds to Digital Video Format

Hence, Titles which are MP4:

High School

Eyes Wide Open

Payphone

3. Artists that are associated with Pop-alternative genre are Gotye, Coldplay, Fiest

Hence, the titles and years that correspond to these artists:

The Reminder, 2007

Open Season, 2006

Mylo Xyloto, 2011

Somebody I used to know, 2011

Eyes wide open, 2012

4.

We need to select music whose media format is analog. Hence, it will return music records with media type vinyl and cassette tape

Hence, it will return the following records:

Born to run, Bruce Springstein, 1981, Vinyl, 15.95

Greatest Hits, Ella Flitzgerald, 1972, Vinyl, 15.95

Quadrophenia, The Who, 1973, Vinyl, 25.95

Rolling Stones Box Set, Rolling Stones, 2013, Vinyl, 50.00

5. b. SELECT Artist, Title from MUSIC where MediaType = \"CD\"

6. SELECT Title from MUSIC;

7. DELETE FROM MEDIA where MediaType=\"Cassette Tape\";

8. UPDATE MUSIC

SET Year=2012

WHERE Title=\"Somebody I used to know\";

9. INSERT INTO ARTIST

Values (\"Adam Young\", \"Pop\");