14:47

	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 #37 page 1

Find the classes including only one ship in the database (taking into account Outcomes table).

Solution 3.2.1

Here is one of the queries the checking system rejects:

Console

Execute

SELECT class
FROM Ships
GROUP BY class
HAVING COUNT(name) = 1
UNION
SELECT class
FROM Classes c, Outcomes o
WHERE c.class = o.ship AND
 NOT EXISTS (SELECT 'x' 
 FROM Ships s
 WHERE o.ship = s.class
 );

The first request in Union counts the ships of each class in the Ships table leaving only the classes that have one ship in the resulting set. The second request defines classes in which the leading ship is in the Outcomes table on condition that ships of that class are not in the Ships table.

Let's examine the following data example for which this request will provide (give) the wrong result.

Everyone who has accomplished tasks based on this data scheme (“Ships”) knows what the Bismark is. It's a leading ship which is not included in the Ships table. Now let's imagine that another ship of Bismark's class is included in the Ships table, for example, the Tirpitz.

Then the first request will return Bismark's class as the Ships table contains only one ship of that class. The second request won't return Bismark because the predicate:

NOT EXISTS (SELECT 'x' 
 FROM Ships s
 WHERE o.ship = s.class
 )

for the Bismark ship in the Outcomes table will be evaluated as FALSE. As a result of the conjunction of these queries, we'll get the Bismark class in the imprint data of the whole query.

Pages

mistakes UNION EXISTS Outcomes ships Bismarck exercise 37

Previous [Exercise #70]

[Exercise #38] 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 #37 page 1