Solution: Uncovering unpopular tracks - SQL Tutorial

(lively music) - [Instructor] In this challenge, you are asked by the management of Red30 Tech to help them identify tracks that have not been selling. So let's take a look at how we would respond to this request. First, starting with our comment block. Within our block, we've specified the query's author, the query's create date, as well as a short description of what it's doing. With this done, we can go down to our FROM clause and here we've specified the track table, aliased with a t, joined to our genre table, aliased with a g via the ON keyword, which is specifying the GenreId in both of these tables as the common field between the two. With our FROM clause complete, we can go up to our SELECT clause and include the fields from these tables that we want to see in our results. And these fields are the TrackId field from the track table, the name field as well from the track table. And these fields are aliased as TrackiD and TrackName respectively. Again, pointing out that both these aliases have spaces in them, so we've surrounded them both with double quotes. The next field that we're interested in seeing from the track table is the composer field, and the last field in our result set is going to be the name field from the genre table, aliased as genre. Now, let's go down to our WHERE clause and in here, we have TrackId NOT IN and we have a subquery within our brackets here and that subquery states SELECT DISTINCT InvoiceLine.TrackId FROM InvoiceLine. Now, just referencing our question again, we're asked to identify the tracks that are not selling. Where is our selling information or data held? And that is in our InvoiceLine table. So by us stating within our WHERE clause TrackId NOT IN the InvoiceLine table, then we are saying we effectively want the tracks that are not selling. That is do not have a record of sale in the InvoiceLine table. Last but not least is our ORDER BY clause where we've specified our Track Name field as the field that we want our results sorted by in ascending order. Let's now hit test my code and take a look at our result. Under console output, we can see that we have our four fields specified and we also are identifying the tracks that are not selling effectively, helping Red30 Tech's management to optimize their database.