лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Упражнение 10 Найдите модели принтеров, имеющих самую высокую цену. Вывести: model, price Задача обычно не вызывает затруднений, однако, иногда встречаются решения подобные следующему: 1. SELECT model, MAX ( DISTINCT price ) 2. FROM Printer 3. GROUP BY model ; Понятно естественное желание решить задачу без подзапросов. Если бы требовалось вывести только максимальную цену, то тогда группировка была бы не нужна, так как максимум находился бы по всему набору принтеров: 1. SELECT MAX ( price ) 2. FROM Printer ; Однако в задаче требуется вывести еще и номер (номера) модели, имеющей максимальную цену. Поскольку мы не можем в предложении SELECT использовать агрегатные значения наряду с детализированными (если не использовать группировку по детализированным значениям), то в результате и получаем представленное выше неправильное решение с группировкой по модели. Это решение дает максимальную цену по каждой модели, нам же нужно получить модели, которые имеют абсолютную (по всему набору принтеров) максимальную цену. Итак, приходится использовать подзапрос, в котором вычисляется максимальная цена: 1. SELECT model, price 2. FROM Printer 3. WHERE price = ( SELECT MAX ( price ) 4. FROM Printer 5. ) ; При этом подзапрос может вводиться не только с простым оператором сравнения («=»), но и с предложением IN или >= ALL. Подзапрос можно использовать и в предложении FROM: 1. SELECT model, price 2. FROM Printer pr, ( SELECT MAX ( price ) AS maxprice 3. FROM Printer 4. ) AS mp 5. WHERE price = mp.maxprice; Однако это не дает выигрыша в производительности, так как в любом случае вычисление подзапроса выполняется один раз, а потом уже производится сравнение цен для каждой строки. И все же, можно ли решить задачу без подзапроса? Упражнение 13 Найдите среднюю скорость ПК, выпущенных производителем A Решение 1.10.1. Характерная для начинающих ошибка, когда вновь изученные конструкции языка применяются к месту и ни к месту. Вот типичный пример: 1. SELECT AVG ( speed ) AS avg_speed 2. FROM PC 3. WHERE speed IN ( SELECT speed 4. FROM PC, Product 5. WHERE product.model = PC.model AND 6. maker= 'A' 7. ) ; Здесь в подзапросе предложения WHERE отбираются значения скорости процессора ПК, выпущенных производителем A. Далее вычисляется средняя скорость по всем тем ПК, скорость процессора у которых совпадает с одним из значений в списке, полученным из подзапроса. В результате будет учтена и скорость ПК, скажем, производителя B, если она совпадает со скоростью одного из ПК, выпущенного производителем A. Правильный результат будет получен только в том случае, если производители ПК обладают моделями с уникальными наборами скоростей процессора. Упражнение 15 Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD Неверное решение связано с поверхностным знакомством со схемой данных: Решение 1.11.1 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.model <> t.model 7. ) ; В запросе находятся такие ПК, для которых существует другая модель с таким же размером жесткого диска. Ошибка заключается в интуитивном представлении об уникальности модели в таблице PC. Однако, как мы уже говорили, номера моделей уникальны лишь в таблице Product, а здесь они могут повторяться, что и делает данный запрос неверным, так как исключает из рассмотрения одинаковые модели с одинаковыми размерами жестких дисков. Упражнение 16 Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, то есть (i,j), но не (j,i), Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и RAM Вот решение, которое довольно часто встречается у посетителей сайта: 1. SELECT MAX ( model ) AS 'model' , MIN ( model ) AS 'model' , speed, ram 2. FROM PC 3. GROUP BY speed, ram 4. HAVING MAX ( model ) > MIN ( model ) ; Не известно, по какой причине выводят только максимальную и минимальную модель для каждой совпадающей пары значений speed, ram. Возможно, в заблуждение вводит результат «правильного» запроса на основной базе. В этой задаче требуется упорядочить все модели, а не только максимальную и минимальную. Экстремальные характеристики упомянуты для однозначности, то есть, чтобы выводить пару моделей один раз, например: 11221121 но не 11211122 То есть если, скажем, три модели — 1122, 1121, 1135 — имеют одинаковые характеристики, то вывод должен быть таким: 1135 1122 1135 1121 1122 1121 Ниже представлено почти правильное, хотя и громоздкое решение. Решение 1.12.2 1. SELECT P.model, L.model, P.speed, P.ram 2. FROM PC P JOIN 3. ( SELECT speed, ram 4. FROM PC 5. GROUP BY speed, ram 6. HAVING SUM ( speed ) /speed = 2 AND 7. SUM ( ram ) /ram = 2 8. ) S ON P.speed = S.speed AND 9. P.ram = S.ram JOIN 10. PC L ON L.speed = S.speed AND 11. L.ram = S.ram AND 12. L.model < P.model; Здесь в подзапросе S отбираются уникальные пары характеристик (скорость, память), совпадающие у двух ПК (SUM(speed)/speed = 2) — сумма одинаковых значений, деленная на это значение, дает количество ПК. Хотя с тем же успехом можно было написать такое предложение HAVING: 1. HAVING COUNT ( * ) = 2 Подзапрос дважды соединяется с таблицей PC по этой паре характеристик. При этом второе соединение выполняется лишь для того, чтобы упорядочить модели (L.model < P.model). Ошибка данного решения состоит в том, что число ПК с одинаковыми характеристиками может быть больше двух. В этой ситуации ни одна из таких моделей не попадет в результирующий набор представленного решения. Несмотря на то, что решение легко исправить, лучше написать его не в такой избыточной форме. Еще одна типичная ошибка при решении данного упражнения вызвана возможным наличием в таблице PC компьютеров одинаковых моделей. В связи с этим при выводе пар ПК необходимо исключать дубликаты. Упражнение 17 Найдите портативные компьютеры, скорость которых меньше скорости любого ПК. Вывести: type, model, speed Ошибки, которые здесь допускаются, связаны с излишним использованием операций соединения. Самым вопиющим примером, по мнению автора, является следующий: 1. SELECT DISTINCT p.type, l.model, l.speed 2. FROM Product p, Laptop l, PC c 3. WHERE l.speed < ( SELECT MIN ( speed ) 4. FROM PC 5. ) AND 6. p.type = 'laptop' ; В предложении FROM используется декартово произведение трех таблиц! Если присутствие таблицы Product еще можно как-то оправдать — ведь в задаче требуется указать еще и тип продукции, то таблицу PC можно смело исключить — это не повлияет на результат. Естественно, решение не будет оптимальным по скорости выполнения. Кроме того, могут возникнуть проблемы с памятью, так как мощность промежуточного результата может стать огромной даже для относительно небольших таблиц. Напомним, что мощность декартового произведения равна произведению мощностей операндов. Например, для таблиц с количеством строк 100, 500 и 1000 их декартово произведение будет содержать 50000000 строк! И, тем не менее, решение является правильным, так как предложение DISTINCT исключает все дубликаты, появившиеся в результате декартового произведения. Упражнение 18 Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price Найдите две ошибки в следующем решении: 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; Упражнение 20 Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей Новички часто задают вопрос: "Почему правильным решением считается производитель Е с тремя моделями, хотя запрос 1. SELECT Product.maker, PC.model 2. FROM PC, Product 3. WHERE Product .model = PC.model; показывает, что у этого производителя имеется всего одна модель?" Модели, которые в принципе выпускаются тем или иным производителем, содержатся в таблице Product. О таблице PC можно сказать, что это информация о компьютерах имеющихся в наличии. Соединение таблиц ограничивает выборку только моделями (вернее, конкретными ПК), имеющимися в наличии. Поскольку в задании говорится о моделях производителей, а не о имеющихся ПК, то нужно анализировать только таблицу Product. В результате обнаружатся недостающие модели. Упражнение 23 Найдите производителей, которые производили бы как ПК со скоростью не менее 750 МГц, так и портативные компьютеры со скоростью не менее 750 МГц. Вывести: Maker Нижеприведенный запрос содержит характерную ошибку, допускаемую при решении этого упражнения. Решение 1.15.1 1. SELECT DISTINCT maker 2. FROM product 3. WHERE model IN ( SELECT model 4. FROM PC 5. WHERE speed >= 750 6. ) OR 7. model IN ( SELECT model 8. FROM Laptop 9. WHERE speed >= 750 10. ) ; Ошибка состоит в том, что в результирующий набор попадет также и производитель, выпускающий что-нибудь одно: либо ПК, либо портативные компьютеры, так как предикат в предложении WHERE будет истинен при выполнении хотя бы одного из условий, соединяемых оператором OR. Такой подход не удовлетворяет условиям задачи и совершенно справедливо отвергается системой. Вот попытка «изменить» ситуацию в лучшую сторону: Решение 1.15.2 1. SELECT DISTINCT maker 2. FROM Product a, PC b, Laptop c 3. WHERE b.speed >= 750 AND 4. c.speed >= 750 AND 5. ( a.model = b.model OR 6. a.model = c.model 7. ) ; Используя равенство предикатов 1. x AND ( y OR z ) = ( x AND y ) OR ( x AND z ) , выполним синтаксические преобразования рассматриваемого запроса: 1. SELECT DISTINCT maker 2. FROM Product a, PC b, Laptop c 3. WHERE (( b.speed >= 750 AND 4. c.speed >= 750 5. ) AND 6. a.model = b.model 7. ) OR 8. (( b.speed >= 750 AND 9. c.speed >= 750 10. ) AND 11. a.model = c.model 12. ) ; В результирующий набор попадут строки, удовлетворяющие хотя бы одному из предикатов, соединяемых оператором OR. Рассмотрим, например, запрос с первым предикатом: 1. SELECT DISTINCT maker 2. FROM Product a, PC b, Laptop c 3. WHERE (( b.speed >= 750 AND 4. c.speed >= 750 5. ) AND 6. a.model = b.model 7. ) ; Перепишем его в синтаксически более удобной форме: 1. SELECT DISTINCT maker 2. FROM Product a JOIN 3. PC b ON a.model = b.model, 4. Laptop c 5. WHERE ( b.speed >= 750 AND 6. c.speed >= 750 7. ) ; и далее 1. SELECT DISTINCT maker 2. FROM ( SELECT maker 3. FROM Product a JOIN 4. PC b ON a.model = b.model 5. WHERE b.speed >= 750 6. ) x, 7. ( SELECT * 8. FROM Laptop c 9. WHERE c.speed >= 750 10. ) y; Теперь, пожалуй, уже можно проанализировать. Первый подзапрос, который мы обозначили х соединяет по внешнему ключу таблицу PC с таблицей Product, отбирая производителей ПК со скоростью больше или равной 750. Второй подзапрос (y) фильтрует модели портативных компьютеров со скоростью больше или равной 750. То, как соединяются x и y, называется декартовым произведением. То есть производитель требуемых ПК будет в результирующем наборе сочетаться с каждой моделью ПК-блокнота, даже если она произведена другим производителем. В результате мы опять получим производителей, которые могут производить только что-то одно. Некоторая разница по сравнению с первым решением заключается в том, что если ни один производитель не выпускает портативные компьютеры с требуемой скоростью, то мы получим пустой набор записей. Этот частично правильный результат не дает первый пример 1.15.1 Совпадение результатов на основной базе является совершенно случайным. Так уж оказалось, что те производители, которые выпускают требуемые по условию задачи ПК, выпускаю также и нужные портативные компьютеры. Таким образом, несмотря на совпадение результатов на «видимой» базе, запрос не является правильным при любом совместимом со схемой состоянием базы данных. Чтобы не быть голословным, покажем результаты оригинального запроса 1.15.2 с расширением списка выводимых столбцов: 1. SELECT maker, a.model a_m, b.model b_m, c.model c_m 2. FROM Product a, PC b, Laptop c 3. WHERE (( b.speed >= 750 AND 4. c.speed >= 750 5. ) AND 6. a.model = b.model 7. ) OR 8. (( b.speed >= 750 AND 9. c.speed >= 750 10. ) AND 11. a.model = c.model 12. ) ; Рассмотрим пару строк из результирующего набора: maker a_m b_m c_m B 1121 1121 1752 A 1752 1121 1752 Как видно, модель 1121 (ПК) принадлежит производителю В, а модель 1752 (портативный компьютер) — производителю А. Так что у нас нет никаких оснований считать, что оба эти производителя удовлетворяют условиям задачи. Объединение требуемых моделей ПК и портативных компьютеров в один набор дает лишь иллюзию, что мы получаем и то, и другое: Решение 1.15.3 1. SELECT maker 2. FROM ( SELECT maker 3. FROM Product INNER JOIN 4. PC ON Product.model = PC.model 5. WHERE type= 'PC' AND 6. speed >= 750 7. UNION ALL 8. SELECT maker 9. FROM Product INNER JOIN 10. Laptop ON Product.model = Laptop.model 11. WHERE type= 'laptop' AND 12. speed >= 750 13. ) S 14. GROUP BY maker; В результате будет получен список производителей, для которых имеется хотя бы одна строка в наборе из предложения FROM. Ниже более короткий вариант той же ошибки. Решение 1.15.4 1. SELECT maker 2. FROM Product 3. WHERE model IN ( SELECT model 4. FROM PC 5. WHERE speed >= 750 6. UNION ALL 7. SELECT model 8. FROM Laptop 9. WHERE speed >= 750 10. ) 11. GROUP BY maker; Следующее решение использует соединение. Решение 1.15.5 1. SELECT maker 2. FROM Product INNER JOIN 3. PC ON Product.model = PC.model INNER JOIN 4. Laptop ON Laptop.model = Product.model 5. WHERE PC.speed >= 750 AND 6. Laptop.speed >= 750 7. GROUP BY maker; Идея состоит в следующем: получить в результирующей таблице столбец с именем производителя, столбец со скоростью ПК этого производителя, а также столбец со скоростью портативного компьютера того же производителя. Поэтому, если задать нужные ограничения на скорости, то строки, удовлетворяющие этим ограничениям, должны дать то, что нужно (группировка по производителю устраняет дубликаты). Идея правильная, однако, соединяется не то и не по тем предикатам. Действительно, первое внутреннее соединение даст нам список производителей и номера моделей ПК. Только ПК, так как model — первичный ключ в таблице Product. Поэтому второе внутреннее соединение с таблицей Laptop (по столбцу model!) даст нам пустой результирующий набор, так как в таблице Laptop нет (и не может быть!) моделей ПК. Чтобы реализовать эту идею, нужно соединять производителей нужных ПК с аналогичными производителями портативных компьютеров, но не по номеру модели, а по имени производителя, или же использовать внешние соединения, а не внутренние. Упражнение 24 Найдите номер модели продукта (ПК, ПК- блокнота или принтера), имеющего самую высокую цену. Вывести: model Вот ошибочное решение, которое какое-то время назад принималось проверочной системой сайта: Решение 1.16.1 1. SELECT model 2. FROM ( SELECT model, price 3. FROM PC 4. WHERE price = ( SELECT MAX ( price ) 5. FROM PC 6. ) 7. UNION 8. SELECT model, price 9. FROM Laptop 10. WHERE price = ( SELECT MAX ( price ) 11. FROM Laptop 12. ) 13. UNION 14. SELECT model, price 15. FROM Printer 16. WHERE price = ( SELECT MAX ( price ) 17. FROM Printer 18. ) 19. ) T 20. WHERE price = ( SELECT MAX ( price ) 21. FROM Laptop 22. ) ; Давайте разберемся, какие данные должны быть в проверочной базе, чтобы блокировались такие решения. Но сначала посмотрим, что же делает этот запрос. В каждом из трех аналогичных подзапросов разыскиваются максимальные по цене модели по каждому из трех видов продукции — ПК, портативным компьютерам и принтерам. Далее используется оператор UNION для объединения найденных моделей, что, помимо этого, устраняет дубликаты строк модель, цена. Наконец, отбираются только те модели, цена которых совпадает с максимальной ценой на портативные компьютеры. Поэтому, если максимальной окажется цена на принтеры, то данное решение не будет приниматься системой. Но тогда будет приниматься решение, в котором условие отбора будет следующим: 1. WHERE price = ( SELECT MAX ( price ) 2. FROM Printer 3. ) Более того, если максимальная цена будет у моделей только одного типа продукции (скажем, принтеров), то будет приниматься еще более неправильно решение: |