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

by

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.

More Related Contents:

  1. Is it possible to use greater than equal to operator in group by? [closed]
  2. Cannot update. Database or object is read-only. Error [closed]
  3. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  4. What is the difference between single and double quotes in SQL?
  5. Optimise PostgreSQL for fast testing
  6. Querying data by joining two tables in two database on different servers
  7. Are soft deletes a good idea? [duplicate]
  8. How to delete duplicate rows without unique identifier
  9. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  10. Best way to check for “empty or null value”
  11. Storing money in a decimal column – what precision and scale? [closed]
  12. What is a Covered Index?
  13. What is the difference between using a cross join and putting a comma between the two tables?
  14. When or why would you use a right outer join instead of left?
  15. PostgreSQL parameterized Order By / Limit in table function
  16. Pivot Table and Concatenate Columns
  17. Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s? [duplicate]
  18. How do I interpret precision and scale of a number in a database?
  19. What are the benefits of using database cursor?
  20. What is semi-join in database?
  21. Procedure or function !!! has too many arguments specified
  22. The backend version is not supported to design database diagrams or tables
  23. SQL query that gives distinct results that match multiple columns
  24. Android multiple databases open
  25. What is it exactly a BLOB in a DBMS context
  26. Composite Primary Keys : Good or Bad?
  27. Is there a version control system for database structure changes?
  28. Using a database table as a queue
  29. Temporal database design, with a twist (live vs draft rows)
  30. Executing SQL query on multiple databases

Leave a Comment