Database: flight_company

CREATE TABLE "airport" (
"id" int,
"City" text,
"Country" text,
"IATA" text,
"ICAO" text,
"name" text,
primary key("id")
);
CREATE TABLE "operate_company" (
"id" int,
"name" text,
"Type" text,
"Principal_activities" text,
"Incorporated_in" text,
"Group_Equity_Shareholding" real,
primary key ("id")
);
CREATE TABLE "flight" (
"id" int,
"Vehicle_Flight_number" text,
"Date" text,
"Pilot" text,
"Velocity" real,
"Altitude" real,
"airport_id" int,
"company_id" int,
primary key ("id"),
foreign key ("airport_id") references `airport`("id"),
foreign key ("company_id") references `operate_company`("id")
);

Question: How many flights have a velocity larger than 200?
SQL: SELECT count(*) FROM flight WHERE velocity  >  200

Question: List the vehicle flight number, date and pilot of all the flights, ordered by altitude.
SQL: SELECT vehicle_flight_number ,  date ,  pilot FROM flight ORDER BY altitude ASC

Question: List the id, country, city and name of the airports ordered alphabetically by the name.
SQL: SELECT id ,  country ,  city ,  name FROM airport ORDER BY name

Question: What is maximum group equity shareholding of the companies?
SQL: SELECT max(group_equity_shareholding) FROM operate_company

Question: What is the velocity of the pilot named 'Thompson'?
SQL: SELECT avg(velocity) FROM flight WHERE pilot  =  'Thompson'

Question: What are the names and types of the companies that have ever operated a flight?
SQL: SELECT T1.name ,  T1.type FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id

Question: What are the names of the airports which are not in the country 'Iceland'?
SQL: SELECT name FROM airport WHERE country != 'Iceland'

Question: What are the distinct types of the companies that have operated any flights with velocity less than 200?
SQL: SELECT DISTINCT T1.type FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id WHERE T2.velocity  <  200

Question: What are the ids and names of the companies that operated more than one flight?
SQL: SELECT T1.id ,  T1.name FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id GROUP BY T1.id HAVING count(*)  >  1

Question: What is the id, name and IATA code of the airport that had most number of flights?
SQL: SELECT T1.id ,  T1.name ,  T1.IATA FROM airport AS T1 JOIN flight AS T2 ON T1.id  =  T2.airport_id GROUP BY T2.id ORDER BY count(*) DESC LIMIT 1

Question: What are the different pilot names who had piloted a flight in the country 'United States' or in the airport named 'Billund Airport'?
SQL: SELECT DISTINCT T2.pilot FROM airport AS T1 JOIN flight AS T2 ON T1.id  =  T2.airport_id WHERE T1.country  =  'United States' OR T1.name  =  'Billund Airport'

Question: What is the most common company type, and how many are there?
SQL: SELECT TYPE ,  count(*) FROM operate_company GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1

Question: How many airports haven't the pilot 'Thompson' driven an aircraft?
SQL: SELECT count(*) FROM airport WHERE id NOT IN ( SELECT airport_id FROM flight WHERE pilot  =  'Thompson' );

Question: List the name of the pilots who have flied for both a company that mainly provide 'Cargo' services and a company that runs 'Catering services' activities.
SQL: SELECT T2.pilot FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id WHERE T1.principal_activities  =  'Cargo' INTERSECT SELECT T2.pilot FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id WHERE T1.principal_activities  =  'Catering services'

Question: Which of the airport names contains the word 'international'?
SQL: SELECT name FROM airport WHERE name LIKE '%international%'

Question: How many companies operates airlines in each airport?
SQL: SELECT T3.id ,  count(*) FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id JOIN airport AS T3 ON T2.airport_id  =  T3.id GROUP BY T3.id

Question: how many airports are there in each country?
SQL: SELECT count(*) ,  country FROM airport GROUP BY country

Question: which countries have more than 2 airports?
SQL: SELECT country FROM airport GROUP BY country HAVING count(*)  >  2

Question: which pilot is in charge of the most number of flights?
SQL: SELECT pilot FROM flight GROUP BY pilot ORDER BY count(*) DESC LIMIT 1