Database: flight_1
Schema:
CREATE TABLE flight(
	flno number(4,0) primary key,
	origin varchar2(20),
	destination varchar2(20),
	distance number(6,0),
	departure_date date,
	arrival_date date,
	price number(7,2),
    aid number(9,0),
    foreign key("aid") references `aircraft`("aid"))

CREATE TABLE aircraft(
	aid number(9,0) primary key,
	name varchar2(30),
	distance number(6,0))

CREATE TABLE employee(
	eid number(9,0) primary key,
	name varchar2(30),
	salary number(10,2))

CREATE TABLE certificate(
	eid number(9,0),
	aid number(9,0),
	primary key(eid,aid),
	foreign key("eid") references `employee`("eid"),
	foreign key("aid") references `aircraft`("aid"))

Question: How many aircrafts do we have?
SQL: SELECT count(*) FROM Aircraft

Question: How many aircrafts exist in the database?
SQL: SELECT count(*) FROM Aircraft

Question: Show name and distance for all aircrafts.
SQL: SELECT name ,  distance FROM Aircraft

Question: What are the names and distances for all airplanes?
SQL: SELECT name ,  distance FROM Aircraft

Question: Show ids for all aircrafts with more than 1000 distance.
SQL: SELECT aid FROM Aircraft WHERE distance  >  1000

Question: What are the ids of all aircrafts that can cover a distance of more than 1000?
SQL: SELECT aid FROM Aircraft WHERE distance  >  1000

Question: How many aircrafts have distance between 1000 and 5000?
SQL: SELECT count(*) FROM Aircraft WHERE distance BETWEEN 1000 AND 5000

Question: What is the count of aircrafts that have a distance between 1000 and 5000?
SQL: SELECT count(*) FROM Aircraft WHERE distance BETWEEN 1000 AND 5000

Question: What is the name and distance for aircraft with id 12?
SQL: SELECT name ,  distance FROM Aircraft WHERE aid  =  12

Question: What is the name and distance for the aircraft that has an id of 12?
SQL: SELECT name ,  distance FROM Aircraft WHERE aid  =  12

Question: What is the minimum, average, and maximum distance of all aircrafts.
SQL: SELECT min(distance) ,  avg(distance) ,  max(distance) FROM Aircraft

Question: Return the minimum, average and maximum distances traveled across all aircrafts.
SQL: SELECT min(distance) ,  avg(distance) ,  max(distance) FROM Aircraft

Question: Show the id and name of the aircraft with the maximum distance.
SQL: SELECT aid ,  name FROM Aircraft ORDER BY distance DESC LIMIT 1

Question: What is the id and name of the aircraft that can cover the maximum distance?
SQL: SELECT aid ,  name FROM Aircraft ORDER BY distance DESC LIMIT 1

Question: Show the name of aircrafts with top three lowest distances.
SQL: SELECT name FROM Aircraft ORDER BY distance LIMIT 3

Question: What are the aircrafts with top 3 shortest lengthes? List their names.
SQL: SELECT name FROM Aircraft ORDER BY distance LIMIT 3

Question: Show names for all aircrafts with distances more than the average.
SQL: SELECT name FROM Aircraft WHERE distance  >  (SELECT avg(distance) FROM Aircraft)

Question: What are the names of all aircrafts that can cover more distances than average?
SQL: SELECT name FROM Aircraft WHERE distance  >  (SELECT avg(distance) FROM Aircraft)

Question: How many employees do we have?
SQL: SELECT count(*) FROM Employee

Question: What is the number of employees?
SQL: SELECT count(*) FROM Employee

Question: Show name and salary for all employees sorted by salary.
SQL: SELECT name ,  salary FROM Employee ORDER BY salary

Question: What is the name and salary of all employees in order of salary?
SQL: SELECT name ,  salary FROM Employee ORDER BY salary

Question: Show ids for all employees with at least 100000 salary.
SQL: SELECT eid FROM Employee WHERE salary  >  100000

Question: What is the id of every employee who has at least a salary of  100000?
SQL: SELECT eid FROM Employee WHERE salary  >  100000

Question: How many employees have salary between 100000 and 200000?
SQL: SELECT count(*) FROM Employee WHERE salary BETWEEN 100000 AND 200000

Question: What is the number of employees that have a salary between 100000 and 200000?
SQL: SELECT count(*) FROM Employee WHERE salary BETWEEN 100000 AND 200000

Question: What is the name and salary for employee with id 242518965?
SQL: SELECT name ,  salary FROM Employee WHERE eid  =  242518965

Question: What is the name and salary of the employee with the id 242518965?
SQL: SELECT name ,  salary FROM Employee WHERE eid  =  242518965

Question: What is average and maximum salary of all employees.
SQL: SELECT avg(salary) ,  max(salary) FROM Employee

Question: What is the average and largest salary of all employees?
SQL: SELECT avg(salary) ,  max(salary) FROM Employee

Question: Show the id and name of the employee with maximum salary.
SQL: SELECT eid ,  name FROM Employee ORDER BY salary DESC LIMIT 1

Question: What is the id and name of the employee with the highest salary?
SQL: SELECT eid ,  name FROM Employee ORDER BY salary DESC LIMIT 1

Question: Show the name of employees with three lowest salaries.
SQL: SELECT name FROM Employee ORDER BY salary ASC LIMIT 3

Question: What is the name of the 3 employees who get paid the least?
SQL: SELECT name FROM Employee ORDER BY salary ASC LIMIT 3

Question: Show names for all employees with salary more than the average.
SQL: SELECT name FROM Employee WHERE salary  >  (SELECT avg(salary) FROM Employee)

Question: What are the names of all employees who have a salary higher than average?
SQL: SELECT name FROM Employee WHERE salary  >  (SELECT avg(salary) FROM Employee)

Question: Show the id and salary of Mark Young.
SQL: SELECT eid ,  salary FROM Employee WHERE name  =  'Mark Young'

Question: What is the id and salary of the employee named Mark Young?
SQL: SELECT eid ,  salary FROM Employee WHERE name  =  'Mark Young'

Question: How many flights do we have?
SQL: SELECT count(*) FROM Flight

Question: What is the number of flights?
SQL: SELECT count(*) FROM Flight

Question: Show flight number, origin, destination of all flights in the alphabetical order of the departure cities.
SQL: SELECT flno ,  origin ,  destination FROM Flight ORDER BY origin

Question: What is the flight number, origin, and destination for all flights in alphabetical order by departure cities?
SQL: SELECT flno ,  origin ,  destination FROM Flight ORDER BY origin

Question: Show all flight number from Los Angeles.
SQL: SELECT flno FROM Flight WHERE origin  =  "Los Angeles"

Question: What are the numbers of all flights coming from Los Angeles?
SQL: SELECT flno FROM Flight WHERE origin  =  "Los Angeles"

Question: Show origins of all flights with destination Honolulu.
SQL: SELECT origin FROM Flight WHERE destination  =  "Honolulu"

Question: What are the origins of all flights that are headed to Honolulu?
SQL: SELECT origin FROM Flight WHERE destination  =  "Honolulu"

Question: Show me the departure date and arrival date for all flights from Los Angeles to Honolulu.
SQL: SELECT departure_date ,  arrival_date FROM Flight WHERE origin  =  "Los Angeles" AND destination  =  "Honolulu"

Question: What are the departure and arrival dates of all flights from LA to Honolulu?
SQL: SELECT departure_date ,  arrival_date FROM Flight WHERE origin  =  "Los Angeles" AND destination  =  "Honolulu"

Question: Show flight number for all flights with more than 2000 distance.
SQL: SELECT flno FROM Flight WHERE distance  >  2000

Question: What are the numbers of all flights that can cover a distance of more than 2000?
SQL: SELECT flno FROM Flight WHERE distance  >  2000

Question: What is the average price for flights from Los Angeles to Honolulu.
SQL: SELECT avg(price) FROM Flight WHERE origin  =  "Los Angeles" AND destination  =  "Honolulu"

Question: What is the average price for flights from LA to Honolulu?
SQL: SELECT avg(price) FROM Flight WHERE origin  =  "Los Angeles" AND destination  =  "Honolulu"

Question: Show origin and destination for flights with price higher than 300.
SQL: SELECT origin ,  destination FROM Flight WHERE price  >  300

Question: What is the origin and destination for all flights whose price is higher than 300?
SQL: SELECT origin ,  destination FROM Flight WHERE price  >  300

Question: Show the flight number and distance of the flight with maximum price.
SQL: SELECT flno ,  distance FROM Flight ORDER BY price DESC LIMIT 1

Question: What is the flight number and its distance for the one with the maximum price?
SQL: SELECT flno ,  distance FROM Flight ORDER BY price DESC LIMIT 1

Question: Show the flight number of flights with three lowest distances.
SQL: SELECT flno FROM Flight ORDER BY distance ASC LIMIT 3

Question: What are the numbers of the shortest flights?
SQL: SELECT flno FROM Flight ORDER BY distance ASC LIMIT 3

Question: What is the average distance and average price for flights from Los Angeles.
SQL: SELECT avg(distance) ,  avg(price) FROM Flight WHERE origin  =  "Los Angeles"

Question: What is the average distance and price for all flights from LA?
SQL: SELECT avg(distance) ,  avg(price) FROM Flight WHERE origin  =  "Los Angeles"

Question: Show all origins and the number of flights from each origin.
SQL: SELECT origin ,  count(*) FROM Flight GROUP BY origin

Question: For each origin, how many flights came from there?
SQL: SELECT origin ,  count(*) FROM Flight GROUP BY origin

Question: Show all destinations and the number of flights to each destination.
SQL: SELECT destination ,  count(*) FROM Flight GROUP BY destination

Question: What are the destinations and number of flights to each one?
SQL: SELECT destination ,  count(*) FROM Flight GROUP BY destination

Question: Which origin has most number of flights?
SQL: SELECT origin FROM Flight GROUP BY origin ORDER BY count(*) DESC LIMIT 1

Question: What place has the most flights coming from there?
SQL: SELECT origin FROM Flight GROUP BY origin ORDER BY count(*) DESC LIMIT 1

Question: Which destination has least number of flights?
SQL: SELECT destination FROM Flight GROUP BY destination ORDER BY count(*) LIMIT 1

Question: What destination has the fewest number of flights?
SQL: SELECT destination FROM Flight GROUP BY destination ORDER BY count(*) LIMIT 1

Question: What is the aircraft name for the flight with number 99
SQL: SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid WHERE T1.flno  =  99

Question: What is the name of the aircraft that was on flight number 99?
SQL: SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid WHERE T1.flno  =  99

Question: Show all flight numbers with aircraft Airbus A340-300.
SQL: SELECT T1.flno FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid WHERE T2.name  =  "Airbus A340-300"

Question: What are the flight numbers for the aircraft Airbus A340-300?
SQL: SELECT T1.flno FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid WHERE T2.name  =  "Airbus A340-300"

Question: Show aircraft names and number of flights for each aircraft.
SQL: SELECT T2.name ,  count(*) FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid GROUP BY T1.aid

Question: What is the name of each aircraft and how many flights does each one complete?
SQL: SELECT T2.name ,  count(*) FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid GROUP BY T1.aid

Question: Show names for all aircraft with at least two flights.
SQL: SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid GROUP BY T1.aid HAVING count(*)  >= 2

Question: What are the names for all aircrafts with at least 2 flights?
SQL: SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid  =  T2.aid GROUP BY T1.aid HAVING count(*)  >= 2

Question: How many employees have certificate.
SQL: SELECT count(DISTINCT eid) FROM Certificate

Question: What is the count of distinct employees with certificates?
SQL: SELECT count(DISTINCT eid) FROM Certificate

Question: Show ids for all employees who don't have a certificate.
SQL: SELECT eid FROM Employee EXCEPT SELECT eid FROM Certificate

Question: What are the ids of all employees that don't have certificates?
SQL: SELECT eid FROM Employee EXCEPT SELECT eid FROM Certificate

Question: Show names for all aircrafts of which John Williams has certificates.
SQL: SELECT T3.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T1.name  =  "John Williams"

Question: What are the names of all aircrafts that John Williams have certificates to be able to fly?
SQL: SELECT T3.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T1.name  =  "John Williams"

Question: Show names for all employees who have certificate of Boeing 737-800.
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800"

Question: What are the names of all employees who have a certificate to fly Boeing 737-800?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800"

Question: Show names for all employees who have certificates on both Boeing 737-800 and Airbus A340-300.
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800" INTERSECT SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Airbus A340-300"

Question: What are the names of all employees who can fly both the Boeing 737-800 and the Airbus A340-300?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800" INTERSECT SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Airbus A340-300"

Question: Show names for all employees who do not have certificate of Boeing 737-800.
SQL: SELECT name FROM Employee EXCEPT SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800"

Question: What are the names of all employees who are not certified to fly Boeing 737-800s?
SQL: SELECT name FROM Employee EXCEPT SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.name  =  "Boeing 737-800"

Question: Show the name of aircraft which fewest people have its certificate.
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid GROUP BY T1.aid ORDER BY count(*) DESC LIMIT 1

Question: What are the names of the aircraft that the least people are certified to fly?
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid GROUP BY T1.aid ORDER BY count(*) DESC LIMIT 1

Question: Show the name and distance of the aircrafts with more than 5000 distance and which at least 5 people have its certificate.
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid WHERE T2.distance  >  5000 GROUP BY T1.aid ORDER BY count(*)  >=  5

Question: What is the name and distance of every aircraft that can cover a distance of more than 5000 and which at least 5 people can fly?
SQL: SELECT T2.name FROM Certificate AS T1 JOIN Aircraft AS T2 ON T2.aid  =  T1.aid WHERE T2.distance  >  5000 GROUP BY T1.aid ORDER BY count(*)  >=  5

Question: what is the salary and name of the employee who has the most number of aircraft certificates?
SQL: SELECT T1.name ,  T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1

Question: What is the salaray and name of the employee that is certified to fly the most planes?
SQL: SELECT T1.name ,  T1.salary FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1

Question: What is the salary and name of the employee who has the most number of certificates on aircrafts with distance more than 5000?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.distance  >  5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1

Question: What is the salaray and name of the employee with the most certificates to fly planes more than 5000?
SQL: SELECT T1.name FROM Employee AS T1 JOIN Certificate AS T2 ON T1.eid  =  T2.eid JOIN Aircraft AS T3 ON T3.aid  =  T2.aid WHERE T3.distance  >  5000 GROUP BY T1.eid ORDER BY count(*) DESC LIMIT 1