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
List all the names of schools with an endowment amount smaller than or equal to 10.
assistant
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
human
List each donator name and the amount of endowment in descending order of the amount of endowment.
assistant
SELECT donator_name , sum(amount) FROM endowment GROUP BY donator_name ORDER BY sum(amount) DESC
human
List the names of the schools without any endowment.
assistant
SELECT school_name FROM school WHERE school_id NOT IN (SELECT school_id FROM endowment)
human
Show the names of donors who donated to both school 'Glenn' and 'Triton.'
assistant
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'
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
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" ;