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 name of different colleges involved in the tryout in alphabetical order.
assistant
SELECT DISTINCT cName FROM tryout ORDER BY cName
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
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
Find the name and college of students whose decisions are yes in the tryout.
assistant
SELECT T1.pName , T2.cName FROM player AS T1 JOIN tryout AS T2 ON T1.pID = T2.pID WHERE T2.decision = 'yes'
human
What are the names of all tryout participants who are from the largest college?
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 ) ;