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

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

Решение 1.16.2

1.
SELECT
DISTINCT
model
FROM
Printer
2.
WHERE
price =
(
SELECT
MAX
(
price
)
3.
FROM
Printer
4.
)
;
Вывод. Каковы бы ни были данные, с помощью первого запроса можно подогнать решение максимум за три попытки. Второе решение вообще не будет проходить, если максимум достигается хотя бы на двух видах продукции. Однако тогда для подгонки первого решения потребуется всего две попытки. Если же в каждом виде продукции есть модель с одной и той же максимальной ценой, то достаточно будет одной попытки.
Кстати говоря, данные проверочной базы подобраны оптимально к рассматриваемым случаям, но, тем не менее, не спасают от «принятия» неправильных решений.
Выход, и не только для данной ситуации, можно найти в увеличении количества проверочных баз, где будут смоделированы различные варианты данных. Однако это замедлит работу системы, в результате чего пользователь будет дольше находиться в состоянии ожидания ответа. Отказываясь от увеличения числа проверочных баз данных, автор успокаивает себя мыслью, что посетителями сайта движет желание изучить язык
SQL
и повысить квалификацию, а не стремление обмануть систему.
Рассмотрим еще один, хотя и неправильный, подход без использования UNION. Решение использует соединение всех моделей с последующим перебором вариантов при помощи оператора CASE:
Решение 1.16.3
1.
SELECT
DISTINCT
CASE
2.
WHEN
PC.price > = l.price
AND
3. PC.price > = prn.price
4.
THEN
pc.model
5.
WHEN
l.price > = PC.price
AND
6. l.price > = prn.price
7.
THEN
l.model

8.
WHEN
prn.price > = l.price
AND
9. prn.price > = pc.price
10.
THEN
prn.model
11.
END
AS
model
12.
FROM
PC, laptop l, printer prn
13.
WHERE
PC.price =
(
SELECT
MAX
(
price
)
14.
FROM
PC
15.
)
AND
16. l.price =
(
SELECT
MAX
(
price
)
17.
FROM
Laptop
18.
)
AND
19. prn.price =
(
SELECT
MAX
(
price
)
20.
FROM
Printer
21.
)
;
В предложении FROM используется декартово произведение трех таблиц.
С помощью предложения WHERE отбираются только те строки, которые содержат модели каждого типа продукции, имеющие максимальную цену в своей продукционной категории. Возникающая здесь избыточность (если, скажем, по две модели из каждой таблицы имеют максимальную цену, то результирующее число строк будет равно восьми — 2*2*2) не является ошибочной, так как возможные дубликаты моделей будут впоследствии устранены при помощи DISTINCT в предложении SELECT. Главное, что каждая строка будет содержать искомую глобальную максимальную цену.
Затем модели с этой глобальной максимальной ценой отбираются в операторе CASE. Вот здесь и кроется ошибка. Особенность обработки оператора CASE заключается в последовательной проверке предложений WHEN. Поэтому при первом выполнении условия будет возвращаться значение из соответствующего предложения THEN, и проверка последующих предложений WHEN выполняться уже не будет.
Рассмотрим с этой точки зрения следующий вариант данных. Пусть максимальную стоимость имеют модели принтера и ПК. Тогда первое предложение WHEN оператора CASE будет удовлетворено:
1.
WHEN
PC.price > = l.price
AND
2. PC.price > = prn.price
3.
THEN
pc.model

Действительно, оба предиката сравнения будут истинны, в результате чего запрос вернет только модель ПК, но не принтера. Если быть более точным, то в результате мы получим все модели ПК, которые имеют одинаковую максимальную цену.
Попробуйте исправить это решение, не используя оператор UNION.
Упражнение 25
Найдите производителей принтеров,
которые производят ПК с наименьшим
объемом RAM и с самым быстрым
процессором среди всех ПК, имеющих
наименьший объем RAM. Вывести: Maker
Ключевой здесь является фраза «имеющих наименьший объем RAM». Она не избыточна, как это может показаться на первый взгляд. Не достаточно найти все модели, имеющие максимальную скорость среди ПК с минимальной
RAM.
Поясним сказанное демонстрацией неправильных решений. Для этой задачи их немало накопилось.
Вот первый пример.
Решение 1.17.1
1.
SELECT
c.maker
2.
FROM
Product c,
3.
(
SELECT
b.model,
MAX
(
b.speed
)
speed
4.
FROM
PC b
5.
WHERE
b.ram
IN
(
SELECT
MIN
(
a.ram
)
6.
FROM
PC a
7.
)
8.
GROUP
BY
b.model
9.
)
t
10.
WHERE
c.model = t.model
AND

11.
EXISTS
(
SELECT
d.model
12.
FROM
Printer d, Product e
13.
WHERE
d.model = e.model
AND
14. e.maker = c.maker
15.
)
;
1. Ошибка в подзапросе
1.
(
SELECT
b.model,
MAX
(
b.speed
)
speed
2.
FROM
PC b
3.
WHERE
b.ram
IN
(
SELECT
MIN
(
a.ram
)
4.
FROM
PC a
5.
)
6.
GROUP
BY
b.model
7.
)
t
Здесь выбираются модели ПК с минимальной памятью, и для каждой такой модели определяется ПК с максимальной скоростью. Ошибка состоит в том, что максимальную скорость нужно определять по всем ПК с минимальной памятью, а не по каждой модели. Кроме того, если у производителя будет две модели с минимальной памятью, то он дважды попадет в результирующий набор, так как в запросе отсутствует устранение дубликатов (DISTINCT, например).
2. Ошибка в определении производителей принтеров
1.
AND
EXISTS
(
SELECT
d.model
2.
FROM
Printer d, Product e
3.
WHERE
d.model=e.model
AND
4. e.maker = c.maker
5.
)
Мы уже обсуждали этот вопрос (
пункт 1.2
).

3. Однако мы еще не выявили главной ошибки решения, которую лучше проанализировать, устранив предыдущие. В следующем решении устранены дубликаты, правильно определены производители принтеров, а также находится глобальный максимум по скорости среди моделей с минимальной памятью.
Решение 1.17.2
1.
SELECT
DISTINCT
maker
2.
FROM
Product
3.
WHERE
type =
'printer'
AND
4. maker
IN
(
SELECT
maker
5.
FROM
Product
6.
WHERE
model
IN
(
SELECT
model
7.
FROM
PC
8.
WHERE
speed =
(
SELECT
MAX
(
speed
)
9.
FROM
(
SELECT
speed
10.
FROM
PC
11.
WHERE
ram=
(
SELECT
MIN
(
ram
)
12.
FROM
PC
13.
)
14.
)
AS
z4 15.
)
16.
)
17.
)
;
Вот как определяется здесь максимум по скорости среди моделей с минимальной памятью:
1. speed =
(
SELECT
MAX
(
speed
)
2.
FROM
(
SELECT
speed
3.
FROM
PC
4.
WHERE
ram =
(
SELECT
MIN
(
ram
)
5.
FROM
PC
6.
)
7.
)
AS
z4 8.
)

Что же осталось. Вернемся к формулировке, в которой требуются «ПК с наименьшим объемом RAM и с самым быстрым процессором среди всех ПК, имеющих наименьший объем RAM». Фактически, здесь содержится два условия:
ПК с наименьшим объемом RAM
и
ПК с самым быстрым процессором среди всех ПК, имеющих
наименьший объем RAM
В рассматриваемом решении используется только второе из этих условий, а именно, определяются лишь модели, имеющие скорость, совпадающую с максимальной скоростью для моделей с минимальной памятью.
Поясним на примере. Пусть минимальная память для моделей ПК в БД — 64
Мбайт и имеются следующие модели:
Speed
ram
600
64
600
128
450
64
Код, используемый для определения искомой скорости,
1.
SELECT
MAX
(
speed
)
2.
FROM
(
SELECT
speed
3.
FROM
PC
4.
WHERE
ram =
(
SELECT
MIN
(
ram
)
5.
FROM
PC
6.
)
7.
)
AS
z4; даст 600. Действительно, это максимальная скорость для моделей с минимальной (64) памятью. А далее мы отбираем модели с этой скоростью, куда попадает и модель {600, 128}, хотя она и не отвечает условиям задачи.
Если производитель этой модели выпускает еще и принтеры (а он

выпускает!), да к несчастью еще и не является производителем модели {600,
64}, то получаем «неверно» при проверке запроса.
Правильным выбором будет, естественно, лишь модель {600, 64}. Надеемся, что теперь решить эту задачу не составит труда.
Упражнение 26
Найдите среднюю цену ПК и портативных
компьютеров, выпущенных производителем
A (латинская буква). Вывести: одна общая
средняя цена
Решение 1.18.1
1.
SELECT
AVG
(
av.p
)
AS
avg_price
2.
FROM
(
SELECT
AVG
(
price
)
p
3.
FROM
Product m, PC
4.
WHERE
m.model = PC.model
AND
5. maker =
'A'
6.
UNION
7.
SELECT
AVG
(
price
)
p
8.
FROM
Product m, Laptop l
9.
WHERE
m.model = l.model
AND
10. maker =
'A'
11.
)
AS
av;
В подзапросе предложения FROM для производителя А объединяются средние цены на ПК и портативные компьютеры, после чего в основном запросе вычисляется среднее этих средних значений. Ошибка чисто арифметическая, которая заключается в том, что общее среднее значение
(которое и нужно посчитать) не равно в общем случае среднему от средних значений.
Решение 1.18.2

1.
SELECT
((
SELECT
SUM
(
price
)
2.
FROM
Product
INNER
JOIN
3. PC
ON
Product.model = PC.model
4.
WHERE
maker=
'A'
)
5. +
6.
(
SELECT
SUM
(
price
)
7.
FROM
Product
INNER
JOIN
8. Laptop
ON
Product.model = Laptop.model
9.
WHERE
maker=
'A'
)
10.
)
/
((
SELECT
COUNT
(
price
)
11.
FROM
Product
INNER
JOIN
12.
PC
ON
Product.model = PC.model
13.
WHERE
maker=
'A'
)
14.
+
15.
(
SELECT
COUNT
(
price
)
16.
FROM
Product
INNER
JOIN
17.
Laptop
ON
Product.model = Laptop.model
18.
WHERE
maker=
'A'
)
19.
)
AS
AVG_price;
Во втором решении сумма цен ПК и портативных компьютеров делится на их общее количество. С точки зрения математики все правильно. Но с точки зрения SQL — нет. Дело в том, что если в базе данных нет ПК (или портативных компьютеров), которые выпускал бы производитель А, то функция COUNT вернет значение 0 (что согласуется с математическими представлениями), а вот функция SUM вернет NULL-значение. В результате чего суммарная цена окажется равной NULL, а не суммарной цене имеющейся продукции другого типа, что хотелось бы получить.
Упражнение 27
Найдите средний размер диска ПК каждого
из тех производителей, которые выпускают
и принтеры. Вывести: maker, средний
размер HD.
Проследите за ходом решения задачи и найдите ошибки.

1. Определим всех производителей, которые выпускают принтеры.
1.
SELECT
Product.maker
2.
FROM
Product
INNER
JOIN
3. Printer
ON
Product.model = Printer.model
4.
GROUP
BY
Product.maker;
2. Выведем для каждого ПК размер жесткого диска и его производителя.
1.
SELECT
PC.hd, Product.maker
2.
FROM
PC
INNER
JOIN
3. Product
ON
PC.model = Product.model;
3. Ограничимся в пункте 2 только теми строками, которые имеют производителя, найденного в пункте 1.
1.
SELECT
PC.hd, Product.maker
2.
FROM
PC
INNER
JOIN
3. Product
ON
PC.model = Product.model
4.
WHERE
Product.maker
IN
(
SELECT
Product1.maker
5.
FROM
Product Product1
INNER
JOIN
6. Printer
ON
Product1.model =
Printer.model
7.
GROUP
BY
Product1.maker
8.
)
;
4. В окончательном решении получаем средние значения на основе запроса из пункта 3.

1.
SELECT
Result.maker,
AVG
(
result.hd
)
2.
FROM
(
SELECT
PC.hd, Product.maker
3.
FROM
PC
INNER
JOIN
4. Product
ON
PC.model = Product.model
5.
WHERE
Product.maker
IN
(
SELECT
Product1.maker
6.
FROM
Product
Product1
INNER
JOIN
7.
Printer
ON
Product1.model = Printer.model
8.
GROUP
BY
Product1.maker
9.
)
10.
)
AS
result
11.
GROUP
BY
result.maker;
Упражнение 28
Используя таблицу Product, определить
количество производителей, выпускающих
по одной модели.
В поддержку часто присылают такое решение от новичков:
1.
SELECT
COUNT
(
Maker
)
AS
qnty
2.
FROM
Product
3.
GROUP
BY
maker
4.
HAVING
COUNT
(
model
)
=
1
;
Помимо того, что это неверное решение задачи, новичка выдают функции
COUNT(maker)/COUNT(model). COUNT(maker) - это вовсе не число производителей, а число строк в группе, для которых maker не равен NULL.
COUNT(model) оказалось равным числу моделей, но не потому, что использован аргумент model, а потому, что каждая строка в таблице представляет собой модель, а model является ключом и не может быть NULL.
Поскольку maker, согласно схеме, тоже не может быть NULL, то имеем

1.
COUNT
(
maker
)
=
COUNT
(
model
)
=
COUNT
(
*
)
=
COUNT
(
1
)
= ...
И все это число моделей в группе, а именно число моделей производителя, т.к. группировка выполняется по имени производителя (maker).
Итак, вот что делает данный запрос.
1. Записи в таблице Product группируются по maker с подсчетом количества строк (моделей) для каждого производителя.
2. Выполняется фильтрация групп, ограничивающая эти количества значением 1.
В результате получим:
1
1
1
...
И таких строк будет столько, сколько у нас имеется производителей, выпускающих по одной модели. Таким образом, рассматриваемый запрос отвечает следующему условию:
Для каждого производителя, выпускающего по одной модели, получить
количество моделей. Вывод: количество моделей.
Согласитесь, что это совсем не то, что требовалось найти в упражнении 28.
Хотя, чтобы решить задачу, осталось сделать совсем немногое, а именно, пересчитать эти строки. Это можно сделать, используя вышеприведенный запрос в качестве подзапроса (или CTE).
Если этой подсказки вам недостаточно для решения задачи, изучите следующий материал:
Получение итоговых значений
.

База
данных
«Фирма
вторсырья»
Фирма занимается приемом вторсырья и имеет несколько пунктов приема. Каждый пункт получает деньги для их выдачи сдатчикам в обмен на сырье.
Фактически, на схеме представлены две базы данных. В каждой задаче по этой схеме используется только одна пара таблиц (либо с суффиксом «_o», либо без него).
В таблицах Income_o и Outcome_o первичным ключом является пара атрибутов {point, date} — номер пункта приема и дата. Этот ключ должен моделировать ситуацию, когда сведения о получении денег на приемном пункте и их выдаче сдатчикам записываются в базу данных не чаще одного раза в день.
Рис. 2.1. Схема базы данных «Фирма вторсырья»
Примечание.
Значения данных в столбце date не содержат времени, например, 2001-03-22 00:00:00.000. К сожалению, использование для этого столбца типа данных datetime может вызвать непонимание, поскольку очевидно, что учет времени не позволит ограничить многократный ввод значений с одной и той же датой (и номером пункта), но отличающихся временем дня. Этот недостаток, связанный с отсутствием отдельных типов данных для даты и времени, уже преодолен в версии
SQL
Server 2008. При использовании же SQL

Server 2000 обеспечить правильность ввода можно при помощи, например, следующего ограничения
(CK_Income_o):
1.
ALTER
TABLE
Income_o
ADD
2. CONSTRAINT PK_Income_o
PRIMARY
KEY
3.
(
4.
[
point
]
,
5.
[
date
]
6.
)
,
7. CONSTRAINT CK_Income_o
CHECK
8.
(
9. DATEPART
(
hour,
[
date
])
+ DATEPART
(
minute,
[
date
])
+
10.
DATEPART
(
second,
[
date
])
+
DATEPART
(
millisecond,
[
date
])
=
0 11.
)
;
Это ограничение (сумма часов, минут, секунд и миллисекунд равна нулю) не позволит ввести какое-либо время, отличное от 00:00:00.000. При таком ограничении первичный ключ на данной таблице будет действительно гарантировать наличие лишь одной записи в день для каждой точки.
Таблица Income_o (point, date, inc) содержит информацию о поступлении денежных сумм (inc) на пункт приема (point). Аналогичная таблица —
Outcome_o (point, date, out) — служит для контроля расхода денежных средств
(out).
Вторая пара таблиц — Income (code, point, date, inc) и Outcome (code, point, date, out) — моделирует ситуацию, когда приход и расход денег может фиксироваться несколько раз в день. Следует отметить, что если записывать в последние таблицы только дату без времени (что и имеет место), то никакая естественная комбинация атрибутов не может служить первичным ключом, поскольку суммы денег также могут совпадать. Поэтому нужно либо учитывать время, либо добавить искусственный ключ. Мы использовали второй вариант, добавив целочисленный столбец code только для того, чтобы обеспечить уникальность записей в таблице.