Database: shakespeare
Question: What is the title which has character named "Froth"?
Evidence: character named "Froth" refers to CharName = 'Froth'
SQL: SELECT DISTINCT T1.title 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 T4.CharName = 'Froth'

Database: beer_factory
Question: How many sweet bottled root beers that do not contain cane sugar were purchased in 2015 through the selling company located in Sac State American River Courtyard?
Evidence: sweet refers to Honey = 'TRUE'; bottled refers to ContainerType = 'Bottle'; do not contain cane sugar refers to CaneSugar = 'FALSE'; in 2015 refers to PurchaseDate < = '2015-12-31'; Sac State American River Courtyard refers to LocationName = 'Sac State American River Courtyard';
SQL: SELECT COUNT(T1.BrandID) FROM rootbeer AS T1 INNER JOIN rootbeerbrand AS T2 ON T1.BrandID = T2.BrandID INNER JOIN location AS T3 ON T1.LocationID = T3.LocationID WHERE T3.LocationName = 'Sac State American River Courtyard' AND T1.PurchaseDate LIKE '2015%' AND T2.Honey = 'TRUE' AND T2.CaneSugar = 'FALSE' AND T1.ContainerType = 'Bottle'

Database: retails
Question: Please list the phone numbers of all the suppliers for the parts ordered in order no.1.
Evidence: phone number refers to s_phone; order no.1 refers to l_orderkey = 1
SQL: SELECT T2.s_phone FROM lineitem AS T1 INNER JOIN supplier AS T2 ON T1.l_suppkey = T2.s_suppkey WHERE T1.l_orderkey = 1

Database: retail_complains
Question: Was the tag in the complaint filed by Matthew Pierce on 2016/10/28 approved by himself?
Evidence: on 2016/10/28 refers to Date received = '2016-10-28'; "Consumer consent provided?" in (null, 'N/A', 'Empty') means that the company didn't get the permission of consent; "Consumer consent provided?" not in (null, 'N/A', 'Empty') means that customers provide the consent for this tag;
SQL: SELECT CASE WHEN T2.`Consumer consent provided?` IN (NULL, 'N/A', 'Empty') THEN 'No' ELSE 'Yes' END FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T1.first = 'Matthew' AND T1.last = 'Pierce' AND T2.`Date received` = '2016-10-28'

Database: movielens
Question: How many latest released dramas and action movies?
SQL: SELECT COUNT(T1.movieid) FROM movies2directors AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid WHERE T2.year = 4 AND T1.genre IN ('Action', 'drama')

Database: computer_student
Question: Among the students being advised by advisors, which students' year in the program do the advisors advise the majority of?
Evidence: students refers to student = 1; students' year in the program do the advisors advise the majority of refers to max(count(yearsInProgram))
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.yearsInProgram ORDER BY COUNT(T1.p_id_dummy) DESC LIMIT 1

Database: book_publishing_company
Question: State the publisher name for publisher ID 877? Calculate its average year to date sales.
Evidence: publisher id refers to pub_id; publisher name refers to pub_name; average year to date sales = AVG(ytd_sales)
SQL: SELECT T2.pub_name, AVG(T1.ytd_sales) FROM titles AS T1 INNER JOIN publishers AS T2 ON T1.pub_id = T2.pub_id WHERE T1.pub_id = '0877' GROUP BY T2.pub_name

Database: book_publishing_company
Question: List all employees who are at the maximum level in their job designation.
Evidence: maximum level in their job designation refers to job_lvl = MAX(max_lvl)
SQL: SELECT T1.fname, T1.lname FROM employee AS T1 INNER JOIN jobs AS T2 ON T1.job_id = T2.job_id WHERE T1.job_lvl = T2.max_lvl

Database: regional_sales
Question: What is the percentage of total orders of Stephen Payne that had a net profit of over 1000?
Evidence: Sales Team = 'Stephen Payne'; net profit can be computed as SUBTRACT(Unit Price, Unit Cost); DIVIDE(COUNT(OrderNumber where Sales Team = 'Stephen Payne' and Net Profit > 1000)), (COUNT(OrderNumber where Sales Team = 'Stephen Payne')) as percentage;
SQL: SELECT CAST(SUM(CASE WHEN REPLACE(T1.`Unit Price`, ',', '') - REPLACE(T1.`Unit Cost`, ',', '') > 1000 THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.OrderNumber) FROM `Sales Orders` AS T1 INNER JOIN `Sales Team` AS T2 ON T2.SalesTeamID = T1._SalesTeamID WHERE T2.`Sales Team` = 'Stephen Payne'

Database: authors
Question: List the names of all authors affiliated with Birkbeck University of London.
Evidence: affiliated with Birkbeck University of London refers to Affiliation = 'Birkbeck University of London'
SQL: SELECT Name FROM Author WHERE Affiliation = 'Birkbeck University of London'

Database: app_store
Question: How many users mildly likes the 7 Minute Workout app and when was it last updated?
Evidence: mildly likes the app refers to Sentiment_Polarity> = 0 and Sentiment_Polarity<0.5;
SQL: SELECT COUNT(T2.Sentiment_Polarity), T1."Last Updated" FROM playstore AS T1 INNER JOIN user_reviews AS T2 ON T1.App = T2.App WHERE T1.App = '7 Minute Workout' AND T2.Sentiment_Polarity BETWEEN 0 AND 0.5

Database: coinmarketcap
Question: When was Lebowskis not opened?
Evidence: when refers to date; not opened refers to open IS NULL
SQL: SELECT DISTINCT T2.date FROM coins AS T1 INNER JOIN historical AS T2 ON T1.id = T2.coin_id WHERE T1.name = 'Lebowskis' AND (T2.open IS NULL OR T2.open = 0)

Database: public_review_platform
Question: How many Yelp_Business close after 8PM on Mondays?
Evidence: close after 8PM refers to closing_time = '9PM' or closing_time = '10PM' closing_time = '11PM' closing_time = '12PM'; Mondays refers to day_of_week = 'Monday'
SQL: SELECT COUNT(T1.business_id) FROM Business_Hours AS T1 INNER JOIN Days AS T2 ON T1.day_id = T2.day_id WHERE T2.day_of_week LIKE 'Monday' AND T1.closing_time > '8PM'

Database: soccer_2016
Question: How many players are Indians?
Evidence: are Indians refers to Country_Name = 'India'
SQL: SELECT COUNT(T1.Player_Id) FROM Player AS T1 INNER JOIN Country AS T2 ON T1.Country_Name = T2.Country_ID WHERE T2.Country_Name = 'India'

Database: music_platform_2
Question: Which category has the most reviews?
Evidence: Most review refers to Max(Count(reviews.podcast_id))
SQL: SELECT T1.category FROM categories AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id GROUP BY T1.category ORDER BY COUNT(T2.podcast_id) DESC LIMIT 1

Database: sales_in_weather
Question: In March 2014, which weather stations recorded the highest number of days whose temperature is below the 30-year normal?
Evidence: in March 2014 refers to substring (date, 1, 4) = '2014' and substring (date, 6, 2) = '03'; temperature is below the 30-year normal refers to depart < 0; highest number of days refers to Max(Count(date))
SQL: SELECT station_nbr FROM weather WHERE SUBSTR(`date`, 1, 4) = '2014' AND SUBSTR(`date`, 6, 2) = '03' AND depart < 0 GROUP BY station_nbr HAVING COUNT(DISTINCT `date`) = ( SELECT COUNT(DISTINCT `date`) FROM weather WHERE SUBSTR(`date`, 1, 4) = '2014' AND SUBSTR(`date`, 6, 2) = '03' AND depart < 0 GROUP BY station_nbr ORDER BY COUNT(`date`) DESC LIMIT 1 )

Database: book_publishing_company
Question: For the quantities, what percent more did the store in Fremont sell than the store in Portland in 1993?
Evidence: qty is abbreviation for quantity; Fremont and Portland are name of city; sell in 1993 refers to YEAR(ord_date) = 1993; percentage = DIVIDE(
SUBTRACT(SUM(qty where city = ‘Fremont’ and year(ord_date = 1993)), 
SUM(qty where city = ‘Portland’ and year(ord_date = 1993))), SUM(qty where city = ‘Fremont’ and year(ord_date = 1993)) *100
SQL: SELECT CAST(SUM(CASE WHEN T2.city = 'Fremont' THEN qty END) - SUM(CASE WHEN T2.city = 'Portland' THEN qty END) AS REAL) * 100 / SUM(CASE WHEN T2.city = 'Fremont' THEN qty END) FROM sales AS T1 INNER JOIN stores AS T2 ON T1.stor_id = T2.stor_id WHERE STRFTIME('%Y', T1.ord_date) = '1993'