CREATE TABLE Album (
    AlbumId NUMBER PRIMARY KEY,
    Title TEXT,
    ArtistId NUMBER,
    FOREIGN KEY ("ArtistId") REFERENCES Artist("ArtistId")
);
CREATE TABLE Artist (
    ArtistId NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE Customer (
    CustomerId NUMBER PRIMARY KEY,
    FirstName TEXT,
    LastName TEXT,
    Company TEXT,
    Address TEXT,
    City TEXT,
    State TEXT,
    Country TEXT,
    PostalCode TEXT,
    Phone TEXT,
    Fax TEXT,
    Email TEXT,
    SupportRepId NUMBER,
    FOREIGN KEY ("SupportRepId") REFERENCES Employee("EmployeeId")
);
CREATE TABLE Employee (
    EmployeeId NUMBER PRIMARY KEY,
    LastName TEXT,
    FirstName TEXT,
    Title TEXT,
    ReportsTo NUMBER,
    BirthDate TIME,
    HireDate TIME,
    Address TEXT,
    City TEXT,
    State TEXT,
    Country TEXT,
    PostalCode TEXT,
    Phone TEXT,
    Fax TEXT,
    Email TEXT,
    FOREIGN KEY ("ReportsTo") REFERENCES Employee("EmployeeId")
);
CREATE TABLE Genre (
    GenreId NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE Invoice (
    InvoiceId NUMBER PRIMARY KEY,
    CustomerId NUMBER,
    InvoiceDate TIME,
    BillingAddress TEXT,
    BillingCity TEXT,
    BillingState TEXT,
    BillingCountry TEXT,
    BillingPostalCode TEXT,
    Total NUMBER,
    FOREIGN KEY ("CustomerId") REFERENCES Customer("CustomerId")
);
CREATE TABLE InvoiceLine (
    InvoiceLineId NUMBER PRIMARY KEY,
    InvoiceId NUMBER,
    TrackId NUMBER,
    UnitPrice NUMBER,
    Quantity NUMBER,
    FOREIGN KEY ("TrackId") REFERENCES Track("TrackId"),
    FOREIGN KEY ("InvoiceId") REFERENCES Invoice("InvoiceId")
);
CREATE TABLE MediaType (
    MediaTypeId NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE Playlist (
    PlaylistId NUMBER PRIMARY KEY,
    Name TEXT
);
CREATE TABLE PlaylistTrack (
    PlaylistId NUMBER PRIMARY KEY,
    TrackId NUMBER,
    FOREIGN KEY ("TrackId") REFERENCES Track("TrackId"),
    FOREIGN KEY ("PlaylistId") REFERENCES Playlist("PlaylistId")
);
CREATE TABLE Track (
    TrackId NUMBER PRIMARY KEY,
    Name TEXT,
    AlbumId NUMBER,
    MediaTypeId NUMBER,
    GenreId NUMBER,
    Composer TEXT,
    Milliseconds NUMBER,
    Bytes NUMBER,
    UnitPrice NUMBER,
    FOREIGN KEY ("MediaTypeId") REFERENCES MediaType("MediaTypeId"),
    FOREIGN KEY ("GenreId") REFERENCES Genre("GenreId"),
    FOREIGN KEY ("AlbumId") REFERENCES Album("AlbumId")
);

Question: Count the number of tracks that are part of the rock genre.
SQL: SELECT COUNT(*) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Rock"

Question: What is the average unit price of tracks that belong to Jazz genre?
SQL: SELECT AVG(UnitPrice) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Jazz"

Question: Find the average unit price of jazz tracks.
SQL: SELECT AVG(UnitPrice) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Jazz"

Question: What is the first name and last name of the customer that has email "luisg@embraer.com.br"?
SQL: SELECT FirstName ,  LastName FROM CUSTOMER WHERE Email  =  "luisg@embraer.com.br"

Question: Find the full name of the customer with the email "luisg@embraer.com.br".
SQL: SELECT FirstName ,  LastName FROM CUSTOMER WHERE Email  =  "luisg@embraer.com.br"

Question: How many customers have email that contains "gmail.com"?
SQL: SELECT COUNT(*) FROM CUSTOMER WHERE Email LIKE "%gmail.com%"

Question: Count the number of customers that have an email containing "gmail.com".
SQL: SELECT COUNT(*) FROM CUSTOMER WHERE Email LIKE "%gmail.com%"

Question: What is the first name and last name employee helps the customer with first name Leonie?
SQL: SELECT T2.FirstName ,  T2.LastName FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId WHERE T1.FirstName  =  "Leonie"

Question: Find the full names of employees who help customers with the first name Leonie.
SQL: SELECT T2.FirstName ,  T2.LastName FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId WHERE T1.FirstName  =  "Leonie"

Question: What city does the employee who helps the customer with postal code 70174 live in?
SQL: SELECT T2.City FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId WHERE T1.PostalCode  =  "70174"

Question: Find the cities corresponding to employees who help customers with the postal code 70174.
SQL: SELECT T2.City FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId WHERE T1.PostalCode  =  "70174"

Question: How many distinct cities does the employees live in?
SQL: SELECT COUNT(DISTINCT city) FROM EMPLOYEE

Question: Find the number of different cities that employees live in.
SQL: SELECT COUNT(DISTINCT city) FROM EMPLOYEE

Question: Find all invoice dates corresponding to customers with first name Astrid and last name Gruber.
SQL: SELECT T2.InvoiceDate FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId  =  T2.CustomerId WHERE T1.FirstName  =  "Astrid" AND LastName  =  "Gruber"

Question: What are the invoice dates for customers with the first name Astrid and the last name Gruber?
SQL: SELECT T2.InvoiceDate FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId  =  T2.CustomerId WHERE T1.FirstName  =  "Astrid" AND LastName  =  "Gruber"

Question: Find all the customer last names that do not have invoice totals larger than 20.
SQL: SELECT LastName FROM CUSTOMER EXCEPT SELECT T1.LastName FROM CUSTOMER AS T1 JOIN Invoice AS T2 ON T1.CustomerId  =  T2.CustomerId WHERE T2.total  >  20

Question: What are the last names of customers without invoice totals exceeding 20?
SQL: SELECT LastName FROM CUSTOMER EXCEPT SELECT T1.LastName FROM CUSTOMER AS T1 JOIN Invoice AS T2 ON T1.CustomerId  =  T2.CustomerId WHERE T2.total  >  20

Question: Find the first names of all customers that live in Brazil and have an invoice.
SQL: SELECT DISTINCT T1.FirstName FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId  =  T2.CustomerId WHERE T1.country  =  "Brazil"

Question: What are the different first names for customers from Brazil who have also had an invoice?
SQL: SELECT DISTINCT T1.FirstName FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId  =  T2.CustomerId WHERE T1.country  =  "Brazil"

Question: Find the address of all customers that live in Germany and have invoice.
SQL: SELECT DISTINCT T1.Address FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId  =  T2.CustomerId WHERE T1.country  =  "Germany"

Question: What are the addresses of customers living in Germany who have had an invoice?
SQL: SELECT DISTINCT T1.Address FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId  =  T2.CustomerId WHERE T1.country  =  "Germany"

Question: List the phone numbers of all employees.
SQL: SELECT Phone FROM EMPLOYEE

Question: What are the phone numbers for each employee?
SQL: SELECT Phone FROM EMPLOYEE

Question: How many tracks are in the AAC audio file media type?
SQL: SELECT COUNT(*) FROM MEDIATYPE AS T1 JOIN TRACK AS T2 ON T1.MediaTypeId  =  T2.MediaTypeId WHERE T1.Name  =  "AAC audio file"

Question: Count the number of tracks that are of the media type "AAC audio file".
SQL: SELECT COUNT(*) FROM MEDIATYPE AS T1 JOIN TRACK AS T2 ON T1.MediaTypeId  =  T2.MediaTypeId WHERE T1.Name  =  "AAC audio file"

Question: What is the average duration in milliseconds of tracks that belong to Latin or Pop genre?
SQL: SELECT AVG(Milliseconds) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Latin" OR T1.Name  =  "Pop"

Question: Find the average millisecond length of Latin and Pop tracks.
SQL: SELECT AVG(Milliseconds) FROM GENRE AS T1 JOIN TRACK AS T2 ON T1.GenreId  =  T2.GenreId WHERE T1.Name  =  "Latin" OR T1.Name  =  "Pop"

Question: Please show the employee first names and ids of employees who serve at least 10 customers.
SQL: SELECT T1.FirstName ,  T1.SupportRepId FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId  =  T2.EmployeeId GROUP BY T1.SupportRepId HAVING COUNT(*)  >=  10