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

CREATE TABLE "College" (
	"cName" VARCHAR(20) NOT NULL, 
	state VARCHAR(2), 
	enr NUMERIC(5, 0), 
	PRIMARY KEY ("cName")
)

/*
2 rows from College table:
cName	state	enr
LSU	LA	18000
ASU	AZ	12000
*/


CREATE TABLE "Player" (
	"pID" NUMERIC(5, 0) NOT NULL, 
	"pName" VARCHAR(20), 
	"yCard" VARCHAR(3), 
	"HS" NUMERIC(5, 0), 
	PRIMARY KEY ("pID")
)

/*
2 rows from Player table:
pID	pName	yCard	HS
10001	Andrew	no	1200
20002	Blake	no	1600
*/


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

/*
2 rows from Tryout table:
pID	cName	pPos	decision
10001	LSU	goalie	no
10001	ASU	goalie	yes
*/
What are the names of all the states with college students playing in the mid position but no goalies?
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'
[{'state': 'OK'}]