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
Find the name of all students who were in the tryout sorted in alphabetic order.
assistant
SELECT T1.pName FROM player AS T1 JOIN tryout AS T2 ON T1.pID = T2.pID ORDER BY T1.pName
human
Find the name and hours of the students whose tryout decision is yes.
assistant
SELECT T1.pName , T1.HS FROM player AS T1 JOIN tryout AS T2 ON T1.pID = T2.pID WHERE T2.decision = 'yes'
human
How many students got accepted after the tryout?
assistant
SELECT count(*) FROM tryout WHERE decision = 'yes'
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
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 ;