лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Упражнение 11 (подсказки и решения) Наличие стандартной агрегатной функции AVG решает все проблемы: 1. SELECT AVG ( speed ) 2. FROM PC; Однако на форуме сайта были замечены попытки выделиться из общей «серой» массы. Вот вполне эквивалентное решение, которое, правда, добавляет лишнюю операцию в процедурный план: 1. SELECT SUM ( speed ) / COUNT ( speed ) 2. FROM PC; Справедливость следующего решения зависит от имеющихся ограничений: 1. SELECT SUM ( speed ) / SUM ( 1 ) 2. FROM PC; А именно, если SUM(speed) игнорирует строки с NULL-значением скорости, то SUM(1) подсчитает общее количество строк в таблице PC, что, по существу, эквивалентно использованию COUNT(*). В результате в числителе будет подсчитана сумма скоростей всех ПК за исключением строк с неизвестной скоростью, которая будет делиться на общее число строк. Поэтому все приведенные решения будут эквивалентны, только если NULL- значения недопустимы, то есть имеется ограничение NOT NULL на столбце speed. Упражнение 15 (подсказки и решения) Неверное решение 1.11.1 этой задачи легко поправить, если различать ПК не по номеру модели, а, как и положено, по первичному ключу — столбцу code: 1. SELECT DISTINCT t.hd 2. FROM PC t 3. WHERE EXISTS ( SELECT * 4. FROM PC 5. WHERE pc.hd = t.hd AND 6. pc.code <> t.code 7. ) ; Поскольку достаточно лишь двух ПК с одинаковыми жесткими дисками, можно использовать самосоединение таблицы PC по аналогичным условиям: 1. SELECT DISTINCT pc1.hd 2. FROM PC pc1, PC pc2 3. WHERE pc1.hd = pc2.hd AND 4. pc1.code <> pc2.code; Однако наиболее оптимальным будет решение с группировкой и условиями отбора в предложении HAVING: 1. SELECT 2. PC.hd FROM PC 3. GROUP BY hd 4. HAVING COUNT ( hd ) > 1 ; Для полноты картины приведем еще одно решение, которое использует подзапрос с группировкой и которое по эффективности также уступает вышеприведенному. 1. SELECT DISTINCT hd 2. FROM PC 3. WHERE ( SELECT COUNT ( hd ) 4. FROM PC pc2 5. WHERE pc2.hd = pc.hd 6. ) > 1 ; Причина низкой эффективности рассмотренных решений с подзапросами заключается в том, что все они используют коррелирующий подзапрос, то есть подзапрос, который будет выполняться для каждой строки основного запроса. Запрос с соединением имеет самую низкую производительность. Это вполне понятно, так как операции соединения весьма затратные, несмотря на достаточно эффективные алгоритмы их реализации. Упражнение 16 (подсказки и решения) Избыточность решения 1.12.2 можно устранить, если вообще убрать подзапрос, а соединение выполнить между таблицами P и L. При этом запрос получится не только компактным, но и легко читаемым и, что не менее важно, более эффективным. Еще один пример тяжело читаемого запроса, который был бы правильным, будь номер модели (model) числовым. 1. SELECT MAX ( model1 ) , MIN ( model2 ) , MAX ( speed ) , MAX ( ram ) 2. FROM ( SELECT pc1.model AS model1, pc2.model AS model2, pc1.speed, pc2.ram, 3. CASE WHEN CAST ( pc1.model AS NUMERIC ( 6 , 2 )) > 4. CAST ( pc2.model AS NUMERIC ( 6 , 2 )) 5. THEN pc1.model+pc2.model 6. ELSE pc2.model+pc1.model 7. END AS sm 8. FROM PC pc1, PC pc2 9. WHERE pc1.speed = pc2.speed AND 10. pc1.ram = pc2.ram AND 11. pc1.model <> pc2.model 12. ) a 13. GROUP BY a.sm; Однако тип данных VARCHAR(50) подразумевает наличие произвольных символов, наличие которых имеет место в проверочной базе данных (скажем, T-64). На этих данных преобразование типа 1. CAST ( pc1.model AS NUMERIC ( 6 , 2 )) будет вызывать ошибку. Я считаю, что это хороший пример того, как не следует писать запросы. Как же следует их писать? Загляните на форум задачи, когда её решите, там вы найдете лучшие образцы. Упражнение 17 (подсказки и решения) Итак, избавляемся от декартового произведения Для этого убираем таблицу PC из предложения FROM, а таблицы Product и Laptop соединяем по столбцу model: 1. SELECT DISTINCT type, Laptop.model, speed 2. FROM Laptop, Product 3. WHERE Product.model = Laptop.model AND 4. Laptop.speed < ( SELECT MIN ( speed ) FROM PC ) ; Условие p.type = 'laptop' уже излишне, так как внутреннее соединение будет содержать модели только данного типа. Оказывается, что можно избавиться и от соединения, поскольку таблица Product используется только для того, чтобы в результирующем наборе вывести тип продукта. Но тип продукта заранее известен — это портативный компьютер, поэтому мы может просто применить выражение (константу) для указания типа, убрав соединение: 1. SELECT DISTINCT 'Laptop' AS type, model, speed 2. FROM Laptop 3. WHERE speed < ( SELECT MIN ( speed ) FROM PC ) ; Заметим, что данное решение будет справедливо только в том случае, если в таблице Laptop будут находиться изделия с типом Laptop. Для нашей базы данных это условие выполняется, т. к. имеется всего три типа продукции и соответственно три таблицы. Поэтому нарушение данного условия может быть связано только с изменением структуры, что, впрочем, тоже следует иметь в виду при разработке приложений со встроенными SQL-запросами. Упражнение 18 (подсказки и решения) В следующем решении: 1. SELECT c.maker, a.priceA price 2. FROM ( SELECT MIN ( price ) priceA 3. FROM Printer 4. WHERE Color = 'y' 5. ) a INNER JOIN 6. Printer b ON a.priceA = b.price INNER JOIN 7. Product c ON b.model = c.model; в подзапросе определяются минимальная цена на цветные принтеры, затем выполняется соединение по этой цене с таблицей принтеров, чтобы найти все принтеры с такой ценой. Наконец, соединение с таблицей Product дает производителей найденных принтеров. Конечно, соединение по цене можно заменить простым сравнением 1. WHERE price = ( SELECT MIN ( price ) priceA 2. FROM Printer 3. WHERE Color = 'y' 4. ) Однако ошибка не в этом, а в том, что отыскиваются любые принтеры, у которых цена совпадает с минимальной ценой на цветные принтеры, что, собственно говоря, и имеет место. В результате получаем: Maker Price D 270.0 A 270.0 Правильный же ответ дает только одну строку: Maker Price D 270.0 Вторая строка не является цветным принтером, в этом легко убедиться, если добавить в предложение SELECT дополнительные столбцы: 1. SELECT c.maker, a.priceA price, color, b.type что дает maker price color type D 270.0 y Jet A 270.0 n Matrix Вторая ошибка заключается в возможном наличии дубликатов, т. к. для одного и того же производителя может иметься несколько принтеров по одной и той же цене. Упражнение 23 (подсказки и решения) Для решения этой задачи используется обычно два подхода: соединение с последующим выбором нужной строки и проверка на попадание производителя в два списка. Следующее неправильное решение, реализует первый подход: Решение 4.11.1 1. SELECT DISTINCT a.maker 2. FROM Product a LEFT JOIN 3. PC b ON a.model = b.model AND 4. b.speed> = 750 LEFT JOIN 5. Laptop c ON a.model = c.model AND c.speed> = 750 6. WHERE NOT ( b.model IS NULL AND 7. c.model IS NULL 8. ) ; Это еще один вариант на тему «чего-то одного». Действительно, модель уникальна, т. е. она представляет собой либо ПК, либо портативный компьютер. В результате внешних соединений получаются строки типа: 1. maker model ( PC ) NULL или 1. maker NULL model ( laptop ) Однако здесь не может быть строк: 1. maker model ( PC ) model ( laptop ) поскольку соединение выполняется по номеру модели. В результате в списке имеются производители, которые выпускают только один вид продукции с требуемыми характеристиками. Однако подправить это решение просто, добавив группировку по производителю и посчитав модели. Предлагаем вам сделать это самостоятельно. Решение 4.11.2 Для демонстрации второго подхода рассмотрим следующий вариант: 1. SELECT DISTINCT t.maker 2. FROM Product t 3. WHERE ( t.model IN ( SELECT model 4. FROM PC 5. WHERE speed >= 750 6. ) OR 7. t.model IN ( SELECT model 8. FROM Laptop 9. WHERE speed >= 750 10. ) 11. ) AND 12. EXISTS ( SELECT * 13. FROM Product 14. WHERE Product.maker = t.maker AND 15. Product.type= 'PC' 16. ) AND 17. EXISTS ( SELECT * 18. FROM Product 19. WHERE Product.maker = t.maker AND 20. Product.type= 'Laptop' 21. ) ; который можно прочитать следующим образом: найти производителя, который выпускает ПК со скоростью не менее 750 МГц или портативный компьютер со скоростью не менее 750 МГц; при этом данный производитель должен производить ПК и портативные компьютеры. Несомненный прогресс этого решения по сравнению с решением 1.15.1 состоит в том, что выводятся производители, как ПК, так и портативных компьютеров. Однако это решение допускает вариант, когда производитель выпускает только ПК со скоростью более 750 МГц, в то время как все его портативные компьютеры имеют скорость менее 750 МГц, и наоборот. Упражнение 26 (подсказки и решения) Ниже приводится еще пара неверных решений этой задачи, которые содержат легко исправимую ошибку. Решение 4.12.1 1. SELECT AVG ( price ) 2. FROM ( SELECT price 3. FROM PC 4. WHERE model IN ( SELECT model 5. FROM product 6. WHERE maker= 'A' AND 7. type= 'PC' 8. ) 9. UNION 10. SELECT price 11. FROM Laptop 12. WHERE model IN ( SELECT model 13. FROM product 14. WHERE maker= 'A' AND 15. type= 'Laptop' 16. ) 17. ) AS prod; Решение 4.12.2 1. SELECT AVG ( price ) 2. FROM ( SELECT price, model 3. FROM pc 4. WHERE model IN ( SELECT model 5. FROM product 6. WHERE maker= 'A' AND 7. type= 'PC' 8. ) 9. UNION 10. SELECT price, model 11. FROM Laptop 12. WHERE model IN ( SELECT model 13. FROM product 14. WHERE maker= 'A' AND 15. type= 'Laptop' 16. ) 17. ) AS prod; Первое из этих решений дает на основной базе результат 772.5, а второе — 773.0 при правильном значении 734.5454545454545. В запросе 4.12.1 выбираются цены на все модели производителя А из таблицы PC. Затем они объединяются с ценами на все модели производителя А из таблицы Laptop. Наконец, вычисляется среднее значение. Что же тут неправильного? Ошибка состоит в том, как объединяются цены. Оператор UNION исключает дубликаты, поэтому из нескольких одинаковых цен (если таковые имеются) будет оставаться только одна. Как результат, среднее будет посчитано по неверному количеству. В запросе 4.12.2 выбирается не только цена, но и номер модели. То есть объединение выполняется по паре атрибутов. Это решение было бы правильным, если бы в соответствующей таблице не было одинаковых моделей с одинаковыми ценами. Последнее было бы гарантировано, если бы пара атрибутов {price, model} являлась первичным ключом. Однако согласно нашей схеме это не так. Сама по себе такая ситуация не является нереальной. Представим себе, что одна модель комплектуется большим диском, чем другая модель с тем же номером, а памяти, наоборот, имеет меньше. Тогда цены у них вполне может быть одинаковы. Естественно, может быть несколько идентичных моделей. В результате объединения будут исключены дубликаты пар {price, model} и, как следствие, получен неверный результат. Надеемся, теперь вполне очевидно, как следует решать эту задачу. Упражнение 27 (подсказки и решения) Смотрите упражнение номер 2 Упражнение 30 (подсказки и решения) Чтобы решить проблему удвоения, утроения результатов, достаточно сначала выполнить соответствующую группировку по каждой таблице, а уже затем их соединять. Тогда эта задача сводится к задаче 29, решение которой не вызывало проблем и потому не приводится в книге. Это не единственный способ решить задачу. Загляните на форум сайта , чтобы познакомиться с другими вариантами. Упражнение 46 (подсказки и решения) Обещанный в пункте 3.1 пример автоматического соединения двух решений, каждое из которых правильно учитывает один из двух моментов, вызывающих характерные ошибки, может выглядеть так (смотрите комментарии) 1. -- Корабли, участвующие в битве при Гвадалканале и которые есть в Ships 2. -- Обратите внимание на использование коррелирующего подзапроса в 3. -- предложении WHERE, который решает проблему устранения дубликатов при 4. -- декартовом произведения 5. SELECT a.ship, displacement, numGuns 6. FROM ( SELECT ship 7. FROM Outcomes 8. WHERE battle = 'Guadalcanal' 9. ) AS a, Classes 10. WHERE class IN ( SELECT class 11. FROM Ships 12. WHERE name = a.ship 13. ) 14. UNION 15. -- Аналогичный по логике запрос, который выбирает те головные корабли из 16. -- Outcomes, которые сражались при Гвадалканале. 17. SELECT a.ship, displacement, numGuns 18. FROM ( SELECT ship 19. FROM Outcomes 20. WHERE battle = 'Guadalcanal' 21. ) AS a, Classes 22. WHERE class IN ( SELECT ship 23. FROM Outcomes 24. WHERE ship = a.ship 25. ) 26. UNION 27. --По сути, это решение 3.1.1 28. SELECT a.ship, displacement, numGuns 29. FROM ( SELECT ship 30. FROM Outcomes 31. WHERE battle = 'Guadalcanal' 32. ) AS a LEFT JOIN 33. Classes ON a.ship = class; В результате получим лишние строки, характерным примером которых являются такие: ship displacement numGuns California 32000 12 California NULL NULL Можно еще утяжелить этот запрос (и сделать его менее эффективным), добавив код для исключения ошибочной строки. Критерием здесь может служить присутствие NULL-значения, например, в столбце displacement, если есть другая строка с тем же именем корабля. Однако мы советуем обойтись без этого и решить задачу иначе. Тем более, что это возможно, в чем легко убедиться, зайдя на форум сайта, посвященный этой задаче. В заключение приведем почти правильное решение: 1. SELECT name, displacement, numGuns 2. FROM Classes, Ships 3. WHERE Classes.class = Ships.class AND 4. name IN ( SELECT Ship 5. FROM Outcomes 6. WHERE battle = 'Guadalcanal' 7. ) 8. UNION 9. SELECT class, displacement, numGuns 10. FROM Classes 11. WHERE class IN ( SELECT ship 12. FROM Outcomes 13. WHERE battle = 'Guadalcanal' 14. ) ; Первый запрос из объединения в этом решении находит информацию о кораблях, которые есть в таблице Ships и которые принимали участие в сражение при Гвадалканале. Второй запрос находит нужные нам головные корабли в Outcomes. Возможные дубликаты (когда головной корабль имеется также и в таблице Ships) исключаются использованием предложения UNION. Так что же здесь неверно? Если до сих пор непонятно, вернитесь к обсуждению задачи в пункте 3.1 Упражнение 37 (подсказки и решения) Рассмотрим следующее решение задачи, которое свободно от ошибок, проанализированных в пункте 3.2 : 1. SELECT t1.class 2. FROM ( SELECT a.class AS class, COUNT ( b.name ) AS coun 3. FROM Classes a LEFT JOIN 4. Ships b ON b.class = a.class 5. GROUP BY a.class 6. UNION ALL 7. SELECT a1.class AS class, COUNT ( ship ) AS coun 8. FROM Classes a1 LEFT JOIN 9. Outcomes d ON d.ship = a1.class 10. WHERE d.ship NOT IN ( SELECT b.name 11. FROM Ships b 12. ) 13. GROUP BY a1.class 14. ) t1 15. GROUP BY t1.class 16. HAVING SUM ( t1.coun ) = 1 ; Действительно, в подзапросе объединяются два запроса, первый из которых подсчитывает для каждого класса корабли из таблицы Ships, а второй подсчитывает только те головные корабли, которых нет в Ships. Затем основной запрос для каждого класса эти количества суммирует и оставляет только те классы, в которых содержится только один корабль. Обратите внимание на использование UNION ALL . Это необходимо, т. к. в противном случае будут устранены дубликаты пары {класс, количество кораблей}, в результате чего будет выводиться класс, для которого имеется один неголовной корабль в Ships и головной в Outcomes. Как раз эта характерная ошибка отмечалась нами в пункте 3.2 Что же осталось исправить, раз это решение не принимается системой? Причина состоит в том, что головной корабль класса может принимать участие в нескольких сражениях, и тогда второй из объединяемых запросов столько раз учтет один и тот же головной корабль, сколько раз тот участвовал в сражениях. Упражнение 39 (подсказки и решения) Рассмотрим решение, которое учитывает даты сражений, но все же является не вполне верным: 1. SELECT t.name 2. FROM ( SELECT o.ship AS name, battle 3. FROM Outcomes o 4. ) t, Battles b 5. WHERE t.battle = b.name 6. GROUP BY t.name 7. HAVING ( SELECT result 8. FROM Outcomes, Battles 9. WHERE ship = t.name AND 10. battle = name AND 11. date = MIN ( b.date ) 12. ) = 'damaged' AND 13. ( SELECT result 14. FROM Outcomes, Battles 15. WHERE ship = t.name AND 16. battle = name AND 17. date = MAX ( b.date ) 18. ) IN ( 'damaged' , 'ok' , 'sunk' ) AND 19. COUNT ( t.name ) > 1 ; В этом решении участвовавшие в сражениях корабли группируются по именам, после чего остаются только те которые отвечают следующим условиям: в сражении с минимальной датой корабль должен быть поврежден; в сражении с максимальной датой результат сражения может быть любым; число сражений должно быть больше одного. Логическая ошибка, допущенная в этом запросе, заключается в том, что если корабль участвовал более чем в двух сражениях, то в первом своем сражении (сражении с минимальной датой) корабль может и не быть поврежден. Более точно, результат его сражения должен быть ok, чтобы представленное выше решение перестало давать правильный результат. Действительно, потоплен корабль быть не может, иначе бы он не участвовал в более поздних сражениях. Если бы он был поврежден, то запрос его бы справедливо учитывал. А вот если последовательность результатов будет следующей: ok, damaged и любой из трех возможных, то представленное решение его бы не выводило, хотя такой корабль и отвечает условиям задачи. Упражнение 51 (подсказки и решения) Здесь мы хотим привести одно интересное решение, которое использует только соединения: 1. SELECT DISTINCT CASE 2. WHEN sh.name IS NOT NULL 3. THEN sh.name 4. ELSE ou.ship 5. END name 6. FROM Ships sh FULL JOIN 7. Outcomes ou ON ou.ship = sh.name LEFT OUTER JOIN 8. Classes cl ON ou.ship = cl.class OR 9. sh.class = cl.class LEFT OUTER JOIN 10. Classes cl2 ON cl.displacement = cl2.displacement AND 11. cl.numGuns < cl2.numGuns 12. WHERE cl.class IS NOT NULL AND 13. cl2.class IS NULL ; Достаточно сложная логика этого решения будет, безусловно, полезна для обучения на данном этапе. Начнем, как обычно, с предложения FROM. Полное соединение (FULL JOIN) дает нам все корабли из базы данных. При этом возможны следующие варианты: корабль присутствует только в таблице Ships; корабль присутствует в обеих таблицах; корабль присутствует только в таблице Outcomes. Этим трем случаям соответствуют следующие строки результирующего набора (показаны только значимые в данном случае столбцы): Ship name NULL ship_1 ship_2 ship_2 ship_3 NULL Затем выполняется левое соединение с таблицей классов по предикату: 1. ou.ship = cl.class OR 2. sh.class = cl.class То есть либо имя корабля из Outcomes должно совпадать с именем класса (висящие головные корабли), либо класс корабля из таблицы Ships. Результат соединения можно проиллюстрировать следующей таблицей: ship name sh.class cl.class NULL ship_1 class_1 class_1 ship_2 ship_2 class_1 class_1 ship_3 NULL NULL NULL class_1 NULL class_1 class_1 Третья строка таблицы соответствует случаю, когда класс корабля неизвестен (он не является головным!), а четвертая описывает случай головного корабля, отсутствующего в таблице Ships. Забегая немного вперед, заметим, что третья строка не может нам дать никакой информации о водоизмещении и числе орудий такого корабля, поэтому она отфильтровывается следующим предикатом в предложении WHERE рассматриваемого решения: 1. cl.class IS NOT NULL Наконец, последнее левое соединение опять выполняется с таблицей классов, но уже по другому предикату: 1. cl.displacement = cl2.displacement AND 2. cl.numGuns < cl2.numGuns Первое условие предиката очевидно — мы соединяем строки по равенству значений водоизмещения, так как нам нужно находить максимум в группе кораблей, имеющих одинаковое водоизмещение. Чтобы понять смысл второго условия, опять обратимся к примеру. Дополним нашу таблицу необходимыми столбцами и рассмотрим результат соединения по данному предикату на примере, скажем, первой строки предыдущей таблицы при следующих значениях числа орудий у классов кораблей водоизмещением 30 000 тонн: class_1 16 class_2 10 class_3 14 shi p Nam e sh.clas s cl.clas s displacemen t cl2.clas s cl.numGun s cl2.numGun s NUL L ship_1 class_1 class_1 30000 NULL 16 NULL NUL L ship_2 class_2 class_2 30000 class_1 10 16 NUL L ship_2 class_2 class_2 30000 class_3 10 14 NUL L ship_3 class_3 class_3 30000 class_1 14 16 Таким образом, корабли класса, имеющего максимальное число орудий в свой группе по водоизмещению, будут иметь NULL-значение в столбцах, относящихся к таблице cl2 (при левом соединении!), в том числе в столбце cl2.class, так как предикат не удовлетворяется. Именно этот критерий и используется в предложении WHERE для отбора записей, отвечающих условиям задачи (cl2.class IS NULL). Наконец, оператор CASE формирует имя корабля в окончательном результирующем наборе, выбирая из двух вариантов — столбцы ship или name — тот, в котором находится не NULL-значение. Если изложенное выше вам не вполне понятно, почитайте о внешних соединениях в главе 5 ( пункт 5.6.1 ). Интересное решение, но учитывающее не все возможные варианты данных. Оно не будет давать верный результат, если имеется класс, число орудий на кораблях которого, нам не известно. Обратите внимание на схему данных: столбец numGuns допускает NULL-значения! Предлагаем вам самостоятельно проанализировать причину ошибки и исправить рассмотренное решение. Упражнение 53 (подсказки и решения) Вот решение, в котором сделана попытка выполнить округление: 1. SELECT CAST ( ROUND ( AVG ( numguns ) , 2 ) AS DECIMAL ( 10 , 2 )) Avg_numGuns 2. FROM Classes 3. WHERE type = 'bb' ; Попытка неудачная, так как округление , как и последующее приведение к типу DECIMAL (впрочем, совершенно излишнее в данном контексте), уже применяется к значению целого типа. В результате получить удается не более чем два нуля после десятичной точки. Проблема заключается в том, что AVG(numguns) применяется к аргументу целого типа, поэтому и результат приводится к целому, причем дробная часть не округляется, а отбрасывается, что является особенностью SQL Server, и не является общим правилом. Упражнение 54 (подсказки и решения) Чтобы написать UNION в решении 3.10.2, недостаточно выполнять объединение по одному столбцу numGuns. Список столбцов должен быть таким, чтобы он однозначно определял корабль. Тогда действительно исключаться будут дубликаты, а не полезная информация. Ниже представлено решение, которое использует этот прием, но содержит незначительную ошибку, поиск которой предоставляем читателям. 1. SELECT CAST ( AVG ( numguns* 1.0 ) AS NUMERIC ( 6 , 2 )) 2. FROM ( SELECT ship, type, numguns 3. FROM Outcomes RIGHT JOIN 4. Classes ON ship = class 5. UNION 6. SELECT name, type, numguns 7. FROM Ships s JOIN 8. Classes c ON c.class = s.class 9. ) AS al_sh 10. WHERE type = 'bb' ; Упражнение 55 (подсказки и решения) Здесь мы приводим решение, которое верно (хотя и излишне) учитывает корабли из таблицы Outcomes. 1. SELECT t1.class, MIN ( b.launched ) 2. FROM ( SELECT name, class 3. FROM Ships 4. UNION 5. SELECT ship, class 6. FROM Outcomes JOIN 7. Classes ON ship = class AND 8. ship NOT IN ( SELECT name 9. FROM Ships 10. ) 11. ) t1 LEFT JOIN 12. Ships b ON t1.class = b.class 13. GROUP BY t1.class; Единственная ошибка этого решения заключается в том, что не учтена ситуация, когда имеется класс, для которого нет кораблей в текущем состоянии базы данных. Заметим, что это допускается схемой, имеющей связь «один-ко-многим» между таблицами Classes и Ships. Упражнение 56 (подсказки и решения) Ниже приведено решение, в котором правильно проведен учет потопленных кораблей. Незначительную ошибку в этом решении предлагаем найти самостоятельно. В случае затруднения вернитесь к решению 3.12.3 1. SELECT class, SUM ( r ) sunks 2. FROM ( SELECT name, class, CASE 3. WHEN result = 'sunk' 4. THEN 1 ELSE 0 5. END r 6. FROM Ships AS s LEFT JOIN 7. Outcomes AS o ON o.ship = s.name 8. UNION 9. SELECT ship, class, CASE 10. WHEN result = 'sunk' 11. THEN 1 ELSE 0 12. END r 13. FROM Classes c JOIN 14. ( SELECT * 15. FROM Outcomes 16. WHERE NOT Ship IN ( SELECT name 17. FROM Ships ) 18. ) AS ot ON ot.ship = c.class 19. ) AS b GROUP BY class; Упражнение 57 (подсказки и решения) Ниже представлено решение, в котором правильно определяется число потопленных кораблей (хотя, на наш взгляд, весьма громоздко), однако неправильно подсчитывается общее число кораблей в классе. 1. SELECT f.class, SUM ( count_out ) AS cnt 2. FROM ( SELECT t.class, SUM ( cnt ) AS count_out 3. FROM ( SELECT c.class, ship, COUNT ( * ) CNT 4. FROM Classes c LEFT JOIN 5. Ships s ON c.class = s.class INNER JOIN 6. Outcomes o ON o.ship = s.name AND 7. result = 'sunk' 8. GROUP BY c.class,ship 9. ) AS t 10. GROUP BY t.class 11. UNION ALL 12. SELECT t.class, SUM ( cnt ) AS count_out 13. FROM ( SELECT c.class, ship, COUNT ( * ) cnt 14. FROM Classes c INNER JOIN 15. Outcomes o ON c.class = o.ship AND 16. o.result = 'sunk' AND 17. NOT EXISTS ( SELECT * 18. FROM Ships 19. WHERE o.ship = name 20. ) 21. GROUP BY c.class,ship 22. ) AS t 23. GROUP BY t.class 24. ) AS f 25. GROUP BY f.class 26. HAVING 2 < ( SELECT SUM ( cnt ) 27. FROM ( SELECT COUNT ( c.class ) AS cnt 28. FROM Classes c, Ships s 29. WHERE c.class = s.class AND 30. c.class = f.class 31. UNION 32. SELECT COUNT ( c.class ) AS cnt 33. FROM Classes c, Outcomes o 34. WHERE c.class = o.ship AND 35. c.class = f.class AND 36. NOT EXISTS ( SELECT * 37. FROM Ships 38. WHERE o.ship = name 39. ) 40. ) AS k 41. ) ; Подсчет общего числа кораблей в классе выполняется здесь в предложении HAVING основного запроса. В подзапросе этого предложения для каждого класса из основного запроса выполняется объединение числа кораблей из таблицы Ships с числом кораблей (головных) из таблицы Outcomes при условии, что такие корабли не были учтены ранее (их нет в таблице Ships). Видно, что поскольку объединяются одноатрибутные отношения посредством оператора UNION, то если у нас имеется по одному кораблю и в одном, и другом наборе, мы заведомо получаем неверный результат в результате устранения дубликатов. Однако здесь это как бы не должно являться ошибкой, так как мы отбираем классы, имеющие в сумме более двух кораблей. А других возможных вариантов быть не должно, поскольку головной корабль если и есть, то он только один (несмотря на излишнее использование COUNT во втором запросе). И все же ошибка кроется именно здесь. Дело в том, что если головной корабль принимал участие более чем в одном сражении, то мы его учитываем по числу сражений, разумеется, если его нет в таблице Ships. Исправить это решение несложно, предлагаем вам сделать это самостоятельно. Однако можно написать и более простой (а также более эффективный) запрос. Упражнение 59 (подсказки и решения) В решении 2.2.1 не учитывается тот факт, что возможна ситуация, когда на некотором пункте приема может быть только приход, но не быть расхода, то есть в таблице Outcome_o может не быть ни одной строки, относящейся к данному пункту приема. С точки зрения предметной области эта ситуация возможна для вновь открытого пункта приема, когда факт приема денежных средств уже зафиксирован, а факт расхода — еще нет. Тогда выражение в предложении SELECT 1. ss.inc - dd.out для такой точки приема станет эквивалентно выражению 1. ss.inc - NULL что даст в результате NULL, а не ss.inc, как это должно быть по условиям задачи. Исправить решение очень легко, переписав ошибочное выражение в виде 1. ( COALESCE ( ss.inc, 0 ) - COALESCE ( dd.out, 0 ) ) что соответствует стандарту, или с помощью функции SQL Server ISNULL: 1. ( ISNULL ( ss.inc, 0 ) - ISNULL ( dd.out, 0 ) ) Упражнение 60 (подсказки и решения) В решении 2.3.1 используется полное внешнее соединение (FULL JOIN) подзапросов, чтобы учесть возможные варианты, когда в результате выполнения этих подзапросов для какого-нибудь пункта приема либо сумма прихода, либо сумма расхода будет NULL-значением (другими словами, не было расхода и/или прихода). Если, скажем, полученный приход составляет 1000, а расход — 800, то будут учтены все возможные варианты: Приход Расход 1000 800 NULL 800 1000 NULL Варианта NULL NULL быть не может, так как это бы означало, что пункта приема просто не существовало (на данный момент времени). В предложении SELECT используется конструкция, которая должна заменить NULL нулем в выражении вычисления остатка. Логика совершенно правильная, однако конструкция применена неверно: 1. CASE inc 2. WHEN NULL 3. THEN 0 4. ELSE inc 5. END Ошибка заключается в том, что здесь фактически задействована простая операция сравнения с NULL-значением, а именно, 1. CASE 2. WHEN inc = NULL 3. THEN 0 4. ELSE inc 5. END Сравнение же с NULL-значением всегда дает UNKNOWN. Поэтому условие WHEN не выполняется, в результате чего выполняется ветвь ELSE, всегда возвращая значение inc, даже в том случае, когда inc есть NULL. Упражнение 70 (подсказки и решения) Здесь мы хотим рассмотреть вполне прозрачное решение, содержащее две незначительные ошибки. Вот оно (см. комментарии): 1. SELECT q.battle 2. FROM ( 3. --Определяем корабли из Ships участвовавшие в битвах 4. SELECT Outcomes.battle, Outcomes.ship, Classes.country 5. FROM Classes INNER JOIN 6. Ships ON Classes.class = Ships.class INNER JOIN 7. Outcomes ON Ships.name = Outcomes.ship 8. UNION 9. --Определяем головные корабли из Outcomes 10. SELECT Outcomes.battle, Outcomes.ship, Classes.country 11. FROM Outcomes INNER JOIN 12. Classes ON Outcomes.ship = Classes.class 13. ) AS q 14. GROUP BY q.battle 15. HAVING COUNT ( q.country ) >= 3 ; Надеемся, что вы без труда их найдете и исправите. Упражнение 121 (подсказки и решения) Беда с этими головными кораблями! Вот как иногда неправильно решается вопрос о головных кораблях, спущенных на воду ранее 1941 года: 1. SELECT class 2. FROM Classes 3. WHERE EXISTS ( SELECT 1 4. FROM Ships 5. WHERE launched < 1941 AND 6. Ships.class = Classes.class 7. ) ; То есть здесь класс отождествляется с наличием головного корабля в БД, а именно, разыскивается класс, который имеет в БД корабль, спущенный на воду ранее 1941 года. Однако из описания предметной области следует, что не всегда есть корабль, имя которого совпадает с именем класса. Поэтому искать неучтенные в Ships головные корабли следует исключительно в таблице Outcomes. Наконец, о неучтенном варианте в решении 5.3.3 . Итак, возможна следующая ситуация. Имеется головной корабль с неизвестным годом спуска на воду. Более того, он может участвовать только в сражениях после 1941 года. Пусть для всех других корабли того же класса год спуска на воду тоже неизвестен (это допускается схемой данных). Однако, если хотя бы один из этих кораблей участвовал в сражении до 1941 года, то нам следует такой корабль включить в результирующий набор вместе с головным, так как головной корабль (если он есть!) должен быть спущен на воду ранее любого другого корабля аналогичного класса. Вот решение, которое, казалось бы, учитывает все оговоренные моменты: 1. -- Корабли, спущенные на воду до 1941 года 2. SELECT name 3. FROM Ships 4. WHERE launched < 1941 5. 6. UNION 7. -- Корабли, принимавшие участие в сражениях до 1941 года 8. SELECT ship 9. FROM Outcomes JOIN 10. Battles ON Battles.name = Outcomes.battle 11. WHERE date < '19410101' 12. 13. UNION 14. -- Головные корабли из Outcomes, в классе которых есть другие корабли, 15. -- спущенные на воду до 1941 года 16. SELECT ship 17. FROM Outcomes 18. WHERE ship IN ( SELECT class 19. FROM Ships 20. WHERE launched < 1941 21. ) 22. 23. UNION 24. -- Головные корабли из Outcomes при условии, что хотя бы один из кораблей 25. -- того же класса, участвовал в сражении до 1941 года 26. SELECT ship 27. FROM Outcomes 28. WHERE Ship IN ( SELECT class 29. FROM Ships JOIN 30. Outcomes ON Ships.name = Outcomes.ship JOIN 31. Battles ON Battles.name = Outcomes.battle 32. WHERE date < '19410101' 33. ) ; Однако система все равно сообщает об ошибке… Как уже было отмечено в пункте 3.5 , головные корабли с неизвестным годом спуска на воду могут находиться не только в таблице Outcomes, но и в таблице Ships. Более того, такие корабли не будут учтены рассматриваемым запросом, если их нет в таблице Outcomes, то есть они либо не участвовали в сражениях, либо информация об их участии неизвестна. Так что здесь чистая логика, и никаких подвохов. Упражнение (-2) (подсказки и решения) При решении этой задачи часто допускается весьма характерная и, на наш взгляд, принципиальная ошибка, которая имеется и в решении 3.6.2. Однако, чтобы не приводить здесь окончательный вариант и позволить читателю самому его построить, объясним суть ошибки, упростив формулировку задачи: Определить год, когда на воду было спущено максимальное количество кораблей. Вывод: количество кораблей, год Определить распределение количества кораблей по годам можно так: 1. SELECT launched [ year ] , COUNT ( * ) cnt 2. FROM Ships 3. GROUP BY launched; Примечание: В SQL Server имена столбцов заключаются в квадратные скобки ([]), чтобы избежать неоднозначности. Например, неоднозначность возникает при использовании пробелов в именах, когда первое слово может быть истолковано как имя столбца, а второе — как его псевдоним (alias). Хорошим стилем признается отказ от пробелов в именах, однако, вполне оправданным является их употребление для формирования заголовков отчета. В нашем случае ([year]) квадратные скобки применяются во избежание путаницы с функцией year(), которая возвращает год из аргумента, представленного типом дата-время. Теперь нам нужно оставить из всех строк, возвращаемых этим запросом, только те, у которых количество (cnt) максимально, то есть: 1. cnt >= ALL ( SELECT COUNT ( * ) cnt 2. FROM Ships 3. GROUP BY launched 4. ) Окончательно получим: Решение 4.19.1 1. SELECT * 2. FROM ( SELECT launched [ year ] , COUNT ( * ) cnt 3. FROM Ships 4. GROUP BY launched 5. ) x 6. WHERE cnt >= ALL ( SELECT COUNT ( * ) cnt 7. FROM Ships 8. GROUP BY launched 9. ) ; Тем не менее, здесь кроется ошибка. Эта ошибка не связана с формальным построением решения. Оно не вызывает сомнения. Как это обычно происходит при решении задач на сайте, ошибка заключается в неточном учете особенностей модели предметной области, а именно, ее ограничений. В данном случае допускается, что в базе данных могут быть корабли с неизвестным годом спуска на воду, так как, во-первых, столбец launched допускает NULL-значения и, во-вторых, для головного корабля, который присутствует только в таблице Outcomes, год спуска на воду неизвестен. Строить корабли — это вам не кроликов разводить. Корабли строятся годами. Поэтому, если для ряда кораблей год спуска на воду неизвестен (NULL), то велика вероятность того, что число таких кораблей будет больше, чем количество кораблей, спущенных на воду в любом реальном году. Особенность группировки заключается в том (и это оговорено в стандарте), что NULL-значения трактуются как равные. Следовательно, все корабли с неизвестным годом спуска на воду, будут просуммированы с годом NULL. Полагаем, что результат не должен включать такую строку по той причине, что неизвестный год не означает один и тот же. С этим можно, конечно, поспорить. Однако все споры сведутся к допустимости использования специфического значения NULL в реляционной модели. Дискуссии по этому поводу ведутся со времен создания этой модели Коддом Е.Ф., которому и принадлежит идея NULL-значения. Однако, насколько нам известно, достойной альтернативы предложено не было. Возвращаясь к нашей задаче, мы, в знак безграничного уважения к Кодду, внесем в решение следующее изменение: Решение 4.19.2 1. SELECT * FROM ( SELECT launched [ year ] , COUNT ( * ) cnt 2. FROM Ships 3. WHERE launched IS NOT NULL 4. GROUP BY launched 5. ) x 6. WHERE cnt >= ALL ( SELECT COUNT ( * ) cnt 7. FROM Ships 8. WHERE launched IS NOT NULL 9. GROUP BY launched 10. ) ; В подзапросе предложения WHERE проверку на NULL-значения можно не выполнять, если использовать вместо функции COUNT(*) функцию COUNT(launched), поскольку в этом случае будут подсчитаны только корабли с известным годом спуска на воду: 1. WHERE cnt >= ALL ( SELECT COUNT ( launched ) cnt 2. FROM Ships 3. GROUP BY launched 4. ) Для всех же кораблей с неизвестным годом спуска на воду будет получена строка со значением 0, так как если в наборе нет ни одной записи, то функция COUNT возвращает именно это значение. Последнее не должно нас смущать, поскольку количество кораблей в основном запросе больше нуля, если есть хотя бы один корабль с известным годом спуска на воду. Аналогичным образом можно поступить и в основном запросе, что позволит получить более краткую форму решения: Решение 4.19.3 1. SELECT * FROM ( SELECT launched [ year ] , COUNT ( launched ) cnt 2. FROM Ships 3. GROUP BY launched 4. ) x 5. WHERE cnt >= ALL ( SELECT COUNT ( launched ) cnt 6. FROM Ships 7. GROUP BY launched 8. ) ; Справедливости ради следует отметить, что стоимость решения 4.19.3 по его плану выполнения в SQL Server 2000 будет незначительно уступать (в третьей значащей цифре) стоимости решения 4.19.2. Примечание: Стоимость любого запроса к учебным базам данных, а также процедурный план его выполнения можно посмотреть на странице сайта . Язык манипуляции данными в SQL …язык учителя дзэн передает идеи, а не чувства или намерения. И поэтому он играет не ту роль, какую обычно играет язык; поскольку выбор фраз исходит от учителя, то чудо свершается в той области, которая ему присуща, и ученик раскрывается сам себе, понимает себя, и таким образом обычная фраза становится ключом. Х. Кортасар. Игра в классики. Данная книга ориентирована на практическое применение языка SQL , то есть в первую очередь на использование оператора SELECT, реализующего выборку данных из реляционных СУБД, и операторов INSERT, UPDATE и DELETE, которые служат для модификации данных. Эти операторы и составляют тот подъязык SQL, который называется языком манипуляции данными (или DML — Data Manipulation Language). В этой «теоретической» части мы старались придерживаться тех синтаксических конструкций рассматриваемых операторов, которые, во-первых, полностью соответствуют стандарту SQL- 92, и, во-вторых, поддерживаются практически всеми коммерческими СУБД. Естественно, нельзя было обойти вниманием и некоторые особенности реализации. Это обусловлено тем, что имеющиеся в каждой реализации расширения стандарта позволяют зачастую более компактно написать запрос, а также тем, что некоторые моменты не стандартизируются и, как правило, опускаются на уровень реализации. Поскольку сайт работает с MS SQL Server 2005, то особенности реализации, которые оговариваются отдельно, относятся по большей части именно к этому продукту. В частности, это касается функций работы со строками и значениями типа даты/времени, которые рассматриваются в главе 7 ( |