loading..
Русский    English
16:52
go

Exercise #46 page 2

Where is this information taken from? The information about ship`s class is in Ships table, so we need to use one more table. Thus, if the ship from Outcomes table is present in the Ships table (Outcomes.ship = Ships.name), then the class is certain, and therefore all information about this ship can be taken from Classes table (Ships.class = Classes.class). Let`s consider solution with such joins:

Solution 3.1.2

Console
Execute
  1. SELECT o.ship, c.displacement, c.numGuns
  2. FROM Outcomes o LEFT JOIN 
  3.  Ships s ON o.ship = s.name LEFT JOIN 
  4.  Classes c ON s.class=c.class
  5. WHERE o.battle = 'Guadalcanal';

The result is true on basic database, but the system denies solution. The left join guarantees that the ship draws in outer dataset even if its class is unknown (there is no ship in the Ships table). In the last case such row will be returned:

  1. A ship NULL NULL

The mistake is covered in Bismarck ship. The error occures when the Outcomes table has lead ship which is absent in Ships table. Let`s suppose that Bismarck ship participates in the battle of Guadalcanal. The concerned query returns such string:

  1. Bismarck NULL NULL
that`s why this ship is absent in the Ships table. However the characteristics are certain, because the class of ship is known (the ship is lead). The right row will be:

  1. Bismarck 8 42000

Row:

  1. A ship NULL NULL
will be return if the ship which is not lead had participated in battle and it isn`t present in the Ships table. This situation might occur if the class of ship is unknown, however, this it`s impossible, because the `class` column have NOT NULL restriction.

Bookmark and Share
Pages 1 2 3
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.