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
How many technicians are there?
assistant
SELECT count(*) FROM technician
human
Show the team that have at least two technicians.
assistant
SELECT Team FROM technician GROUP BY Team HAVING COUNT(*) >= 2
human
Show the starting years shared by technicians from team 'CLE' and 'CWS'.
assistant
SELECT Starting_Year FROM technician WHERE Team = 'CLE' INTERSECT SELECT Starting_Year FROM technician WHERE Team = 'CWS'
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
What is the starting year of the oldest technicians?
assistant
SELECT Starting_Year FROM technician ORDER BY Age DESC LIMIT 1
human
What is the team and starting year for each technician?
assistant
SELECT Team , Starting_Year FROM technician ;