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 student ids who don't have any sports.
assistant
SELECT StuID FROM Student EXCEPT SELECT StuID FROM Sportsinfo
human
Show ids of students who play video game and play sports.
assistant
SELECT StuID FROM Sportsinfo INTERSECT SELECT StuID FROM Plays_games
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 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 sport name and the number of students.
assistant
SELECT sportname , count(*) FROM Sportsinfo GROUP BY sportname
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 ;