лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас

• Название: Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
• Анкор: лекция
• Дата: 21.12.2022
• Размер: 7.88 Mb.
• Имя файла: Moiseenko_SQL.pdf
• Тип: Учебник; #857633
• страница: 2 из 47

Упражнение 1
Найдите номер модели, скорость и размер
жесткого диска для всех ПК стоимостью
менее 500 долларов. Вывести: model, speed и hd
Первая задача сложности 1. Даже новички легко справляются с ее решением. Действительно, одна таблица, одно условие отбора по стоимости и ограничение вывода тремя столбцами:
1.
SELECT
model, speed, hd
2.
FROM
PC
3.
WHERE
price <
500
;
Казалось бы, какую пользу можно извлечь из анализа этой задачи? В ответ на этот вопрос предлагаем рассмотреть другое решение той же задачи:
4.
SELECT
Product.model, PC.speed, PC.hd
5.
FROM
Product, PC
6.
WHERE
Product.model = PC.model AND price < 500;
Решения дают один и тот же результат в силу того, что поддерживается целостность по ссылкам между таблицами PC и Product по номеру модели
(столбец model). В частности, это означает, что в таблице PC не может быть модели, которой бы не было в таблице Product. Однако второй запрос не принимался системой, в результате чего автор получил возмущенное письмо от приславшего решение.
Оказалось, что при переносе баз на другой сервер некоторые связи были утеряны, в результате чего в таблице PC появилась модель с удовлетворяющими условиям задачи характеристиками и номером, который отсутствовал в таблице Product. Естественно, второе решение не выдавало этой строки, и система проверки правильности не принимала такого решения.
Несогласованность данных была устранена, связь восстановлена, и второе решение стало благополучно проходить проверку. Мораль же этой истории

заключается в том, что не нужно соединять таблицы, если в этом нет необходимости. По условиям задачи нам не нужна была информация из таблицы Product, поэтому не следовало ее использовать в запросе. Это не оправдание допущенной ошибки при удалении связи, хотя, как вы видели, даже при несогласованных данных первое решение продолжало давать отвечающий условию результат, а именно, выдавало все ПК со стоимостью менее 500 долларов.
Совет:
Если наша цель не просто научиться писать запросы, а
создавать их по возможности эффективными, то следует,
безусловно, избегать излишних соединений таблиц.
Помимо того, что сама операция соединения весьма затратная с точки зрения ресурсов, она может вызвать наложение ненужных блокировок на таблицы (в нашем примере – на таблицу Product), что будет приводить в состояние ожидания параллельно выполняющиеся запросы (например, на модификацию данных), адресуемые к этим таблицам. В результате будет снижаться производительность всей системы.
Упражнение 2
Найдите производителей принтеров.
Вывести: maker.
Здесь впервые встречается ошибка, характерная для нескольких задач (например,
20
,
27
,
28
).
Причина в невнимательном изучении
схемы
данных
. Неправильное решение:
1.
SELECT
DISTINCT maker
2.
FROM
Product
3.
WHERE model
IN
(
SELECT
model
4.
FROM
Printer
);

Таким образом, для каждой строки из таблицы Product проверяется, есть ли такая модель в таблице Printer. Связь между этими таблицами (один-ко- многим) допускает наличие модели в таблице Product, которая отсутствовала бы в таблице Printer.
Пусть, например, фирма занимается ремонтом принтеров. При этом в таблице Product содержится информация обо всех известных моделях принтеров, а в таблице Printer только о тех, которые обслуживает фирма.
Например, если фирма не занимается ремонтом принтеров Sharp, то модели
Sharp будут находиться в таблице Product, а в таблице Printer - нет.
В результате мы можем потерять производителя принтеров, если его моделей нет среди обслуживаемых (в таблице Printer). Как уже говорилось при обсуждении схемы данных
, тип продукции в таблице Product, задается атрибутом type, который и упускается из виду.
Если вам еще не ясно, как решить эту задачу, загляните в главу 4 «Подсказки и решения»
Внимание:
Если к данной задаче имеется пояснение или приведен вариант правильного решения, в конце анализа задачи будет ставиться ссылка на соответствующую страницу этой главы -
ПиР
Упражнение 3
Найдите номер модели, объем памяти и
размеры экранов ноутбуков, цена которых
превышает 1000 долларов.
Еще одна простая задача. Однако и здесь была допущена одна поучительная ошибка. Вот то решение, которое ее содержит:
1.
SELECT
model, ram, screen
2.
FROM
Laptop

3.
WHERE
price >
'1000'
;
Строковые константы в операторах
SQL
заключаются в одинарные кавычки.
Константы числовых типов в кавычки не заключаются. Таким образом, последний предикат следовало бы записать как price > 1000. Однако здесь есть одна особенность, связанная с неявным преобразованием типов. Подробнее об этом вы можете почитать в
главе 5 (пункт 5.9)
. Здесь же следует сказать, что в
SQL Server
2000 не выполняется неявное преобразование строки к значению типа money (деньги). Поэтому рассматриваемый запрос приводил к появлению сообщения об ошибке:
Disallowed implicit conversion from data type varchar to data type
money, table 'Laptop', column 'price'. Use the CONVERT function to run
this query.
(«Запрещено неявное преобразование типа данных varchar к типу данных money; таблица Laptop, столбец price. Используйте функцию CONVERT для выполнения этого запроса».)
Заметим, что если бы столбец price был любого другого числового типа, например, float, то неявное преобразование было выполнено, и ошибки бы не возникало. Конечно, можно выполнить явное преобразование типа; вот вполне корректная версия данного запроса:
1.
SELECT
model, ram, screen
2.
FROM
Laptop
3.
WHERE
price >
CAST
(
'1000'
AS
MONEY);
Если вы сейчас выполните запрос, вызывавший ошибку, то он вернет результирующий набор, а не указанное выше сообщение об ошибке. Дело в том, что в на сайте происходит обновление версий, и уже в SQL Server 2005 это странное отличие поведения типа money от других числовых типов при неявном приведении типов было устранено.
Таким образом, вы получите правильный результат, используя неявное приведение типа. Вот только зачем заставлять сервер тратить на это ресурсы, если можно вообще обойтись без приведения типов?

Упражнение 5
Найдите номер модели, скорость и размер
жесткого диска ПК, имеющих 12х или 24х CD и
цену менее 600 долларов.
При решении этой задачи обычно совершают две типичные ошибки.
Первая ошибка связана с интуитивным предположением, что скорость CD-устройства является целочисленным значением. В результате запрос
1.
SELECT
PC.model, PC.speed, PC.hd
2.
FROM
PC
3.
WHERE
PC.cd
IN
(
12
,
24
)
AND
4. price <
600
; выдаст ошибку приведения несовместимых типов данных:
Conversion failed when converting the varchar value '12x' to data type int.
(«Ошибка при преобразовании значения '12x' типа varchar к типу данных int»)
Внимательное чтение
схемы данных
скажет, что столбец cd имеет тип varchar. Поэтому, чтобы получить правильное решение достаточно переписать запрос в виде:
5.
SELECT
PC.model, PC.speed, PC.hd
6.
FROM
PC
7.
WHERE
PC.cd
IN
(
'12x'
,
'24x'
)
AND
8. price <
600
;
Вторая ошибка логическая и заключается в неправомерном использовании предиката BETWEEN. Вот это решение:

1.
SELECT
model, speed, hd
2.
FROM
PC
3.
WHERE
price <
600
AND
4. cd
BETWEEN
'12x'
AND
'24x'
;
Даже если предположить, что между моделями 12х и 24х-скоростных CD- приводов нет других моделей (скажем, 20х), решение не будет верным в силу правила сравнения строковых значений. Это правило гласит, что строки сравниваются посимвольно до первого отличающегося символа. Далее вывод о сравнении строк целиком делается на основании результата сравнения отличающихся символов. Например, справедливо 'abcz' < 'abd', так как первый отличающийся символ в первой строке ('c') меньше соответствующего символа второй строки ('d'). Если одна строка является префиксом второй
(например, 'упражнения' и 'упражнениями'), то истинным будет сравнение 'упражнения' < 'упражнениями'.
Здесь уместно заметить, что сравнение (и соответственно порядок сортировки) зависит от параметра
COLLATION [3]
. Повсюду, если не оговорено противное, мы будем предполагать, что все текстовые поля имеют одинаковую установку этого параметра, обеспечивающего сравнение, независящее от регистра.
Предикат BETWEEN эквивалентен одновременному выполнению двух простых операторов сравнения: cd >= ‘12x' AND cd <= '24x'
Исходя из вышесказанного, этому предикату будут удовлетворять, помимо требуемых задачей, например, следующие значения:
‘130x’, 145’, ‘150000000000y’ и т. д.
Еще одним вариантом решения этой несложной задачи будет использование двух предикатов простого сравнения:
1.
SELECT
PC.model, PC.speed, PC.hd
2.
FROM
PC
3.
WHERE
(PC.cd =
'12x' OR
4.
PC.cd =
'24x') AND
5. Price < 600;

Упражнение 6
Для каждого производителя, выпускающего
ПК-блокноты c объёмом жесткого диска не
менее 10 Гбайт, найти скорости таких ПК-
блокнотов. Вывод: производитель, скорость.
Вот задача, в которой впервые потребовалась информация из нескольких таблиц: имя производителя (maker) находится в таблице Product, а скорость (speed) и объем жесткого диска (hd) в таблице Laptop.
Примечание:
Пусть
простят
нас
искушенные
в
SQL
читатели, что мы разбираем ошибки
начинающих, но в задачах такой сложности
других ошибок и не бывает. Сложность задачи
указана в столбце "Уровень" в
списке задач
. Так
что можно перейти к задачам с коэффициентом
сложности 2 или 3.
1.
SELECT
DISTINCT
Product.maker, Laptop.speed
2.
FROM
Product, Laptop
3.
WHERE
laptop.hd >= 10 AND
4. type
IN
(
SELECT
type
5.
FROM
Product
6.
WHERE
type =
'laptop'
)
;
При этом автор этого решения пишет, что данный запрос выдает на 5 строк больше, чем в правильном ответе, а запрос, который кажется ему более правильным:
1.
SELECT
DISTINCT
Product.maker, Laptop.speed
2.
FROM
Product, Laptop
3.
WHERE
Laptop.hd >= 10;

выдает результат со всеми типами продуктов.
Ошибка в том, что перечисление таблиц через запятую без указания способа их соединения есть не что иное, как декартово произведение, почитать о котором можно в главе 5
(
пункт 5.6
).
Мы согласны с тем, что второе решение более правильное. В нем не хватает только соединения таблиц. В то время как первое — это попытка подогнать решение, ограничив выдачу второго «правильного» решения только моделями блокнотов. Следует заметить, что попытка была достаточно неуклюжей, так как, если мы правильно поняли идею автора, вместо предиката
7. type
IN
(
SELECT
type
8.
FROM
Product
9.
WHERE
type =
'laptop'
10.
)
достаточно было написать
1. type =
'laptop'
Упражнение 7
Найдите номера моделей и цены всех
имеющихся в продаже продуктов (любого
типа) производителя B (латинская буква).
Продукция в базе данных может быть трех типов:
ПК, ноутбуки и принтеры. Естественным решением является объединение трех наборов по каждому типу продукции. Вот как решал эту задачу один наш участник:
1.
SELECT
model, price
2.
FROM
PC
3.
WHERE
model =
(
SELECT
model
4.
FROM
Product

5.
WHERE
maker =
'B'
AND
6. type =
'PC'
7.
)
8.
UNION
9.
SELECT
model, price
10.
FROM
Laptop
11.
WHERE
model =
(
SELECT
model
12.
FROM
Product
13.
WHERE
maker =
'B'
AND
14. type =
'Laptop'
15.
)
16.
UNION
17.
SELECT
model, price
18.
FROM
Printer
19.
WHERE
model =
(
SELECT
model
20.
FROM
Product
21.
WHERE
maker =
'B'
AND
22. type =
'Printer'
23.
)
;
При этом на основной базе решение дает правильный результат, а на проверочной
SQL
Server выдает следующую ошибку:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
(«Подзапрос возвращает более 1 значения. Это недопустимо, если подзапрос используется как выражение или с операторами сравнения =, !=, <, <= , >, >=».)
Иначе говоря, мы не можем сравнивать отдельное значение с набором, который имеет место, если производитель B выпускает более одной модели какого-либо типа, что и имеет место в проверочной базе данных.
Поправить запрос несложно, достаточно заменить предикат простого сравнения («=») предикатом попадания в список значений (
IN
):
1.
SELECT
model, price
2.
FROM
PC
3.
WHERE
model
IN
(
SELECT
model
4.
FROM
Product

5.
WHERE
maker =
'B'
AND
6. type =
'PC'
7.
)
8.
UNION
9.
SELECT
model, price
10.
FROM
Laptop
11.
WHERE
model
IN
(
SELECT
model
12.
FROM
Product
13.
WHERE
maker =
'B'
AND
14. type =
'Laptop'
15.
)
16.
UNION
17.
SELECT
model, price
18.
FROM
Printer
19.
WHERE
model
IN
(
SELECT
model
20.
FROM
Product
21.
WHERE
maker =
'B'
AND
22. type =
'Printer'
23.
)
;
Заметим, что возможные повторяющиеся здесь пары значений {модель, цена} будут устранены оператором UNION.
Однако налицо явная избыточность: в каждом из объединяемых запросов выполняется отбор моделей производителя B. Указанный недостаток можно устранить, сначала выполнив объединение, а затем отбор по производителю:
1.
SELECT
*
FROM
(
SELECT
model, price
2.
FROM
PC
3.
UNION
4.
SELECT
model, price
5.
FROM
Laptop
6.
UNION
7.
SELECT
model, price
8.
FROM
Printer
9.
)
AS
a
10.
WHERE
a.model
IN
(
SELECT
model
11.
FROM
Product
12.
WHERE
maker =
'B'
13.
)
;

При этом здесь уже не может быть отбора по типу, однако в этом нет нужды, так как номер модели уникален в таблице Product, то есть один и тот же номер не может принадлежать продукции различных типов. В результате мы получим процедурный план, содержащий 8 операций вместо 12, что имело место в первом варианте решения. Соответственно и время выполнения последнего запроса будет меньше.
Предикат IN будет проверяться для каждой записи объединения. Поэтому эффективность выполнения такого запроса будет зависеть от того, как далеко в списке будет находиться искомая модель. Для исключаемых моделей придется просматривать весь список. В конечном итоге время выполнения таких запросов будет тем больше, чем длиннее список (то есть чем больше моделей имеет производитель B).
Можно вместо предиката IN использовать соединение, однако SQL Server дает для этих двух случаев идентичные планы выполнения.
1.
SELECT a.
model
, price
2.
FROM
(
SELECT
model, price
3.
FROM
PC
4.
UNION
5.
SELECT
model, price
6.
FROM
Laptop
7.
UNION
8.
SELECT
model, price
9.
FROM
Printer)
AS
a JOIN
10.
Product p
ON
a.model = p.model
11.
WHERE
p.maker =
'B';
Альтернативой запросам, использующим объединение, могут служить запросы на основе соединения. В данной задаче такое решение будет иметь менее эффективный план выполнения, хотя в других случаях может оказаться предпочтительным. Так или иначе, в учебных целях будет полезно рассмотреть разные способы решения задачи, что и предлагается вам выполнить самостоятельно.

Упражнение 8
Найдите производителя, продающего
ПК, но не ноутбуки.
Начнем с ошибки новичка:
1.
SELECT
DISTINCT
maker
2.
FROM
Product
3.
WHERE
type =
'PC'
AND
4.
NOT
(
type =
'laptop'
)
;
Предикат в предложении WHERE проверяется для каждой строки, формируемой предложением FROM, то есть в нашем случае для каждой строки из таблицы Product. Каждая строка представляет собой некоторую модель, которая может быть чем-то одним, либо ПК, либо ноутбуком, либо принтером. Поэтому, если выполнен первый предикат (type = 'PC'), то автоматически будет выполнен и второй — NOT (type = 'laptop'). Другими словами, второй предикат здесь излишен. Нам же нужно убедиться в том, что если есть строка с типом PC, то нет другой строки с типом laptop для того же поставщика.
Второе решение, верное по логике, опирается на неверную трактовку предметной области, которую мы уже обсуждали:
1.
SELECT
DISTINCT
p.maker
2.
FROM
Product p
INNER
JOIN
3. PC
ON
p.model = PC.model
4.
WHERE
p.maker
NOT
IN
(
SELECT
ip.maker
5.
FROM
Laptop il
INNER
JOIN
6. Product ip
ON
il.model = ip.model
7.
)
;
Здесь проверяется наличие модели ПК в таблице PC и отсутствие модели ноутбуков для одного и того же поставщика. Ошибка заключается в том, что мы можем получить как лишних поставщиков (если в текущем состоянии базы данных в таблице Laptop отсутствуют модели некоего производителя ПК, хотя

они и есть в Product), так и недосчитаться нужных (если в текущем состоянии базы данных в таблице PC нет ни одной модели некоторого поставщика, не производящего ноутбуки).