Database: simpson_episodes
Question: Among the episodes with an award nominee or winner, which has the highest percent of 5 star votes?
Evidence: highest percent of 5 star votes refers to max(percent) where star = 5
SQL: SELECT T1.episode_id FROM Award AS T1 INNER JOIN Vote AS T2 ON T2.episode_id = T1.episode_id WHERE T2.stars = 5 ORDER BY T2.percent DESC LIMIT 1;

Database: chicago_crime
Question: How many incidents are considered "severe" in the IUCR classification?
Evidence: severe refers to index_code = 'I'; incidents refers to iucr_no
SQL: SELECT COUNT(*) FROM IUCR WHERE index_code = 'I'

Database: food_inspection_2
Question: Among the facilities that had undergone at least one inspection in 2010, how many of them have the most serious food safety issues?
Evidence: in 2010 refers to inspection_date like '2010%'; the most serious food safety issues refers to risk_level = 3
SQL: SELECT COUNT(DISTINCT T2.license_no) FROM inspection AS T1 INNER JOIN establishment AS T2 ON T1.license_no = T2.license_no WHERE strftime('%Y', T1.inspection_date) = '2010' AND T2.risk_level = 3

Database: books
Question: List the titles of all the books that Peter H. Smith wrote.
Evidence: "Peter H.Smit" is the author_name
SQL: SELECT T1.title 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 T3.author_name = 'Peter H. Smith'

Database: retails
Question: Provide the order comments for at least 5 orders made by customers in the furniture segment.
Evidence: order comments refer to o_comment; c_mktsegment = 'Furniture';
SQL: SELECT T1.o_comment FROM orders AS T1 INNER JOIN customer AS T2 ON T1.o_custkey = T2.c_custkey WHERE T2.c_mktsegment = 'Furniture' LIMIT 5

Database: shakespeare
Question: Provide the character name, paragraph number, and plain text of "cousin to the king" description.
Evidence: character name refers to CharName; paragraph number refers to ParagraphNum
SQL: SELECT T1.CharName, T2.ParagraphNum, T2.PlainText FROM characters AS T1 INNER JOIN paragraphs AS T2 ON T1.id = T2.character_id WHERE T1.Description = 'cousin to the king'

Database: bike_share_1
Question: Calculate the average usage of each bike in the third quarter of year 2013. Find the average wind direction within the same period.
Evidence: the third quarter of year 2013 implies 3 month interval, including July, August, and September of 2013 and refers to date; the average usage of each bike = DIVIDE(SUM(duration), COUNT(bike_id)); the average wind direction = DIVIDE(SUM(wind_dir_degrees), COUNT(date));
SQL: SELECT AVG(T1.duration), AVG(T2.wind_dir_degrees) FROM trip AS T1 INNER JOIN weather AS T2 ON T2.zip_code = T1.zip_code WHERE SUBSTR(CAST(T2.date AS TEXT), 1, INSTR(T2.date, '/') - 1) IN ('7', '8', '9') AND SUBSTR(CAST(T2.date AS TEXT), -4) = '2013'

Database: cs_semester
Question: How many courses does the student with the highest GPA this semester take?
Evidence: student with the highest GPA refers to student_id where MAX(gpa);
SQL: SELECT COUNT(course_id) FROM registration WHERE student_id IN ( SELECT student_id FROM student WHERE gpa = ( SELECT MAX(gpa) FROM student ) )

Database: regional_sales
Question: In 2020, what were the total orders of all stores in Maricopa County?
Evidence: total orders refer to COUNT(OrderNumber); 2020 refers to SUBSTR(OrderDate, -2) = '20';
SQL: SELECT SUM(CASE WHEN T2.County = 'Maricopa County' AND OrderDate LIKE '%/%/20' THEN 1 ELSE 0 END) FROM `Sales Orders` AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StoreID = T1._StoreID

Database: beer_factory
Question: List down the brand names of root beer that gained a 5-star rating from a customer's review in 2013. Calculate the unit profit available to wholesalers for each brand.
Evidence: 5-star rating refers to StarRating = 5; in 2013 refers to ReviewDate LIKE '2013%'; unit profit available to wholesalers = SUBTRACT(CurrentRetailPrice, WholesaleCost);
SQL: SELECT T1.BrandName, T1.CurrentRetailPrice - T1.WholesaleCost AS result FROM rootbeerbrand AS T1 INNER JOIN rootbeerreview AS T2 ON T1.BrandID = T2.BrandID WHERE T2.StarRating = 5 AND T2.ReviewDate BETWEEN '2013-01-01' AND '2013-12-31'

Database: books
Question: Among the books ordered by Lucas Wyldbore, what is the percentage of those books over $13?
Evidence: books over $13 refers to price > 13; percentage = Divide (Sum (order_id where price > 13), Count (order_id)) * 100
SQL: SELECT CAST(SUM(CASE WHEN T1.price > 13 THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) FROM order_line AS T1 INNER JOIN cust_order AS T2 ON T2.order_id = T1.order_id INNER JOIN customer AS T3 ON T3.customer_id = T2.customer_id WHERE T3.first_name = 'Lucas' AND T3.last_name = 'Wyldbore'

Database: chicago_crime
Question: Give the coordinate of the alleys where a crime was reported and an arrest was made.
Evidence: coordinate refers to latitude, longitude; alley refers to location_description = 'ALLEY'; an arrest was made refers to arrest = 'TRUE'
SQL: SELECT latitude, longitude FROM Crime WHERE location_description = 'ALLEY' AND arrest = 'TRUE' GROUP BY latitude, longitude

Database: social_media
Question: Among the tweets posted from Santa Fe state in Argentina, how many of them were posted on 31st?
Evidence: "Sante Fe" is the State; "Argentina" is the Country; posted on 31st refers to Day = 31
SQL: SELECT COUNT(T1.TweetID) FROM twitter AS T1 INNER JOIN location AS T2 ON T1.LocationID = T2.LocationID WHERE T1.Day = 31 AND T2.State = 'Santa' AND T2.Country = 'Argentina'

Database: retail_world
Question: How many suppliers does Northwind have in USA?
Evidence: 'USA' is a country; supplier refers to CompanyName
SQL: SELECT COUNT(SupplierID) FROM Suppliers WHERE Country = 'USA'

Database: retail_world
Question: List the order IDs, product IDs and unit price of orders which total payment is greater than 15000.
Evidence: total payment is greater than 15000 refers to MULTIPLY((Quantity * UnitPrice * (1 - Discount))) > 15000

SQL: SELECT ProductID, OrderID, UnitPrice FROM `Order Details` WHERE UnitPrice * Quantity * (1 - Discount) > 15000

Database: legislator
Question: Among the Independent senators that started their term in 2011 and onwards, what are the official full names of the senators that caucused with the Democrat?
Evidence: Independent refers to party = 'Independent'; senators refers to type = 'sen'; started the term in 2011 and onwards refers to strftime('%Y', start) > = '2011'; caucused with the Democrat refers to caucus = 'Democrat';
SQL: SELECT T1.official_full_name FROM current AS T1 INNER JOIN `current-terms` AS T2 ON T1.bioguide_id = T2.bioguide WHERE T2.party = 'Independent' AND strftime('%Y', T2.start) >= '2011' AND T2.type = 'sen' AND T2.caucus = 'Democrat'

Database: authors
Question: Who authored the paper titled "Testing timed automata
"?
Evidence: 'Testing timed automata' is a title of a paper; Who authored refers to PaperAuthor.Name
SQL: SELECT T2.Name FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Title = 'Testing timed automata'

Database: beer_factory
Question: What is the difference in the average number of sales per day of root beer brands that contain honey and that don’t contain honey.
Evidence: difference in the average = SUBTRACT(DIVIDE(MULTIPLY(SUM(Honey = 'TRUE'), 1.0), COUNT(TransactionDate)), DIVIDE(MULTIPLY(SUM(Honey = 'FALSE'), 1.0), COUNT(TransactionDate))); contain honey refers to Honey = 'TRUE'; don’t contain honey refers to Honey = 'FALSE'
SQL: SELECT (CAST(SUM(CASE WHEN T1.Honey = 'TRUE' THEN 1 ELSE 0 END) AS REAL) / COUNT(DISTINCT T3.TransactionDate)) - (CAST(SUM(CASE WHEN T1.Honey <> 'TRUE' THEN 1 ELSE 0 END) AS REAL) / COUNT(DISTINCT T3.TransactionDate)) FROM rootbeerbrand AS T1 INNER JOIN rootbeer AS T2 ON T1.BrandID = T2.BrandID INNER JOIN `transaction` AS T3 ON T2.RootBeerID = T3.RootBeerID