Database: book_publishing_company
Question: List all titles with sales of quantity more than 20 and store located in the CA state.
Evidence: qty is abbreviation for quantity; sales of quantity more than 20 refers to qty>20; store refers to stor_name
SQL: SELECT T1.title, T2.qty FROM titles AS T1 INNER JOIN sales AS T2 ON T1.title_id = T2.title_id INNER JOIN stores AS T3 ON T2.stor_id = T3.stor_id WHERE T2.qty > 20 AND T3.state = 'CA'

Database: olympics
Question: How many athletes competed in the 1992 Summer Olympics?
Evidence: 1992 Summer Olympics refer to games_name = '1928 Summer';
SQL: SELECT COUNT(T2.person_id) FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id WHERE T1.games_name = '1928 Summer'

Database: cars
Question: How many Japanese cars weigh more than 2000 lbs?
Evidence: Japanese car refers to country = 'Japan'; weigh more than 2000 lbs refers to weight > 2000
SQL: SELECT COUNT(*) FROM data AS T1 INNER JOIN production AS T2 ON T1.ID = T2.ID INNER JOIN country AS T3 ON T3.origin = T2.country WHERE T3.country = 'Japan' AND T1.weight > 2000

Database: retail_world
Question: Give the number of orders that were shipped by "Federal Shipping".
Evidence: Federal Shipping refers to CompanyName = 'Federal Shipping';
SQL: SELECT COUNT(T1.OrderID) FROM Orders AS T1 INNER JOIN Shippers AS T2 ON T1.ShipVia = T2.ShipperID WHERE T2.CompanyName = 'Federal Shipping'

Database: language_corpus
Question: What is the word ID for the second word for the biwords pair with most repetitions?
Evidence: second word refers to w2nd; biwords pair with most repetitions refers to MAX(biwords.occurrences)
SQL: SELECT w2nd FROM biwords WHERE occurrences = ( SELECT MAX(occurrences) FROM biwords )

Database: public_review_platform
Question: Give the percentage of "Automotive" businesses among all the Yelp businesses.
Evidence: automotive businesses refers to category_name = 'Automotive'; percentage = MULTIPLY(DIVIDE(SUM(category_name = 'Automotive'), COUNT(business_id)), 1.0);
SQL: SELECT CAST(SUM(CASE WHEN T2.category_name LIKE 'Automotive' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.business_id) AS "percentage" FROM Business_Categories AS T1 INNER JOIN Categories AS T2 ON T1.category_id = T2.category_id

Database: legislator
Question: Calculate the percentage of legislators who are Senator and were born in 1964.
Evidence: are senator refers to class IS NOT NULL; born in 1964 refers to birthday_bio = 1964; calculation = MULTIPLY(DIVIDE(COUNT(class IS NOT NULL THEN bioguide_id), COUNT(bioguide_id)), 1.0)
SQL: SELECT CAST(SUM(CASE WHEN T2.class IS NOT NULL THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) FROM current AS T1 INNER JOIN `current-terms` AS T2 ON T1.bioguide_id = T2.bioguide WHERE T1.birthday_bio LIKE '%1964%'

Database: books
Question: Which customer has made the most orders? Show his/her full name.
Evidence: most order refers to Max(Count(order_id)); customer refers to first_name, last_name
SQL: SELECT T1.first_name, T1.last_name FROM customer AS T1 INNER JOIN cust_order AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.first_name, T1.last_name ORDER BY COUNT(*) DESC LIMIT 1

Database: car_retails
Question: Find the customer who made the highest payment in 2005.
Evidence: The highest payment refers to max(amount); 2005 refers to year(paymentDate);
SQL: SELECT t2.customerName FROM payments AS t1 INNER JOIN customers AS t2 ON t1.customerNumber = t2.customerNumber WHERE STRFTIME('%Y', t1.paymentDate) = '2005' GROUP BY t2.customerNumber, t2.customerName ORDER BY SUM(t1.amount) DESC LIMIT 1

Database: trains
Question: Please list the IDs of all the trains that run in the east direction and have less than 4 cars.
Evidence: less than 4 cars refers to carsNum < 4
SQL: SELECT T1.id FROM trains AS T1 INNER JOIN ( SELECT train_id, MAX(position) AS carsNum FROM cars GROUP BY train_id ) AS T2 ON T1.id = T2.train_id WHERE T1.direction = 'east' AND T2.carsNum < 4

Database: shipping
Question: Show the population of the city which was the destination of shipment no.1398.
Evidence: shipment no. 1398 refers to ship_id = 1398
SQL: SELECT T2.population FROM shipment AS T1 INNER JOIN city AS T2 ON T1.city_id = T2.city_id WHERE T1.ship_id = '1398'

Database: regional_sales
Question: Which sales channel was most preferred in commercializing products in January 2020 based on the number of orders placed?
Evidence: order refers to OrderDate; in 2020 refers to Substr(OrderDate, -2) = '20'; January refers to Substr(OrderDate, 1, 1) = '1';  most preferred sales channel refers to Sales Channel where Max(Count(OrderNumber))
SQL: SELECT `Sales Channel` FROM `Sales Orders` WHERE OrderDate LIKE '1/%/20' GROUP BY `Sales Channel` ORDER BY COUNT(`Sales Channel`) DESC LIMIT 1

Database: retails
Question: What is the delivery time and the clerk of order number 6?
Evidence: delivery time = subtract(l_receiptdate, l_commitdate); clerk refers to o_clerk; order number 6 refers to o_orderkey = 6
SQL: SELECT JULIANDAY(T2.l_receiptdate) - JULIANDAY(T2.l_commitdate), T1.o_clerk FROM orders AS T1 INNER JOIN lineitem AS T2 ON T1.o_orderkey = T2.l_orderkey WHERE T1.o_orderkey = 6

Database: sales
Question: Name the sales person who helped Elizabeth A. White to purchase Road-250 Black, 48.
Evidence: name of the sales person = FirstName, MiddleInitial, LastName; 'Road-250 Black, 48' is name of product;
SQL: SELECT DISTINCT T3.FirstName, T3.MiddleInitial, T3.LastName FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID INNER JOIN Employees AS T3 ON T2.SalesPersonID = T3.EmployeeID INNER JOIN Customers AS T4 ON T2.CustomerID = T4.CustomerID WHERE T4.MiddleInitial = 'A' AND T4.LastName = 'White' AND T1.Name = 'Road-250 Black, 48' AND T4.FirstName = 'Elizabeth'

Database: social_media
Question: How many tweets in total were posted by a user in Argentina?
Evidence: "Argentina" is the Country
SQL: SELECT COUNT(T1.TweetID) FROM twitter AS T1 INNER JOIN location AS T2 ON T2.LocationID = T1.LocationID WHERE T2.Country = 'Argentina' LIMIT 1

Database: soccer_2016
Question: Compute the run rate at the end of 16 overs of the match ID 335999. Please include the name of the "Man of_the Match".
Evidence: end of 16 overs refers to count(Toss_Name = 'field' ) = 16; run rate = divide(count(Runs_Scored) when Toss_Name = 'bat', sum(Over_Id)when Toss_Name = 'field'); name refers to Player_Name
SQL: SELECT CAST(COUNT(CASE WHEN T1.Toss_Name = 'bat' THEN T3.Runs_Scored ELSE NULL END) AS REAL) / SUM(CASE WHEN T1.Toss_Name = 'field' THEN 1 ELSE 0 END) FROM Toss_Decision AS T1 INNER JOIN Match AS T2 ON T1.Toss_Id = T2.Toss_Decide INNER JOIN Batsman_Scored AS T3 ON T2.Match_Id = T3.Match_Id WHERE T2.Match_Id = 335987 AND T2.Match_Date = '2008-04-18' GROUP BY T3.Over_Id HAVING COUNT(T1.Toss_Name = 'field') = 16

Database: retail_world
Question: How many suppliers in Australia whose products were discontinued?
Evidence: in Australia refers to Country = 'Australia'; discontinued refers to Discontinued = 1
SQL: SELECT COUNT(T1.Discontinued) FROM Products AS T1 INNER JOIN Suppliers AS T2 ON T1.SupplierID = T2.SupplierID WHERE T1.Discontinued = 1 AND T2.Country = 'Australia'

Database: computer_student
Question: Is the teacher who teaches course no.9 a faculty member?
Evidence: teacher refers to taughtBy.p_id; course no.9 refers to taughtBy.course_id = 9; faculty member refers to hasPosition ! = 0
SQL: SELECT T2.hasPosition FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T1.course_id = 9

Database: retail_world
Question: In August of 1996, how many orders were placed by the customer with the highest amount of orders?
Evidence: August of 1996 refers to OrderDate = '1996-8'; highest amount of orders refers to MAX(COUNT(OrderID))
SQL: SELECT COUNT(OrderID) FROM Orders WHERE OrderDate LIKE '1996-08%' GROUP BY CustomerID ORDER BY COUNT(OrderID) DESC LIMIT 1