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: A list of the top 5 countries by number of invoices. List country name and number of invoices.
SQL: SELECT billing_country ,  COUNT(*) FROM invoices GROUP BY billing_country ORDER BY count(*) DESC LIMIT 5;

Question: What are the top 5 countries by number of invoices and how many do they have?
SQL: SELECT billing_country ,  COUNT(*) FROM invoices GROUP BY billing_country ORDER BY count(*) DESC LIMIT 5;

Question: A list of the top 8 countries by gross/total invoice size. List country name and gross invoice size.
SQL: SELECT billing_country ,  SUM(total) FROM invoices GROUP BY billing_country ORDER BY SUM(total) DESC LIMIT 8;

Question: What are the names of the top 8 countries by total invoice size and what are those sizes?
SQL: SELECT billing_country ,  SUM(total) FROM invoices GROUP BY billing_country ORDER BY SUM(total) DESC LIMIT 8;

Question: A list of the top 10 countries by average invoice size. List country name and average invoice size.
SQL: SELECT billing_country ,  AVG(total) FROM invoices GROUP BY billing_country ORDER BY AVG(total) DESC LIMIT 10;

Question: What are the names of the countries and average invoice size of the top countries by size?
SQL: SELECT billing_country ,  AVG(total) FROM invoices GROUP BY billing_country ORDER BY AVG(total) DESC LIMIT 10;

Question: Find out 5 customers who most recently purchased something. List customers' first and last name.
SQL: SELECT T1.first_name ,  T1.last_name FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id ORDER BY T2.invoice_date DESC LIMIT 5;

Question: What are the first and last names of the 5 customers who purchased something most recently?
SQL: SELECT T1.first_name ,  T1.last_name FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id ORDER BY T2.invoice_date DESC LIMIT 5;

Question: Find out the top 10 customers by total number of orders. List customers' first and last name and the number of total orders.
SQL: SELECT T1.first_name ,  T1.last_name ,  COUNT(*) FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id GROUP BY T1.id ORDER BY COUNT(*) DESC LIMIT 10;

Question: What are the top 10 customers' first and last names by total number of orders and how many orders did they make?
SQL: SELECT T1.first_name ,  T1.last_name ,  COUNT(*) FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id GROUP BY T1.id ORDER BY COUNT(*) DESC LIMIT 10;

Question: List the top 10 customers by total gross sales. List customers' first and last name and total gross sales.
SQL: SELECT T1.first_name ,  T1.last_name ,  SUM(T2.total) FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id GROUP BY T1.id ORDER BY SUM(T2.total) DESC LIMIT 10;

Question: What are the top 10 customers' first and last names with the highest gross sales, and also what are the sales?
SQL: SELECT T1.first_name ,  T1.last_name ,  SUM(T2.total) FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id GROUP BY T1.id ORDER BY SUM(T2.total) DESC LIMIT 10;

Question: List the top 5 genres by number of tracks. List genres name and total tracks.
SQL: 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;

Question: How many tracks does each genre have and what are the names of the top 5?
SQL: 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;

Question: List every album's title.
SQL: SELECT title FROM albums;

Question: What are the titles of all the albums?
SQL: SELECT title FROM albums;

Question: List every album ordered by album title in ascending order.
SQL: SELECT title FROM albums ORDER BY title;

Question: What are the titles of all the albums alphabetically ascending?
SQL: SELECT title FROM albums ORDER BY title;

Question: List every album whose title starts with A in alphabetical order.
SQL: SELECT title FROM albums WHERE title LIKE 'A%' ORDER BY title;

Question: What are the titles of all albums that start with A in alphabetical order?
SQL: SELECT title FROM albums WHERE title LIKE 'A%' ORDER BY title;

Question: List the customers first and last name of 10 least expensive invoices.
SQL: SELECT T1.first_name ,  T1.last_name FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id ORDER BY total LIMIT 10;

Question: What are the first and last names of the customers with the 10 cheapest invoices?
SQL: SELECT T1.first_name ,  T1.last_name FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id  =  T1.id ORDER BY total LIMIT 10;

Question: List total amount of  invoice from Chicago, IL.
SQL: SELECT sum(total) FROM invoices WHERE billing_city  =  "Chicago" AND billing_state  =  "IL";

Question: What are the total amount of money in the invoices billed from Chicago, Illinois?
SQL: SELECT sum(total) FROM invoices WHERE billing_city  =  "Chicago" AND billing_state  =  "IL";

Question: List the number of invoices from Chicago, IL.
SQL: SELECT COUNT(*) FROM invoices WHERE billing_city  =  "Chicago" AND billing_state  =  "IL";

Question: How many invoices were billed from Chicago, IL?
SQL: SELECT COUNT(*) FROM invoices WHERE billing_city  =  "Chicago" AND billing_state  =  "IL";

Question: List the number of invoices from the US, grouped by state.
SQL: SELECT billing_state ,  COUNT(*) FROM invoices WHERE billing_country  =  "USA" GROUP BY billing_state;

Question: How many invoices were billed from each state?
SQL: SELECT billing_state ,  COUNT(*) FROM invoices WHERE billing_country  =  "USA" GROUP BY billing_state;