loading..
   English
17:33

Exercise #55 (tips and solutions)

The next solution is correctly (but excessively) takes into account ships from Outcomes table.

Console
Execute
  1. SELECT t1.class, MIN(b.launched)
  2. FROM (SELECT name, class
  3. FROM Ships
  4. UNION
  5. SELECT ship, class
  6. FROM Outcomes JOIN
  7. Classes ON ship = class AND
  8. ship NOT IN(SELECT name
  9. FROM Ships
  10. )
  11. ) t1 LEFT JOIN
  12. Ships b ON t1.class = b.class
  13. GROUP BY t1.class;

The only mistake of this solution occures in case when there is a class without ships in current state of DB. Remark that such situation is possible, because tables Classes and Ships connected by one-to-many relationships.

To return to discussion of exercise #55

To solve a problem on SQL-EX.RU


Bookmark and Share
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 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