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 are the names of players whose training hours is between 500 and 1500?
assistant
SELECT pName FROM Player WHERE HS BETWEEN 500 AND 1500
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
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 students whose are playing the role of goalie?
assistant
SELECT count(*) FROM tryout WHERE pPos = 'goalie'
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
What are the names of all students who successfully tried out for the position of striker?
assistant
SELECT T1.pName FROM player AS T1 JOIN tryout AS T2 ON T1.pID = T2.pID WHERE T2.decision = "yes" AND T2.pPos = "striker" ;