Database: school_finance

CREATE TABLE "School" (
"School_id" text,
"School_name" text,
"Location" text,
"Mascot" text,
"Enrollment" int,
"IHSAA_Class" text,
"IHSAA_Football_Class" text,
"County" text,
PRIMARY KEY ("School_id")
);
CREATE TABLE "budget" (
"School_id" int,
"Year" int,
"Budgeted" int,
"total_budget_percent_budgeted" real,
"Invested" int,
"total_budget_percent_invested" real,
"Budget_invested_percent" text,
PRIMARY KEY("School_id","YEAR"),
FOREIGN KEY("School_id") REFERENCES "School"("School_id")

);
CREATE TABLE "endowment" (
"endowment_id" int,
"School_id" int,
"donator_name" text,
"amount" real,
PRIMARY KEY("endowment_id"),
FOREIGN KEY("School_id") REFERENCES "School"("School_id")
);

Question: How many schools are there?
SQL: SELECT count(*) FROM school

Question: Count the number of schools.
SQL: SELECT count(*) FROM school

Question: Show all school names in alphabetical order.
SQL: SELECT school_name FROM school ORDER BY school_name

Question: List the name, location, mascot for all schools.
SQL: SELECT school_name ,  LOCATION ,  mascot FROM school

Question: What are the total and average enrollment of all schools?
SQL: SELECT sum(enrollment) ,  avg(enrollment) FROM school

Question: What are the mascots for schools with enrollments above the average?
SQL: SELECT mascot FROM school WHERE enrollment  >  (SELECT avg(enrollment) FROM school)

Question: List the name of the school with the smallest enrollment.
SQL: SELECT school_name FROM school ORDER BY enrollment LIMIT 1

Question: Show the average, maximum, minimum enrollment of all schools.
SQL: SELECT avg(enrollment) ,  max(enrollment) ,  min(enrollment) FROM school

Question: Show each county along with the number of schools and total enrollment in each county.
SQL: SELECT county ,  count(*) ,  sum(enrollment) FROM school GROUP BY county

Question: How many donors have endowment for school named "Glenn"?
SQL: SELECT count(DISTINCT T1.donator_name) FROM endowment AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id WHERE T2.school_name  =  "Glenn"

Question: List each donator name and the amount of endowment in descending order of the amount of endowment.
SQL: SELECT donator_name ,  sum(amount) FROM endowment GROUP BY donator_name ORDER BY sum(amount) DESC

Question: List the names of the schools without any endowment.
SQL: SELECT school_name FROM school WHERE school_id NOT IN (SELECT school_id FROM endowment)

Question: List all the names of schools with an endowment amount smaller than or equal to 10.
SQL: SELECT T2.school_name FROM endowment AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id GROUP BY T1.school_id HAVING sum(T1.amount)  <=  10

Question: Show the names of donors who donated to both school "Glenn" and "Triton."
SQL: SELECT T1.donator_name FROM endowment AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id WHERE T2.school_name  =  'Glenn' INTERSECT SELECT T1.donator_name FROM endowment AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id WHERE T2.school_name  =  'Triton'

Question: Show the names of all the donors except those whose donation amount less than 9.
SQL: SELECT donator_name FROM endowment EXCEPT SELECT donator_name FROM endowment WHERE amount  <  9

Question: List the amount and donor name for the largest amount of donation.
SQL: SELECT amount ,  donator_name FROM endowment ORDER BY amount DESC LIMIT 1

Question: How many budgets are above 3000 in year 2001 or before?
SQL: SELECT count(*) FROM budget WHERE budgeted  >  3000 AND YEAR  <=  2001

Question: Count the number of budgets in year 2001 or before whose budgeted amount is greater than 3000
SQL: SELECT count(*) FROM budget WHERE budgeted  >  3000 AND YEAR  <=  2001

Question: Show each school name, its budgeted amount, and invested amount in year 2002 or after.
SQL: SELECT T2.school_name ,  T1.budgeted ,  T1.invested FROM budget AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id WHERE T1.year  >=  2002

Question: Show all donor names.
SQL: SELECT DISTINCT donator_name FROM endowment

Question: How many budget record has a budget amount smaller than the invested amount?
SQL: SELECT count(*) FROM budget WHERE budgeted  <  invested

Question: What is the total budget amount for school "Glenn" in all years?
SQL: SELECT sum(T1.budgeted) FROM budget AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id WHERE T2.school_name  =  'Glenn'

Question: Show the names of schools with a total budget amount greater than 100 or a total endowment greater than 10.
SQL: SELECT T2.school_name FROM budget AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id JOIN endowment AS T3 ON T2.school_id  =  T3.school_id GROUP BY T2.school_name HAVING sum(T1.budgeted)  >  100 OR sum(T3.amount)  >  10

Question: Find the names of schools that have more than one donator with donation amount above 8.5.
SQL: SELECT T2.School_name FROM endowment AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id WHERE T1.amount  >  8.5 GROUP BY T1.school_id HAVING count(*)  >  1

Question: Find the number of schools that have more than one donator whose donation amount is less than 8.5.
SQL: SELECT count(*) FROM (SELECT * FROM endowment WHERE amount  >  8.5 GROUP BY school_id HAVING count(*)  >  1)

Question: List the name, IHSAA Football Class, and Mascot of the schools that have more than 6000 of budgeted amount or were founded before 2003, in the order of percent of total invested budget and total budgeted budget.
SQL: SELECT T1.School_name ,  T1.Mascot ,  T1.IHSAA_Football_Class FROM school AS T1 JOIN budget AS T2 ON T1.school_id  =  T2.school_id WHERE Budgeted  >  6000 OR YEAR  <  2003 ORDER BY T2.total_budget_percent_invested ,  T2.total_budget_percent_budgeted