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
Find the team names of the universities whose enrollments are smaller than the average enrollment size.
assistant
SELECT t2.team_name FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id = t2.school_id WHERE enrollment < (SELECT avg(enrollment) FROM university)
human
Find how many different affiliation types there are.
assistant
SELECT count(DISTINCT affiliation) FROM university
human
Find the total number of students enrolled in the colleges that were founded after the year of 1850 for each affiliation type.
assistant
SELECT sum(Enrollment) , affiliation FROM university WHERE founded > 1850 GROUP BY affiliation
human
Find the total student enrollment for different affiliation type schools.
assistant
SELECT sum(enrollment) , affiliation FROM university GROUP BY affiliation
human
Find the number of universities that have over a 20000 enrollment size for each affiliation type.
assistant
SELECT COUNT( * ) , affiliation FROM university WHERE enrollment > 20000 GROUP BY affiliation ;