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 is the the phone number of Nancy Edwards?
SQL: SELECT phone FROM employees WHERE first_name = "Nancy" AND last_name = "Edwards";

Question: Who is the youngest employee in the company? List employee's first and last name.
SQL: SELECT first_name , last_name FROM employees ORDER BY birth_date DESC LIMIT 1;

Question: What si the youngest employee's first and last name?
SQL: SELECT first_name , last_name FROM employees ORDER BY birth_date DESC LIMIT 1;

Question: List top 10 employee work longest in the company. List employee's first and last name.
SQL: SELECT first_name , last_name FROM employees ORDER BY hire_date ASC LIMIT 10;

Question: What are the first and last names of the top 10 longest-serving employees?
SQL: SELECT first_name , last_name FROM employees ORDER BY hire_date ASC LIMIT 10;

Question: Find the number of employees whose title is IT Staff from each city?
SQL: SELECT count(*) ,  city FROM employees WHERE title  =  'IT Staff' GROUP BY city

Question: How many employees who are IT staff are from each city?
SQL: SELECT count(*) ,  city FROM employees WHERE title  =  'IT Staff' GROUP BY city

Question: Which employee manage most number of peoples? List employee's first and last name, and number of people report to that employee.
SQL: SELECT T2.first_name , T2.last_name ,  count(T1.reports_to) FROM employees AS T1 JOIN employees AS T2 ON T1.reports_to = T2.id GROUP BY T1.reports_to ORDER BY count(T1.reports_to) DESC LIMIT 1;

Question: What are the first and last names of all the employees and how many people report to them?
SQL: SELECT T2.first_name , T2.last_name ,  count(T1.reports_to) FROM employees AS T1 JOIN employees AS T2 ON T1.reports_to = T2.id GROUP BY T1.reports_to ORDER BY count(T1.reports_to) DESC LIMIT 1;

Question: How many orders does Lucas Mancini has?
SQL: SELECT count(*) FROM customers AS T1 JOIN invoices AS T2 ON T1.id = T2.customer_id WHERE T1.first_name = "Lucas" AND T1.last_name = "Mancini";

Question: How many orders does Luca Mancini have in his invoices?
SQL: SELECT count(*) FROM customers AS T1 JOIN invoices AS T2 ON T1.id = T2.customer_id WHERE T1.first_name = "Lucas" AND T1.last_name = "Mancini";

Question: What is the total amount of money spent by Lucas Mancini?
SQL: SELECT sum(T2.total) FROM customers AS T1 JOIN invoices AS T2 ON T1.id = T2.customer_id WHERE T1.first_name = "Lucas" AND T1.last_name = "Mancini";

Question: How much money did Lucas Mancini spend?
SQL: SELECT sum(T2.total) FROM customers AS T1 JOIN invoices AS T2 ON T1.id = T2.customer_id WHERE T1.first_name = "Lucas" AND T1.last_name = "Mancini";

Question: List all media types.
SQL: SELECT name FROM media_types;

Question: What are the names of all the media types?
SQL: SELECT name FROM media_types;

Question: List all different genre types.
SQL: SELECT DISTINCT name FROM genres;

Question: What are the different names of the genres?
SQL: SELECT DISTINCT name FROM genres;

Question: List the name of all playlist.
SQL: SELECT name FROM playlists;

Question: What are the names of all the playlists?
SQL: SELECT name FROM playlists;

Question: Who is the composer of track Fast As a Shark?
SQL: SELECT composer FROM tracks WHERE name = "Fast As a Shark";

Question: What is the composer who created the track "Fast As a Shark"?
SQL: SELECT composer FROM tracks WHERE name = "Fast As a Shark";

Question: How long does track Fast As a Shark has?
SQL: SELECT milliseconds FROM tracks WHERE name = "Fast As a Shark";

Question: How many milliseconds long is Fast As a Shark?
SQL: SELECT milliseconds FROM tracks WHERE name = "Fast As a Shark";

Question: What is the name of tracks whose genre is Rock?
SQL: SELECT T2.name FROM genres AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T1.name = "Rock";

Question: What is the name of all tracks in the Rock genre?
SQL: SELECT T2.name FROM genres AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T1.name = "Rock";

Question: What is title of album which track Balls to the Wall belongs to?
SQL: SELECT T1.title FROM albums AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T2.name = "Balls to the Wall";

Question: What is the name of the album that has the track Ball to the Wall?
SQL: SELECT T1.title FROM albums AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T2.name = "Balls to the Wall";

Question: List name of all tracks in Balls to the Wall.
SQL: SELECT T2.name FROM albums AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T1.title = "Balls to the Wall";

Question: What is the name of all tracks in the album named Balls to the Wall?
SQL: SELECT T2.name FROM albums AS T1 JOIN tracks AS T2 ON T1.id = T2.genre_id WHERE T1.title = "Balls to the Wall";

Question: List title of albums have the number of tracks greater than 10.
SQL: SELECT T1.title FROM albums AS T1 JOIN tracks AS T2 ON T1.id = T2.album_id GROUP BY T1.id HAVING count(T1.id)  >  10;

Question: What are the names of the albums that have more than 10 tracks?
SQL: SELECT T1.title FROM albums AS T1 JOIN tracks AS T2 ON T1.id = T2.album_id GROUP BY T1.id HAVING count(T1.id)  >  10;

Question: List the name of tracks belongs to genre Rock and whose 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" AND T3.name = "MPEG audio file";