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 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 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 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 game ids and the number of hours played.
assistant
SELECT gameid , sum(hours_played) FROM Plays_games GROUP BY gameid
human
Show all game names played by at least 1000 hours.
assistant
SELECT gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid = T2.gameid GROUP BY T1.gameid HAVING sum(hours_played) >= 1000
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 ;