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
Show the names of all technicians.
assistant
SELECT name FROM technician;
human
Show the names of all technicians and the machines they are assigned to.
assistant
SELECT T3.Name , t1.machine_id 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 the names of all technicians assigned to the machine with ID 2.
assistant
SELECT T3.Name , t1.machine_id 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 AND t1.machine_id = 2;
human
Show the names of all technicians in ascending order of quality rank assigned to the machine with ID 2.
assistant
SELECT T3.Name , t1.machine_id , 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 AND t1.machine_id = 2 ORDER by quality_rank;
human
Show the names of all technicians in ascending order of quality rank assigned to the machine with ID 1.
assistant
SELECT T3.Name , t1.machine_id , 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 AND t1.machine_id = 1 ORDER by quality_rank ;