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: 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