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 has more than 15000 students?
assistant
SELECT count(*) FROM College WHERE enr > 15000
human
What is the average enrollment number?
assistant
SELECT avg(enr) FROM College
human
How many schools have some students playing in goalie and mid positions.
assistant
SELECT COUNT(*) FROM (SELECT cName FROM tryout WHERE pPos = 'goalie' INTERSECT SELECT cName FROM tryout WHERE pPos = 'mid')
human
What is the total enrollment number of all colleges?
assistant
SELECT sum(enr) FROM College
human
How many students whose are playing the role of goalie?
assistant
SELECT count(*) FROM tryout WHERE pPos = 'goalie'
human
What is the total number of students enrolled in schools without any goalies?
assistant
SELECT SUM( enr ) FROM college WHERE cName NOT IN ( SELECT cName FROM tryout WHERE pPos = "goalie" ) ;