Database: codebase_comments
Question: List 5 github address that the solutions can be implemented without the need of compilation.
Evidence: github address refers to Url; solution can be implemented without the need of compliation refers to WasCompiled = 1;
SQL: SELECT T1.Url FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId WHERE T2.WasCompiled = 1 LIMIT 5

Database: simpson_episodes
Question: Who produced The simpson 20s: Season 20?
Evidence: produced refers to role = 'producer'
SQL: SELECT DISTINCT person FROM Credit WHERE role = 'producer';

Database: shakespeare
Question: Which Shakespeare story with character ID 324 has description of 'this friend of Caesar'?
SQL: SELECT T1.Title FROM works AS T1 INNER JOIN chapters AS T2 ON T1.id = T2.work_id INNER JOIN paragraphs AS T3 ON T2.id = T3.chapter_id INNER JOIN characters AS T4 ON T3.character_id = T4.id WHERE T2.id = '324' AND T2.Description = 'friend to Caesar'

Database: olympics
Question: Give the NOC code and region name of the heaviest competitor.
Evidence: NOC code refers to noc; the heaviest refers to MAX(weight);
SQL: SELECT T1.noc, T1.region_name FROM noc_region AS T1 INNER JOIN person_region AS T2 ON T1.id = T2.region_id INNER JOIN person AS T3 ON T2.person_id = T3.id ORDER BY T3.weight DESC LIMIT 1

Database: authors
Question: Mention the titile of paper writen by Joe Lograsso.
Evidence: 'Joe Lograsso' is name of paper author
SQL: SELECT T1.Title FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T2.Name = 'Joe Lograsso'

Database: simpson_episodes
Question: Which title is the winner of Best International TV Series in 2017?
Evidence: winner refers to result = 'Winner'; Best International TV Series in 2017 refers to award = 'Best International TV Series' and year = '2017'


SQL: SELECT T2.title FROM Award AS T1 INNER JOIN Episode AS T2 ON T1.episode_id = T2.episode_id WHERE T1.award = 'Best International TV Series' AND SUBSTR(T1.year, 1, 4) = '2017';

Database: chicago_crime
Question: How many percent of domestic violence cases were arrested in West Pullman?
Evidence: domestic violence refers to domestic = 'TRUE'; arrested refers to arrest = 'TRUE'; West Pullman refers to community_area_name = 'West Pullman'; percent = divide(count(report_no where arrest = 'TRUE'), count(report_no)) where domestic = 'TRUE' and community_area_name = 'West Pullman' * 100%
SQL: SELECT CAST(COUNT(CASE WHEN T2.arrest = 'TRUE' THEN T2.report_no END) AS REAL) * 100 / COUNT(T2.report_no) FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T2.community_area_no = T1.community_area_no WHERE T1.community_area_name = 'West Pullman' AND T2.domestic = 'TRUE'

Database: chicago_crime
Question: How many violation of laws are there where no arrest has been made?
Evidence: "The violation of laws " is the description of incidents; no arrest has been made refers to arrest = 'FALSE'
SQL: SELECT SUM(CASE WHEN T1.description LIKE '%The violation of laws%' THEN 1 ELSE 0 END) FROM FBI_Code AS T1 INNER JOIN Crime AS T2 ON T1.fbi_code_no = T2.fbi_code_no WHERE T2.Arrest = 'FALSE'

Database: public_review_platform
Question: List out the user id that has compliment type of photos.
Evidence: compliment type of photos refers to compliment_type = 'photos'
SQL: SELECT T2.user_id FROM Compliments AS T1 INNER JOIN Users_Compliments AS T2 ON T1.compliment_id = T2.compliment_id WHERE T1.compliment_type = 'photos'

Database: airline
Question: How many planes of Spirit Air Lines took off on 2018/8/7?
Evidence: Spirit Air Lines refers to Description = 'Spirit Air Lines: NK'; on 2018/8/7 refers to FL_DATE = '2018/8/7';
SQL: SELECT COUNT(T2.Code) FROM Airlines AS T1 INNER JOIN `Air Carriers` AS T2 ON T1.OP_CARRIER_AIRLINE_ID = T2.Code WHERE T1.FL_DATE = '2018/8/7' AND T2.Description = 'Spirit Air Lines: NK'

Database: college_completion
Question: What is the ratio of Asian male graduates to Asian female graduates from Harvard University in 2013?
Evidence: ratio = MULTIPLY(DIVIDE(SUM(grad_cohort WHERE Gender = 'M'), SUM( grad_cohort WHERE Gender = 'F')), 1.0); Asian refers to race = 'A'; female refers to gender = 'F'; graduates refers to grad_cohort; male refers to gender = 'M'; Harvard University refers to chronname = 'Harvard University'; in 2013 refers to year = 2013;
SQL: SELECT CAST(SUM(CASE WHEN T2.Gender = 'M' THEN T2.grad_cohort ELSE 0 END) AS REAL) / SUM(CASE WHEN T2.Gender = 'F' THEN T2.grad_cohort ELSE 0 END) FROM institution_details AS T1 INNER JOIN institution_grads AS T2 ON T1.unitid = T2.unitid WHERE T1.chronname = 'Harvard University' AND T2.year = 2013 AND T2.race = 'A'

Database: soccer_2016
Question: What is the role of SC Ganguly in the match on 2008/4/18?
Evidence: role refers to of Role_Id; SC Ganguly refers to Player_Name = 'SC Ganguly'; on 2008/4/18 refers to Match_Date = '2008-04-18'
SQL: SELECT T2.Role_Id FROM Player AS T1 INNER JOIN Player_Match AS T2 ON T1.Player_Id = T2.Player_Id INNER JOIN Rolee AS T3 ON T2.Role_Id = T3.Role_Id INNER JOIN Match AS T4 ON T2.Match_Id = T4.Match_Id WHERE T1.Player_Name = 'SC Ganguly' AND T4.Match_Date = '2008-04-18'

Database: legislator
Question: Among legislators who have an Instagram account, list down their full names and nicknames who have a Thomas ID of less than 1000.
Evidence: have an Instagram account refers to instagram is not null; full names refers to official_full_name; nicknames refers to nickname_name; Thomas ID of less than 1000 refers to thomas_id < 1000;
SQL: SELECT T1.official_full_name, T1.nickname_name FROM current AS T1 INNER JOIN `social-media` AS T2 ON T2.bioguide = T1.bioguide_id WHERE T2.instagram IS NOT NULL AND T1.thomas_id < 1000

Database: address
Question: Which state has greater than 50 CBSA officers of metro type?
Evidence: greater than 50 CBSA officers of metro type refers to COUNT(CBSA_type = 'Metro') > 50;
SQL: SELECT T2.state FROM CBSA AS T1 INNER JOIN zip_data AS T2 ON T1.CBSA = T2.CBSA WHERE T1.CBSA_type = 'Metro' GROUP BY T2.state HAVING COUNT(T1.CBSA_type) > 50

Database: image_and_language
Question: Calculate the percentage of "airplane" object class in the table.
Evidence: DIVIDE(SUM(OBJ_SAMPLE_ID where OBJ_CLASS = 'airplane'), COUNT(OBJ_CLASS)) as percentage;
SQL: SELECT CAST(SUM(CASE WHEN T2.OBJ_CLASS = 'airplane' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T2.OBJ_CLASS) FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID

Database: image_and_language
Question: How many object samples are there in image no.1?
Evidence: object samples refers to OBJ_SAMPLE_ID; image no.1 refers to IMG_ID = 1
SQL: SELECT COUNT(OBJ_SAMPLE_ID) FROM IMG_OBJ WHERE IMG_ID = 1

Database: bike_share_1
Question: On 8/29/2013, who took the longest to arrive in California Ave Caltrain Station from University and Emerson? Indicate the bike id.
Evidence: start_date = '8/29/2013'; end_date = '8/29/2013'; end_station_name = 'California Ave Caltrain Station'; start_station_name = 'University and Emerson'; who took the longest to arrive refers to MAX(duration);
SQL: SELECT bike_id FROM trip WHERE start_date LIKE '8/29/2013%' AND end_date LIKE '8/29/2013%' AND end_station_name = 'California Ave Caltrain Station' AND start_station_name = 'University and Emerson' AND duration = ( SELECT MAX(duration) FROM trip WHERE start_date LIKE '8/29/2013%' AND end_date LIKE '8/29/2013%' AND end_station_name = 'California Ave Caltrain Station' AND start_station_name = 'University and Emerson' )

Database: books
Question: How many books were published in 2017?
Evidence: published in 2017 refers to Substr(publication_date,1, 4) = '2017'
SQL: SELECT COUNT(*) FROM book WHERE STRFTIME('%Y', publication_date) = '2017'