Database: bike_share_1
Question: What is the total number of bikes that can be hold in Redwood City before 2014.
Evidence: total number of bikes that can be hold = MAX(dock_count); before 2014 refers to year(installation_date)<2014;
SQL: SELECT SUM(CASE WHEN city = 'Redwood City' AND SUBSTR(installation_date, -4) < '2014' THEN dock_count ELSE 0 END) NUM FROM station

Database: public_review_platform
Question: List the active business ID and its stars of the businesses fall under the category of Pets.
Evidence: active business refers to active = 'true'; 'Pets' is the category_name
SQL: SELECT T1.business_id, T1.stars FROM Business AS T1 INNER JOIN Business_Categories ON T1.business_id = Business_Categories.business_id INNER JOIN Categories AS T3 ON Business_Categories.category_id = T3.category_id WHERE T1.active LIKE 'TRUE' AND T3.category_name LIKE 'Pets'

Database: regional_sales
Question: Find the percentage of products that were shipped from Burbank in 2018?
Evidence: DIVIDE(SUM(Order Quantity where City Name = 'Burbank' and SUBSTR(OrderDate, -2) = '18')), (SUM(Order Quantity where SUBSTR(OrderDate, -2) = '18')) as percentage;
SQL: SELECT CAST(SUM(CASE WHEN T3.`City Name` = 'Burbank' THEN T2.`Order Quantity` ELSE 0 END) AS REAL) * 100 / SUM(T2.`Order Quantity`) FROM Products AS T1 INNER JOIN `Sales Orders` AS T2 ON T2._ProductID = T1.ProductID INNER JOIN `Store Locations` AS T3 ON T3.StoreID = T2._StoreID WHERE T2.OrderDate LIKE '%/%/18'

Database: sales
Question: List down the first name of customers who placed order for product id 1.
SQL: SELECT T1.FirstName FROM Customers AS T1 INNER JOIN Sales AS T2 ON T1.CustomerID = T2.CustomerID INNER JOIN Products AS T3 ON T2.ProductID = T3.ProductID WHERE T2.ProductID = 1

Database: cs_semester
Question: Please list the full names of all the students who are research assistants with the highest research capability.
Evidence: research assistant refers to the student who serves for research where the abbreviation is RA; the highest research capability refers to capability = 5; full name refers to f_name and l_name;
SQL: SELECT T1.f_name, T1.l_name FROM student AS T1 INNER JOIN RA AS T2 ON T1.student_id = T2.student_id WHERE T2.capability = 5

Database: retail_complains
Question: Identify by their ID all clients who did not give their consent permission.
Evidence: did not give their consent permission refers to Consumer consent provided is null, 'N/A', or empty;
SQL: SELECT Client_ID FROM events WHERE `Consumer consent provided?` = 'N/A' OR 'Consumer consent provided?' IS NULL OR 'Consumer consent provided?' = ''

Database: codebase_comments
Question: Provide the github address with the summary of method "A test for Decompose
".
Evidence: github address refers to Url; summary of method refers to Summary; Summary = 'A test for Decompose';
SQL: SELECT T1.Url FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId INNER JOIN Method AS T3 ON T2.Id = T3.SolutionId WHERE T3.Summary = 'A test for Decompose'

Database: music_tracker
Question: What is the tag and the artist of the most downloaded single?
Evidence: the most downloaded single refers to MAX(totalSnatched where releaseType = 'single');
SQL: SELECT T2.tag, T1.artist FROM torrents AS T1 INNER JOIN tags AS T2 ON T1.id = T2.id WHERE T1.releaseType = 'single' ORDER BY T1.totalSnatched DESC LIMIT 1

Database: codebase_comments
Question: How many more stars in percentage are there for the repository of solution "1" than solution "2"?
Evidence: solution refers to Solution.Id; Solution.Id = 1 OR 2; percentage = DIVIDE(MULTIPLY(SUBTRACT(SUM(Solution.Id = 1), SUM(Solution.Id = 2)), 100)), SUM(Solution.Id = 2);
SQL: SELECT CAST(SUM(CASE WHEN T2.Id = 1 THEN T1.Stars ELSE 0 END) - SUM(CASE WHEN T2.Id = 2 THEN T1.Stars ELSE 0 END) AS REAL) * 100 / SUM(CASE WHEN T2.Id = 2 THEN T1.Stars ELSE 0 END) FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId

Database: regional_sales
Question: Describe the ID, city and region of the stores which are in Allen country.
Evidence: ID refers to StoreID;
SQL: SELECT DISTINCT T2.StoreID, T2.`City Name`, T1.Region FROM Regions AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StateCode = T1.StateCode WHERE T2.County = 'Allen County'

Database: professional_basketball
Question: Which coach has serviced in NBA for more than 10 years.
Evidence: "NBA" is the lgID; coach who serviced for more than 10 years refers to coachID where Subtract (Max(year), Min(year)) > 10
SQL: SELECT coachID FROM coaches WHERE lgID = 'NBA' GROUP BY coachID HAVING MAX(year) - MIN(year) > 10

Database: books
Question: List the title of the earliest published Japanese book.
Evidence: Japanese book refers to language_name = 'Japanese'; earliest published refers to Min(publication_date)
SQL: SELECT T1.title FROM book AS T1 INNER JOIN book_language AS T2 ON T1.language_id = T2.language_id WHERE T2.language_name = 'Japanese' ORDER BY T1.publication_date ASC LIMIT 1

Database: food_inspection
Question: Among the establishments with a postal code of 94102, how many establishments have a score of 90 or more in 2015?
Evidence: establishment has the same meaning as business; score of 90 or more refers to score ≥ 90; year(date) = 2015;
SQL: SELECT COUNT(DISTINCT T2.business_id) FROM violations AS T1 INNER JOIN businesses AS T2 ON T1.business_id = T2.business_id INNER JOIN inspections AS T3 ON T2.business_id = T3.business_id WHERE STRFTIME('%Y', T1.`date`) = '2015' AND T2.postal_code = '94102' AND T3.score > 90

Database: talkingdata
Question: Give the number of device models for "中兴" phone brand.
SQL: SELECT COUNT(device_id) FROM phone_brand_device_model2 WHERE device_model = '中兴'

Database: soccer_2016
Question: How many times has SR Watson been named 'Man of the Match'?
SQL: SELECT SUM(CASE WHEN T2.Player_Name = 'SR Watson' THEN 1 ELSE 0 END) FROM `Match` AS T1 INNER JOIN Player AS T2 ON T1.Man_of_the_Match = T2.Player_Id

Database: legislator
Question: What is the religion of current legislator Sherrod Brown?
Evidence: religion refers to religion_bio;
SQL: SELECT religion_bio FROM current WHERE official_full_name = 'Sherrod Brown'

Database: world_development_indicators
Question: List down the top 3 Latin American & Caribbean countries with the highest average value in "CO2 emissions (kt)" indicator since 1965. Give their highest value and in what year.
Evidence: Latin American & Caribbean countries is the name of the region where Region in ('Latin America' , 'Caribbean'); CO2 emissions from gaseous fuel consumption (kt) is the name of indicator where IndicatorName = 'CO2 emissions from gaseous fuel consumption (kt)'; average value in CO2 emissions (kt) = DIVIDE(SUM(Value), SUM(IndicatorName = 'CO2 emissions from gaseous fuel consumption (kt)')); Year > 1965
SQL: SELECT DISTINCT T1.CountryCode, T1.Year, T1.Value FROM Indicators AS T1 INNER JOIN Country AS T2 ON T1.CountryCode = T2.CountryCode WHERE T2.Region = 'Latin America & Caribbean' AND T1.IndicatorName = 'CO2 emissions (kt)' AND T1.Year > 1965 AND T1.Year < 1980 ORDER BY T1.Value DESC LIMIT 3

Database: synthea
Question: Provide medications received by patients with an allergy to mould on 6th June 2016.
Evidence: medications refers to DESCRIPTION from medications; allergy to mould refers to allergies where DESCRIPTION = 'Allergy to mould'; on 6th June 2016 refers to START = '6/6/16';
SQL: SELECT T2.DESCRIPTION FROM allergies AS T1 INNER JOIN medications AS T2 ON T1.PATIENT = T2.PATIENT WHERE T1.START = '6/6/16' AND T1.DESCRIPTION = 'Allergy to mould'