metBD (1084482), страница 39
Текст из файла (страница 39)
Упражнения
Как и в предыдущей части, все следующие упражнения основаны на базе данных поставщиков-деталей-изделий. В каждом из них требуется записать предложение SELECT для указанного запроса, за исключением упражнений 15—18 и 26. Для удобства повторим здесь структуру рассматриваемой базы данных:
S (НОМЕР_ПОСТАВЩИКА, ФАМИЛИЯ, СОСТОЯНИЕ, ГОРОД)
P (НОМЕР_ДЕТАЛИ, НАЗВАНИЕ, ЦВЕТ, ВЕС, ГОРОД)
J (НОМЕР_ИЗДЕЛИЯ, НАЗВАНИЕ, ГОРОД)
SPJ (НОМЕР_ПОСТАВЩИКА, НОМЕР_ДЕТАЛИ, НОМЕР_ИЗДЕЛИЯ,
КОЛИЧЕСТВО)
В каждом разделе упражнения упорядочены приблизительно в порядке возрастания их сложности. Необходимо попытаться выполнить, по крайней мере, некоторые из легких упражнений в каждой группе. Упражнения 12—18 являются весьма трудными.
Подзапросы
-
Выдать названия изделий, для которых поставляются детали поставщиком S1.
-
Выдать цвета деталей, поставляемых поставщиком S1.
-
Выдать номера деталей, поставляемых для какого-либо изделия в Лондоне.
-
Выдать номера изделий, использующих, по крайней мере, одну деталь, поставляемую поставщиком S1.
-
Выдать номера поставщиков, поставляющих, по крайней мере, одну деталь, поставляемую, по крайней мере, одним поставщиком, который поставляет, по крайней мере, одну красную деталь.
-
Выдать номера поставщиков, имеющих состояние меньшее, чем у поставщика S1.
-
Выдать номера поставщиков, поставляющих детали для какого-либо изделия с деталью Р1 в количестве, большем, чем средний объем поставок детали Р1 для этого изделия. Примечание. В этом упражнении нужно использовать стандартную функцию AVG.
Квантор EXISTS
-
Повторите упражнение 3.3 и используйте в Вашем решении EXISTS.
-
Повторите упражнение 3.4 и используйте в Вашем решении EXISTS.
-
Выдать номера изделий, для которых не поставляет какой-либо красной детали поставщик из Лондона.
-
Выдать номера изделий, для которых детали полностью поставляет поставщик S1.
-
Выдать номера деталей, поставляемых для всех изделий в Лондон.
-
Выдать номера поставщиков, поставляющих одну и ту же деталь для всех изделий.
-
Выдать номера изделий, для которых поставляются, по крайней мере, все детали, имеющиеся у поставщика S1.
Для следующих четырех упражнений (15—18) преобразуйте приведенное предложение SELECT языка SQL обратно в его эквивалент на естественном языке.
-
SELECT DISTINCT НОМЕР_ИЗДЕЛИЯ
FROM SPJ SPJX
WHERE NOT EXISTS
(SELECT *
FROM SPJ SPJY
WHERE SPJY.НОМЕР_ИЗДЕЛИЯ = SPJX.НОМЕР_ИЗДЕЛИЯ
AND NOT EXISTS
(SELECT *
FROM SPJ SPJZ
WHERE SPJZ.НОМЕР_ДЕТАЛИ =
SPJY.НОМЕР_ДЕТАЛИ
AND SPJZ.НОМЕР_ПОСТАВЩИКА = ’S1’));
-
SELECT DISTINCT НОМЕР_ИЗДЕЛИЯ
FROM SPJ SPJX
WHERE NOT EXISTS
(SELECT *
FROM SPJ SPJY
WHERE EXISTS
(SELECT *
FROM SPJ SPJA
WHERE SPJA.НОМЕР_ПОСТАВЩИКА = ’S1’
AND SPJA.НОМЕР_ДЕТАЛИ =
SPJY.НОМЕР_ДЕТАЛИ)
AND NOT EXISTS
(SELECT *
FROM SPJ SPJB
WHERE SPJB.НОМЕР_ПОСТАВЩИКА = ’S1’
AND SPJB.НОМЕР_ДETAЛИ =
SPJY.НОМЕР_ДЕТАЛИ
AND SPJB.НОМЕР_ИЗДEЛИЯ =
SPJX.НОМЕР_ИЗДЕЛИЯ));
-
SELECT DISTINCT НОМЕР_ИЗДЕЛИЯ
FROM SPJ SPJX
WHERE NOT EXISTS
(SELECT *
FROM SPJ SPJY
WHERE EXISTS
(SELECT *
FROM SPJ SPJA
WHERE SPJA.НОМЕР_ДЕТАЛИ =
SPJY.НОМЕР_ДЕТАЛИ
AND SPJA.НОМЕР_ИЗДЕЛИЯ =
SPJX.НОМЕР_ИЗДЕЛИЯ)
AND NOT EXISTS
(SELECT *
FROM SPJ SPJB
WHERE SPJB.НОМЕР_ПОСТАВЩИКА = ’S1’
AND SPJB.НОМЕР_ДЕТАЛИ =
SPJY.НОМЕР_ДЕТАЛИ
AND SPJB.НОМЕР_ИЗДЕЛИЯ =
SPJX.НОМЕР_ИЗДЕЛИЯ));
-
SELECT DISTINCT НОМЕР_ИЗДЕЛИЯ
FROM SPJ SPJX
WHERE NOT EXISTS
(SELECT *
FROM SPJ SPJY
WHERE EXISTS
(SELECT *
FROM SPJ SPJA
WHERE SPJA.НОМЕР_ПОСТАВЩИКА =
SPJY.НОМЕР_ПОСТАВЩИКА
AND SPJA.НОМЕР_ДЕТАЛИ IN
(SELECT НОМЕР_ДЕТАЛИ
FROM P
WHERE ЦВЕТ = ’Красный’)
AND NOT EXISTS
(SELECT *
FROM SPJ SPJB
WHERE SPJB.НОМЕР_ПОСТАВЩИКА =
SPJY.НОМЕР_ПОСТАВЩИКА
AND SPJB.НОМЕР_ИЗДЕЛИЯ =
SPJX.НОМЕР_ИЗДЕЛИЯ)));
Стандартные функции
-
Выдать общее число изделий, для которых поставляет детали поставщик S1.
-
Выдать общее количество деталей Р1, поставляемых поставщиком S1.
-
Для каждой поставляемой для некоторого изделия детали выдать ее номер, номер изделия и соответствующее общее количество деталей.
-
Выдать номера изделий, для которых город является первым в алфавитном списке таких городов.
-
Выдать номера изделий, для которых средний объем поставки деталей Р1 больше наибольшего объема поставки любой детали для изделия J1.
-
Выдать номера поставщиков, поставляющих деталь Р1 для какого-либо изделия в количестве, большем среднего объема поставок детали Р1 для этого изделия.
Объединение
-
Постройте упорядоченный список всех городов, в которых размещаются, по крайней мере, один поставщик, деталь или изделие.
-
Приведите результат следующего предложения SELECT:
SELECT Р.ЦВЕТ
FROM Р
UNION
SELECT P.ЦВЕТ
FROM Р;
8.4 Операции обновления
Введение
В двух последних частях весьма подробно было рассмотрено предложение выборки данных SELECT языка SQL. Обратим теперь наше внимание на предложения обновления данных UPDATE (обновить), DELETE (удалить) и INSERT (вставить).
Как и предложение SELECT, три предложения обновления данных оперируют не только базовыми таблицами, но и представлениями. Однако не все представления являются обновляемыми. Если пользователь попытается выполнять операцию обновления над необновляемым представлением, система просто отвергнет эту операцию с соответствующим сообщением для пользователя. Предположим, следовательно, для целей данной части, что все таблицы, которые будут обновляться, являются базовыми таблицами.
В следующих трех разделах подробно обсуждаются три операции обновления. Синтаксис этих операций следует тому же общему образцу, который был уже показан для операции SELECT. Для удобства в начале соответствующих разделов приводится в общих чертах синтаксис обсуждаемых предложений языка SQL.
Предложение update
Предложение UPDATE имеет следующий общий формат:
UPDATE таблица
SET поле = выражение
[, поле = выражение] …
[WHERE предикат];
Все записи в «таблице», которые удовлетворяют «предикату», обновляются в соответствии с присваиваниями «поле = выражение» во фразе SET (установить).
Обновление единственной записи
Изменить цвет детали Р2 на желтый, увеличить ее вес на 5 и установить значение города «неизвестен» (NULL).
UPDATE Р
SET ЦВЕТ = ’Желтый’, ВЕС = ВЕС + 5, ГОРОД = NULL
WHERE НОМЕР_ДЕТАЛИ = ’Р2’;
Для каждой записи, которая должна быть обновлена (т. е. для каждой записи, которая удовлетворяет предикату WHERE,. или для всех записей, если фраза WHERE опущена), ссылки во фразе SET на поля этой записи обозначают значения этих полей перед тем, как будет выполнено какое-либо присваивание в этой фразе SET.
Обновление множества записей
Удвоить состояние всех поставщиков, находящихся в Лондоне.
UPDATE S
SET СОСТОЯНИЕ = 2*СОСТОЯНИЕ
WHERE ГОРОД = ’Лондон’;
Обновление с подзапросом
Установить объем поставок равным нулю для всех поставщиков из Лондона.
UPDATE SP
SET КОЛИЧЕСТВО = 0
WHERE ’Лондон’ =
(SELECT ГОРОД
FROM S
WHERE S.НОМЕР_ПОСТАВЩИКА =
SP.НОМЕР_ПОСТАВЩИКА);
Обновление нескольких таблиц
Изменить номер поставщика S2 на S9.
UPDATE S
SET НОМЕР_ПОСТАВЩИКА = ’S9’
WHERE НОМЕР_ПОСТАВЩИКА = ’S2’;
UPDATE SP
SET НОМЕР_ПОСТАВЩИКА = ’S9’
WHERE НОМЕР_ПОСТАВЩИКА = ’S2’;
Невозможно обновить более одной таблицы в единственном запросе. Иными словами, в предложении UPDATE должна специфицироваться в точности одна таблица. Поэтому в данном примере мы сталкиваемся со следующей проблемой целостности (точнее, с проблемой целостности по ссылкам): база данных становится противоречивой после выполнения первого предложения UPDATE — она включает теперь некоторые поставки, для которых не имеется соответствующей записи о поставщике, и остается в таком состоянии до тех пор, пока не будет выполнено второе предложение UPDATE. Изменение порядка предложений UPDATE, конечно, не решает эту проблему. Поэтому важно обеспечить выполнение обоих эти предложений, а не только одного.
Предложение delete
Предложение DELETE имеет следующий общий формат:
DELETE
FROM таблица [WHERE предикат];
Удаляются все записи в «таблице», которые удовлетворяют «предикату».
Удаление единственной записи
Удалить поставщика S1.
DELETE
FROM S
WHERE НОМЕР_ПОСТАВЩИКА = ’S1’;
И снова, если таблица SP в настоящее время содержит какие-либо поставки для поставщика S1, это удаление нарушит непротиворечивость базы данных.
Удаление множества записей
Удалить всех поставщиков из Лондона.
DELETE
FROM S
WHERE ГОРОД = ’Лондон’;
Удаление множества записей
Удалить все поставки.
DELETE
FROM SP;
SP — все еще известная таблица, но она теперь пуста. Удалить все записи — это не уничтожить таблицу (операция DROP).
Удаление с подзапросом
Удалить все поставки для поставщиков из Лондона.
DELETE
FROM SP
WHERE ’Лондон’ =
(SELECT ГОРОД
FROM S
WHERE S.НОМЕР_ПОСТАВЩИКА =
SP.НОМЕР_ПОСТАВЩИКА);
Предложение insert
Предложение INSERT имеет следующий общий формат:
INSERT
INTO таблица [(поле [, поле] …)]
VALUES (константа [, константа] …);
или:
INSERT
INTO таблица [(поле [, поле] …)]
подзапрос;
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
