Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 23 October 2017 07:14:59


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

Main page

Typical errors at solution of exercises. Exercise 37

S. Moiseenko

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

Here is one of the queries the checking system rejects:

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 Terplits.

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 requests, we'll get the Bismark class in the imprint data of the whole request. 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 results of the request.

ivc_mayak published the examining request on our forum and asked to show the data on which this request will give a wrong result. Here they are,

INSERT INTO Ships VALUES( 'Terplits', 'Bismarck', 1940)

Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises.

Main page

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.