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 of the school with the smallest enrollment.
assistant
SELECT school_name FROM school ORDER BY enrollment LIMIT 1
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, 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 all school names in alphabetical order.
assistant
SELECT school_name FROM school ORDER BY school_name
human
What are the mascots for schools with enrollments above the average?
assistant
SELECT mascot FROM school WHERE enrollment > (SELECT avg(enrollment) FROM school)
human
List the name, location, mascot for all schools.
assistant
SELECT school_name , LOCATION , mascot FROM school ;