human
Schema:
flight (flno, origin, destination, distance, departure_date, arrival_date, price, aid)
aircraft (aid, name, distance)
employee (eid, name, salary)
certificate (eid, aid)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the name and distance for aircraft with id 12?
assistant
SELECT name , distance FROM Aircraft WHERE aid = 12
human
How many aircrafts have distance between 1000 and 5000?
assistant
SELECT count(*) FROM Aircraft WHERE distance BETWEEN 1000 AND 5000
human
Show names for all aircraft with at least two flights.
assistant
SELECT T2.name FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid = T2.aid GROUP BY T1.aid HAVING count(*) >= 2
human
How many flights do we have?
assistant
SELECT count(*) FROM Flight
human
Show aircraft names and number of flights for each aircraft.
assistant
SELECT T2.name , count(*) FROM Flight AS T1 JOIN Aircraft AS T2 ON T1.aid = T2.aid GROUP BY T1.aid
human
How many aircrafts exist in the database?
assistant
SELECT COUNT( * ) FROM Aircraft ;