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 names of technicians in ascending order of quality rank of the machine they are assigned.
assistant
SELECT T3.Name 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 ORDER BY T2.quality_rank
human
Show names of technicians who are assigned to repair machines with value point more than 70.
assistant
SELECT T3.Name 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 WHERE T2.value_points > 70
human
Show names of technicians and the number of machines they are assigned to repair.
assistant
SELECT T2.Name , COUNT(*) FROM repair_assignment AS T1 JOIN technician AS T2 ON T1.technician_ID = T2.technician_ID GROUP BY T2.Name
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 names of the technicians that have not been assigned to repair machines?
assistant
SELECT Name FROM technician WHERE technician_id NOT IN ( SELECT technician_id FROM repair_assignment ) ;