human
Schema:
basketball_match (Team_ID, School_ID, Team_Name, ACC_Regular_Season, ACC_Percent, ACC_Home, ACC_Road, All_Games, All_Games_Percent, All_Home, All_Road, All_Neutral)
university (School_ID, School, Location, Founded, Affiliation, Enrollment, Nickname, Primary_conference)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the average enrollment size of the universities that are founded before 1850?
assistant
SELECT avg(enrollment) FROM university WHERE founded < 1850
human
When was the school with the largest enrollment founded?
assistant
SELECT founded FROM university ORDER BY enrollment DESC LIMIT 1
human
Find the total student enrollment for different affiliation type schools.
assistant
SELECT sum(enrollment) , affiliation FROM university GROUP BY affiliation
human
What is the maximum enrollment across all schools?
assistant
SELECT max(Enrollment) FROM university
human
What is the total and minimum enrollment of all schools?
assistant
SELECT sum(enrollment) , min(enrollment) FROM university
human
What are the different affiliations, and how many schools with each have an enrollment size of above 20000?
assistant
SELECT COUNT( * ) , affiliation FROM university WHERE enrollment > 20000 GROUP BY affiliation ;