Database: address
Question: What is the difference in the number of cities with P.O. box only and cities with Post Office among the cities with area code 787?
Evidence: SUBTRACT(COUNT(type = 'P.O. Box Only'), COUNT(type = 'Post Office')) where area_code = 787;
SQL: SELECT COUNT(CASE WHEN T2.type = 'P.O. Box Only' THEN 1 ELSE NULL END) - COUNT(CASE WHEN T2.type = 'Post Office' THEN 1 ELSE NULL END) AS DIFFERENCE FROM area_code AS T1 INNER JOIN zip_data AS T2 ON T1.zip_code = T2.zip_code WHERE T1.area_code = 787

Database: authors
Question: Among the papers published in 2009, pick 10 and list down the conference's short name of these papers.
Evidence: published in 2009 refers to Year = 2009
SQL: SELECT T2.PaperId, T4.ShortName FROM Author AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.AuthorId INNER JOIN Paper AS T3 ON T2.PaperId = T3.Id INNER JOIN Conference AS T4 ON T3.ConferenceId = T4.Id WHERE T3.Year = 2009 LIMIT 10

Database: olympics
Question: List out all the gold medalist winners in cycling sport.
Evidence: gold medalist winners refer to full_name where medal_name = 'Gold'; cycling sport refers to sport_name = 'Cycling';
SQL: SELECT DISTINCT T5.full_name FROM event AS T1 INNER JOIN competitor_event AS T2 ON T1.id = T2.event_id INNER JOIN games_competitor AS T3 ON T2.competitor_id = T3.id INNER JOIN sport AS T4 ON T1.sport_id = T4.id INNER JOIN person AS T5 ON T3.person_id = T5.id WHERE T4.sport_name = 'Cycling' AND T2.medal_id = 1

Database: regional_sales
Question: List all the order numbers for In-Store sales and find the city where the store is located.
Evidence: In-Store sales refer to Sales Channel = 'In-Store'; city refers to City Name;
SQL: SELECT DISTINCT T1.OrderNumber, T2.`City Name` FROM `Sales Orders` AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StoreID = T1._StoreID WHERE T1.`Sales Channel` = 'In-Store'

Database: authors
Question: List the names of authors affiliated with the University of Oxford in alphabetical order.
Evidence: affiliated with the University of Oxford refers to Affiliation = 'University of Oxford'
SQL: SELECT Name FROM Author WHERE Affiliation = 'University of Oxford' ORDER BY Name ASC

Database: movie_3
Question: Which actor acted in the most films?
Evidence: actor refers to first_name, last_name; the most film refers to MAX(SUM(film_id))
SQL: SELECT T.first_name, T.last_name FROM ( SELECT T2.first_name, T2.last_name, SUM(T1.film_id) AS num FROM film_actor AS T1 INNER JOIN actor AS T2 ON T1.actor_id = T2.actor_id GROUP BY T2.first_name, T2.last_name ) AS T ORDER BY T.num DESC LIMIT 1

Database: student_loan
Question: Mention the name of students who filed for bankruptcy and have never been absent from school.
Evidence: have never been absent refers to month = 0;
SQL: SELECT T1.name FROM longest_absense_from_school AS T1 INNER JOIN filed_for_bankrupcy AS T2 ON T1.name = T2.name WHERE T1.month = 0

Database: donor
Question: How many schools which have suburban metro are there in Bethlehem?
Evidence: Bethlehem is school_city; suburban metro refers to school_metro = 'suburban';
SQL: SELECT COUNT(schoolid) FROM projects WHERE school_city = 'Bethlehem' AND school_metro = 'suburban'

Database: authors
Question: List the authors and journal short name of the papers with "chemiluminescence" in its title and has a journal ID from 245 to 250.
Evidence: with "chemiluminescence" in its title refers to Title LIKE 'chemiluminescence%'; journal ID from 245 to 250 refers to JournalId BETWEEN 245 AND 250
SQL: SELECT T2.Name, T3.ShortName FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId INNER JOIN Journal AS T3 ON T1.JournalId = T3.Id WHERE T1.JournalId BETWEEN 245 AND 250 AND T1.Title LIKE '%chemiluminescence%'

Database: cookbook
Question: Among the recipes with alcohol content over 10, which recipe takes the longest to prepare?
Evidence: with alcohol content over 10 refers to alcohol > 10; takes the longest to prepare refers to MAX(prep_min)
SQL: SELECT T1.title FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id WHERE T2.alcohol > 10 ORDER BY T1.prep_min DESC LIMIT 1

Database: movies_4
Question: Provide the titles and revenues of the movies produced by the DreamWorks company.
Evidence: produced by the DreamWorks company refers to company_name = 'DreamWorks'
SQL: SELECT T1.title, T1.revenue FROM movie AS T1 INNER JOIN movie_company AS T2 ON T1.movie_id = T2.movie_id INNER JOIN production_company AS T3 ON T2.company_id = T3.company_id WHERE T3.company_name = 'DreamWorks'

Database: university
Question: Calculate the number of female students at Arizona State University in 2014.
Evidence: female students refers to DIVIDE(MULTIPLY(pct_female_students, num_students), 100); at Arizona State University refers to university_name = 'Arizona State University'; in 2014 refers to year = 2014
SQL: SELECT CAST(T2.num_students * T2.pct_female_students AS REAL) / 100 FROM university AS T1 INNER JOIN university_year AS T2 ON T1.id = T2.university_id WHERE T1.university_name = 'Arizona State University' AND T2.year = 2014

Database: books
Question: What is the publisher name of the book titled The Illuminati?
Evidence: "The Illuminati" is the title of book
SQL: SELECT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id WHERE T1.title = 'The Illuminati'

Database: software_company
Question: Among all the customers, how many of them are teenagers?
Evidence: teenager is a person aged between 13 and 19 years;
SQL: SELECT COUNT(ID) FROM Customers WHERE age >= 13 AND age <= 19

Database: retail_world
Question: Identify the number of employees in Northern sales region.
Evidence: Northern sales region refers to RegionDescription = 'Northern'
SQL: SELECT COUNT(T2.EmployeeID) FROM Territories AS T1 INNER JOIN EmployeeTerritories AS T2 ON T1.TerritoryID = T2.TerritoryID INNER JOIN Region AS T3 ON T1.RegionID = T3.RegionID WHERE T3.RegionDescription = 'Northern'

Database: shipping
Question: What is the model year of the truck used in shipment id 1003?
Evidence: shipment id 1003 refers to ship_id = 1003
SQL: SELECT T1.model_year FROM truck AS T1 INNER JOIN shipment AS T2 ON T1.truck_id = T2.truck_id WHERE T2.ship_id = '1003'

Database: video_games
Question: How many different publishers have published a game that starts with "Marvel"?
Evidence: game that starts with "Marvel" refers to game_name LIKE 'Marvel%';
SQL: SELECT COUNT(DISTINCT T1.publisher_id) FROM game_publisher AS T1 INNER JOIN game AS T2 ON T1.game_id = T2.id WHERE T2.game_name LIKE 'Marvel%'

Database: retails
Question: Please indicate the names of the customers whose order with a total price over $300000.
Evidence: customer name refers to c_name; a total price over $300000 refers to o_totalprice > 300000
SQL: SELECT T2.c_name FROM orders AS T1 INNER JOIN customer AS T2 ON T1.o_custkey = T2.c_custkey WHERE T1.o_totalprice > 300000

Database: talkingdata
Question: To which categories does app user no.1977658975649780000 belong?
Evidence: app no. refers to app_id; app_id = 1977658975649780000;
SQL: SELECT T1.category FROM label_categories AS T1 INNER JOIN app_labels AS T2 ON T1.label_id = T2.label_id WHERE T2.app_id = 1977658975649780000

Database: retail_world
Question: Calculate the percentage of products supplied by Gai pturage over all products.
Evidence: "Gai pturage" is the CompanyName; Percentage = Divide (Count(SupplierID where CompanyName = 'Gai pturage'), Count (SupplierID)) * 100
SQL: SELECT CAST(COUNT(CASE WHEN T2.CompanyName = 'Gai pturage' THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T1.SupplierID) FROM Products AS T1 INNER JOIN Suppliers AS T2 ON T1.SupplierID = T2.SupplierID