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
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
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
How many colleges has more than 15000 students?
assistant
SELECT count(*) FROM College WHERE enr > 15000
human
What is the total enrollment number of all colleges?
assistant
SELECT sum(enr) FROM College
human
What are the names and enrollment numbers for colleges that have more than 10000 enrolled and are located in Louisiana?
assistant
SELECT cName , enr FROM College WHERE enr > 10000 AND state = "LA" ;