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 games are played for all football games by students on scholarship?
assistant
SELECT sum(gamesplayed) FROM Sportsinfo WHERE sportname = 'Football' AND onscholarship = 'Y'
human
Show ids of students who play video game and play sports.
assistant
SELECT StuID FROM Sportsinfo INTERSECT SELECT StuID FROM Plays_games
human
How many students play sports?
assistant
SELECT count(DISTINCT StuID) FROM Sportsinfo
human
Show student ids who don't have any sports.
assistant
SELECT StuID FROM Student EXCEPT SELECT StuID FROM Sportsinfo
human
Show student ids who are female and play football.
assistant
SELECT StuID FROM Student WHERE sex = 'F' INTERSECT SELECT StuID FROM Sportsinfo WHERE sportname = 'Football'
human
What are the ids of all male students who do not play football?
assistant
SELECT StuID FROM Student WHERE sex = "M" EXCEPT SELECT StuID FROM Sportsinfo WHERE sportname = "Football" ;