04:54

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Database «Ships»

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

SELECT DISTINCT ship FROM Outcomes os
WHERE EXISTS (SELECT ship 
 FROM Outcomes oa
 WHERE oa.ship = os.ship AND 
 result = 'damaged'
 ) AND 
 EXISTS (SELECT SHIP
 FROM Outcomes ou
 WHERE ou.ship=os.ship
 GROUP BY ship 
 HAVING COUNT(battle)>1
 );

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).

Pages

mistakes EXISTS exercise 39 Outcomes Battles

Previous [Exercise #38]

[Exercise #151] Next

Last added:

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 Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #39 page 1