CREATE TABLE player (pid NUMBER, pname CLOB, ycard CLOB, hs NUMBER)
CREATE TABLE tryout (pid NUMBER, cname CLOB, ppos CLOB, decision CLOB)
CREATE TABLE college (cname CLOB, state CLOB, enr NUMBER)

How many schools have students playing in goalie and mid-field positions?
SELECT COUNT(*) FROM (SELECT cname FROM tryout WHERE ppos = 'goalie' INTERSECT SELECT cname FROM tryout WHERE ppos = 'mid')
WITH "cte" AS (SELECT "cname" FROM "tryout" WHERE "ppos" = 'goalie' INTERSECT SELECT "cname" FROM "tryout" WHERE "ppos" = 'mid') SELECT COUNT(*) FROM "cte" "cte"