Русский    English

Exercise #55 page 1

For each class, define the year in which the first ship of this class was launched. If year of launch of the head ship is unknown, define the minimum launch year for ships of this class.Result set: class, year.

It seems, that mistakes are caused by traps in previous tasks. When users solve tasks whith level one difficulty, they try to take into account all possible conditions. The taking all variants in solution into account doesn`t make it wrong, but it increases cost of execution. Besides, some such solutions contain typical mistakes, that it was decided to examine it.

First of all, users are try to take into account the lead ships from table Outcomes. The Outcomes table isn`t necessary for solution, because we need to find out the year, which is an attribute of table Ships. So even if the ship exists in the Outcomes table, but is absents in Ships table, we can`t find out its year of launch though. In case when DB hasn`t  other ships of this class the existance of lead ship draw a blank at all, because the result must be the such:

class   NULL
as the task says "for each class", it means that launch year is unknown for this ship class. This result can be obtained by using the outer join for tables Classes and Ships.

Let`s move to the analysis of solutions having mistakes with accounting the Outcomes table.

Solution 3.11.1

  1. SELECT C.class , launched
  2. FROM Classes C ,
  3. (SELECT name , class , launched
  4. FROM Ships
  5. UNION
  6. SELECT ship , ship , NULL
  7. FROM Outcomes O
  9. FROM Ships S
  10. WHERE S.class = O.ship
  11. )
  12. UNION
  13. SELECT ship , ship , MIN(launched)
  14. FROM Ships S ,
  15. Outcomes O
  16. WHERE S.class = O.ship
  17. GROUP BY ship
  18. ) S
  19. WHERE C.class = S.name;

Take a look at subquery S. It has union of three queries with {ship name, class, year of launch} as resulting set of attributes. The first query selects all ships from Ships table. The second one selects ships from Outcomes table which have name doesn`t matches any of classes from Ships table. The NULL value is using as the year of launch, that`s correct. The ship name is associates with class name (SELECT ship, ship, NULL). It`s correct because this query selects lead ships:

  1. WHERE C.class = S.name

Finally, third query determines minimal launch year for shipses classes having lead ships in Outcomes table.

The mistake occures when lead ship from Outcomes table exists in Ships table as well and its year of launch is unknown. Then the first query returns row with year is NULL for this class and the third one with minimal launch year. Totally we have two non-duplicate rows, therefore they haven`t been excluded by using UNION.

Also, the select condition by lead ships automatically excludes ships which doesn`t have lead ships at all.

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