Задания для самостоятельной работы. 1. Создайте таблицу Сотрудники в базе данных BookSHOP

1. Создайте таблицу Сотрудники в базе данных BookSHOP. Таблица должна содержать поля: личный_номер (первичный ключ), фамилия_имя, размер_комиссионных (значение по умолчанию - 3%), должность («директор», «консультант», «младший продавец», «старший продавец», «закупщик»), дата_приема_на_работу (по умолчанию - текущая дата).

2. В таблицу Заказы внесите следующие изменения: а) добавьте столбец Оформил_заказ с ограничениями NOT NULL и внешнего ключа со ссылкой на столбец Личный_номер таблицы Сотрудники; б) добавьте столбец Количество с с ограничением NOT NULL и значением по умолчанию - 1.

3. Создайте копию таблицы Поставки, переопределив первичный ключ как Номер_договора типа serial и упорядочив все строки в новой таблице по полям Код_книги и Код_поставщика.

4. Измените таблицу Сотрудники так, чтобы размер комиссионных по умолчанию был равен 5% и для этого поля не допускались NULL-значения.

5. В таблице Заказы в состав первичного ключа дополнительно введите поле Код_книги (Указание: для этого следует удалить имеющийся в таблице первичный ключ)

6. Создайте уникальный кластерный индекс для таблицы Книги. Обоснуйте выбор полей для индексирования.

7. Создайте представление для вывода трех заказчиков, имеющих наибольшее количество заказов.

8. Создайте представление для вывода количества оформленных каждым сотрудником заказов и общей стоимости этих заказов.

9. Создайте представление, которое выводило бы данные о поставках, упорядоченные по наименованию издательства и количеству книг.

10. Создайте представление, которое выводило бы информацию о неоплаченных заказах.

II. Инструкции языка манипулирования данными

1. Инструкция INSERT

Таблицы создаются инструкцией CREATE TABLE. Эта инструкция создает пустую таблицу – таблицу без строк. Значения вводятся, удаляются или обновляются с помощью инструкций языка манипулирования данными (DML), основными из которых являются инструкции INSERT (вставить), DELETE (удалить), и UPDATE (обновить). Подобно предложению SELECT они могут оперировать как базовыми таблицами, так и представлениями.

Синтаксис инструкцииINSERT:

INSERT [INTO] {базовая_таблица | представление} [(столбец [,...])]{ DEFAULT VALUES
| VALUES ({ DEFAULT | NULL | выражение } [,...]) | запрос }[ RETURNING { * | выражение [ [ AS ] имя ] } [,...] ]

Предложение INTO необязательно и просто улучшает читабельность инструкции.

Предложение RETURNING (не стандартное) позволяет вывести на экран (так же, как и при использовании SELECT) значения выражений, построенных на основе вставляемых данных.

Возможны два варианта использования INSERT: без запроса и с запросом. В первом варианте в указанную таблицу вставляется строка со значениями полей, указанными в перечне предложения VALUES (значения), причем i-е значение должно соответствовать i-му столбцу в списке столбцов (как и в инструкции SELECT, порядок следования столбцов может быть произвольным). Столбцы, не указанные в списке, заполняются NULL-значениями или значениями по умолчанию. Если NULL-значениями для такого столбца не допустимы и не указано значение по умолчанию, то транзакция отменяется и выводится сообщение об ошибке. Если в списке VALUES указаны все столбцы модифицируемой таблицы и порядок их перечисления соответствует порядку столбцов в описании таблицы, то список столбцов после имени таблицы или представления можно опустить.

Во втором варианте использования инструкции INSERT сначала выполняется запрос, т.е. по предложению SELECT в памяти формируется рабочая таблица, а потом строки рабочей таблицы загружаются в INSERT-таблицу. При этом i-й столбец рабочей таблицы (i-й элемент списка SELECT) соответствует i-му столбцу в списке столбцов модифицируемой таблицы. Здесь также при выполнении указанных выше условий может быть опущен список столбцов предложения INTO. Инструкция INSERT с запросом чаще применяется для переноса строк из одной таблицы в другую (возможно через представление, но, очевидно, модифицируемое).

Примеры:

/* вставляем в таблицу Доставка новую строку */

INSERT INTO "Доставка"

VALUES (1000, -- Заказ

DEFAULT, -- Курьер

DEFAULT, -- Дата_время

DEFAULT) -- Доставлен

/* вставляем в таблицу Доставка все оплаченные заказы */

INSERT INTO "Доставка" ("Заказ", "Дата_время")

SELECT "Код_заказа", "Дата" + '1 0:0'::interval

FROM "Заказы"

WHERE "Оплачен" LIKE ‘%да%’;

/* создаем правило для представления Просроченные_ доставки_2 на вставку */

CREATE RULE _ins_ AS ON INSERT TO "Просроченные_ доставки_2"

DO INSTEAD

INSERT INTO "Доставка" VALUES (NEW."Заказ", NEW."Дата_время");

/* проверяем, как работает правило _ins_ */

insert into "Заказы" VALUES (DEFAULT, NULL, NULL, NULL,NULL);

insert into "Просроченные_ доставки_2" VALUES
((select max("Код_заказа") from "Заказы"), now()+'1 0:'::interval);

select * from "Просроченные_ доставки_2";

select * from “Доставка";

2. Инструкция DELETE

Инструкция DELETE позволяет удалить содержимое всех строк указанной таблицы или тех ее строк, которые удовлетворяют указанному условию. Эта инструкция имеет формат

DELETE FROM [ ONLY ] {базовая_таблица | представление} [ [ AS ] псевдоним ][ USING список ][ WHERE { условие | WHERE CURRENT OF курсор } ][ RETURNING { * | выражение [ [ AS ] имя ] [,...] } ]

Список в предложении USING аналогичен списку предложения FROM инструкции SELECT.

При отсутствии предложения WHERE удаляется содержимое всех строк указанной таблицы, в противном случае - те ее строки, которые удовлетворяют условию в предложении WHERE.

Предложение RETURNING аналогично такому же предложению инструкции INSERT.

Перед удалением строк из таблицы имеет смысл выполнить инструкцию SELECT с таким же условием отбора строк, как и условие в предложении WHERE инструкции DELETE, с тем, чтобы убедиться в корректности выполняемой операции удаления.

Для удаления всех строк инструкция DELETE используется редко. Если необходимо удалить все строки в таблице, во многих случаях следует отдать предпочтение инструкции TRUNCATE TABLE, т.к. эта инструкция работает более быстро, удаляя ссылки данной таблицы на физические страницы данных, а не удаляя данные построчно как инструкция DELETE. Но следует помнить, что TRUNCATE TABLE, в отличие от DELETE, не записывает удаляемые строки в журнал транзакций, а значит, удаленные строки восстановить нельзя. Кроме того, TRUNCATE TABLE не применима к таблицам, на которые ссылаются другие таблицы через внешние ключи.

Примеры:

/* удаляем из таблицы Заказы строки с неопределенными значениями кода заказчика */:

DELETE FROM “Заказы” WHERE “Код_заказчика” IS NULL;

/* создаем правило для представления Просроченные_ доставки_2 на удаление */

CREATE OR REPLACE RULE _del_ ASON DELETE TO "Просроченные_ доставки_2" DO INSTEAD
DELETE FROM "Доставка"WHERE "Доставка"."Заказ" = old."Заказ";

/* удаляем из Просроченные_ доставки_2 строки, соответствующие неопределенным заказчикам */

SELECT"Код_заказа" FROM "Заказы" WHERE "."Код_заказчика" IS NULL;

DELETE FROM "Просроченные_ доставки_2"
USING "Заказы"
WHERE ("Заказ" = "Заказы"."Код_заказа") AND
("Заказы"."Код_заказчика" IS NULL);

3. Инструкция UPDATE

Инструкция UPDATE используется для изменения существующих значений. В разных диалектах SQL форматы этой инструкции могут отличаться как друг от друга, так и от формата ANSI SQL. В PostgreSQL принят следующий формат (в упрощенном виде):

UPDATE [ ONLY ] {базовая_таблица | представление} [ [ AS ] псевдоним ]SET { столбец = { выражение | DEFAULT } | (столбец [,...]) = ({ выражение | DEFAULT } [,...]) } [,...][ FROM список ][ WHERE условие | WHERE CURRENT OF курсор ][ RETURNING { * | выражение [ [ AS ] имя ] [,...] } ]

В отсутствие необязательных предложений FROM и WHERE обновляются значения указанных в списке предложения SET столбцов во всех строках модифицируемой таблицы, но список предложения SET может включать лишь столбцы из обновляемой таблицы, т.е. значение одного из столбцов модифицируемой таблицы может заменяться на значение ее другого столбца или выражения, содержащего значения нескольких ее столбцов, включая изменяемый.

Предложение WHERE позволяет сократить число обновляемых строк, указывая условие их отбора.

Предложение FROM позволяет производить обновление значений модифицируемой таблицы по значениям столбцов из других таблиц, представлений или подзапросов. Перечень таблиц-источников предложения FROM содержит имя модифицируемой таблицы и тех таблиц, значения столбцов которых используются для обновления. При этом, естественно, таблицы должны быть связаны друг с другом в WHERE. В выражениях, значения которых присваиваются столбцам в списке предложения SET, следует уточнять имена используемых столбцов, предваряя их именем таблицы или псевдонимом, при этом в качестве выражения может выступать подзапрос, возвращающий скалярное значение.

Если команды манипулирования данными нарушают ограничения, установленные на столбцы таблиц, то соответствующие транзакции отменяются, и выводится сообщение об ошибке. Наиболее важным является вопрос о взаимоотношении команд манипулирования данными и внешних и родительских ключей. Для столбцов, определенных как внешние ключи, любые значения, которые помещаются в эти столбцы командами INSERT или UPDATE должны быть представлены в их родительских ключах. Можно помещать NULL-значения в эти столбцы, несмотря на то, что NULL-значения не допустимы в родительских ключах. Можно удалять или изменять любые строки с внешними ключами, но любое значение родительского ключа не может быть удалено или изменено. Это означает, например, что нельзя удалить запись о заказчике из таблицы Заказчики пока на эту таблицу есть ссылки внешних ключей других таблиц.

Для разрешения аномалий, возникающих при изменении или удалении текущего значения родительского ключа, говоря в целом об SQL, имеется три возможности:

· Запретить изменения в родительском ключе (ограниченное изменение – RESTRICTED).

· Разрешить автоматическое изменение внешнего ключа (каскадное изменение —CASCADES).

· Разрешить изменение в родительском ключе и автоматически установить внешний ключ в NULL (SET NULL).

Примеры:

/* корректируем количество заказываемых книг с учетом имеющегося в наличии остатка */

UPDATE “Заказы”
SET “Количество” = T2.”Остаток”

FROM “Заказы” T1, “Книги” T2

WHERE T1.“Код_книги“ = T2.” Код_книги ” AND T1.” Количество ” > T2.”Остаток”

/* создаем правило для представления Просроченные_ доставки_2 на обновление */

CREATE RULE _upd_ AS ON UPDATE TO "Просроченные_ доставки_2"

DO INSTEAD

UPDATE "Доставка"

SET "Заказ" = NEW."Заказ", "Дата_время" = NEW."Дата_время"

WHERE "Заказ" = OLD."Заказ";

/* изменяем данные в представлении Просроченные_ доставки_2 */

UPDATE "Просроченные_ доставки_2" AS divr
SET "Дата_время" = "Дата_время" + '1 0:0'::interval

FROM "Заказы" AS ord

WHERE divr."Заказ" = ord."Код_заказа" AND ord."Код_книги" = 5;