CREATE TABLE College (
    cName TEXT PRIMARY KEY,
    state TEXT,
    enr NUMBER
);
CREATE TABLE Player (
    pID NUMBER PRIMARY KEY,
    pName TEXT,
    yCard TEXT,
    HS NUMBER
);
CREATE TABLE Tryout (
    pID NUMBER PRIMARY KEY,
    cName TEXT,
    pPos TEXT,
    decision TEXT,
    FOREIGN KEY ("cName") REFERENCES College("cName"),
    FOREIGN KEY ("pID") REFERENCES Player("pID")
);

Question: What is the total enrollment number of all colleges?
SQL: SELECT sum(enr) FROM College

Question: How many students are enrolled in college?
SQL: SELECT sum(enr) FROM College

Question: What is the average enrollment number?
SQL: SELECT avg(enr) FROM College

Question: How many students, on average, does each college have enrolled?
SQL: SELECT avg(enr) FROM College

Question: How many colleges in total?
SQL: SELECT count(*) FROM College

Question: How many different colleges are there?
SQL: SELECT count(*) FROM College

Question: How many players have more than 1000 hours of training?
SQL: SELECT count(*) FROM Player WHERE HS  >  1000

Question: How many different players trained for more than 1000 hours?
SQL: SELECT count(*) FROM Player WHERE HS  >  1000

Question: How many colleges has more than 15000 students?
SQL: SELECT count(*) FROM College WHERE enr  >  15000

Question: What is the number of colleges with a student population greater than 15000?
SQL: SELECT count(*) FROM College WHERE enr  >  15000

Question: What is the average training hours of all players?
SQL: SELECT avg(HS) FROM Player

Question: How many hours do the players train on average?
SQL: SELECT avg(HS) FROM Player

Question: Find the name and training hours of players whose hours are below 1500.
SQL: SELECT pName ,  HS FROM Player WHERE HS  <  1500

Question: What are the names and number of hours spent training for each player who trains for less than 1500 hours?
SQL: SELECT pName ,  HS FROM Player WHERE HS  <  1500

Question: How many different colleges do attend the tryout test?
SQL: SELECT count(DISTINCT cName) FROM tryout

Question: How many different colleges were represented at tryouts?
SQL: SELECT count(DISTINCT cName) FROM tryout

Question: What are the unique types of player positions in the tryout?
SQL: SELECT count(DISTINCT pPos) FROM tryout

Question: What are the different types of player positions?
SQL: SELECT count(DISTINCT pPos) FROM tryout

Question: How many students got accepted after the tryout?
SQL: SELECT count(*) FROM tryout WHERE decision  =  'yes'

Question: How many students received a yes from tryouts?
SQL: SELECT count(*) FROM tryout WHERE decision  =  'yes'

Question: How many students whose are playing the role of goalie?
SQL: SELECT count(*) FROM tryout WHERE pPos  =  'goalie'

Question: What is the number of students playing as a goalie?
SQL: SELECT count(*) FROM tryout WHERE pPos  =  'goalie'

Question: Find the max, average and min training hours of all players.
SQL: SELECT avg(HS) ,  max(HS) ,  min(HS) FROM Player

Question: What is the average, maximum, and minimum for the number of hours spent training?
SQL: SELECT avg(HS) ,  max(HS) ,  min(HS) FROM Player

Question: What is average enrollment of colleges in the state FL?
SQL: SELECT avg(enr) FROM College WHERE state  =  'FL'

Question: What is average number of students enrolled in Florida colleges?
SQL: SELECT avg(enr) FROM College WHERE state  =  'FL'

Question: What are the names of players whose training hours is between 500 and 1500?
SQL: SELECT pName FROM Player WHERE HS BETWEEN 500 AND 1500

Question: What are the names of players who train between 500 and 1500 hours?
SQL: SELECT pName FROM Player WHERE HS BETWEEN 500 AND 1500

Question: Find the players whose names contain letter 'a'.
SQL: SELECT DISTINCT pName FROM Player WHERE pName LIKE '%a%'

Question: Who are the players that have names containing the letter a?
SQL: SELECT DISTINCT pName FROM Player WHERE pName LIKE '%a%'

Question: Find the name, enrollment of the colleges whose size is bigger than 10000 and location is in state LA.
SQL: SELECT cName ,  enr FROM College WHERE enr  >  10000 AND state = "LA"

Question: What are the names and enrollment numbers for colleges that have more than 10000 enrolled and are located in Louisiana?
SQL: SELECT cName ,  enr FROM College WHERE enr  >  10000 AND state = "LA"

Question: List all information about college sorted by enrollment number in the ascending order.
SQL: SELECT * FROM College ORDER BY enr

Question: What information do you have on colleges sorted by increasing enrollment numbers?
SQL: SELECT * FROM College ORDER BY enr

Question: List the name of the colleges whose enrollment is greater 18000 sorted by the college's name.
SQL: SELECT cName FROM College WHERE enr  >  18000 ORDER BY cName

Question: What is the name of every college in alphabetical order that has more than 18000 students enrolled?
SQL: SELECT cName FROM College WHERE enr  >  18000 ORDER BY 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

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

Question: Find the name of different colleges involved in the tryout in alphabetical order.
SQL: SELECT DISTINCT cName FROM tryout ORDER BY 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

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

Question: What was the most popular position at tryouts?
SQL: SELECT pPos FROM tryout GROUP BY pPos ORDER BY count(*) DESC LIMIT 1

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

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

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

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

Question: What are the names of schools with the top 3 largest size?
SQL: SELECT cName FROM college ORDER BY enr DESC LIMIT 3

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

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

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

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

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

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'

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'