DEV II : Ming Jin Yong

DVD Titles Database

Query 1:

<aside> 🚧

Write a SQL statement that will list all of the DVD titles in the Comedy genre, ordered by their release date in DESCENDING order, with the following data columns: TITLE, RELEASE_DATE, AWARD, LABEL, SOUND, GENRE, RATING, FORMAT. This query will only include data columns and NOT any of the key columns. Hint: You will need to add an ORDER BY clause and use the keyword DESC after the fieldname. I.e. ORDER BY fieldname DESC..

</aside>

SELECT   title, release_date, award, label, sound, genre, rating, format
FROM     dvd_titles, formats, genres, labels, ratings, sounds
WHERE    dvd_titles.label_id=labels.label_id
        AND
        dvd_titles.format_id=formats.format_id
        AND
        dvd_titles.genre_id=genres.genre_id
        AND
        ratings.rating_id=dvd_titles.rating_id
        AND
        sounds.sound_id=dvd_titles.sound_id
        AND
        genres.genre = 'Comedy'
ORDER BY release_date DESC

Query 2:

<aside> 🚧

Write a SQL statement that will list all of the DVD titles that have DTS sound and are by Universal (the studio/label), with the following data columns: TITLE, RELEASE_DATE, AWARD, GENRE, RATING, FORMAT. This query will only include data columns and NOT any of the key columns.

</aside>

SELECT   title, release_date, award, genre, rating, format
FROM     dvd_titles, formats, genres, labels, ratings, sounds
WHERE    dvd_titles.label_id=labels.label_id
        AND
        dvd_titles.format_id=formats.format_id
        AND
        dvd_titles.genre_id=genres.genre_id
        AND
        ratings.rating_id=dvd_titles.rating_id
        AND
        sounds.sound_id=dvd_titles.sound_id
        AND
        sounds.sound = 'DTS'
        AND
        labels.label = 'Universal'