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
How many students play video games?
assistant
SELECT count(DISTINCT StuID) FROM Plays_games
human
What is the first name and last name of the student who have most number of sports?
assistant
SELECT T2.Fname , T2.Lname FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID GROUP BY T1.StuID ORDER BY count(*) DESC LIMIT 1
human
Which sport has most number of students on scholarship?
assistant
SELECT sportname FROM Sportsinfo WHERE onscholarship = 'Y' GROUP BY sportname ORDER BY count(*) DESC LIMIT 1
human
How many games are played for all students?
assistant
SELECT sum(gamesplayed) FROM Sportsinfo
human
How many students play sports?
assistant
SELECT count(DISTINCT StuID) FROM Sportsinfo
human
How many students play each sport?
assistant
SELECT sportname , COUNT( * ) FROM Sportsinfo GROUP BY sportname ;