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
Find the name of tracks which are in both Movies and music playlists.
assistant
SELECT T1.name FROM tracks AS T1 JOIN playlist_tracks AS T2 ON T1.id = T2.track_id JOIN playlists AS T3 ON T2.playlist_id = T3.id WHERE T3.name = 'Movies' INTERSECT SELECT T1.name FROM tracks AS T1 JOIN playlist_tracks AS T2 ON T1.id = T2.track_id JOIN playlists AS T3 ON T2.playlist_id = T3.id WHERE T3.name = 'Music'
human
List name of all tracks in Balls to the Wall.
assistant
SELECT T2.name FROM albums AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T1.title = 'Balls to the Wall';
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
List the name of tracks belongs to genre Rock or 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' OR T3.name = 'MPEG audio file';
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 names of the tracks that are Rock or Jazz songs?
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" ;