human
Schema:
school (School_ID, School, Location, Enrollment, Founded, Denomination, Boys_or_Girls, Day_or_Boarding, Year_Entered_Competition, School_Colors)
school_details (School_ID, Nickname, Colors, League, Class, Division)
school_performance (School_Id, School_Year, Class_A, Class_AA)
player (Player_ID, Player, Team, Age, Position, School_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
List the locations of schools that do not have any player.
assistant
SELECT LOCATION FROM school WHERE School_ID NOT IN (SELECT School_ID FROM Player)
human
For each player, show the team and the location of school they belong to.
assistant
SELECT T1.Team , T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID = T2.School_ID
human
How many different positions of players are there?
assistant
SELECT count(DISTINCT POSITION) FROM player
human
What are the teams of the players, sorted in ascending alphabetical order?
assistant
SELECT Team FROM player ORDER BY Team ASC
human
List the teams of the players with the top 5 largest ages.
assistant
SELECT Team FROM player ORDER BY Age DESC LIMIT 5
human
Which team has the oldest player?
assistant
SELECT Team FROM player ORDER BY Age DESC LIMIT 1 ;