human
Schema:
Album (AlbumId, Title, ArtistId)
Artist (ArtistId, Name)
Customer (CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId)
Employee (EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email)
Genre (GenreId, Name)
Invoice (InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total)
InvoiceLine (InvoiceLineId, InvoiceId, TrackId, UnitPrice, Quantity)
MediaType (MediaTypeId, Name)
Playlist (PlaylistId, Name)
PlaylistTrack (PlaylistId, TrackId)
Track (TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice)
Please answer with SQL.
assistant
Sure, I understand.
human
How many distinct cities does the employees live in?
assistant
SELECT COUNT(DISTINCT city) FROM EMPLOYEE
human
Find all invoice dates corresponding to customers with first name Astrid and last name Gruber.
assistant
SELECT T2.InvoiceDate FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId = T2.CustomerId WHERE T1.FirstName = 'Astrid' AND LastName = 'Gruber'
human
Find all the customer last names that do not have invoice totals larger than 20.
assistant
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
human
Find the address of all customers that live in Germany and have invoice.
assistant
SELECT DISTINCT T1.Address FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId = T2.CustomerId WHERE T1.country = 'Germany'
human
Find the first names of all customers that live in Brazil and have an invoice.
assistant
SELECT DISTINCT T1.FirstName FROM CUSTOMER AS T1 JOIN INVOICE AS T2 ON T1.CustomerId = T2.CustomerId WHERE T1.country = 'Brazil'
human
What are the distinct billing countries of the invoices?
assistant
SELECT distinct ( BillingCountry ) FROM INVOICE ;