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 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
How many different colleges do attend the tryout test?
assistant
SELECT count(DISTINCT cName) FROM tryout
human
What is the total enrollment number of all colleges?
assistant
SELECT sum(enr) FROM College
human
How many colleges has more than 15000 students?
assistant
SELECT count(*) FROM College WHERE enr > 15000
human
How many different colleges are there?
assistant
SELECT COUNT( * ) FROM College ;