Database: movielens
Question: Among the worst actresses, how many of them got a rating of more than 3 to the movies they starred?
Evidence: Worst actresses means a_quality is the least; The least a_quality is 0
SQL: SELECT COUNT(T1.userid) FROM u2base AS T1 INNER JOIN movies2actors AS T2 ON T1.movieid = T2.movieid INNER JOIN actors AS T3 ON T2.actorid = T3.actorid INNER JOIN users AS T4 ON T1.userid = T4.userid WHERE T3.a_quality = 0 AND T1.rating > 3 AND T4.u_gender = 'F'

Database: shakespeare
Question: Calculate the percentage of paragraphs in all chapters of "All's Well That Ends Well".
Evidence: "All's Well That Ends Well" refers to Title = 'All's Well That Ends Well'; percentage = divide(sum(paragraphs.id) when Title = 'All's Well That Ends Well', count(paragraphs.id)) as percentage
SQL: SELECT CAST(SUM(IIF(T1.Title = 'All''s Well That Ends Well', 1, 0)) AS REAL) * 100 / COUNT(T3.id) 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

Database: chicago_crime
Question: Give the FBI code for the crime described by "The killing of one human being by another."
Evidence: "The killing of one human being by another" is the description; FBI code refers to fbi_code_no
SQL: SELECT fbi_code_no FROM FBI_Code WHERE description = 'The killing of one human being by another.'

Database: talkingdata
Question: List the app users IDs and installed status for the event ID of 844.
Evidence: app user IDs refers to app_id; is_installed = 1 means the app status is installed; is_installed = 0 means the app status is not installed;
SQL: SELECT app_id , IIF(is_installed = 1, 'YES', 'NO') AS status FROM app_events WHERE event_id = 844

Database: talkingdata
Question: Among the users who use a Galaxy Note 2, how many of them are female?
Evidence: Galaxy Note 2 refers to device_model = 'Galaxy Note 2'; female refers to gender = 'F';
SQL: SELECT COUNT(T1.device_id) FROM phone_brand_device_model2 AS T1 INNER JOIN gender_age AS T2 ON T2.device_id = T1.device_id WHERE T2.gender = 'F' AND T1.device_model = 'Galaxy Note 2'

Database: soccer_2016
Question: How many Indian cities are there in the database?
Evidence: Indian refers to Country_Name = 'India'
SQL: SELECT SUM(CASE WHEN T2.Country_Name = 'India' THEN 1 ELSE 0 END) FROM City AS T1 INNER JOIN Country AS T2 ON T1.Country_Id = T2.Country_Id

Database: books
Question: Name the publisher of the oldest book.
Evidence: publisher refers to publisher_name;  oldest book refers to Min(publication_date)
SQL: SELECT T2.publisher_name FROM book AS T1 INNER JOIN publisher AS T2 ON T1.publisher_id = T2.publisher_id ORDER BY T1.publication_date ASC LIMIT 1

Database: law_episode
Question: Who is the person who appeared the most in the series? Calculate in percentage how many times he or she appeared.
Evidence: who refers to name; appear the most refers to max(count(person_id)); percentage = divide(count(person_id where max(count(person_id))), count(person_id)) * 100%
SQL: SELECT T2.person_id, CAST(COUNT(T2.person_id) AS REAL) * 100 / ( SELECT COUNT(T2.person_id) AS num FROM Credit AS T1 INNER JOIN Person AS T2 ON T2.person_id = T1.person_id ) AS per FROM Credit AS T1 INNER JOIN Person AS T2 ON T2.person_id = T1.person_id GROUP BY T2.person_id ORDER BY COUNT(T2.person_id) DESC LIMIT 1

Database: movie_3
Question: Among films with store ID of 2, list the title of films with the highest rental rate.
Evidence: highest rental rate refers to MAX(rental_rate)
SQL: SELECT T1.title FROM film AS T1 INNER JOIN inventory AS T2 ON T1.film_id = T2.film_id WHERE T2.store_id = 2 ORDER BY rental_rate DESC LIMIT 1

Database: retail_world
Question: Name products and their quantity ordered by the company 'GROSELLA-Restaurante' in the sales order that was processed by Nancy Davolio.
Evidence: name products refers to ProductName; 'GROSELLA-Restaurante' refers to CompanyName; 'Nancy Davolio' is the full name of an employee; full name refers to FirstName, LastName;
SQL: SELECT T4.ProductName, T3.Quantity FROM Employees AS T1 INNER JOIN Orders AS T2 ON T1.EmployeeID = T2.EmployeeID INNER JOIN `Order Details` AS T3 ON T2.OrderID = T3.OrderID INNER JOIN Products AS T4 ON T3.ProductID = T4.ProductID INNER JOIN Customers AS T5 ON T2.CustomerID = T5.CustomerID WHERE T1.FirstName = 'Nancy' AND T1.LastName = 'Davolio' AND T5.CompanyName = 'GROSELLA-Restaurante'

Database: shipping
Question: What is the average shipment weight carried by the oldest Mack?
Evidence: "Mack" is the make; oldest refers to Min(model_year); average shipment weight refers to AVG(weight)
SQL: SELECT AVG(T2.weight) FROM truck AS T1 INNER JOIN shipment AS T2 ON T1.truck_id = T2.truck_id WHERE T1.make = 'Mack'

Database: olympics
Question: For how many times has London held the Olympic games?
Evidence: London refers to city_name = 'London'; how many times refer to COUNT(games_id);
SQL: SELECT COUNT(T1.games_id) FROM games_city AS T1 INNER JOIN city AS T2 ON T1.city_id = T2.id WHERE T2.city_name = 'London'

Database: genes
Question: How many pairs of positively correlated genes are both non-essential?
Evidence: If Expression_Corr > 0, it means the expression correlation is positive
SQL: SELECT COUNT(T2.GeneID2) FROM Genes AS T1 INNER JOIN Interactions AS T2 ON T1.GeneID = T2.GeneID1 WHERE T2.Expression_Corr > 0 AND T1.Essential = 'Non-Essential'

Database: movies_4
Question: What is the original language of the movie with the tagline "An offer you can't refuse."?
Evidence: language refers to language_name; original language refers to language_role = 'Original'
SQL: SELECT T3.language_name FROM movie AS T1 INNER JOIN movie_languages AS T2 ON T1.movie_id = T2.movie_id INNER JOIN language AS T3 ON T2.language_id = T3.language_id INNER JOIN language_role AS T4 ON T2.language_role_id = T4.role_id WHERE T4.language_role = 'Original' AND T1.tagline LIKE 'An offer you can%t refuse.'

Database: human_resources
Question: Which employee's job position requires a higher education level, Jose Rodriguez or Sandy Adams?
Evidence: Jose Rodriguez AND Sandy Adams are the fullname of employee; full name = firstname, lastname; higher education level refers to MAX(educationrequired)
SQL: SELECT T1.firstname, T1.lastname FROM employee AS T1  INNER JOIN position AS T2  ON T1.positionID = T2.positionID WHERE (T1.lastname = 'Adams' AND T1.firstname = 'Sandy') OR (T1.lastname = 'Rodriguez' AND T1.firstname = 'Jose') ORDER BY T2.educationrequired DESC LIMIT 1

Database: address
Question: Among the residential areas with the bad alias "Internal Revenue Service", how many of them are in the Eastern time zone?
Evidence: "Internal Revenue Service" is the bad_alias; in Eastern time zone refers to time_zone = 'Eastern'
SQL: SELECT COUNT(T1.zip_code) FROM zip_data AS T1 INNER JOIN avoid AS T2 ON T1.zip_code = T2.zip_code WHERE T2.bad_alias = 'Internal Revenue Service' AND T1.time_zone = 'Eastern'

Database: talkingdata
Question: What is the difference between the events of device number -9222956879900150000 that can be located and those that are unable to be located?
Evidence: difference = SUBTRACT(SUM(latitude! = 0 and longitude! = 0), SUM(latitude = 0 and longitude = 0)); device number refers to device_id; device_id = -922956879900150000; can be location refers to latitude ! = 0 and longitude ! = 0; unable to be location refers to latitude = 0 and longitude = 0;
SQL: SELECT SUM(IIF(latitude != 0 AND longitude != 0, 1, 0)) - SUM(IIF(latitude = 0 AND longitude = 0, 1, 0)) AS diff FROM events WHERE device_id = '-922956879900150000'

Database: student_loan
Question: Please list all the female students that have filed for bankruptcy.
Evidence: females students have filed for bankruptcy refers to name that appeared in both filed_for_bankrupcy and male tables
SQL: SELECT name FROM filed_for_bankrupcy WHERE name NOT IN ( SELECT name FROM male )