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
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
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
Please show different denominations and the corresponding number of schools.
assistant
SELECT Denomination , COUNT(*) FROM school GROUP BY Denomination
human
Show the denomination shared by more than one school.
assistant
SELECT Denomination FROM school GROUP BY Denomination HAVING COUNT(*) > 1
human
Show the denomination shared by schools founded before 1890 and schools founded after 1900
assistant
SELECT Denomination FROM school WHERE Founded < 1890 INTERSECT SELECT Denomination FROM school WHERE Founded > 1900 ;