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
Show names of technicians and series of machines they are assigned to repair.
assistant
SELECT T3.Name , T2.Machine_series FROM repair_assignment AS T1 JOIN machine AS T2 ON T1.machine_id = T2.machine_id JOIN technician AS T3 ON T1.technician_ID = T3.technician_ID
human
What are the team and starting year of technicians?
assistant
SELECT Team , Starting_Year FROM technician
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
Show the team that have at least two technicians.
assistant
SELECT Team FROM technician GROUP BY Team HAVING COUNT(*) >= 2
human
What is the name of the technician whose team is not 'NYY'?
assistant
SELECT Name FROM technician WHERE Team != "NYY" ;