Database: law_episode
Question: What is the episode that has mafia keyword?
Evidence: mafia keyword refers to Keyword = 'mafia'
SQL: SELECT T1.episode FROM Episode AS T1 INNER JOIN Keyword AS T2 ON T1.episode_id = T2.episode_id WHERE T2.Keyword = 'mafia'

Database: shooting
Question: How many instances were found in June 2015?
Evidence: in June 2015 refers to date between '2015-06-01' and '2015-06-30'; record number refers to case_number
SQL: SELECT COUNT(case_number) FROM incidents WHERE date BETWEEN '2015-06-01' AND '2015-06-30'

Database: public_review_platform
Question: What is the average rating of inactive businesses?
Evidence: rating refers to stars; inactive refers to active = 'False'; average rating of inactive businesses = DIVIDE(SUM(stars), COUNT(business_id));
SQL: SELECT CAST(SUM(stars) AS REAL) / COUNT(business_id) AS "average" FROM Business WHERE active LIKE 'FALSE'

Database: movies_4
Question: What is the average revenue of the movie in which Dariusz Wolski works as the director of photography?
Evidence: director of photography refers to job = 'Director of Photography'; average revenue = divide(sum(revenue), count(movie_id))
SQL: SELECT CAST(SUM(T1.revenue) AS REAL) / COUNT(T1.movie_id) FROM movie AS T1 INNER JOIN movie_crew AS T2 ON T1.movie_id = T2.movie_id INNER JOIN person AS T3 ON T2.person_id = T3.person_id WHERE T3.person_name = 'Dariusz Wolski' AND T2.job = 'Director of Photography'

Database: computer_student
Question: What year in the program do the students with more than 2 advisors are in?
Evidence: students refers to student = 1; more than 2 advisors refers to count(p_id_dummy) > 2
SQL: SELECT T2.yearsInProgram FROM advisedBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.student = 1 GROUP BY T2.p_id HAVING COUNT(T2.p_id) > 2

Database: chicago_crime
Question: How many weapons violation crimes have occurred in the Calumet district?
Evidence: "Calumet" is the district_name; 'WEAPON VIOLATION' is the primary_description of crime
SQL: SELECT SUM(CASE WHEN T3.district_name = 'Calumet' THEN 1 ELSE 0 END) FROM IUCR AS T1 INNER JOIN Crime AS T2 ON T2.iucr_no = T1.iucr_no INNER JOIN District AS T3 ON T3.district_no = T2.district_no WHERE T1.primary_description = 'WEAPONS VIOLATION'

Database: sales_in_weather
Question: Please list the dates on which the sale of item no.5 in store no.3 exceeded 100 and the average wind speed exceeded 10.
Evidence: item no. 5 refers to item_nbr = 5; store no.3 refers to store_nbr = 3; exceed 100 refers to units > 100; average wind speed exceeded 10 refers to avgspeed > 10
SQL: SELECT T1.`date` 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 WHERE T2.store_nbr = 3 AND T1.item_nbr = 5 AND T1.units > 100 AND T3.avgspeed > 10

Database: chicago_crime
Question: What is the legislative district's office address where 010XX W LAKE ST is located?
Evidence: the legislative district's office address refers to ward_office_address; 010XX W LAKE ST refers to block = '010XX W LAKE ST'
SQL: SELECT T1.ward_office_address FROM Ward AS T1 INNER JOIN Crime AS T2 ON T1.ward_no = T2.ward_no WHERE T2.block = '010XX W LAKE ST' GROUP BY T1.ward_office_address

Database: retail_world
Question: Which category does "tofu" belong to?
Evidence: category refers to CategoryName; tofu refers to ProductName = 'Tofu';
SQL: SELECT T2.CategoryName FROM Products AS T1 INNER JOIN Categories AS T2 ON T1.CategoryID = T2.CategoryID WHERE T1.ProductName = 'Tofu'

Database: image_and_language
Question: List all the corresponding classes for attributes of image id 8.
Evidence: classes for attributes refers to ATT_CLASS; image id 8 refers to IMG_ID = 8
SQL: SELECT T2.ATT_CLASS FROM IMG_OBJ_att AS T1 INNER JOIN ATT_CLASSES AS T2 ON T1.ATT_CLASS_ID = T2.ATT_CLASS_ID WHERE T1.IMG_ID = 8

Database: software_company
Question: List the marital status of customers within the age of 40 to 60 that has the highest income among the group.
Evidence: age of 40 to 60 refers to age BETWEEN 40 AND 60; the highest income refers to MAX(INCOME_K);
SQL: SELECT T1.MARITAL_STATUS FROM Customers AS T1 INNER JOIN Demog AS T2 ON T1.GEOID = T2.GEOID WHERE T1.age >= 40 AND T1.age <= 60 ORDER BY T2.INCOME_K DESC LIMIT 1

Database: simpson_episodes
Question: Please list the names of all the awards won by the crew member whose nickname is Doofus.
Evidence: award won refers to result = 'Winner'
SQL: SELECT T2.award FROM Person AS T1 INNER JOIN Award AS T2 ON T1.name = T2.person WHERE T1.nickname = 'Doofus' AND T2.result = 'Winner';

Database: shooting
Question: What is the percentage of the cases involved more than 3 officers from year 2010 to 2015?
Evidence: more than 3 officers refers to officer_count > 3; from year 2010 to 2015 refers to date between '2010-01-01' and '2015-12-31'; percentage = divide(count(case_number where officer_count > 3), count(case_number)) where date between '2010-01-01' and '2015-12-31' * 100%
SQL: SELECT CAST(SUM(IIF(officer_count > 3, 1, 0)) AS REAL) * 100 / COUNT(case_number) FROM incidents WHERE STRFTIME('%Y', date) BETWEEN '2010' AND '2015'

Database: retails
Question: What is the name of the country of the supplier with the highest debt?
Evidence: name of the country refers to n_name; the highest debt refers to min(s_acctbal)
SQL: SELECT T2.n_name FROM supplier AS T1 INNER JOIN nation AS T2 ON T1.s_nationkey = T2.n_nationkey ORDER BY T1.s_suppkey DESC LIMIT 1

Database: simpson_episodes
Question: List the award name and persons who won the award in 2009.
Evidence: won the award refers to result = 'Winner'; in 2009 refers to year = 2009
SQL: SELECT award, person FROM Award WHERE result = 'Winner' AND SUBSTR(year, 1, 4) = '2009';

Database: student_loan
Question: How many students are unemployed and have payment due?
Evidence: are unemployed and have payment due refers to
SQL: SELECT COUNT(T1.name) FROM unemployed AS T1 INNER JOIN no_payment_due AS T2 ON T1.`name` = T2.`name`

Database: soccer_2016
Question: List down the DOB of players who received the "man of the match" award.
SQL: SELECT T2.DOB FROM Match AS T1 INNER JOIN Player AS T2 ON T2.Player_Id = T1.Man_of_the_Match

Database: address
Question: Provide the zip codes and their affiliated organization for the postal point under Kingsport-Bristol, TN-VA.
Evidence: postal point under Kingsport-Bristol, TN-VA refers to zip_code where CBSA_name = 'Kingsport-Bristol, TN-VA'; affiliated organization refers to organization from CBSA;
SQL: SELECT T2.zip_code, T2.organization FROM CBSA AS T1 INNER JOIN zip_data AS T2 ON T1.CBSA = T2.CBSA WHERE T1.CBSA_name = 'Kingsport-Bristol, TN-VA'

Database: retail_world
Question: Provide the full name of the employee who processed the sales order with ID 10274.
Evidence: full name refers to FirstName, LastName; sales order with ID 10274 refers to OrderID = 10274
SQL: SELECT T1.FirstName, T1.LastName FROM Employees AS T1 INNER JOIN Orders AS T2 ON T1.EmployeeID = T2.EmployeeID WHERE T2.OrderID = 10274

Database: synthea
Question: Calculate the average age of patients with prediabetes care plan.
Evidence: SUBTRACT(SUM(deathdate), SUM(birthdate)), COUNT(patient) where REASONDESCRIPTION = 'Prediabetes' from careplans;
SQL: SELECT CAST(SUM(CASE WHEN T1.deathdate IS NULL THEN strftime('%Y', T2.STOP) - strftime('%Y', T1.birthdate) ELSE strftime('%Y', T1.deathdate) - strftime('%Y', T1.birthdate) END) AS REAL) / COUNT(DISTINCT T1.patient) FROM patients AS T1 INNER JOIN careplans AS T2 ON T1.patient = T2.PATIENT WHERE T2.REASONDESCRIPTION = 'Prediabetes'

Database: food_inspection_2
Question: State the name of dbas with verified quality.
Evidence: name of dba refers to dba_name; with verified quality refers to results = 'Pass' or results = 'Pass w/Conditions'
SQL: SELECT DISTINCT T1.dba_name FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE T2.results LIKE '%Pass%'