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
Show the enrollment and primary_conference of the oldest college.
assistant
SELECT enrollment , primary_conference FROM university ORDER BY founded LIMIT 1
human
List all public schools and their locations.
assistant
SELECT school , LOCATION FROM university WHERE affiliation = 'Public'
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
What is the maximum enrollment across all schools?
assistant
SELECT max(Enrollment) FROM university
human
Find the total student enrollment for different affiliation type schools.
assistant
SELECT sum(enrollment) , affiliation FROM university GROUP BY affiliation
human
Return the total and minimum enrollments across all schools.
assistant
SELECT SUM( enrollment ) , MIN( enrollment ) FROM university ;