Database: flight_1

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