Database: shooting
Question: What is the proportion of white males and females in the police force?
Evidence: white refers to race = 'W'; male refers to gender = 'M'; female refers to gender = 'F'; proportion of white males = divide(count(officers where race = 'W' and gender = 'M'), count(officers)) * 100%; proportion of white females = divide(count(officers where race = 'W' and gender = 'F'), count(officers)) * 100%
SQL: SELECT CAST(SUM(gender = 'M') AS REAL) / SUM(gender = 'F') FROM officers WHERE race = 'W'

Database: bike_share_1
Question: Please calculate the average temperature of those trips that started at Market at 4th in 2013.
Evidence: started at refers to start_station_name; start station_name = 'Market at 4th'; in 2013 refers to start_date like '%2013%'; temperature refers to mean_temperature_f; average temperature = DIVIDE(SUM(mean_temperature_f), COUNT(mean_temperature_f));
SQL: SELECT AVG(T2.mean_temperature_f) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE SUBSTR(CAST(T2.date AS TEXT), -4) = '2013' AND T1.start_station_name = 'Market at 4th'

Database: social_media
Question: Please list the texts of all the tweets that are reshared.
Evidence: reshared refers to Isreshare = 'TRUE'
SQL: SELECT text FROM twitter WHERE IsReshare = 'TRUE'

Database: movie_3
Question: List at least 10 films that the customers can rent for more than 5 days.
Evidence: film refers to title; rent for more than 5 days refers to rental_duration > 5
SQL: SELECT T.title FROM ( SELECT T1.title, COUNT(T3.customer_id) AS num FROM film AS T1 INNER JOIN inventory AS T2 ON T1.film_id = T2.film_id INNER JOIN rental AS T3 ON T2.inventory_id = T3.inventory_id WHERE T1.rental_duration > 5 GROUP BY T1.title ) AS T WHERE T.num > 10

Database: language_corpus
Question: Which word that has 71303 appearance in the Catalan language?
Evidence: has 71303 appearance refers to occurrences = 71303;
SQL: SELECT T1.word FROM words AS T1 INNER JOIN langs_words AS T2 ON T1.wid = T2.wid WHERE T2.occurrences = 71303

Database: video_games
Question: Show the name of the earliest platform in the database.
Evidence: the earliest refers to min(release_year); name of platform refers to platform_name
SQL: SELECT T2.platform_name FROM game_platform AS T1 INNER JOIN platform AS T2 ON T1.platform_id = T2.id ORDER BY T1.release_year ASC LIMIT 1

Database: regional_sales
Question: Write down the store IDs and region of the state "Michigan".
Evidence: "Michigan" is the State
SQL: SELECT DISTINCT T2.StoreID, T1.Region FROM Regions AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StateCode = T1.StateCode WHERE T2.State = 'Michigan'

Database: public_review_platform
Question: Among the users who have posted more than 10 reviews, how many users are elite users?
Evidence: posted more than 10 reviews refers to count(Reviews.user_id) > 10
SQL: SELECT COUNT(T4.user_id) FROM ( SELECT T1.user_id FROM Users AS T1 INNER JOIN Elite AS T2 ON T1.user_id = T2.user_id INNER JOIN Reviews AS T3 ON T1.user_id = T3.user_id WHERE T3.user_id IS NOT NULL GROUP BY T3.user_id HAVING COUNT(T3.user_id) > 10 ) T4

Database: image_and_language
Question: What is the ID of the image with the most number of object samples?
Evidence: ID of the image refers to IMG_ID; most number of object samples refers to max(count(OBJ_SAMPLE_ID))
SQL: SELECT IMG_ID FROM IMG_OBJ GROUP BY IMG_ID ORDER BY COUNT(OBJ_SAMPLE_ID) DESC LIMIT 1

Database: codebase_comments
Question: What is the task of method number 2?
Evidence: task of the method refers to the second part of the Name after the "."; method number refers to Method_100k.Id; Method_100k.Id = 2;
SQL: SELECT SUBSTR(SUBSTR(Name, INSTR(Name, '.') + 1), 1, INSTR(SUBSTR(Name, INSTR(Name, '.') + 1), '.') - 1) task FROM Method WHERE Id = 2

Database: beer_factory
Question: Which root beer got the most five stars in 2012? Give the brand name of this beer.
Evidence: most five stars refers to MAX(COUNT(StarRating = 5)); in 2012 refers to FirstBrewedYear = 2012;
SQL: SELECT T3.BrandName FROM rootbeer AS T1 INNER JOIN rootbeerreview AS T2 ON T1.BrandID = T2.BrandID INNER JOIN rootbeerbrand AS T3 ON T1.BrandID = T3.BrandID WHERE T2.StarRating = 5 AND strftime('%Y', T2.ReviewDate) = '2012' GROUP BY T1.BrandID ORDER BY COUNT(T2.BrandID) DESC LIMIT 1

Database: disney
Question: List the voice actors from the movie "Meet the Robinsons".
Evidence: Meet the Robinsons refers to movie_title = 'Meet the Robinsons';
SQL: SELECT 'voice-actor' FROM `voice-actors` WHERE movie = 'Meet the Robinsons'

Database: retail_world
Question: Indicate the name of the categories to which the products of order number 10933 belong.
Evidence: order number 10933 refers to OrderID = 10933;
SQL: SELECT T3.CategoryName FROM Products AS T1 INNER JOIN `Order Details` AS T2 ON T1.ProductID = T2.ProductID INNER JOIN Categories AS T3 ON T1.CategoryID = T3.CategoryID WHERE T2.OrderID = 10933

Database: professional_basketball
Question: Which team(s) has greater than 75% lost among all the games played.
Evidence: greater than 75% lost refers to Divide(lost, games) > 0.75; team refers to tmID
SQL: SELECT name FROM teams WHERE CAST(lost AS REAL) * 100 / games > 75

Database: codebase_comments
Question: What is the language of method number 28 that can be found in the repository number 3?
Evidence: language of method refers to Lang; method number refers to Method_100k.Id; Method_100k.Id = 28; repository number refers to RepoId; RepoID = 3;
SQL: SELECT T2.Lang FROM Solution AS T1 INNER JOIN Method AS T2 ON T1.Id = T2.SolutionId WHERE T2.Id = 28 AND T1.RepoId = 3

Database: retails
Question: List by order number the 3 items with the lowest price after applying the discount.
Evidence: order number refers to l_orderkey; the lowest price after applying the discount refers to MIN(MULTIPLY(l_extendedprice), SUBTRACT(1, l_discount));
SQL: SELECT l_orderkey FROM lineitem ORDER BY l_extendedprice * (1 - l_discount) LIMIT 3

Database: software_company
Question: What is the number of inhabitants and income of geographic identifier 239?
Evidence: geographic identifier 239 refers to GEOID = 239; number of inhabitants refers to INHABITANTS_K; income refers to INCOME_K;
SQL: SELECT INHABITANTS_K FROM Demog WHERE GEOID = 239

Database: car_retails
Question: Who is the sales agent of the distinct customer who paid the highest amount in the year 2004?
SQL: SELECT DISTINCT T3.lastName, T3.firstName FROM payments AS T1 INNER JOIN customers AS T2 ON T1.customerNumber = T2.customerNumber INNER JOIN employees AS T3 ON T2.salesRepEmployeeNumber = T3.employeeNumber WHERE STRFTIME('%Y', T1.paymentDate) = '2004' ORDER BY T1.amount DESC LIMIT 1

Database: shipping
Question: How many shipments in 2017 were done by Sue Newell?
Evidence: in 2017 refers to Cast(ship_date AS DATE) = 2017
SQL: SELECT COUNT(*) FROM shipment AS T1 INNER JOIN driver AS T2 ON T1.driver_id = T2.driver_id WHERE STRFTIME('%Y', T1.ship_date) = '2017' AND T2.first_name = 'Sue' AND T2.last_name = 'Newell'

Database: movielens
Question: List the different director IDs of the movies whose user rating is more than 4.
SQL: SELECT DISTINCT T2.directorid FROM u2base AS T1 INNER JOIN movies2directors AS T2 ON T1.movieid = T2.movieid WHERE T1.rating > 4

Database: regional_sales
Question: List out the product name of order which has unit cost of 781.22.
SQL: SELECT T FROM ( SELECT DISTINCT IIF(T1.`Unit Cost` = 781.22, T2.`Product Name`, NULL) AS T FROM `Sales Orders` T1 INNER JOIN Products T2 ON T2.ProductID = T1._ProductID ) WHERE T IS NOT NULL

Database: language_corpus
Question: What is the title of Catalan language wikipedia page with revision page id '16203226'?
Evidence: revision page id '16203226' refers to revision = 16203226
SQL: SELECT title FROM pages WHERE revision = 16203226