loading..
Русский    English
21:52

Exercise #39 page 1

Define the ships that "survived for future battles"; being damaged in one battle, they took part in another.

Here is example of wrong understood condition: 

Solution 3.4.1

Console
Execute
  1. SELECT DISTINCT ship FROM Outcomes os
  2. WHERE EXISTS (SELECT ship
  3. FROM Outcomes oa
  4. WHERE oa.ship = os.ship AND
  5. result = 'damaged'
  6. ) AND
  7. EXISTS (SELECT SHIP
  8. FROM Outcomes ou
  9. WHERE ou.ship=os.ship
  10. GROUP BY ship
  11. HAVING COUNT(battle)>1
  12. );

This solution is realized in a relational calculus style, namely, the boats which were damaged (first EXISTS) and which were participated in more then one battle (second EXISTS) are being searched in the Outcomes table.

The mistake is the condition "preserved for future sea-battle" was ignored; this phrase implies, that after the battle, where the ship was damaged, it fought in LATER battle . Thus, reception of the correct decision for this task, need s to review dates of battles, which contain in table of battles (Battles table). 

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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.