Database: synthea
Question: Identify the allergy period for Isadora Moen and what triggered it.
Evidence: allergy period = SUBTRACT(allergies.START, allergies.STOP); what triggered the allergy refers to allergies.DESCRIPTION;
SQL: SELECT T2.START, T2.STOP, T2.DESCRIPTION FROM patients AS T1 INNER JOIN allergies AS T2 ON T1.patient = T2.PATIENT WHERE T1.first = 'Isadora' AND T1.last = 'Moen'

Database: chicago_crime
Question: List the case number of crimes against society that happened in June 2018.
Evidence: crime against society refers to crime_against = 'Society'; in June 2018 refers to date LIKE '6/%/2018%'
SQL: SELECT T2.case_number FROM FBI_Code AS T1 INNER JOIN Crime AS T2 ON T2.fbi_code_no = T1.fbi_code_no WHERE T2.date LIKE '6/%/2018%' AND T1.crime_against = 'Society'

Database: public_review_platform
Question: Describe category name which had above 10% in comparing with all business and categories.
Evidence: above 10% refers to DIVIDE(COUNT(Business_Categories.business_id = category_id), COUNT(category_id)) * 100% > 10%
SQL: SELECT T1.category_name FROM Categories AS T1 INNER JOIN Business_Categories AS T2 ON T1.category_id = T2.category_id GROUP BY T2.category_id HAVING COUNT(T2.business_id) > ( SELECT COUNT(T3.business_id) FROM Business_Categories AS T3 ) * 0.1

Database: law_episode
Question: Display the number of 9-star votes the episode Sideshow received.
Evidence: 9-star vote refers to stars = '9'; episode Sideshow refers to title = 'Sideshow'
SQL: SELECT T2.votes FROM Episode AS T1 INNER JOIN Vote AS T2 ON T1.episode_id = T2.episode_id WHERE T2.stars = 9 AND T1.title = 'Sideshow'

Database: donor
Question: What is the average donation amount to a project created by a teacher working in a school in Brooklyn?
Evidence: school in Brooklyn refers to school_city = 'Brooklyn'; Average = AVG(donation_total);
SQL: SELECT SUM(T2.donation_total) / COUNT(donationid) FROM projects AS T1 INNER JOIN donations AS T2 ON T1.projectid = T2.projectid WHERE T1.school_city = 'Brooklyn'

Database: codebase_comments
Question: What is the repository number for the solution of method "SCore.Poisson.ngtIndex"?
Evidence: repository number refers to RepoId; method refers to Name; Name = ‘SCore.Poisson.ngtIndex’
SQL: SELECT T1.RepoId FROM Solution AS T1 INNER JOIN Method AS T2 ON T1.Id = T2.SolutionId WHERE T2.Name = 'SCore.Poisson.ngtIndex'

Database: world_development_indicators
Question: Which form of government has more countries that have completed the actual external debt reporting between the two types of government accounting concepts, budgetary central government vs. consolidated central government?
Evidence: have completed the actual external debt reporting refers to ExternalDebtReportingStatus = 'Actual'
SQL: SELECT SUM(CASE WHEN GovernmentAccountingConcept = 'Budgetary central government' THEN 1 ELSE 0 END), SUM(CASE WHEN GovernmentAccountingConcept = 'Consolidated central government' THEN 1 ELSE 0 END) central_nums FROM country WHERE ExternalDebtReportingStatus = 'Actual'

Database: books
Question: What is the average price for the order line?
Evidence: average price refers to AVG(price)
SQL: SELECT AVG(price) FROM order_line

Database: retails
Question: Among the products manufactured by manufacturer 5 that have a retail price of no more than 1,000, how many products were shipped via rail?
Evidence: manufacturer 5 refers to p_mfgr = 'Manufacturer#5'; retail price of no more than 1,000 refers to p_retailprice < 1000; shipped via rail refers to shipmode = 'RAIL';
SQL: SELECT COUNT(T1.ps_partkey) FROM partsupp AS T1 INNER JOIN lineitem AS T2 ON T1.ps_suppkey = T2.l_suppkey INNER JOIN part AS T3 ON T1.ps_partkey = T3.p_partkey WHERE T3.p_mfgr = 'Manufacturer#5' AND T3.p_retailprice < 1000 AND T2.l_shipmode = 'RAIL'

Database: social_media
Question: Which state was the tweet `tw-685681052912873473` from? Give the state code.
Evidence: tw-685681052912873473' is the TweetID
SQL: SELECT T2.StateCode FROM twitter AS T1 INNER JOIN location AS T2 ON T2.LocationID = T1.LocationID WHERE T1.TweetID = 'tw-685681052912873473'

Database: airline
Question: List the tail numbers of all the aircraft that arrived on time at Meadows Field airport in August of 2018?
Evidence: tail number refers to TAIL_NUM; arrived on time refers to ARR_DELAY < = 0; Meadows Field airport refers to Description = 'Bakersfield, CA: Meadows Field'; in August of 2018 refers to FL_DATE like '2018/8%';
SQL: SELECT T2.TAIL_NUM FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.DEST WHERE T2.FL_DATE LIKE '2018/8%' AND T1.Description = 'Bakersfield, CA: Meadows Field' AND T2.DEST = 'BFL' AND T2.ARR_DELAY <= 0 GROUP BY T2.TAIL_NUM

Database: superstore
Question: Calculate the difference between the total sales in the East superstore and the total sales in the West superstore.
Evidence: East superstore refers to Region = 'East'; West superstore refers to Region = 'West'; difference = subtract(sum(Sales) when Region = 'East', sum(Sales) when Region = 'West')
SQL: SELECT SUM(T1.Sales) - SUM(T2.Sales) AS difference FROM east_superstore AS T1 INNER JOIN west_superstore AS T2 ON T1.`Customer ID` = T2.`Customer ID`

Database: movies_4
Question: How many horror movies are there?
Evidence: horror movies refers to genre_name = 'Horror'
SQL: SELECT COUNT(T1.movie_id) FROM movie_genres AS T1 INNER JOIN genre AS T2 ON T1.genre_id = T2.genre_id WHERE T2.genre_name = 'Horror'

Database: public_review_platform
Question: Identify the most popular and appealing active business in Gilbert based on users' reviews.
Evidence: most popular and appealing refers to review_count = 'High' and max(stars); active business refers to active = 'true'; in Gilbert refers to city = 'Gilbert'
SQL: SELECT business_id FROM Business WHERE city = 'Gilbert' AND active = 'true' AND review_count = 'High' ORDER BY stars DESC LIMIT 1

Database: book_publishing_company
Question: State the title and royalty percentage for title ID BU2075 between 10000 to 50000 range.
Evidence: lorange mean low range; hirange mean high range; range refers to between the low and high range; lorange>10000; hirange<12000
SQL: SELECT T1.title, T2.royalty FROM titles AS T1 INNER JOIN roysched AS T2 ON T1.title_id = T2.title_id WHERE T2.lorange > 10000 AND T2.hirange < 50000 AND T1.title_ID = 'BU2075'

Database: food_inspection_2
Question: List down the inspection ID with the inspector's comment "A certified food service manager must be present in all establishments at which potentially hazardous food is prepared or served. NO CERTIFIED FOOD MANAGER ON DUTY AT THIS TIME FOODS ARE COOKED AND SERVED SERIOUS CITATION ISSUED" and inspection category of Personnel.
Evidence: inspector's comment "A certified food service manager must be present in all establishments at which potentially hazardous food is prepared or served. NO CERTIFIED FOOD MANAGER ON DUTY AT THIS TIME FOODS ARE COOKED AND SERVED SERIOUS CITATION ISSUED" refers to inspector_comment = 'A certified food service manager must be present in all establishments at which potentially hazardous food is prepared or served.FOUND NO CITY OF CHICAGO SANITATION CERTIFICATE POSTED OR VALID DOCUMENTATION DURING THIS INSPECTION.'; inspection category of Personnel refers to category = 'Personnel'
SQL: SELECT T2.inspection_id FROM inspection_point AS T1 INNER JOIN violation AS T2 ON T1.point_id = T2.point_id WHERE T1.category = 'Personnel' AND T2.inspector_comment = 'A certified food service manager must be present in all establishments at which potentially hazardous food is prepared or served.FOUND NO CITY OF CHICAGO SANITATION CERTIFICATE POSTED OR VALID DOCUMENTATION DURING THIS INSPECTION.'

Database: restaurant
Question: How many restaurants in the city of Richmond serve Mediterranean food?
Evidence: Mediterranean food refers to food_type =   'mediterranean'
SQL: SELECT COUNT(id_restaurant) FROM generalinfo WHERE food_type = 'mediterranean' AND city = 'richmond'