Database: retails
Question: What is the total number of orders made by customers in United States?
Evidence: orders refer to o_orderkey; the United States is the name of the nation which refers to n_name = 'UNITED STATES';
SQL: SELECT COUNT(T1.o_orderkey) FROM orders AS T1 INNER JOIN customer AS T2 ON T1.o_custkey = T2.c_custkey INNER JOIN nation AS T3 ON T2.c_nationkey = T3.n_nationkey WHERE T3.n_name = 'UNITED STATES'

Database: music_platform_2
Question: How many reviews does 'LifeAfter/The Message' have which were rated below 3?
Evidence: LifeAfter/The Message' is the title of podcast; rated below 3 refers to rating < 3
SQL: SELECT COUNT(T2.rating) FROM podcasts AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.title = 'LifeAfter/The Message' AND T2.rating <= 3

Database: mental_health_survey
Question: How many users participated in the mental health survey for 2014?
Evidence: mental health survey for 2014 refers to SurveyID = 2014
SQL: SELECT COUNT(DISTINCT T1.UserID) FROM Answer AS T1 INNER JOIN Survey AS T2 ON T1.SurveyID = T2.SurveyID WHERE T2.Description = 'mental health survey for 2014'

Database: movie_3
Question: List the store ID of the film titled "Amadeus Holy".
SQL: SELECT T2.store_id FROM film AS T1 INNER JOIN inventory AS T2 ON T1.film_id = T2.film_id WHERE T1.title = 'Amadeus Holy'

Database: public_review_platform
Question: Mention the user average star, elite year and the compliment type of user ID 6027 whereby number of compliments reach uber.
Evidence: number of compliments reach uber refers to number_of_compliments = 'Uber'; elite year refers to year_id; user average star refers to user_average_stars
SQL: SELECT T2.user_average_stars, T1.year_id, T4.compliment_type, T3.number_of_compliments FROM Elite AS T1 INNER JOIN Users AS T2 ON T1.user_id = T2.user_id INNER JOIN Users_Compliments AS T3 ON T2.user_id = T3.user_id INNER JOIN Compliments AS T4 ON T3.compliment_id = T4.compliment_id INNER JOIN Years AS T5 ON T1.year_id = T5.year_id WHERE T3.number_of_compliments = 'Uber' AND T3.user_id = 6027

Database: car_retails
Question: How many employees are there in Sydney?
Evidence: sales agent and sales representative are synonyms; Sydney is a city;
SQL: SELECT COUNT(employeeNumber) FROM employees WHERE officeCode = ( SELECT officeCode FROM offices WHERE city = 'Sydney' )

Database: movie_platform
Question: Which year has the least number of movies that was released and what is the title of the movie in that year that has the highest number of rating score of 1?
Evidence: least number of movies refers to MIN(movie_release_year); highest rating score refers to MAX(SUM(movie_id) where rating_score = '1')


SQL: SELECT DISTINCT T1.movie_release_year, T1.movie_title FROM movies AS T1 INNER JOIN ratings AS T2 ON T1.movie_id = T2.movie_id WHERE T1.movie_release_year = ( SELECT movie_release_year FROM movies GROUP BY movie_release_year ORDER BY COUNT(movie_id) DESC LIMIT 1 ) AND T2.rating_score = 1

Database: beer_factory
Question: List the full name and phone number of male customers from Fair Oaks who are subscribed to the email list.
Evidence: full name = First, Last; male customers refers to Gender = 'M'; Fair Oaks refers to City = 'Fair Oaks'; subscribed to the email list refers to SubscribedToEmailList = 'TRUE';
SQL: SELECT First, Last, PhoneNumber FROM customers WHERE Gender = 'M' AND City = 'Fair Oaks' AND SubscribedToEmailList = 'TRUE'

Database: olympics
Question: List out all the medals won by Lee Chong Wei.
Evidence: medals refer to medal_id where medal_id <> 4;
SQL: SELECT DISTINCT T1.medal_name FROM medal AS T1 INNER JOIN competitor_event AS T2 ON T1.id = T2.medal_id INNER JOIN games_competitor AS T3 ON T2.competitor_id = T3.id INNER JOIN person AS T4 ON T3.person_id = T4.id WHERE T4.full_name = 'Lee Chong Wei' AND T2.medal_id <> 4

Database: world
Question: Calculate the average GNP of all countries that use Arabic language.
Evidence: average GNP = AVG(GNP); use Arabic language refers to Language = 'Arabic';
SQL: SELECT AVG(T1.GNP) FROM Country AS T1 INNER JOIN CountryLanguage AS T2 ON T1.Code = T2.CountryCode WHERE T2.Language = 'Arabic'

Database: cs_semester
Question: Among the students who got a B in the course Machine Learning Theory, how many of them have a gpa of over 3?
Evidence: B refers to grade; GPA is an abbreviated name of Grade Point Average where over 3 refers to gpa > 3;
SQL: SELECT COUNT(student_id) FROM registration WHERE grade = 'B' AND student_id IN ( SELECT student_id FROM student WHERE gpa > 3 AND course_id IN ( SELECT course_id FROM course WHERE name = 'Machine Learning Theory' ) )

Database: computer_student
Question: What is the average number of courses taught by a professor?
Evidence: professor refers to professor = 1; average number of courses = divide(count(taughtBy.course_id), count(taughtBy.p_id) where professor = 1 )
SQL: SELECT CAST(COUNT(T1.course_id) AS REAL) / COUNT(DISTINCT T2.p_id) AS num FROM taughtBy AS T1 INNER JOIN person AS T2 ON T1.p_id = T2.p_id WHERE T2.professor = 1

Database: cookbook
Question: How many ingredients are there in Apricot Yogurt Parfaits?
Evidence: Apricot Yogurt Parfaits refers to title
SQL: SELECT COUNT(*) FROM Recipe AS T1 INNER JOIN Quantity AS T2 ON T1.recipe_id = T2.recipe_id WHERE T1.title = 'Apricot Yogurt Parfaits'

Database: disney
Question: Provide the name of the song from the movie directed by Ben Sharpsteen.
Evidence: Ben Sharpsteen refers to director = 'Ben Sharpsteen';
SQL: SELECT T1.song FROM characters AS T1 INNER JOIN director AS T2 ON T1.movie_title = T2.name WHERE T2.director = 'Ben Sharpsteen'

Database: regional_sales
Question: Find the number of baseball ordered in December 2017.
Evidence: Product Name = 'Baseball'; December 2017 refers to OrderDate LIKE '12/%/17';
SQL: SELECT COUNT(T2.OrderNumber) FROM Products AS T1 INNER JOIN `Sales Orders` AS T2 ON T2._ProductID = T1.ProductID WHERE T1.`Product Name` = 'Baseball' AND T2.OrderDate LIKE '12/%/18'

Database: public_review_platform
Question: How many active businesses are located at Phoenix, Arizona?
Evidence: active business refers to active = 'true'; 'Phoenix' is the city
SQL: SELECT COUNT(business_id) FROM Business WHERE city LIKE 'Phoenix' AND active LIKE 'True'

Database: retail_complains
Question: When did the earliest complaint start on 2017/3/22?
Evidence: earliest complaint refers to oldest ser_start; on 2017/3/22 refers to "Date received" = '2017-03-22';
SQL: SELECT MIN(ser_time) FROM callcenterlogs WHERE `Date received` = '2017-03-22'

Database: public_review_platform
Question: What are the states of businesses with attribute of beer and wine located?
Evidence: with attribute of beer and wine refers to attribute_value = 'beer_and_wine';
SQL: SELECT DISTINCT T2.state FROM Business_Attributes AS T1 INNER JOIN Business AS T2 ON T1.business_id = T2.business_id WHERE T1.attribute_value = 'beer_and_wine'

Database: disney
Question: How much more total box office gross did the Walt Disney Company have in revenue in 1998 than in 1997?
Evidence: SUBTRACT(SUM(Year = 1998), SUM(Year = 1997))
SQL: SELECT SUM(CASE WHEN `Year` = 1998 THEN Total ELSE 0 END) - SUM(CASE WHEN `Year` = 1997 THEN Total ELSE 0 END) FROM revenue

Database: university
Question: What is the ID of university with the largest percentage of international students?
Evidence: largest percentage of international students refers to MAX(pct_international_students); ID of university refers to university_id
SQL: SELECT university_id FROM university_year ORDER BY pct_international_students DESC LIMIT 1

Database: movie_platform
Question: Between 1970 to 1980, how many movies with a popularity of more than 11,000 were released?
Evidence: Between 1970 to 1980 refers to movie_release_year between 1970 and 1980; popularity of more than 11,000 refers movie_popularity >11000
SQL: SELECT COUNT(movie_id) FROM movies WHERE movie_release_year BETWEEN '1970' AND '1980' AND movie_popularity > 11000