Database: language_corpus
Question: What are the total occurence of words that paired with "nombre"?
Evidence: total occurrence refers to sum(occurrences); paired with "nombre" refers to w1st.word = "nombre" or w2nd.word = "nombre"
SQL: SELECT SUM(T2.occurrences) FROM words AS T1 INNER JOIN biwords AS T2 ON T1.wid = T2.w1st OR T1.wid = T2.w2nd WHERE T2.w1st IN (( SELECT wid FROM words WHERE word = 'nombre' ) OR T2.w2nd IN ( SELECT wid FROM words WHERE word = 'nombre' ))

Database: talkingdata
Question: How many events were there on 30th April, 2016?
Evidence: on 30th April, 2016 refers to `timestamp` LIKE '2016-04-30%';
SQL: SELECT COUNT(event_id) FROM events WHERE SUBSTR(`timestamp`, 1, 10) = '2016-04-30'

Database: sales
Question: Calculate the revenue produced through sales of HL Road Frame - Red, 56.
Evidence: revenue = MULTIPLY(SUM(Quantity, Price)); 'HL Road Frame - Red, 56' is name of product;
SQL: SELECT SUM(T2.Quantity * T1.Price) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Name = 'HL Road Frame - Red, 56'

Database: university
Question: How many universities had over 30000 students in 2011?
Evidence: in 2011 refers to year 2011; had over 30000 students refers to num_students > 30000;
SQL: SELECT COUNT(*) FROM university_year WHERE year = 2011 AND num_students > 30000

Database: simpson_episodes
Question: How many awards had been held in 2009?
Evidence: had been held in 2009 refers to year = 2009
SQL: SELECT COUNT(award_id) FROM Award WHERE SUBSTR(year, 1, 4) = '2009';

Database: simpson_episodes
Question: Find the winning rate of award in 2010. Describe the winner name, award name, episode title and role of the winner in that episode.
Evidence: in 2010 refers to year = 2010; winning rate refers to DIVIDE(COUNT(result = 'winner'), COUNT(*));
SQL: SELECT T3.rate, T4.person, T4.award, T5.title, T4.role FROM ( SELECT CAST(SUM(CASE WHEN T1.result = 'Winner' THEN 1 ELSE 0 END) AS REAL) / SUM(CASE WHEN T1.result IN ('Winner', 'Nominee') THEN 1 ELSE 0 END) AS rate , T1.person, T1.award, T2.title, T1.role FROM Award AS T1 INNER JOIN Episode AS T2 ON T1.episode_id = T2.episode_id WHERE SUBSTR(T1.year, 1, 4) = '2010' ) AS T3 INNER JOIN Award AS T4 INNER JOIN Episode AS T5 ON T4.episode_id = T5.episode_id WHERE T4.year = 2010 AND T4.result = 'Winner';

Database: professional_basketball
Question: How many players received Rookie of the Year award from 1969 to 2010?
Evidence: from 1969 to 2010 refers to year BETWEEN 1969 and 2010; 'Rookie of the Year' is the award
SQL: SELECT COUNT(playerID) FROM awards_players WHERE year BETWEEN 1969 AND 2010 AND award = 'Rookie of the Year'

Database: regional_sales
Question: What is the detailed coordinates of the store where the product with the 4th highest profit were purchased from?
Evidence: detailed coordinates refers to Latitude, Longitude; highest net profit = Max(Subtract(Unit Price, Unit Cost))
SQL: SELECT T2.Latitude, T2.Longitude FROM `Sales Orders` AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StoreID = T1._StoreID ORDER BY REPLACE(T1.`Unit Price`, ',', '') - REPLACE(T1.`Unit Cost`, ',', '') DESC LIMIT 3, 1

Database: chicago_crime
Question: How many aldermen have "James" as their first name?
SQL: SELECT COUNT(*) FROM Ward WHERE alderman_first_name = 'James'

Database: european_football_1
Question: For the Ligue 2 game that made the most goals, who is the winner of that game?
Evidence: Ligue 2 is the name of division; the most goals refer to MAX(SUM(FTHG, FTAG)) which are short names for Final-time Home-team Goals and Final-time Away-team Goals; winner refers to FTR = 'A';
SQL: SELECT CASE WHEN T1.FTR = 'H' THEN T1.HomeTeam ELSE T1.AwayTeam END WINNER FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Ligue 2' ORDER BY T1.FTAG + T1.FTHG DESC LIMIT 1

Database: public_review_platform
Question: What is the category of the business with short review length and highest review stars within business ID from 7 to 14?
Evidence: category of the business refers to category_name; short review length refers to review_length = 'Short'; the highest star rating refers to MAX(stars); business ID from 7 to 14 refers to business_id BETWEEN 7 AND 14
SQL: SELECT DISTINCT T3.category_name FROM Reviews AS T1 INNER JOIN Business_Categories AS T2 ON T1.business_id = T2.business_id INNER JOIN Categories AS T3 ON T2.category_id = T3.category_id WHERE T2.business_id >= 7 AND T2.business_id < 15 AND T1.review_length = 'Short' AND T1.review_stars = ( SELECT MAX(review_stars) FROM Reviews )

Database: retail_world
Question: Calculate the percentage of shipping done through Speedy Express.
Evidence: through Speedy Express refers to CompanyName = 'Speedy Express'; percentage = divide(count(ShipperID where CompanyName = 'Speedy Express') , count(ShipperID)) * 100%
SQL: SELECT CAST(COUNT(CASE WHEN T2.CompanyName = 'Speedy Express' THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T1.ShipVia) FROM Orders AS T1 INNER JOIN Shippers AS T2 ON T1.ShipVia = T2.ShipperID

Database: beer_factory
Question: What is the phone number of the customer who owns the credit card of number 6011179359005380?
Evidence: FALSE;
SQL: SELECT DISTINCT T1.PhoneNumber FROM customers AS T1 INNER JOIN `transaction` AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.CreditCardNumber = 6011179359005382

Database: address
Question: Provide the zip codes, cities, and locations of the postal points that have Shared Reshipper as a bad alias.
Evidence: latitude and longitude coordinates can be used to identify the location;
SQL: SELECT T1.zip_code, T2.city, T2.latitude, T2.longitude FROM avoid AS T1 INNER JOIN zip_data AS T2 ON T1.zip_code = T2.zip_code WHERE T1.bad_alias = 'Shared Reshipper'

Database: cookbook
Question: How many calories does the turkey tenderloin bundles recipe have?
Evidence: turkey tenderloin refers to title
SQL: SELECT T2.calories FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id WHERE T1.title = 'Turkey Tenderloin Bundles'

Database: legislator
Question: In California, how many representatives ended their term in 1995?
Evidence: California refers to state = 'CA'; representatives refers to type = 'rep'; ended the term in 1995 refers to end like '1995%';
SQL: SELECT COUNT(*) FROM `current-terms` WHERE state = 'CA' AND type = 'rep' AND end LIKE '1995%'

Database: talkingdata
Question: How many users who are between 20 and 60 use phone brand of TCL?
SQL: SELECT COUNT(T1.device_id) FROM gender_age AS T1 INNER JOIN phone_brand_device_model2 AS T2 ON T1.device_id = T2.device_id WHERE T1.age BETWEEN 20 AND 60 AND T2.phone_brand = 'TCL'

Database: app_store
Question: What is the average download for entertainment apps with size no more than 1.0 M?
Evidence: downloads and installs are synonyms; entertainment apps refers to Category = 'ENTERTAINMENT';
SQL: SELECT AVG(CAST(REPLACE(REPLACE(Installs, ',', ''), '+', '') AS INTEGER)) FROM playstore WHERE Category = 'ENTERTAINMENT' AND Size < '1.0M'

Database: professional_basketball
Question: In 1975, what was the average point of all-star players coming from the USA?
Evidence: in 1975 refers to season_id = 1975; coming from USA refers to birthCountry = 'USA'; average point refers to Divide (Sum(points), Count(playerID))
SQL: SELECT AVG(T2.points) FROM players AS T1 INNER JOIN player_allstar AS T2 ON T1.playerID = T2.playerID WHERE T2.season_id = 1975

Database: bike_share_1
Question: What is the longest duration for a bike trip starting on a day with a fog in 2013?
Evidence: longest duration refers to MAX(duration); starting on a day with a fog refers to start_date where events = 'fog'; in 2013 refers to date LIKE '%2013';
SQL: SELECT MAX(T1.duration) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T2.date LIKE '%2013%' AND T2.events = 'Fog' AND T2.zip_code = 94107