Database: retails
Question: How much is the profit for smoke turquoise purple blue salmon that was delivered in person on 5/7/1996?
Evidence: SUBTRACT(MULTIPLY(l_extendedprice, (SUBTRACT(1, l_discount)), MULTIPLY(ps_supplycost, l_quantity))) where p_name = 'smoke turquoise purple blue salmon' and l_receiptdate = '1996-05-07' and l_shipinstruct = 'DELIVER IN PERSON';
SQL: SELECT T1.l_extendedprice * (1 - T1.l_discount) - T2.ps_supplycost * T1.l_quantity AS num FROM lineitem AS T1 INNER JOIN partsupp AS T2 ON T1.l_suppkey = T2.ps_suppkey INNER JOIN part AS T3 ON T2.ps_partkey = T3.p_partkey WHERE T1.l_receiptdate = '1996-05-07' AND T1.l_shipinstruct = 'DELIVER IN PERSON' AND T3.p_name = 'smoke turquoise purple blue salmon'

Database: soccer_2016
Question: What is the average winning margin of the matches held in Newlands?
Evidence: average winning margin refers to avg(win_margin); held in Newlands refers to venue_name = 'Newlands'
SQL: SELECT AVG(T1.win_margin) FROM Match AS T1 INNER JOIN Venue AS T2 ON T1.venue_id = T2.venue_id WHERE T2.venue_name = 'Newlands'

Database: soccer_2016
Question: What is the average of Indian players that were born between 1975 and 1985 among all players?
Evidence: Indian players refers to Country_Name = 'India'; born between 1975 and 1985 refers to strftime('%Y',T1.DOB) between '1975' and '1985'; average refers to DIVIDE(COUNT(Country_Name = 'India'), COUNT(Player_Id))
SQL: SELECT CAST(SUM(CASE WHEN T2.Country_Name = 'India' THEN 1 ELSE 0 END) AS REAL) / COUNT(T1.Player_Id) FROM Player AS T1 INNER JOIN Country AS T2 ON T1.Country_Name = T2.Country_ID WHERE strftime('%Y', T1.DOB) BETWEEN '1975' AND '1985'

Database: student_loan
Question: What is the percentage ratio of students who are enlisted in foreign legions that have a due payment?
Evidence: have a due payment refers to bool = 'pos'; in foreign legions refers to organ = 'foreign_legion'; ratio refers to DIVIDE(COUNT(bool = 'pos'), COUNT(bool = 'neg'))
SQL: SELECT CAST(SUM(IIF(T1.bool = 'pos', 1, 0)) AS REAL) * 100 / SUM(IIF(T1.bool = 'neg', 1, 0)) FROM no_payment_due AS T1 INNER JOIN enlist AS T2 ON T1.`name` = T2.`name` WHERE T2.organ = 'foreign_legion'

Database: synthea
Question: Provide the patients' full names who received the extraction of wisdom tooth.
Evidence: patient's full name refers to first, last; extraction of wisdom tooth refers to DESCRIPTION = 'Extraction of wisdom tooth' from procedures;
SQL: SELECT T1.first, T1.last FROM patients AS T1 INNER JOIN procedures AS T2 ON T1.patient = T2.PATIENT WHERE T2.DESCRIPTION = 'Extraction of wisdom tooth'

Database: disney
Question: Among the movies released from 2001 to 2005, list down the titles and directors of the movies which had a total gross of more than 100% above the average.
Evidence: Released from 2001 to 2005 refers to substr(release_date, length(release_date) - 3, length(release_date)) between '2001' and '2005'; DIVIDE(SUM(total_gross), COUNT(movie_title));
SQL: SELECT T2.name, T2.director FROM movies_total_gross AS T1 INNER JOIN director AS T2 ON T1.movie_title = T2.name WHERE SUBSTR(T1.release_date, LENGTH(T1.release_date) - 3, LENGTH(T1.release_date)) BETWEEN '2001' AND '2005' AND CAST(REPLACE(trim(T1.total_gross, '$'), ',', '') AS REAL) / ( SELECT SUM(CAST(REPLACE(trim(T3.total_gross, '$'), ',', '') AS REAL)) / COUNT(T3.movie_title) AS avg_gross FROM movies_total_gross AS T3 INNER JOIN director AS T4 ON T3.movie_title = T4.name WHERE SUBSTR(T3.release_date, LENGTH(T3.release_date) - 3, LENGTH(T3.release_date)) BETWEEN '2001' AND '2005' ) - 1 > 1

Database: olympics
Question: Calculate the average age of the persons who participated in the 1992 Summer Games.
Evidence: DIVIDE(SUM(age), COUNT(person_id)) where games_name = '1992 Summer';
SQL: SELECT AVG(T2.age) FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id INNER JOIN person AS T3 ON T2.person_id = T3.id WHERE T1.games_name = '1992 Summer'

Database: disney
Question: List the movie titles directed by Jack Kinney.
Evidence: Jack Kinney refers to director = 'Jack Kinney';
SQL: SELECT name FROM director WHERE director = 'Jack Kinney'

Database: world_development_indicators
Question: List the long name of countries with indicator name in 1980.
Evidence: with any indicator name implies IndicatorName is not NULL; Year = '1980';
SQL: SELECT DISTINCT T1.LongName FROM Country AS T1 INNER JOIN Indicators AS T2 ON T1.CountryCode = T2.CountryCode WHERE T2.Year = 1980 AND T2.IndicatorName IS NOT NULL

Database: university
Question: How many universities scored 40 in teaching criteria?
Evidence: scored 40 refers to score = 40; in teaching refers to criteria_name = 'Teaching'
SQL: SELECT COUNT(*) FROM ranking_criteria AS T1 INNER JOIN university_ranking_year AS T2 ON T1.id = T2.ranking_criteria_id WHERE T2.score = 40 AND T1.criteria_name = 'Teaching' AND T2.score = 40

Database: social_media
Question: How many tweets are in English?
Evidence: english is the language and refers to Lang = 'en'
SQL: SELECT COUNT(TweetID) AS tweet_number FROM twitter WHERE Lang = 'en'

Database: soccer_2016
Question: List the name of all New Zealand umpires.
Evidence: New Zealand umpires refers to Country_Name = 'New Zealand'; name of umpires refers to Umpire_Name
SQL: SELECT T1.Umpire_Name FROM Umpire AS T1 INNER JOIN Country AS T2 ON T1.Umpire_Country = T2.Country_Id WHERE T2.Country_Name = 'New Zealand'

Database: food_inspection_2
Question: What is the percentage of establishments with a risk level of 1 among all of the establishments that passed the inspection?
Evidence: a risk level of 1 refers to risk_level = 1; pass the inspection refers to results = 'Pass'; percentage = divide(sum(license_no where risk_level = 1), count(license_no)) * 100% where results = 'Pass'
SQL: SELECT CAST(COUNT(CASE WHEN T1.risk_level = 1 THEN T1.license_no END) AS REAL) * 100 / COUNT(T1.license_no) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE T2.results = 'Pass'

Database: synthea
Question: For how long did Elly Koss's cystitis last?
Evidence: SUM(MULTIPLY(365, SUBTRACT(strftime('%Y', STOP), strftime('%Y', START))), MULTIPLY(30, SUBTRACT(strftime('%m', STOP), strftime('%m', START))), SUBTRACT(strftime('%d', STOP), strftime('%d', START))) where DESCRIPTION = 'Cystitis';
SQL: SELECT strftime('%J', T2.STOP) - strftime('%J', T2.START) AS days FROM patients AS T1 INNER JOIN conditions AS T2 ON T1.patient = T2.PATIENT WHERE T1.first = 'Elly' AND T1.last = 'Koss' AND T2.description = 'Cystitis'

Database: video_games
Question: Indicate, by region, which platform has sold the most games.
Evidence: region refers to region_name; platform refers to game_platform; sold the most games refers to MAX(SUM(num_sales));
SQL: SELECT T.region_name FROM ( SELECT T1.platform_name, T4.region_name, SUM(T3.num_sales) FROM platform AS T1 INNER JOIN game_platform AS T2 ON T1.id = T2.platform_id INNER JOIN region_sales AS T3 ON T1.id = T3.game_platform_id INNER JOIN region AS T4 ON T3.region_id = T4.id GROUP BY T1.platform_name, T4.region_name ORDER BY SUM(T3.num_sales) DESC LIMIT 1 ) t

Database: chicago_crime
Question: What is the name of the community that has the highest number of crimes related to prostitution?
Evidence: name of the community refers to community_area_name; the highest number of crimes refers to max(case_number); prostitution refers to primary_description = 'PROSTITUTION'
SQL: SELECT T3.community_area_name FROM Crime AS T1 INNER JOIN IUCR AS T2 ON T1.iucr_no = T2.iucr_no INNER JOIN Community_Area AS T3 ON T1.community_area_no = T3.community_area_no WHERE T2.primary_description = 'PROSTITUTION' GROUP BY T1.iucr_no ORDER BY T1.case_number DESC LIMIT 1