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 names of technicians who have not been assigned to repair machines.
assistant
SELECT Name FROM technician WHERE technician_id NOT IN (SELECT technician_id FROM repair_assignment)
human
List the names of technicians in ascending order of age.
assistant
SELECT Name FROM technician ORDER BY Age ASC
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
List the name of technicians whose team is not 'NYY'.
assistant
SELECT Name FROM technician WHERE Team != "NYY" ;