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
Show total hours per week and number of games played for students under 20.
assistant
SELECT sum(hoursperweek) , sum(gamesplayed) FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID WHERE T2.age < 20
human
Show all student IDs with the number of sports and total number of games played
assistant
SELECT StuID , count(*) , sum(gamesplayed) FROM Sportsinfo GROUP BY StuID
human
How many games are played for all students?
assistant
SELECT sum(gamesplayed) FROM Sportsinfo
human
Show all game ids and the number of hours played.
assistant
SELECT gameid , sum(hours_played) FROM Plays_games GROUP BY gameid
human
Show all student IDs with more than total 10 hours per week on all sports played.
assistant
SELECT StuID FROM Sportsinfo GROUP BY StuID HAVING sum(hoursperweek) > 10
human
What are the ids of all students and number of hours played?
assistant
SELECT Stuid , SUM( hours_played ) FROM Plays_games GROUP BY Stuid ;