lianghsun/spider-text2sql-bench huggingface dataset:
### Database 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
What are the names and distances for all airplanes?

### SQL
SELECT name ,  distance FROM Aircraft
### Database 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
Show ids for all aircrafts with more than 1000 distance.

### SQL
SELECT aid FROM Aircraft WHERE distance  >  1000
### Database 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
What are the ids of all aircrafts that can cover a distance of more than 1000?

### SQL
SELECT aid FROM Aircraft WHERE distance  >  1000
### Database 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 have distance between 1000 and 5000?

### SQL
SELECT count(*) FROM Aircraft WHERE distance BETWEEN 1000 AND 5000
### Database 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
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
### Database 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
What is the name and distance for aircraft with id 12?

### SQL
SELECT name ,  distance FROM Aircraft WHERE aid  =  12
### Database 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
What is the name and distance for the aircraft that has an id of 12?

### SQL
SELECT name ,  distance FROM Aircraft WHERE aid  =  12
### Database 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
What is the minimum, average, and maximum distance of all aircrafts.

### SQL
SELECT min(distance) ,  avg(distance) ,  max(distance) FROM Aircraft
### Database 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
Return the minimum, average and maximum distances traveled across all aircrafts.

### SQL
SELECT min(distance) ,  avg(distance) ,  max(distance) FROM Aircraft
### Database 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
Show the id and name of the aircraft with the maximum distance.

### SQL
SELECT aid ,  name FROM Aircraft ORDER BY distance DESC LIMIT 1
### Database 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
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
### Database 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
Show the name of aircrafts with top three lowest distances.

### SQL
SELECT name FROM Aircraft ORDER BY distance LIMIT 3
### Database 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
What are the aircrafts with top 3 shortest lengthes? List their names.

### SQL
SELECT name FROM Aircraft ORDER BY distance LIMIT 3
### Database 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
Show names for all aircrafts with distances more than the average.

### SQL
SELECT name FROM Aircraft WHERE distance  >  (SELECT avg(distance) FROM Aircraft)
### Database 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
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)
### Database 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 employees do we have?

### SQL
SELECT count(*) FROM Employee
### Database 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
What is the number of employees?

### SQL
SELECT count(*) FROM Employee
### Database 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
Show name and salary for all employees sorted by salary.

### SQL
SELECT name ,  salary FROM Employee ORDER BY salary
### Database 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
What is the name and salary of all employees in order of salary?

### SQL
SELECT name ,  salary FROM Employee ORDER BY salary
### Database 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
Show ids for all employees with at least 100000 salary.

### SQL
SELECT eid FROM Employee WHERE salary  >  100000
### Database 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
What is the id of every employee who has at least a salary of  100000?

### SQL
SELECT eid FROM Employee WHERE salary  >  100000
### Database 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 employees have salary between 100000 and 200000?

### SQL
SELECT count(*) FROM Employee WHERE salary BETWEEN 100000 AND 200000
### Database 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
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
### Database 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
What is the name and salary for employee with id 242518965?

### SQL
SELECT name ,  salary FROM Employee WHERE eid  =  242518965
### Database 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
What is the name and salary of the employee with the id 242518965?

### SQL
SELECT name ,  salary FROM Employee WHERE eid  =  242518965
### Database 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
What is average and maximum salary of all employees.

### SQL
SELECT avg(salary) ,  max(salary) FROM Employee
### Database 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
What is the average and largest salary of all employees?

### SQL
SELECT avg(salary) ,  max(salary) FROM Employee
### Database 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
Show the id and name of the employee with maximum salary.

### SQL
SELECT eid ,  name FROM Employee ORDER BY salary DESC LIMIT 1
### Database 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
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
### Database 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
Show the name of employees with three lowest salaries.

### SQL
SELECT name FROM Employee ORDER BY salary ASC LIMIT 3
### Database 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
What is the name of the 3 employees who get paid the least?

### SQL
SELECT name FROM Employee ORDER BY salary ASC LIMIT 3
### Database 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
Show names for all employees with salary more than the average.

### SQL
SELECT name FROM Employee WHERE salary  >  (SELECT avg(salary) FROM Employee)
### Database 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
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)
### Database 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
Show the id and salary of Mark Young.

### SQL
SELECT eid ,  salary FROM Employee WHERE name  =  'Mark Young'
### Database 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
What is the id and salary of the employee named Mark Young?

### SQL
SELECT eid ,  salary FROM Employee WHERE name  =  'Mark Young'
### Database 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 flights do we have?

### SQL
SELECT count(*) FROM Flight
### Database 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
What is the number of flights?

### SQL
SELECT count(*) FROM Flight
### Database 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
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
### Database 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
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
### Database 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
Show all flight number from Los Angeles.

### SQL
SELECT flno FROM Flight WHERE origin  =  "Los Angeles"
### Database 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
What are the numbers of all flights coming from Los Angeles?

### SQL
SELECT flno FROM Flight WHERE origin  =  "Los Angeles"
### Database 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
Show origins of all flights with destination Honolulu.

### SQL
SELECT origin FROM Flight WHERE destination  =  "Honolulu"
### Database 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
What are the origins of all flights that are headed to Honolulu?

### SQL
SELECT origin FROM Flight WHERE destination  =  "Honolulu"
### Database 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
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"
### Database 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
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"
### Database 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
Show flight number for all flights with more than 2000 distance.

### SQL
SELECT flno FROM Flight WHERE distance  >  2000
### Database 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
What are the numbers of all flights that can cover a distance of more than 2000?

### SQL
SELECT flno FROM Flight WHERE distance  >  2000
### Database 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
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"
### Database 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
What is the average price for flights from LA to Honolulu?

### SQL
SELECT avg(price) FROM Flight WHERE origin  =  "Los Angeles" AND destination  =  "Honolulu"
### Database 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
Show origin and destination for flights with price higher than 300.

### SQL
SELECT origin ,  destination FROM Flight WHERE price  >  300
### Database 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
What is the origin and destination for all flights whose price is higher than 300?

### SQL
SELECT origin ,  destination FROM Flight WHERE price  >  300
### Database 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
Show the flight number and distance of the flight with maximum price.

### SQL
SELECT flno ,  distance FROM Flight ORDER BY price DESC LIMIT 1
### Database 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
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
### Database 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
Show the flight number of flights with three lowest distances.

### SQL
SELECT flno FROM Flight ORDER BY distance ASC LIMIT 3
### Database 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
What are the numbers of the shortest flights?

### SQL
SELECT flno FROM Flight ORDER BY distance ASC LIMIT 3
### Database 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
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"
### Database 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
What is the average distance and price for all flights from LA?

### SQL
SELECT avg(distance) ,  avg(price) FROM Flight WHERE origin  =  "Los Angeles"
### Database 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
Show all origins and the number of flights from each origin.

### SQL
SELECT origin ,  count(*) FROM Flight GROUP BY origin
### Database 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
For each origin, how many flights came from there?

### SQL
SELECT origin ,  count(*) FROM Flight GROUP BY origin
### Database 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
Show all destinations and the number of flights to each destination.

### SQL
SELECT destination ,  count(*) FROM Flight GROUP BY destination