CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);
CREATE TABLE Video_Games (
    GameID NUMBER PRIMARY KEY,
    GName TEXT,
    GType TEXT
);
CREATE TABLE Plays_Games (
    StuID NUMBER,
    GameID NUMBER,
    Hours_Played NUMBER,
    FOREIGN KEY ("StuID") REFERENCES Student("StuID"),
    FOREIGN KEY ("GameID") REFERENCES Video_Games("GameID")
);
CREATE TABLE SportsInfo (
    StuID NUMBER,
    SportName TEXT,
    HoursPerWeek NUMBER,
    GamesPlayed NUMBER,
    OnScholarship TEXT,
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);

Question: Show student ids who don't have any sports.
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Sportsinfo

Question: What are the ids of all students who don't play sports?
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Sportsinfo

Question: Show student ids who are on scholarship and have major 600.
SQL: SELECT StuID FROM Student WHERE major  =  600 INTERSECT SELECT StuID FROM Sportsinfo WHERE onscholarship  =  'Y'

Question: What are the student ids for those on scholarship in major number 600?
SQL: SELECT StuID FROM Student WHERE major  =  600 INTERSECT SELECT StuID FROM Sportsinfo WHERE onscholarship  =  'Y'

Question: Show student ids who are female and play football.
SQL: SELECT StuID FROM Student WHERE sex  =  'F' INTERSECT SELECT StuID FROM Sportsinfo WHERE sportname  =  "Football"

Question: What are the ids of all female students who play football?
SQL: SELECT StuID FROM Student WHERE sex  =  'F' INTERSECT SELECT StuID FROM Sportsinfo WHERE sportname  =  "Football"

Question: Show all male student ids who don't play football.
SQL: SELECT StuID FROM Student WHERE sex  =  'M' EXCEPT SELECT StuID FROM Sportsinfo WHERE sportname  =  "Football"

Question: What are the ids of all male students who do not play football?
SQL: SELECT StuID FROM Student WHERE sex  =  'M' EXCEPT SELECT StuID FROM Sportsinfo WHERE sportname  =  "Football"

Question: Show total hours per week and number of games played for student David Shieber.
SQL: SELECT sum(hoursperweek) ,  sum(gamesplayed) FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T2.Fname  =  "David" AND T2.Lname  =  "Shieber"

Question: What is the total number of hours per work and number of games played by David Shieber?
SQL: SELECT sum(hoursperweek) ,  sum(gamesplayed) FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T2.Fname  =  "David" AND T2.Lname  =  "Shieber"

Question: Show total hours per week and number of games played for students under 20.
SQL: SELECT sum(hoursperweek) ,  sum(gamesplayed) FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T2.age  <  20

Question: What is the total number of hours per week and number of games played by students under 20?
SQL: SELECT sum(hoursperweek) ,  sum(gamesplayed) FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T2.age  <  20

Question: How many students play video games?
SQL: SELECT count(DISTINCT StuID) FROM Plays_games

Question: How many different students play games?
SQL: SELECT count(DISTINCT StuID) FROM Plays_games

Question: Show ids of students who don't play video game.
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Plays_games

Question: What are the ids of all students who are not video game players?
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Plays_games

Question: Show ids of students who play video game and play sports.
SQL: SELECT StuID FROM Sportsinfo INTERSECT SELECT StuID FROM Plays_games

Question: What are the ids of all students who played video games and sports?
SQL: SELECT StuID FROM Sportsinfo INTERSECT SELECT StuID FROM Plays_games

Question: Show all game ids and the number of hours played.
SQL: SELECT gameid ,  sum(hours_played) FROM Plays_games GROUP BY gameid

Question: What are ids and total number of hours played for each game?
SQL: SELECT gameid ,  sum(hours_played) FROM Plays_games GROUP BY gameid

Question: Show all student ids and the number of hours played.
SQL: SELECT Stuid ,  sum(hours_played) FROM Plays_games GROUP BY Stuid

Question: What are the ids of all students and number of hours played?
SQL: SELECT Stuid ,  sum(hours_played) FROM Plays_games GROUP BY Stuid

Question: Show the game name that has most number of hours played.
SQL: SELECT gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid  =  T2.gameid GROUP BY T1.gameid ORDER BY sum(hours_played) DESC LIMIT 1

Question: What is the name of the game that has been played the most?
SQL: SELECT gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid  =  T2.gameid GROUP BY T1.gameid ORDER BY sum(hours_played) DESC LIMIT 1

Question: Show all game names played by at least 1000 hours.
SQL: SELECT gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid  =  T2.gameid GROUP BY T1.gameid HAVING sum(hours_played)  >=  1000

Question: What are the names of all the games that have been played for at least 1000 hours?
SQL: SELECT gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid  =  T2.gameid GROUP BY T1.gameid HAVING sum(hours_played)  >=  1000

Question: Show all game names played by Linda Smith
SQL: SELECT Gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid  =  T2.gameid JOIN Student AS T3 ON T3.Stuid  =  T1.Stuid WHERE T3.Lname  =  "Smith" AND T3.Fname  =  "Linda"

Question: What are the names of all games played by Linda Smith?
SQL: SELECT Gname FROM Plays_games AS T1 JOIN Video_games AS T2 ON T1.gameid  =  T2.gameid JOIN Student AS T3 ON T3.Stuid  =  T1.Stuid WHERE T3.Lname  =  "Smith" AND T3.Fname  =  "Linda"

Question: Find the last and first name of students who are playing Football or Lacrosse.
SQL: 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"

Question: What is the first and last name of all students who play Football or Lacrosse?
SQL: 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"

Question: Find the first name and age of the students who are playing both Football and Lacrosse.
SQL: SELECT fname ,  age FROM Student WHERE StuID IN (SELECT StuID FROM Sportsinfo WHERE SportName  =  "Football" INTERSECT SELECT StuID FROM Sportsinfo WHERE SportName  =  "Lacrosse")

Question: What are the first names and ages of all students who are playing both Football and Lacrosse?
SQL: SELECT fname ,  age FROM Student WHERE StuID IN (SELECT StuID FROM Sportsinfo WHERE SportName  =  "Football" INTERSECT SELECT StuID FROM Sportsinfo WHERE SportName  =  "Lacrosse")

Question: Find the last name and gender of the students who are playing both Call of Destiny and Works of Widenius games.
SQL: 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")

Question: what is the last name and gender of all students who played both Call of Destiny and Works of Widenius?
SQL: 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")