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

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'

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'

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'

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'

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

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

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)

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)

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

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

Question: Find names of all colleges whose enrollment is greater than that of all colleges in the FL state.
SQL: SELECT cName FROM college WHERE enr  >  (SELECT max(enr) FROM college WHERE state  =  'FL')

Question: What are the names of all colleges with a larger enrollment than the largest college in Florida?
SQL: SELECT cName FROM college WHERE enr  >  (SELECT max(enr) FROM college WHERE state  =  'FL')

Question: What is the total number of enrollment of schools that do not have any goalie player?
SQL: SELECT sum(enr) FROM college WHERE cName NOT IN (SELECT cName FROM tryout WHERE pPos  =  "goalie")

Question: What is the total number of students enrolled in schools without any goalies?
SQL: SELECT sum(enr) FROM college WHERE cName NOT IN (SELECT cName FROM tryout WHERE pPos  =  "goalie")

Question: What is the number of states that has some college whose enrollment is larger than the average enrollment?
SQL: SELECT count(DISTINCT state) FROM college WHERE enr  >  (SELECT avg(enr) FROM college)

Question: How many states have a college with more students than average?
SQL: SELECT count(DISTINCT state) FROM college WHERE enr  >  (SELECT avg(enr) FROM college)

Question: What is the number of states that has some colleges whose enrollment is smaller than the average enrollment?
SQL: SELECT count(DISTINCT state) FROM college WHERE enr  <  (SELECT avg(enr) FROM college)

Question: How many states have smaller colleges than average?
SQL: SELECT count(DISTINCT state) FROM college WHERE enr  <  (SELECT avg(enr) FROM college)