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 how many school locations have the word 'NY'.
assistant
SELECT count(*) FROM university WHERE LOCATION LIKE '%NY%'
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 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
human
Count the number of different affiliation types.
assistant
SELECT COUNT( DISTINCT affiliation ) FROM university ;