Database: california_schools
Question: What is the average number of test takers from Fresno schools that opened between 1/1/1980 and 12/31/1980?
Evidence: between 1/1/1980 and 12/31/1980 means the year = 1980
SQL: SELECT AVG(T1.NumTstTakr) FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE strftime('%Y', T2.OpenDate) = '1980' AND T2.County = 'Fresno'

Database: financial
Question: The average unemployment ratio of 1995 and 1996, which one has higher percentage?
Evidence: A12 refers to unemploymant rate 1995; A13 refers to unemploymant rate 1996
SQL: SELECT DISTINCT IIF(AVG(A13) > AVG(A12), '1996', '1995') FROM district

Database: california_schools
Question: What is the telephone number for the school with the lowest average score in reading in Fresno Unified?
Evidence: Fresno Unified is a name of district;
SQL: SELECT T2.Phone FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE T2.District = 'Fresno Unified' AND T1.AvgScrRead IS NOT NULL ORDER BY T1.AvgScrRead ASC LIMIT 1

Database: financial
Question: How many accounts who choose issuance after transaction are staying in East Bohemia region?
Evidence: A3 contains the data of region; 'POPLATEK PO OBRATU' represents for 'issuance after transaction'.
SQL: SELECT COUNT(T2.account_id) FROM district AS T1 INNER JOIN account AS T2 ON T1.district_id = T2.district_id WHERE T1.A3 = 'east Bohemia' AND T2.frequency = 'POPLATEK PO OBRATU'

Database: california_schools
Question: How many schools with an average score in Math greater than 400 in the SAT test are exclusively virtual?
Evidence: Exclusively virtual refers to Virtual = 'F'
SQL: SELECT COUNT(DISTINCT T2.School) FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE T2.Virtual = 'F' AND T1.AvgScrMath > 400

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: california_schools
Question: Please list the lowest three eligible free rates for students aged 5-17 in continuation schools.
Evidence: Eligible free rates for students aged 5-17 = `Free Meal Count (Ages 5-17)` / `Enrollment (Ages 5-17)`
SQL: SELECT `Free Meal Count (Ages 5-17)` / `Enrollment (Ages 5-17)` FROM frpm WHERE `Educational Option Type` = 'Continuation School' AND `Free Meal Count (Ages 5-17)` / `Enrollment (Ages 5-17)` IS NOT NULL ORDER BY `Free Meal Count (Ages 5-17)` / `Enrollment (Ages 5-17)` ASC LIMIT 3

Database: financial
Question: List out the account numbers of clients who are youngest and have highest average salary?
Evidence: If the person A's birthdate < B's birthdate, it means that person B is younger than person A; A11 refers to average salary
SQL: SELECT T1.account_id  FROM account AS T1 INNER JOIN disp AS T2 ON T1.account_id = T2.account_id INNER JOIN client AS T3 ON T2.client_id = T3.client_id INNER JOIN district AS T4 on T4.district_id = T1.district_id WHERE T2.client_id = ( SELECT client_id FROM client ORDER BY birth_date DESC LIMIT 1) GROUP BY T4.A11, T1.account_id

Database: california_schools
Question: What are the two most common first names among the school administrators? Indicate the district to which they administer.
SQL: SELECT DISTINCT T1.AdmFName1, T1.District FROM schools AS T1 INNER JOIN ( SELECT admfname1 FROM schools GROUP BY admfname1 ORDER BY COUNT(admfname1) DESC LIMIT 2 ) AS T2 ON T1.AdmFName1 = T2.admfname1

Database: california_schools
Question: In chartered schools with charter number 00D2, what are the names of all the administrators? Include the name of the school and the city to which it belongs
Evidence: Chartered schools refer to Charter = 1 in the table schools; Full name refers to first name, last name
SQL: SELECT AdmFName1, AdmLName1, School, City FROM schools WHERE Charter = 1 AND CharterNum = '00D2'

Database: california_schools
Question: What is the unabbreviated mailing street address of the school with the highest FRPM count for K-12 students?
SQL: SELECT T2.MailStreet FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode ORDER BY T1.`FRPM Count (K-12)` DESC LIMIT 1

Database: california_schools
Question: Of the schools that offers a magnet program serving a grade span of Kindergarten to 8th grade, how many offers Multiple Provision Types? List the number of cities that offers a Kindergarten to 8th grade span and indicate how many schools are there serving such grade span for each city.
Evidence: Kindergarten to 8th grade refers to K-8; 'Offers a magnet program' means Magnet = 1; Multiple Provision Types refers to `NSLP Provision Status` = 'Multiple Provision Types'
SQL: SELECT T2.City, COUNT(T2.CDSCode) FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.Magnet = 1 AND T2.GSoffered = 'K-8' AND T1.`NSLP Provision Status` = 'Multiple Provision Types' GROUP BY T2.City

Database: financial
Question: How many male customers who are living in North Bohemia have average salary greater than 8000?
Evidence: Male means that gender = 'M'; A3 refers to region; A11 pertains to average salary.
SQL: SELECT COUNT(T1.client_id) FROM client AS T1 INNER JOIN district AS T2 ON T1.district_id = T2.district_id WHERE T1.gender = 'M' AND T2.A3 = 'north Bohemia' AND T2.A11 > 8000

Database: california_schools
Question: How many schools in Amador which the Low Grade is 9 and the High Grade is 12?
SQL: SELECT COUNT(T1.`School Name`) FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.County = 'Amador' AND T1.`Low Grade` = 9 AND T1.`High Grade` = 12

Database: california_schools
Question: What are the valid e-mail addresses of the administrator of the school located in the San Bernardino county, City of San Bernardino City Unified that opened between 1/1/2009 to 12/31/2010 whose school types are public Intermediate/Middle Schools and Unified Schools?
Evidence: Intermediate/Middle Schools refers to SOC = 62; Unified School refers to DOC = 54; years between 2009 and 2010 can refer to 'between 1/1/2009 to 12/31/2010'
SQL: SELECT T2.AdmEmail1, T2.AdmEmail2 FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.County = 'San Bernardino' AND T2.City = 'San Bernardino' AND T2.DOC = 54 AND strftime('%Y', T2.OpenDate) BETWEEN '2009' AND '2010' AND T2.SOC = 62

Database: california_schools
Question: What is the average math score of the school with the lowest average score for all subjects, and in which county is it located?
Evidence: Average score for all subjects can be computed by AvgScrMath + AvgScrRead + AvgScrWrite
SQL: SELECT T1.AvgScrMath, T2.County FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE T1.AvgScrMath IS NOT NULL ORDER BY T1.AvgScrMath + T1.AvgScrRead + T1.AvgScrWrite ASC LIMIT 1

Database: california_schools
Question: What is the free or reduced price meal count for ages 5 to 17 in the Youth Authority School with a mailing street address of PO Box 1040?
SQL: SELECT T1.`FRPM Count (Ages 5-17)` FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.MailStreet = 'PO Box 1040' AND T2.SOCType = 'Youth Authority Facilities'