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
List the name of the colleges whose enrollment is greater 18000 sorted by the college's name.
assistant
SELECT cName FROM College WHERE enr > 18000 ORDER BY cName
human
What is average enrollment of colleges in the state FL?
assistant
SELECT avg(enr) FROM College WHERE state = 'FL'
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)
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 total enrollment number of all colleges?
assistant
SELECT sum(enr) FROM College
human
What information do you have on colleges sorted by increasing enrollment numbers?
assistant
SELECT * FROM College ORDER BY enr ;