Database: superstore
Question: Calculate the total cost of the orders by Brad Thomas in 2016.
Evidence: Brad Thomas is the "Customer Name"; in 2016 refers to strftime('%Y', "Order Date") = '2016'; total cost refers to SUM(SUTRACT(MULTIPLY(DIVIDE(Sales, SUTRACT(1, discount)), Quantity), Profit))
SQL: SELECT SUM((T1.Sales / (1 - T1.Discount)) * T1.Quantity - T1.Profit) AS cost FROM east_superstore AS T1 INNER JOIN people AS T2 ON T1.`Customer ID` = T2.`Customer ID` INNER JOIN product AS T3 ON T1.`Product ID` = T3.`Product ID` AND T1.Region = T3.Region WHERE T1.Region = 'East' AND T2.`Customer Name` = 'Brad Thomas' AND strftime('%Y', T1.`Order Date`) = '2016'

Database: sales
Question: Calculate the quantity percentage of the gift products in the total trading quantity.
Evidence: percentage = MULTIPLY(DIVIDE(SUM(Quantity WHERE Price = 0), SUM(Quantity)), 1.0); gift products refers to Price = 0;
SQL: SELECT CAST(SUM(IIF(T1.Price = 0, T2.Quantity, 0)) AS REAL) * 100 / SUM(T2.Quantity)FROM Products AS T1 INNER JOIN Sales AS T2 ON T1.ProductID = T2.ProductID

Database: shipping
Question: Among the shipments for Downey, how many shipments were shipped to California in 2016?
Evidence: "Downey" is the city_name; 'California' is the state, whose abbreviation is CA; in 2016 refers to year(ship_date) = 2016,
SQL: SELECT COUNT(*) FROM shipment AS T1 INNER JOIN city AS T2 ON T1.city_id = T2.city_id INNER JOIN customer AS T3 ON T3.cust_id = T1.cust_id WHERE T2.city_name = 'Downey' AND STRFTIME('%Y', T1.ship_date) = '2016' AND T3.state = 'CA'

Database: ice_hockey_draft
Question: Among the players with a height of over 6'2" inches, how many of them were born in Sweden?
Evidence: height of over 6'2" inches refers to height_in_inch > '6''2"'; born in Sweden refers to nation = 'Sweden' ;
SQL: SELECT COUNT(T1.ELITEID) FROM PlayerInfo AS T1 INNER JOIN height_info AS T2 ON T1.height = T2.height_id WHERE T2.height_in_inch > '6''2"' AND T1.nation = 'Sweden'

Database: movie_3
Question: Who is the manager of the store with the largest collection of films?
Evidence: Who refers to first_name, last_name; the largest collection of films refers to MAX(film_id)
SQL: SELECT T.first_name, T.last_name FROM ( SELECT T3.first_name, T3.last_name, COUNT(T1.film_id) AS num FROM inventory AS T1 INNER JOIN store AS T2 ON T1.store_id = T2.store_id INNER JOIN staff AS T3 ON T2.manager_staff_id = T3.staff_id GROUP BY T3.first_name, T3.last_name ) AS T ORDER BY T.num DESC LIMIT 1

Database: movie_3
Question: What is the percentage of documentary films?
Evidence: documentary' is a name of a category; calculation = DIVIDE(SUM(name = 'Documentary'), COUNT(film_id)) * 100
SQL: SELECT CAST(SUM(IIF(T2.name = 'Documentary', 1, 0)) AS REAL) * 100 / COUNT(T1.film_id) FROM film_category AS T1 INNER JOIN category AS T2 ON T1.category_id = T2.category_id

Database: car_retails
Question: How many French customers does Gerard Hernandez take care of?
Evidence: Gerakd Hermandez is an employee; French customer refers to customer from France where country = 'France'
SQL: SELECT COUNT(t1.customerNumber) FROM customers AS t1 INNER JOIN employees AS t2 ON t1.salesRepEmployeeNumber = t2.employeeNumber WHERE t1.country = 'France' AND t2.firstName = 'Gerard' AND t2.lastName = 'Hernandez'

Database: simpson_episodes
Question: List the stars of episodes aired in November 2008.
Evidence: in November 2008 refers to air_date LIKE '2008-11%'
SQL: SELECT T2.stars FROM Episode AS T1 INNER JOIN Vote AS T2 ON T2.episode_id = T1.episode_id WHERE SUBSTR(T1.air_date, 1, 7) = '2008-11';

Database: books
Question: What is the name of the publisher with publisher ID 22?
Evidence: name of publisher refers to publisher_name
SQL: SELECT publisher_name FROM publisher WHERE publisher_id = 22

Database: ice_hockey_draft
Question: What is the weight of the player with the longest time on ice in the player’s first 7 years of NHL career in kilograms?
Evidence: weight in kilograms refers to weight_in_kg; longest time on ice in the player's first 7 years of NHL career refers to MAX(sum_7yr_TOI);
SQL: SELECT T2.weight_in_kg FROM PlayerInfo AS T1 INNER JOIN weight_info AS T2 ON T1.weight = T2.weight_id WHERE T1.sum_7yr_TOI = ( SELECT MAX(t.sum_7yr_TOI) FROM PlayerInfo t )

Database: books
Question: Among the books published in 2004, list the name of the publisher of books with number of pages greater than 70% of the average number of pages of all books.
Evidence: published in 2004 refers to publication_date LIKE '2004%'; books with number of pages greater than 70% of the average number of pages refers to num_pages > Multiply(Avg(num_pages), 0.7); name of publisher refers to publisher_name
SQL: SELECT T1.title, T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id WHERE STRFTIME('%Y', T1.publication_date) = '2004' AND T1.num_pages * 100 > ( SELECT AVG(num_pages) FROM book ) * 70

Database: student_loan
Question: Among the students that have filed for bankruptcy, how many of them have been absent from school for over 5 months?
Evidence: absent from school for over 5 months refers to `month` > 5;
SQL: SELECT COUNT(T1.name) FROM filed_for_bankrupcy AS T1 INNER JOIN longest_absense_from_school AS T2 ON T1.`name` = T2.`name` WHERE T2.`month` > 5

Database: retails
Question: Which part has a bigger size, "pink powder drab lawn cyan" or "cornflower sky burlywood green beige"?
Evidence: size refers to p_size; "pink powder drab lawn cyan" or "cornflower sky burlywood green beige" refers to p_name in ('pink powder drab lawn cyan', 'cornflower sky burlywood green beige')
SQL: SELECT T.p_name FROM ( SELECT p_name, p_size FROM part WHERE p_name IN ('pink powder drab lawn cyan', 'cornflower sky burlywood green beige') ) AS T ORDER BY p_size DESC LIMIT 1

Database: regional_sales
Question: In 2019, how many orders were shipped by the sales team with the highest number of orders in the said year? Provide the name of the sales team.
Evidence: shipped refers to ShipDate; in 2019 refers to shipped in 2019 refers to SUBSTR(ShipDate, -2) = '19'; order in the said year refers to SUBSTR(OrderDate, -2) = '19'; highest number of order refers to Max(Count(OrderNumber))
SQL: SELECT COUNT(T1.OrderNumber), T2.`Sales Team` FROM `Sales Orders` AS T1 INNER JOIN `Sales Team` AS T2 ON T2.SalesTeamID = T1._SalesTeamID WHERE T1.OrderDate LIKE '%/%/19' AND T1.ShipDate LIKE '%/%/19' GROUP BY T2.`Sales Team` ORDER BY COUNT(T1.OrderNumber) DESC LIMIT 1

Database: movie_platform
Question: List all movie title rated in April 2020 from user who was a trialist.
Evidence: movie title rated in April 2020 refers to rating_timestamp_utc LIKE '%2020-04-%'; user is a trial list refers to user_trialist = 1;
SQL: SELECT T1.movie_title FROM movies AS T1 INNER JOIN ratings AS T2 ON T1.movie_id = T2.movie_id WHERE T2.user_trialist = 1 AND T2.rating_timestamp_utc LIKE '2020-04%'

Database: computer_student
Question: Between the faculty employee professors, how many teaches high-level or harder undergraduate courses? Indicate each of the professors unique identifying number.
Evidence: faculty employee professors refers to hasPosition = 'Faculty_eme' and professor = 1; high-level or harder undergraduate courses refers to courseLevel = 'Level_400'; professors unique identifying number refers to person.p_id
SQL: SELECT COUNT(*) FROM person AS T1 INNER JOIN taughtBy AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T2.course_id WHERE T1.hasPosition = 'Faculty_eme' AND T1.professor = 1 AND T3.courseLevel = 'Level_400'