human
Schema:
country (Country_id, Country_name, Capital, Official_native_language)
team (Team_id, Name)
match_season (Season, Player, Position, Country, Team, Draft_Pick_Number, Draft_Class, College)
player (Player_ID, Player, Years_Played, Total_WL, Singles_WL, Doubles_WL, Team)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the different colleges this season?
assistant
SELECT DISTINCT college FROM match_season;
human
How many players are playing for the listed colleges?
assistant
SELECT college , COUNT( * ) FROM match_season GROUP by college;
human
What are the draft classes for the players from UCLA and Duke?
assistant
SELECT DISTINCT draft_class FROM match_season WHERE College = 'UCLA' OR College = 'Duke';
human
Could you tell me the draft pick numbers of the players from those draft classes?
assistant
SELECT draft_pick_number FROM match_season WHERE draft_class IN ( SELECT DISTINCT draft_class FROM match_season WHERE College = 'UCLA' OR College = 'Duke' );
human
What players were those draft pick numbers for? | do you want to see both the players and the draft pick numbers? | Yes please.
assistant
SELECT player , draft_pick_number FROM match_season WHERE draft_class IN ( SELECT DISTINCT draft_class FROM match_season WHERE College = "UCLA" OR College = "Duke" ) ;