loading..
Ðóññêèé    English
21:56
go

Exercise #55 page 3

Solution 3.11.3 (comments belong to author of solution)

Console
Execute
  1. /* 
  2. the launch year of lead ships
  3. */
  4. SELECT class, launched AS year
  5. FROM Ships
  6. WHERE name = class
  7. UNION
  8. /* 
  9.  minimal year of launch by classes
  10.  which haven`t information about lead ships in Ships table 
  11. */
  12. SELECT class, MIN(launched)
  13. FROM Ships
  14. WHERE class NOT IN (SELECT class 
  15.  FROM Ships 
  16.  WHERE name = class
  17.  )
  18. GROUP BY class
  19. UNION
  20. /*
  21.   using NULL value as launch year for absent in Ships table classes 
  22. */
  23. SELECT class, NULL
  24. FROM classes
  25. WHERE class NOT IN(SELECT class 
  26.  FROM ships
  27.  );

This solution takes into account all classes, include that which hasn`t ships in DB (the last query in union). Seemingly, mistake of this solution results from attempting to account lead ships (which is the first ships in classes, therefore they have minimal year of launch) and classes which isn`t have lead ships in DB.

Let`s imagine situation when launch year of ship is unknown (NULL value), but ship is presents in Ships table. In the mean time, Ships table contains ship with the same class with known launch year. Then just this year must be in resulted set.

But this solution returns NULL, meantime the ship with known year will be ignored due to the following filtration:

  1. WHERE class NOT IN (SELECT class 
  2.  FROM Ships 
  3.  WHERE name = class
  4.  )

Such mistake is contains in the next solution. It suggested to analyze it by your own.

Solution 3.11.4

Console
Execute
  1. SELECT class,
  2. (SELECT launched 
  3. FROM (SELECT launched 
  4. FROM Ships sh 
  5. WHERE cl.class = sh.name
  6. UNION
  7. SELECT launched 
  8. FROM Ships sh 
  9. WHERE launched = (SELECT MIN(launched) 
  10. FROM ships sh2
  11. WHERE class = cl.class AND 
  12. NOT EXISTS(SELECT launched 
  13. FROM ships sh 
  14. WHERE cl.class = sh.name
  15. )
  16. )
  17. ) tab
  18. ) year
  19. FROM classes cl

T&S

To solve the problem on SQL-EX.RU
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
https://exchangesumo.com/obmen/PPC-PRUSD-sort
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.