Русский    English

Exercise #37 page 3

Solution 3.2.3

Look now, how we can more easily write a query that contains a similar mistake:

  1. SELECT class
  2. FROM (SELECT class
  3. FROM Ships
  5. SELECT ship
  6. FROM Outcomes o
  7. WHERE o.ship IN(SELECT class
  8. FROM Classes
  9. )
  10. ) AS cl
  11. GROUP BY class
  12. HAVING COUNT(class) = 1;

The idea: Ship classes from the Ships table are selected in the subquery and merged with the leading ships of the Outcomes table with preservation of duplicates (UNION ALL). This uses the fact that the name of the leading ship coincides with the class name (SELECT Ship) (!!!). The fact that the duplicates are stored is absolutely correct, because we get one row for a class for any number of ships in this class otherwise. Then grouping by class, and picking the classes that contain a single ship are being done. The solution looks much shorter and clearer than the solution 3.2.2. Correction it will be easier, but will have to be corrected, because the solution will give an incorrect result, if the leadIng ship is present both in the Ships table and in the Outcomes table, with the result that we count it twice.


It is worth paying attention to the fruitful idea of this decision - first to combine all the ships, and only then carry out the grouping by classes.

Bookmark and Share
Pages 1 2 3 4
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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.