Database: university
Question: Please list the names of all the universities in Australia.
Evidence: in Australia refers to country_name = 'Australia'; name of university refers to university_name
SQL: SELECT T1.university_name FROM university AS T1 INNER JOIN country AS T2 ON T1.country_id = T2.id WHERE T2.country_name = 'Australia'

Database: simpson_episodes
Question: List the name character awarded for the Outstanding Voice-Over Performance award in 2009.
Evidence: in 2009 refers to year = 2009
SQL: SELECT T2.character FROM Award AS T1 INNER JOIN Character_Award AS T2 ON T1.award_id = T2.award_id WHERE T1.year = 2009 AND T1.award = 'Outstanding Voice-Over Performance';

Database: student_loan
Question: Among the unemployed students, how many of them have no payment due?
Evidence: have no payment due refers to bool = 'neg';


SQL: SELECT COUNT(T1.name) FROM unemployed AS T1 INNER JOIN no_payment_due AS T2 ON T1.name = T2.name WHERE T2.bool = 'neg'

Database: soccer_2016
Question: Give the country where St. George's Park is located.
Evidence: country refers to Country_Name; St. George's Park refers to Venue_Name = 'St George''s Park'
SQL: SELECT T3.Country_Name FROM Venue AS T1 INNER JOIN City AS T2 ON T2.City_Id = T1.City_Id INNER JOIN Country AS T3 ON T3.Country_Id = T2.Country_id WHERE T1.Venue_Name = 'St George''s Park'

Database: airline
Question: List the air carrier's description with arrival time lower than the 40% of the average arrival time of flights that flew to Phoenix.
Evidence: arrival time lower than the 40% of the average arrival time refers to ARR_TIME < MULTIPLY(AVG(ARR_TIME), 0.4); flew to Phoenix refers to DEST = 'PHX';
SQL: SELECT T1.Description FROM `Air Carriers` AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.OP_CARRIER_AIRLINE_ID WHERE T2.DEST = 'PHX' AND T2.ARR_TIME < ( SELECT AVG(ARR_TIME) * 0.4 FROM Airlines ) GROUP BY T1.Description

Database: movie_platform
Question: Name all the list titles created by user 4208563.
Evidence: user 4208563 refers to user_id = 4208563
SQL: SELECT list_title FROM lists WHERE user_id LIKE 4208563

Database: books
Question: Name the publisher who published the most books.
Evidence: published the most books refers to Max(Count(book_id)); publisher refers to publisher_name
SQL: SELECT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id GROUP BY T2.publisher_name ORDER BY COUNT(T2.publisher_id) DESC LIMIT 1

Database: talkingdata
Question: For the device with an event occurring on 2016/5/1 at 0:55:25, what is the gender of its user?
Evidence: on 2016/5/1 at 0:55:25 refers to timestamp = '2016-05-01 00:55:25';
SQL: SELECT T1.gender FROM gender_age AS T1 INNER JOIN events AS T2 ON T1.device_id = T2.device_id WHERE T2.timestamp = '2016-05-01 00:55:25'

Database: regional_sales
Question: Calculate the average net profit of bar tools which has ordered quantity exceed 5.
Evidence: net profit can be computed as SUBTRACT(Unit Price, Unit Cost); AVG(net profit) where Product Name = 'Bar Tools' and Order Quantity > 5;
SQL: SELECT SUM(REPLACE(T1.`Unit Price`, ',', '') - REPLACE(T1.`Unit Cost`, ',', '')) / COUNT(T1.OrderNumber) FROM `Sales Orders` AS T1 INNER JOIN Products AS T2 ON T2.ProductID = T1._ProductID WHERE T2.`Product Name` = 'Bar Tools' AND T1.`Order Quantity` > 5

Database: codebase_comments
Question: What is the name of the solution path with the highest processed time?
Evidence: highest processed time refers to max(ProcessedTime);
SQL: SELECT Path FROM Solution WHERE ProcessedTime = ( SELECT MAX(ProcessedTime) FROM Solution )

Database: shipping
Question: Provide the weight of the shipment to U-haul Center Of N Syracuse on 2016/9/21.
Evidence: "U-haul Center Of N Syracuse" is the cust_name; on 2016/9/21 refers to ship_date = '2016/09/21'
SQL: SELECT T1.weight FROM shipment AS T1 INNER JOIN customer AS T2 ON T1.cust_id = T2.cust_id WHERE T2.cust_name = 'U-haul Center Of N Syracuse' AND T1.ship_date = '2016-09-21'

Database: public_review_platform
Question: Indicate the business id and days which are opened from 8AM to 6PM.
Evidence: opened from 8AM to 6PM refers to Business_Hours where opening_time = '8AM' and closing_time = '6PM'; days refer to day_id;
SQL: SELECT DISTINCT day_id FROM Business_Hours WHERE opening_time = '8AM' AND closing_time = '6PM'

Database: movie_3
Question: Please provide the full name of the customer at 692 Joliet Street.
Evidence: full name refers to first_name, last_name; 692 Joliet Street refers to address = '692 Joliet Street'
SQL: SELECT T2.first_name, T2.last_name FROM address AS T1 INNER JOIN customer AS T2 ON T1.address_id = T2.address_id WHERE T1.address = '692 Joliet Street'

Database: address
Question: Which CBSAs have more than 10 zip codes?
Evidence: has more than 10 zip codes refers to CBSA where count(CBSA) > 10
SQL: SELECT T1.CBSA_name FROM CBSA AS T1 INNER JOIN zip_data AS T2 ON T1.CBSA = T2.CBSA GROUP BY T1.CBSA HAVING COUNT(T2.zip_code) > 10

Database: car_retails
Question: Among the motorcycles with product scale of 1:10, which of them is the most ordered by American customers?
Evidence: motorcycle is a product line; American is a nationality of country = 'USA';
SQL: SELECT T1.productName FROM products AS T1 INNER JOIN orderdetails AS T2 ON T1.productCode = T2.productCode INNER JOIN orders AS T3 ON T2.orderNumber = T3.orderNumber INNER JOIN customers AS T4 ON T3.customerNumber = T4.customerNumber WHERE T1.productLine = 'Motorcycles' AND T1.productScale = '1:10' AND T4.country = 'USA' GROUP BY T1.productName ORDER BY SUM(T2.quantityOrdered) DESC LIMIT 1

Database: ice_hockey_draft
Question: Which team has the most Swedish?
Evidence: Swedish refers to nation = 'Sweden'; team with the most Swedish refers to MAX(TEAM WHERE nation = 'Sweden');
SQL: SELECT T.TEAM FROM ( SELECT T2.TEAM, COUNT(DISTINCT T1.ELITEID) FROM PlayerInfo AS T1 INNER JOIN SeasonStatus AS T2 ON T1.ELITEID = T2.ELITEID WHERE T1.nation = 'Sweden' GROUP BY T2.TEAM ORDER BY COUNT(DISTINCT T1.ELITEID) DESC LIMIT 1 ) AS T

Database: movielens
Question: How many American movies have cast number more than 1?
Evidence: USA is a country
SQL: SELECT COUNT(T2.actorid) FROM movies AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid WHERE T1.country = 'USA' AND T2.cast_num > 1

Database: cars
Question: Among the car models introduced in the market in 1970, how many of them have the USA as their origin country?
Evidence: introduced in the market in 1970 refers to model_year = 1970; have the USA as origin country refers to country = 'USA'
SQL: SELECT COUNT(*) FROM production AS T1 INNER JOIN country AS T2 ON T1.country = T2.origin WHERE T1.model_year = 1970 AND T2.country = 'USA'

Database: movie_3
Question: State the address location of store No.1.
Evidence: store no. 1 refers to store_id = 1; address location refers to address, address2, district
SQL: SELECT T1.address, T1.address2, T1.district FROM address AS T1 INNER JOIN store AS T2 ON T1.address_id = T2.address_id WHERE T2.store_id = 1

Database: codebase_comments
Question: What is the average time needed for the solutions containing files within the repository whose url is "https://github.com/jeffdik/tachy.git" to be processd?
Evidence: average time = avg(ProcessedTime)；
SQL: SELECT CAST(SUM(T2.ProcessedTime) AS REAL) / COUNT(T2.RepoId) FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId WHERE T1.Url = 'https://github.com/jeffdik/tachy.git'

Database: student_loan
Question: Which organization has the least number of male students?
Evidence: organization refers to organ; male students are mentioned in male.name; least number of male students refers to MIN(male.name);
SQL: SELECT T.organ FROM ( SELECT T2.organ, COUNT(T1.name) AS num FROM male AS T1 INNER JOIN enlist AS T2 ON T1.name = T2.name GROUP BY T2.organ ) T ORDER BY T.num LIMIT 1