Database: trains
Question: Among the trains running west, how many trains have no more than one car with an open roof?
Evidence: running west refers to direction = 'west'; open roof refers to roof = 'none'
SQL: SELECT SUM(CASE WHEN T1.direction = 'west' THEN 1 ELSE 0 END)as count FROM trains AS T1 INNER JOIN ( SELECT train_id, COUNT(id) FROM cars WHERE roof = 'none' GROUP BY train_id HAVING COUNT(id) = 1 ) AS T2 ON T1.id = T2.train_id

Database: retail_world
Question: How many employees have Andrew Fuller as their direct supervisor?
Evidence: direct supervisor refers to ReportsTo where the person being reported to is usually the direct supervisor of the reporter;
SQL: SELECT COUNT(EmployeeID) FROM Employees WHERE ReportsTo = ( SELECT EmployeeID FROM Employees WHERE LastName = 'Fuller' AND FirstName = 'Andrew' )

Database: car_retails
Question: What profit can the seller Carousel DieCast Legends make from the sale of the product described as "The perfect holiday or anniversary gift for executives"?
Evidence: seller and product vendor are synonyms; Carousel DieCast Legends is a product vendor; profit = SUM(SUBTRACT(msrp, buyPrice));
SQL: SELECT SUM(T2.MSRP - T2.buyPrice) FROM productlines AS T1 INNER JOIN products AS T2 ON T1.productLine = T2.productLine WHERE T2.productVendor = 'Carousel DieCast Legends' AND T1.textDescription LIKE '%perfect holiday or anniversary gift for executives%'

Database: codebase_comments
Question: List all the tokenized name of the solution path "jurney_P4Backup\P4Backup\P4Backup.sln
".
Evidence: tokenized name refers to NameTokenized; solution path refers to Path; Path = 'jurney_P4Backup\P4Backup\P4Backup.sln';
SQL: SELECT DISTINCT T2.NameTokenized FROM Solution AS T1 INNER JOIN Method AS T2 ON T1.Id = T2.SolutionId WHERE T1.Path = 'jurney_P4BackupP4BackupP4Backup.sln'

Database: chicago_crime
Question: List all the crimes of the narcotic type that exist.
Evidence: narcotic type refers to primary_description = 'NARCOTICS'; crime refers to secondary_description
SQL: SELECT secondary_description FROM IUCR WHERE primary_description = 'NARCOTICS' GROUP BY secondary_description

Database: olympics
Question: How many athletes from region 151 have won a medal?
Evidence: athletes refer to person_id; region 151 refers to region_id = 151; won a medal refers to medal_id <> 4;
SQL: SELECT COUNT(T3.person_id) FROM competitor_event AS T1 INNER JOIN games_competitor AS T2 ON T1.competitor_id = T2.id INNER JOIN person_region AS T3 ON T2.person_id = T3.person_id WHERE T3.region_id = 151 AND T1.medal_id != 4

Database: chicago_crime
Question: How many neighborhoods are there in Near North Side?
Evidence: Near North Side refers to community_area_name = 'Near North Side'
SQL: SELECT SUM(CASE WHEN T1.community_area_name = 'Near North Side' THEN 1 ELSE 0 END) FROM Community_Area AS T1 INNER JOIN Neighborhood AS T2 ON T1.community_area_no = T2.community_area_no

Database: donor
Question: Please list the types of resources that the vendor Lakeshore Learning Materials has provided for the projects.
Evidence: Lakeshore Learning Materials is vendor_name; type of resources refers to project_resource_type;
SQL: SELECT DISTINCT project_resource_type FROM resources WHERE vendor_name = 'Lakeshore Learning Materials'

Database: shipping
Question: List all the cities where Zachery Hicks transported goods.
Evidence: city refers to city_name
SQL: SELECT DISTINCT T3.city_name FROM shipment AS T1 INNER JOIN driver AS T2 ON T1.driver_id = T2.driver_id INNER JOIN city AS T3 ON T1.city_id = T3.city_id WHERE T2.first_name = 'Zachery' AND T2.last_name = 'Hicks'

Database: food_inspection_2
Question: Please list the location coordinates of all the facilities that had an inspection on 2010/5/11.
Evidence: location coordinates refers to latitude, longitude; on 2010/5/11 refers to inspection_date = '2010-05-11'
SQL: SELECT DISTINCT T2.latitude, T2.longitude FROM inspection AS T1 INNER JOIN establishment AS T2 ON T1.license_no = T2.license_no WHERE T1.inspection_date = '2010-05-11'

Database: public_review_platform
Question: Provide the number of Yelp businesses in "Gilbert" which got a" high" review count.
Evidence: "Gilbert" is the name of city; high review count refers to review_count = 'High'
SQL: SELECT COUNT(business_id) FROM Business WHERE review_count = 'High' AND city = 'Gilbert'

Database: retail_world
Question: List the product ID of the top five products, by descending order, the number of quantities in stock.
Evidence: by descending in number of quantity in stock refers to max(UnitsInStock)
SQL: SELECT ProductID FROM Products ORDER BY UnitsInStock DESC LIMIT 5

Database: donor
Question: Write the message of the donor of the project with the title of Lets Share Ideas who paid with a credit card.
Evidence: message of the donor refer to donation_message; Lets Share Ideas refer to title; paid with a credit card refer to payment_method
SQL: SELECT T3.donation_message FROM essays AS T1 INNER JOIN projects AS T2 ON T1.projectid = T2.projectid INNER JOIN donations AS T3 ON T2.projectid = T3.projectid WHERE T1.title = 'Lets Share Ideas' AND T3.payment_method = 'creditcard'

Database: chicago_crime
Question: How many community areas are in the Far North side?
Evidence: the Far North side refers to side = 'Far North'
SQL: SELECT COUNT(*) FROM Community_Area WHERE side = 'Far North '

Database: movielens
Question: Among the actors who acted in UK movies, what percentage of actors received a rating of at least 3?
Evidence: UK is a country
SQL: SELECT CAST(SUM(IIF(T3.a_quality >= 3, 1, 0)) AS REAL) * 100 / COUNT(T1.movieid) FROM movies AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid INNER JOIN actors AS T3 ON T2.actorid = T3.actorid WHERE T1.country = 'UK'

Database: trains
Question: Please list the shapes of all the head cars on the trains that run in the east direction.
Evidence: head cars refers to position = 1;
SQL: SELECT T1.shape FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east' AND T1.position = 1 GROUP BY T1.shape

Database: car_retails
Question: How many Planes orders were there?
Evidence: Planes is a product line;
SQL: SELECT COUNT(T1.productCode) FROM orderdetails AS T1 INNER JOIN products AS T2 ON T1.productCode = T2.productCode WHERE T2.productLine = 'Planes'

Database: food_inspection
Question: How many kinds of violations did "Stacks Restaurant" have on 2016/10/4?
Evidence: "Stacks Restaurant" is the name of the business; date = '2016-10-04';
SQL: SELECT COUNT(DISTINCT T1.violation_type_id) FROM violations AS T1 INNER JOIN businesses AS T2 ON T1.business_id = T2.business_id WHERE T2.name = 'Stacks Restaurant' AND T1.`date` = '2016-10-04'

Database: retail_world
Question: Write the address and phone number of Margaret Peacock.
Evidence: Margaret Peacock is the full name of an employee; full name refers to FirstName, LastName; phone number refers to HomePhone
SQL: SELECT Address, HomePhone FROM Employees WHERE FirstName = 'Margaret' AND LastName = 'Peacock'

Database: coinmarketcap
Question: What was the max profit a user can make on Bitcoin on 2013/4/28?
Evidence: the max profit = subtract(high, low); on 2013/4/28 refers to date = '2013-04-28'
SQL: SELECT T2.high - T2.low FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T2.date = '2013-04-28' AND T1.name = 'Bitcoin'

Database: talkingdata
Question: What is the percentage of device users in the F27-28 age group who experienced an event on the 3rd of May 2016?
Evidence: percentage = MULTIPLY(DIVIDE(SUM(`group` = 'F27-28'), COUNT(device_id)), 1.0); on the 3rd of May 2016 refers to timestamp = '2016-05-03%';
SQL: SELECT SUM(IIF(T1.`group` = 'F27-28', 1, 0)) / COUNT(T1.device_id) AS per FROM gender_age AS T1 INNER JOIN events_relevant AS T2 ON T1.device_id = T2.device_id WHERE SUBSTR(T2.timestamp, 1, 10) = '2016-05-03'