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 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
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
How many technicians are there?
assistant
SELECT count(*) FROM technician
human
What is the starting year of the oldest technicians?
assistant
SELECT Starting_Year FROM technician ORDER BY Age DESC LIMIT 1
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 aged either 36 or 37?
assistant
SELECT Name FROM technician WHERE Age = 36 OR Age = 37 ;