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 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
List the locations of schools in ascending order of enrollment.
assistant
SELECT LOCATION FROM school ORDER BY Enrollment ASC
human
Please show different denominations and the corresponding number of schools in descending order.
assistant
SELECT Denomination , COUNT(*) FROM school GROUP BY Denomination ORDER BY COUNT(*) DESC
human
Show the denomination shared by more than one school.
assistant
SELECT Denomination FROM school GROUP BY Denomination HAVING COUNT(*) > 1
human
Please show different denominations and the corresponding number of schools.
assistant
SELECT Denomination , COUNT(*) FROM school GROUP BY Denomination
human
List the enrollment for each school that does not have 'Catholic' as denomination.
assistant
SELECT Enrollment FROM school WHERE Denomination != "Catholic" ;