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
What is the first name and last name of the student who have most number of sports?
assistant
SELECT T2.Fname , T2.Lname FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID GROUP BY T1.StuID ORDER BY count(*) DESC LIMIT 1
human
Show ids of students who don't play video game.
assistant
SELECT StuID FROM Student EXCEPT SELECT StuID FROM Plays_games
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 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
What are the ids of all students who don't play sports?
assistant
SELECT StuID FROM Student EXCEPT SELECT StuID FROM Sportsinfo ;