NBAmine/xlangai-spider-test-with-context huggingface dataset:
Database schema:
CREATE TABLE bike (
  id NUMBER PRIMARY KEY,
  product_name TEXT,
  weight NUMBER,
  price NUMBER,
  material TEXT
);
CREATE TABLE cyclist (
  id NUMBER PRIMARY KEY,
  heat NUMBER,
  name TEXT,
  nation TEXT,
  result NUMBER
);
CREATE TABLE cyclists_own_bikes (
  cyclist_id NUMBER PRIMARY KEY,
  bike_id NUMBER,
  purchase_year NUMBER,
  FOREIGN KEY (bike_id) REFERENCES bike(id),
  FOREIGN KEY (cyclist_id) REFERENCES cyclist(id)
);

Question: What are the id and name of the cyclist who owns the most bikes?
SQL: SELECT T1.id ,  T1.name FROM cyclist AS T1 JOIN cyclists_own_bikes AS T2 ON T1.id  =  T2.cyclist_id GROUP BY T1.id ORDER BY count(*) DESC LIMIT 1

Question: List the name and price of the bike that is owned by both the cyclists named 'Bradley Wiggins' and the cyclist named 'Antonio Tauler'.
SQL: SELECT T3.product_name ,  T3.price FROM cyclist AS T1 JOIN cyclists_own_bikes AS T2 ON T1.id  =  T2.cyclist_id JOIN bike AS T3 ON T2.bike_id  =  T3.id WHERE T1.name  =  'Bradley Wiggins' INTERSECT SELECT T3.product_name ,  T3.price FROM cyclist AS T1 JOIN cyclists_own_bikes AS T2 ON T1.id  =  T2.cyclist_id JOIN bike AS T3 ON T2.bike_id  =  T3.id WHERE T1.name  =  'Antonio Tauler'

Question: What are the maximum and minimum weight of all bikes?
SQL: SELECT max(weight) ,  min(weight) FROM bike

Question: How many cyclists did not purchase any bike after year 2015?
SQL: SELECT count(*) FROM cyclist WHERE id NOT IN ( SELECT cyclist_id FROM cyclists_own_bikes WHERE purchase_year  >  2015 )

Question: List the heat, name, and nation for all the cyclists.
SQL: SELECT heat ,  name ,  nation FROM cyclist

Question: What are the distinct product names of bikes owned by cyclists from 'Russia' or cyclists from 'Great Britain'?
SQL: SELECT DISTINCT T3.product_name FROM cyclist AS T1 JOIN cyclists_own_bikes AS T2 ON T1.id  =  T2.cyclist_id JOIN bike AS T3 ON T2.bike_id  =  T3.id WHERE T1.nation  =  'Russia' OR T1.nation  =  'Great Britain'

Question: What are the names of distinct racing bikes that are purchased by the cyclists with better results than '4:21.558' ?
SQL: SELECT DISTINCT T3.product_name FROM cyclist AS T1 JOIN cyclists_own_bikes AS T2 ON T1.id  =  T2.cyclist_id JOIN bike AS T3 ON T2.bike_id  =  T3.id WHERE T1.result  <  '4:21.558'

Question: What are the names of the bikes that have substring 'fiber' in their material?
SQL: SELECT product_name FROM bike WHERE material LIKE "%fiber%"

Question: How many bikes are heavier than 780 grams?
SQL: SELECT count(*) FROM bike WHERE weight  >  780

Question: What are the distinct ids and product names of the bikes that are purchased after year 2015?
SQL: SELECT DISTINCT T1.id ,  T1.product_name FROM bike AS T1 JOIN cyclists_own_bikes AS T2 ON T1.id  =  T2.bike_id WHERE T2.purchase_year  >  2015

Question: How many bikes does each cyclist own? Order by cyclist id.
SQL: SELECT cyclist_id ,  count(*) FROM cyclists_own_bikes GROUP BY cyclist_id ORDER BY cyclist_id
Database schema:
CREATE TABLE book (
  Book_ID NUMBER PRIMARY KEY,
  Title TEXT,
  Type TEXT,
  Pages NUMBER,
  Chapters NUMBER,
  Audio TEXT,
  Release TEXT
);
CREATE TABLE review (
  Review_ID NUMBER PRIMARY KEY,
  Book_ID NUMBER,
  Rating NUMBER,
  Readers_in_Million NUMBER,
  Rank NUMBER,
  FOREIGN KEY (Book_ID) REFERENCES book(Book_ID)
);

Question: What are the maximum and minimum number of chapters for each book?
SQL: SELECT max(Chapters) ,  min(Chapters) FROM book

Question: How many books are there?
SQL: SELECT count(*) FROM book

Question: What is the average rating in reviews?
SQL: SELECT avg(Rating) FROM review

Question: What are the types and release dates of books?
SQL: SELECT TYPE ,  Release FROM book

Question: What is the title of the book with the highest rank in the review?
SQL: SELECT T1.Title FROM book AS T1 JOIN review AS T2 ON T1.Book_ID  =  T2.Book_ID ORDER BY T2.Rank LIMIT 1

Question: What is the average number of readers for books of type "Novel"?
SQL: SELECT avg(T2.Readers_in_Million) FROM book AS T1 JOIN review AS T2 ON T1.Book_ID  =  T2.Book_ID WHERE T1.Type  =  "Novel"

Question: How many distinct types of book are there?
SQL: SELECT count(DISTINCT TYPE) FROM book

Question: Show the types of books that have both books with more than 75 chapters and books with less than 50 chapters.
SQL: SELECT TYPE FROM book WHERE Chapters  >  75 INTERSECT SELECT TYPE FROM book WHERE Chapters  <  50

Question: List the titles of books in ascending alphabetical order.
SQL: SELECT Title FROM book ORDER BY Title ASC

Question: List the titles of books in ascending order of the ratings in review?
SQL: SELECT T1.Title FROM book AS T1 JOIN review AS T2 ON T1.Book_ID  =  T2.Book_ID ORDER BY T2.Rating ASC

Question: For each book type return the type and the number of books of that type.
SQL: SELECT TYPE ,  COUNT(*) FROM book GROUP BY TYPE

Question: What is the most common type of books?
SQL: SELECT TYPE FROM book GROUP BY TYPE ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the rating of the book with the largest number of chapters?
SQL: SELECT T2.Rating FROM book AS T1 JOIN review AS T2 ON T1.Book_ID  =  T2.Book_ID ORDER BY T1.Chapters DESC LIMIT 1

Question: List the title and audio length for all the books in descending order of the number of readers.
SQL: SELECT T1.Title ,  T1.audio FROM book AS T1 JOIN review AS T2 ON T1.Book_ID  =  T2.Book_ID ORDER BY T2.Readers_in_Million DESC

Question: What are the titles and ratings of books?
SQL: SELECT T1.Title ,  T2.Rating FROM book AS T1 JOIN review AS T2 ON T1.Book_ID  =  T2.Book_ID

Question: What are the types of books that have at least three books belonging to?
SQL: SELECT TYPE FROM book GROUP BY TYPE HAVING COUNT(*)  >=  3

Question: How many books do not have reviews?
SQL: SELECT count(*) FROM book WHERE Book_ID NOT IN (SELECT Book_ID FROM review)
Database schema:
CREATE TABLE channel (
  Channel_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Analogue_terrestrial_channel TEXT,
  Digital_terrestrial_channel TEXT,
  Internet TEXT
);
CREATE TABLE director (
  Director_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Age NUMBER
);
CREATE TABLE program (
  Program_ID NUMBER PRIMARY KEY,
  Start_Year NUMBER,
  Title TEXT,
  Director_ID NUMBER,
  Channel_ID NUMBER,
  FOREIGN KEY (Channel_ID) REFERENCES channel(Channel_ID),
  FOREIGN KEY (Director_ID) REFERENCES director(Director_ID)
);
CREATE TABLE director_admin (
  Director_ID NUMBER PRIMARY KEY,
  Channel_ID NUMBER,
  Is_first_director OTHERS,
  FOREIGN KEY (Channel_ID) REFERENCES channel(Channel_ID),
  FOREIGN KEY (Director_ID) REFERENCES director(Director_ID)
);

Question: How many different digital terrestrial channels are there?
SQL: SELECT count(DISTINCT Digital_terrestrial_channel) FROM channel

Question: Find the the name of the oldest director.
SQL: SELECT name FROM director ORDER BY age DESC LIMIT 1

Question: Find the number of programs for each channel. Return the name of each channel as well.
SQL: SELECT t1.name ,  count(*) FROM channel AS t1 JOIN program AS t2 ON t1.channel_id  =  t2.channel_id GROUP BY t1.channel_id

Question: Which director is in charge of the most programs?
SQL: SELECT t2.name FROM program AS t1 JOIN director AS t2 ON t1.director_id  =  t2.director_id GROUP BY t1.director_id ORDER BY count(*) DESC LIMIT 1

Question: give me the name of channels that have both a director younger than 40 and a director older than 60.
SQL: SELECT t1.name FROM channel AS t1 JOIN director_admin AS t2 ON t1.channel_id  =  t2.channel_id JOIN director AS t3 ON t2.director_id  =  t3.director_id WHERE t3.age  <  40 INTERSECT SELECT t1.name FROM channel AS t1 JOIN director_admin AS t2 ON t1.channel_id  =  t2.channel_id JOIN director AS t3 ON t2.director_id  =  t3.director_id WHERE t3.age  >  60

Question: Find the id and name of the channel that is not directed by Hank Baskett.
SQL: SELECT t1.name ,  t1.channel_id FROM channel AS t1 JOIN director_admin AS t2 ON t1.channel_id  =  t2.channel_id JOIN director AS t3 ON t2.director_id  =  t3.director_id WHERE t3.name != "Hank Baskett"

Question: List all program titles in the order of starting year. List the most recent one first.
SQL: SELECT title FROM program ORDER BY start_year DESC

Question: Find the number of channels that do not run any program.
SQL: SELECT count(*) FROM channel WHERE channel_id NOT IN (SELECT channel_id FROM program)
Database schema:
CREATE TABLE member (
  Member_ID NUMBER PRIMARY KEY,
  Name TEXT,
  Nationality TEXT,
  Age NUMBER
);
CREATE TABLE club (
  Club_ID NUMBER PRIMARY KEY,
  Overall_Ranking NUMBER,
  Team_Leader TEXT,
  Club_Name TEXT
);
CREATE TABLE club_leader (
  Club_ID NUMBER PRIMARY KEY,
  Member_ID NUMBER,
  Year_Join TEXT,
  FOREIGN KEY (Member_ID) REFERENCES member(Member_ID),
  FOREIGN KEY (Club_ID) REFERENCES club(Club_ID)
);

Question: Show the names of club leaders and the names of clubs they joined.
SQL: SELECT T3.Name ,  T2.Club_Name FROM club_leader AS T1 JOIN club AS T2 ON T1.Club_ID  =  T2.Club_ID JOIN member AS T3 ON T1.Member_ID  =  T3.Member_ID

Question: Show different nationalities along with the number of members of each nationality.
SQL: SELECT Nationality ,  COUNT(*) FROM member GROUP BY Nationality

Question: List the names of members whose nationality is not `` England '' .
SQL: select name from member where nationality != "england"

Question: Show the nations that have both members older than 22 and members younger than 19.
SQL: SELECT Nationality FROM member WHERE Age  >  22 INTERSECT SELECT Nationality FROM member WHERE Age  <  19

Question: What is the average age of all the club leaders?
SQL: SELECT avg(T2.age) FROM club_leader AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id

Question: Please show the most common nationality of members.
SQL: SELECT Nationality ,  COUNT(*) FROM member GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the nations that have at least two members.
SQL: SELECT Nationality FROM member GROUP BY Nationality HAVING COUNT(*)  >=  2

Question: Show the names of club leaders of clubs with overall ranking higher than 100.
SQL: SELECT T3.Name ,  T2.Club_Name FROM club_leader AS T1 JOIN club AS T2 ON T1.Club_ID  =  T2.Club_ID JOIN member AS T3 ON T1.Member_ID  =  T3.Member_ID WHERE T2.Overall_Ranking  <  100

Question: What are the names and nationalities of the members?
SQL: SELECT Name ,  Nationality FROM member

Question: Show the names of members whose age is either 19 or 20.
SQL: SELECT Name FROM member WHERE Age  =  19 OR Age  =  20

Question: List the names of members in ascending order of age.
SQL: SELECT Name FROM member ORDER BY Age ASC
Database schema:
CREATE TABLE Sailors (
  sid NUMBER PRIMARY KEY,
  name TEXT,
  rating NUMBER,
  age NUMBER
);
CREATE TABLE Boats (
  bid NUMBER PRIMARY KEY,
  name TEXT,
  color TEXT
);
CREATE TABLE Reserves (
  sid NUMBER,
  bid NUMBER,
  day TEXT,
  FOREIGN KEY (bid) REFERENCES Boats(bid),
  FOREIGN KEY (sid) REFERENCES Sailors(sid)
);

Question: Find the name, rating and age of all sailors ordered by rating and age.
SQL: SELECT name ,  rating ,  age FROM Sailors ORDER BY rating ,  age

Question: Who reserved boat 102?
SQL: SELECT T1.name FROM Sailors AS T1 JOIN Reserves AS T2 ON T1.sid  =  T2.sid WHERE T2.bid  =  102

Question: Return all details of sailors who are older than 30.
SQL: SELECT * FROM Sailors WHERE age  >  30

Question: How many sailors whose name starts with letter D exist ?
SQL: select count(*) from sailors where name like 'd%'

Question: Find the number of reservations by sailors with id greater than 1 for each boat.
SQL: SELECT bid ,  count(*) FROM Reserves WHERE sid  >  1 GROUP BY bid

Question: What is highest rating between sailors?
SQL: SELECT max(rating) FROM Sailors

Question: What are the rating and average age for sailors who reserved red boats for each rating?
SQL: SELECT T1.rating ,  avg(T1.age) FROM Sailors AS T1 JOIN Reserves AS T2 ON T1.sid  =  T2.sid JOIN Boats AS T3 ON T3.bid  =  T2.bid WHERE T3.color  =  'red' GROUP BY T1.rating

Question: Find the total number of boats.
SQL: SELECT count(*) FROM Boats

Question: What are the sailors' names, the ones whose rating is higher than any sailor named Luis?
SQL: SELECT name FROM Sailors WHERE rating  >  (SELECT min(rating) FROM Sailors WHERE name  =  'Luis')

Question: What are the names and ids of all sailors who do not have boat reservations?
SQL: SELECT sid ,  name FROM Sailors EXCEPT SELECT T1.sid ,  T1.name FROM Sailors AS T1 JOIN Reserves AS T2 ON T1.sid  =  T2.sid

Question: What are the names and ages of all sailors sorted by decreasing rating?
SQL: SELECT name ,  age FROM Sailors ORDER BY rating DESC

Question: Find id for the sailors who do not have a reservation of a boat?
SQL: SELECT sid FROM Sailors EXCEPT SELECT T1.sid FROM Sailors AS T1 JOIN Reserves AS T2 ON T1.sid  =  T2.sid

Question: What are the names and ids of sailors who reserved red and blue boats?
SQL: SELECT DISTINCT T2.sid ,  T3.name FROM Boats AS T1 JOIN Reserves AS T2 ON  T1.bid  =  T2.bid JOIN Sailors AS T3 ON T2.sid  =  T3.sid WHERE T1.color  =  'red' INTERSECT SELECT DISTINCT T2.sid ,  T3.name FROM Boats AS T1 JOIN Reserves AS T2 ON  T1.bid  =  T2.bid JOIN Sailors AS T3 ON T2.sid  =  T3.sid WHERE T1.color  =  "blue"

Question: What is the name and id of every sailor who reserved one or more boats?
SQL: SELECT DISTINCT T1.name ,  T1.sid FROM Sailors AS T1 JOIN Reserves AS T2 ON T1.sid  =  T2.sid

Question: What is the maximum rating for sailors?
SQL: SELECT max(rating) FROM Sailors
Database schema:
CREATE TABLE Services (
  Service_ID NUMBER PRIMARY KEY,
  Service_Details TEXT
);
CREATE TABLE Customers (
  Customer_ID NUMBER PRIMARY KEY,
  Customer_Details TEXT
);
CREATE TABLE Channels (
  Channel_ID NUMBER PRIMARY KEY,
  Channel_Details TEXT
);
CREATE TABLE Customers_and_Services (
  Customers_and_Services_ID NUMBER PRIMARY KEY,
  Customer_ID NUMBER,
  Service_ID NUMBER,
  Customers_and_Services_Details TEXT,
  FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
  FOREIGN KEY (Service_ID) REFERENCES Services(Service_ID)
);
CREATE TABLE Customer_Interactions (
  Customer_Interaction_ID NUMBER PRIMARY KEY,
  Channel_ID NUMBER,
  Customer_ID NUMBER,
  Service_ID NUMBER,
  Status_Code TEXT,
  Services_and_Channels_Details TEXT,
  FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
  FOREIGN KEY (Channel_ID) REFERENCES Channels(Channel_ID),
  FOREIGN KEY (Service_ID) REFERENCES Services(Service_ID)
);
CREATE TABLE Integration_Platform (
  Integration_Platform_ID NUMBER PRIMARY KEY,
  Customer_Interaction_ID NUMBER,
  Integration_Platform_Details TEXT,
  FOREIGN KEY (Customer_Interaction_ID) REFERENCES Customer_Interactions(Customer_Interaction_ID)
);
CREATE TABLE Analytical_Layer (
  Analytical_ID NUMBER PRIMARY KEY,
  Customers_and_Services_ID TEXT,
  Pattern_Recognition TEXT,
  Analytical_Layer_Type_Code TEXT,
  FOREIGN KEY (Customers_and_Services_ID) REFERENCES Customers_and_Services(Customers_and_Services_ID)
);

Question: What is the most common analytical layer type code?
SQL: SELECT analytical_layer_type_code FROM analytical_layer GROUP BY analytical_layer_type_code ORDER BY count(*) DESC LIMIT 1

Question: Find the names of all the services which both have been used by customer "Hardy Kutch" and have been rated "bad" in one of the customer interactions.
SQL: SELECT DISTINCT t3.service_details FROM customers AS t1 JOIN customers_and_services AS t2 ON t1.customer_id  =  t2.customer_id JOIN services AS t3 ON t2.service_id  =  t3.service_id JOIN customer_interactions AS t4 ON t3.service_id  =  t4.service_id WHERE t1.customer_details  =  "Hardy Kutch" AND t4.services_and_channels_details  =  "bad"

Question: Which customers never used any services ? Give me the customer details .
SQL: select customer_details from customers where customer_id not in (select customer_id from customers_and_services)

Question: Find the details of the customers who have used the least-used service .
SQL: select distinct t1.customer_details from customers as t1 join customers_and_services as t2 on t1.customer_id  =  t2.customer_id where t2.service_id  =  (select service_id from services group by service_id order by count(*) asc limit 1)
Database schema:
CREATE TABLE Artists (
  artistID NUMBER PRIMARY KEY,
  lname TEXT,
  fname TEXT,
  birthYear NUMBER,
  deathYear NUMBER
);
CREATE TABLE Paintings (
  paintingID NUMBER PRIMARY KEY,
  title TEXT,
  year NUMBER,
  height_mm NUMBER,
  width_mm NUMBER,
  medium TEXT,
  mediumOn TEXT,
  location TEXT,
  painterID NUMBER,
  FOREIGN KEY (painterID) REFERENCES Artists(artistID)
);
CREATE TABLE Sculptures (
  sculptureID NUMBER PRIMARY KEY,
  title TEXT,
  year NUMBER,
  medium TEXT,
  location TEXT,
  sculptorID NUMBER,
  FOREIGN KEY (sculptorID) REFERENCES Artists(artistID)
);

Question: List the names of all distinct paintings from shortest to longest in height.
SQL: SELECT DISTINCT title FROM paintings ORDER BY height_mm

Question: What is the average height of paintings for different medium types?
SQL: SELECT avg(height_mm) ,  medium FROM paintings GROUP BY medium

Question: What are the unique first names of the artists who had medium oil paintings located in gallery 241?
SQL: SELECT DISTINCT T1.fname FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T2.medium  =  "oil" AND LOCATION  =  "Gallery 241"

Question: What are the titles of paintings that are created after 1910 and whose medium is oil?
SQL: SELECT title FROM paintings WHERE YEAR  >  1910 AND medium  =  "oil"

Question: What is the unique id of every painter who had a medium oil painting displayed at gallery 240?
SQL: SELECT DISTINCT painterID FROM paintings WHERE medium  =  "oil" AND LOCATION  =  "Gallery 240"

Question: What are largest height and width dimensions for paintings in each year?
SQL: SELECT max(height_mm) ,  max(width_mm) ,  YEAR FROM paintings GROUP BY YEAR ORDER BY YEAR

Question: In what locations and on what mediums are the paintings created by the artist with the first name Pablo?
SQL: SELECT T2.location ,   T2.medium FROM artists AS T1 JOIN paintings AS T2 ON T1.artistID  =  T2.painterID WHERE T1.fname  =  "Pablo"

Question: What are the medium types of the painting with id = 80
SQL: SELECT medium FROM paintings WHERE paintingID  =  80

Question: Find the names and years of all sculptures that are not located in gallery 226.
SQL: SELECT title ,  YEAR FROM sculptures WHERE LOCATION != "Gallery 226"

Question: List the title and location of all sculptures.
SQL: SELECT title ,  LOCATION FROM sculptures

Question: What is the first and last name of each distinct artists who made a sculpture before 1900?
SQL: SELECT DISTINCT T1.lname ,  T1.fname FROM artists AS T1 JOIN sculptures AS T2 ON T1.artistID  =  T2.sculptorID WHERE T2.year  <  1900

Question: Which locations have paintings in the mediums of on panel and on canvas?
SQL: SELECT DISTINCT LOCATION FROM paintings WHERE mediumOn  =  "panel" INTERSECT SELECT DISTINCT LOCATION FROM paintings WHERE mediumOn  =  "canvas"

Question: What are the names of both paintings and sculptures created between 1900 and 1950?
SQL: SELECT title FROM paintings WHERE YEAR BETWEEN 1900 AND 1950 UNION SELECT title FROM sculptures WHERE YEAR BETWEEN 1900 AND 1950
Database schema:
CREATE TABLE conference (
  Conference_ID NUMBER PRIMARY KEY,
  Conference_Name TEXT,
  Year NUMBER,
  Location TEXT
);
CREATE TABLE institution (
  Institution_ID NUMBER PRIMARY KEY,
  Institution_Name TEXT,
  Location TEXT,
  Founded NUMBER
);
CREATE TABLE staff (
  staff_ID NUMBER PRIMARY KEY,
  name TEXT,
  Age NUMBER,
  Nationality TEXT,
  Institution_ID NUMBER,
  FOREIGN KEY (Institution_ID) REFERENCES institution(Institution_ID)
);
CREATE TABLE conference_participation (
  Conference_ID NUMBER,
  staff_ID NUMBER PRIMARY KEY,
  role TEXT,
  FOREIGN KEY (Conference_ID) REFERENCES conference(Conference_ID),
  FOREIGN KEY (staff_ID) REFERENCES staff(staff_ID)
);

Question: Show the institution name and location of institution that is most recently founded.
SQL: SELECT institution_name ,  LOCATION FROM institution ORDER BY founded DESC LIMIT 1

Question: Show the institution name and the number of staff for each institution founded after 1800.
SQL: SELECT T1.institution_name ,  count(*) FROM institution AS T1 JOIN staff AS T2 ON T1.institution_id  =  T2.institution_id WHERE T1.founded  >  1800 GROUP BY T2.institution_id
Database schema:
CREATE TABLE PilotSkills (
  pilot_name TEXT PRIMARY KEY,
  plane_name TEXT,
  age NUMBER,
  FOREIGN KEY (plane_name) REFERENCES Hangar(plane_name)
);
CREATE TABLE Hangar (
  plane_name TEXT PRIMARY KEY,
  location TEXT
);

Question: Find all different planes whose names contain substring 'Bomber'.
SQL: SELECT DISTINCT plane_name FROM pilotskills WHERE plane_name LIKE '%Bomber%'

Question: Return the name of the oldest pilot.
SQL: SELECT pilot_name FROM PilotSkills ORDER BY age DESC LIMIT 1

Question: How many pilots are younger than all pilots who own the F-14 Fighter?
SQL: SELECT count(*) FROM pilotskills WHERE age  <  (SELECT min(age) FROM pilotskills WHERE plane_name  =  'F-14 Fighter')

Question: What are the different hangar locations and how many pilots correspond to each. Also, what are their average ages?
SQL: SELECT count(T1.pilot_name) ,  avg(T1.age) ,  T2.location FROM pilotskills AS T1 JOIN hangar AS T2 ON T1.plane_name  =  T2.plane_name GROUP BY T2.location

Question: What is the name of the most popular plane?
SQL: SELECT plane_name FROM pilotskills GROUP BY plane_name ORDER BY count(*) DESC LIMIT 1

Question: Find the pilots who have either plane Piper Cub or plane F-14 Fighter.
SQL: SELECT pilot_name FROM pilotskills WHERE plane_name  =  'Piper Cub' OR plane_name  =  'F-14 Fighter'

Question: What are the names of planes that the pilot Jones who is 32 has?
SQL: SELECT plane_name FROM pilotskills WHERE pilot_name  =  'Jones'  AND age  =  32

Question: Which plane does the pilot Jones with age 32 has?
SQL: SELECT plane_name FROM pilotskills WHERE pilot_name  =  'Jones'  AND age  =  32

Question: Count the number of pilots with age greater than 40.
SQL: SELECT count(*) FROM pilotskills WHERE age  >  40

Question: What are the names of pilots who are younger than 35 and have a plane named Piper Cub?
SQL: SELECT pilot_name FROM PilotSkills WHERE age  <  35 AND plane_name  =  'Piper Cub'
Database schema:
CREATE TABLE customers (
  Id NUMBER PRIMARY KEY,
  LastName TEXT,
  FirstName TEXT
);
CREATE TABLE goods (
  Id TEXT PRIMARY KEY,
  Flavor TEXT,
  Food TEXT,
  Price NUMBER
);
CREATE TABLE items (
  Receipt NUMBER PRIMARY KEY,
  Ordinal NUMBER,
  Item TEXT,
  FOREIGN KEY (Item) REFERENCES goods(Id)
);
CREATE TABLE receipts (
  ReceiptNumber NUMBER PRIMARY KEY,
  Date TEXT,
  CustomerId NUMBER,
  FOREIGN KEY (CustomerId) REFERENCES customers(Id)
);

Question: Give me the first name and last name of customers who have bought apple flavor Tart.
SQL: SELECT DISTINCT T4.FirstName ,  T4.LastName FROM goods AS T1 JOIN items AS T2 ON T1.id  =  T2.item JOIN receipts AS T3 ON T2.receipt  =  T3.ReceiptNumber JOIN customers AS T4 ON T3.CustomerId  =  T4.id WHERE T1.flavor  =  "Apple" AND T1.food  =  "Tart"

Question: What are the average, minimum and maximum prices for each food?
SQL: SELECT food ,  avg(price) ,  max(price) ,  min(price) FROM goods GROUP BY food