Database: movies_4
Question: What is the average revenue made by Latin movies?
Evidence: Latin movies refers to language_name = 'Latin'; average revenue = AVG(revenue)
SQL: SELECT AVG(T1.revenue) FROM movie AS T1 INNER JOIN movie_languages AS T2 ON T1.movie_id = T2.movie_id INNER JOIN language AS T3 ON T2.language_id = T3.language_id WHERE T3.language_name = 'Latin'

Database: shakespeare
Question: How many chapters include the character name "First Witch"?
Evidence: character name "First Witch" refers to CharName = 'First Witch'
SQL: SELECT COUNT(T2.chapter_id) FROM characters AS T1 INNER JOIN paragraphs AS T2 ON T1.id = T2.character_id WHERE T1.CharName = 'First Witch'

Database: student_loan
Question: How many female students are not joining any of the organization?
Evidence: female students refers to enlist.name who are NOT in male.name
SQL: SELECT COUNT(name) FROM person WHERE name NOT IN ( SELECT name FROM male ) AND name NOT IN ( SELECT name FROM enrolled )

Database: movie_3
Question: List the descriptions of movies under the category Travel.
Evidence: the category Travel refers to category.name = 'Travel'
SQL: SELECT T1.description FROM film AS T1 INNER JOIN film_category AS T2 ON T1.film_id = T2.film_id INNER JOIN category AS T3 ON T2.category_id = T3.category_id WHERE T3.`name` = 'Travel'

Database: university
Question: Provide the country ID of Cyprus.
Evidence: Cyprus refers to country_name = 'Cyprus';
SQL: SELECT id FROM country WHERE country_name = 'Cyprus'

Database: talkingdata
Question: Give the number of "game-Fishing" apps.
Evidence: game-Fishing refers to category = 'game-Fishing';
SQL: SELECT COUNT(T2.app_id) FROM label_categories AS T1 INNER JOIN app_labels AS T2 ON T1.label_id = T2.label_id WHERE T1.category = 'game-Fishing'

Database: sales
Question: How many sales people managed to sell Headlights - Weatherproof?
Evidence: Headlights - Weatherproof' is name of product
SQL: SELECT COUNT(T2.SalesPersonID) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID WHERE T1.Name = 'Headlights - Weatherproof'

Database: food_inspection
Question: Who were the owners of eateries which had highest health hazard by improper cooking time or temperatures?
Evidence: owners of eateries refer to owner_name; highest health hazard by improper cooking time or temperatures refers to risk_category = 'High Risk' and description = 'Improper cooking time or temperatures';
SQL: SELECT T2.owner_name FROM violations AS T1 INNER JOIN businesses AS T2 ON T1.business_id = T2.business_id WHERE T1.risk_category = 'High Risk' AND T1.description = 'Improper cooking time or temperatures'

Database: retail_world
Question: Please list the full name and region of each employee in alphabetical order.
Evidence: full name refers to FirstName LastName; region refers to RegionDescription
SQL: SELECT DISTINCT T1.FirstName, T1.LastName, T4.RegionDescription FROM Employees AS T1 INNER JOIN EmployeeTerritories AS T2 ON T1.EmployeeID = T2.EmployeeID INNER JOIN Territories AS T3 ON T2.TerritoryID = T3.TerritoryID INNER JOIN Region AS T4 ON T3.RegionID = T4.RegionID ORDER BY T1.FirstName

Database: soccer_2016
Question: How old is SC Ganguly in 2008?
Evidence: SC Ganguly refers to Player_Name = 'SC Ganguly'; old refers to SUBTRACT(2008, strftime('%Y',DOB))
SQL: SELECT 2008 - strftime('%Y', DOB) FROM Player WHERE Player_Name = 'SC Ganguly'

Database: retail_world
Question: What is the name of the contact person of the Pavlova supplier company?
Evidence: contact person refers to ContactName; Pavlova is the name of the product;
SQL: SELECT T2.ContactName FROM Products AS T1 INNER JOIN Suppliers AS T2 ON T1.SupplierID = T2.SupplierID WHERE T1.ProductName = 'Pavlova'

Database: social_media
Question: Among all the tweets posted from Buenos Aires, how many of them were posted on Thursdays?
Evidence: "Buenos Aires" is the City; posted on Thursday refers to Weekday = 'Thursday'
SQL: SELECT COUNT(T1.TweetID) FROM twitter AS T1 INNER JOIN location AS T2 ON T2.LocationID = T1.LocationID WHERE T2.City = 'Buenos Aires' AND T1.Weekday = 'Thursday'

Database: social_media
Question: How many female Twitter users are there from Wisconsin?
Evidence: female users refers to Gender = 'Female'; 'Wisconsin' is the State
SQL: SELECT COUNT(T1.Likes) FROM twitter AS T1 INNER JOIN location AS T2 ON T2.LocationID = T1.LocationID INNER JOIN user AS T3 ON T3.UserID = T1.UserID WHERE T2.State = 'Wisconsin' AND T3.Gender = 'Female'

Database: synthea
Question: How long did Isadora Moen's allergy last? Tell me what kind of allergy she has.
Evidence: how long the allergies last = SUBTRACT(allergies.STOP, allergies.START); kind of allergy refers to allergies.DESCRIPTION;
SQL: SELECT CASE WHEN SUBSTR(T1.STOP, -2, 1) != '9' THEN SUBSTR(T1.STOP, LENGTH(T1.STOP) - 1) + 2000 END - CASE WHEN SUBSTR(T1.START, -2, 1) = '9' THEN SUBSTR(T1.START, LENGTH(T1.START) - 1) + 1900 ELSE SUBSTR(T1.START, LENGTH(T1.START) - 1) + 2000 END AS years , T1.DESCRIPTION FROM allergies AS T1 INNER JOIN patients AS T2 ON T2.patient = T1.PATIENT WHERE T1.STOP IS NOT NULL AND T1.START IS NOT NULL AND T2.first = 'Isadora' AND T2.last = 'Moen'

Database: restaurant
Question: How many restaurants are on Irving Street?
Evidence: Irving Street refers to street_name = 'irving'
SQL: SELECT COUNT(id_restaurant) FROM location WHERE street_name = 'irving'

Database: beer_factory
Question: Which of the root beer brand have the lowest purchase?
Evidence: root beer brand refers to BrandName; lowest purchase refers to MIN(COUNT(BrandID));
SQL: SELECT T2.BrandName FROM rootbeer AS T1 INNER JOIN rootbeerbrand AS T2 ON T1.BrandID = T2.BrandID GROUP BY T2.BrandID ORDER BY COUNT(T1.BrandID) LIMIT 1

Database: chicago_crime
Question: How many solicit on public way prostitution crimes were arrested in West Garfield Park?
Evidence: solicit on public way prostitution crime refers to secondary_description = 'SOLICIT ON PUBLIC WAY' AND primary_description = 'PROSTITUTION'; arrested refers to arrest = 'TRUE'; West Garfield Park refers to community_area_name = 'West Garfield Park'
SQL: SELECT SUM(CASE WHEN T2.arrest = 'TRUE' THEN 1 ELSE 0 END) FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T1.community_area_no = T2.community_area_no INNER JOIN IUCR AS T3 ON T2.iucr_no = T3.iucr_no WHERE T1.community_area_name = 'West Garfield Park' AND T3.secondary_description = 'SOLICIT ON PUBLIC WAY' AND T3.primary_description = 'PROSTITUTION'

Database: public_review_platform
Question: What time does the business with ID no.12 open on Monday?
Evidence: open time refers to opening_time; on Monday refers to day_of_week = 'Monday'; business with ID no. refers to business_id 


SQL: SELECT T1.opening_time FROM Business_Hours AS T1 INNER JOIN Days AS T2 ON T1.day_id = T2.day_id WHERE T1.business_id = 12 AND T2.day_of_week = 'Monday'

Database: university
Question: How many female students are there in University of Pennsylvania in 2011?
Evidence: in 2011 refers to year 2011; female students refers to DIVIDE(MULTIPLY(num_students, pct_female_students), 100); University of Pennsylvania refers to a university name;
SQL: SELECT CAST(T1.num_students * T1.pct_female_students AS REAL) / 100 FROM university_year AS T1 INNER JOIN university AS T2 ON T1.university_id = T2.id WHERE T1.year = 2011 AND T2.university_name = 'University of Pennsylvania'

Database: superstore
Question: Name 10 products that were shipped first class from the East region.
Evidence: shipped first class refers to "Ship Mode" = 'First Class'; Region = 'East'
SQL: SELECT T2.`Product Name` FROM east_superstore AS T1 INNER JOIN product AS T2 ON T1.`Product ID` = T2.`Product ID` WHERE T1.`Ship Mode` = 'First Class' AND T2.Region = 'East' LIMIT 10