Database: superhero
Question: What is the average of superheroes with no skin colour?
Evidence: average = DIVIDE(COUNT(superhero.id), SUM(skin_colour_id = 1)); no skin colour refers to skin_colour_id WHERE colour.id = 1;
SQL: SELECT CAST(COUNT(*) AS REAL) / SUM(CASE WHEN T2.id = 1 THEN 1 ELSE 0 END) FROM superhero AS T1 INNER JOIN colour AS T2 ON T1.skin_colour_id = T2.id

Database: student_club
Question: List the last names of students under the Elementary Education major and include their phone numbers.
Evidence: 'Elementary Education' is the major name; phone numbers refers to phone
SQL: SELECT T1.last_name, T1.phone FROM member AS T1 INNER JOIN major AS T2 ON T2.major_id = T1.link_to_major WHERE T2.major_name = 'Elementary Education'

Database: codebase_community
Question: Please list the users' display names whose posts had over 20000 views in 2011.
Evidence: had over 20000 views in 2011 refers to ViewCount > 20000 where YEAR(CreationDate) = 2011;
SQL: SELECT T1.DisplayName FROM users AS T1 INNER JOIN posts AS T2 ON T1.Id = T2.OwnerUserId WHERE STRFTIME('%Y', T2.CreaionDate) = '2011' AND T2.ViewCount > 20000

Database: financial
Question: List out the id number of client who choose statement of issuance after transaction are Disponent?
Evidence: 'POPLATEK PO OBRATU' stands for issuance after transaction
SQL: SELECT T2.client_id FROM account AS T1 INNER JOIN disp AS T2 ON T1.account_id = T2.account_id WHERE T1.frequency = 'POPLATEK PO OBRATU' AND T2.type = 'DISPONENT'

Database: toxicology
Question: Among the molecules which contain "cl" element, which of them are carcinogenic?
Evidence: label = '+' mean molecules are carcinogenic;
SQL: SELECT DISTINCT T1.molecule_id FROM atom AS T1 INNER JOIN molecule AS T2 ON T1.molecule_id = T2.molecule_id WHERE T1.element = 'cl' AND T2.label = '+'

Database: card_games
Question: What are the cards for set OGW? State the colour for these cards.
Evidence: set OGW refers to setCode = 'OGW';
SQL: SELECT id, colors FROM cards WHERE id IN ( SELECT id FROM set_translations WHERE setCode = 'OGW' )

Database: card_games
Question: Name the foreign name of the card that has abzan watermark? List out the type of this card.
SQL: SELECT DISTINCT T1.name, T1.type FROM cards AS T1 INNER JOIN foreign_data AS T2 ON T2.uuid = T1.uuid WHERE T1.watermark = 'abzan'

Database: financial
Question: What is the district Id of the account that placed the order with the id 33333?
SQL: SELECT T3.district_id FROM `order` AS T1 INNER JOIN account AS T2 ON T1.account_id = T2.account_id INNER JOIN district AS T3 ON T2.district_id = T3.district_id WHERE T1.order_id = 33333

Database: student_club
Question: How many students in the Student_Club are from the College of Engineering?
SQL: SELECT COUNT(T1.member_id) FROM member AS T1 INNER JOIN major AS T2 ON T1.link_to_major = T2.major_id WHERE T2.college = 'College of Engineering'

Database: formula_1
Question: List all races in 2017 and the hosting country order by date of the event.
SQL: SELECT DISTINCT T2.name, T1.country FROM circuits AS T1 INNER JOIN races AS T2 ON T2.circuitID = T1.circuitId WHERE T2.year = 2017 ORDER BY T2.date ASC

Database: formula_1
Question: Please list all the years that Silverstone Circuit was used in a Formula_1 race.
SQL: SELECT DISTINCT T2.year FROM circuits AS T1 INNER JOIN races AS T2 ON T2.circuitID = T1.circuitId WHERE T1.name = 'Silverstone Circuit'

Database: superhero
Question: Name the superhero, otherwise known as Charles Chandler.
Evidence: name the superhero refers to superhero_name; Charles Chandler is the full name of superhero;
SQL: SELECT superhero_name FROM superhero WHERE full_name = 'Charles Chandler'

Database: california_schools
Question: Which county reported the most number of school closure in the 1980s with school wonership code belonging to Youth Authority Facilities (CEA)?
Evidence: Youth Authority Facilities (CEA) refers to SOC = 11; 1980s = years between 1980 and 1989
SQL: SELECT County FROM schools WHERE strftime('%Y', ClosedDate) BETWEEN '1980' AND '1989' AND StatusType = 'Closed' AND SOC = 11 GROUP BY County ORDER BY COUNT(School) DESC LIMIT 1

Database: card_games
Question: Who is the illustrator that illustrated the least amount of cards? List the format of play of the cards that he/she illustrated.
Evidence: format of the cards refers to format; illustrator refers to artist; the least amount of cards refers to MIN(artist)
SQL: SELECT T1.artist, T2.format FROM cards AS T1 INNER JOIN legalities AS T2 ON T2.uuid = T1.uuid GROUP BY T1.artist ORDER BY COUNT(T1.id) ASC LIMIT 1

Database: toxicology
Question: What are the bond type and the atoms of the bond ID of TR001_6_9?
Evidence: atoms refer to atom_id or atom_id2
SQL: SELECT T1.bond_type, T2.atom_id, T2.atom_id2 FROM bond AS T1 INNER JOIN connected AS T2 ON T1.bond_id = T2.bond_id WHERE T2.bond_id = 'TR001_6_9'

Database: superhero
Question: List the eyes, hair and skin colour of all female superheroes published by Dark Horse Comics.
Evidence: eyes refers to eye_colour_id; hair refers to hair_colour_id; skin colour refers to skin_colour_id; female superheroes refers to gender = 'Female'; published by Dark Horse Comics refers to publisher_name = 'Dark Horse Comics';
SQL: SELECT T1.eye_colour_id, T1.hair_colour_id, T1.skin_colour_id FROM superhero AS T1 INNER JOIN publisher AS T2 ON T2.id = T1.publisher_id INNER JOIN gender AS T3 ON T3.id = T1.gender_id WHERE T2.publisher_name = 'Dark Horse Comics' AND T3.gender = 'Female'

Database: card_games
Question: List down the name of cards with original types of Creature - Elf and the date of rulings for these cards.
Evidence: Creature - Elf is the originalType;
SQL: SELECT T1.id, T2.date FROM cards AS T1 INNER JOIN rulings AS T2 ON T1.uuid = T2.uuid WHERE T1.originalType = 'Creature - Elf'

Database: european_football_2
Question: What is the height of the tallest player? Indicate his name.
Evidence: tallest player refers to MAX(height);
SQL: SELECT player_name FROM Player ORDER BY height DESC LIMIT 1

Database: california_schools
Question: How many chartered schools located in the city of Hickman are owned by the Elementary School District?
Evidence: Elementary School District refers to DOC = 52; Chartered schools refer to Charter = 1 in the table schools
SQL: SELECT COUNT(School) FROM schools WHERE DOC = 52 AND Charter = 1 AND City = 'Hickman'

Database: financial
Question: List the top nine districts, by descending order, from the highest to the lowest, the number of female account holders.
Evidence: A2 refers to districts; Female refers to gender = 'F'
SQL: SELECT T2.A2, COUNT(T1.client_id) FROM client AS T1 INNER JOIN district AS T2 ON T1.district_id = T2.district_id WHERE T1.gender = 'F' GROUP BY T2.district_id, T2.A2 ORDER BY COUNT(T1.client_id) DESC LIMIT 9

Database: debit_card_specializing
Question: What is the percentage of the customers who used EUR in 2012/8/25?
Evidence: '2012/8/25' can be represented by '2012-08-25'
SQL: SELECT CAST(SUM(IIF(T2.Currency = 'EUR', 1, 0)) AS FLOAT) * 100 / COUNT(T1.CustomerID) FROM transactions_1k AS T1 INNER JOIN customers AS T2 ON T1.CustomerID = T2.CustomerID WHERE T1.Date = '2012-08-25'

Database: california_schools
Question: When did the first-through-twelfth-grade school with the largest enrollment open?
Evidence: K-12 means First-through-twelfth-grade
SQL: SELECT T2.OpenDate FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode ORDER BY T1.`Enrollment (K-12)` DESC LIMIT 1

Database: debit_card_specializing
Question: What is the biggest monthly consumption of the customers who use euro as their currency?
Evidence: Monthly consumption = SUM(consumption) / 12
SQL: SELECT SUM(T2.Consumption) / 12 AS MonthlyConsumption FROM customers AS T1 INNER JOIN yearmonth AS T2 ON T1.CustomerID = T2.CustomerID WHERE T1.Currency = 'EUR' GROUP BY T1.CustomerID ORDER BY MonthlyConsumption DESC LIMIT 1