human
Schema:
airport (id, City, Country, IATA, ICAO, name)
operate_company (id, name, Type, Principal_activities, Incorporated_in, Group_Equity_Shareholding)
flight (id, Vehicle_Flight_number, Date, Pilot, Velocity, Altitude, airport_id, company_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Show me the names of all the pilots
assistant
SELECT pilot FROM flight;
human
Which of these pilots flied for a company that runs 'Cargo' activities?
assistant
SELECT * FROM flight AS t1 JOIN operate_company AS t2 ON t1.company_id = t2.id WHERE principal_activities = 'Cargo';
human
Which pilots flied for a company that runs 'Catering services' activities?
assistant
SELECT * FROM flight AS t1 JOIN operate_company AS t2 ON t1.company_id = t2.id WHERE principal_activities = "Catering services" ;