Database: regional_sales
Question: List all the numbers ordered by 'Rochester Ltd' in 2018.
Evidence: Rochester Ltd is the name of the customer; all the numbers ordered refer to OrderNumber; 2018 refers to SUBSTR(OrderDate, -2) = '18';
SQL: SELECT DISTINCT T FROM ( SELECT CASE  WHEN T1.OrderDate LIKE '%/%/18' AND T2.`Customer Names` = 'Rochester Ltd' THEN T1.OrderNumber ELSE NULL END AS T FROM `Sales Orders` T1 INNER JOIN Customers T2 ON T2.CustomerID = T1._CustomerID ) WHERE T IS NOT NULL

Database: donor
Question: How many resources with a unit price less than 15 are not technology type? List them by vendor id
Evidence: unit price less than 15 refers to item_unit_price< = 15; are not technology type refers to project_resource_type = 'technology'
SQL: SELECT vendorid FROM resources WHERE project_resource_type = 'Technology' AND item_unit_price <= 15

Database: shakespeare
Question: How many scenes are there in Act 1 in Twelfth Night?
Evidence: Twelfth Night refers to Title = 'Twelfth Night'
SQL: SELECT COUNT(T1.id) FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id WHERE T2.Act = 1 AND T1.Title = 'Twelfth Night'

Database: app_store
Question: Name the top 10 most reviewed apps.
Evidence: most reviewed app refers to MAX(Reviews);
SQL: SELECT DISTINCT App FROM playstore ORDER BY Reviews DESC LIMIT 10

Database: student_loan
Question: List at least 5 students who has the longest absense from schoool?
Evidence: longest absense refers to MAX(month)
SQL: SELECT name FROM longest_absense_from_school ORDER BY month DESC LIMIT 5

Database: restaurant
Question: List all the streets with more than 10 restaurants in Alameda county.
Evidence: street refers to street_name; more than 10 restaurants refers to count(id_restaurant) > 10
SQL: SELECT T2.street_name FROM geographic AS T1 INNER JOIN location AS T2 ON T1.city = T2.city WHERE T1.county = 'alameda county' GROUP BY T2.street_name HAVING COUNT(T2.id_restaurant) > 10

Database: sales_in_weather
Question: On the day with the highest max temperature in 2012, how many items in store no.3 had no sales?
Evidence: highest max temperature refers to Max(tmax); in 2012 refers to SUBSTR(date, 1, 4) = '2012'; store no.3 refers to store_nbr = 3; had no sale refers to units = 0
SQL: SELECT COUNT(DISTINCT T1.item_nbr) FROM sales_in_weather AS T1 INNER JOIN relation AS T2 ON T1.store_nbr = T2.store_nbr INNER JOIN weather AS T3 ON T2.station_nbr = T3.station_nbr AND T1.store_nbr = 3 AND SUBSTR(T1.`date`, 1, 4) = '2012' AND T1.units = 0 GROUP BY T3.tmax ORDER BY T3.tmax DESC LIMIT 1

Database: donor
Question: What are the coordinates of the school where project 'Look, Look, We Need a Nook!' Was donated to and what resource type is it?
Evidence: Coordinates of the school refer to school_latitude, school_longitude); Look, Look, We Need a Nook! Refer to title;
SQL: SELECT T2.school_latitude, T2.school_longitude, T2.resource_type FROM essays AS T1 INNER JOIN projects AS T2 ON T1.projectid = T2.projectid WHERE T1.title LIKE 'Look, Look, We Need a Nook!'

Database: beer_factory
Question: What is the brand name of the root beer that gained a 1-star rating from customer ID 331115 while saying, "Yuk, more like licorice soda"?
Evidence: 1-star rating refers to StarRating = 1; saying, "Yuk, more like licorice soda" refers to Review = 'Yuk, more like licorice soda.';
SQL: SELECT T1.BrandName FROM rootbeerbrand AS T1 INNER JOIN rootbeerreview AS T2 ON T1.BrandID = T2.BrandID WHERE T2.CustomerID = 331115 AND T2.Review = 'Yuk, more like licorice soda.' AND T2.StarRating = 1

Database: software_company
Question: Among the male customer in their twenties, how many are from places where the average income is more than 3000?
Evidence: male customer in their twenties refer to SEX = 'Male' where age BETWEEN 20 AND 29; average income is more than 3000 refers to INCOME_K > 3000;
SQL: SELECT COUNT(T2.GEOID) FROM Customers AS T1 INNER JOIN Demog AS T2 ON T1.GEOID = T2.GEOID WHERE T1.SEX = 'Male' AND T2.INCOME_K > 3000 AND T1.age >= 20 AND T1.age <= 29

Database: food_inspection_2
Question: Which business had the highest number of inspections done? Calculate the percentage of passed and failed inspections of the said business.
Evidence: business name refers to dba_name; the highest number of inspections done max(count(inspection_id)); percentage of passed inspections = divide(sum(inspection_id where results = 'Pass'), total(inspection_id)) * 100%; percentage of failed inspections = divide(sum(inspection_id where results = 'Fail'), total(inspection_id)) * 100%
SQL: SELECT T2.dba_name , CAST(SUM(CASE WHEN T1.results = 'Pass' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.inspection_id) AS percentagePassed , CAST(SUM(CASE WHEN T1.results = 'Fail' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.inspection_id) FROM inspection AS T1 INNER JOIN establishment AS T2 ON T1.license_no = T2.license_no GROUP BY T2.dba_name ORDER BY COUNT(T1.license_no) DESC LIMIT 1

Database: airline
Question: How long was the longest minute delay caused by a weather problem in airport id 12264?
Evidence: longest minute delay caused by a weather problem refers to MAX(WEATHER_DELAY); airport id refers to ORIGIN_AIRPORT_ID; ORIGIN_AIRPORT_ID = 12264;
SQL: SELECT WEATHER_DELAY FROM Airlines WHERE ORIGIN_AIRPORT_ID = 12264 ORDER BY WEATHER_DELAY DESC LIMIT 1

Database: legislator
Question: List the official full names of all the legislators that served 13 district for 26 consecutive years.
Evidence: served only one district for 26 consecutive years refers to SUBTRACT(SUM(cast(strftime('%Y', end)), CAST(strftime('%Y', start)))) = 26
SQL: SELECT DISTINCT CASE WHEN SUM(CAST(strftime('%Y', T2.end) AS int) - CAST(strftime('%Y', T2.start) AS int)) = 26 THEN T1.official_full_name END FROM current AS T1 INNER JOIN `current-terms` AS T2 ON T1.bioguide_id = T2.bioguide GROUP BY T1.official_full_name, T2.district HAVING COUNT(T1.official_full_name) = 13

Database: image_and_language
Question: What are the x and y coordinates of all the images with a prediction relationship class id of 98?
Evidence: prediction relationship class id of 98 refers to PRED_CLASS_ID = 98
SQL: SELECT T2.X, T2.Y FROM IMG_REL AS T1 INNER JOIN IMG_OBJ AS T2 ON T1.IMG_ID = T2.IMG_ID WHERE T1.PRED_CLASS_ID = 98

Database: synthea
Question: What are the birth date of patients who took outpatient encounter care plan?
Evidence: outpatient encounter care plan refers to careplans where DESCRIPTION = 'Outpatient Encounter';
SQL: SELECT DISTINCT T1.birthdate FROM patients AS T1 INNER JOIN encounters AS T2 ON T1.patient = T2.PATIENT WHERE T2.DESCRIPTION = 'Outpatient Encounter'

Database: language_corpus
Question: How many times does the word "heròdot" appear in the Wikipedia page?
Evidence: heròdot refers to word = 'heròdot'; times appear refers to SUM(pid)
SQL: SELECT COUNT(T2.occurrences) FROM words AS T1 INNER JOIN pages_words AS T2 ON T1.wid = T2.wid WHERE T1.word = 'heròdot'

Database: soccer_2016
Question: List down the name of teams that won the toss of the coin from matches with ID from 336010 to 336020.
Evidence: name of teams refers to Team_Name; won the toss refers to Toss_Winner; matches with ID from 336010 to 336020  refers to Match_Id BETWEEN 336010 AND 336020
SQL: SELECT T2.Team_Name FROM Match AS T1 INNER JOIN Team AS T2 ON T2.Team_Id = T1.Toss_Winner WHERE T1.Match_Id BETWEEN 336010 AND 336020

Database: retails
Question: How many countries are there in the region whose comment description is "asymptotes sublate after the r."
Evidence: r_comment = 'asymptotes sublate after the r'; countries refer to n_nationkey;
SQL: SELECT COUNT(T1.n_nationkey) FROM nation AS T1 INNER JOIN region AS T2 ON T1.n_regionkey = T2.r_regionkey WHERE T2.r_comment = 'asymptotes sublate after the r'