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
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
List all schools and their nicknames in the order of founded year.
assistant
SELECT school , nickname FROM university ORDER BY founded
human
Show the enrollment and primary_conference of the oldest college.
assistant
SELECT enrollment , primary_conference FROM university ORDER BY founded LIMIT 1
human
Find the total student enrollment for different affiliation type schools.
assistant
SELECT sum(enrollment) , affiliation FROM university GROUP BY affiliation
human
Find the founded year of the newest non public school.
assistant
SELECT founded FROM university WHERE affiliation != 'Public' ORDER BY founded DESC LIMIT 1
human
Return the founded year for the school with the largest enrollment.
assistant
SELECT founded FROM university ORDER BY enrollment DESC LIMIT 1 ;