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
How many colleges in total?
assistant
SELECT count(*) 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 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
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
How many colleges has more than 15000 students?
assistant
SELECT count(*) FROM College WHERE enr > 15000
human
What are the names of colleges in LA that have more than 15,000 students and of colleges in AZ with less than 13,000 students?
assistant
SELECT cName FROM College WHERE enr < 13000 AND state = "AZ" UNION SELECT cName FROM College WHERE enr > 15000 AND state = "LA" ;