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 number of students who participate in the tryout for each college ordered by descending count.
assistant
SELECT count(*) , cName FROM tryout GROUP BY cName ORDER BY count(*) DESC
human
Find the states where have some 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'
human
How many students got accepted after the tryout?
assistant
SELECT count(*) FROM tryout WHERE decision = 'yes'
human
Find the states where have some college students in tryout.
assistant
SELECT DISTINCT state FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName
human
How many different colleges do attend the tryout test?
assistant
SELECT count(DISTINCT cName) FROM tryout
human
How many students are enrolled in colleges that have student accepted during tryouts, and in which states are those colleges?
assistant
SELECT DISTINCT T1.state , T1.enr FROM college AS T1 JOIN tryout AS T2 ON T1.cName = T2.cName WHERE T2.decision = "yes" ;