lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the name of players whose card is yes in the descending order of training hours.

### SQL
SELECT pName FROM Player WHERE yCard  =  'yes' ORDER BY HS DESC
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the name of the players who received a card in descending order of the hours of training?

### SQL
SELECT pName FROM Player WHERE yCard  =  'yes' ORDER BY HS DESC
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the name of different colleges involved in the tryout in alphabetical order.

### SQL
SELECT DISTINCT cName FROM tryout ORDER BY cName
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the different names of the colleges involved in the tryout in alphabetical order?

### SQL
SELECT DISTINCT cName FROM tryout ORDER BY cName
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Which position is most popular among players in the tryout?

### SQL
SELECT pPos FROM tryout GROUP BY pPos ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What was the most popular position at tryouts?

### SQL
SELECT pPos FROM tryout GROUP BY pPos ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the number of students who participate in the tryout for each college ordered by descending count.

### SQL
SELECT count(*) ,  cName FROM tryout GROUP BY cName ORDER BY count(*) DESC
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many students participated in tryouts for each college by descennding count?

### SQL
SELECT count(*) ,  cName FROM tryout GROUP BY cName ORDER BY count(*) DESC
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is minimum hours of the students playing in different position?

### SQL
SELECT min(T2.HS) ,   T1.pPos FROM tryout AS T1 JOIN player AS T2 ON T1.pID  =  T2.pID GROUP BY T1.pPos
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
For each position, what is the minimum time students spent practicing?

### SQL
SELECT min(T2.HS) ,   T1.pPos FROM tryout AS T1 JOIN player AS T2 ON T1.pID  =  T2.pID GROUP BY T1.pPos
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of schools with the top 3 largest size?

### SQL
SELECT cName FROM college ORDER BY enr DESC LIMIT 3
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of the schools with the top 3 largest class sizes?

### SQL
SELECT cName FROM college ORDER BY enr DESC LIMIT 3
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the name of school that has the smallest enrollment in each state?

### SQL
SELECT cName ,  state ,  min(enr) FROM college GROUP BY state
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the name of the school with smallest enrollment size per state?

### SQL
SELECT cName ,  state ,  min(enr) FROM college GROUP BY state
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the states where have some college students in tryout.

### SQL
SELECT DISTINCT state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the different states that have students trying out?

### SQL
SELECT DISTINCT state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the states where have some college students in tryout and their decisions are yes.

### SQL
SELECT DISTINCT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the different states that had students successfully try out?

### SQL
SELECT DISTINCT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the name and college of students whose decisions are yes in the tryout.

### SQL
SELECT T1.pName ,  T2.cName FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of all the players who received a yes during tryouts, and also what are the names of their colleges?

### SQL
SELECT T1.pName ,  T2.cName FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the name of all students who were in the tryout sorted in alphabetic order.

### SQL
SELECT T1.pName FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID ORDER BY T1.pName
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of all students who tried out in alphabetical order?

### SQL
SELECT T1.pName FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID ORDER BY T1.pName
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the name and hours of the students whose tryout decision is yes.

### SQL
SELECT T1.pName ,  T1.HS FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names and hours spent practicing of every student who received a yes at tryouts?

### SQL
SELECT T1.pName ,  T1.HS FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the states of the colleges that have students in the tryout who played in striker position.

### SQL
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'striker'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the states of the colleges where students who tried out for the striker position attend?

### SQL
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'striker'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the names of the students who are in the position of striker and got a yes tryout decision.

### SQL
SELECT T1.pName FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'yes' AND T2.pPos  =  'striker'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of all students who successfully tried out for the position of striker?

### SQL
SELECT T1.pName FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'yes' AND T2.pPos  =  'striker'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the state of the college which player Charles is attending.

### SQL
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName JOIN player AS T3 ON T2.pID  =  T3.pID WHERE T3.pName  =  'Charles'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
In which state is the college that Charles attends?

### SQL
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName JOIN player AS T3 ON T2.pID  =  T3.pID WHERE T3.pName  =  'Charles'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the average and maximum hours for the students whose tryout decision is yes.

### SQL
SELECT avg(T1.HS) ,  max(T1.HS) FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the average and maximum number of hours students who made the team practiced?

### SQL
SELECT avg(T1.HS) ,  max(T1.HS) FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the average hours for the students whose tryout decision is no.

### SQL
SELECT avg(T1.HS) FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'no'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the average number of hours spent practicing for students who got rejected?

### SQL
SELECT avg(T1.HS) FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T2.decision  =  'no'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the maximum training hours for the students whose training hours is greater than 1000 in different positions?

### SQL
SELECT max(T1.HS) ,  pPos FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T1.HS  >  1000 GROUP BY T2.pPos
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
For each position, what is the maximum number of  hours for students who spent more than 1000 hours training?

### SQL
SELECT max(T1.HS) ,  pPos FROM player AS T1 JOIN tryout AS T2 ON T1.pID  =  T2.pID WHERE T1.HS  >  1000 GROUP BY T2.pPos
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Which colleges do the tryout players whose name starts with letter D go to?

### SQL
SELECT T1.cName FROM  tryout AS T1 JOIN player AS T2 ON T1.pID  =  T2.pID WHERE T2.pName LIKE 'D%'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Which colleges does each player with a name that starts with the letter D  who tried out go to?

### SQL
SELECT T1.cName FROM  tryout AS T1 JOIN player AS T2 ON T1.pID  =  T2.pID WHERE T2.pName LIKE 'D%'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Which college has any student who is a goalie and succeeded in the tryout.

### SQL
SELECT cName FROM  tryout WHERE decision  =  'yes' AND pPos  =  'goalie'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What college has a student who successfully made the team in the role of a goalie?

### SQL
SELECT cName FROM  tryout WHERE decision  =  'yes' AND pPos  =  'goalie'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the name of the tryout players who are from the college with largest size.

### SQL
SELECT T2.pName FROM  tryout AS T1 JOIN player AS T2 ON T1.pID  =  T2.pID WHERE T1.cName  =  (SELECT cName FROM college ORDER BY enr DESC LIMIT 1)
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of all tryout participants who are from the largest college?

### SQL
SELECT T2.pName FROM  tryout AS T1 JOIN player AS T2 ON T1.pID  =  T2.pID WHERE T1.cName  =  (SELECT cName FROM college ORDER BY enr DESC LIMIT 1)
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the state and enrollment of the colleges where have any students who got accepted in the tryout decision.

### SQL
SELECT DISTINCT T1.state ,  T1.enr FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many students are enrolled in colleges that have student accepted during tryouts, and in which states are those colleges?

### SQL
SELECT DISTINCT T1.state ,  T1.enr FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.decision  =  'yes'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the names of either colleges in LA with greater than 15000 size or in state AZ with less than 13000 enrollment.

### SQL
SELECT cName FROM College WHERE enr  <  13000 AND state = "AZ" UNION SELECT cName FROM College WHERE enr  >  15000 AND state = "LA"
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of colleges in LA that have more than 15,000 students and of colleges in AZ with less than 13,000 students?

### SQL
SELECT cName FROM College WHERE enr  <  13000 AND state = "AZ" UNION SELECT cName FROM College WHERE enr  >  15000 AND state = "LA"
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the names of schools that have some students playing in goalie and mid positions.

### SQL
SELECT cName FROM  tryout WHERE pPos  =  'goalie' INTERSECT SELECT cName FROM  tryout WHERE pPos  =  'mid'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of all schools that have students trying out for the position of goal and 'mid'-field.

### SQL
SELECT cName FROM  tryout WHERE pPos  =  'goalie' INTERSECT SELECT cName FROM  tryout WHERE pPos  =  'mid'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the names of states that have some college students playing in goalie and mid positions.

### SQL
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie' INTERSECT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of the states that have some college students playing in the positions of goalie and mid-field?

### SQL
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie' INTERSECT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many schools have some students playing in goalie and mid positions.

### SQL
SELECT COUNT(*) FROM (SELECT cName FROM  tryout WHERE pPos  =  'goalie' INTERSECT SELECT cName FROM  tryout WHERE pPos  =  'mid')
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many schools have students playing in goalie and mid-field positions?

### SQL
SELECT COUNT(*) FROM (SELECT cName FROM  tryout WHERE pPos  =  'goalie' INTERSECT SELECT cName FROM  tryout WHERE pPos  =  'mid')
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the names of schools that have some players in the mid position but not in the goalie position.

### SQL
SELECT cName FROM  tryout WHERE pPos  =  'mid' EXCEPT SELECT cName FROM  tryout WHERE pPos  =  'goalie'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of the schools with some players in the mid position but no goalies?

### SQL
SELECT cName FROM  tryout WHERE pPos  =  'mid' EXCEPT SELECT cName FROM  tryout WHERE pPos  =  'goalie'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the names of states that have some college students playing in the mid position but not in the goalie position.

### SQL
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of all the states with college students playing in the mid position but no goalies?

### SQL
SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie'
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
How many states that have some college students playing in the mid position but not in the goalie position.

### SQL
SELECT COUNT(*) FROM (SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie')
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What is the count of states with college students playing in the mid position but not as goalies?

### SQL
SELECT COUNT(*) FROM (SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'mid' EXCEPT SELECT T1.state FROM college AS T1 JOIN tryout AS T2 ON T1.cName  =  T2.cName WHERE T2.pPos  =  'goalie')
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find the states where have the colleges whose enrollments are less than the largest size.

### SQL
SELECT DISTINCT state FROM college WHERE enr  <  (SELECT max(enr) FROM college)
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the states with colleges that have enrollments less than the some other college?

### SQL
SELECT DISTINCT state FROM college WHERE enr  <  (SELECT max(enr) FROM college)
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
Find names of colleges with enrollment greater than that of some (at least one) college in the FL state.

### SQL
SELECT DISTINCT cName FROM college WHERE enr  >  (SELECT min(enr) FROM college WHERE state  =  'FL')
### Database Schema
CREATE TABLE College 
  ( cName   	varchar(20) NOT NULL,
    state   	varchar(2),
    enr     	numeric(5,0),
    PRIMARY KEY (cName)
  )

CREATE TABLE Player
  ( pID			numeric(5,0) NOT NULL,
  	pName   	varchar(20),
    yCard   	varchar(3),
    HS      	numeric(5,0),
    PRIMARY KEY (pID)
  )

CREATE TABLE Tryout
  ( pID			numeric(5,0),
  	cName   	varchar(20),
    pPos    	varchar(8),
    decision    varchar(3),
    PRIMARY KEY (pID, cName),
    FOREIGN KEY (pID) REFERENCES Player(pID),
    FOREIGN KEY (cName) REFERENCES College(cName)
  )

### Question
What are the names of the colleges that are larger than at least one college in Florida?

### SQL
SELECT DISTINCT cName FROM college WHERE enr  >  (SELECT min(enr) FROM college WHERE state  =  'FL')