| 20 |
How many schools in Amador which the Low Grade is 9 and the High Grade is 12? |
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 |
SELECT COUNT(School) FROM schools WHERE County = 'Amador' AND GSoffered = '9-12' |
0 |
0 |
| 21 |
In Los Angeles how many schools have more than 500 free meals but less than 700 free or reduced price meals for K-12? |
SELECT COUNT(CDSCode) FROM frpm WHERE County Name = 'Los Angeles' AND Free Meal Count (K-12) > 500 AND FRPM Count (K-12) < 700 |
SELECT count(DISTINCT T1.School Name) FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.City = 'Los Angeles' AND T1.Free Meal Count (K-12) > 500 AND T1.FRPM Count (K-12) < 700 |
0 |
0 |
| 22 |
Which school in Contra Costa has the highest number of test takers? |
SELECT sname FROM satscores WHERE cname = 'Contra Costa' AND NOT sname IS NULL ORDER BY NumTstTakr DESC LIMIT 1 |
SELECT T2.sname, SUM(T2.NumTstTakr) AS TotalTestTakers FROM schools AS T1 JOIN satscores AS T2 ON T1.CDSCode = T2.cds WHERE T2.cname = 'Contra Costa' GROUP BY T2.sname ORDER BY TotalTestTakers DESC LIMIT 1 |
0 |
0 |
| 23 |
List the names of schools with more than 30 difference in enrollements between K-12 and ages 5-17? Please also give the full street adress of the schools. Diffrence in enrollement = Enrollment (K-12) - Enrollment (Ages 5-17) |
SELECT T1.School, T1.Street FROM schools AS T1 INNER JOIN frpm AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.Enrollment (K-12) - T2.Enrollment (Ages 5-17) > 30 |
SELECT f.School Name, CONCAT(s.Street, ', ', s.City, ', ', s.State, ' ', s.Zip) AS FullStreetAddress FROM frpm AS f JOIN schools AS s ON f.CDSCode = s.CDSCode AND f.School Name = s.School WHERE (f.Enrollment (K-12) - f.Enrollment (Ages 5-17)) > 30 |
0 |
0 |
| 24 |
Give the names of the schools with the percent eligible for free meals in K-12 is more than 0.1 and test takers whose test score is greater than or equal to 1500? Percent eligible for free meals = Free Meal Count (K-12) / Total (Enrollment (K-12) |
SELECT T2.School Name FROM satscores AS T1 INNER JOIN frpm AS T2 ON T1.cds = T2.CDSCode WHERE CAST(T2.Free Meal Count (K-12) AS FLOAT) / T2.Enrollment (K-12) > 0.1 AND T1.NumGE1500 > 0 |
SELECT T1.School Name FROM frpm AS T1 INNER JOIN satscores AS T2 ON T1.CDSCode = T2.cds WHERE (T1.Free Meal Count (K-12) / T1.Enrollment (K-12)) > 0.1 AND T2.NumGE1500 > 0 |
0.990657 |
0 |
| 25 |
Name schools in Riverside which the average of average math score for SAT is grater than 400, what is the funding type of these schools? Average of average math = sum(average math scores) / count(schools). |
SELECT T1.sname, T2.Charter Funding Type FROM satscores AS T1 INNER JOIN frpm AS T2 ON T1.cds = T2.CDSCode WHERE T2.District Name LIKE 'Riverside%' GROUP BY T1.sname, T2.Charter Funding Type HAVING CAST(SUM(T1.AvgScrMath) AS FLOAT) / COUNT(T1.cds) > 400 |
SELECT s.School, s.FundingType FROM schools AS s JOIN satscores AS sat ON s.School = sat.sname AND s.County = sat.cname WHERE s.County = 'Riverside' GROUP BY s.School, s.FundingType HAVING AVG(sat.AvgScrMath) > 400 |
0.0155288 |
0 |
| 26 |
State the names and full communication address of high schools in Monterey which has more than 800 free or reduced price meals for ages 15-17? Full communication address should include Street, City, State and zip code if any. |
SELECT T1.School Name, T2.Street, T2.City, T2.State, T2.Zip FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.County = 'Monterey' AND T1.Free Meal Count (Ages 5-17) > 800 AND T1.School Type = 'High Schools (Public)' |
SELECT T1.School, T1.Street, T1.City, T1.State, T1.Zip FROM schools AS T1 JOIN frpm AS T2 ON T1.CDSCode = T2.CDSCode AND T1.School = T2.School Name WHERE T1.City = 'Monterey' AND T2.High Grade = 12 AND T2.FRPM Count (Ages 5-17) > 800 |
0 |
0 |
| 27 |
What is the average score in writing for the schools that were opened after 1991 or closed before 2000? List the school names along with the score. Also, list the communication number of the schools if there is any. Communication number refers to phone number. |
SELECT T2.School, T1.AvgScrWrite, T2.Phone FROM schools AS T2 LEFT JOIN satscores AS T1 ON T2.CDSCode = T1.cds WHERE DATE_FORMAT(CAST(T2.OpenDate AS TIMESTAMP), 'yyyy') > '1991' OR DATE_FORMAT(CAST(T2.ClosedDate AS TIMESTAMP), 'yyyy') < '2000' |
SELECT s.School, sa.AvgScrWrite, s.Phone FROM schools AS s JOIN satscores AS sa ON s.CDSCode = sa.cds WHERE CAST(SUBSTRING(s.OpenDate, 1, 4) AS INT) > 1991 OR CAST(SUBSTRING(s.ClosedDate, 1, 4) AS INT) < 2000 |
0.0521004 |
0 |
| 28 |
Consider the average difference between K-12 enrollment and 15-17 enrollment of schools that are locally funded, list the names and DOC type of schools which has a difference above this average. Difference between K-12 enrollment and 15-17 enrollment can be computed by Enrollment (K-12) - Enrollment (Ages 5-17) |
SELECT T2.School, T2.DOC FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.FundingType = 'Locally funded' AND (T1.Enrollment (K-12) - T1.Enrollment (Ages 5-17)) > (SELECT AVG(T3.Enrollment (K-12) - T3.Enrollment (Ages 5-17)) FROM frpm AS T3 INNER JOIN schools AS T4 ON T3.CDSCode = T4.CDSCode WHERE T4.FundingType = 'Locally funded') |
SELECT DISTINCT FundingType FROM schools |
0 |
0 |
| 29 |
When did the first-through-twelfth-grade school with the largest enrollment open? K-12 means First-through-twelfth-grade |
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 |
SELECT T1.OpenDate FROM schools AS T1 JOIN frpm AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.Low Grade = 'K' AND T2.High Grade = '12' ORDER BY T2.Enrollment (K-12) DESC LIMIT 1 |
1 |
0 |
| 30 |
Which cities have the top 5 lowest enrollment number for students in grades 1 through 12? K-12 refers to students in grades 1 through 12. |
SELECT T2.City FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode GROUP BY T2.City ORDER BY SUM(T1.Enrollment (K-12)) ASC LIMIT 5 |
SELECT T2.City, SUM(T1.Enrollment (K-12)) AS TotalEnrollment FROM frpm AS T1 JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE CAST(T1.Low Grade AS INT) <= 1 AND CAST(T1.High Grade AS INT) >= 12 GROUP BY T2.City ORDER BY TotalEnrollment ASC LIMIT 5 |
0 |
0 |
| 31 |
What is the eligible free rate of the 10th and 11th schools with the highest enrolment for students in grades 1 through 12? K-12 refers to students in grades 1 through 12; Eligible free rate for K-12 = Free Meal Count (K-12) / Enrollment (K-12) |
SELECT CAST(Free Meal Count (K-12) AS FLOAT) / Enrollment (K-12) FROM frpm ORDER BY Enrollment (K-12) DESC LIMIT 2 OFFSET 9 |
SELECT School Name, (Free Meal Count (K-12) / Enrollment (K-12)) AS eligible_free_rate FROM frpm WHERE Enrollment (K-12) IS NOT NULL AND Enrollment (K-12) > 0 ORDER BY Enrollment (K-12) DESC OFFSET 9 LIMIT 2 |
0 |
0 |
| 32 |
What is the eligible free or reduced price meal rate for the top 5 schools in grades 1-12 with the highest free or reduced price meal count of the schools with the ownership code 66? grades 1-12 means K-12; Eligible free or reduced price meal rate for K-12 = FRPM Count (K-12) / Enrollment (K-12) |
SELECT CAST(T1.FRPM Count (K-12) AS FLOAT) / T1.Enrollment (K-12) FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.SOC = 66 ORDER BY T1.FRPM Count (K-12) DESC LIMIT 5 |
SELECT T1.School Name, (T1.FRPM Count (K-12) / T1.Enrollment (K-12)) AS Eligible Free or Reduced Price Meal Rate FROM frpm AS T1 JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.SOC = '66' ORDER BY T1.FRPM Count (K-12) DESC LIMIT 5 |
0 |
0 |
| 33 |
If there are any, what are the websites address of the schools with a free meal count of 1,900-2,000 to students aged 5-17? Include the name of the school. |
SELECT T2.Website, T1.School Name FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T1.Free Meal Count (Ages 5-17) BETWEEN 1900 AND 2000 AND NOT T2.Website IS NULL |
SELECT s.School, s.Website FROM frpm AS f JOIN schools AS s ON f.CDSCode = s.CDSCode WHERE f.Free Meal Count (Ages 5-17) >= 1900 AND f.Free Meal Count (Ages 5-17) <= 2000 |
0 |
0 |
| 34 |
What is the free rate for students between the ages of 5 and 17 at the school run by Kacey Gibson? Eligible free rates for students aged 5-17 = Free Meal Count (Ages 5-17) / Enrollment (Ages 5-17) |
SELECT CAST(T2.Free Meal Count (Ages 5-17) AS FLOAT) / T2.Enrollment (Ages 5-17) FROM schools AS T1 INNER JOIN frpm AS T2 ON T1.CDSCode = T2.CDSCode WHERE T1.AdmFName1 = 'Kacey' AND T1.AdmLName1 = 'Gibson' |
SELECT T1.Free Meal Count (Ages 5-17) / T1.Enrollment (Ages 5-17) FROM frpm AS T1 JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.AdmFName1 = 'Kacey' AND T2.AdmLName1 = 'Gibson' |
1 |
0 |
| 35 |
What is the administrator's email address of the chartered school with the fewest students enrolled in grades 1 through 12? Charted school means Charter School (Y/N) = 1 in the table frpm; Students enrolled in grades 1 through 12 refers to Enrollment (K-12) |
SELECT T2.AdmEmail1 FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T1.Charter School (Y/N) = 1 ORDER BY T1.Enrollment (K-12) ASC LIMIT 1 |
SELECT T2.AdmEmail1 FROM frpm AS T1 JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T1.Charter School (Y/N) = 1 ORDER BY T1.Enrollment (K-12) ASC LIMIT 1 |
1 |
1 |
| 36 |
Under whose administration is the school with the highest number of students scoring 1500 or more on the SAT? Indicate their full names. full name means first name, last name; There are at most 3 administrators for each school; SAT Scores are greater or equal to 1500 refers to NumGE1500 |
SELECT T2.AdmFName1, T2.AdmLName1, T2.AdmFName2, T2.AdmLName2, T2.AdmFName3, T2.AdmLName3 FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode ORDER BY T1.NumGE1500 DESC LIMIT 1 |
SELECT CONCAT_WS(' ', s.administrator_first_name_1, s.administrator_last_name_1) AS administrator_1_full_name, CONCAT_WS(' ', s.administrator_first_name_2, s.administrator_last_name_2) AS administrator_2_full_name, CONCAT_WS(' ', s.administrator_first_name_3, s.administrator_last_name_3) AS administrator_3_full_name FROM schools AS s JOIN satscores AS ss ON s.school_id = ss.school_id ORDER BY ss.NumGE1500 DESC LIMIT 1 |
0 |
0.125 |
| 37 |
What is the complete address of the school with the lowest excellence rate? Indicate the Street, City, Zip and State. Execellence Rate = NumGE1500 / NumTstTakr; complete address has Street, City, State, Zip code |
SELECT T2.Street, T2.City, T2.State, T2.Zip FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode ORDER BY CAST(T1.NumGE1500 AS FLOAT) / T1.NumTstTakr ASC LIMIT 1 |
SELECT s.Street, s.City, s.Zip, s.State FROM schools AS s JOIN satscores AS sa ON s.CDSCode = sa.cds WHERE sa.NumTstTakr IS NOT NULL AND sa.NumTstTakr > 0 ORDER BY (CAST(sa.NumGE1500 AS DOUBLE) / sa.NumTstTakr) ASC LIMIT 1; |
0 |
0 |
| 38 |
What are the webpages for the Los Angeles County school that has between 2,000 and 3,000 test takers? |
SELECT T2.Website FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE T1.NumTstTakr BETWEEN 2000 AND 3000 AND T2.County = 'Los Angeles' |
SELECT T1.Website FROM schools AS T1 INNER JOIN satscores AS T2 ON T1.CDSCode = T2.cds WHERE T1.County = 'Los Angeles' AND T2.NumTstTakr BETWEEN 2000 AND 3000 |
0.9 |
0.9 |
| 39 |
What is the average number of test takers from Fresno schools that opened between 1/1/1980 and 12/31/1980? between 1/1/1980 and 12/31/1980 means the year = 1980 |
SELECT AVG(T1.NumTstTakr) FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE DATE_FORMAT(CAST(T2.OpenDate AS TIMESTAMP), 'yyyy') = '1980' AND T2.County = 'Fresno' |
SELECT AVG(T2.NumTstTakr) FROM schools AS T1 INNER JOIN satscores AS T2 ON T1.CDSCode = T2.cds WHERE T1.City = 'Fresno' AND YEAR(T1.OpenDate) = 1980 |
0 |
0 |
| 40 |
What is the telephone number for the school with the lowest average score in reading in Fresno Unified? Fresno Unified is a name of district; |
SELECT T2.Phone FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE T2.District = 'Fresno Unified' AND NOT T1.AvgScrRead IS NULL ORDER BY T1.AvgScrRead ASC LIMIT 1 |
SELECT T1.Phone FROM schools AS T1 JOIN satscores AS T2 ON T1.CDSCode = T2.cds WHERE T1.District = 'Fresno Unified' ORDER BY T2.AvgScrRead ASC LIMIT 1 |
0 |
0 |
| 41 |
List the names of virtual schools that are among the top 5 in their respective counties based on average reading scores. Exclusively virtual refers to Virtual = 'F'; respective counties means PARTITION BY County |
SELECT School FROM (SELECT T2.School, T1.AvgScrRead, RANK() OVER (PARTITION BY T2.County ORDER BY T1.AvgScrRead DESC) AS rnk FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE T2.Virtual = 'F') AS ranked_schools WHERE rnk <= 5 |
SELECT T1.School FROM schools AS T1 JOIN satscores AS T2 ON T1.CDSCode = T2.cds WHERE T1.Virtual = 'F' QUALIFY ROW_NUMBER() OVER (PARTITION BY T1.County ORDER BY T2.AvgScrRead DESC) <= 5 |
0 |
0 |
| 42 |
What is the type of education offered in the school who scored the highest average in Math? |
SELECT T2.EdOpsName FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode ORDER BY T1.AvgScrMath DESC LIMIT 1 |
SELECT T1.GSoffered FROM schools AS T1 JOIN satscores AS T2 ON T1.CDSCode = T2.cds GROUP BY T1.School, T1.GSoffered ORDER BY AVG(T2.AvgScrMath) DESC LIMIT 1 |
0 |
0 |
| 43 |
What is the average math score of the school with the lowest average score for all subjects, and in which county is it located? Average score for all subjects can be computed by AvgScrMath + AvgScrRead + AvgScrWrite |
SELECT T1.AvgScrMath, T2.County FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE NOT T1.AvgScrMath IS NULL ORDER BY T1.AvgScrMath + T1.AvgScrRead + T1.AvgScrWrite ASC LIMIT 1 |
SELECT T1.AvgScrMath, T1.cname FROM satscores AS T1 ORDER BY (T1.AvgScrMath + T1.AvgScrRead + T1.AvgScrWrite) ASC LIMIT 1 |
0 |
0 |
| 44 |
What is the average writing score of the school who has the highest number of test takers whose total SAT sscores are greater or equal to 1500? Indicate the city to where the school is situated. |
SELECT T1.AvgScrWrite, T2.City FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode ORDER BY T1.NumGE1500 DESC LIMIT 1 |
SELECT s.AvgScrWrite, sch.City FROM satscores AS s JOIN schools AS sch ON s.cds = sch.CDSCode ORDER BY s.NumGE1500 DESC LIMIT 1; |
0.8 |
0.5 |
| 45 |
What is the average writing score of each of the schools managed by Ricci Ulrich? List the schools and the corresponding average writing scores. Usually, administrators manage the school stuff. |
SELECT T2.School, T1.AvgScrWrite FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode WHERE T2.AdmFName1 = 'Ricci' AND T2.AdmLName1 = 'Ulrich' |
SELECT s.School, AVG(sat.AvgScrWrite) AS AverageWritingScore FROM schools AS s JOIN satscores AS sat ON s.CDSCode = sat.cds WHERE (s.AdmFName1 = 'Ricci' AND s.AdmLName1 = 'Ulrich') OR (s.AdmFName2 = 'Ricci' AND s.AdmLName2 = 'Ulrich') OR (s.AdmFName3 = 'Ricci' AND s.AdmLName3 = 'Ulrich') GROUP BY s.School ORDER BY AverageWritingScore DESC |
1 |
0.1 |