Database: olympics
Question: Which summer Olympic have the highest and lowest number of participants?
Evidence: the highest number of participants refers to MAX(COUNT(person_id)); the lowest number of participants refers to MIN(COUNT(person_id)); Which summer Olympic refers to games_name where season = 'Summer';
SQL: SELECT ( SELECT T1.games_name FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id WHERE T1.season = 'Summer' GROUP BY T1.games_year ORDER BY COUNT(T2.person_id) DESC LIMIT 1 ) AS HIGHEST , ( SELECT T1.games_name FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id WHERE T1.season = 'Summer' GROUP BY T1.games_year ORDER BY COUNT(T2.person_id) LIMIT 1 ) AS LOWEST

Database: address
Question: Calculate the average of 2020's population in each zip code.
Evidence: average of 2020 population in each zip code refers to Divide (Sum(population_2020), Count(zip_code))
SQL: SELECT CAST(SUM(population_2020) AS REAL) / COUNT(zip_code) FROM zip_data

Database: shakespeare
Question: Among the history works written by Shakespeare, how many works whose 1st acts have no more than 2 scenes?
Evidence: history refers to GenreType = 'History' ; 1st acts  refers to Act = 1; no more than 2 scenes refers to count(Scene) < 2
SQL: SELECT COUNT(DISTINCT T2.work_id) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id WHERE T2.Act = 1 AND T2.Scene < 2 AND T1.GenreType = 'History'

Database: books
Question: Provide the full name of the customers who have ordered the book The Sorrows of Young Werther.
Evidence: full name refers to first_name, last_name; 'The Sorrows of Young Werther' is the title of the book
SQL: SELECT T4.first_name, T4.last_name FROM book AS T1 INNER JOIN order_line AS T2 ON T1.book_id = T2.book_id INNER JOIN cust_order AS T3 ON T3.order_id = T2.order_id INNER JOIN customer AS T4 ON T4.customer_id = T3.customer_id WHERE T1.title = 'The Sorrows of Young Werther'

Database: authors
Question: Please list all of the associations that the authors of the paper "FIBER: A Generalized Framework for Auto-tuning Software" are affiliated with.
Evidence: paper "FIBER: A Generalized Framework for Auto-tuning Software" refers to Title = 'FIBER: A Generalized Framework for Auto-tuning Software'; are affiliated with refers to Affiliation
SQL: SELECT DISTINCT T2.Affiliation FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Title = 'FIBER: A Generalized Framework for Auto-tuning Software'

Database: social_media
Question: Please give the user ID of the user who has posted the most tweets.
Evidence: users with the most tweet refers to UserID where Max(Count (TweetID))
SQL: SELECT UserID FROM twitter GROUP BY UserID ORDER BY COUNT(DISTINCT TweetID) DESC LIMIT 1

Database: regional_sales
Question: List out the city name of states located in South region.
SQL: SELECT T FROM ( SELECT DISTINCT CASE  WHEN T1.Region = 'South' THEN T2.`City Name` END AS T FROM Regions T1 INNER JOIN `Store Locations` T2 ON T2.StateCode = T1.StateCode ) WHERE T IS NOT NULL

Database: retails
Question: How many countries belong to the Algeria region?
Evidence: the algeria region refers to r_name = 'ALGERIA'
SQL: SELECT COUNT(T1.r_name) FROM region AS T1 INNER JOIN nation AS T2 ON T1.r_regionkey = T2.n_regionkey WHERE T2.n_name = 'ALGERIA'

Database: food_inspection_2
Question: What is the establishment's name and employee involved in the inspection ID 44256 on May 5, 2010?
Evidence: establishment's name refers to dba_name; employee name refers to first_name, last_name; inspection ID 44256 refers to inspection_id = 44256; on May 5, 2010 refers to inspection_date = '2010-05-05'
SQL: SELECT T1.dba_name, T3.first_name, T3.last_name FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no INNER JOIN employee AS T3 ON T2.employee_id = T3.employee_id WHERE T2.inspection_date = '2010-05-05' AND T2.inspection_id = 44256

Database: address
Question: Indicate the name of the congressman represent in Guanica.
Evidence: name of congressman implies full name which refers to first_name, last_name; Guanica is the city;
SQL: SELECT T3.first_name, T3.last_name FROM zip_data AS T1 INNER JOIN zip_congress AS T2 ON T1.zip_code = T2.zip_code INNER JOIN congress AS T3 ON T2.district = T3.cognress_rep_id WHERE T1.city = 'Guanica'

Database: movie_platform
Question: Where can I find the movie list "Short and pretty damn sweet"?
Evidence: Short and pretty damn sweet is list_title; location of the movie refers to list_url;
SQL: SELECT list_url FROM lists WHERE list_title = 'Short and pretty damn sweet'

Database: authors
Question: List all the titles and their publishing journals from the 60's.
Evidence: from the 60’s refer to Year 1960 BETWEEN 1970


SQL: SELECT T1.Title, T1.JournalId FROM Paper AS T1 INNER JOIN Journal AS T2 ON T1.JournalId = T2.Id WHERE T1.Year >= 1960 AND T1.Year <= 1970

Database: movie_3
Question: Write down the inventories' IDs and actors' names of "STREETCAR INTENTIONS".
Evidence: "STREETCAR INTENTIONS" is the title of film; actor's names refers to first_name, last_name
SQL: SELECT T4.inventory_id, T1.first_name, T1.last_name FROM actor AS T1 INNER JOIN film_actor AS T2 ON T1.actor_id = T2.actor_id INNER JOIN film AS T3 ON T2.film_id = T3.film_id INNER JOIN inventory AS T4 ON T2.film_id = T4.film_id WHERE T3.title = 'STREETCAR INTENTIONS'

Database: retails
Question: Please list the names of all the suppliers for parts under Brand#55.
Evidence: supplier name refers to s_name; Brand#55 refers to p_brand = 'Brand#55'
SQL: SELECT T3.s_name FROM part AS T1 INNER JOIN partsupp AS T2 ON T1.p_partkey = T2.ps_partkey INNER JOIN supplier AS T3 ON T2.ps_suppkey = T3.s_suppkey WHERE T1.p_brand = 'Brand#55'

Database: public_review_platform
Question: How many compliments received from medium users that Phoenix city achieved?
Evidence: medium refers to number_of_compliments = 'Medium';
SQL: SELECT COUNT(T1.number_of_compliments) FROM Users_Compliments AS T1 INNER JOIN Reviews AS T2 ON T1.user_id = T2.user_id INNER JOIN Business AS T3 ON T2.business_id = T3.business_id WHERE T3.city LIKE 'Phoenix' AND T1.number_of_compliments LIKE 'Medium'

Database: professional_basketball
Question: In the year 1997 allstar game, which teams did the players had the most rebounds play in? List their team ids.
Evidence: in 1997 refers to year = 1997; the most rebounds refers to max(rebounds); team id refers to tmID
SQL: SELECT T2.tmID FROM players_teams AS T1 INNER JOIN teams AS T2 ON T1.tmID = T2.tmID AND T1.year = T2.year INNER JOIN player_allstar AS T3 ON T3.playerID = T1.playerID WHERE T3.season_id = 1997 ORDER BY T1.rebounds DESC LIMIT 1

Database: language_corpus
Question: Which word has the time of occurrences as 340691?
Evidence: occurrences of 340691 refers to occurrences = 340691
SQL: SELECT word FROM words WHERE occurrences = 340691

Database: app_store
Question: What is the number of neutral comments from all the weather apps?
Evidence: neutral comments refers to Sentiment = 'Neutral'; weather app refers to Genre = 'Weather';
SQL: SELECT COUNT(T2.Sentiment) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.Genres = 'Weather' AND T2.Sentiment = 'Neutral'

Database: retail_world
Question: What is the full name of the employee who is in charge of the territory of Denver?
Evidence: full name refers to FirstName, LastName; Denver is a TerritoryDescription
SQL: SELECT T1.FirstName, T1.LastName FROM Employees AS T1 INNER JOIN EmployeeTerritories AS T2 ON T1.EmployeeID = T2.EmployeeID INNER JOIN Territories AS T3 ON T2.TerritoryID = T3.TerritoryID WHERE T3.TerritoryDescription = 'Denver'