Database: beer_factory
Question: Among the root beer purchased in 2014, what percentage were sold in cans?
Evidence: in 2014 refers to PurchaseDate > = '2014-01-01' AND PurchaseDate < = '2014-12-31'; percentage = MULTIPLY(DIVIDE(SUM(ContainerType = 'Can'), COUNT(RootBeerID) WHERE PurchaseDate > = '2014-01-01' AND PurchaseDate < = '2014-12-31'), 1.0); in cans refers to ContainerType = 'Can';
SQL: SELECT CAST(COUNT(CASE WHEN ContainerType = 'Can' THEN RootBeerID ELSE NULL END) AS REAL) * 100 / COUNT(RootBeerID) FROM rootbeer WHERE PurchaseDate LIKE '2014%'

Database: menu
Question: Name the dishes that were on the menu page ID 174.
Evidence: FALSE;
SQL: SELECT T2.name FROM MenuItem AS T1 INNER JOIN Dish AS T2 ON T2.id = T1.dish_id WHERE T1.menu_page_id = 174

Database: chicago_crime
Question: Who was the alderman of the legislative district where case No. JB103470 took place? Give the full name.
Evidence: case No. JB103470 refers to case_number = 'JB103470'; full name refers to alderman_first_name, alderman_last_name
SQL: SELECT T1.alderman_first_name, T1.alderman_last_name FROM Ward AS T1 INNER JOIN Crime AS T2 ON T1.ward_no = T2.ward_no WHERE T2.case_number = 'JB103470'

Database: airline
Question: Tell the number of flights that landed at Lake Charles Regional Airport on 2018/8/15.
Evidence: landed at refers to DEST; Lake Charles Regional Airport refers to Description = 'Lake Charles, LA: Lake Charles Regional'; on 2018/8/15 refers to FL_DATE = '2018/8/15';
SQL: SELECT COUNT(T1.Code) FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.DEST WHERE T2.FL_DATE = '2018/8/15' AND T1.Description = 'Lake Charles, LA: Lake Charles Regional'

Database: sales
Question: List down the product name for products from id 1 to 10.
Evidence: products from id 1 to 10 refers to ProductID BETWEEN 1 AND 10;
SQL: SELECT Name FROM Products WHERE ProductID BETWEEN 1 AND 10

Database: simpson_episodes
Question: What is the ratio between the 5 stars votes for 'No Loan Again, Naturally' episode and 'Coming to Homerica' episode?
Evidence: 5 stars votes refers to stars = 5 ; 'No Loan Again, Naturally' and 'Coming to Homerica' are both the title of episode;  ratio = Divide (votes where title = 'No Loan Again, Naturally', votes where title = 'Coming to Homerica')
SQL: SELECT CAST(SUM(CASE WHEN T1.title = 'No Loan Again, Naturally' THEN T1.votes ELSE 0 END) AS REAL) / SUM(CASE WHEN T1.title = 'Coming to Homerica' THEN T1.votes ELSE 0 END) AS ratio FROM Episode AS T1 INNER JOIN Vote AS T2 ON T2.episode_id = T1.episode_id WHERE T2.stars = 5;

Database: chicago_crime
Question: What is the neighborhood name in the community area of Lake View?
Evidence: "Lake View" is the community_area_name
SQL: SELECT T2.neighborhood_name FROM Community_Area AS T1 INNER JOIN Neighborhood AS T2 ON T1.community_area_no = T2.community_area_no WHERE T1.community_area_name = 'Lake View'

Database: cookbook
Question: Is the ingredient "graham cracker crumbs" optional in the recipe "Raspberry Chiffon Pie"?
Evidence: 'graham cracker crumbs' is a name of an ingredient; 'Raspberry Chiffon Pie' refers to title
SQL: SELECT T2.optional FROM Recipe AS T1 INNER JOIN Quantity AS T2 ON T1.recipe_id = T2.recipe_id INNER JOIN Ingredient AS T3 ON T3.ingredient_id = T2.ingredient_id WHERE T1.title = 'Raspberry Chiffon Pie' AND T3.name = 'graham cracker crumbs'

Database: soccer_2016
Question: How many players got out in the first inning of match ID "548335"?
Evidence: got out refers to Player_Out; the first inning refers to Innings_No = 1; match ID "548335" refers to Match_Id = 548335
SQL: SELECT SUM(CASE WHEN Match_Id = 548335 THEN 1 ELSE 0 END) FROM Wicket_Taken WHERE Innings_No = 1

Database: food_inspection_2
Question: How much did Hacienda Los Torres from ward 36 fine for failing an inspection?
Evidence: Hacienda Los Torres refers to dba_name = 'HACIENDA LOS TORRES'; ward 36 refers to ward = 36; failing an inspection refers to results = 'Fail';
SQL: SELECT SUM(T3.fine) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no INNER JOIN violation AS T3 ON T2.inspection_id = T3.inspection_id WHERE T1.dba_name = 'HACIENDA LOS TORRES' AND T1.ward = 36 AND T2.results = 'Fail'

Database: public_review_platform
Question: How many 2 stars rated business located in Phoenix, Arizona?
Evidence: located in Phoenix refers to city = 'Phoenix'; Arizona refers to state = 'AZ'
SQL: SELECT COUNT(business_id) FROM Business WHERE city = 'Phoenix' AND state = 'AZ' AND stars = 2

Database: movie_3
Question: What is Mary Smith's rental ID?
SQL: SELECT T2.rental_id FROM customer AS T1 INNER JOIN rental AS T2 ON T1.customer_id = T2.customer_id WHERE T1.first_name = 'MARY' AND T1.last_name = 'SMITH'

Database: legislator
Question: Provide the facebook ID of the facebook account named "RepWilson".
SQL: SELECT facebook_id FROM `social-media` WHERE facebook = 'RepWilson'

Database: sales
Question: How many free or gift products are there?
Evidence: free gift refers to Price = 0;
SQL: SELECT COUNT(ProductID) FROM Products WHERE Price = 0

Database: social_media
Question: Calculate the average number of male users who posted tweets in a week.
Evidence: male user refers to Gender = 'Male'; average tweet in a week per user refers to Divide ( Divide(Count(TweetID), Count (UserID)), Divide(31, 7))
SQL: SELECT COUNT(DISTINCT T1.TweetID) / COUNT(DISTINCT T1.UserID) / 7 AS avg FROM twitter AS T1 INNER JOIN user AS T2 ON T1.UserID = T2.UserID WHERE T2.Gender = 'Male' AND T1.Day BETWEEN 1 AND 31

Database: professional_basketball
Question: In year 2000, who are the coaches with more than 50 games won. List the coachID, team name and number of game won at home game.
Evidence: more than 50 games won refers to won > 50
SQL: SELECT T1.coachID, T2.name, T2.won FROM coaches AS T1 INNER JOIN teams AS T2 ON T1.tmID = T2.tmID AND T1.year = T2.year WHERE T1.year = 2000 AND T2.won > 50

Database: world_development_indicators
Question: How many countries uses the 1968 System of National Accounts methodology?
Evidence: uses the 1968 System of National Accounts methodology refers to SystemOfNationalAccounts = '1968 System of National Accounts methodology'
SQL: SELECT COUNT(CountryCode)  FROM Country WHERE SystemOfNationalAccounts = 'Country uses the 1968 System of National Accounts methodology.'

Database: language_corpus
Question: How many word appeared 8 times? State the language id of the page.
Evidence: appeared 8 times refers to occurrences = 8;
SQL: SELECT COUNT(T2.wid), T1.lid FROM pages AS T1 INNER JOIN pages_words AS T2 ON T1.pid = T2.pid WHERE T2.occurrences = 8

Database: professional_basketball
Question: Find the full name of the player born in Atlanta and have the highest number of blocks. Also, in which team did this player perform the most number of blocks?
Evidence: full name refers to first_name, middle_name, last_name; born in Atlanta refers to birthCity = 'Atlanta'; the highest number of blocks refers to max(blocks); team refers to tmID
SQL: SELECT T1.firstName, T1.lastName, T2.tmID FROM players AS T1 INNER JOIN players_teams AS T2 ON T1.playerID = T2.playerID WHERE T1.birthCity = 'Atlanta' ORDER BY T2.blocks DESC LIMIT 1

Database: software_company
Question: How many of the customer's reference ID that has a TRUE response?
Evidence: reference ID refers to REFID;
SQL: SELECT COUNT(REFID) FROM Mailings1_2 WHERE RESPONSE = 'true'

Database: student_loan
Question: How many students enlisted in the navy?
Evidence: navy refers to organ = 'navy';
SQL: SELECT COUNT(name) FROM enlist WHERE organ = 'navy'

Database: law_episode
Question: Write down the organization, year, award, and award category in which Rene Balcer is the winner.
Evidence: Rene Balcer refers to name = 'Rene Balcer'; the winner refers to result = 'Winner'
SQL: SELECT T2.organization, T2.year, T2.award, T2.award_category FROM Person AS T1 INNER JOIN Award AS T2 ON T1.person_id = T2.person_id WHERE T1.name = 'Rene Balcer' AND T2.result = 'Winner'