Database: machine_repair

CREATE TABLE "repair" (
"repair_ID" int,
"name" text,
"Launch_Date" text,
"Notes" text,
PRIMARY KEY ("repair_ID")
);
CREATE TABLE "machine" (
"Machine_ID" int,
"Making_Year" int,
"Class" text,
"Team" text,
"Machine_series" text,
"value_points" real,
"quality_rank" int,
PRIMARY KEY ("Machine_ID")
);
CREATE TABLE "technician" (
"technician_id" real,
"Name" text,
"Team" text,
"Starting_Year" real,
"Age" int,
PRIMARY Key ("technician_id")
);
CREATE TABLE "repair_assignment" (
"technician_id" int,
"repair_ID" int,
"Machine_ID" int,
PRIMARY Key ("technician_id","repair_ID","Machine_ID"),
FOREIGN KEY (`technician_id`) REFERENCES `technician`(`technician_id`),
FOREIGN KEY (`repair_ID`) REFERENCES `repair`(`repair_ID`),
FOREIGN KEY (`Machine_ID`) REFERENCES `machine`(`Machine_ID`)
);

Question: How many technicians are there?
SQL: SELECT count(*) FROM technician

Question: What is the number of technicians?
SQL: SELECT count(*) FROM technician

Question: List the names of technicians in ascending order of age.
SQL: SELECT Name FROM technician ORDER BY Age ASC

Question: What are the names of the technicians by ascending order of age?
SQL: SELECT Name FROM technician ORDER BY Age ASC

Question: What are the team and starting year of technicians?
SQL: SELECT Team ,  Starting_Year FROM technician

Question: What is the team and starting year for each technician?
SQL: SELECT Team ,  Starting_Year FROM technician

Question: List the name of technicians whose team is not "NYY".
SQL: SELECT Name FROM technician WHERE Team != "NYY"

Question: What is the name of the technician whose team is not 'NYY'?
SQL: SELECT Name FROM technician WHERE Team != "NYY"

Question: Show the name of technicians aged either 36 or 37
SQL: SELECT Name FROM technician WHERE Age  =  36 OR Age  =  37

Question: What are the names of the technicians aged either 36 or 37?
SQL: SELECT Name FROM technician WHERE Age  =  36 OR Age  =  37

Question: What is the starting year of the oldest technicians?
SQL: SELECT Starting_Year FROM technician ORDER BY Age DESC LIMIT 1

Question: What is the starting year for the oldest technician?
SQL: SELECT Starting_Year FROM technician ORDER BY Age DESC LIMIT 1

Question: Show different teams of technicians and the number of technicians in each team.
SQL: SELECT Team ,  COUNT(*) FROM technician GROUP BY Team

Question: For each team, how many technicians are there?
SQL: SELECT Team ,  COUNT(*) FROM technician GROUP BY Team

Question: Please show the team that has the most number of technicians.
SQL: SELECT Team FROM technician GROUP BY Team ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the teams with the most technicians?
SQL: SELECT Team FROM technician GROUP BY Team ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the team that have at least two technicians.
SQL: SELECT Team FROM technician GROUP BY Team HAVING COUNT(*)  >=  2

Question: What is the team with at least 2 technicians?
SQL: SELECT Team FROM technician GROUP BY Team HAVING COUNT(*)  >=  2

Question: Show names of technicians and series of machines they are assigned to repair.
SQL: 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

Question: What are the names of technicians and the machine series that they repair?
SQL: 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

Question: Show names of technicians in ascending order of quality rank of the machine they are assigned.
SQL: 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

Question: What are the names of the technicians by ascending order of quality rank for the machine they are assigned?
SQL: 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

Question: Show names of technicians who are assigned to repair machines with value point more than 70.
SQL: 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

Question: What are the names of the technicians that are assigned to repair machines with more point values than 70?
SQL: 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

Question: Show names of technicians and the number of machines they are assigned to repair.
SQL: SELECT T2.Name ,  COUNT(*) FROM repair_assignment AS T1 JOIN technician AS T2 ON T1.technician_ID  =  T2.technician_ID GROUP BY T2.Name

Question: What are the names of the technicians  and how many machines are they assigned to repair?
SQL: SELECT T2.Name ,  COUNT(*) FROM repair_assignment AS T1 JOIN technician AS T2 ON T1.technician_ID  =  T2.technician_ID GROUP BY T2.Name

Question: List the names of technicians who have not been assigned to repair machines.
SQL: SELECT Name FROM technician WHERE technician_id NOT IN (SELECT technician_id FROM repair_assignment)

Question: What are the names of the technicians that have not been assigned to repair machines?
SQL: SELECT Name FROM technician WHERE technician_id NOT IN (SELECT technician_id FROM repair_assignment)

Question: Show the starting years shared by technicians from team "CLE" and "CWS".
SQL: SELECT Starting_Year FROM technician WHERE Team  =  "CLE" INTERSECT SELECT Starting_Year FROM technician WHERE Team  =  "CWS"

Question: What are the starting years shared by the technicians from the team "CLE" or "CWS"?
SQL: SELECT Starting_Year FROM technician WHERE Team  =  "CLE" INTERSECT SELECT Starting_Year FROM technician WHERE Team  =  "CWS"