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 ids of students who don't play video game.
assistant
SELECT StuID FROM Student EXCEPT SELECT StuID FROM Plays_games
human
How many games are played for all students?
assistant
SELECT sum(gamesplayed) 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
Find the first name and age of the students who are playing both Football and Lacrosse.
assistant
SELECT fname , age FROM Student WHERE StuID IN (SELECT StuID FROM Sportsinfo WHERE SportName = 'Football' INTERSECT SELECT StuID FROM Sportsinfo WHERE SportName = 'Lacrosse')
human
Find the last and first name of students who are playing Football or Lacrosse.
assistant
SELECT T2.lname , T2.fname FROM SportsInfo AS T1 JOIN Student AS T2 ON T1.StuID = T2.StuID WHERE T1.SportName = 'Football' OR T1.SportName = 'Lacrosse'
human
Find the last name and gender of the students who are playing both Call of Destiny and Works of Widenius games.
assistant
SELECT lname , sex FROM Student WHERE StuID IN ( SELECT T1.StuID FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.GameID = T2.GameID WHERE T2.Gname = "Call of Destiny" INTERSECT SELECT T1.StuID FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.GameID = T2.GameID WHERE T2.Gname = "Works of Widenius" ) ;