Database: authors
Question: What is the average number of papers published in the journal "Molecular Brain" every year from 2008 to 2011?
Evidence: "Molecular Brain" is the FullName of journal; year from 2008 to 2011 refers to Year BETWEEN 2008 AND 2011; average = Divide (Count(Id),4)
SQL: SELECT CAST(COUNT(T2.Id) AS REAL) / COUNT(DISTINCT T2.Year) FROM Journal AS T1 INNER JOIN Paper AS T2 ON T1.Id = T2.JournalId WHERE T1.FullName = 'Molecular Brain' AND T2.Year BETWEEN 2008 AND 2011

Database: retail_world
Question: Among the employees who handled orders to Brazil, who has the highest salary and calculate the average salary of them.
Evidence: orders to Brazil refers to ShipCountry = 'Brazil'; highest salary refers to max(salary); average salary = AVG(Salary)
SQL: SELECT T1.FirstName, T1.LastName, AVG(T1.Salary) FROM Employees AS T1 INNER JOIN Orders AS T2 ON T1.EmployeeID = T2.EmployeeID WHERE T2.ShipCountry = 'Brazil' GROUP BY T1.FirstName, T1.LastName ORDER BY SUM(T1.Salary) DESC LIMIT 1

Database: cookbook
Question: Name the recipes which can lead to constipation.
Evidence: can lead to constipation refers to iron > 20
SQL: SELECT T1.title FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id WHERE T2.iron > 20

Database: public_review_platform
Question: How many businesses with the category are open from Monday to Thursday?
Evidence: open from Monday to Thursday refers to day_of_week BETWEEN Monday AND Thursday and day_id BETWEEN 2 AND 5;
SQL: SELECT COUNT(T2.business_id) FROM Categories AS T1 INNER JOIN Business_Categories AS T2 ON T1.category_id = T2.category_id INNER JOIN Business AS T3 ON T2.business_id = T3.business_id INNER JOIN Business_Hours AS T4 ON T3.business_id = T4.business_id INNER JOIN Days AS T5 ON T4.day_id = T5.day_id WHERE T5.day_of_week LIKE 'Monday' OR T5.day_of_week LIKE 'Tuesday' OR T5.day_of_week LIKE 'Wednesday' OR T5.day_of_week LIKE 'Thursday'

Database: retail_complains
Question: How many female clients are there older than 30?
Evidence: female refers to sex = 'Female'; older than 30 refers to age > 30
SQL: SELECT COUNT(sex) FROM client WHERE sex = 'Female' AND age > 30

Database: student_loan
Question: List all the disabled female students.
SQL: SELECT T1.name FROM disabled AS T1 INNER JOIN male AS T2 ON T1.name <> T2.name

Database: soccer_2016
Question: Which teams have had a player awarded the Purple Cap and another with the Orange Cap in the same season?
SQL: SELECT T5.Team_Name, T1.Orange_Cap, T1.Purple_Cap FROM Season AS T1 INNER JOIN Match AS T2 ON T1.Season_Id = T2.Season_Id INNER JOIN Player_Match AS T3 ON T2.Match_Id = T3.Match_Id INNER JOIN Player AS T4 ON T3.Player_Id = T4.Player_Id INNER JOIN Team AS T5 ON T3.Team_Id = T5.Team_Id GROUP BY T5.Team_Name, T1.Orange_Cap, T1.Purple_Cap

Database: regional_sales
Question: How many online sales were made in May 2018 where products were shipped from Norman?
Evidence: online sales refer to OrderNumber where Sales Channel = 'Online'; May 2018 refers to OrderDate LIKE '5/%/18'; Norman is the name of the city;
SQL: SELECT SUM(CASE WHEN T1.OrderDate LIKE '5/%/18' AND T1.`Sales Channel` = 'Online' AND T2.`City Name` = 'Norman' THEN 1 ELSE 0 END) FROM `Sales Orders` AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StoreID = T1._StoreID

Database: address
Question: For the county where DeSantis Ron is from, what is the average female median age?
Evidence: average female median age refers to Divide (Sum(female_median_age), Count(county))
SQL: SELECT SUM(T4.female_median_age) / COUNT(T1.county) FROM country AS T1 INNER JOIN zip_congress AS T2 ON T1.zip_code = T2.zip_code INNER JOIN congress AS T3 ON T2.district = T3.cognress_rep_id INNER JOIN zip_data AS T4 ON T1.zip_code = T4.zip_code WHERE T3.first_name = 'DeSantis' AND T3.last_name = 'Ron'

Database: sales
Question: In customers with the first name of Erica, how many of them bought a quantity below 200?
Evidence: quantity below 200 refers to quantity < 200;
SQL: SELECT COUNT(T1.ProductID) FROM Sales AS T1 INNER JOIN Customers AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.FirstName = 'Erica' AND T1.Quantity < 200

Database: address
Question: Describe the number of postal points and the countries in West Virginia.
Evidence: postal points refer to zip_code; West Virginia is the name of the state, in which name = 'West Virginia';
SQL: SELECT COUNT(DISTINCT T2.zip_code), COUNT(DISTINCT T2.county) FROM state AS T1 INNER JOIN country AS T2 ON T1.abbreviation = T2.state WHERE T1.name = 'West Virginia'

Database: retail_complains
Question: What are the products that people who were born after 2005 complain about?
Evidence: year > 2005;
SQL: SELECT DISTINCT T2.Product FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T1.year > 2005

Database: university
Question: How many universities had above 30% of international students in 2013?
Evidence: had above 30% of international students refers to pct_international_students > 30; in 2013 refers to year = 2013
SQL: SELECT COUNT(*) FROM university_year WHERE pct_international_students > 30 AND year = 2013

Database: software_company
Question: What is the total number of widowed customers with an age below 50?
Evidence: widowed customers with an age below 50 refer to MARITAL_STATUS = 'Widowed' where age < 50;
SQL: SELECT COUNT(ID) FROM Customers WHERE MARITAL_STATUS = 'Widowed' AND age < 50

Database: university
Question: Calculate the average score per university under Alumni criteria in 2008.
Evidence: under Alumni criteria refers to criteria_name = 'Alumni'; in 2008 refers to year = 2008; average score refers to DIVIDE(SUM(score), COUNT(university_id))
SQL: SELECT AVG(T2.score) FROM ranking_criteria AS T1 INNER JOIN university_ranking_year AS T2 ON T1.id = T2.ranking_criteria_id WHERE T1.criteria_name = 'Alumni' AND T2.year = 2008

Database: college_completion
Question: Give the total number of all graduated students from a 2-year public schools in Alabama in 2011.
Evidence: number of graduated students refers to grad_cohort; 2-year refers to level = '2-year'; public refers to control = 'Public'; Alabama refers to state = 'Alabama'; in 2011 refers to year = '2011'; reace = 'X' means all students.
SQL: SELECT SUM(T2.grad_cohort) FROM state_sector_details AS T1 INNER JOIN state_sector_grads AS T2 ON T2.stateid = T1.stateid WHERE T1.state = 'Alabama' AND T2.year = 2011 AND T1.level = '2-year' AND T1.control = 'Public' AND T2.race = 'X'

Database: authors
Question: Write down the author's name and IDs who are affiliated with Univeristiy of Oulu.
Evidence: "University of Oulu" is the Affiliation Organization
SQL: SELECT Name, id FROM Author WHERE Affiliation = 'University of Oulu'

Database: movie_platform
Question: For all ratings which are rated in year 2020, name the movies which has the rating scored 4 and above.
Evidence: ratings in year 2020 refers to rating_timestamp_utc like '%2020%'; rating_score > = 4;
SQL: SELECT T2.movie_title FROM ratings AS T1 INNER JOIN movies AS T2 ON T1.movie_id = T2.movie_id WHERE CAST(SUBSTR(T1.rating_timestamp_utc, 1, 4) AS INTEGER) = 2020 AND CAST(SUBSTR(T1.rating_timestamp_utc, 6, 2) AS INTEGER) > 4

Database: donor
Question: Is donor “22cbc920c9b5fa08dfb331422f5926b5” a teacher?
Evidence: donor “22cbc920c9b5fa08dfb331422f5926b5” refers to donor_acctid = '22cbc920c9b5fa08dfb331422f5926b5'; is a teacher refers to is_teacher_acct;
SQL: SELECT DISTINCT is_teacher_acct FROM donations WHERE donor_acctid = '22cbc920c9b5fa08dfb331422f5926b5'