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 all male student ids who don't play football.
assistant
SELECT StuID FROM Student WHERE sex = 'M' EXCEPT SELECT StuID FROM Sportsinfo WHERE sportname = 'Football'
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
Which sport has most number of students on scholarship?
assistant
SELECT sportname FROM Sportsinfo WHERE onscholarship = 'Y' GROUP BY sportname ORDER BY count(*) DESC LIMIT 1
human
How many students play sports?
assistant
SELECT count(DISTINCT StuID) FROM Sportsinfo
human
How many games are played for all students?
assistant
SELECT sum(gamesplayed) FROM Sportsinfo
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" ;