SQL query to look for a Kevin Bacon number of 2
To give a sketch of a solution rather than an exact solution I would use this general approach
SELECT *
FROM ACTOR
WHERE id IN (
SELECT id
/* ... of actors that have worked on a film worked
on by actors that have worked on a KB film*/
EXCEPT
SELECT id
/* ... of all actors that have worked on a KB film
including KB himself*/ )
Also as you are not allowed to use recursive CTEs anyway here's an answer using those.
WITH RecursiveCTE
AS (SELECT C.pid,
C.mid,
0 as Level
FROM CASTS C
JOIN ACTOR A
ON A.id = C.pid
WHERE A.fname = 'Kevin'
and A.lname = 'Bacon'
UNION ALL
SELECT c1.pid,
c2.mid,
R.Level + 1
FROM RecursiveCTE R
JOIN CASTS c1
ON c1.mid = R.mid
AND R.Level < 2
JOIN CASTS c2
ON c1.pid = c2.pid)
SELECT *
FROM ACTOR
WHERE id IN (SELECT pid
FROM RecursiveCTE
GROUP BY pid
HAVING MIN(Level) = 2)