Database: retail_world
Question: Calculate the percentage salary of employees who handled orders shipped in 1996.
Evidence: shipped in 1996 refers to ShippedDate = 1996; percentage salary = divide(sum(Salary when ShippedDate = 1996), sum(Salary)) as percentage
SQL: SELECT CAST(SUM(CASE WHEN STRFTIME('%Y', T2.ShippedDate) = '1996' THEN T1.Salary ELSE 0 END) AS REAL) * 100 / SUM(T1.Salary) FROM Employees AS T1 INNER JOIN Orders AS T2 ON T1.EmployeeID = T2.EmployeeID

Database: soccer_2016
Question: Provide the country ID of East London.
Evidence: East London refers to City_Name = 'East London'
SQL: SELECT Country_id FROM City WHERE City_Name = 'East London'

Database: student_loan
Question: State the unemployed students who enlisted in marines.
Evidence: enlisted in marines refers to organ = 'marines';
SQL: SELECT T1.name FROM unemployed AS T1 INNER JOIN enlist AS T2 ON T1.name = T2.name WHERE T2.organ = 'marines'

Database: public_review_platform
Question: Mention the user ID and their year of joining Yelp who had great experience on business ID 143.
Evidence: year of joining Yelp refers to user_yelping_since_year; great experience refers to Reviews where review_stars = 5;
SQL: SELECT T2.user_id, T2.user_yelping_since_year FROM Reviews AS T1 INNER JOIN Users AS T2 ON T1.user_id = T2.user_id WHERE T1.business_id = 143 AND T1.review_stars = 5

Database: book_publishing_company
Question: Among the publishers in the USA, how many of them have published books that are over $15?
Evidence: are over $15 refers to price>15
SQL: SELECT COUNT(DISTINCT T1.pub_id) FROM titles AS T1 INNER JOIN publishers AS T2 ON T1.pub_id = T2.pub_id WHERE T2.country = 'USA' AND T1.price > 15

Database: music_platform_2
Question: List all the cagetories for all the podcasts with "jessica" in the title.
Evidence: podcast with 'jessica' in title refers to title like '%jessica%'
SQL: SELECT category FROM categories WHERE podcast_id IN ( SELECT podcast_id FROM podcasts WHERE title LIKE '%jessica%' )

Database: cookbook
Question: Provide the ingredients that are rationed in the recipe with the highest carbohydrate content.
Evidence: the highest carbohydrate content refers to MAX(carbo)
SQL: SELECT T1.name FROM Ingredient AS T1 INNER JOIN Quantity AS T2 ON T1.ingredient_id = T2.ingredient_id INNER JOIN Nutrition AS T3 ON T3.recipe_id = T2.recipe_id WHERE T2.max_qty = T2.min_qty ORDER BY T3.carbo DESC LIMIT 1

Database: movielens
Question: List the ids and ratings of each actors played in the movie with the id 1722327?
SQL: SELECT T1.actorid, T1.a_quality FROM actors AS T1 INNER JOIN movies2actors AS T2 ON T1.actorid = T2.actorid WHERE T2.movieid = 1722327

Database: retail_world
Question: Of the customers who are from Canada, how many used Federal Shipping?
Evidence: from Canada refers to Country = 'Canada'; Federal Shipping refers to ShipName = 'Federal Shipping'
SQL: SELECT COUNT(T3.CustomerID) FROM Shippers AS T1 INNER JOIN Orders AS T2 ON T1.ShipperID = T2.ShipVia INNER JOIN Customers AS T3 ON T2.CustomerID = T3.CustomerID WHERE T2.ShipName = 'Federal Shipping'

Database: human_resources
Question: How many Account Representatives are there in Illinois with satisfying performance?
Evidence: Account Representatives is a position title; satisfying performance mostly refers togood performance
SQL: SELECT COUNT(*) FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID INNER JOIN position AS T3  ON T3.positionID = T1.positionID WHERE T3.positiontitle = 'Account Representative' AND T1.performance = 'Good' AND T2.state = 'IL'

Database: books
Question: Who published the book "The Secret Garden"?
Evidence: "The Secret Garden" is the title of the book; who published the book refers to publisher_name
SQL: SELECT DISTINCT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id WHERE T1.title = 'The Secret Garden'

Database: food_inspection_2
Question: How many inspection points with serious point level that have no fine?
Evidence: serious point level refers to point_level = 'Serious '; have no fine refers to fine = 0
SQL: SELECT COUNT(DISTINCT T2.point_id) FROM inspection_point AS T1 INNER JOIN violation AS T2 ON T1.point_id = T2.point_id WHERE T1.point_level = 'Serious ' AND T2.fine = 0

Database: food_inspection_2
Question: State the salary of the employee who did the most inspections.
Evidence: the most inspections refers to max(count(employee_id))
SQL: SELECT T1.salary FROM employee AS T1 INNER JOIN ( SELECT T.employee_id, COUNT(T.inspection_id) FROM inspection AS T GROUP BY T.employee_id ORDER BY COUNT(T.inspection_id) DESC LIMIT 1 ) AS T2 ON T1.employee_id = T2.employee_id

Database: student_loan
Question: What is the average absent month for a unemployed male students?
Evidence: average = DIVIDE(SUM(month), COUNT(unemployed.name who are in male.name)); unemployed male students refers to unemployed.name who are IN male.name;
SQL: SELECT AVG(T2.month) AS avg FROM unemployed AS T1 INNER JOIN longest_absense_from_school AS T2 ON T2.name = T1.name INNER JOIN male AS T3 ON T3.name = T2.name

Database: retails
Question: Please list the names of all the suppliers for the part "hot spring dodger dim light".
Evidence: supplier name refers to s_name; part "hot spring dodger dim light" refers to p_name = 'hot spring dodger dim light'
SQL: SELECT T2.s_name FROM partsupp AS T1 INNER JOIN supplier AS T2 ON T1.ps_suppkey = T2.s_suppkey INNER JOIN part AS T3 ON T1.ps_partkey = T3.p_partkey WHERE T3.p_name = 'hot spring dodger dim light'

Database: codebase_comments
Question: List all the path of solution from all the "it" lang code method.
Evidence: path of the solution refers to Path; solution refers to Solution.Id;
SQL: SELECT DISTINCT T1.Path FROM Solution AS T1 INNER JOIN Method AS T2 ON T1.Id = T2.SolutionId WHERE T2.Lang = 'it'

Database: shipping
Question: How many shipments were shipped by the driver named Zachary Hicks?
SQL: SELECT COUNT(*) FROM shipment AS T1 INNER JOIN driver AS T2 ON T1.driver_id = T2.driver_id WHERE T1.driver_id = 23

Database: food_inspection_2
Question: List point level of inspections with no fine.
Evidence: no fine refers to fine = 0
SQL: SELECT DISTINCT T1.point_level FROM inspection_point AS T1 INNER JOIN violation AS T2 ON T1.point_id = T2.point_id WHERE T2.fine = 0

Database: public_review_platform
Question: Which business ID have the shortest business operating hours?
Evidence: the shortest business operating hours refer to MIN(SUBTRACT(closing_time, opening_time));
SQL: SELECT business_id FROM Business_Hours ORDER BY closing_time - opening_time LIMIT 1

Database: movie_platform
Question: Provide list titles created by user who are eligible for trial when he created the list.
Evidence: eligible for trial refers to user_eligible_for_trial = 1
SQL: SELECT DISTINCT T2.list_title FROM lists_users AS T1 INNER JOIN lists AS T2 ON T1.list_id = T2.list_id WHERE T1.user_eligible_for_trial = 1

Database: world_development_indicators
Question: What is the long name of the country with the description "Estimates are derived from data on foreign-born population." on the series code SM.POP.TOTL?
SQL: SELECT T1.LongName FROM Country AS T1 INNER JOIN CountryNotes AS T2 ON T1.CountryCode = T2.Countrycode WHERE T2.Description = 'Estimates are derived FROM data on foreign-born population.' AND T2.Seriescode = 'SM.POP.TOTL'

Database: synthea
Question: List out all the observation information collected for the patient named Bella Rolfson.
Evidence: observation information refers to observations.DESCRIPTION AND observations.VALUE AND observations.UNITS;
SQL: SELECT DISTINCT T2.DESCRIPTION, T2.VALUE, T2.UNITS FROM patients AS T1 INNER JOIN observations AS T2 ON T1.patient = T2.PATIENT WHERE T1.first = 'Bella' AND T1.last = 'Rolfson'