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
Show the locations of schools that have more than 1 player.
assistant
SELECT T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID = T2.School_ID GROUP BY T1.School_ID HAVING COUNT(*) > 1
human
How many different positions of players are there?
assistant
SELECT count(DISTINCT POSITION) 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
List the teams of the players with the top 5 largest ages.
assistant
SELECT Team FROM player ORDER BY Age DESC LIMIT 5
human
Find the team of the player of the highest age.
assistant
SELECT Team FROM player ORDER BY Age DESC LIMIT 1
human
Find the team of each player and sort them in ascending alphabetical order.
assistant
SELECT Team FROM player ORDER BY Team ASC ;