human
Schema:
basketball_match (Team_ID, School_ID, Team_Name, ACC_Regular_Season, ACC_Percent, ACC_Home, ACC_Road, All_Games, All_Games_Percent, All_Home, All_Road, All_Neutral)
university (School_ID, School, Location, Founded, Affiliation, Enrollment, Nickname, Primary_conference)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the team names of the universities whose enrollments are smaller than the average enrollment size.
assistant
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)
human
List all schools and their nicknames in the order of founded year.
assistant
SELECT school , nickname FROM university ORDER BY founded
human
How many schools are in the basketball match?
assistant
SELECT count(DISTINCT school_id) FROM basketball_match
human
List names of all teams in the basketball competition, ordered by all home scores in descending order.
assistant
SELECT team_name FROM basketball_match ORDER BY All_Home DESC
human
What is the team name and acc regular season score of the school that was founded for the longest time?
assistant
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
human
Find the location and all games score of the school that has Clemson as its team name.
assistant
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" ;