Database: synthea
Question: Give me the immunization codes and dates on which Ms. Jacquelyn Shanahan was immunized with influenza seasonal injectable preservative-free vaccine.
Evidence: immunization codes refers to immunizations.CODE; immunization dates refers to immunizations.DATE; immunized with influenza seasonal injectable preservative-free vaccine refers to immunizations.DESCRIPTION = 'Influenza seasonal injectable preservative free'; 4 consecutive years refers to 4 succeeding years;
SQL: SELECT DISTINCT T2.CODE, T2.DATE FROM patients AS T1 INNER JOIN immunizations AS T2 ON T1.patient = T2.PATIENT WHERE T1.prefix = 'Ms.' AND T1.first = 'Jacquelyn' AND T1.last = 'Shanahan' AND T2.DESCRIPTION = 'Influenza  seasonal  injectable  preservative free'

Database: university
Question: How many students were there in University of Michigan in 2011?
Evidence: in 2011 refers to year 2011; in University of Michigan refers to university_name = 'University of Michigan';
SQL: SELECT COUNT(*) FROM university AS T1 INNER JOIN university_year AS T2 ON T1.id = T2.university_id WHERE T1.university_name = 'University of Michigan' AND T2.year = 2011

Database: car_retails
Question: Calculate the actual profit for order number 10100.
Evidence: SUM(MULTIPLY(quantityOrdered (SUBTRACT (priceEach, buyPrice));
SQL: SELECT SUM((t1.priceEach - t2.buyPrice) * t1.quantityOrdered) FROM orderdetails AS t1 INNER JOIN products AS t2 ON t1.productCode = t2.productCode WHERE t1.orderNumber = '10100'

Database: professional_basketball
Question: For team who has more home won than home lost more than 80%, list the team name and the offense points.
Evidence: home won than home lost more than 80% refers to Divide(Subtract(homeWon, homeLost), games) > 0.8; offense point refers to o_fgm
SQL: SELECT name, o_pts FROM teams WHERE CAST((homeWon - homeLost) AS REAL) * 100 / games > 80

Database: food_inspection
Question: Among the routine inspections of Tiramisu Kitchen, how many of them have a score of over 70?
Evidence: Tiramisu Kitchen is the name of the business; routine inspections refer to type = 'Routine - Unscheduled'; score of over 70 refers to score > 70;
SQL: SELECT COUNT(T2.business_id) FROM inspections AS T1 INNER JOIN businesses AS T2 ON T1.business_id = T2.business_id WHERE T2.name = 'Tiramisu Kitchen' AND T1.type = 'Routine - Unscheduled' AND T1.score > 70

Database: simpson_episodes
Question: Calculate the percentage of people who were born after 1970 and from California.
Evidence: born after 1970 refers to birthdate > 1970; from California refers to birth_region = 'California'; percentage = divide(count(birthdate > 1970 and birth_region = 'California'), total(birthdate)) * 100%
SQL: SELECT CAST(SUM(CASE WHEN birth_region = 'California' AND SUBSTR(birthdate, 1, 4) > '1970' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(birthdate) FROM Person;

Database: retail_world
Question: Among the employees, give me the full names of those who have less than 4 territories.
Evidence: less than 4 territories refers to EmployeeID where Count(TerritoryID) < 4
SQL: SELECT T1.FirstName, T1.LastName FROM Employees AS T1 INNER JOIN EmployeeTerritories AS T2 ON T1.EmployeeID = T2.EmployeeID WHERE T2.EmployeeID < 4

Database: regional_sales
Question: In the West, how many stores are there in the city whose land area is below 20,000,000?
Evidence: "West" is the Region; land area is below 20,000,000 refers to Land Area < 20,000,000
SQL: SELECT SUM(CASE WHEN T1.Region = 'West' AND T2.`Land Area` < 20000000 THEN 1 ELSE 0 END) FROM Regions AS T1 INNER JOIN `Store Locations` AS T2 ON T2.StateCode = T1.StateCode

Database: movie_platform
Question: How many users were not trialists when they rated the movie "Patti Smith: Dream of Life"?
Evidence: Patti Smith: Dream of Life' is movie_title; the user was not a trialist when he created the list refers to user_trialist = 0;
SQL: SELECT COUNT(T1.user_id) FROM ratings AS T1 INNER JOIN movies AS T2 ON T1.movie_id = T2.movie_id WHERE T2.movie_title = 'Patti Smith: Dream of Life' AND T1.user_trialist = 0

Database: movie_3
Question: Tell the special features of the film Uprising Uptown.
Evidence: "UPRISING UPTOWN" is the title of film
SQL: SELECT special_features FROM film WHERE title = 'UPRISING UPTOWN'

Database: menu
Question: What is the name of the menu with the highest number of pages?
Evidence: highest number of pages refers to MAX(COUNT(page_number));
SQL: SELECT T1.name FROM Menu AS T1 INNER JOIN MenuPage AS T2 ON T1.id = T2.menu_id GROUP BY T2.menu_id ORDER BY COUNT(T2.page_number) DESC LIMIT 1

Database: soccer_2016
Question: What are the average extra runs given in the second innings of every match?
Evidence: second innings refers to Innings_No = 2; average extra runs = divide(sum(Extra_Runs), count(Innings_No)) when Innings_No = 2
SQL: SELECT AVG(Innings_No) FROM Extra_Runs WHERE Innings_No = 2

Database: professional_basketball
Question: How many teams have played more than 3800 points and have player with "Most Valuable Player" award?
Evidence: played more than 3800 points refers to Sum(points) > = 3800
SQL: SELECT COUNT(DISTINCT T4.name) FROM ( SELECT T1.name, SUM(T2.points) FROM teams AS T1 INNER JOIN players_teams AS T2 ON T1.tmID = T2.tmID AND T1.year = T2.year INNER JOIN awards_players AS T3 ON T2.playerID = T3.playerID WHERE T3.award = 'Most Valuable Player' GROUP BY T1.name HAVING SUM(T2.points) >= 3800 ) AS T4

Database: synthea
Question: What gender is more prone to 'dander (animal) allergy'?
Evidence: gender who is more prone to dander (animal) allergy refers to MAX(COUNT(Gender WHERE allergies.DESCRIPTION = 'Dander (animal) allergy'));
SQL: SELECT T1.gender FROM patients AS T1 INNER JOIN allergies AS T2 ON T1.patient = T2.PATIENT WHERE T2.DESCRIPTION = 'Dander (animal) allergy' GROUP BY T1.gender ORDER BY COUNT(T1.gender) DESC LIMIT 1

Database: video_games
Question: How many sales does game platform id 3871 make in Europe?
Evidence: number of sales = multiply(num_sales, 100000); in Europe refers to region_name = 'Europe'
SQL: SELECT T2.num_sales * 100000 FROM region AS T1 INNER JOIN region_sales AS T2 ON T1.id = T2.region_id WHERE T1.region_name = 'Europe' AND T2.game_platform_id = 3871

Database: retail_world
Question: Which employee has created the least order and please indicates the employee's title?
Evidence: least order refers to Min(OrderID)
SQL: SELECT T1.Title FROM Employees AS T1 INNER JOIN Orders AS T2 ON T1.EmployeeID = T2.EmployeeID GROUP BY T1.Title ORDER BY COUNT(T2.OrderID) LIMIT 1

Database: trains
Question: Among the trains that run in the east direction, how many of them have at least one car in a non-regular shape?
Evidence: non-regular shape refers to shape in ('elipse', 'bucket')
SQL: SELECT SUM(CASE WHEN T1.shape IN ('bucket', 'elipse') THEN 1 ELSE 0 end)as count FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east'

Database: student_loan
Question: How many male students filed for bankruptcy as compare to female students?
Evidence: difference = SUBTRACT(COUNT(filed_for_bankrupcy.name who are In male.name), COUNT(filed_for_bankrupcy.name who are NOT in male.name)); male students are mentioned in male.name; female students refers to filed_for_bankrupy.name who are NOT in male.name;
SQL: SELECT COUNT(T2.name) - SUM(IIF(T2.name IS NULL, 1, 0)) AS num FROM filed_for_bankrupcy AS T1 LEFT JOIN male AS T2 ON T2.name = T1.name

Database: student_loan
Question: List out female students that enrolled in occ school and ulca?
Evidence: female students refers to enrolled.name who are NOT in male.name; occ school and ulca refers to school IN('occ', 'ulca');
SQL: SELECT name FROM enrolled WHERE school IN ('occ', 'ulca') AND name NOT IN ( SELECT name FROM male )