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
Find the names of schools that have more than one donator with donation amount above 8.5.
assistant
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
human
How many budget record has a budget amount smaller than the invested amount?
assistant
SELECT count(*) FROM budget WHERE budgeted < invested
human
Show each county along with the number of schools and total enrollment in each county.
assistant
SELECT county , count(*) , sum(enrollment) FROM school GROUP BY county
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'
human
Show the names of schools with a total budget amount greater than 100 or a total endowment greater than 10.
assistant
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
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 ;