loading..
Русский    English
05:59
go

Exercise #55 page 2

Solution 3.11.2

Console
Execute
  1. SELECT DISTINCT class, MIN(launched) 
  2. FROM Ships GROUP BY Class
  3. UNION
  4. SELECT DISTINCT Ship AS class, NULL 
  5. FROM Outcomes 
  6. WHERE ship IN (SELECT class 
  7.  FROM Classes
  8.  ) AND 
  9.  ship NOT IN (SELECT name 
  10.  FROM Ships
  11.  );

The first query in union counts minimal year of launch from Ships table by classes. The second query selects lead ships from Outcomes table (predicate IN) which is absent in Ships table (predicate NOT IN). Such ship takes into account with NULL year of launch, as it was done in solution 3.11.1. But current solution has other mistake. Outcomes table consists ship which is absent in Ships table (e.g. Bismarck ship) but Ships table has other ship of Bismark class with known year of launch. In this case we have two rows on the one class with different year of launch (known year and NULL value).

Solution 3.11.2 has completly excessive keyword DISTINCT. The first subquery is using GROUP BY, thats why it doesn`t have duplicates. The second subquery removes duplicates by using UNION.

Next, let`s consider solutions without using Outcomes table, but having logical mistakes yet.
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.