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
Find the states of the colleges that have students in the tryout who played in striker position.
assistant
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.pPos = 'striker'
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
How many different colleges do attend the tryout test?
assistant
SELECT count(DISTINCT cName) FROM tryout
human
What is the state and enrollment of the colleges where have any students who got accepted in the tryout decision.
assistant
SELECT DISTINCT T1.state , T1.enr FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.decision = 'yes'
human
Find the states where have some college students in tryout.
assistant
SELECT DISTINCT state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName
human
What are the different states that had students successfully try out?
assistant
SELECT DISTINCT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.decision = "yes" ;