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

How many schools have some students playing in goalie and mid 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"