CREATE TABLE artists (
    id NUMBER PRIMARY KEY,
    name TEXT
);
CREATE TABLE sqlite_sequence (
    name TEXT,
    seq TEXT
);
CREATE TABLE albums (
    id NUMBER PRIMARY KEY,
    title TEXT,
    artist_id NUMBER,
    FOREIGN KEY ("artist_id") REFERENCES artists("id")
);
CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    last_name TEXT,
    first_name TEXT,
    title TEXT,
    reports_to NUMBER,
    birth_date TIME,
    hire_date TIME,
    address TEXT,
    city TEXT,
    state TEXT,
    country TEXT,
    postal_code TEXT,
    phone TEXT,
    fax TEXT,
    email TEXT,
    FOREIGN KEY ("reports_to") REFERENCES employees("id")
);
CREATE TABLE customers (
    id NUMBER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    company TEXT,
    address TEXT,
    city TEXT,
    state TEXT,
    country TEXT,
    postal_code TEXT,
    phone TEXT,
    fax TEXT,
    email TEXT,
    support_rep_id NUMBER,
    FOREIGN KEY ("support_rep_id") REFERENCES employees("id")
);
CREATE TABLE genres (
    id NUMBER PRIMARY KEY,
    name TEXT
);
CREATE TABLE invoices (
    id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    invoice_date TIME,
    billing_address TEXT,
    billing_city TEXT,
    billing_state TEXT,
    billing_country TEXT,
    billing_postal_code TEXT,
    total NUMBER,
    FOREIGN KEY ("customer_id") REFERENCES customers("id")
);
CREATE TABLE media_types (
    id NUMBER PRIMARY KEY,
    name TEXT
);
CREATE TABLE tracks (
    id NUMBER PRIMARY KEY,
    name TEXT,
    album_id NUMBER,
    media_type_id NUMBER,
    genre_id NUMBER,
    composer TEXT,
    milliseconds NUMBER,
    bytes NUMBER,
    unit_price NUMBER,
    FOREIGN KEY ("media_type_id") REFERENCES media_types("id"),
    FOREIGN KEY ("genre_id") REFERENCES genres("id"),
    FOREIGN KEY ("album_id") REFERENCES albums("id")
);
CREATE TABLE invoice_lines (
    id NUMBER PRIMARY KEY,
    invoice_id NUMBER,
    track_id NUMBER,
    unit_price NUMBER,
    quantity NUMBER,
    FOREIGN KEY ("track_id") REFERENCES tracks("id"),
    FOREIGN KEY ("invoice_id") REFERENCES invoices("id")
);
CREATE TABLE playlists (
    id NUMBER PRIMARY KEY,
    name TEXT
);
CREATE TABLE playlist_tracks (
    playlist_id NUMBER PRIMARY KEY,
    track_id NUMBER,
    FOREIGN KEY ("track_id") REFERENCES tracks("id"),
    FOREIGN KEY ("playlist_id") REFERENCES playlists("id")
);

Question: What are the names of all Rock tracks that are stored on MPEG audio files?
SQL: 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";

Question: List the name of tracks belongs to genre Rock or media type is MPEG audio file.
SQL: 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";

Question: What are the names of all tracks that belong to the Rock genre and whose media type is MPEG?
SQL: 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";

Question: List the name of tracks belongs to genre Rock or genre Jazz.
SQL: 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"

Question: What are the names of the tracks that are Rock or Jazz songs?
SQL: 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"

Question: List the name of all tracks in the playlists of Movies.
SQL: SELECT T1.name FROM tracks AS T1 JOIN playlist_tracks AS T2 ON T1.id = T2.track_id JOIN playlists AS T3 ON T3.id = T2.playlist_id WHERE T3.name = "Movies";

Question: What are the names of all tracks that are on playlists titled Movies?
SQL: SELECT T1.name FROM tracks AS T1 JOIN playlist_tracks AS T2 ON T1.id = T2.track_id JOIN playlists AS T3 ON T3.id = T2.playlist_id WHERE T3.name = "Movies";

Question: List the name of playlist which has number of tracks greater than 100.
SQL: SELECT T2.name FROM playlist_tracks AS T1 JOIN playlists AS T2 ON T2.id = T1.playlist_id GROUP BY T1.playlist_id HAVING count(T1.track_id)  >  100;

Question: What are the names of all playlists that have more than 100 tracks?
SQL: SELECT T2.name FROM playlist_tracks AS T1 JOIN playlists AS T2 ON T2.id = T1.playlist_id GROUP BY T1.playlist_id HAVING count(T1.track_id)  >  100;

Question: List all tracks bought by customer Daan Peeters.
SQL: SELECT T1.name FROM tracks AS T1 JOIN invoice_lines AS T2 ON T1.id = T2.track_id JOIN invoices AS T3 ON T3.id = T2.invoice_id JOIN customers AS T4 ON T4.id = T3.customer_id WHERE T4.first_name = "Daan" AND T4.last_name = "Peeters";

Question: What are the tracks that Dean Peeters bought?
SQL: SELECT T1.name FROM tracks AS T1 JOIN invoice_lines AS T2 ON T1.id = T2.track_id JOIN invoices AS T3 ON T3.id = T2.invoice_id JOIN customers AS T4 ON T4.id = T3.customer_id WHERE T4.first_name = "Daan" AND T4.last_name = "Peeters";

Question: How much is the track Fast As a Shark?
SQL: SELECT unit_price FROM tracks WHERE name = "Fast As a Shark";

Question: What is the unit price of the tune "Fast As a Shark"?
SQL: SELECT unit_price FROM tracks WHERE name = "Fast As a Shark";

Question: Find the name of tracks which are in Movies playlist but not in music playlist.
SQL: 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' EXCEPT 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'

Question: What are the names of all tracks that are on the Movies playlist but not in the music playlist?
SQL: 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' EXCEPT 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'

Question: Find the name of tracks which are in both Movies and music playlists.
SQL: 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'

Question: What are the names of all the tracks that are in both the Movies and music playlists?
SQL: 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'

Question: Find number of tracks in each genre?
SQL: SELECT count(*) ,  T1.name FROM genres AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id GROUP BY T1.name;

Question: How many tracks are in each genre?
SQL: SELECT count(*) ,  T1.name FROM genres AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id GROUP BY T1.name;