Database: game_1

create table Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 );
create table Video_Games (
       GameID           INTEGER PRIMARY KEY,
       GName            VARCHAR(40),
       GType            VARCHAR(40)
);
create table Plays_Games (
       StuID                INTEGER,
       GameID            INTEGER,
       Hours_Played      INTEGER,
       FOREIGN KEY(GameID) REFERENCES Video_Games(GameID),
       FOREIGN KEY(StuID) REFERENCES Student(StuID)
);
create table SportsInfo (
  StuID INTEGER,
  SportName VARCHAR(32),
  HoursPerWeek INTEGER,
  GamesPlayed INTEGER,
  OnScholarship VARCHAR(1),
  FOREIGN KEY(StuID) REFERENCES Student(StuID)
);

Question: How many video games exist?
SQL: SELECT count(*) FROM Video_games

Question: How many video games do you have?
SQL: SELECT count(*) FROM Video_games

Question: How many video game types exist?
SQL: SELECT count(DISTINCT gtype) FROM Video_games

Question: What is the count of different game types?
SQL: SELECT count(DISTINCT gtype) FROM Video_games

Question: Show all video game types.
SQL: SELECT DISTINCT gtype FROM Video_games

Question: What are the different types of video games?
SQL: SELECT DISTINCT gtype FROM Video_games

Question: Show all video games and their types in the order of their names.
SQL: SELECT gname ,  gtype FROM Video_games ORDER BY gname

Question: What are the names of all the video games and their types in alphabetical order?
SQL: SELECT gname ,  gtype FROM Video_games ORDER BY gname

Question: Show all video games with type Collectible card game.
SQL: SELECT gname FROM Video_games WHERE gtype  =  "Collectible card game"

Question: What are the names of all video games that are collectible cards?
SQL: SELECT gname FROM Video_games WHERE gtype  =  "Collectible card game"

Question: What is the type of video game Call of Destiny.
SQL: SELECT gtype FROM Video_games WHERE gname  =  "Call of Destiny"

Question: What type of game is Call of Destiny?
SQL: SELECT gtype FROM Video_games WHERE gname  =  "Call of Destiny"

Question: How many video games have type Massively multiplayer online game?
SQL: SELECT count(*) FROM Video_games WHERE gtype  =  "Massively multiplayer online game"

Question: Count the number of video games with Massively multiplayer online game type .
SQL: SELECT count(*) FROM Video_games WHERE gtype  =  "Massively multiplayer online game"

Question: Show all video game types and the number of video games in each type.
SQL: SELECT gtype ,  count(*) FROM Video_games GROUP BY gtype

Question: What are the types of video games and how many are in each type?
SQL: SELECT gtype ,  count(*) FROM Video_games GROUP BY gtype

Question: Which game type has most number of games?
SQL: SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) DESC LIMIT 1

Question: What type has the most games?
SQL: SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) DESC LIMIT 1

Question: Which game type has least number of games?
SQL: SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) LIMIT 1

Question: What is the type with the fewest games?
SQL: SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) LIMIT 1

Question: Show ids for all students who live in CHI.
SQL: SELECT StuID FROM Student WHERE city_code  =  "CHI"

Question: What are the ids of all students who live in CHI?
SQL: SELECT StuID FROM Student WHERE city_code  =  "CHI"

Question: Show ids for all students who have advisor 1121.
SQL: SELECT StuID FROM Student WHERE Advisor  =  1121

Question: What are the ids of all students who have advisor number 1121?
SQL: SELECT StuID FROM Student WHERE Advisor  =  1121

Question: Show first name for all students with major 600.
SQL: SELECT Fname FROM Student WHERE Major  =  600

Question: What are the first names for all students who are from the major numbered 600?
SQL: SELECT Fname FROM Student WHERE Major  =  600

Question: Show the average, minimum, and maximum age for different majors.
SQL: SELECT major ,  avg(age) ,  min(age) ,  max(age) FROM Student GROUP BY major

Question: What are the average, minimum, and max ages for each of the different majors?
SQL: SELECT major ,  avg(age) ,  min(age) ,  max(age) FROM Student GROUP BY major

Question: Show all advisors who have at least two students.
SQL: SELECT advisor FROM Student GROUP BY advisor HAVING count(*)  >=  2

Question: What are the advisors
SQL: SELECT advisor FROM Student GROUP BY advisor HAVING count(*)  >=  2

Question: How many sports do we have?
SQL: SELECT count(DISTINCT sportname) FROM Sportsinfo

Question: How many different types of sports do we offer?
SQL: SELECT count(DISTINCT sportname) FROM Sportsinfo

Question: How many students play sports?
SQL: SELECT count(DISTINCT StuID) FROM Sportsinfo

Question: How many different students are involved in sports?
SQL: SELECT count(DISTINCT StuID) FROM Sportsinfo

Question: List ids for all student who are on scholarship.
SQL: SELECT StuID FROM Sportsinfo WHERE onscholarship  =  'Y'

Question: What are the ids for all sporty students who are on scholarship?
SQL: SELECT StuID FROM Sportsinfo WHERE onscholarship  =  'Y'

Question: Show last names for all student who are on scholarship.
SQL: SELECT T2.Lname FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T1.onscholarship  =  'Y'

Question: What are the last names for all scholarship students?
SQL: SELECT T2.Lname FROM Sportsinfo AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T1.onscholarship  =  'Y'

Question: How many games are played for all students?
SQL: SELECT sum(gamesplayed) FROM Sportsinfo

Question: What is the total number of games played?
SQL: SELECT sum(gamesplayed) FROM Sportsinfo

Question: How many games are played for all football games by students on scholarship?
SQL: SELECT sum(gamesplayed) FROM Sportsinfo WHERE sportname  =  "Football" AND onscholarship  =  'Y'

Question: What is the total number of all football games played by scholarship students?
SQL: SELECT sum(gamesplayed) FROM Sportsinfo WHERE sportname  =  "Football" AND onscholarship  =  'Y'

Question: Show all sport name and the number of students.
SQL: SELECT sportname ,  count(*) FROM Sportsinfo GROUP BY sportname

Question: How many students play each sport?
SQL: SELECT sportname ,  count(*) FROM Sportsinfo GROUP BY sportname

Question: Show all student IDs with the number of sports and total number of games played
SQL: SELECT StuID ,  count(*) ,  sum(gamesplayed) FROM Sportsinfo GROUP BY StuID

Question: What are the ids of all students along with how many sports and games did they play?
SQL: SELECT StuID ,  count(*) ,  sum(gamesplayed) FROM Sportsinfo GROUP BY StuID

Question: Show all student IDs with more than total 10 hours per week on all sports played.
SQL: SELECT StuID FROM Sportsinfo GROUP BY StuID HAVING sum(hoursperweek)  >  10

Question: What are the student IDs for everybody who worked for more than 10 hours per week on all sports?
SQL: SELECT StuID FROM Sportsinfo GROUP BY StuID HAVING sum(hoursperweek)  >  10

Question: What is the first name and last name of the student who have most number of sports?
SQL: 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

Question: What is the first and last name of the student who played the most sports?
SQL: 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

Question: Which sport has most number of students on scholarship?
SQL: SELECT sportname FROM Sportsinfo WHERE onscholarship  =  'Y' GROUP BY sportname ORDER BY count(*) DESC LIMIT 1

Question: What is the sport with the most scholarship students?
SQL: SELECT sportname FROM Sportsinfo WHERE onscholarship  =  'Y' GROUP BY sportname ORDER BY count(*) DESC LIMIT 1

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")