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

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