human
Schema:
repair (repair_ID, name, Launch_Date, Notes)
machine (Machine_ID, Making_Year, Class, Team, Machine_series, value_points, quality_rank)
technician (technician_id, Name, Team, Starting_Year, Age)
repair_assignment (technician_id, repair_ID, Machine_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
List the name of technicians whose team is not 'NYY'.
assistant
SELECT Name FROM technician WHERE Team != 'NYY'
human
What are the team and starting year of technicians?
assistant
SELECT Team , Starting_Year FROM technician
human
Show the team that have at least two technicians.
assistant
SELECT Team FROM technician GROUP BY Team HAVING COUNT(*) >= 2
human
Please show the team that has the most number of technicians.
assistant
SELECT Team FROM technician GROUP BY Team ORDER BY COUNT(*) DESC LIMIT 1
human
How many technicians are there?
assistant
SELECT count(*) FROM technician
human
For each team, how many technicians are there?
assistant
SELECT Team , COUNT( * ) FROM technician GROUP BY Team ;