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
Please show the employee last names that serves no more than 20 customers.
assistant
SELECT T1.LastName FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId = T2.EmployeeId GROUP BY T1.SupportRepId HAVING COUNT(*) <= 20
human
What is the first name and last name of the customer that has email 'luisg@embraer.com.br'?
assistant
SELECT FirstName , LastName FROM CUSTOMER WHERE Email = 'luisg@embraer.com.br'
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 birth dates of employees living in Edmonton?
assistant
SELECT BirthDate FROM EMPLOYEE WHERE City = 'Edmonton'
human
What is the first name and last name employee helps the customer with first name Leonie?
assistant
SELECT T2.FirstName , T2.LastName FROM CUSTOMER AS T1 JOIN EMPLOYEE AS T2 ON T1.SupportRepId = T2.EmployeeId WHERE T1.FirstName = 'Leonie'
human
What are the invoice dates for customers with the first name Astrid and the 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" ;