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
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
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
List the name of the school with the smallest enrollment.
assistant
SELECT school_name FROM school ORDER BY enrollment LIMIT 1
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
List the name, location, mascot for all schools.
assistant
SELECT school_name , LOCATION , mascot FROM school
human
Show all school names in alphabetical order.
assistant
SELECT school_name FROM school ORDER BY school_name ;