Database: shipping
Question: Identify the total weight of shipments transported to San Mateo, California, in 2016.
Evidence: "San Mateo" is the city_name; in 2016 refers to Cast(ship_date as DATE) = 2016
SQL: SELECT SUM(T1.weight) FROM shipment AS T1 INNER JOIN city AS T2 ON T1.city_id = T2.city_id WHERE T2.city_name = 'San Mateo' AND STRFTIME('%Y', T1.ship_date) = '2016'

Database: legislator
Question: Which legislator has the YouTube channel 'RoskamIL06?' Write the official full name.
Evidence: YouTube channel 'RoskamIL06' refers to youtube = 'RoskamIL06';
SQL: SELECT T1.official_full_name FROM current AS T1 INNER JOIN `social-media` AS T2 ON T2.bioguide = T1.bioguide_id WHERE T2.youtube = 'RoskamIL06'

Database: food_inspection
Question: List the inspection dates, scores and inspection types for the eateries with tax code AA.
Evidence: eateries with tax code AA refer to business_id where tax_code = 'AA';
SQL: SELECT T1.`date`, T1.score, T1.type FROM inspections AS T1 INNER JOIN businesses AS T2 ON T1.business_id = T2.business_id WHERE T2.tax_code = 'AA'

Database: synthea
Question: What is the prevalence rate of the patients' diseases started on 9th May 2014?
Evidence: diseases started on 9th May 2014 refer to DESCRIPTION from conditions where START = '5/9/2014';
SQL: SELECT T2."PREVALENCE RATE" FROM conditions AS T1 INNER JOIN all_prevalences AS T2 ON lower(T1.DESCRIPTION) = lower(T2.ITEM) WHERE T1.START = '2014-05-09'

Database: professional_basketball
Question: List all the coaches with more game lost than won from year 2000-2010. List the coach ID, team name and year.
Evidence: from year 2000 to 2010 refers to year between 2000 and 2010; more game lost then won refers to lost > won
SQL: SELECT DISTINCT T1.coachID, T2.tmID, T1.year FROM coaches AS T1 INNER JOIN teams AS T2 ON T1.tmID = T2.tmID AND T1.year = T2.year WHERE T1.year BETWEEN 2000 AND 2010 AND T2.lost > T2.won

Database: world_development_indicators
Question: How many countries are using the same type of currency? Please list the short names of any 3 countries.
Evidence: any 3 countries refers to count(shortname)>3
SQL: SELECT ShortName FROM country WHERE currencyunit = 'U.S. dollar' LIMIT 3

Database: synthea
Question: Provide the social security number of the patient with the highest systolic blood pressure.
Evidence: social security number refers to ssn; highest systolic blood pressure refers to MAX(observations.VALUE WHERE observations.DESCRIPTION = 'Systolic Blood Pressure');
SQL: SELECT T2.ssn FROM observations AS T1 INNER JOIN patients AS T2 ON T1.PATIENT = T2.patient WHERE T1.DESCRIPTION = 'Systolic Blood Pressure' ORDER BY T1.VALUE DESC LIMIT 1

Database: menu
Question: Calculate the image area of the page menu for the dish named "Baked Stuffed Mullet & Sauce Pomard". Please include the page number and image ID.
Evidence: image area = MULTIPLY(full_height, full_width);
SQL: SELECT T1.full_height * T1.full_width, T1.page_number, T1.image_id FROM MenuPage AS T1 INNER JOIN MenuItem AS T2 ON T1.id = T2.menu_page_id INNER JOIN Dish AS T3 ON T2.dish_id = T3.id WHERE T3.name = 'Baked Stuffed Mullet & Sauce Pomard'

Database: books
Question: Who is the author of First Things First?
Evidence: "First Things First" is the title of the book; author refers to author_name
SQL: SELECT T3.author_name FROM book AS T1 INNER JOIN book_author AS T2 ON T1.book_id = T2.book_id INNER JOIN author AS T3 ON T3.author_id = T2.author_id WHERE T1.title = 'First Things First'

Database: public_review_platform
Question: List all the users with average star less than 3 stars in 2012
Evidence: average star less than 3 stars refers to user_average_stars < 3; in 2012 refers to user_yelping_since_year = 2012
SQL: SELECT user_id FROM Users WHERE user_yelping_since_year = 2012 AND user_average_stars < 3

Database: professional_basketball
Question: Among the coaches who won the 'ABA Coach of the Year' award, which is the coach with the highest number of won games?
Evidence: "ABA Coach of the Year" is the award; highest number of won games refers to Max(Count(won))
SQL: SELECT T1.coachID FROM coaches AS T1 INNER JOIN awards_coaches AS T2 ON T1.coachID = T2.coachID WHERE T2.award = 'ABA Coach of the Year' GROUP BY T1.coachID, T1.won ORDER BY T1.won DESC LIMIT 1

Database: world_development_indicators
Question: Among the countries in the group of Heavily Indebted Poor Countries, how many of them are under the lending category of the International Development Associations?
Evidence: group of Heavily Indebted Poor Countries is OtherGroups = 'HIPC'; International Development Associations refers to lendingcategory = 'IDA'
SQL: SELECT COUNT(CountryCode) FROM Country WHERE LendingCategory = 'IDA' AND OtherGroups = 'HIPC'

Database: address
Question: Provide the alias and elevation of the city with zip code 1028.
SQL: SELECT T1.alias, T2.elevation FROM alias AS T1 INNER JOIN zip_data AS T2 ON T1.zip_code = T2.zip_code WHERE T1.zip_code = 1028

Database: image_and_language
Question: List all the attribute classes of image ID 22.
Evidence: attribute classes of image ID 22 refer to ATT_CLASS where MG_ID = 22;
SQL: SELECT T1.ATT_CLASS FROM ATT_CLASSES AS T1 INNER JOIN IMG_OBJ_ATT AS T2 ON T1.ATT_CLASS_ID = T2.ATT_CLASS_ID WHERE T2.IMG_ID = 22

Database: car_retails
Question: State the email of those who are staff of Murphy Diane whose number is 1002 and living in San Francisco
Evidence: staff of refers to reportsTO; San Francisco is a city;
SQL: SELECT T1.email FROM employees AS T1 INNER JOIN offices AS T2 ON T1.officeCode = T2.officeCode WHERE T1.reportsTo = 1002 AND T2.city = 'San Francisco'

Database: software_company
Question: List the geographic id of places where the income is above average.
Evidence: geographic ID refers to GEOID; income is above average refers to INCOME_K > DIVIDE(SUM(INCOME_K), COUNT(GEOID));
SQL: SELECT AVG(INCOME_K) FROM Demog

Database: book_publishing_company
Question: In 1994 which title had less order quanty than the average order quantity? Find the title name, type and price.
Evidence: orders in 1994 refers to YEAR(ord_date) = 1994; order quantity refers to number of order expressed by ord_num; average order quantity = DIVIDE(SUM(ord_num), COUNT(title_id))
SQL: SELECT DISTINCT T1.title, T1.type, T1.price FROM titles AS T1 INNER JOIN sales AS T2 ON T1.title_id = T2.title_id WHERE T2.ord_date LIKE '1994%' AND T2.Qty < ( SELECT CAST(SUM(T4.qty) AS REAL) / COUNT(T3.title_id) FROM titles AS T3 INNER JOIN sales AS T4 ON T3.title_id = T4.title_id )

Database: sales
Question: Calculate the percentage of sold blades in the total number of transactions.
Evidence: percentage = MULTIPLY(DIVIDE(SUM(Quantity WHERE Name = 'Blade'), SUM(Quantity)), 1.0); 'blades' refers to name of product;
SQL: SELECT CAST(SUM(IIF(T1.Name = 'Blade', T2.Quantity, 0)) AS REAL) * 100 / SUM(T2.Quantity) FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID

Database: retail_world
Question: What is the difference in number of unit price from Chef Anton's Cajun Seasoning product and Chef Anton's Gumbo Mix product of New Orleans Cajun Delights company.
Evidence: Chef Anton's Cajun Seasoning' AND 'Chef Anton''s Gumbo Mix' are ProductName; 'New Orleans Cajun Delights' is a CompanyName; calculation = SUBTRACT(UnitPrice where ProductName = 'Chef Anton's Cajun Seasoning', UnitPrice where ProductName = 'Chef Anton''s Gumbo Mix')
SQL: SELECT ( SELECT T1.UnitPrice FROM Products AS T1 INNER JOIN Suppliers AS T2 ON T1.SupplierID = T2.SupplierID WHERE T2.CompanyName = 'New Orleans Cajun Delights' AND T1.ProductName LIKE 'Chef Anton%s Cajun Seasoning' ) - ( SELECT T1.UnitPrice FROM Products AS T1 INNER JOIN Suppliers AS T2 ON T1.SupplierID = T2.SupplierID WHERE T2.CompanyName = 'New Orleans Cajun Delights' AND T1.ProductName LIKE 'Chef Anton%s Gumbo Mix' ) AS calu