Please Answer to the 3rd Question Due before Lab on Wednesday, January 27\". COM
ID: 3662557 • Letter: P
Question
Please Answer to the 3rd Question
Due before Lab on Wednesday, January 27". COMP355 - Database Management Systems This lab assignment has five (5) problems worth 100 points total For each problem, write an SQL query against the Chinook Database v1.4. Each query must run successfully using DB Browser for SQLite v3.4.0 (SQLite v3.8.7) A description of the correct result set for each problem is provided -your query must reproduce this result exactly (including attribute names/order and row order/contents). Problem 1- 20 points Produce a discography of the band Pearl Jam. The result set should have a single column, titled album title, and should be sorted alphabetically. Your query must not hardcode any numeric ids (e.g. artist id) SELECT alb. Title AS album title FROM album abINNERJOIN artist artON (alb Artistid=art ArtistId) WHERE art Name-Pearl Jam ORDER BY alb.Title ASC Problem 2 - 20 points Produce a track list for the two-disc album Live' by the band The Black Crowes The result set should have a the following columns: album title, title of the album, track id (numeric track identifier), track name (name of the track), minutes (full minutes in the song) and secondsinumber of seconds, rounded) The rows should be sorted first by the album title (such that all of the disc 1 tracks appear before disc 2) and then by the track id (smallest first) The minutes/seconds should be computed from the milliseconds field: the ROUND function (See https://www.sqlite.org/lang_corefunc.html for reference will be useful. SELECT alb.Title AS album title, t.Trackld AS track id, t.Name AS track name, (t.Milliseconds/60000) AS minutes, ROUND(t.Miliseconds % 60000 / 1000.0) AS seconds FROM (artist art INNER JOIN album alb ON art.Artistld-alb Artistid INNER JOIN track t ON alb.Albu Id=t.AlbumldWHERE art.Name = 'The Black Crowes' AND alb·Title LIKE 'Live%" ORDER BY album title ASC, track id ASC Problem 3 - 20 points There is one invoice that totals more than $25 For that order, produce all information necessary for an invoice report: for each purchased track, include the invoice line id (invoice line), track id (track identifier), album title (title of theExplanation / Answer
Q.1. Your answer is correct.
Query :
SELECT ALB.TITLE AS ALBUM_TITLE FROM ALBUM ALB INNER JOIN ARTIST ART ON (ALB.ARTISTID=ART.ARTISTID)
WHERE ART.NAME='Pearl Jam' ORDER BY ALB.TITLE ASC
Q.2. YOUR QUERY HAS ONLY ONE MISTAKE. THERE SHOULD NOT BE LIKE STATEMENT.CORRECT QUERY IS
SELECT ALB.TITLE AS ALBUM_TITLE, T.TRACKID AS TRACK_ID, T.NAME AS TRACK_NAME, (T.MILLISECONDS/60000) AS MINUTES,
ROUND(T.MILLISECONDS%60000/1000.0) AS SECONDS FROM (ARTIST ART INNER JOIN ALBUM ALB ON ART.ARTISTID=ALB.ARTISTID) INNER JOIN TRACK T ON ALB.ALBUMID WHERE ART.NAME='The Black Crowes'
AND ALB.TITLE='Live' ORDER BY ALBUM_TITLE ASC, TRACK_ID ASC
Q.3. yOUR QUERY IS CORRECT.