human
Schema:
artists (id, name)
sqlite_sequence (name, seq)
albums (id, title, artist_id)
employees (id, last_name, first_name, title, reports_to, birth_date, hire_date, address, city, state, country, postal_code, phone, fax, email)
customers (id, first_name, last_name, company, address, city, state, country, postal_code, phone, fax, email, support_rep_id)
genres (id, name)
invoices (id, customer_id, invoice_date, billing_address, billing_city, billing_state, billing_country, billing_postal_code, total)
media_types (id, name)
tracks (id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes, unit_price)
invoice_lines (id, invoice_id, track_id, unit_price, quantity)
playlists (id, name)
playlist_tracks (playlist_id, track_id)
Please answer with SQL.
assistant
Sure, I understand.
human
List the name of tracks belongs to genre Rock and whose media type is MPEG audio file.
assistant
SELECT T2.name FROM genres AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id JOIN media_types AS T3 ON T3.id = T2.media_type_id WHERE T1.name = 'Rock' AND T3.name = 'MPEG audio file';
human
Find number of tracks in each genre?
assistant
SELECT count(*) , T1.name FROM genres AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id GROUP BY T1.name;
human
List the name of tracks belongs to genre Rock or genre Jazz.
assistant
SELECT T2.name FROM genres AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T1.name = 'Rock' OR T1.name = 'Jazz'
human
List the top 5 genres by number of tracks. List genres name and total tracks.
assistant
SELECT T1.name , COUNT(*) FROM genres AS T1 JOIN tracks AS T2 ON T2.genre_id = T1.id GROUP BY T1.id ORDER BY count(*) DESC LIMIT 5;
human
What is the name of tracks whose genre is Rock?
assistant
SELECT T2.name FROM genres AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T1.name = 'Rock';
human
What are the different names of the genres?
assistant
SELECT DISTINCT name FROM genres ;