SQL There is a single track with odd pricing it is either an

SQL

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 Artist Albumld Artistld Title Name L Artist ld PlaylistTrack Playlist playlistId stid Trackld Name Employee n Employ eeld e-m Customer Last Name CustomerId First Name FirstName Title LastName ReportsTo Com BirthDate Address HireDate City Address State City Country State PostalCode Country Phone PostalCode Fax Phone Email Fax Email supportRepld Track Trackld Name Albumld MediaTypeld Genreld Composer Milliseconds Bytes Unit Price Invoice Line Invoice Lineld nvoiceld Trackld UnitPrice Quantity Invoice Invoice Id L customer ld Invoice Date Billing Address BillingCity Billingstate BillingCountry Billing PostalCode Total Media Type Media T Name Genre Genreld Name

Solution

Select Al.Title,Ar.Name,T.Name,MT.Name,\'$\'|| T.UnitPrice from Track T inner join Album Al on T.AlbumId = Al.AlbumId inner join Artist Ar on Al.ArtistId = Ar.ArtistId inner join MediaType MT on T.MediaTypeId = MT.MediaTypeId where T.UnitPrice = (Select UnitPrice from T where MT.Name = \'audio\' and T.UnitPrice != 0.99 or Mt.Name = \'video\' and T.UnitPrice != 1.99)

order by T.TrackId;