Database: books
Question: Name the title of the book with the most number of pages that was published from 1990 to 2000 by publisher Free Press.
Evidence: books with the most number of pages refers to Max(num_pages); published from 1990 to 2000 refers to SUBSTR(publication_date, 1, 4) BETWEEN '1990' AND '2000'; 'Free Press' is the publisher_name
SQL: SELECT T1.title FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id WHERE T2.publisher_name = 'Free Press' AND STRFTIME('%Y', T1.publication_date) BETWEEN '1990' AND '2000' ORDER BY T1.num_pages DESC LIMIT 1

Database: food_inspection_2
Question: List down the phone numbers of employees who did Canvass inspection.
Evidence: phone number refers to phone; Canvass inspection refers to inspection_type = 'Canvass'
SQL: SELECT DISTINCT T1.phone FROM employee AS T1 INNER JOIN inspection AS T2 ON T1.employee_id = T2.employee_id WHERE T2.inspection_type = 'Canvass'

Database: movie
Question: What is the percentage of the USA actors that showed up in the credit list of movie "Mrs. Doubtfire"?
Evidence: USA actors refers to Birth Country = 'USA'; movie "Mrs. Doubtfire" refers to Title = 'Mrs. Doubtfire'; percentage = divide(count(ActorID where Birth Country = 'USA'), count(ActorID)) * 100%
SQL: SELECT CAST(SUM(CASE WHEN T3.`Birth Country` = 'USA' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T3.`Birth Country`) FROM movie AS T1 INNER JOIN characters AS T2 ON T1.MovieID = T2.MovieID INNER JOIN actor AS T3 ON T3.ActorID = T2.ActorID WHERE T1.Title = 'Mrs. Doubtfire'

Database: movie_platform
Question: Which user made a critic for the film "When Will I Be Loved" and got 2 comments for the critic?
Evidence: When Will I Be Loved refers to movie_title;  2 comments for the critic refers to critic_comments = 2;
SQL: SELECT T1.user_id FROM ratings AS T1 INNER JOIN movies AS T2 ON T1.movie_id = T2.movie_id WHERE T2.movie_title = 'When Will I Be Loved' AND T1.critic_comments = 2

Database: retail_world
Question: Among the products ordered in order no. 10248, which product has the biggest ratio of units on order to units in stock?
Evidence: order no. refers to OrderID; biggest ratio = MAX(DIVIDE(UnitsOnOrder, UnitsInStock));
SQL: SELECT T1.ProductName FROM Products AS T1 INNER JOIN `Order Details` AS T2 ON T1.ProductID = T2.ProductID WHERE T2.OrderID = 10248 ORDER BY T1.UnitsOnOrder / T1.UnitsInStock DESC LIMIT 1

Database: world_development_indicators
Question: In 1960, what is largest population for country with upper middle income?
Evidence: in 1960 refers to year = '1960'; the largest population refers to max(value where IndicatorName = 'Population, total'); country with upper middle income refers to incomegroup = 'Upper middle income'
SQL: SELECT MAX(T2.Value) FROM Country AS T1 INNER JOIN Indicators AS T2 ON T1.CountryCode = T2.CountryCode WHERE T1.IncomeGroup = 'Upper middle income' AND T2.Year = 1960 AND T2.IndicatorName = 'Population, total'

Database: sales_in_weather
Question: Which station sold the highest quantity of item number 5 overall?
Evidence: item number 5 refers to item_nbr = 5; sold highest quantity refers to Max(Sum(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 WHERE T1.item_nbr = 5 GROUP BY T2.station_nbr ORDER BY SUM(T1.units) DESC LIMIT 1

Database: donor
Question: List the primary subject of all the donations with a corporate sponsored giftcard.
Evidence: corporate sponsored giftcard refers to payment_included_campaign_gift_card = 't'; primary subject refers to primary_focus_subject
SQL: SELECT DISTINCT T2.primary_focus_subject FROM donations AS T1 INNER JOIN projects AS T2 ON T1.projectid = T2.projectid WHERE T1.payment_included_campaign_gift_card = 't'

Database: food_inspection_2
Question: How many taverns failed in July 2010?
Evidence: tavern refers to facility_type = 'Tavern'; failed refers to results = 'Fail'; in July 2010 refers to inspection_date like '2010-07%'
SQL: SELECT COUNT(DISTINCT T1.license_no) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE strftime('%Y-%m', T2.inspection_date) = '2010-07' AND T2.results = 'Fail' AND T1.facility_type = 'Restaurant'

Database: retails
Question: How many items did Customer#000021159 order? Calculate those items total charges.
Evidence: items Customer#000021159 order refer to l_linenumber where c_name = 'Customer#000021159'; SUM(MULTIPLY(MULTIPLY(l_extendedprice, SUBTRACT(1, l_discount)), SUM(1, l_tax))) where c_name = 'Customer#000021159';
SQL: SELECT COUNT(T2.o_orderkey), SUM(T3.l_extendedprice * (1 - T3.l_discount) * (1 + T3.l_tax)) FROM customer AS T1 INNER JOIN orders AS T2 ON T1.c_custkey = T2.o_custkey INNER JOIN lineitem AS T3 ON T2.o_orderkey = T3.l_orderkey WHERE T1.c_name = 'Customer#000021159' GROUP BY T3.l_linenumber

Database: synthea
Question: How many patients have the most prevalent conditions?
Evidence: the most prevalent conditions refer to MAX(PREVALENCE RATE);
SQL: SELECT COUNT(DISTINCT T2.patient) FROM all_prevalences AS T1 INNER JOIN conditions AS T2 ON lower(T1.ITEM) = lower(T2.DESCRIPTION) ORDER BY T1."PREVALENCE RATE" DESC LIMIT 1

Database: simpson_episodes
Question: Who is the recipient of the Primetime Emmy Award with the most votes?
Evidence: Primetime Emmy Award refers to award_category = 'Primetime Emmy'; the most votes refers to MAX(votes)
SQL: SELECT T1.person FROM Award AS T1 INNER JOIN Vote AS T2 ON T2.episode_id = T1.episode_id WHERE T1.award_category = 'Primetime Emmy' ORDER BY T2.votes DESC LIMIT 1;

Database: student_loan
Question: Calculate the average number of disabled female students enrolled in UCI.
Evidence: average = DIVIDE(SUM(disabled.name who are not in male.name WHERE school = 'uci'), COUNT(enrolled.name)); female students refers to disabled.name who are NOT in male.name; UCI refers to school = 'uci';
SQL: SELECT CAST(SUM(IIF(T1.school = 'uci' AND T4.name IS NULL, 1, 0)) AS REAL) / COUNT(T1.name) FROM enrolled AS T1 INNER JOIN disabled AS T2 ON T1.name = T2.name INNER JOIN person AS T3 ON T1.name = T3.name LEFT JOIN male AS T4 ON T3.name = T4.name

Database: shakespeare
Question: How many scenes are there in Act 5 of work ID 9?
SQL: SELECT COUNT(Scene) FROM chapters WHERE work_id = 9 AND Act = 5

Database: student_loan
Question: List out the number of students who filed for bankruptcy and enlisted in navy.
Evidence: navy refers to organ = 'navy';
SQL: SELECT COUNT(T1.name) FROM enlist AS T1 INNER JOIN filed_for_bankrupcy AS T2 ON T1.name = T2.name WHERE T1.organ = 'navy'

Database: food_inspection
Question: Among the restaurants being inspected in 2016, how many of them are in San Francisco?
Evidence: inspected in 2016 refers to YEAR(date) = 2016; San Francisco refers to city in ('San Francisco', 'SF' ,'S.F.', 'SAN FRANCISCO');
SQL: SELECT COUNT(DISTINCT T2.business_id) FROM inspections AS T1 INNER JOIN businesses AS T2 ON T1.business_id = T2.business_id WHERE STRFTIME('%Y', T1.`date`) = '2016' AND T2.city IN ('San Francisco', 'SAN FRANCISCO', 'SF', 'S.F.')

Database: simpson_episodes
Question: What are the keywords of the episode "Take My Life, Please"?
Evidence: episode "Take My Life, Please" refers to title = 'Take My Life, Please'
SQL: SELECT T2.keyword FROM Episode AS T1 INNER JOIN Keyword AS T2 ON T1.episode_id = T2.episode_id WHERE T1.title = 'Take My Life, Please';

Database: retails
Question: Please list the order comments of all the orders made by customers in the household segment.
Evidence: orders in household segment refer to o_orderkey where c_mktsegment = 'HOUSEHOLD'; order comments refer to o_comment;
SQL: SELECT T1.o_comment FROM orders AS T1 INNER JOIN customer AS T2 ON T1.o_custkey = T2.c_custkey WHERE T2.c_mktsegment = 'HOUSEHOLD'