Database: university_basketball

CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
);
CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
);

Question: List all schools and their nicknames in the order of founded year.
SQL: SELECT school ,  nickname FROM university ORDER BY founded

Question: What are the different schools and their nicknames, ordered by their founding years?
SQL: SELECT school ,  nickname FROM university ORDER BY founded

Question: List all public schools and their locations.
SQL: SELECT school ,  LOCATION FROM university WHERE affiliation  =  'Public'

Question: What are the public schools and what are their locations?
SQL: SELECT school ,  LOCATION FROM university WHERE affiliation  =  'Public'

Question: When was the school with the largest enrollment founded?
SQL: SELECT founded FROM university ORDER BY enrollment DESC LIMIT 1

Question: Return the founded year for the school with the largest enrollment.
SQL: SELECT founded FROM university ORDER BY enrollment DESC LIMIT 1

Question: Find the founded year of the newest non public school.
SQL: SELECT founded FROM university WHERE affiliation != 'Public' ORDER BY founded DESC LIMIT 1

Question: What is the founded year of the non public school that was founded most recently?
SQL: SELECT founded FROM university WHERE affiliation != 'Public' ORDER BY founded DESC LIMIT 1

Question: How many schools are in the basketball match?
SQL: SELECT count(DISTINCT school_id) FROM basketball_match

Question: Count the number of schools that have had basketball matches.
SQL: SELECT count(DISTINCT school_id) FROM basketball_match

Question: What is the highest acc percent score in the competition?
SQL: SELECT acc_percent FROM basketball_match ORDER BY acc_percent DESC LIMIT 1

Question: Return the highest acc percent across all basketball matches.
SQL: SELECT acc_percent FROM basketball_match ORDER BY acc_percent DESC LIMIT 1

Question: What is the primary conference of the school that has the lowest acc percent score in the competition?
SQL: SELECT t1.Primary_conference FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id ORDER BY t2.acc_percent LIMIT 1

Question: Return the primary conference of the school with the lowest acc percentage score.
SQL: SELECT t1.Primary_conference FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id ORDER BY t2.acc_percent LIMIT 1

Question: What is the team name and acc regular season score of the school that was founded for the longest time?
SQL: SELECT t2.team_name ,  t2.ACC_Regular_Season FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id ORDER BY t1.founded LIMIT 1

Question: Return the name of the team and the acc during the regular season for the school that was founded the earliest.
SQL: SELECT t2.team_name ,  t2.ACC_Regular_Season FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id ORDER BY t1.founded LIMIT 1

Question: Find the location and all games score of the school that has Clemson as its team name.
SQL: SELECT t2.All_Games ,  t1.location FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id WHERE team_name  =  'Clemson'

Question: What are the all games score and location of the school called Clemson?
SQL: SELECT t2.All_Games ,  t1.location FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id WHERE team_name  =  'Clemson'

Question: What are the average enrollment size of the universities that are founded before 1850?
SQL: SELECT avg(enrollment) FROM university WHERE founded  <  1850

Question: Return the average enrollment of universities founded before 1850.
SQL: SELECT avg(enrollment) FROM university WHERE founded  <  1850

Question: Show the enrollment and primary_conference of the oldest college.
SQL: SELECT enrollment ,  primary_conference FROM university ORDER BY founded LIMIT 1

Question: What are the enrollment and primary conference for the university which was founded the earliest?
SQL: SELECT enrollment ,  primary_conference FROM university ORDER BY founded LIMIT 1

Question: What is the total and minimum enrollment of all schools?
SQL: SELECT sum(enrollment) ,  min(enrollment) FROM university

Question: Return the total and minimum enrollments across all schools.
SQL: SELECT sum(enrollment) ,  min(enrollment) FROM university

Question: Find the total student enrollment for different affiliation type schools.
SQL: SELECT sum(enrollment) ,  affiliation FROM university GROUP BY affiliation

Question: What are the total enrollments of universities of each affiliation type?
SQL: SELECT sum(enrollment) ,  affiliation FROM university GROUP BY affiliation

Question: How many schools do not participate in the basketball match?
SQL: SELECT count(*) FROM university WHERE school_id NOT IN (SELECT school_id FROM basketball_match)

Question: Count the number of universities that do not participate in the baketball match.
SQL: SELECT count(*) FROM university WHERE school_id NOT IN (SELECT school_id FROM basketball_match)

Question: Find the schools that were either founded after 1850 or public.
SQL: SELECT school FROM university WHERE founded  >  1850 OR affiliation  =  'Public'

Question: What are the schools that were either founded before 1850 or are public?
SQL: SELECT school FROM university WHERE founded  >  1850 OR affiliation  =  'Public'

Question: Find how many different affiliation types there are.
SQL: SELECT count(DISTINCT affiliation) FROM university

Question: Count the number of different affiliation types.
SQL: SELECT count(DISTINCT affiliation) FROM university

Question: Find how many school locations have the word 'NY'.
SQL: SELECT count(*) FROM university WHERE LOCATION LIKE "%NY%"

Question: How many universities have a location that contains NY?
SQL: SELECT count(*) FROM university WHERE LOCATION LIKE "%NY%"

Question: Find the team names of the universities whose enrollments are smaller than the average enrollment size.
SQL: SELECT t2.team_name FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id WHERE enrollment  <  (SELECT avg(enrollment) FROM university)

Question: What are the names of teams from universities that have a below average enrollment?
SQL: SELECT t2.team_name FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id WHERE enrollment  <  (SELECT avg(enrollment) FROM university)

Question: Find the number of universities that have over a 20000 enrollment size for each affiliation type.
SQL: SELECT count(*) ,  affiliation FROM university WHERE enrollment  >  20000 GROUP BY affiliation

Question: What are the different affiliations, and how many schools with each have an enrollment size of above 20000?
SQL: SELECT count(*) ,  affiliation FROM university WHERE enrollment  >  20000 GROUP BY affiliation

Question: Find the total number of students enrolled in the colleges that were founded after the year of 1850 for each affiliation type.
SQL: SELECT sum(Enrollment) ,  affiliation FROM university WHERE founded  >  1850 GROUP BY affiliation

Question: What are the different affiliations, and what is the total enrollment of schools founded after 1850 for each enrollment type?
SQL: SELECT sum(Enrollment) ,  affiliation FROM university WHERE founded  >  1850 GROUP BY affiliation

Question: What is the maximum enrollment across all schools?
SQL: SELECT max(Enrollment) FROM university

Question: Return the maximum enrollment across all schools.
SQL: SELECT max(Enrollment) FROM university

Question: List all information regarding the basketball match.
SQL: SELECT * FROM basketball_match

Question: What is all the information about the basketball match?
SQL: SELECT * FROM basketball_match

Question: List names of all teams in the basketball competition, ordered by all home scores in descending order.
SQL: SELECT team_name FROM basketball_match ORDER BY All_Home DESC

Question: What are the names of all the teams in the basketball competition, sorted by all home scores in descending order?
SQL: SELECT team_name FROM basketball_match ORDER BY All_Home DESC