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 ascending order of enrollment.
assistant
SELECT LOCATION FROM school ORDER BY Enrollment ASC
human
What are the enrollments of schools whose denomination is not 'Catholic'?
assistant
SELECT Enrollment FROM school WHERE Denomination != 'Catholic'
human
List the locations of schools in descending order of founded year.
assistant
SELECT LOCATION FROM school ORDER BY Founded 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
Order denominations in descending order of the count of schools with the denomination. Return each denomination with the count of schools.
assistant
SELECT Denomination , COUNT( * ) FROM school GROUP BY Denomination ORDER BY COUNT( * ) DESC ;