Database: bike_share_1
Question: Which day in the month of November, 2014 have a foggy weather in the zip code 94301 and in total, how many bikes were borrowed by subscribers from all of the stations in the said day?
Evidence: day in the month of November, 2014 refers to start_date between '11/1/2014' and '11/30/2014'; foggy weather refers to events = 'Fog'; subscriber refers to subscription_type; all of the stations bikes were borrowed from refer to start_station_name;
SQL: SELECT T2.date, COUNT(T1.start_station_name) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T2.date LIKE '11/%/2014%' AND T2.zip_code = 94301 AND T2.events = 'Fog' AND T1.subscription_type = 'Subscriber'

Database: codebase_comments
Question: Please provide the path of solution of method whose tokenized name is html parser feed.
Evidence: path of solution refers to Path; method refers to Name; tokenized name refers to NameTokenized; Name = ''html parser feed';
SQL: SELECT T1.Path FROM Solution AS T1 INNER JOIN Method AS T2 ON T1.Id = T2.SolutionId WHERE T2.NameTokenized = 'html parser feed'

Database: shooting
Question: From the cases where the subject were deceased, list the subject's last name, gender, race and case number.
Evidence: subject were deceased refers to subject_statuses = 'Deceased'
SQL: SELECT T2.last_name, T2.gender, T2.race, T2.case_number FROM incidents AS T1 INNER JOIN subjects AS T2 ON T1.case_number = T2.case_number WHERE T1.subject_statuses = 'Deceased'

Database: professional_basketball
Question: How many players received Most Valuable Player award from 1969 to 1975?
Evidence: Most Valuable Player award refers to award = 'Most Valuable Player'; from 1969 to 1975 refers to year between 1969 and 1975
SQL: SELECT COUNT(DISTINCT playerID) FROM awards_players WHERE year BETWEEN 1969 AND 1975 AND award = 'Most Valuable Player'

Database: legislator
Question: How many current legislators have both accounts on both VoteView.com and maplight.org?
Evidence: have both accounts on both VoteView.com and maplight.org refers to icpsr_id is not null AND maplight_id is not null;
SQL: SELECT COUNT(*) FROM current WHERE icpsr_id IS NOT NULL AND maplight_id IS NOT NULL

Database: movies_4
Question: Accumulate the budget of the movie titles with the keyword of "video game".
Evidence: keyword of "video game" refers to keyword_name = 'video game'
SQL: SELECT SUM(T1.budget) FROM movie AS T1 INNER JOIN movie_keywords AS T2 ON T1.movie_id = T2.movie_id INNER JOIN keyword AS T3 ON T2.keyword_id = T3.keyword_id WHERE T3.keyword_name = 'video game'

Database: sales_in_weather
Question: What are the top 3 stations that have sold the highest quantities for an item in a single day?
Evidence: highest quantity refers to Max(units); station refers to station_nbr
SQL: SELECT T2.station_nbr FROM sales_in_weather AS T1 INNER JOIN relation AS T2 ON T1.store_nbr = T2.store_nbr ORDER BY T1.units DESC LIMIT 3

Database: soccer_2016
Question: Calculate the total winning match for Deccan Chargers.
Evidence: winning refers to Match_Winner; Deccan Chargers refers to Team_Name = 'Deccan Chargers'
SQL: SELECT SUM(T2.Match_Winner) FROM Team AS T1 INNER JOIN Match AS T2 ON T1.Team_Id = T2.Match_Winner WHERE T1.Team_Name = 'Deccan Chargers'

Database: human_resources
Question: What is the office phone number of the location at which Sandy Adams works?
Evidence: Sandy Adams is the fullname of an employee; full name = firstname, lastname;
SQL: SELECT T2.officephone FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T1.lastname = 'Adams' AND T1.firstname = 'Sandy'

Database: music_tracker
Question: Among the releases that were released in 2000, how many of them were released as an album and tagged "pop"?
Evidence: groupYear = 2000; album refers to releaseType;
SQL: SELECT COUNT(T1.groupName) FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T2.tag = 'pop' AND T1.releaseType = 'album' AND T1.groupYear = 2000

Database: chicago_crime
Question: Provide the occurrence date and location of the deceptive practice due to the unlawful use of recorded sound.
Evidence: location refers to latitude, longitude; deceptive practice refers to primary_description = 'DECEPTIVE PRACTICE'; unlawful use of recorded sound refers to secondary_description = 'UNLAWFUL USE OF RECORDED SOUND'
SQL: SELECT T2.date, T2.latitude, T2.longitude FROM IUCR AS T1 INNER JOIN Crime AS T2 ON T2.iucr_no = T1.iucr_no WHERE T1.primary_description = 'DECEPTIVE PRACTICE' AND T1.secondary_description = 'UNLAWFUL USE OF RECORDED SOUND'

Database: bike_share_1
Question: What is the average duration of a bike trip made on the day with the hottest temperature ever in 2014?
Evidence: average duration = DIVIDE(SUM(duration), COUNT(id)); hottest temperature refers to max_temperature_f; in 2014 refers to date LIKE '%2014';
SQL: SELECT AVG(T1.duration) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE T2.date LIKE '%2014%' AND T1.start_station_name = '2nd at Folsom' AND T2.max_temperature_f = ( SELECT max_temperature_f FROM weather ORDER BY max_temperature_f DESC LIMIT 1 )

Database: superstore
Question: Who is the customer from the East region that purchased the order with the highest profit?
Evidence: highest profit refers to MAX(profit); Region = 'East'
SQL: SELECT T2.`Customer Name` FROM east_superstore AS T1 INNER JOIN people AS T2 ON T1.`Customer ID` = T2.`Customer ID` WHERE T1.Region = 'East' ORDER BY T1.Profit DESC LIMIT 1

Database: food_inspection_2
Question: Calculate the percentage of inspections with the fine for a minor food safety problem.
Evidence: fine for a minor food safety problem refers to fine = 100; percentage = divide(count(inspection_id where fine = 100), sum(inspection_id)) * 100%
SQL: SELECT CAST(COUNT(CASE WHEN fine = 100 THEN inspection_id END) AS REAL) * 100 / COUNT(inspection_id) FROM violation

Database: restaurant
Question: In how many counties is there a street called Appian Way?
Evidence: a street called Appian Way refers to street_name = 'appian way'
SQL: SELECT COUNT(DISTINCT T2.county) FROM location AS T1 INNER JOIN geographic AS T2 ON T1.city = T2.city WHERE T1.street_name = 'appian way'

Database: books
Question: What is the most common domain for the email address among all the customers?
Evidence: most common domain for the email refers to Max(Count(SUBSTR(email, CHARINDEX('@', email) + 1, length(email) - charindex('@', email))))
SQL: SELECT SUBSTR(email, INSTR(email, '@') + 1, LENGTH(email) - INSTR(email, '@')) AS ym FROM customer GROUP BY SUBSTR(email, INSTR(email, '@') + 1, LENGTH(email) - INSTR(email, '@')) ORDER BY COUNT(*) DESC LIMIT 1

Database: movie_3
Question: What is the title of the restricted film, whose length is 71 minutes and whose replacement cost is $29.99?
Evidence: restricted means rating = 'R'; length is 71 minutes refers to length = 71; replacement cost is $29.99 refers to replacement_cost = 29.99
SQL: SELECT title FROM film WHERE replacement_cost = 29.99 AND rating = 'R' AND length = 71

Database: world_development_indicators
Question: For how many consecutive years did Aruba get a footnote on the series code BX.KLT.DINV.CD.WD?
Evidence: Aruba is the name of country where ShortName = 'Aruba'
SQL: SELECT COUNT(T2.Year) FROM Country AS T1 INNER JOIN FootNotes AS T2 ON T1.CountryCode = T2.Countrycode WHERE T1.ShortName = 'Aruba' AND T2.Seriescode = 'BX.KLT.DINV.CD.WD'