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 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
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
Find the name, enrollment of the colleges whose size is bigger than 10000 and location is in state LA.
assistant
SELECT cName , enr FROM College WHERE enr > 10000 AND state = 'LA'
human
Find the names of either colleges in LA with greater than 15000 size or in state AZ with less than 13000 enrollment.
assistant
SELECT cName FROM College WHERE enr < 13000 AND state = 'AZ' UNION SELECT cName FROM College WHERE enr > 15000 AND state = 'LA'
human
List all information about college sorted by enrollment number in the ascending order.
assistant
SELECT * FROM College ORDER BY enr
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 ;