human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Video_Games (GameID, GName, GType)
Plays_Games (StuID, GameID, Hours_Played)
SportsInfo (StuID, SportName, HoursPerWeek, GamesPlayed, OnScholarship)
Please answer with SQL.
assistant
Sure, I understand.
human
Could you please show me all the student IDs with the number of sports?
assistant
SELECT StuID , COUNT( * ) FROM Sportsinfo GROUP BY StuID;
human
Could you add the total number of games played to that table please?
assistant
SELECT StuID , COUNT( * ) , SUM( gamesplayed ) FROM Sportsinfo GROUP BY StuID;
human
How many students are on scholarships?
assistant
SELECT COUNT( distinct stuid ) FROM SportsInfo WHERE OnScholarship = 'Y';
human
What is the most common sports name?
assistant
SELECT SportName FROM SportsInfo GROUP BY SportName ORDER BY COUNT( * ) DESC LIMIT 1 ;