target-benchmark/spider-queries huggingface dataset:
Database: city_record (tables: city, hosting_city)
Question: Which cities have served as host cities more than once? Return me their GDP and population.
SQL: SELECT t1.gdp ,  t1.Regional_Population FROM city AS T1 JOIN hosting_city AS T2 ON T1.city_id  =  T2.host_city GROUP BY t2.Host_City HAVING count(*)  >  1
Database: e_government (tables: individuals)
Question: List every individual's first name, middle name and last name in alphabetical order by last name.
SQL: SELECT individual_first_name ,  individual_middle_name ,  individual_last_name FROM individuals ORDER BY individual_last_name
Database: e_government (tables: individuals)
Question: What are the first, middle, and last names of all individuals, ordered by last name?
SQL: SELECT individual_first_name ,  individual_middle_name ,  individual_last_name FROM individuals ORDER BY individual_last_name
Database: e_government (tables: forms)
Question: List all the types of forms.
SQL: SELECT DISTINCT form_type_code FROM forms
Database: e_government (tables: forms)
Question: What are the different types of forms?
SQL: SELECT DISTINCT form_type_code FROM forms
Database: e_government (tables: forms, party_forms)
Question: Find the name of the most popular party form.
SQL: SELECT t1.form_name FROM forms AS t1 JOIN party_forms AS t2 ON t1.form_id  =  t2.form_id GROUP BY t2.form_id ORDER BY count(*) DESC LIMIT 1
Database: e_government (tables: forms, party_forms)
Question: What is the name of the party form that is most common?
SQL: SELECT t1.form_name FROM forms AS t1 JOIN party_forms AS t2 ON t1.form_id  =  t2.form_id GROUP BY t2.form_id ORDER BY count(*) DESC LIMIT 1
Database: e_government (tables: parties)
Question: Find the payment method and phone of the party with email "enrico09@example.com".
SQL: SELECT payment_method_code ,  party_phone FROM parties WHERE party_email  =  "enrico09@example.com"
Database: e_government (tables: parties)
Question: What is the payment method code and party phone of the party with the email 'enrico09@example.com'?
SQL: SELECT payment_method_code ,  party_phone FROM parties WHERE party_email  =  "enrico09@example.com"
Database: e_government (tables: parties, party_forms)
Question: Find the emails of parties with the most popular party form.
SQL: SELECT t1.party_email FROM parties AS t1 JOIN party_forms AS t2 ON t1.party_id  =  t2.party_id WHERE t2.form_id  =  (SELECT form_id FROM party_forms GROUP BY form_id ORDER BY count(*) DESC LIMIT 1)
Database: e_government (tables: parties, party_forms)
Question: What are the party emails associated with parties that used the party form that is the most common?
SQL: SELECT t1.party_email FROM parties AS t1 JOIN party_forms AS t2 ON t1.party_id  =  t2.party_id WHERE t2.form_id  =  (SELECT form_id FROM party_forms GROUP BY form_id ORDER BY count(*) DESC LIMIT 1)
Database: e_government (tables: organizations)
Question: List all the name of organizations in order of the date formed.
SQL: SELECT organization_name FROM organizations ORDER BY date_formed ASC
Database: e_government (tables: organizations)
Question: What are the names of organizations, ordered by the date they were formed, ascending?
SQL: SELECT organization_name FROM organizations ORDER BY date_formed ASC
Database: e_government (tables: organizations)
Question: Find the name of the youngest organization.
SQL: SELECT organization_name FROM organizations ORDER BY date_formed DESC LIMIT 1
Database: e_government (tables: organizations)
Question: What is the name of the organization that was formed most recently?
SQL: SELECT organization_name FROM organizations ORDER BY date_formed DESC LIMIT 1
Database: e_government (tables: organizations, organization_contact_individuals, individuals)
Question: Find the last name of the latest contact individual of the organization "Labour Party".
SQL: SELECT t3.individual_last_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id  =  t2.organization_id JOIN individuals AS t3 ON t2.individual_id  =  t3.individual_id WHERE t1.organization_name  =  "Labour Party" ORDER BY t2.date_contact_to DESC LIMIT 1
Database: e_government (tables: organizations, organization_contact_individuals, individuals)
Question: What is the last name of the contact individual from the Labour party organization who was contacted most recently?
SQL: SELECT t3.individual_last_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id  =  t2.organization_id JOIN individuals AS t3 ON t2.individual_id  =  t3.individual_id WHERE t1.organization_name  =  "Labour Party" ORDER BY t2.date_contact_to DESC LIMIT 1
Database: e_government (tables: organizations, organization_contact_individuals, individuals)
Question: Find the last name of the first ever contact person of the organization with the highest UK Vat number.
SQL: SELECT t3.individual_last_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id  =  t2.organization_id JOIN individuals AS t3 ON t2.individual_id  =  t3.individual_id WHERE t1.uk_vat_number  =  (SELECT max(uk_vat_number) FROM organizations) ORDER BY t2.date_contact_to ASC LIMIT 1
Database: e_government (tables: organizations, organization_contact_individuals, individuals)
Question: What is the last name of the first individual contacted from the organization with the maximum UK Vat number across all organizations?
SQL: SELECT t3.individual_last_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id  =  t2.organization_id JOIN individuals AS t3 ON t2.individual_id  =  t3.individual_id WHERE t1.uk_vat_number  =  (SELECT max(uk_vat_number) FROM organizations) ORDER BY t2.date_contact_to ASC LIMIT 1
Database: e_government (tables: services)
Question: How many services are there?
SQL: SELECT count(*) FROM services
Database: e_government (tables: services)
Question: Count the number of services.
SQL: SELECT count(*) FROM services
Database: e_government (tables: party_services, services)
Question: Find name of the services that has never been used.
SQL: SELECT service_name FROM services EXCEPT SELECT t1.service_name FROM services AS t1 JOIN party_services AS t2 ON t1.service_id  =  t2.service_id
Database: e_government (tables: party_services, services)
Question: What are the names of the services that have never been used?
SQL: SELECT service_name FROM services EXCEPT SELECT t1.service_name FROM services AS t1 JOIN party_services AS t2 ON t1.service_id  =  t2.service_id
Database: e_government (tables: addresses)
Question: Find the name of all the cities and states.
SQL: SELECT town_city FROM addresses UNION SELECT state_province_county FROM addresses
Database: e_government (tables: addresses)
Question: What are the names of all cities and states?
SQL: SELECT town_city FROM addresses UNION SELECT state_province_county FROM addresses
Database: e_government (tables: addresses)
Question: How many cities are there in state "Colorado"?
SQL: SELECT count(*) FROM addresses WHERE state_province_county  =  "Colorado"
Database: e_government (tables: addresses)
Question: Count the number of cities in the state of Colorado.
SQL: SELECT count(*) FROM addresses WHERE state_province_county  =  "Colorado"
Database: e_government (tables: parties)
Question: Find the payment method code used by more than 3 parties.
SQL: SELECT payment_method_code FROM parties GROUP BY payment_method_code HAVING count(*)  >  3
Database: e_government (tables: parties)
Question: What are the payment method codes that have been used by more than 3 parties?
SQL: SELECT payment_method_code FROM parties GROUP BY payment_method_code HAVING count(*)  >  3
Database: e_government (tables: organizations)
Question: Find the name of organizations whose names contain "Party".
SQL: SELECT organization_name FROM organizations WHERE organization_name LIKE "%Party%"
Database: e_government (tables: organizations)
Question: What are the names of organizations that contain the word "Party"?
SQL: SELECT organization_name FROM organizations WHERE organization_name LIKE "%Party%"
Database: e_government (tables: parties)
Question: How many distinct payment methods are used by parties?
SQL: SELECT count(DISTINCT payment_method_code) FROM parties
Database: e_government (tables: parties)
Question: Count the number of different payment method codes used by parties.
SQL: SELECT count(DISTINCT payment_method_code) FROM parties
Database: e_government (tables: party_services, parties)
Question: Which is the email of the party that has used the services the most number of times?
SQL: SELECT t1.party_email FROM parties AS t1 JOIN party_services AS t2 ON t1.party_id  =  t2.customer_id GROUP BY t1.party_email ORDER BY count(*) DESC LIMIT 1
Database: e_government (tables: party_services, parties)
Question: Return the party email that has used party services the greatest number of times.
SQL: SELECT t1.party_email FROM parties AS t1 JOIN party_services AS t2 ON t1.party_id  =  t2.customer_id GROUP BY t1.party_email ORDER BY count(*) DESC LIMIT 1
Database: e_government (tables: addresses)
Question: Which state can address "6862 Kaitlyn Knolls" possibly be in?
SQL: SELECT state_province_county FROM addresses WHERE line_1_number_building LIKE "%6862 Kaitlyn Knolls%"
Database: e_government (tables: addresses)
Question: Give the state corresponding to the line number building "6862 Kaitlyn Knolls".
SQL: SELECT state_province_county FROM addresses WHERE line_1_number_building LIKE "%6862 Kaitlyn Knolls%"
Database: e_government (tables: organizations, organization_contact_individuals)
Question: What is the name of organization that has the greatest number of contact individuals?
SQL: SELECT t1.organization_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id  =  t2.organization_id GROUP BY t1.organization_name ORDER BY count(*) DESC LIMIT 1
Database: e_government (tables: organizations, organization_contact_individuals)
Question: Return the name of the organization which has the most contact individuals.
SQL: SELECT t1.organization_name FROM organizations AS t1 JOIN organization_contact_individuals AS t2 ON t1.organization_id  =  t2.organization_id GROUP BY t1.organization_name ORDER BY count(*) DESC LIMIT 1
Database: e_government (tables: organization_contact_individuals, individuals)
Question: Find the last name of the individuals that have been contact individuals of an organization.
SQL: SELECT DISTINCT t1.individual_last_name FROM individuals AS t1 JOIN organization_contact_individuals AS t2 ON t1.individual_id  =  t2.individual_id
Database: e_government (tables: organization_contact_individuals, individuals)
Question: What are the last names of individuals who have been contact individuals for an organization?
SQL: SELECT DISTINCT t1.individual_last_name FROM individuals AS t1 JOIN organization_contact_individuals AS t2 ON t1.individual_id  =  t2.individual_id
Database: school_bus (tables: driver)
Question: How many drivers are there?
SQL: SELECT count(*) FROM driver
Database: school_bus (tables: driver)
Question: Show the name, home city, and age for all drivers.
SQL: SELECT name ,  home_city ,  age FROM driver
Database: school_bus (tables: driver)
Question: Show the party and the number of drivers in each party.
SQL: SELECT party ,  count(*) FROM driver GROUP BY party
Database: school_bus (tables: driver)
Question: Show the name of drivers in descending order of age.
SQL: SELECT name FROM driver ORDER BY age DESC
Database: school_bus (tables: driver)
Question: Show all different home cities.
SQL: SELECT DISTINCT home_city FROM driver
Database: school_bus (tables: driver)
Question: Show the home city with the most number of drivers.
SQL: SELECT home_city FROM driver GROUP BY home_city ORDER BY count(*) DESC LIMIT 1
Database: school_bus (tables: driver)
Question: Show the party with drivers from Hartford and drivers older than 40.
SQL: SELECT party FROM driver WHERE home_city  =  'Hartford' AND age  >  40
Database: school_bus (tables: driver)
Question: Show home city where at least two drivers older than 40 are from.
SQL: SELECT home_city FROM driver WHERE age  >  40 GROUP BY home_city HAVING count(*)  >=  2
Database: school_bus (tables: driver)
Question: Show all home cities except for those having a driver older than 40.
SQL: SELECT home_city FROM driver EXCEPT SELECT home_city FROM driver WHERE age  >  40
Database: school_bus (tables: driver, school_bus)
Question: Show the names of the drivers without a school bus.
SQL: SELECT name FROM driver WHERE driver_id NOT IN (SELECT driver_id FROM school_bus)
Database: school_bus (tables: school)
Question: Show the types of schools that have two schools.
SQL: SELECT TYPE FROM school GROUP BY TYPE HAVING count(*)  =  2
Database: school_bus (tables: school, driver, school_bus)
Question: Show the school name and driver name for all school buses.
SQL: SELECT T2.school ,  T3.name FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id JOIN driver AS T3 ON T1.driver_id  =  T3.driver_id
Database: school_bus (tables: school_bus)
Question: What is the maximum, minimum and average years spent working on a school bus?
SQL: SELECT max(years_working) ,  min(years_working) ,  avg(years_working) FROM school_bus
Database: school_bus (tables: school, school_bus)
Question: Show the school name and type for schools without a school bus.
SQL: SELECT school ,  TYPE FROM school WHERE school_id NOT IN (SELECT school_id FROM school_bus)
Database: school_bus (tables: school, school_bus)
Question: Show the type of school and the number of buses for each type.
SQL: SELECT T2.type ,  count(*) FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id GROUP BY T2.type
Database: school_bus (tables: driver)
Question: How many drivers are from Hartford city or younger than 40?
SQL: SELECT count(*) FROM driver WHERE home_city  =  'Hartford' OR age  <  40
Database: school_bus (tables: driver)
Question: List names for drivers from Hartford city and younger than 40.
SQL: SELECT name FROM driver WHERE home_city  =  'Hartford' AND age  <  40
Database: school_bus (tables: driver, school_bus)
Question: find the name of driver who is driving the school bus with the longest working history.
SQL: SELECT t1.name FROM driver AS t1 JOIN school_bus AS t2 ON t1.driver_id  =  t2.driver_id ORDER BY years_working DESC LIMIT 1
Database: flight_company (tables: flight)
Question: How many flights have a velocity larger than 200?
SQL: SELECT count(*) FROM flight WHERE velocity  >  200
Database: flight_company (tables: flight)
Question: List the vehicle flight number, date and pilot of all the flights, ordered by altitude.
SQL: SELECT vehicle_flight_number ,  date ,  pilot FROM flight ORDER BY altitude ASC
Database: flight_company (tables: airport)
Question: List the id, country, city and name of the airports ordered alphabetically by the name.
SQL: SELECT id ,  country ,  city ,  name FROM airport ORDER BY name
Database: flight_company (tables: operate_company)
Question: What is maximum group equity shareholding of the companies?
SQL: SELECT max(group_equity_shareholding) FROM operate_company
Database: flight_company (tables: flight)
Question: What is the velocity of the pilot named 'Thompson'?
SQL: SELECT avg(velocity) FROM flight WHERE pilot  =  'Thompson'
Database: flight_company (tables: operate_company, flight)
Question: What are the names and types of the companies that have ever operated a flight?
SQL: SELECT T1.name ,  T1.type FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id
Database: flight_company (tables: airport)
Question: What are the names of the airports which are not in the country 'Iceland'?
SQL: SELECT name FROM airport WHERE country != 'Iceland'
Database: flight_company (tables: operate_company, flight)
Question: What are the distinct types of the companies that have operated any flights with velocity less than 200?
SQL: SELECT DISTINCT T1.type FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id WHERE T2.velocity  <  200
Database: flight_company (tables: operate_company, flight)
Question: What are the ids and names of the companies that operated more than one flight?
SQL: SELECT T1.id ,  T1.name FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id GROUP BY T1.id HAVING count(*)  >  1
Database: flight_company (tables: flight, airport)
Question: What is the id, name and IATA code of the airport that had most number of flights?
SQL: SELECT T1.id ,  T1.name ,  T1.IATA FROM airport AS T1 JOIN flight AS T2 ON T1.id  =  T2.airport_id GROUP BY T2.id ORDER BY count(*) DESC LIMIT 1
Database: flight_company (tables: flight, airport)
Question: What are the different pilot names who had piloted a flight in the country 'United States' or in the airport named 'Billund Airport'?
SQL: SELECT DISTINCT T2.pilot FROM airport AS T1 JOIN flight AS T2 ON T1.id  =  T2.airport_id WHERE T1.country  =  'United States' OR T1.name  =  'Billund Airport'
Database: flight_company (tables: operate_company)
Question: What is the most common company type, and how many are there?
SQL: SELECT TYPE ,  count(*) FROM operate_company GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1
Database: flight_company (tables: flight, airport)
Question: How many airports haven't the pilot 'Thompson' driven an aircraft?
SQL: SELECT count(*) FROM airport WHERE id NOT IN ( SELECT airport_id FROM flight WHERE pilot  =  'Thompson' );
Database: flight_company (tables: operate_company, flight)
Question: List the name of the pilots who have flied for both a company that mainly provide 'Cargo' services and a company that runs 'Catering services' activities.
SQL: SELECT T2.pilot FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id WHERE T1.principal_activities  =  'Cargo' INTERSECT SELECT T2.pilot FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id WHERE T1.principal_activities  =  'Catering services'
Database: flight_company (tables: airport)
Question: Which of the airport names contains the word 'international'?
SQL: SELECT name FROM airport WHERE name LIKE '%international%'
Database: flight_company (tables: operate_company, flight, airport)
Question: How many companies operates airlines in each airport?
SQL: SELECT T3.id ,  count(*) FROM operate_company AS T1 JOIN flight AS t2 ON T1.id  =  T2.company_id JOIN airport AS T3 ON T2.airport_id  =  T3.id GROUP BY T3.id
Database: flight_company (tables: airport)
Question: how many airports are there in each country?
SQL: SELECT count(*) ,  country FROM airport GROUP BY country
Database: flight_company (tables: airport)
Question: which countries have more than 2 airports?
SQL: SELECT country FROM airport GROUP BY country HAVING count(*)  >  2
Database: flight_company (tables: flight)
Question: which pilot is in charge of the most number of flights?
SQL: SELECT pilot FROM flight GROUP BY pilot ORDER BY count(*) DESC LIMIT 1
Database: cre_Docs_and_Epenses (tables: accounts)
Question: How many accounts do we have?
SQL: SELECT count(*) FROM Accounts
Database: cre_Docs_and_Epenses (tables: accounts)
Question: Count the number of accounts.
SQL: SELECT count(*) FROM Accounts
Database: cre_Docs_and_Epenses (tables: accounts)
Question: Show all account ids and account details.
SQL: SELECT account_id ,  account_details FROM Accounts
Database: cre_Docs_and_Epenses (tables: accounts)
Question: What are the ids and details of all accounts?
SQL: SELECT account_id ,  account_details FROM Accounts
Database: cre_Docs_and_Epenses (tables: statements)
Question: How many statements do we have?
SQL: SELECT count(*) FROM Statements
Database: cre_Docs_and_Epenses (tables: statements)
Question: Count the number of statements.
SQL: SELECT count(*) FROM Statements
Database: cre_Docs_and_Epenses (tables: statements)
Question: List all statement ids and statement details.
SQL: SELECT STATEMENT_ID ,  statement_details FROM Statements
Database: cre_Docs_and_Epenses (tables: statements)
Question: What are the ids and details of all statements?
SQL: SELECT STATEMENT_ID ,  statement_details FROM Statements
Database: cre_Docs_and_Epenses (tables: statements, accounts)
Question: Show statement id, statement detail, account detail for accounts.
SQL: SELECT T1.statement_id ,  T2.statement_details ,  T1.account_details FROM Accounts AS T1 JOIN Statements AS T2 ON T1.statement_id  =  T2.statement_id
Database: cre_Docs_and_Epenses (tables: statements, accounts)
Question: What are the statement ids, statement details, and account details, for all accounts?
SQL: SELECT T1.statement_id ,  T2.statement_details ,  T1.account_details FROM Accounts AS T1 JOIN Statements AS T2 ON T1.statement_id  =  T2.statement_id
Database: cre_Docs_and_Epenses (tables: accounts)
Question: Show all statement id and the number of accounts for each statement.
SQL: SELECT STATEMENT_ID ,  count(*) FROM Accounts GROUP BY STATEMENT_ID
Database: cre_Docs_and_Epenses (tables: accounts)
Question: What are the different statement ids on accounts, and the number of accounts for each?
SQL: SELECT STATEMENT_ID ,  count(*) FROM Accounts GROUP BY STATEMENT_ID
Database: cre_Docs_and_Epenses (tables: statements, accounts)
Question: Show the statement id and the statement detail for the statement with most number of accounts.
SQL: SELECT T1.statement_id ,  T2.statement_details FROM Accounts AS T1 JOIN Statements AS T2 ON T1.statement_id  =  T2.statement_id GROUP BY T1.statement_id ORDER BY count(*) DESC LIMIT 1
Database: cre_Docs_and_Epenses (tables: statements, accounts)
Question: What are the statement id and statement detail for the statement that has the most corresponding accounts?
SQL: SELECT T1.statement_id ,  T2.statement_details FROM Accounts AS T1 JOIN Statements AS T2 ON T1.statement_id  =  T2.statement_id GROUP BY T1.statement_id ORDER BY count(*) DESC LIMIT 1
Database: cre_Docs_and_Epenses (tables: documents)
Question: Show the number of documents.
SQL: SELECT count(*) FROM Documents
Database: cre_Docs_and_Epenses (tables: documents)
Question: Count the number of documents.
SQL: SELECT count(*) FROM Documents
Database: cre_Docs_and_Epenses (tables: documents)
Question: List the document type code, document name, and document description for the document with name 'Noel CV' or name 'King Book'.
SQL: SELECT document_type_code ,  document_name ,  document_description FROM Documents WHERE document_name  =  'Noel CV' OR document_name  =  'King Book'
Database: cre_Docs_and_Epenses (tables: documents)
Question: What are the type come, name, and description of the document that has either the name 'Noel CV' or 'King Book'?
SQL: SELECT document_type_code ,  document_name ,  document_description FROM Documents WHERE document_name  =  'Noel CV' OR document_name  =  'King Book'
Database: cre_Docs_and_Epenses (tables: documents)
Question: Show the ids and names of all documents.
SQL: SELECT document_id ,  document_name FROM Documents
Database: cre_Docs_and_Epenses (tables: documents)
Question: What are the ids and names for each of the documents?
SQL: SELECT document_id ,  document_name FROM Documents
Database: cre_Docs_and_Epenses (tables: documents)
Question: Find names and ids of all documents with document type code BK.
SQL: SELECT document_name ,  document_id FROM Documents WHERE document_type_code  =  "BK"
Database: cre_Docs_and_Epenses (tables: documents)
Question: What are the names and ids of documents that have the type code BK?
SQL: SELECT document_name ,  document_id FROM Documents WHERE document_type_code  =  "BK"
Database: cre_Docs_and_Epenses (tables: documents)
Question: How many documents are with document type code BK for each product id?
SQL: SELECT count(*) ,  project_id FROM Documents WHERE document_type_code  =  "BK" GROUP BY project_id
Database: cre_Docs_and_Epenses (tables: documents)
Question: Count the number of documents with the type code BK that correspond to each product id.
SQL: SELECT count(*) ,  project_id FROM Documents WHERE document_type_code  =  "BK" GROUP BY project_id