Database: cs_semester
Question: Which professor is Oliy Spratling working with? Give the full name.
Evidence: research assistant refers to the student who serves for research where the abbreviation is RA; full name refers to f_name and l_name;
SQL: SELECT T1.first_name, T1.last_name FROM prof AS T1 INNER JOIN RA AS T2 ON T1.prof_id = T2.prof_id INNER JOIN student AS T3 ON T2.student_id = T3.student_id WHERE T3.f_name = 'Oliy' AND T3.l_name = 'Spratling'

Database: public_review_platform
Question: What is the review length of user 11021 to business with business ID 3?
Evidence: review length refers to review_length; user 11021 refers to user_id = 11021; business ID 3 refers to business_id = 3
SQL: SELECT review_length FROM Reviews WHERE user_id = 11021 AND business_id = 3

Database: chicago_crime
Question: What are the full names of the top 5 most crowded ward aldermen?
Evidence: most crowded ward refers to Max(Population); full name of alderman refers to alderman_first_name, alderman_last_name
SQL: SELECT alderman_first_name, alderman_last_name FROM Ward ORDER BY Population DESC LIMIT 5

Database: law_episode
Question: For how many times was Park Dietz credited?
Evidence: credited refers to credited = 'true'
SQL: SELECT COUNT(T3.person_id) FROM Episode AS T1 INNER JOIN Credit AS T2 ON T1.episode_id = T2.episode_id INNER JOIN Person AS T3 ON T3.person_id = T2.person_id WHERE T2.credited = 'true' AND T3.name = 'Park Dietz'

Database: simpson_episodes
Question: List the name of persons who were not included in the credit for the 'How the Test Was Won' episode.
Evidence: "How the Test Was Won" is the title of episode; not included in the credit refers to credited = ' '; name of person refers to person
SQL: SELECT T2.person FROM Episode AS T1 INNER JOIN Credit AS T2 ON T1.episode_id = T2.episode_id WHERE T1.title = 'How the Test Was Won' AND T2.credited = 'false';

Database: movielens
Question: List the id of male users who gave ratings of less than 3 to French movies with running time of 2.
Evidence: Male users mean that u_gender = 'M'; France is a country
SQL: SELECT T1.userid FROM u2base AS T1 INNER JOIN movies AS T2 ON T1.movieid = T2.movieid INNER JOIN users AS T3 ON T1.userid = T3.userid WHERE T2.country = 'France' AND T2.runningtime = 2 AND T1.rating < 3 AND T3.u_gender = 'M'

Database: chicago_crime
Question: What is the precise coordinate of the location where simple assault incidents happened the most in Chatham?
Evidence: precise coordinates refers to latitude, longitude; 'Simple Assault' is the title of incident; 'Chatham' is the community_area_name; most incident happened refers to Max(Count(latitude, longitude))
SQL: SELECT T2.latitude, T2.longitude FROM FBI_Code AS T1 INNER JOIN Crime AS T2 ON T1.fbi_code_no = T2.fbi_code_no INNER JOIN Community_Area AS T3 ON T2.community_area_no = T3.community_area_no WHERE T1.title = 'Simple Assault' AND T3.community_area_name = 'Chatham' AND T3.community_area_no = 44 ORDER BY T2.latitude DESC, T2.longitude DESC LIMIT 1

Database: talkingdata
Question: How many events were participated by the users at longitude of "-156"?
SQL: SELECT COUNT(event_id) FROM events WHERE longitude = -156

Database: chicago_crime
Question: How many crime cases have been classified as "Weapons Violation" by the FBI?
Evidence: "Weapons Violation" is the title of crime; crime cases refers to report_no;
SQL: SELECT SUM(CASE WHEN T2.title = 'Weapons Violation' THEN 1 ELSE 0 END) FROM Crime AS T1 INNER JOIN FBI_Code AS T2 ON T1.fbi_code_no = T2.fbi_code_no

Database: retail_complains
Question: In 2014, what is the percentage of complaints from consumers in Houston that the delay was over 5 days?
Evidence: in 2014 refers to Date received LIKE'2014%'; Houston refers to city = 'Houston'; delay over 5 days refers to subtract(Date sent to company, Date received) > 5; percentage = divide(count(Complaint ID where subtract(Date sent to company , Date received) > 5) , count(Complaint ID)) * 100%
SQL: SELECT CAST((SUM(CASE WHEN strftime('%J', T2.`Date sent to company`) - strftime('%J', T2.`Date received`) > 5 THEN 1 ELSE 0 END)) AS REAL) * 100 / COUNT(T1.client_id) FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T1.city = 'Houston' AND strftime('%Y', T2.`Date received`) = '2014'

Database: legislator
Question: Among the current legislators who have accounts on both http://thomas.gov, how many of them have accounts on instagram?
Evidence: have accounts on both http://thomas.gov refers to thomas_id is NOT null; have accounts on instagram refers to instagram is not null;
SQL: SELECT COUNT(DISTINCT T1.bioguide_id) FROM current AS T1 INNER JOIN `social-media` AS T2 ON T1.bioguide_id = T2.bioguide WHERE T1.thomas_id IS NOT NULL AND T2.instagram IS NOT NULL

Database: simpson_episodes
Question: Among the casts who were born in Los Angeles, describe the name and birth date of who have 1.8 m and above in height.
Evidence: "Los Angeles" is the birth_place; 1.8 m and above in height refers to height_meters > = 1.8
SQL: SELECT name, birthdate FROM Person WHERE birth_place = 'Los Angeles' AND height_meters >= 1.8;

Database: address
Question: What is the percentage ratio between Democrats and Republicans in Indiana? List the zip codes belonging to Democrats.
Evidence: "Democrats" and "Republicans" refers to party = 'Democrat" and party = 'Republican'; percentage ratio = Multiply (Divide (Count(party = 'Democrat"), Count(party = 'Republican')), 100)
SQL: SELECT CAST(COUNT(CASE WHEN T2.party = 'Democrat' THEN 1 ELSE NULL END) AS REAL) / COUNT(CASE WHEN T2.party = 'Republican' THEN 1 ELSE NULL END)FROM zip_congress AS T1 INNER JOIN congress AS T2 ON T2.cognress_rep_id = T1.district

Database: beer_factory
Question: How many times did Anna Himes use her Mastercard when paying between 12/25/2014 and 5/20/2016 ?
Evidence: Mastercard refers to CreditCardType = 'MasterCard'; between 12/25/2014 and 5/20/2016 refers to TransactionDate BETWEEN '2014-12-25' AND '2016-05-20';
SQL: SELECT COUNT(T2.CustomerID) FROM customers AS T1 INNER JOIN `transaction` AS T2 ON T1.CustomerID = T2.CustomerID WHERE T1.First = 'Anna' AND T1.Last = 'Himes' AND T2.CreditCardType = 'MasterCard' AND T2.TransactionDate BETWEEN '2014-12-25' AND '2016-05-20'

Database: retail_world
Question: How many territories are there in the Eastern Region?
Evidence: Eastern Region refers to RegionDescription = 'Eastern';
SQL: SELECT COUNT(T1.TerritoryID) FROM Territories AS T1 INNER JOIN Region AS T2 ON T1.RegionID = T2.RegionID WHERE T2.RegionDescription = 'Eastern'

Database: codebase_comments
Question: Please list the path of the solution that contains files found within the repository most people like.
Evidence: more stars mean more people like this repository; most people like refers to max(Stars);
SQL: SELECT DISTINCT T2.Path FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId WHERE T1.Stars = ( SELECT MAX(Stars) FROM Repo )

Database: authors
Question: How many papers were preprinted between the years 1990 and 2000?
Evidence: years 1990 and 2000 refers to Year BETWEEN '1990' AND '2000'; papers refers to COUNT(id)
SQL: SELECT COUNT(id) FROM Paper WHERE Year BETWEEN '1990' AND '2000' AND ConferenceId = 0 AND JournalId = 0

Database: retail_complains
Question: What is the percentage of male clients complaining about their credit cards?
Evidence: percentage = MULTIPLY(DIVIDE(SUM(sex = 'Male'), COUNT(client_id)), 1.0); male refers to sex = 'Male'; credit cards refers to Product = 'Credit card';
SQL: SELECT CAST(SUM(CASE WHEN T1.sex = 'Male' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.sex) FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID WHERE T2.Product = 'Credit card'