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 average hours for the students whose tryout decision is no.
assistant
SELECT avg(T1.HS) FROM player AS T1 JOIN tryout AS T2 ON T1.pID = T2.pID WHERE T2.decision = 'no'
human
Find the average and maximum hours for the students whose tryout decision is yes.
assistant
SELECT avg(T1.HS) , max(T1.HS) FROM player AS T1 JOIN tryout AS T2 ON T1.pID = T2.pID WHERE T2.decision = 'yes'
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
How many students got accepted after the tryout?
assistant
SELECT count(*) FROM tryout WHERE decision = 'yes'
human
What are the names and hours spent practicing of every student who received a yes at tryouts?
assistant
SELECT T1.pName , T1.HS FROM player AS T1 JOIN tryout AS T2 ON T1.pID = T2.pID WHERE T2.decision = "yes" ;