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 names of the students who are in the position of striker and got a yes tryout decision.
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'
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 colleges do the tryout players whose name starts with letter D go to?
assistant
SELECT T1.cName FROM tryout AS T1 JOIN player AS T2 ON T1.pID = T2.pID WHERE T2.pName LIKE 'D%'
human
Find the name of the tryout players who are from the college with largest size.
assistant
SELECT T2.pName FROM tryout AS T1 JOIN player AS T2 ON T1.pID = T2.pID WHERE T1.cName = (SELECT cName FROM college ORDER BY enr DESC LIMIT 1)
human
Which position is most popular among players in the tryout?
assistant
SELECT pPos FROM tryout GROUP BY pPos ORDER BY count(*) DESC LIMIT 1
human
What are the unique types of player positions in the tryout?
assistant
SELECT COUNT( DISTINCT pPos ) FROM tryout ;