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
Show the team that have at least two technicians.
assistant
SELECT Team FROM technician GROUP BY Team HAVING COUNT(*) >= 2
human
List the names of technicians in ascending order of age.
assistant
SELECT Name FROM technician ORDER BY Age ASC
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 are the team and starting year of technicians?
assistant
SELECT Team , Starting_Year FROM technician
human
What are the starting years shared by the technicians from the team 'CLE' or 'CWS'?
assistant
SELECT Starting_Year FROM technician WHERE Team = "CLE" INTERSECT SELECT Starting_Year FROM technician WHERE Team = "CWS" ;