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 in descending order of founded year.
assistant
SELECT LOCATION FROM school ORDER BY Founded DESC
human
Show locations and nicknames of schools.
assistant
SELECT T1.Location , T2.Nickname FROM school AS T1 JOIN school_details AS T2 ON T1.School_ID = T2.School_ID
human
Show the denomination of the school that has the most players.
assistant
SELECT T2.Denomination FROM player AS T1 JOIN school AS T2 ON T1.School_ID = T2.School_ID GROUP BY T1.School_ID ORDER BY COUNT(*) DESC LIMIT 1
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
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
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 ) ;