Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

База данных «Корабли»

Решение 3.11.3 (с комментариями автора решения)

Консоль

Выполнить

/* 
Год спуска на воду головных кораблей
*/
SELECT class, launched AS year
FROM Ships
WHERE name = class
UNION
/* 
Минимальный год спуска на воду кораблей по классам, 
у которых нет данных по головным кораблям в таблице Ships
*/
SELECT class, MIN(launched)
FROM Ships
WHERE class NOT IN (SELECT class 
 FROM Ships 
 WHERE name = class
 )
GROUP BY class
UNION
/*
Выводим NULL в качестве года спуска на воду для классов, 
кораблей которых нет в Ships
*/
SELECT class, NULL
FROM classes
WHERE class NOT IN(SELECT class 
 FROM ships
 )

/* Год спуска на воду головных кораблей */ SELECT class, launched AS year FROM Ships WHERE name = class UNION /* Минимальный год спуска на воду кораблей по классам, у которых нет данных по головным кораблям в таблице Ships */ SELECT class, MIN(launched) FROM Ships WHERE class NOT IN (SELECT class FROM Ships WHERE name = class ) GROUP BY class UNION /* Выводим NULL в качестве года спуска на воду для классов, кораблей которых нет в Ships */ SELECT class, NULL FROM classes WHERE class NOT IN(SELECT class FROM ships )

    Схема

В этом решении в соответствии с условием задачи учтены все классы, включая те, которые не имеют кораблей в БД (последний запрос в объединении). По-видимому, допущенная здесь ошибка связана с попыткой отдельного учета головных кораблей (которые являются первыми кораблями в классе и, следовательно, имеют наименьший год спуска на воду) и классов, не имеющих головных кораблей в базе данных.

Представим ситуацию, когда год спуска головного корабля неизвестен (NULL), но при этом он имеется в таблице Ships. Там же находится другой корабль аналогичного класса с известным годом спуска на воду. Тогда именно этот год должен по условию задачи фигурировать в выходном наборе.

Данное же решение даст (первый запрос в объединении) NULL, в то время как корабль с нужным годом будет проигнорирован во втором запросе объединения в силу следующей фильтрации

WHERE class NOT IN (SELECT class 
 FROM Ships 
 WHERE name = class
 )

Аналогичная ошибка содержится и в нижеследующем варианте, который предлагается проанализировать самостоятельно.

Решение 3.11.4

Консоль

Выполнить

SELECT class,
(SELECT launched 
FROM (SELECT launched 
FROM Ships sh 
WHERE cl.class = sh.name
UNION
SELECT launched 
FROM Ships sh 
WHERE launched = (SELECT MIN(launched) 
FROM ships sh2
WHERE class = cl.class AND 
NOT EXISTS(SELECT launched 
FROM ships sh 
WHERE cl.class = sh.name
)
)
) tab
) year
FROM classes cl

SELECT class, (SELECT launched FROM (SELECT launched FROM Ships sh WHERE cl.class = sh.name UNION SELECT launched FROM Ships sh WHERE launched = (SELECT MIN(launched) FROM ships sh2 WHERE class = cl.class AND NOT EXISTS(SELECT launched FROM ships sh WHERE cl.class = sh.name ) ) ) tab ) year FROM classes cl

    Схема

ПиР

Решить задачу на SQL-EX.RU