lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Show the id and details for the investors who have the top 3 number of transactions.

### SQL
SELECT T2.investor_id ,  T1.Investor_details FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id GROUP BY T2.investor_id ORDER BY COUNT(*) DESC LIMIT 3
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Show the ids of the investors who have at least two transactions.

### SQL
SELECT T2.investor_id FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id GROUP BY T2.investor_id HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
Show the ids and details of the investors who have at least two transactions with type code "SALE".

### SQL
SELECT T2.investor_id ,  T1.Investor_details FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id WHERE T2.transaction_type_code  =  "SALE" GROUP BY T2.investor_id HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
What are the dates of transactions with at least 100 share count or amount bigger than 100?

### SQL
SELECT date_of_transaction FROM TRANSACTIONS WHERE share_count  >=  100 OR amount_of_transaction  >=  100
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
What are the details of all sales and purchases?

### SQL
SELECT sales_details FROM sales UNION SELECT purchase_details FROM purchases
### Database Schema
CREATE TABLE `Investors` (
`investor_id` INTEGER PRIMARY KEY,
`Investor_details` VARCHAR(255)
)

CREATE TABLE `Lots` (
`lot_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`lot_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` )
)

CREATE TABLE `Ref_Transaction_Types` (
`transaction_type_code` VARCHAR(10) PRIMARY KEY,
`transaction_type_description` VARCHAR(80) NOT NULL
)

CREATE TABLE `Transactions` (
`transaction_id` INTEGER PRIMARY KEY,
`investor_id` INTEGER NOT NULL,
`transaction_type_code` VARCHAR(10) NOT NULL,
`date_of_transaction` DATETIME,
`amount_of_transaction` DECIMAL(19,4),
`share_count` VARCHAR(40),
`other_details` VARCHAR(255),
FOREIGN KEY (`investor_id` ) REFERENCES `Investors`(`investor_id` ),FOREIGN KEY (`transaction_type_code` ) REFERENCES `Ref_Transaction_Types`(`transaction_type_code` )
)

CREATE TABLE `Sales` (
`sales_transaction_id` INTEGER PRIMARY KEY,
`sales_details` VARCHAR(255),
FOREIGN KEY (`sales_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Purchases` (
`purchase_transaction_id` INTEGER NOT NULL,
`purchase_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`purchase_transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

CREATE TABLE `Transactions_Lots` (
`transaction_id` INTEGER NOT NULL,
`lot_id` INTEGER NOT NULL,
FOREIGN KEY (`lot_id` ) REFERENCES `Lots`(`lot_id` ),
FOREIGN KEY (`transaction_id` ) REFERENCES `Transactions`(`transaction_id` )
)

### Question
What are the details of the lots which are not used in any transactions?

### SQL
SELECT lot_details FROM Lots EXCEPT SELECT T1.lot_details FROM Lots AS T1 JOIN transactions_lots AS T2 ON T1.lot_id  =  T2.lot_id
### Database Schema
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
### Database Schema
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 do you have?

### SQL
SELECT count(*) FROM Video_games
### Database Schema
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 game types exist?

### SQL
SELECT count(DISTINCT gtype) FROM Video_games
### Database Schema
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
What is the count of different game types?

### SQL
SELECT count(DISTINCT gtype) FROM Video_games
### Database Schema
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
Show all video game types.

### SQL
SELECT DISTINCT gtype FROM Video_games
### Database Schema
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
What are the different types of video games?

### SQL
SELECT DISTINCT gtype FROM Video_games
### Database Schema
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
Show all video games and their types in the order of their names.

### SQL
SELECT gname ,  gtype FROM Video_games ORDER BY gname
### Database Schema
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
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
### Database Schema
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
Show all video games with type Collectible card game.

### SQL
SELECT gname FROM Video_games WHERE gtype  =  "Collectible card game"
### Database Schema
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
What are the names of all video games that are collectible cards?

### SQL
SELECT gname FROM Video_games WHERE gtype  =  "Collectible card game"
### Database Schema
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
What is the type of video game Call of Destiny.

### SQL
SELECT gtype FROM Video_games WHERE gname  =  "Call of Destiny"
### Database Schema
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
What type of game is Call of Destiny?

### SQL
SELECT gtype FROM Video_games WHERE gname  =  "Call of Destiny"
### Database Schema
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 have type Massively multiplayer online game?

### SQL
SELECT count(*) FROM Video_games WHERE gtype  =  "Massively multiplayer online game"
### Database Schema
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
Count the number of video games with Massively multiplayer online game type .

### SQL
SELECT count(*) FROM Video_games WHERE gtype  =  "Massively multiplayer online game"
### Database Schema
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
Show all video game types and the number of video games in each type.

### SQL
SELECT gtype ,  count(*) FROM Video_games GROUP BY gtype
### Database Schema
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
What are the types of video games and how many are in each type?

### SQL
SELECT gtype ,  count(*) FROM Video_games GROUP BY gtype
### Database Schema
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
Which game type has most number of games?

### SQL
SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) DESC LIMIT 1
### Database Schema
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
What type has the most games?

### SQL
SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) DESC LIMIT 1
### Database Schema
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
Which game type has least number of games?

### SQL
SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) LIMIT 1
### Database Schema
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
What is the type with the fewest games?

### SQL
SELECT gtype FROM Video_games GROUP BY gtype ORDER BY count(*) LIMIT 1
### Database Schema
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
Show ids for all students who live in CHI.

### SQL
SELECT StuID FROM Student WHERE city_code  =  "CHI"
### Database Schema
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
What are the ids of all students who live in CHI?

### SQL
SELECT StuID FROM Student WHERE city_code  =  "CHI"
### Database Schema
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
Show ids for all students who have advisor 1121.

### SQL
SELECT StuID FROM Student WHERE Advisor  =  1121
### Database Schema
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
What are the ids of all students who have advisor number 1121?

### SQL
SELECT StuID FROM Student WHERE Advisor  =  1121
### Database Schema
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
Show first name for all students with major 600.

### SQL
SELECT Fname FROM Student WHERE Major  =  600
### Database Schema
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
What are the first names for all students who are from the major numbered 600?

### SQL
SELECT Fname FROM Student WHERE Major  =  600
### Database Schema
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
Show the average, minimum, and maximum age for different majors.

### SQL
SELECT major ,  avg(age) ,  min(age) ,  max(age) FROM Student GROUP BY major
### Database Schema
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
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
### Database Schema
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
Show all advisors who have at least two students.

### SQL
SELECT advisor FROM Student GROUP BY advisor HAVING count(*)  >=  2
### Database Schema
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
What are the advisors

### SQL
SELECT advisor FROM Student GROUP BY advisor HAVING count(*)  >=  2
### Database Schema
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 sports do we have?

### SQL
SELECT count(DISTINCT sportname) FROM Sportsinfo
### Database Schema
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 different types of sports do we offer?

### SQL
SELECT count(DISTINCT sportname) FROM Sportsinfo
### Database Schema
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 students play sports?

### SQL
SELECT count(DISTINCT StuID) FROM Sportsinfo
### Database Schema
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 different students are involved in sports?

### SQL
SELECT count(DISTINCT StuID) FROM Sportsinfo
### Database Schema
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
List ids for all student who are on scholarship.

### SQL
SELECT StuID FROM Sportsinfo WHERE onscholarship  =  'Y'
### Database Schema
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
What are the ids for all sporty students who are on scholarship?

### SQL
SELECT StuID FROM Sportsinfo WHERE onscholarship  =  'Y'
### Database Schema
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
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'