Question

Write an SQL query against the Chinook Database v1.4. Each query must run successfully using DB Browser for SQLite. Create the exact query below. Must use sub query. I saw that a similar solution for this question used IF and CONCAT functions which do not work and return errors. If it is possible to use the CASE function in place of IF that would be greatly appreciated. Also I really hate to ask but if it could be organized neatly that would also be greatly appreciated. Thanks!!

3. Problem 3 (20 points). There is a single track with "odd" pricing: it is either an audio track whose price is not $0.99 or it is a video track whose price is not $1.99. Write a query to identify this track and retrieve information about the entire album; specifically, you are to retrieve for each album track the following fields in order: the title of the album (album_title), the album's artist's name (artist_name), the name of the track (track_name), the name of the media type of the track (media_type), and the unit price of the track with the $ prepended (unit_price; e.g. $0.99). The tracks should be sorted in order of the TrackId (smallest first). Your query must not hardcode any numeric ids (e.g. TrackId, AlbumId, ArtistId). Furthermore, your query must identify the track with "odd" pricing according to the conditions stated above (i.e. you cannot hardcode which of the two conditions will identify the track). Your query must have subquery for full points album_title artist_name track_name media_type unit_price Revelations Audioslave Revelations Protected AAC audio file $0.99 Revelations Audioslave One and the Same Protected AAC audio file $0.99 Revelations Audioslave Sound of a Gun Protected AAC audio file $0.99 Revelations Audioslave Until We Fall Protected AAC audio file $0.99 Revelations Audioslave Original Fire Protected AAC audio file $0.99 Revelations Audioslave Broken City Protected AAC audio file $0.99 Revelations Audioslave Somedays Protected AAC audio file $0.99 Revelations Audioslave Shape of Things to Come Protected AAC audio file $0.99 Revelations Audioslave Jewel of the Summertime Protected AAC audio file $0.99 Revelations Audioslave Wide Awake Protected AAC audio file $0.99 Revelations Audioslave Nothing Left to Say But Goodbye Protected AAC audio file $0.99 Revelations Audioslave Moth Protected AAC audio file $0.99 Revelations Audioslave Show Me How to Live (Live at the Quart Festival) Protected AAC audio file $0.99 Revelations Audioslave Band Members Discuss Tracks from "Revelations" Protected MPEG-4 video file $0.99

Answer 1

select album.title, artist.name, track.name, mediaType.name, concat("$",invoiceLine.unitPrice) from

album, artist, track, mediaType, invoiceLine where

album.artistId=artist.artistId and album.albumId=track.albumId and track.mediatypeId=mediaType.mediaTypeId and track.trackId=invoiceLine.trackId ' and

(mediaType.name like '%audio%' and invoiceLine.unitPrice!=0.99) OR

(mediaType.name like '%video%' and invoiceLine.unitPrice!=1.99)

orderBy track.trackId