target-benchmark/spider-queries huggingface dataset:
Database: car_road_race (tables: driver, race)
Question: Please show the age of the driver who participated in the most number of races.
SQL: SELECT T1.Age FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID GROUP BY T1.Driver_ID ORDER BY COUNT(*) DESC LIMIT 1
Database: car_road_race (tables: driver, race)
Question: What is the age of the driver who raced in the most races?
SQL: SELECT T1.Age FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID GROUP BY T1.Driver_ID ORDER BY COUNT(*) DESC LIMIT 1
Database: car_road_race (tables: driver, race)
Question: Please show the names and ages of the drivers who participated in at least two races.
SQL: SELECT T1.Driver_Name ,  T1.Age FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID GROUP BY T1.Driver_ID HAVING COUNT(*)  >=  2
Database: car_road_race (tables: driver, race)
Question: What are the names and ages of drivers who raced in two or more races?
SQL: SELECT T1.Driver_Name ,  T1.Age FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID GROUP BY T1.Driver_ID HAVING COUNT(*)  >=  2
Database: car_road_race (tables: driver, race)
Question: Please list the names of races with drivers aged 26 or older participating.
SQL: SELECT T2.Race_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE T1.Age  >=  26
Database: car_road_race (tables: driver, race)
Question: What are the names of races in which drivers 26 or older took part?
SQL: SELECT T2.Race_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE T1.Age  >=  26
Database: car_road_race (tables: driver)
Question: List the names of drivers whose constructor is not "Bugatti".
SQL: SELECT Driver_Name FROM driver WHERE CONSTRUCTOR != "Bugatti"
Database: car_road_race (tables: driver)
Question: What are the names od drivers who did not have the constructor Bugatti?
SQL: SELECT Driver_Name FROM driver WHERE CONSTRUCTOR != "Bugatti"
Database: car_road_race (tables: driver)
Question: List different constructors and the number of drivers that use each constructor.
SQL: SELECT CONSTRUCTOR ,  COUNT(*) FROM driver GROUP BY CONSTRUCTOR
Database: car_road_race (tables: driver)
Question: How many drivers use each constructor?
SQL: SELECT CONSTRUCTOR ,  COUNT(*) FROM driver GROUP BY CONSTRUCTOR
Database: car_road_race (tables: driver)
Question: List the most common type of engine used by drivers.
SQL: SELECT Engine FROM driver GROUP BY Engine ORDER BY COUNT(*) DESC LIMIT 1
Database: car_road_race (tables: driver)
Question: What is the most common type of engine?
SQL: SELECT Engine FROM driver GROUP BY Engine ORDER BY COUNT(*) DESC LIMIT 1
Database: car_road_race (tables: driver)
Question: List the types of engines that are used by at least two drivers.
SQL: SELECT Engine FROM driver GROUP BY Engine HAVING COUNT(*)  >=  2
Database: car_road_race (tables: driver)
Question: What are the engine types that are used by two or more drivers?
SQL: SELECT Engine FROM driver GROUP BY Engine HAVING COUNT(*)  >=  2
Database: car_road_race (tables: driver, race)
Question: List the names of drivers that do not participate in any race.
SQL: SELECT Driver_Name FROM driver WHERE Driver_ID NOT IN (SELECT Driver_ID FROM race)
Database: car_road_race (tables: driver, race)
Question: What are names of drivers who did not take part in a race?
SQL: SELECT Driver_Name FROM driver WHERE Driver_ID NOT IN (SELECT Driver_ID FROM race)
Database: car_road_race (tables: driver)
Question: Show the constructors that are used both by drivers with age lower than 20 and drivers with age over than 30.
SQL: SELECT CONSTRUCTOR FROM driver WHERE Age  <  20 INTERSECT SELECT CONSTRUCTOR FROM driver WHERE Age  >  30
Database: car_road_race (tables: driver)
Question: What are the constructors who are used by both drivers who are younger than 20 and drivers older than 30?
SQL: SELECT CONSTRUCTOR FROM driver WHERE Age  <  20 INTERSECT SELECT CONSTRUCTOR FROM driver WHERE Age  >  30
Database: car_road_race (tables: race)
Question: Find the teams that won more than once.
SQL: SELECT Winning_team FROM race GROUP BY Winning_team HAVING count(*)  >  1
Database: car_road_race (tables: race)
Question: Which teams won more than 1 race?
SQL: SELECT Winning_team FROM race GROUP BY Winning_team HAVING count(*)  >  1
Database: car_road_race (tables: driver, race)
Question: Find the names of drivers who were in both "James Hinchcliffe" and "Carl Skerlong" pole positions before.
SQL: SELECT T1.Driver_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE Pole_Position  =  "Carl Skerlong" INTERSECT SELECT T1.Driver_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE Pole_Position  =  "James Hinchcliffe"
Database: car_road_race (tables: driver, race)
Question: What are the names of drivers who had both the pole position James Hinchcliffe and the pole position Carl Skerlong?
SQL: SELECT T1.Driver_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE Pole_Position  =  "Carl Skerlong" INTERSECT SELECT T1.Driver_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE Pole_Position  =  "James Hinchcliffe"
Database: car_road_race (tables: driver, race)
Question: find the name of drivers who were never in "James Hinchcliffe" pole position before.
SQL: SELECT Driver_Name FROM driver EXCEPT SELECT T1.Driver_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE Pole_Position  =  "James Hinchcliffe"
Database: car_road_race (tables: driver, race)
Question: What are the names of drivers except for those who had the pole position James Hinchcliffe?
SQL: SELECT Driver_Name FROM driver EXCEPT SELECT T1.Driver_Name FROM driver AS T1 JOIN race AS T2 ON T1.Driver_ID  =  T2.Driver_ID WHERE Pole_Position  =  "James Hinchcliffe"
Database: country_language (tables: languages)
Question: How many languages are there?
SQL: SELECT count(*) FROM languages
Database: country_language (tables: languages)
Question: Count the number of languages.
SQL: SELECT count(*) FROM languages
Database: country_language (tables: languages)
Question: List the name of languages in ascending alphabetical order.
SQL: SELECT name FROM languages ORDER BY name ASC
Database: country_language (tables: languages)
Question: What are the names of languages, in alphabetical order?
SQL: SELECT name FROM languages ORDER BY name ASC
Database: country_language (tables: languages)
Question: What are the names of languages that contain the word "ish"?
SQL: SELECT name FROM languages WHERE name LIKE "%ish%"
Database: country_language (tables: languages)
Question: Return the names of langauges that contain the substring "ish".
SQL: SELECT name FROM languages WHERE name LIKE "%ish%"
Database: country_language (tables: countries)
Question: Show the names of countries in descending order of overall scores.
SQL: SELECT name FROM countries ORDER BY overall_score DESC
Database: country_language (tables: countries)
Question: What are the names of the countries, ordered descending by overall score?
SQL: SELECT name FROM countries ORDER BY overall_score DESC
Database: country_language (tables: countries)
Question: What is the average justice scores among countries?
SQL: SELECT avg(justice_score) FROM countries
Database: country_language (tables: countries)
Question: Give the average justice scores across all countries.
SQL: SELECT avg(justice_score) FROM countries
Database: country_language (tables: countries)
Question: What are the maximum and minimum health scores among countries that are not "Norway".
SQL: SELECT max(health_score) ,  min(health_score) FROM countries WHERE name != "Norway"
Database: country_language (tables: countries)
Question: Return the maximum and minimum health scores across all countries other than Norway.
SQL: SELECT max(health_score) ,  min(health_score) FROM countries WHERE name != "Norway"
Database: country_language (tables: official_languages)
Question: How many different official languages are there?
SQL: SELECT count(DISTINCT language_id) FROM official_languages
Database: country_language (tables: official_languages)
Question: Count the number of different official languages.
SQL: SELECT count(DISTINCT language_id) FROM official_languages
Database: country_language (tables: countries)
Question: List names of countries in descending order of education_score.
SQL: SELECT name FROM countries ORDER BY education_score DESC
Database: country_language (tables: countries)
Question: What are the names of the countries, ordered descending by education score?
SQL: SELECT name FROM countries ORDER BY education_score DESC
Database: country_language (tables: countries)
Question: List the name of the country with the biggest score in politics.
SQL: SELECT name FROM countries ORDER BY politics_score DESC LIMIT 1
Database: country_language (tables: countries)
Question: What is the name of the country with the highest politics score?
SQL: SELECT name FROM countries ORDER BY politics_score DESC LIMIT 1
Database: country_language (tables: countries, official_languages, languages)
Question: Show the names of countries and their official languages.
SQL: SELECT T1.name ,  T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id
Database: country_language (tables: countries, official_languages, languages)
Question: What are the names of the countries, as well as the names of their official langauges?
SQL: SELECT T1.name ,  T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id
Database: country_language (tables: official_languages, languages)
Question: Show the official languages and the number of countries speaking each language.
SQL: SELECT T2.name ,  COUNT(*) FROM official_languages AS T1 JOIN languages AS T2 ON T1.language_id  =  T2.id GROUP BY T2.name
Database: country_language (tables: official_languages, languages)
Question: What are the names of the different official languages, as well as the number of countries that speak each?
SQL: SELECT T2.name ,  COUNT(*) FROM official_languages AS T1 JOIN languages AS T2 ON T1.language_id  =  T2.id GROUP BY T2.name
Database: country_language (tables: official_languages, languages)
Question: Show the official language spoken by the most number of countries.
SQL: SELECT T2.name FROM official_languages AS T1 JOIN languages AS T2 ON T1.language_id  =  T2.id GROUP BY T2.id ORDER BY COUNT(*) DESC LIMIT 1
Database: country_language (tables: official_languages, languages)
Question: What is the official language that is most common?
SQL: SELECT T2.name FROM official_languages AS T1 JOIN languages AS T2 ON T1.language_id  =  T2.id GROUP BY T2.id ORDER BY COUNT(*) DESC LIMIT 1
Database: country_language (tables: official_languages, languages)
Question: Show the official languages spoken by at least two countries.
SQL: SELECT T2.name FROM official_languages AS T1 JOIN languages AS T2 ON T1.language_id  =  T2.id GROUP BY T2.id HAVING COUNT(*)  >=  2
Database: country_language (tables: official_languages, languages)
Question: Which official languages are spoken in two or more countries?
SQL: SELECT T2.name FROM official_languages AS T1 JOIN languages AS T2 ON T1.language_id  =  T2.id GROUP BY T2.id HAVING COUNT(*)  >=  2
Database: country_language (tables: countries, official_languages, languages)
Question: Show the average overall scores of countries whose official language is "English".
SQL: SELECT avg(T1.overall_score) FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T3.name  =  "English"
Database: country_language (tables: countries, official_languages, languages)
Question: What is the average overall score across countries with English as their official language?
SQL: SELECT avg(T1.overall_score) FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T3.name  =  "English"
Database: country_language (tables: official_languages, languages)
Question: Show the three official languages that are most commonly spoken.
SQL: SELECT T2.name FROM official_languages AS T1 JOIN languages AS T2 ON T1.language_id  =  T2.id GROUP BY T2.id ORDER BY COUNT(*) DESC LIMIT 3
Database: country_language (tables: official_languages, languages)
Question: What are the names of the three official languages spoken in the most countries?
SQL: SELECT T2.name FROM official_languages AS T1 JOIN languages AS T2 ON T1.language_id  =  T2.id GROUP BY T2.id ORDER BY COUNT(*) DESC LIMIT 3
Database: country_language (tables: countries, official_languages, languages)
Question: Show the official languages sorted in descending order by the average overall scores among countries speaking them.
SQL: SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id GROUP BY T3.id ORDER BY avg(T1.overall_score) DESC
Database: country_language (tables: countries, official_languages, languages)
Question: What are the names of the official languages, sorted descending by the average overall scores across the countries that correspond to each?
SQL: SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id GROUP BY T3.id ORDER BY avg(T1.overall_score) DESC
Database: country_language (tables: countries, official_languages)
Question: Show the name of the country that has the greatest number of official languages.
SQL: SELECT T1.Name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id GROUP BY T1.id ORDER BY COUNT(*) DESC LIMIT 1
Database: country_language (tables: countries, official_languages)
Question: Which country has the greatest number of official languages?
SQL: SELECT T1.Name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id GROUP BY T1.id ORDER BY COUNT(*) DESC LIMIT 1
Database: country_language (tables: official_languages, languages)
Question: List the names of languages that are not the official language of any countries.
SQL: SELECT name FROM languages WHERE id NOT IN (SELECT language_id FROM official_languages)
Database: country_language (tables: official_languages, languages)
Question: What are the names of languages that are not the official language of any country?
SQL: SELECT name FROM languages WHERE id NOT IN (SELECT language_id FROM official_languages)
Database: country_language (tables: countries, official_languages)
Question: List the names of countries that do not have any official language.
SQL: SELECT name FROM countries WHERE id NOT IN (SELECT country_id FROM official_languages)
Database: country_language (tables: countries, official_languages)
Question: What are the names of countries that do not have an official language?
SQL: SELECT name FROM countries WHERE id NOT IN (SELECT country_id FROM official_languages)
Database: country_language (tables: countries, official_languages, languages)
Question: Show the names of languages that are the official language for both countries with overall score greater than 95 and countries with overall score less than than 90.
SQL: SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T1.overall_score  >  95 INTERSECT SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T1.overall_score  <  90
Database: country_language (tables: countries, official_languages, languages)
Question: What are the names of languages that are the official language not only for countries that have an overall score of above 95, but also for countries that have an overall score below 90?
SQL: SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T1.overall_score  >  95 INTERSECT SELECT T3.name FROM countries AS T1 JOIN official_languages AS T2 ON T1.id  =  T2.country_id JOIN languages AS T3 ON T2.language_id  =  T3.id WHERE T1.overall_score  <  90
Database: real_estate_rentals (tables: addresses)
Question: Which countries and cities are included in addresses?
SQL: SELECT country ,  town_city FROM Addresses;
Database: real_estate_rentals (tables: addresses)
Question: What are the countries and cities for each address?
SQL: SELECT country ,  town_city FROM Addresses;
Database: real_estate_rentals (tables: addresses, properties)
Question: In which states are each of the the properties located?
SQL: SELECT DISTINCT T1.county_state_province FROM Addresses AS T1 JOIN Properties AS T2 ON T1.address_id  =  T2.property_address_id;
Database: real_estate_rentals (tables: addresses, properties)
Question: Give the states or provinces corresponding to each property.
SQL: SELECT DISTINCT T1.county_state_province FROM Addresses AS T1 JOIN Properties AS T2 ON T1.address_id  =  T2.property_address_id;
Database: real_estate_rentals (tables: features)
Question: How is the feature rooftop described?
SQL: SELECT feature_description FROM Features WHERE feature_name  =  'rooftop';
Database: real_estate_rentals (tables: features)
Question: Return the description of the feature 'rooftop'.
SQL: SELECT feature_description FROM Features WHERE feature_name  =  'rooftop';
Database: real_estate_rentals (tables: property_features, features)
Question: What are the feature name and description of the most commonly seen feature across properties?
SQL: SELECT T1.feature_name ,  T1.feature_description FROM Features AS T1 JOIN Property_Features AS T2 ON T1.feature_id  =  T2.feature_id GROUP BY T1.feature_name ORDER BY count(*) DESC LIMIT 1;
Database: real_estate_rentals (tables: property_features, features)
Question: Give the feature name and description for the most common feature across all properties.
SQL: SELECT T1.feature_name ,  T1.feature_description FROM Features AS T1 JOIN Property_Features AS T2 ON T1.feature_id  =  T2.feature_id GROUP BY T1.feature_name ORDER BY count(*) DESC LIMIT 1;
Database: real_estate_rentals (tables: properties)
Question: What is the minimum number of rooms in a property?
SQL: SELECT min(room_count) FROM Properties;
Database: real_estate_rentals (tables: properties)
Question: What is the lowest room count across all the properties?
SQL: SELECT min(room_count) FROM Properties;
Database: real_estate_rentals (tables: properties)
Question: How many properties have 1 parking lot or 1 garage?
SQL: SELECT count(*) FROM Properties WHERE parking_lots  =  1 OR garage_yn  =  1;
Database: real_estate_rentals (tables: properties)
Question: Count the number of properties that have 1 parking lot or 1 garage.
SQL: SELECT count(*) FROM Properties WHERE parking_lots  =  1 OR garage_yn  =  1;
Database: real_estate_rentals (tables: ref_user_categories, users)
Question: For users whose description contain the string 'Mother', which age categories are they in?
SQL: SELECT T2.age_category_code FROM Ref_User_Categories AS T1 JOIN Users AS T2 ON T1.user_category_code  =  T2.user_category_code WHERE T1.User_category_description LIKE "%Mother";
Database: real_estate_rentals (tables: ref_user_categories, users)
Question: What are the age categories for users whose description contains the string Mother?
SQL: SELECT T2.age_category_code FROM Ref_User_Categories AS T1 JOIN Users AS T2 ON T1.user_category_code  =  T2.user_category_code WHERE T1.User_category_description LIKE "%Mother";
Database: real_estate_rentals (tables: users, properties)
Question: What is the first name of the user who owns the greatest number of properties?
SQL: SELECT T1.first_name FROM Users AS T1 JOIN Properties AS T2 ON T2.owner_user_id  =  T1.User_id GROUP BY T1.User_id ORDER BY count(*) DESC LIMIT 1;
Database: real_estate_rentals (tables: users, properties)
Question: Return the first name of the user who owns the most properties.
SQL: SELECT T1.first_name FROM Users AS T1 JOIN Properties AS T2 ON T2.owner_user_id  =  T1.User_id GROUP BY T1.User_id ORDER BY count(*) DESC LIMIT 1;
Database: real_estate_rentals (tables: properties, property_features, features)
Question: List the average room count of the properties with gardens.
SQL: SELECT avg(T3.room_count) FROM Property_Features AS T1 JOIN Features AS T2 ON T1.feature_id  =  T2.feature_id JOIN Properties AS T3 ON T1.property_id  =  T3.property_id WHERE T2.feature_name  =  'garden';
Database: real_estate_rentals (tables: properties, property_features, features)
Question: On average, how many rooms do properties with garden features have?
SQL: SELECT avg(T3.room_count) FROM Property_Features AS T1 JOIN Features AS T2 ON T1.feature_id  =  T2.feature_id JOIN Properties AS T3 ON T1.property_id  =  T3.property_id WHERE T2.feature_name  =  'garden';
Database: real_estate_rentals (tables: addresses, property_features, features, properties)
Question: In which cities are there any properties equipped with a swimming pool?
SQL: SELECT T2.town_city FROM Properties AS T1 JOIN Addresses AS T2 ON T1.property_address_id  =  T2.address_id JOIN Property_Features AS T3 ON T1.property_id  =  T3.property_id JOIN Features AS T4 ON T4.feature_id  =  T3.feature_id WHERE T4.feature_name  =  'swimming pool';
Database: real_estate_rentals (tables: addresses, property_features, features, properties)
Question: Return the cities in which there exist properties that have swimming pools.
SQL: SELECT T2.town_city FROM Properties AS T1 JOIN Addresses AS T2 ON T1.property_address_id  =  T2.address_id JOIN Property_Features AS T3 ON T1.property_id  =  T3.property_id JOIN Features AS T4 ON T4.feature_id  =  T3.feature_id WHERE T4.feature_name  =  'swimming pool';
Database: real_estate_rentals (tables: properties)
Question: Which property had the lowest price requested by the vendor? List the id and the price.
SQL: SELECT property_id ,  vendor_requested_price FROM Properties ORDER BY vendor_requested_price LIMIT 1;
Database: real_estate_rentals (tables: properties)
Question: What is the id of the property that had the lowest requested price from the vendor, and what was that price?
SQL: SELECT property_id ,  vendor_requested_price FROM Properties ORDER BY vendor_requested_price LIMIT 1;
Database: real_estate_rentals (tables: properties)
Question: On average, how many rooms does a property have?
SQL: SELECT avg(room_count) FROM Properties;
Database: real_estate_rentals (tables: properties)
Question: What is the average number of rooms in a property?
SQL: SELECT avg(room_count) FROM Properties;
Database: real_estate_rentals (tables: rooms)
Question: How many kinds of room sizes are listed?
SQL: SELECT count(DISTINCT room_size) FROM Rooms;
Database: real_estate_rentals (tables: rooms)
Question: Return the number of different room sizes.
SQL: SELECT count(DISTINCT room_size) FROM Rooms;
Database: real_estate_rentals (tables: user_searches)
Question: What are the ids of users who have searched at least twice, and what did they search?
SQL: SELECT search_seq ,  user_id FROM User_Searches GROUP BY user_id HAVING count(*) >= 2;
Database: real_estate_rentals (tables: user_searches)
Question: Return the ids of users who have performed two or more searches, as well as their search sequence.
SQL: SELECT search_seq ,  user_id FROM User_Searches GROUP BY user_id HAVING count(*) >= 2;
Database: real_estate_rentals (tables: user_searches)
Question: When was the time of the latest search by a user?
SQL: SELECT max(search_datetime) FROM User_Searches;
Database: real_estate_rentals (tables: user_searches)
Question: What was the time of the most recent search?
SQL: SELECT max(search_datetime) FROM User_Searches;
Database: real_estate_rentals (tables: user_searches)
Question: What are all the user searches time and content? Sort the result descending by content.
SQL: SELECT search_datetime ,  search_string FROM User_Searches ORDER BY search_string DESC;
Database: real_estate_rentals (tables: user_searches)
Question: Return the search strings and corresonding time stamps for all user searches, sorted by search string descending.
SQL: SELECT search_datetime ,  search_string FROM User_Searches ORDER BY search_string DESC;