SQLite query using Chinook database There is one invoice tha

SQLite query using Chinook database

There is one invoice that totals more than $25. For that order, write a query to 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 the corresponding album), artist_name (name of the corresponding artist), track_name (name of the track), media_type (type of media), unit_price (unit price of the track), and qty (number purchased of the track). To format the price you may find it useful to use either string concatenation (see lecture) or the PRINTF4 function. The information result should be sorted by the album title (alphabetically), then the artist name (alphabetically), then the track name (alphabetically). Your query must not hardcode any numeric ids (e.g. invoice_id, album_id).

qty

2201

invoice_line_id	track_id	album_title	artist_name	track_name	media_type	unit_price	qty
2201	2931	Achtung Baby	U2	So Cruel	MPEG audio file	$0.99	1

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 IL.InvoiceLineID as Invoice_line_id,IL.TrackID as track_id,Al.Title as album_title,Ar.Name as artist_name,T.name as track_name,MT.Name as media_type,\'$\'+T.UnitPrice as unit_price,IL.Quanity as qty from Invoice I inner join InvoiceLine IL on I.InvoiceID = IL.InvoiceID inner join Track T on IL.TrackId = T.TrackId,inner join Album Al on T.AlbumId = Al.AlbumId inner join Artist Ar on Al.ArtistId = Ar.ArtistId inner join MediaType MT on MT.MediaTypeId = T.MediaTypeId order by Al.Title,Ar.Name,T.name;