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
What are the first names and last names of the employees who live in Calgary city.
assistant
SELECT FirstName , LastName FROM EMPLOYEE WHERE City = 'Calgary'
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
List the phone numbers of all employees.
assistant
SELECT Phone FROM EMPLOYEE
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
Please show the employee first names and ids of employees who serve at least 10 customers.
assistant
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
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 ;