Database: disney
Question: How many movies were released between 1937 and 1950?
Evidence: released between 1937 and 1950 refers to substr(release_date, length(release_date) - 1,length(release_date)) between '37' and '50';
SQL: SELECT COUNT(movie_title) FROM characters WHERE SUBSTR(release_date, LENGTH(release_date) - 1, LENGTH(release_date)) BETWEEN '37' AND '50'

Database: chicago_crime
Question: What is the percentage of crime cases that have been classified as "drug abuse" by the FBI and happened on the street?
Evidence: "Drug Abuse" is the title of crime; happened on the street refers to location_description = 'STREET';  percentage = Divide (Count(fbi_code_no where location_description = 'STREET'), Count(fbi_code_no)) * 100
SQL: SELECT CAST(SUM(CASE WHEN T2.title = 'Drug Abuse' AND T1.location_description = 'STREET' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.fbi_code_no) FROM Crime AS T1 INNER JOIN FBI_Code AS T2 ON T1.fbi_code_no = T2.fbi_code_no

Database: car_retails
Question: For Which order was the most profitable, please list the customer name of the order and the profit of the order.
Evidence: Most profitable order can be computed as MAX(MULTIPLY(quantityOrdered, SUBTRACT(priceEach, buyPrice)).
SQL: SELECT t3.customerName, (t1.priceEach - t4.buyPrice) * t1.quantityOrdered FROM orderdetails AS t1 INNER JOIN orders AS t2 ON t1.orderNumber = t2.orderNumber INNER JOIN customers AS t3 ON t2.customerNumber = t3.customerNumber INNER JOIN products AS t4 ON t1.productCode = t4.productCode GROUP BY t3.customerName, t1.priceEach, t4.buyPrice, t1.quantityOrdered ORDER BY (t1.priceEach - t4.buyPrice) * t1.quantityOrdered DESC LIMIT 1

Database: books
Question: How many orders does the book "O Xará" have?
Evidence: "O Xará" is the title of the book
SQL: SELECT COUNT(*) FROM book AS T1 INNER JOIN order_line AS T2 ON T1.book_id = T2.book_id WHERE T1.title = 'O Xará'

Database: retail_complains
Question: In reviews for the Eagle National Bank product, how many of the 5 star reviews where from Nashville, Tennessee?
Evidence: 5 star refers to Stars = 5; Nashville refers to city = 'Nashville'; Tennessee refers to state_abbrev = 'TN'
SQL: SELECT COUNT(T2.Stars) FROM district AS T1 INNER JOIN reviews AS T2 ON T1.district_id = T2.district_id WHERE T1.city = 'Nashville' AND T1.state_abbrev = 'TN' AND T2.Product = 'Eagle National Mortgage' AND T2.Stars = 5

Database: olympics
Question: Among the Olympic games held in Los Angeles, what is the name of the Olympic game that has the most number of competitors?
Evidence: Los Angeles refers to city_name = 'Lost Angeles'; the Olympic game refers to games_name; the most number of competitors refers to MAX(COUNT(games_name));
SQL: SELECT T1.games_name FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id INNER JOIN games_city AS T3 ON T2.games_id = T3.games_id INNER JOIN city AS T4 ON T3.city_id = T4.id WHERE T4.city_name = 'Los Angeles' GROUP BY T1.id ORDER BY COUNT(T2.person_id) DESC LIMIT 1

Database: authors
Question: What is the total number and conference's homepage of papers held in a conference with an ID of 187 during 1990 to 2000?
Evidence: Id of 187 refers to ConferenceId = 187; during 1990 to 2000 refers to Year BETWEEN 1990 and 2000; total number of conference refers to Count(ConferenceId = 187)
SQL: SELECT COUNT(T2.ConferenceId), T1.HomePage FROM Conference AS T1 INNER JOIN Paper AS T2 ON T1.Id = T2.ConferenceId WHERE T2.Year BETWEEN 1990 AND 2000 AND T2.ConferenceId = 187

Database: app_store
Question: What is the rating and the total Sentiment subjectivity score of "Onefootball - Soccer Scores"?
Evidence: Onefootball - Soccer Scores refers to App = 'Onefootball - Soccer Scores';
SQL: SELECT T1.Rating, SUM(T2.Sentiment_Subjectivity) FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = 'Onefootball - Soccer Scores'

Database: trains
Question: Among the trains running west, how many trains have three-wheeled, jagged roof cars?
Evidence: west is an direction; three-wheeled refers to wheels = 3; jagged roof refers to roof = 'jagged'
SQL: SELECT SUM(CASE WHEN T2.direction = 'west' THEN 1 ELSE 0 END)as count FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.wheels = 3 AND T1.roof = 'jagged'

Database: european_football_1
Question: How many home victories does the Bundesliga division have in more or less than the Premier League division in the 2021 season?
Evidence: Bundesliga and the Premier League are names of division; home victories refer to FTR = 'H', where H stands for home victory; SUBTRACT(COUNT(FTR = 'H' where season = 2021, name = 'Bundesliga'), COUNT(FTR = 'H' where season = 2021, name = 'Premier League'));
SQL: SELECT COUNT(CASE WHEN T2.name = 'Bundesliga' THEN 1 ELSE NULL END) - COUNT(CASE WHEN T2.name = 'Premier League' THEN 1 ELSE NULL END) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T1.season = 2021 AND T1.FTR = 'H'

Database: simpson_episodes
Question: Provide the episode number, title and rating of which were aired in October, 2008.
Evidence: episode number refers to episode; aired in refers to air_date; October refers to Substr(air_date, 6, 2) = '10': 2008 refers to Substr(air_date, 0, 5) = '2008'
SQL: SELECT episode, title, rating FROM Episode WHERE SUBSTR(air_date, 1, 7) LIKE '2008-10%';

Database: professional_basketball
Question: Please list the first names of the players with the most personal fouls in the 'NBL' league.
Evidence: "NBL" is the lgID; most  personal foul refers to Max(Count(PF))
SQL: SELECT T1.firstName FROM players AS T1 INNER JOIN players_teams AS T2 ON T1.playerID = T2.playerID WHERE T2.lgID = 'NBL' GROUP BY T1.playerID, T1.firstName ORDER BY COUNT(PF) DESC LIMIT 1

Database: university
Question: List the names of all the universities that have no less than 50,000 students in the year 2012.
Evidence: have no less than 50,000 students refers to num_students > 50000; name of university refers to university_name;
SQL: SELECT T2.university_name FROM university_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T1.num_students > 50000 AND T1.year = 2012

Database: mental_health_survey
Question: How many users, between the age 27 to 35, were surveyed in 2018?
Evidence: Age 27 to 35 refer to AnswerText 27 BETWEEN 35 where questionid = 1; 2018 refer to SurveyID


SQL: SELECT T1.UserID FROM Answer AS T1 INNER JOIN Question AS T2 ON T1.QuestionID = T2.questionid WHERE T1.SurveyID = 2018 AND T2.questionid = 1 AND T1.AnswerText BETWEEN '27' AND '35'

Database: cars
Question: What is the average price per car produced in Japan?
Evidence: produced in Japan refers to country = 'Japan'; average price per car = avg(price) where country = 'Japan'
SQL: SELECT AVG(T1.price) FROM price AS T1 INNER JOIN production AS T2 ON T1.ID = T2.ID INNER JOIN country AS T3 ON T3.origin = T2.country WHERE T3.country = 'Japan'

Database: video_games
Question: State the name of the publisher with the most games.
Evidence: name of publisher refers to publisher_name; the most games refers to max(game_id)
SQL: SELECT T.publisher_name FROM ( SELECT T2.publisher_name, COUNT(DISTINCT T1.game_id) FROM game_publisher AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id GROUP BY T2.publisher_name ORDER BY COUNT(DISTINCT T1.game_id) DESC LIMIT 1 ) t

Database: movie_3
Question: In children's movies, which was rented the most?
Evidence: children is the name of the category; rented the most refers to MAX(COUNT(title))
SQL: SELECT T.title FROM ( SELECT T4.title, COUNT(T4.title) AS num FROM customer AS T1 INNER JOIN rental AS T2 ON T1.customer_id = T2.customer_id INNER JOIN inventory AS T3 ON T2.inventory_id = T3.inventory_id INNER JOIN film AS T4 ON T3.film_id = T4.film_id INNER JOIN film_category AS T5 ON T4.film_id = T5.film_id INNER JOIN category AS T6 ON T5.category_id = T6.category_id WHERE T6.name = 'Children' GROUP BY T4.title ) AS T ORDER BY T.num DESC LIMIT 1