Question

Using SQL Lite to code

/* 2. Write a query that reports only the track names for all tracks that feature the artist Miles Davis, sorted in alphabetical order. (Note that a composer is not the same as the artist).

Answer 1

select Name 
from Track
where AlbumId in (
    select distinct AlbumId
    from Album
    where ArtistId in (
        select distinct ArtistId
        from Artist
        where Name = 'Miles Davis'
    )
)
order by Name

In this query, the innermost query find the artistId using artistName.. Once we find the artistid, then we search the albums by this artist and find albumIds from album table.. using these AlbumIds, We find the tracks from track table and then alphabetically sort them.