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 the game name that has most number of hours played.
assistant
SELECT gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid = T2.gameid GROUP BY T1.gameid ORDER BY sum(hours_played) DESC LIMIT 1
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
Show all student ids and the number of hours played.
assistant
SELECT Stuid , sum(hours_played) FROM Plays_games GROUP BY Stuid
human
Show total hours per week and number of games played for student David Shieber.
assistant
SELECT sum(hoursperweek) , sum(gamesplayed) FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID WHERE T2.Fname = 'David' AND T2.Lname = 'Shieber'
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 ;