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

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'

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

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

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'

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'

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'

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'

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'

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'

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'

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'

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'

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'

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'

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'

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

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

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

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

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'

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'

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)

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)

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'

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'

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"

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"

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'

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'

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'

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'

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