loading..
Ðóññêèé    English
18:43
go

Exercise #39 (tips and solutions)

Let's consider a solution which takes into account the dates of battles, but nevertheless is not quite correct:

Console
Execute
  1. SELECT t.name 
  2. FROM (SELECT o.ship AS name, battle 
  3.       FROM Outcomes o
  4.       ) t, Battles b
  5. WHERE t.battle = b.name
  6. GROUP BY t.name
  7. HAVING (SELECT result 
  8.         FROM Outcomes, Battles 
  9.         WHERE ship = t.name AND 
  10.               battle = name AND 
  11.               date = MIN(b.date)
  12.         ) = 'damaged' AND
  13.        (SELECT result 
  14.         FROM Outcomes, Battles 
  15.         WHERE ship = t.name AND 
  16.               battle = name AND 
  17.               date = MAX(b.date)
  18.         ) IN ('damaged', 'ok', 'sunk') AND
  19.         COUNT(t.name) > 1;

In this solution, the ships participated in battles are grouped by names, then only those of them are left which match the following conditions:

  •    The ship should be damaged in battle with the minimal date;

  •    The result of battle can be any in battle with the maximal date;

  •    The number of battles should be more than one.

The logic mistake admitted in this query lies in the fact that if the ship participated more than in two battles, the ship need not be damaged in the first battle (battle with the minimal date). More precisely, the result of its battle should be 'ok', for the above solution to be ceased to give correct result. Really, the ship cannot be sunken, otherwise she would not participate in later battles. If she has been damaged, the query fairly would consider it. But if the sequence of results will be the following: 'ok', 'damaged' and any of three possible results, presented solution did not deduce such ship even if it responds conditions of a problem.

To return to discussion of exercise #39

To solve the 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.