loading..
Русский    English
04:31

Exercise #37 page 2

It's clear to everybody who has followed the course of discussion attentively that there are TWO ships in the Bismark class in the database. That means, this class must not be included in the result set of the query.

To check it, add the following row into the basic database:

  1. INSERT INTO Ships VALUES('Tirpitz', 'Bismark', 1940);

Advise:

All the basic teaching databases you can download from sqlbooks.ru.

Solution 3.2.2

The next solution was built by one of the visitors of the site after receiving the above explanation. It also gives the correct result for main database.

Console
Execute
  1. SELECT class
  2. FROM Ships sh
  3. WHERE NOT EXISTS (SELECT ship
  4. FROM Outcomes
  5. WHERE ship = sh.class
  6. )
  7. GROUP BY class
  8. HAVING COUNT(*) = 1
  9. UNION
  10. SELECT ship
  11. FROM Outcomes s
  12. WHERE EXISTS (SELECT class
  13. FROM Classes
  14. WHERE class = s.ship
  15. ) AND
  16. NOT EXISTS (SELECT class
  17. FROM Ships
  18. WHERE class = s.ship
  19. );

Here two queries were combined. The second query selects from the Outcomes table on condition that there exists no other ship of the given leading ship class in the Ships table. 

The first query selects all ships from the Ships table except those whose leading ship is present in the Outcomes table. Next, the grouping by class was executed and ship classes with more than one ship are eliminated with the HAVING clause.

Thus, it is assumed that if the leading ship is in the Outcomes table, this class has at least two ships and, therefore, this class does not meet the conditions of the problem. That is a mistake, because nothing implies that the Ships table cannot contain a leading ship. So if a class has one ship in the database, and this ship is a head one and is present in both of the considered tables, then the solution 3.2.2 mistakenly ignores this class.

Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100