Database: music_platform_2
Question: Calculate the average rating of the true crime category.
Evidence: average rating = Divide (Sum(rating(category = 'true-crime')), Count(podcast_id(category = 'true-crime')))
SQL: SELECT AVG(T2.rating) FROM categories AS T1 INNER JOIN reviews AS T2 ON T2.podcast_id = T1.podcast_id WHERE T1.category = 'true-crime'

Database: chicago_crime
Question: What is the short description of the crime committed the most by criminals in the least populated community?
Evidence: short description refers to title; committed the most refers to max(fbi_code_no); the least populated community refers to min(population)
SQL: SELECT T3.title FROM Community_Area AS T1 INNER JOIN Crime AS T2 ON T1.community_area_no = T2.community_area_no INNER JOIN FBI_Code AS T3 ON T2.fbi_code_no = T3.fbi_code_no GROUP BY T3.title ORDER BY T1.population ASC, T3.fbi_code_no DESC LIMIT 1

Database: world
Question: List all the official and unofficial languages used by the country that declared its independence in 1830.
Evidence: official language refers to IsOfficial = 'T'; unofficial language refers to IsOfficial = 'F'; declared independence in 1830 refers to IndepYear = 1830;
SQL: SELECT T2.Language, T2.IsOfficial FROM Country AS T1 INNER JOIN CountryLanguage AS T2 ON T1.Code = T2.CountryCode WHERE T1.IndepYear = 1830 GROUP BY T2.Language, T2.IsOfficial

Database: video_games
Question: How many games do not have any sales in Europe?
Evidence: do not have any sales refers to num_sales = 0; in Europe refers to region_name = 'Europe'
SQL: SELECT COUNT(*) FROM region_sales AS T1 INNER JOIN region AS T2 ON T1.region_id = T2.id WHERE T2.region_name = 'Europe' AND T1.num_sales = 0

Database: cookbook
Question: How many recipes can be made with canned dairy?
Evidence: canned dairy is a category
SQL: SELECT COUNT(*) FROM Ingredient AS T1 INNER JOIN Quantity AS T2 ON T1.ingredient_id = T2.ingredient_id WHERE T1.category = 'canned dairy'

Database: movie_3
Question: What percentage of films with a length of less than 100 belong to the Drama category?
Evidence: Drama' is a name of a category; calculation = DIVIDE(SUM(length < 100 AND name = 'Drama'), COUNT(film_id)) * 100
SQL: SELECT CAST(SUM(IIF(T2.length < 100 AND T3.name = 'Drama', 1, 0)) AS REAL) * 100 / COUNT(T1.film_id) FROM film_category AS T1 INNER JOIN film AS T2 ON T1.film_id = T2.film_id INNER JOIN category AS T3 ON T1.category_id = T3.category_id

Database: law_episode
Question: Which episode has the highest total number of viewer votes?
Evidence: episode refers to title; the highest total number of viewer votes refers to max(sum(votes))
SQL: SELECT T1.title FROM Episode AS T1 INNER JOIN Vote AS T2 ON T1.episode_id = T2.episode_id GROUP BY T1.title ORDER BY SUM(T1.votes) DESC LIMIT 1

Database: books
Question: Who authored the book with greatest number of pages?
Evidence: greatest number of pages refers to Max(num_pages); who authored refers to author_name
SQL: SELECT T3.author_name FROM book AS T1 INNER JOIN book_author AS T2 ON T1.book_id = T2.book_id INNER JOIN author AS T3 ON T3.author_id = T2.author_id ORDER BY T1.num_pages DESC LIMIT 1

Database: language_corpus
Question: List down the revision page id of titles where "fresc" appears.
Evidence: page id refers to pid; "fresc" refers to word = 'fresc'
SQL: SELECT T3.revision 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 = 'fresc'

Database: student_loan
Question: What is the average of absence for an employed students?
Evidence: average refers to DIVIDE(SUM(month), COUNT(name))
SQL: SELECT AVG(month) FROM longest_absense_from_school WHERE name NOT IN ( SELECT name FROM unemployed )

Database: ice_hockey_draft
Question: How many players who were drafted by the Toronto Maple Leafs have played over 300 games in their first 7 years of the NHL career?
Evidence: drafted by the Toronto Maple Leafs refers to overallby = 'Toronto Maple Leafs'; played over 300 games in their first 7 years of the NHL career refers to sum_7yr_GP > 300;
SQL: SELECT COUNT(ELITEID) FROM PlayerInfo WHERE overallby = 'Toronto Maple Leafs' AND sum_7yr_GP > 300

Database: superstore
Question: What is the total quantity of "Telescoping Adjustable Floor Lamp" ordered from central superstores?
Evidence: "Telescoping Adjustable Floor Lamp" is a "Product Name"; from central superstores refers to Region = 'Central';
SQL: SELECT SUM(T1.Quantity) FROM central_superstore AS T1 INNER JOIN product AS T2 ON T1.`Product ID` = T2.`Product ID` WHERE T2.`Product Name` = 'Telescoping Adjustable Floor Lamp'

Database: movies_4
Question: For movies with the keyword of "civil war", calculate the average revenue generated by these movies.
Evidence: keyword of "civil war" refers to keyword_name = 'civil war'; average revenue = AVG(revenue)
SQL: SELECT AVG(T1.revenue) FROM movie AS T1 INNER JOIN movie_keywords AS T2 ON T1.movie_id = T2.movie_id INNER JOIN keyword AS T3 ON T2.keyword_id = T3.keyword_id WHERE T3.keyword_name = 'civil war'

Database: cs_semester
Question: Find the university from which the professor who advised most undergraduate students graduated.
Evidence: university from which the professor graduated refers to graduate_from; undergraduate students refers to type = 'UG';
SQL: SELECT T1.graduate_from 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.type = 'UG' GROUP BY T1.prof_id ORDER BY COUNT(T2.student_id) DESC LIMIT 1

Database: retail_world
Question: How many product names does the supplier Exotic Liquids have?
Evidence: 'Exotic Liquids' is a CompanyName
SQL: SELECT COUNT(T1.ProductName) FROM Products AS T1 INNER JOIN Suppliers AS T2 ON T1.SupplierID = T2.SupplierID WHERE T2.CompanyName = 'Exotic Liquids'

Database: retail_complains
Question: What is the social number of the client who has the longest delay in his/her complaint? Calculate the days of delay and state the company's response to the consumer.
Evidence: social number refers to social; longest delay = max(subtract(Date sent to company, Date received)); days of delay = subtract(Date sent to company, Date received); company's response refers to 'Company response to consumer'
SQL: SELECT T1.social , 365 * (strftime('%Y', T2.`Date sent to company`) - strftime('%Y', T2.`Date received`)) + 30 * (strftime('%M', T2.`Date sent to company`) - strftime('%M', T2.`Date received`)) + (strftime('%d', T2.`Date sent to company`) - strftime('%d', T2.`Date received`)), T2.`Company response to consumer` FROM client AS T1 INNER JOIN events AS T2 ON T1.client_id = T2.Client_ID ORDER BY 365 * (strftime('%Y', T2.`Date sent to company`) - strftime('%Y', T2.`Date received`)) + 30 * (strftime('%M', T2.`Date sent to company`) - strftime('%M', T2.`Date received`)) + (strftime('%d', T2.`Date sent to company`) - strftime('%d', T2.`Date received`)) DESC LIMIT 1

Database: car_retails
Question: List the name of employees in Japan office and who are they reporting to.
Evidence: Japan is the name of the country; 'reportsTO' is the leader of the 'employeeNumber';
SQL: SELECT t2.firstName, t2.lastName, t2.reportsTo FROM offices AS t1 INNER JOIN employees AS t2 ON t1.officeCode = t2.officeCode WHERE t1.country = 'Japan'

Database: olympics
Question: List the name of the games that Georgios Abaris participated.
Evidence: name of games refers to games_name;
SQL: SELECT T1.games_name FROM games AS T1 INNER JOIN games_competitor AS T2 ON T1.id = T2.games_id INNER JOIN person AS T3 ON T2.person_id = T3.id WHERE T3.full_name = 'Georgios Abaris'

Database: app_store
Question: Which apps have been reviewed more than 75 000 000 times and the content is suitable for teenagers?
Evidence: Reviews>75000000; suitable for teenagers refers to Content Rating = 'Teen';
SQL: SELECT DISTINCT App FROM playstore WHERE Reviews > 75000000 AND `Content Rating` = 'Teen'