loading..
Русский    English
21:51
go

Exercise #46 (tips and solutions)

Example of automatic join of two solutions promised in 3.1 accounting one of two moments leads to mistakes, can look like this (read comments).

Console
Execute
  1. -- Ships which had taken part in the battle of Guadalcanal and are present in the Ships table.
  2. -- Take note of using the correlative subquery in the WHERE clause
  3. -- which solve problem of removing duplicates in Cartesian product. 
  4. SELECT a.ship, displacement, numGuns
  5. FROM (SELECT ship 
  6.  FROM Outcomes 
  7.  WHERE battle = 'Guadalcanal'
  8.  ) AS a, Classes 
  9. WHERE class IN (SELECT class 
  10.  FROM Ships 
  11.  WHERE name = a.ship
  12.  )
  13. UNION
  14. -- The query is analogous to one which selects lead ships from
  15. -- Outcomes table which had actioned in battle of Guadalcanal.
  16. SELECT a.ship, displacement, numGuns
  17. FROM (SELECT ship 
  18.  FROM Outcomes 
  19.  WHERE battle = 'Guadalcanal'
  20.  ) AS a, Classes 
  21. WHERE class IN (SELECT ship 
  22.  FROM Outcomes 
  23.  WHERE ship = a.ship
  24.  )
  25. UNION
  26. -- In essence this is the solution in 3.1.1
  27. SELECT a.ship, displacement, numGuns
  28. FROM (SELECT ship 
  29.  FROM Outcomes 
  30.  WHERE battle = 'Guadalcanal'
  31.  ) AS a LEFT JOIN 
  32.  Classes ON a.ship = class;

The excessive rows like below will be return as a result:

ship displacement numGuns
California 32000 12
California NULL NULL

It`s possible to complicate this query more (and make it less efficient), by adding code for exception the wrong row. The presence of NULL-value in displacement column may be a criteria for exception, if there is one else row with the same ship`s name. However, we advice to manage without this solution and solve task by another way. It is possible, and you can make certain of it by visiting cite`s forum.

In conclusion let`s note almost right solution.

Console
Execute
  1. SELECT name, displacement, numGuns
  2. FROM Classes, Ships
  3. WHERE Classes.class = Ships.class AND 
  4.  name IN (SELECT Ship 
  5.  FROM Outcomes
  6.  WHERE battle = 'Guadalcanal'
  7.  )
  8. UNION 
  9. SELECT class, displacement, numGuns
  10. FROM Classes
  11. WHERE class IN(SELECT ship 
  12.  FROM Outcomes
  13.  WHERE battle = 'Guadalcanal'
  14.  );

The first query from union finds information about ships which is present in the Ships table and had taken part in the battle of Guadalcanal. The second one finds required lead ships in the Outcomes table. The potential duplicates (lead ship is also present in the Ships table) excepts by using UNION clause.

So what`s wrong with this solutuion? If it`s no clear for you, go back to item 3.1 task`s discussion.

To return to discussion of exercise #46

To solve a problem on SQL-EX.RU
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.