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
I want to know the states and the college names which have some college students in tryout.
assistant
SELECT T1.state , T1.cName FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName;
human
Hmm, can you show me the college names with players whose decisions are yes?
assistant
SELECT cName FROM Tryout WHERE decision = 'yes';
human
Can you list just the states which have college students in tryout and their decisions are yes?
assistant
SELECT DISTINCT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.decision = "yes" ;