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 repair assignments are there in file?
assistant
SELECT COUNT( * ) FROM repair_assignment;
human
Show me the name of technicians assigned to those repairs.
assistant
SELECT T2.Name FROM repair_assignment AS T1 JOIN technician AS T2 ON T1.technician_ID = T2.technician_ID;
human
What about the series of machines they repaired?
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
Show me those machines' quality ranks.
assistant
SELECT T3.Name , T2.Machine_series , T2.quality_rank 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
Show them in ascending order of quality rank.
assistant
SELECT T3.Name , T2.Machine_series , T2.quality_rank 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
Only show me the names.
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 ;