human
Schema:
Album (AlbumId, Title, ArtistId)
Artist (ArtistId, Name)
Customer (CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId)
Employee (EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email)
Genre (GenreId, Name)
Invoice (InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total)
InvoiceLine (InvoiceLineId, InvoiceId, TrackId, UnitPrice, Quantity)
MediaType (MediaTypeId, Name)
Playlist (PlaylistId, Name)
PlaylistTrack (PlaylistId, TrackId)
Track (TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the album titles for albums containing both 'Reggae' and 'Rock' genre tracks?
assistant
SELECT T1.Title FROM Album AS T1 JOIN Track AS T2 ON T1.AlbumId = T2.AlbumId JOIN Genre AS T3 ON T2.GenreID = T3.GenreID WHERE T3.Name = 'Reggae' INTERSECT SELECT T1.Title FROM Album AS T1 JOIN Track AS T2 ON T1.AlbumId = T2.AlbumId JOIN Genre AS T3 ON T2.GenreID = T3.GenreID WHERE T3.Name = 'Rock'
human
Find the names of all the tracks that contain the word 'you'.
assistant
SELECT Name FROM TRACK WHERE Name LIKE '%you%'
human
What is the least common media type in all tracks?
assistant
SELECT T1.Name FROM MEDIATYPE AS T1 JOIN TRACK AS T2 ON T1.MediaTypeId = T2.MediaTypeId GROUP BY T2.MediaTypeId ORDER BY COUNT(*) ASC LIMIT 1
human
How many tracks belong to rock genre?
assistant
SELECT COUNT(*) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId = T2.GenreId WHERE T1.Name = 'Rock'
human
List the names of all music genres.
assistant
SELECT Name FROM GENRE
human
Find the name of the genre that is most frequent across all tracks.
assistant
SELECT T1.Name FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId = T2.GenreId GROUP BY T2.GenreId ORDER BY COUNT( * ) DESC LIMIT 1 ;