[Solved] Which one of these Queries are wrong and which is correct (SQL) [closed]

Question

There are several problems with Query 1.

First, why does it have trailing ))? Either they shouldn’t be there, or there are some missing ( elsewhere.

Second, the placement of the EXCEPT is wrong.

The example is this…

SELECT sname FROM ... WHERE NOT EXISTS (...)
EXCEPT
SELECT bid   FROM ...

But it appears that, for a few reasons, it wants to be this…

SELECT sname FROM ... WHERE NOT EXISTS (SELECT bid FROM ...
                                        EXCEPT
                                        SELECT bid FROM ...
                                       )

(It is currently “excepting” from the main query, not from the sub-query in the WHERE clause. Moving the EXCEPT inside the brackets fixes that.)

Finaly, even if that was fixed, it still wouldn’t help. The sub-query would then become…

NOT EXISTS (SELECT bid FROM boats B
            EXCEPT
            SELECT bid FROM reservations R WHERE R.bid = B.bid AND R.sid = S.sid
           )

Unfortunately, one query in the EXCEPT can’t reference the other.

The R.sid = S.sid is fine, because that’s an inner query referencing and outer query. (Those are known as “correlated” sub-queries – in one sense the whole correlated sub-query is executed repeatedly, once for each row in the outer query.)

But the R.bid = B.bid is “jibberish” because the rows in reservations are being processed entirely independently from the rows in boats.

Accepted Answer