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 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.
assistant
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
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
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 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 budget record has a budget amount smaller than the invested amount?
assistant
SELECT count(*) FROM budget WHERE budgeted < invested
human
Count the number of budgets in year 2001 or before whose budgeted amount is greater than 3000
assistant
SELECT COUNT( * ) FROM budget WHERE budgeted > 3000 AND YEAR <= 2001 ;