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
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 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
Find the state of the college which player Charles is attending.
assistant
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName JOIN player AS T3 ON T2.pID = T3.pID WHERE T3.pName = "Charles" ;