Database: movies_4
Question: Who played Captain Jack Sparrow in all of the Pirates of the Caribbean movies?
Evidence: Captain Jack Sparrow refers to character_name = 'Captain Jack Sparrow'; Pirates of the Caribbean movies refers to title LIKE 'Pirates of the Carribbean%'
SQL: SELECT DISTINCT T3.person_name FROM movie AS T1 INNER JOIN movie_cast AS T2 ON T1.movie_id = T2.movie_id INNER JOIN person AS T3 ON T2.person_id = T3.person_id WHERE T2.character_name = 'Captain Jack Sparrow' AND T1.title LIKE 'Pirates of the Caribbean%'

Database: professional_basketball
Question: Which coach has the most 'won' than 'lost' in year '1988'?
Evidence: in year '1988' refers to year = 1988; the most 'won' than 'lost' refers to max(subtract(won, lost))
SQL: SELECT coachID FROM coaches WHERE year = 1988 ORDER BY won - lost DESC LIMIT 1

Database: talkingdata
Question: What are the behavior categories that user number -9222198347540750000 belongs to?
Evidence: behavior categories refers to category; user number refers to app_id; app_id = -9222198347540750000;
SQL: SELECT T3.category FROM app_all AS T1 INNER JOIN app_labels AS T2 ON T1.app_id = T2.app_id INNER JOIN label_categories AS T3 ON T2.label_id = T3.label_id WHERE T1.app_id = -9222198347540750000

Database: language_corpus
Question: What is the title of the page on which the word "grec" has an occurrence of 52 times.
Evidence: occurrence of 52 times refers to pages_words.occurrences = 52; grec refers to word = 'grec'
SQL: SELECT T3.title FROM words AS T1 INNER JOIN pages_words AS T2 ON T1.wid = T2.wid INNER JOIN pages AS T3 ON T2.pid = T3.pid WHERE T1.word = 'grec' AND T2.occurrences = 52

Database: university
Question: How many institutions with over 50,000 students in 2011 had a percentage of oversea students of more than 10%?
Evidence: institutions with over 50,000 students refers to num_students > 50000; in 2011 refers to year = 2011; percentage of oversea students of more than 10% refers to pct_international_students > 10;
SQL: SELECT COUNT(*) FROM university_year WHERE year = 2011 AND num_students > 50000 AND pct_international_students > 10

Database: european_football_1
Question: What is the percentage whereby the away team scored 2 goals during the 2017 seasons?
Evidence: scored 2 goals refers to FTAG = 2, which is short name for Final-time Away-team Goals; DIVIDE(COUNT(Div where season = 2017, FTAG = '2'), COUNT(Div where season = 2017)) as percentage;
SQL: SELECT CAST(SUM(CASE WHEN FTAG = 2 THEN 1 ELSE 0 END) / COUNT(FTAG) AS REAL) * 100 FROM matchs WHERE season = 2017

Database: student_loan
Question: How many students are enlisted to the air force?
Evidence: Air Force refers to organ = 'air_force';
SQL: SELECT COUNT(name) FROM enlist WHERE organ = 'air_force'

Database: books
Question: Among all orders updated in 2022, identify the percentage that has been returned.
Evidence: order updated in 2022 refers to SUBSTR(status_date, 1, 4) = '2022'; has been returned refers to status_value = 'Returned'; percentage = Divide (Count(status_value = 'Returned'), Count(status_value)) * 100
SQL: SELECT CAST(SUM(CASE WHEN T1.status_value = 'Returned' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(*) FROM order_status AS T1 INNER JOIN order_history AS T2 ON T1.status_id = T2.status_id WHERE STRFTIME('%Y', T2.status_date) = '2022'

Database: retail_complains
Question: Among the teenager clients who use Google account and Microsoft account, which group of client is more than the other?
Evidence: teenager refers to 13 < age < = 19; Google account refers to email like '%@gmail.com'; Microsoft account refers to email like '%@outlook.com'
SQL: SELECT CASE WHEN SUM(CASE WHEN email LIKE '%@gmail.com' THEN 1 ELSE 0 END) > SUM(CASE WHEN email LIKE '%@outlook.com' THEN 1 ELSE 0 END) THEN 'Google account' ELSE 'Microsoft account' END FROM client WHERE age BETWEEN 13 AND 19

Database: movie_platform
Question: How many movies have a popularity of more than 400 but less than 500? Indicate the name of the movies and the highest rating score each movie has received.
Evidence: popularity of more than 400 but less than 500 refers to movie_popularity BETWEEN 400 AND 500; highest rating score refer to MAX(rating_score)


SQL: SELECT T1.movie_title, MAX(T2.rating_score) FROM movies AS T1 INNER JOIN ratings AS T2 ON T1.movie_id = T2.movie_id WHERE T1.movie_popularity BETWEEN 400 AND 500 GROUP BY T1.movie_title

Database: social_media
Question: From which country is the tweet with the most likes posted?
Evidence: tweet with the most likes refers to Max(Likes)
SQL: SELECT T2.Country FROM twitter AS T1 INNER JOIN location AS T2 ON T2.LocationID = T1.LocationID ORDER BY T1.Likes DESC LIMIT 1

Database: shakespeare
Question: What is the description of the chapter where the character whose abrreviated name is 1Play appeared first?
Evidence: abbreviated name is 1Play; appeared first refers to Abbrev = '1Play' and min(chapter_id)
SQL: SELECT T2.Description FROM paragraphs AS T1 INNER JOIN chapters AS T2 ON T1.chapter_id = T2.id INNER JOIN characters AS T3 ON T1.character_id = T3.id WHERE T3.Abbrev = '1Play' ORDER BY T1.chapter_id LIMIT 1

Database: shipping
Question: What is the brand and model of truck used in shipment id 1055?
Evidence: shipment id 1055 refers to ship_id = 1055; brand refers to make; model refers to model_year
SQL: SELECT T1.make, T1.model_year FROM truck AS T1 INNER JOIN shipment AS T2 ON T1.truck_id = T2.truck_id WHERE T2.ship_id = '1055'

Database: donor
Question: What was the title for the project which got the biggest donation?
Evidence: biggest donation refers to donation_total = 'max';
SQL: SELECT T1.title FROM essays AS T1 INNER JOIN donations AS T2 ON T1.projectid = T2.projectid WHERE T2.donation_total = ( SELECT MAX(donation_total) FROM donations )

Database: cs_semester
Question: What is the difficulty of the course in which a student with level of intellengence of 5 got an A grade?
Evidence: difficulty of the course refers to diff;
SQL: SELECT T3.diff FROM student AS T1 INNER JOIN registration AS T2 ON T1.student_id = T2.student_id INNER JOIN course AS T3 ON T2.course_id = T3.course_id WHERE T2.grade = 'A' AND T1.intelligence = 5

Database: human_resources
Question: Who is the highest paid employee in "Boston"? Give the full name.
Evidence: Boston refers to locationcity = 'Boston'; the highest paid employee refers to MAX(salary); full name = firstname, lastname
SQL: SELECT T1.firstname, T1.lastname FROM employee AS T1  INNER JOIN location AS T2  ON T1.locationID = T2.locationID WHERE T2.locationcity = 'Boston' ORDER BY T1.salary DESC LIMIT 1

Database: shipping
Question: Among the shipments shipped to Cicero, Illinois, how many shipments weighed between 9,000 to 15,000?
Evidence: "Cicero" is the city; 'Illinois' is the state
SQL: SELECT COUNT(*) FROM shipment AS T1 INNER JOIN city AS T2 ON T1.city_id = T2.city_id WHERE T2.city_name = 'Cicero' AND T2.state = 'Illinois' AND T1.weight BETWEEN 9000 AND 15000

Database: movies_4
Question: What is the average ratio between female and male actors in a movie?
Evidence: female refers to gender = 'Female'；male refers to gender = 'Male'; average ratio = divide(divide(sum(gender_id) when gender = 'Female', sum(gender_id) when gender = 'Male'), count(movie_id)) as percentage
SQL: SELECT CAST(COUNT(CASE WHEN T2.gender = 'Female' THEN T1.person_id ELSE NULL END) AS REAL) / COUNT(CASE WHEN T2.gender = 'Male' THEN T1.person_id ELSE NULL END) FROM movie_cast AS T1 INNER JOIN gender AS T2 ON T1.gender_id = T2.gender_id

Database: synthea
Question: How many black patients stopped their care plan in 2017?
Evidence: black refers to race = 'black'; stopped their care plan in 2017 refers to substr(careplans.STOP, 1, 4) = '2017';
SQL: SELECT COUNT(DISTINCT T2.patient) FROM careplans AS T1 INNER JOIN patients AS T2 ON T1.PATIENT = T2.patient WHERE T2.race = 'black' AND strftime('%Y', T1.STOP) = '2017'