Database: books
Question: How many orders did Antonia Poltun return?
Evidence: order returned refers to status_value = 'Returned'
SQL: SELECT COUNT(*) FROM order_status AS T1 INNER JOIN order_history AS T2 ON T1.status_id = T2.status_id INNER JOIN cust_order AS T3 ON T3.order_id = T2.order_id INNER JOIN customer AS T4 ON T4.customer_id = T3.customer_id WHERE T1.status_value = 'Returned' AND T4.first_name = 'Antonia' AND T4.last_name = 'Poltun'

Database: authors
Question: Among the author included in the paper of "Inspection resistant memory: Architectural support for security from physical examination", write down the author name and ID who were affiliated with Microsoft Research, USA.
Evidence: "Inspection resistant memory: Architectural support for security from physical examination" is the title of paper;  'Microsoft Research, USA' is the Affiliation
SQL: SELECT T2.Name, T1.Id FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Title = 'Inspection resistant memory: Architectural support for security FROM physical examination' AND T2.Affiliation = 'Microsoft Research, USA'

Database: world_development_indicators
Question: What upper middle income country under East Asia & Pacific region which covers the topic about Social Protection & Labor: Migration
? Indicate the short name of the said country.
Evidence: upper middle income country refers to incomegroup = 'Upper middle income'
SQL: SELECT DISTINCT T1.ShortName FROM Country AS T1 INNER JOIN footnotes AS T2 ON T1.CountryCode = T2.CountryCode INNER JOIN Series AS T3 ON T2.Seriescode = T3.SeriesCode WHERE T1.IncomeGroup = 'Upper middle income' AND T1.Region = 'East Asia & Pacific' AND T3.Topic = 'Social Protection & Labor: Migration'

Database: codebase_comments
Question: What format does the method number 8's comment have?
Evidence: format refers CommentIsXml; method number refers to Method_100k.Id; Method_100k.Id = 8;
SQL: SELECT CASE WHEN CommentIsXml = 0 THEN 'isNotXMLFormat' WHEN CommentIsXml = 1 THEN 'isXMLFormat' END format FROM Method WHERE Id = 8

Database: retail_complains
Question: Give the client ID of the complaint received on April 16, 2014 and submitted through fax.
Evidence: April 16, 2014 refers to Date received = '2014-04-16'; submitted through fax refers to Submitted via = 'Fax'
SQL: SELECT T2.Client_ID FROM callcenterlogs AS T1 INNER JOIN events AS T2 ON T1.`Complaint ID` = T2.`Complaint ID` WHERE T2.`Submitted via` = 'Fax' AND T1.`Date received` = '2014-04-16'

Database: soccer_2016
Question: How many of the matches are Superover?
Evidence: Superover refers to Outcome_Type = 'Superover'
SQL: SELECT SUM(CASE WHEN T2.Outcome_Type = 'Superover' THEN 1 ELSE 0 END) FROM Match AS T1 INNER JOIN Outcome AS T2 ON T2.Outcome_Id = T1.Outcome_type

Database: mental_health_survey
Question: For the question “What US state or territory do you work in?”, how many people gave "Kansas" as the answer?
Evidence: question refers to questiontext; AnswerText = 'Kansas'
SQL: SELECT COUNT(T1.UserID) FROM Answer AS T1 INNER JOIN Question AS T2 ON T1.QuestionID = T2.questionid WHERE T2.questiontext LIKE 'What US state or territory do you work in?' AND T1.AnswerText = 'Kansas'

Database: movie_3
Question: What is the total amount of money Mary Smith has spent on film rentals?
Evidence: the total amount = sum(amount)
SQL: SELECT SUM(T1.amount) FROM payment AS T1 INNER JOIN customer AS T2 ON T1.customer_id = T2.customer_id WHERE T2.first_name = 'MARY' AND T2.last_name = 'SMITH'

Database: shakespeare
Question: Please list the character names of all the characters from the work Twelfth Night.
Evidence: character names refers to CharName; Twelfth Night refers to Title = 'Twelfth Night'
SQL: SELECT DISTINCT T4.CharName FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id INNER JOIN characters AS T4 ON T3.character_id = T4.id WHERE T1.Title = 'Twelfth Night'

Database: professional_basketball
Question: Which player from "AFS" team has the tallest height?
Evidence: "AFS" is the tmID; tallest height refers to Max(height)
SQL: SELECT T1.firstName, T1.middleName, T1.lastName FROM players AS T1 INNER JOIN players_teams AS T2 ON T1.playerID = T2.playerID WHERE T2.tmID = 'AFS' ORDER BY T1.height DESC LIMIT 1

Database: public_review_platform
Question: State the locations of all Pet Services business.
Evidence: location refers to city; Pet Services business refers to category_name = 'Pet Services'
SQL: SELECT T1.city FROM Business AS T1 INNER JOIN Business_Categories AS T2 ON T1.business_id = T2.business_id INNER JOIN Categories AS T3 ON T2.category_id = T3.category_id WHERE T3.category_name = 'Pet Services'

Database: menu
Question: Please list the prices of the dish "Clear green turtle" on every menu page it appeared on.
Evidence: Clear green turtle is a name of dish;
SQL: SELECT T2.price FROM Dish AS T1 INNER JOIN MenuItem AS T2 ON T1.id = T2.dish_id WHERE T1.name = 'Clear green turtle'

Database: social_media
Question: What is the average number of tweets posted by the users in a city in Argentina?
Evidence: "Argentina" is the Country; average number of tweets in a city = Divide (Count(TweetID where Country = 'Argentina'), Count (City))
SQL: SELECT SUM(CASE WHEN T2.City = 'Buenos Aires' THEN 1.0 ELSE 0 END) / COUNT(T1.TweetID) AS avg FROM twitter AS T1 INNER JOIN location AS T2 ON T2.LocationID = T1.LocationID WHERE T2.Country = 'Argentina'

Database: chicago_crime
Question: List crimes that the FBI has classified as Drug Abuse by their report number.
Evidence: "Drug Abuse" is the title of crime
SQL: SELECT T2.report_no FROM FBI_Code AS T1 INNER JOIN Crime AS T2 ON T2.fbi_code_no = T1.fbi_code_no WHERE T1.title = 'Drug Abuse'

Database: soccer_2016
Question: List the cities located in U.A.E.
Evidence: city refers to City_Name; U.A.E refers to Country_Name = 'U.A.E'
SQL: SELECT T1.City_Name FROM City AS T1 INNER JOIN Country AS T2 ON T2.Country_Id = T1.Country_id WHERE T2.Country_Name = 'U.A.E'

Database: beer_factory
Question: Which type of card did Dick Ruthven use to pay for all of his transactions?
Evidence: type of card refers to CreditCardType;
SQL: SELECT DISTINCT T2.CreditCardType FROM customers AS T1 INNER JOIN `transaction` AS T2 ON T1.CustomerID = T2.CustomerID WHERE T1.First = 'Dick' AND T1.Last = 'Ruthven'

Database: olympics
Question: In which cities were the 1976 winter and summer games held?
Evidence: cities refer to city_name; 1976 winter and summer games refer to games_name IN ('1976 Winter', '1976 Summer');
SQL: SELECT T2.city_name FROM games_city AS T1 INNER JOIN city AS T2 ON T1.city_id = T2.id INNER JOIN games AS T3 ON T1.games_id = T3.id WHERE T3.games_name IN ('1976 Summer', '1976 Winter')

Database: retails
Question: What is the profit for part no.98768 in order no.1?
Evidence: part no.98768 refers to l_partkey = 98768; order no.1 refers to l_orderkey = 1; profit = subtract(multiply(l_extendedprice, subtract(1, l_discount)), multiply(ps_supplycost, l_quantity))
SQL: SELECT T1.l_extendedprice * (1 - T1.l_discount) - T2.ps_supplycost * T1.l_quantity FROM lineitem AS T1 INNER JOIN partsupp AS T2 ON T1.l_suppkey = T2.ps_suppkey WHERE T1.l_orderkey = 1 AND T1.l_partkey = 98768

Database: olympics
Question: What is the percentage of athletes from Vanuatu who are taller than 175?
Evidence: DIVIDE(COUNT(id where height > 175), COUNT(id)) as percentage where region_name = 'Vanuatu';
SQL: SELECT CAST(COUNT(CASE WHEN T3.height > 175 THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T2.person_id) FROM noc_region AS T1 INNER JOIN person_region AS T2 ON T1.id = T2.region_id INNER JOIN person AS T3 ON T2.person_id = T3.id WHERE T1.region_name = 'Vanuatu'

Database: retail_world
Question: How many companies are there in the city of London?
Evidence: companies refers to CompanyName;
SQL: SELECT COUNT(CompanyName) FROM Customers WHERE City = 'London'