Database: airline
Question: Which airline operated more flights on 2018/8/1, American Airlines Inc. or Endeavor Air Inc.?
Evidence: SUM(Description = 'American Airlines Inc.: AA') > SUM(Description = 'Endeavor Air Inc.: 9E') means American Airlines Inc. operated more flights than  Endeavor Air Inc; SUM(Description = 'American Airlines Inc.: AA') < SUM(Description = 'Endeavor Air Inc.: 9E') means Endeavor Air Inc. operated more flights than American Airlines Inc.; on 2018/8/1 refers to FL_DATE = '2018/8/1';
SQL: SELECT CASE WHEN COUNT(CASE WHEN T3.Description = 'American Airlines Inc.: AA' THEN 1 ELSE NULL END) > COUNT(CASE WHEN T3.Description = 'Endeavor Air Inc.: 9E' THEN 1 ELSE NULL END) THEN 'American Airlines Inc.: AA' ELSE 'Endeavor Air Inc.: 9E' END AS RESULT FROM Airports AS T1 INNER JOIN Airlines AS T2 ON T1.Code = T2.ORIGIN INNER JOIN `Air Carriers` AS T3 ON T2.OP_CARRIER_AIRLINE_ID = T3.Code WHERE T2.FL_DATE = '2018/8/1'

Database: retails
Question: What is the name and marketing segment of the customer with the total order price of 199180.63?
Evidence: name of the customer refers to c_name; total order price of 199180.63 refers o_totalprice = 199180.63; marketing segment refers to c_mktsegment;
SQL: SELECT T2.c_name, T2.c_mktsegment FROM orders AS T1 INNER JOIN customer AS T2 ON T1.o_custkey = T2.c_custkey WHERE T1.o_totalprice = 199180.63

Database: software_company
Question: Find the response status to customer whose geographic ID of 134.
Evidence: GEOID = 134;
SQL: SELECT T2.RESPONSE FROM Customers AS T1 INNER JOIN mailings3 AS T2 ON T1.ID = T2.REFID WHERE T1.GEOID = 134

Database: airline
Question: Which airline does the aircraft with the fastest flight belong to?
Evidence: fastest flight refers to MIN(SUBTRACT(ACTUAL_ELAPSED_TIME, CRS_ELAPSED_TIME));
SQL: SELECT T1.OP_CARRIER_AIRLINE_ID FROM Airlines AS T1 INNER JOIN Airports AS T2 ON T1.ORIGIN = T2.Code WHERE T1.ACTUAL_ELAPSED_TIME IS NOT NULL AND T1.CRS_ELAPSED_TIME IS NOT NULL ORDER BY T1.ACTUAL_ELAPSED_TIME - T1.CRS_ELAPSED_TIME ASC LIMIT 1

Database: sales_in_weather
Question: How many units of item no.9 were sold in store no.1 in total in January, 2012?
Evidence: store no. 1 refers to store_nbr = 1; item no. 9 refers to item_nbr = 9; in January refers to SUBSTR(date, 1, 4) = '2012' and SUBSTR(date, 6, 2) = '01'
SQL: SELECT SUM(units) FROM sales_in_weather WHERE SUBSTR(`date`, 6, 2) = '01' AND SUBSTR(`date`, 1, 4) = '2012' AND item_nbr = 9 AND store_nbr = 1

Database: olympics
Question: At what age did Michael Fred Phelps, II join the Olympics?
Evidence: At what age join the Olympics refers to MIN(age);
SQL: SELECT T2.age FROM person AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.person_id WHERE T1.full_name = 'Michael Fred Phelps, II' ORDER BY T2.age LIMIT 1

Database: movie_3
Question: Compute the total payment made by Sarah Lewis for film rentals so far.
Evidence: total payment = sum(amount)
SQL: SELECT SUM(T3.amount) FROM rental AS T1 INNER JOIN customer AS T2 ON T1.customer_id = T2.customer_id INNER JOIN payment AS T3 ON T1.rental_id = T3.rental_id WHERE T2.first_name = 'SARAH' AND T2.last_name = 'LEWIS'

Database: books
Question: Provide the International Standard Book Number of the book The Mystery in the Rocky Mountains.
Evidence: International Standard Book Number refers to isbn13; 'The Mystery in the Rocky Mountains' is the title of the book
SQL: SELECT isbn13 FROM book WHERE title = 'The Mystery in the Rocky Mountains'

Database: college_completion
Question: Among the states that start with letter A and attained a national sector average of 16.5, give the number of degree-seeking students in the cohort of those students in 2012 .
Evidence: state that starts with letter A refers to state LIKE 'A%'; national sector average of 16.5 refers to awards_per_natl_value = 16.5; number of degree-seeking students in the cohort refers to grad_cohort; in 2012 refers to year = '2012';
SQL: SELECT SUM(T2.grad_cohort) FROM state_sector_details AS T1 INNER JOIN state_sector_grads AS T2 ON T2.stateid = T1.stateid WHERE T2.state LIKE 'A%' AND T1.awards_per_natl_value = 16.5 AND T2.year = 2012

Database: shipping
Question: List the weight of the customer's shipment with annual revenue of 39448581.
SQL: SELECT T1.weight FROM shipment AS T1 INNER JOIN customer AS T2 ON T1.cust_id = T2.cust_id WHERE T2.annual_revenue = 39448581

Database: books
Question: Who is the publisher of Hitchhiker's Guide To The Galaxy: The Filming of the Douglas Adams classic?
Evidence: "Hitchhiker's Guide To The Galaxy: The Filming of the Douglas Adams classic" is the title of the book; publisher refers to publisher_name
SQL: SELECT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id WHERE T1.title = 'Hitchhiker''s Guide To The Galaxy: The Filming of the Douglas Adams classic'

Database: chicago_crime
Question: What percentage of non-domestic crimes have occurred in the Jefferson Park district?
Evidence: non domestic crime refers to domestic = 'FALSE'; 'Jefferson Park' is the district_name; percentage = Divide (Count (case_number where domestic = 'FALSE'), Count(case_number)) * 100
SQL: SELECT CAST(COUNT(CASE WHEN T2.domestic = 'FALSE' THEN T2.case_number END) AS REAL) * 100 / COUNT(T2.case_number) FROM District AS T1 INNER JOIN Crime AS T2 ON T2.district_no = T1.district_no WHERE T1.district_name = 'Jefferson Park'

Database: student_loan
Question: List out student IDs that have the longest absence duration from school.
Evidence: longest absence duration refers to MAX(month)
SQL: SELECT name FROM longest_absense_from_school WHERE `month` = ( SELECT MAX(month) FROM longest_absense_from_school )

Database: music_platform_2
Question: In how many categories were podcast reviews created in the last six months of 2016? List them.
Evidence: created in last six months of 2016 refers to created_at BETWEEN '2016-07-01T00:00:00-07:00' and '2016-12-31T23:59:59-07:00'
SQL: SELECT COUNT(DISTINCT T1.category) FROM categories AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T2.created_at BETWEEN '2016-07-01T00:00:00-07:00' AND '2016-12-31T23:59:59-07:00'

Database: authors
Question: Among the papers published in the journal "Molecular Brain", how many of them were published in the year 2011?
Evidence: "Molecular Brain" is the FullName of journal
SQL: SELECT COUNT(T2.Id) FROM Journal AS T1 INNER JOIN Paper AS T2 ON T1.Id = T2.JournalId WHERE T2.Year = 2011 AND T1.FullName = 'Molecular Brain'

Database: law_episode
Question: What was the role of Jason Kuschner in episode 9?
SQL: SELECT T1.role FROM Credit AS T1 INNER JOIN Person AS T2 ON T2.person_id = T1.person_id INNER JOIN Episode AS T3 ON T1.episode_id = T3.episode_id WHERE T3.episode = 9 AND T2.name = 'Jason Kuschner'

Database: car_retails
Question: How many Sales Rep who are working in Tokyo? List out email and full name of those employees.
Evidence: Sales Rep is a job title; Tokyo is a city; full name = firstName+lastName;
SQL: SELECT T1.firstName, T1.lastName, T1.email FROM employees AS T1 INNER JOIN offices AS T2 ON T1.officeCode = T2.officeCode WHERE T2.city = 'Tokyo' AND T1.jobTitle = 'Sales Rep'

Database: book_publishing_company
Question: What is the price of the book that sells the best?
Evidence: qty is abbreviation for quantity; sells the best mean with the most sales quantity; MAX(qty)
SQL: SELECT T2.price FROM sales AS T1 INNER JOIN titles AS T2 ON T1.title_id = T2.title_id ORDER BY T1.qty DESC LIMIT 1

Database: cs_semester
Question: How many research assistants does Sauveur Skyme have?
Evidence: research assistant refers to the student who serves for research where the abbreviation is RA;
SQL: SELECT COUNT(T1.student_id) FROM RA AS T1 INNER JOIN prof AS T2 ON T1.prof_id = T2.prof_id WHERE T2.first_name = 'Sauveur' AND T2.last_name = 'Skyme'