human
Schema:
School (School_id, School_name, Location, Mascot, Enrollment, IHSAA_Class, IHSAA_Football_Class, County)
budget (School_id, Year, Budgeted, total_budget_percent_budgeted, Invested, total_budget_percent_invested, Budget_invested_percent)
endowment (endowment_id, School_id, donator_name, amount)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the total and average enrollment of all schools?
assistant
SELECT sum(enrollment) , avg(enrollment) FROM school
human
How many budget record has a budget amount smaller than the invested amount?
assistant
SELECT count(*) FROM budget WHERE budgeted < invested
human
How many budgets are above 3000 in year 2001 or before?
assistant
SELECT count(*) FROM budget WHERE budgeted > 3000 AND YEAR <= 2001
human
Show each school name, its budgeted amount, and invested amount in year 2002 or after.
assistant
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
human
How many donors have endowment for school named 'Glenn'?
assistant
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'
human
What is the total budget amount for school 'Glenn' in all years?
assistant
SELECT SUM( T1.budgeted ) FROM budget AS T1 JOIN school AS T2 ON T1.school_id = T2.school_id WHERE T2.school_name = "Glenn" ;