Русский    English

Exercise #46 page 1

Point out the names, displacements and number of guns of ships participating in the battle at Guadalcanal.

The all ships which had taken part in the battle of Guadalcanal is in the Outcomes table, and all the characteristics is in the Classes table. The first occured idea is in the joining of this tables:

Solution 3.1.1

  1. SELECT Outcomes.ship, Classes.displacement, Classes.numGuns
  2. FROM Classes RIGHT JOIN
  3. Outcomes ON Classes.class = Outcomes.ship
  4. WHERE Outcomes.battle = 'Guadalcanal';

The outer join is using correctly, because task says about ships which had taken part in battle, so we need to select all ships, no matter the name matches the class name or it does not. Note that the inner join returns empty set, because there is no lead ships in the main database which take part in this battle. So we have:

ship displacement numGuns
California NULL NULL
Kirishima NULL NULL
South Dakota NULL NULL
Washington NULL NULL

The right answer is:

Ship displacement numGuns
California 32000 12
Kirishima 32000 8
South Dakota 37000 12
Washington 37000 12

Bookmark and Share
Pages 1 2 3
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 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 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100