I'm trying to learn sqlite queries but keep getting this error

Question

I'm trying to write an SQLite Query using sqliteonline.com that can:

Find the names of faculty members who teach in every room in which some class is taught.

Here are the relations:

This is what I have:

SELECT DISTINCT F.fname
FROM Faculty F
WHERE NOT EXISTS (( SELECT *
                    FROM Class C )
                    EXCEPT
                   (SELECT C1.room
                    FROM Class C1
                    WHERE C1.fid = F.fid ))    

AND This is the error I get:

I've double checked and the parenthesis are balanced. So I'm kind of confused on where to go from here.

---

Answer 1

You must remove the parentheses that enclose each of the subqueries and select the same columns in each of them (I guess only room is needed):

SELECT F.fname
FROM Faculty F
WHERE NOT EXISTS ( SELECT room FROM Class C 
                   EXCEPT
                   SELECT C1.room
                   FROM Class C1
                   WHERE C1.fid = F.fid 
                 )

Also DISTINCT is not really needed, because you are selecting unique (I believe) fnames from Faculty.

Answer 2

The problem is the select *. Just select the same column on both sides of the except:

SELECT F.fname
FROM Faculty F
WHERE NOT EXISTS (SELECT c.Room
                  FROM Class C
                  EXCEPT
                  SELECT C1.room
                  FROM Class C1
                  WHERE C1.fid = F.fid
                 )

Note: You should probably select more than just the first name. The rows in Faculty should be unique.

Answer 3

This reads like a relational divison problem. I would address this with a canonical approach, using joins and aggregation:

select fname
from faculty f
inner join class c on c.fid = f.fid
group by fname
having count(distinct c.room) = (select count(distinct room) from class)