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
Which college has any student who is a goalie and succeeded in the tryout.
assistant
SELECT cName FROM tryout WHERE decision = 'yes' AND pPos = 'goalie'
human
Find the names of states that have some college students playing in the mid position but not in the goalie position.
assistant
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
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
Find the names of schools that have some players in the mid position but not in the goalie position.
assistant
SELECT cName FROM tryout WHERE pPos = 'mid' EXCEPT SELECT cName FROM tryout WHERE 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
What are the names of all schools that have students trying out for the position of goal and 'mid'-field.
assistant
SELECT cName FROM tryout WHERE pPos = "goalie" INTERSECT SELECT cName FROM tryout WHERE pPos = "mid" ;