human
Schema:
College (cName, state, enr)
Player (pID, pName, yCard, HS)
Tryout (pID, cName, pPos, decision)
Please answer with SQL.
assistant
Sure, I understand.
human
What is average enrollment of colleges in the state FL?
assistant
SELECT avg(enr) FROM College WHERE state = 'FL'
human
Find the states where have the colleges whose enrollments are less than the largest size.
assistant
SELECT DISTINCT state FROM college WHERE enr < (SELECT max(enr) FROM college)
human
What is the total enrollment number of all colleges?
assistant
SELECT sum(enr) FROM College
human
What is the name of school that has the smallest enrollment in each state?
assistant
SELECT cName , state , min(enr) FROM college GROUP BY state
human
What is the number of states that has some college whose enrollment is larger than the average enrollment?
assistant
SELECT count(DISTINCT state) FROM college WHERE enr > (SELECT avg(enr) FROM college)
human
What is the number of states that has some colleges whose enrollment is smaller than the average enrollment?
assistant
SELECT COUNT( DISTINCT state ) FROM college WHERE enr < ( SELECT avg ( enr ) FROM college ) ;