SQL Aside from \"Various Artists\", \"The Office\", and \"Lost\", there are 9 ar

Question

SQL

Aside from "Various Artists", "The Office", and "Lost", there are 9 artists that have more than 50 tracks. Write a query to generate a list of these artists, identifying their name (artist name) and number of tracks (num_tracks). The list should be sorted by the number of tracks (greatest first), then by the name of the artist (alphabetically). Your query must not hardcode any numeric ids (e.g. TrackId, AlbumId, ArtistId)

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

Explanation / Answer

The following query will help you to solve the problem.

SELECT a.name AS Artist_name,
count(tracks.name) AS num_tracks
FROM track
INNER JOIN
(SELECT artist.artistid ,
artist.name,
albumid
FROM artist
INNER JOIN album
ON artist.artistid = album.albumid) AS a
ON track.albumid = a.albumid
GROUP BY a.name
HAVING num_tracks > 50
ORDER BY num_tracks, Artist_name

Understand the above query step by step.

First we join the table album and artist on artist id.

Next we join the table from step 1 with track on album id. We obtain the count of distinct tracks against each artist using group by clause and use order by to sort them( first according to count of tracks and then according to name of the artist).

Good Luck!!!

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.