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 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
List the names of technicians in ascending order of age.
assistant
SELECT Name FROM technician ORDER BY Age ASC
human
What are the names of the technicians by ascending order of quality rank for 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 ;