18:03

	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 2

It's clear to everybody who has followed the course of discussion attentively that there are TWO ships in the Bismark class in the database. That means, this class must not be included in the result set of the query.

To check it, add the following row into the basic database:

INSERT INTO Ships VALUES('Tirpitz', 'Bismark', 1940);

Advise:

All the basic teaching databases you can download from sqlbooks.ru.

Solution 3.2.2

The next solution was built by one of the visitors of the site after receiving the above explanation. It also gives the correct result for main database.

Console

Execute

SELECT class 
FROM Ships sh 
WHERE NOT EXISTS (SELECT ship 
 FROM Outcomes 
 WHERE ship = sh.class
 )
GROUP BY class 
HAVING COUNT(*) = 1 
UNION
SELECT ship 
FROM Outcomes s 
WHERE EXISTS (SELECT class 
 FROM Classes 
 WHERE class = s.ship
 ) AND 
 NOT EXISTS (SELECT class 
 FROM Ships 
 WHERE class = s.ship
 );

Here two queries were combined. The second query selects from the Outcomes table on condition that there exists no other ship of the given leading ship class in the Ships table.

The first query selects all ships from the Ships table except those whose leading ship is present in the Outcomes table. Next, the grouping by class was executed and ship classes with more than one ship are eliminated with the HAVING clause.

Thus, it is assumed that if the leading ship is in the Outcomes table, this class has at least two ships and, therefore, this class does not meet the conditions of the problem. That is a mistake, because nothing implies that the Ships table cannot contain a leading ship. So if a class has one ship in the database, and this ship is a head one and is present in both of the considered tables, then the solution 3.2.2 mistakenly ignores this class.

Pages

HAVING GROUP BY exercise 37 NOT EXISTS

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
month ago

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #37 page 2