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 states that have some college students playing in the mid position but not in the goalie position.
assistant
SELECT COUNT(*) FROM (SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.pPos = 'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.pPos = 'goalie')
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
Find the names of schools that have some students playing in goalie and mid positions.
assistant
SELECT cName FROM tryout WHERE pPos = 'goalie' INTERSECT SELECT cName FROM tryout WHERE pPos = 'mid'
human
Find the names of states that have some college students playing in goalie and mid positions.
assistant
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.pPos = 'goalie' INTERSECT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.pPos = 'mid'
human
How many students whose are playing the role of goalie?
assistant
SELECT count(*) FROM tryout WHERE pPos = 'goalie'
human
What college has a student who successfully made the team in the role of a goalie?
assistant
SELECT cName FROM tryout WHERE decision = "yes" AND pPos = "goalie" ;