Русский    English

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

  1. SELECT DISTINCT ship FROM Outcomes os
  3. FROM Outcomes oa
  4. WHERE oa.ship = os.ship AND
  5. result = 'damaged'
  6. ) AND
  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
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 date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100