Database: beer_factory
Question: Please name all of the cities in California.
Evidence: California refers to State = 'CA';
SQL: SELECT DISTINCT City FROM customers WHERE State = 'CA'

Database: movies_4
Question: Find the difference in percentage of the movies under keywords of "woman director" and "independent film".
Evidence: under keywords of "woman director" and "independent film" refers to keyword_name = 'woman director' and keyword_name = 'independent film'; difference in percentage = divide(subtract(count(movie_id) when keyword_name = 'woman director', count(movie_id) when keyword_name = 'independent film'), count(movie_id)) as percentage
SQL: SELECT CAST((SUM(CASE WHEN T1.keyword_name = 'woman director' THEN 1 ELSE 0 END) - SUM(CASE WHEN T1.keyword_name = 'independent film' THEN 1 ELSE 0 END)) AS REAL) * 100 / SUM(CASE WHEN T1.keyword_name = 'independent film' THEN 1 ELSE 0 END) FROM keyword AS T1 INNER JOIN movie_keywords AS T2 ON T1.keyword_id = T2.keyword_id

Database: retails
Question: In the parts supply by Supplier#000000654, list the top five parts with the most supply cost in descending order of supply cost.
Evidence: Supplier#000000654 is the name of the supplier which refers to s_name; parts with the most supply cost refer to ps_partkey where MAX(ps_supplycost);
SQL: SELECT T2.ps_partkey FROM supplier AS T1 INNER JOIN partsupp AS T2 ON T1.s_suppkey = T2.ps_suppkey WHERE T1.s_name = 'Supplier#000000654' ORDER BY T2.ps_supplycost DESC LIMIT 5

Database: sales
Question: How many types of "HL Touring Frames" are there?
Evidence: types of HL Touring Frames refers to Name like '%HL Touring Frame%';
SQL: SELECT COUNT(ProductID) FROM Products WHERE Name LIKE '%HL Touring Frame%'

Database: sales
Question: How many customers are named Madison?
SQL: SELECT COUNT(CustomerID) FROM Customers WHERE FirstName = 'Madison'

Database: soccer_2016
Question: When and for what role did the youngest player appear in his first match?
Evidence: When refers to Match_Date; youngest player refers to max(DOB); first match refers to min(Match_Date)
SQL: SELECT T1.Match_Date, T4.Role_Desc FROM `Match` AS T1 INNER JOIN Player_Match AS T2 ON T1.Match_Id = T2.Match_Id INNER JOIN Player AS T3 ON T2.Player_Id = T3.Player_Id INNER JOIN Rolee AS T4 ON T2.Role_Id = T4.Role_Id ORDER BY T3.DOB DESC LIMIT 1

Database: public_review_platform
Question: Which city has the least number of businesses whose amount of funny votes is low?
Evidence: least number of businesses refers to MIN(business_id); funny votes is low refers to review_votes_funny = 'Low';
SQL: SELECT T1.city FROM Business AS T1 INNER JOIN Reviews AS T2 ON T1.business_id = T2.business_id WHERE T2.review_votes_funny LIKE 'low' GROUP BY T1.city

Database: chicago_crime
Question: Who is the commanding officer in the district with the highest number of disorderly conduct?
Evidence: commanding officer refers to commander; the highest number refers to max(count(district_no)); disorderly conduct refers to title = 'Disorderly Conduct'
SQL: SELECT T1.commander FROM District AS T1 INNER JOIN Crime AS T2 ON T1.district_no = T2.district_no INNER JOIN FBI_Code AS T3 ON T2.fbi_code_no = T3.fbi_code_no WHERE T3.title = 'Disorderly Conduct' AND T2.fbi_code_no = 24 GROUP BY T2.fbi_code_no ORDER BY COUNT(T1.district_no) DESC LIMIT 1

Database: video_games
Question: What are the game IDs of the games published by Bethesda Softworks?
Evidence: Bethesda Softworks refers to publisher_name = 'Bethesda Softworks';
SQL: SELECT T1.game_id FROM game_publisher AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.id WHERE T2.publisher_name = 'Bethesda Softworks'

Database: law_episode
Question: How many episodes are there in the 9th season of Law and Order? Calculate the average number of casts per season of the said series.
Evidence: the 9th season refers to season = 9; Law and Order refers to series = 'Law and Order'; cast refers to category = 'Cast'; average number of casts per season = divide(count(person_id), count(episode_id))
SQL: SELECT SUM(CASE WHEN T2.season = 9 THEN 1 ELSE 0 END) AS num , CAST(SUM(CASE WHEN T2.season = 9 THEN 1 ELSE 0 END) AS REAL) / COUNT(T1.episode_id) FROM Credit AS T1 INNER JOIN Episode AS T2 ON T1.episode_id = T2.episode_id WHERE T1.category = 'Cast' AND T2.series = 'Law and Order'

Database: retails
Question: Among the customers from Brazil, how many customers are in automobile market segment?
Evidence: customers refer to c_custkey; Brazil is the name of the nation which refers to n_name = 'BRAZIL'; c_mktsegment = 'automobile';
SQL: SELECT COUNT(T1.c_custkey) FROM customer AS T1 INNER JOIN nation AS T2 ON T1.c_nationkey = T2.n_nationkey WHERE T1.c_mktsegment = 'AUTOMOBILE' AND T2.n_name = 'BRAZIL'

Database: college_completion
Question: What is the name of the school with the highest difference in the average completion rate for the national in which it belongs? Indicate the state appropriations to higher education in fiscal year 2011 per resident to which the school belongs.
Evidence: name of the school refers to chronname; highest difference in the average completion rate for the national in which it belongs = MAX(SUBTRACT(awards_per_value, awards_per_natl_value)); state appropriations to higher education in fiscal year 2011 per resident to which the school belongs refers to state_appr_value;
SQL: SELECT T1.chronname, T2.state_appr_value FROM institution_details AS T1 INNER JOIN state_sector_details AS T2 ON T2.state = T1.state ORDER BY T1.awards_per_value - T2.awards_per_natl_value DESC LIMIT 1

Database: donor
Question: How many donors in Los Angeles donated to school in another city?
Evidence: in Los Angeles refers to donor_city = 'Los Angeles'; school in another city refers to school_city ! = 'Los Angeles'
SQL: SELECT COUNT(T2.schoolid) FROM donations AS T1 INNER JOIN projects AS T2 ON T1.projectid = T2.projectid WHERE T1.donor_city = 'Los Angeles' AND T2.school_city NOT LIKE 'Los Angeles'

Database: retail_world
Question: What is the average salary of sales representatives in the United Kingdom?
Evidence: average salary = avg(Salary); sales representative refers to Title = 'Sales Representative'; in the United Kingdom refers to Country = 'UK'
SQL: SELECT AVG(Salary) FROM Employees WHERE Title = 'Sales Representative' AND Country = 'UK'

Database: computer_student
Question: State the courses and level of courses by professors who are faculty employees.
Evidence: professors who are faculty employees refers to professor = 1; faculty employees refers to hasPosition = 'Faculty_eme'
SQL: SELECT T3.course_id, T3.courseLevel FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id INNER JOIN course AS T3 ON T3.course_id = T1.course_id WHERE T2.hasPosition = 'Faculty_eme'

Database: retails
Question: Customer No.129301 made an order on 1996/7/27, what was the delivery time for the first part of that order?
Evidence: SUBTRACT(l_receiptdate, l_commitdate) WHERE o_orderdate = '1996-07-27' AND o_custkey = '129301';
SQL: SELECT JULIANDAY(T2.l_receiptdate) - JULIANDAY(T2.l_commitdate) FROM orders AS T1 INNER JOIN lineitem AS T2 ON T1.o_orderkey = T2.l_orderkey WHERE T1.o_custkey = '129301' AND T1.o_orderdate = '1996-07-27'

Database: beer_factory
Question: List out root beer brand that is not caffeinated and not containing cane sugar. What is the total amount sold for this products?
Evidence: root beer brand refers to BrandName; not caffeinated refers to Caffeinated = 'FALSE'; not containing cane sugar refers to CaneSugar = 'FALSE'; total amount sold = SUM(PurchasePrice);
SQL: SELECT T1.BrandName, SUM(T3.PurchasePrice) FROM rootbeerbrand AS T1 INNER JOIN rootbeer AS T2 ON T1.BrandID = T2.BrandID INNER JOIN `transaction` AS T3 ON T2.RootBeerID = T3.RootBeerID WHERE T1.CaneSugar = 'FALSE' AND T1.Caffeinated = 'FALSE' GROUP BY T1.BrandName