Database: legislator
Question: List all the representatives in 1789 along with the districts and state.
Evidence: representatives refers to type = 'rep'; in 1789 refers to start like '1789%';
SQL: SELECT T2.district, T2.state FROM historical AS T1 INNER JOIN `historical-terms` AS T2 ON T1.bioguide_id = T2.bioguide WHERE T2.start LIKE '1789%'

Database: european_football_1
Question: How many matches were played in the Scottish Premiership division from 2006 to 2008?
Evidence: Scottish Premiership is a name of division; from 2006 to 2008 means seasons between 2006 and 2008;
SQL: SELECT COUNT(T1.Div) FROM matchs AS T1 INNER JOIN divisions AS T2 ON T1.Div = T2.division WHERE T2.name = 'Scottish Premiership' AND (T1.season BETWEEN 2006 AND 2008)

Database: public_review_platform
Question: What is the ratio between business in shopping category and business in pets category?
Evidence: ratio = divide(count(business_id where category_name = 'Shopping'), count(business_id where category_name = 'Pets'))
SQL: SELECT CAST(SUM(CASE WHEN T2.category_name = 'Shopping' THEN 1 ELSE 0 END) AS REAL) / SUM(CASE WHEN T2.category_name = 'Pets' THEN 1 ELSE 0 END) AS radio FROM Business_Categories AS T1 INNER JOIN Categories AS T2 ON T1.category_id = T2.category_id

Database: retail_complains
Question: Please list all first and last names of clients who live in New York city.
Evidence: New York City refers to city = 'New York City'
SQL: SELECT first, last FROM client WHERE city = 'New York City'

Database: car_retails
Question: How many 2001 Ferrari Enzo were ordered?
Evidence: 2001 Ferrari Enzo refers to productName;
SQL: SELECT SUM(t1.orderNumber) FROM orderdetails AS t1 INNER JOIN products AS t2 ON t1.productCode = t2.productCode WHERE t2.productName = '2001 Ferrari Enzo'

Database: books
Question: List the author's name of the books published by Abrams.
Evidence: "Abrams" is the publisher_name; author's name 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 INNER JOIN publisher AS T4 ON T4.publisher_id = T1.publisher_id WHERE T4.publisher_name = 'Abrams'

Database: retail_complains
Question: Please list all clients' phone numbers and complaint IDs which are still in progress.
Evidence: in progress refers to Company response to consumer = 'In progress'
SQL: SELECT T1.phone, T2.`Complaint ID` FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T2.`Company response to consumer` = 'In progress'

Database: cookbook
Question: Which recipe in the database contains the most total fat? Give its title.
Evidence: the most total fat refers to MAX(total_fat)
SQL: SELECT T1.title FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id ORDER BY T2.total_fat DESC LIMIT 1

Database: menu
Question: Which dish lasted longer, Anchovies or Fresh lobsters in every style?
Evidence: if (SUBTRACT(last_appeared, first_appeared) WHERE name = 'Anchovies') > (SUBTRACT(last_appeared, first_appeared) WHERE name = 'Fresh lobsters in every style'), it means 'Anchovies' lasted longer; if (SUBTRACT(last_appeared , first_appeared) WHERE name = 'Fresh lobsters in every style') > (SUBTRACT(last_appeared , first_appeared) WHERE name = 'Anchovies') it means 'Fresh lobsters in every style' last longer;
SQL: SELECT CASE WHEN SUM(CASE WHEN name = 'Anchovies' THEN last_appeared - first_appeared ELSE 0 END) - SUM(CASE WHEN name = 'Fresh lobsters in every style' THEN last_appeared - first_appeared ELSE 0 END) > 0 THEN 'Anchovies' ELSE 'Fresh lobsters in every style' END FROM Dish WHERE name IN ('Fresh lobsters in every style', 'Anchovies')

Database: car_retails
Question: List down the customer names with a disputed order status.
SQL: SELECT t1.customerName FROM customers AS t1 INNER JOIN orders AS t2 ON t1.customerNumber = t2.customerNumber WHERE t2.status = 'Disputed'

Database: food_inspection_2
Question: How many restaurants failed the inspection in April 2010?
Evidence: restaurant refers to facility_type = 'Restaurant'; failed the inspection refers to results = 'Fail'; in April 2010 refers to inspection_date like '2010-04%'
SQL: SELECT COUNT(DISTINCT T1.license_no) FROM establishment AS T1 INNER JOIN inspection AS T2 ON T1.license_no = T2.license_no WHERE strftime('%Y-%m', T2.inspection_date) = '2010-04' AND T1.facility_type = 'Restaurant' AND T2.results = 'Fail'

Database: cookbook
Question: List the names of alcohol free recipes.
Evidence: alcohol free refers to alcohol = 0
SQL: SELECT T1.title FROM Recipe AS T1 INNER JOIN Nutrition AS T2 ON T1.recipe_id = T2.recipe_id WHERE T2.alcohol = 0

Database: books
Question: Give the author's name of the books that cost 19 dollars and above.
Evidence: books cost 19 dollars and above refers to price > = 19
SQL: SELECT DISTINCT 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 INNER JOIN order_line AS T4 ON T4.book_id = T1.book_id WHERE T4.price > 19

Database: movies_4
Question: List the names of all the producers in the movie "Pirates of the Caribbean: At World's End".
Evidence: List the names refers to person_name; producers refers to job = 'Producer'; "Pirates of the Caribbean: At World's End" refers to title = 'Pirates of the Caribbean: The Curse of the Black Pearl'
SQL: SELECT T3.person_name FROM movie AS T1 INNER JOIN movie_crew AS T2 ON T1.movie_id = T2.movie_id INNER JOIN person AS T3 ON T2.person_id = T3.person_id WHERE T1.title = 'Pirates of the Caribbean: The Curse of the Black Pearl' AND T2.job = 'Producer'

Database: menu
Question: How many dishes appeared on a menu more than once?
Evidence: appeared on a menu more than once refers to times_appeared > menus_appeared;
SQL: SELECT COUNT(*) FROM Dish WHERE times_appeared > menus_appeared

Database: ice_hockey_draft
Question: Among all the players that are right-shooted, how many of them weigh over 90 kg?
Evidence: right-shooted refers to shoots = 'R'; weigh over 90 kg refers to weight_in_kg > 90;
SQL: SELECT COUNT(T1.ELITEID) FROM PlayerInfo AS T1 INNER JOIN weight_info AS T2 ON T1.weight = T2.weight_id WHERE T2.weight_in_kg > 90 AND T1.shoots = 'R'

Database: regional_sales
Question: List down the product IDs and names that include the word "Outdoor".
Evidence: names that include the word "Outdoor" refer to Product Name LIKE '%Outdoor%';
SQL: SELECT ProductID, T FROM ( SELECT ProductID , CASE  WHEN `Product Name` LIKE '%Outdoor%' THEN `Product Name` ELSE NULL END AS T FROM Products ) WHERE T IS NOT NULL ORDER BY T DESC

Database: synthea
Question: How many of the patients born in 1920s had pneumonia?
Evidence: patients born in 1920s refer to patient where birthdate like '192%'; pneumonia refers to DESCRIPTION = 'Pneumonia' from conditions;
SQL: SELECT COUNT(DISTINCT T1.patient) FROM patients AS T1 INNER JOIN conditions AS T2 ON T1.patient = T2.PATIENT WHERE DESCRIPTION = 'Pneumonia' AND strftime('%Y', T1.birthdate) LIKE '192%'

Database: retail_world
Question: Which country are the majority of the suppliers located?
Evidence: majority of the suppliers located refers to MAX(COUNT(SupplierID))
SQL: SELECT Country FROM Suppliers GROUP BY Country ORDER BY COUNT(SupplierID) DESC LIMIT 1

Database: retails
Question: Give the phone number of the customer with the highest account balance.
Evidence: phone number of the customer refers to c_phone; the highest account balance refers to MAX(c_acctbal);
SQL: SELECT c_phone FROM customer ORDER BY c_acctbal DESC LIMIT 1