19:57

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #55 page 2

Solution 3.11.2

Console

Execute

SELECT DISTINCT class, MIN(launched) 
FROM Ships GROUP BY Class
UNION
SELECT DISTINCT Ship AS class, NULL 
FROM Outcomes 
WHERE ship IN (SELECT class 
 FROM Classes
 ) AND 
 ship NOT IN (SELECT name 
 FROM Ships
 );

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

mistakes IN UNION DISTINCT NULL exercise 55

Previous [Exercise #54]

[Exercise #56] Next

Last added:

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 Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #55 page 2