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
What is minimum hours of the students playing in different position?
assistant
SELECT min(T2.HS) , T1.pPos FROM tryout AS T1 JOIN player AS T2 ON T1.pID = T2.pID GROUP BY T1.pPos
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
What is the average training hours of all players?
assistant
SELECT avg(HS) FROM Player
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
What is the average number of hours spent practicing for students who got rejected?
assistant
SELECT avg ( T1.HS ) FROM player AS T1 JOIN tryout AS T2 ON T1.pID = T2.pID WHERE T2.decision = "no" ;