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 players whose names contain letter 'a'.
assistant
SELECT DISTINCT pName FROM Player WHERE pName LIKE '%a%'
human
How many schools have some students playing in goalie and mid positions.
assistant
SELECT COUNT(*) FROM (SELECT cName FROM tryout WHERE pPos = 'goalie' INTERSECT SELECT cName FROM tryout WHERE pPos = 'mid')
human
Find the names of schools that have some players in the mid position but not in the goalie position.
assistant
SELECT cName FROM tryout WHERE pPos = 'mid' EXCEPT SELECT cName FROM tryout WHERE pPos = 'goalie'
human
What are the names of players whose training hours is between 500 and 1500?
assistant
SELECT pName FROM Player WHERE HS BETWEEN 500 AND 1500
human
Which position is most popular among players in the tryout?
assistant
SELECT pPos FROM tryout GROUP BY pPos ORDER BY count(*) DESC LIMIT 1
human
What are the different types of player positions?
assistant
SELECT COUNT( DISTINCT pPos ) FROM tryout ;