Database: school_player

CREATE TABLE "school" (
"School_ID" int,
"School" text,
"Location" text,
"Enrollment" real,
"Founded" real,
"Denomination" text,
"Boys_or_Girls" text,
"Day_or_Boarding" text,
"Year_Entered_Competition" real,
"School_Colors" text,
PRIMARY KEY ("School_Id")
);
CREATE TABLE "school_details" (
"School_ID" int,
"Nickname" text,
"Colors" text,
"League" text,
"Class" text,
"Division" text,
PRIMARY KEY ("School_Id"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
);
CREATE TABLE "school_performance" (
"School_Id" int,
"School_Year" text,
"Class_A" text,
"Class_AA" text,
PRIMARY KEY ("School_Id","School_Year"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
);
CREATE TABLE "player" (
"Player_ID" int,
"Player" text,
"Team" text,
"Age" int,
"Position" text,
"School_ID" int,
PRIMARY KEY ("Player_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `school`(`School_ID`)
);

Question: How many schools are there?
SQL: SELECT count(*) FROM school

Question: Count the number of schools.
SQL: SELECT count(*) FROM school

Question: List the locations of schools in ascending order of enrollment.
SQL: SELECT LOCATION FROM school ORDER BY Enrollment ASC

Question: What is the list of school locations sorted in ascending order of school enrollment?
SQL: SELECT LOCATION FROM school ORDER BY Enrollment ASC

Question: List the locations of schools in descending order of founded year.
SQL: SELECT LOCATION FROM school ORDER BY Founded DESC

Question: What is the list of school locations sorted in descending order of school foundation year?
SQL: SELECT LOCATION FROM school ORDER BY Founded DESC

Question: What are the enrollments of schools whose denomination is not "Catholic"?
SQL: SELECT Enrollment FROM school WHERE Denomination != "Catholic"

Question: List the enrollment for each school that does not have "Catholic" as denomination.
SQL: SELECT Enrollment FROM school WHERE Denomination != "Catholic"

Question: What is the average enrollment of schools?
SQL: SELECT avg(Enrollment) FROM school

Question: Take the average of the school enrollment.
SQL: SELECT avg(Enrollment) FROM school

Question: What are the teams of the players, sorted in ascending alphabetical order?
SQL: SELECT Team FROM player ORDER BY Team ASC

Question: Find the team of each player and sort them in ascending alphabetical order.
SQL: SELECT Team FROM player ORDER BY Team ASC

Question: How many different positions of players are there?
SQL: SELECT count(DISTINCT POSITION) FROM player

Question: Count the number of distinct player positions.
SQL: SELECT count(DISTINCT POSITION) FROM player

Question: Find the team of the player of the highest age.
SQL: SELECT Team FROM player ORDER BY Age DESC LIMIT 1

Question: Which team has the oldest player?
SQL: SELECT Team FROM player ORDER BY Age DESC LIMIT 1

Question: List the teams of the players with the top 5 largest ages.
SQL: SELECT Team FROM player ORDER BY Age DESC LIMIT 5

Question: What are the teams that have the 5 oldest players?
SQL: SELECT Team FROM player ORDER BY Age DESC LIMIT 5

Question: For each player, show the team and the location of school they belong to.
SQL: SELECT T1.Team ,  T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID

Question: What are the team and the location of school each player belongs to?
SQL: SELECT T1.Team ,  T2.Location FROM player AS T1 JOIN school AS T2 ON T1.School_ID  =  T2.School_ID

Question: Show the locations of schools that have more than 1 player.
SQL: 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

Question: Which schools have more than 1 player? Give me the school locations.
SQL: 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

Question: Show the denomination of the school that has the most players.
SQL: 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

Question: What is the denomination of the school the most players belong to?
SQL: 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

Question: Show locations and nicknames of schools.
SQL: SELECT T1.Location ,  T2.Nickname FROM school AS T1 JOIN school_details AS T2 ON T1.School_ID  =  T2.School_ID

Question: What are the location and nickname of each school?
SQL: SELECT T1.Location ,  T2.Nickname FROM school AS T1 JOIN school_details AS T2 ON T1.School_ID  =  T2.School_ID

Question: Please show different denominations and the corresponding number of schools.
SQL: SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination

Question: For each denomination, return the denomination and the count of schools with that denomination.
SQL: SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination

Question: Please show different denominations and the corresponding number of schools in descending order.
SQL: SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination ORDER BY COUNT(*) DESC

Question: Order denominations in descending order of the count of schools with the denomination. Return each denomination with the count of schools.
SQL: SELECT Denomination ,  COUNT(*) FROM school GROUP BY Denomination ORDER BY COUNT(*) DESC

Question: List the school color of the school that has the largest enrollment.
SQL: SELECT School_Colors FROM school ORDER BY Enrollment DESC LIMIT 1

Question: What is the school color of the school with the largest enrollment?
SQL: SELECT School_Colors FROM school ORDER BY Enrollment DESC LIMIT 1

Question: List the locations of schools that do not have any player.
SQL: SELECT LOCATION FROM school WHERE School_ID NOT IN (SELECT School_ID FROM Player)

Question: Which schools do not have any player? Give me the school locations.
SQL: SELECT LOCATION FROM school WHERE School_ID NOT IN (SELECT School_ID FROM Player)

Question: Show the denomination shared by schools founded before 1890 and schools founded after 1900
SQL: SELECT Denomination FROM school WHERE Founded  <  1890 INTERSECT SELECT Denomination FROM school WHERE Founded  >  1900

Question: What are the denominations used by both schools founded before 1890 and schools founded  after 1900?
SQL: SELECT Denomination FROM school WHERE Founded  <  1890 INTERSECT SELECT Denomination FROM school WHERE Founded  >  1900

Question: Show the nicknames of schools that are not in division 1.
SQL: SELECT Nickname FROM school_details WHERE Division != "Division 1"

Question: What are the nicknames of schools whose division is not 1?
SQL: SELECT Nickname FROM school_details WHERE Division != "Division 1"

Question: Show the denomination shared by more than one school.
SQL: SELECT Denomination FROM school GROUP BY Denomination HAVING COUNT(*)  >  1

Question: What are the denomination more than one school have?
SQL: SELECT Denomination FROM school GROUP BY Denomination HAVING COUNT(*)  >  1