Размещено на http://www.allbest.ru/

Лабораторная работа №1

Цель работы: Знакомство с возможностями и приобретение практических навыков работы с утилитой IBExpert как инструментом разработчика и администратора баз данных СУБД Firebird. Создание учебной базы данных средствами SQL.

Выполнение работы:

1. Создадим средствами используемой операционной системы на физическом диске С:\ каталоги 9033\MSSQL.

2. Загрузим утилиту IBExpert, предварительно убедившись, что сервер СУБД Firebird загружен.

2.1 Ознакомимся с правилами работы и структурой меню, а также со всеми доступными пунктами меню утилиты IBExpert.

2.2 Создадим, используя пункт главного меню База данных/Создать базу утилиты IBExpert, в каталоге C:\9033\SQL\ файл рабочей БД с именем 12.fdb:

Рисунок 1 - Создание базы данных

3. Зарегистрируем, используя пункт главного меню База данных/Зарегистрировать базу, созданную БД:

Подключимся, используя пункт главного меню База данных/Подключиться к базе, к созданной БД.

Рисунок 2 - Регистрация базы данных

4. Вызовем SQL-редактор (Инстументы/SQL Редактор). Создадим 5 доменов, последовательно вводя и выполняя следующие запросы:

CREATE DOMAIN Bool AS SMALLINT CHECK (VALUE IN (0, 1));

CREATE DOMAIN Money AS NUMERIC(15,2);

CREATE DOMAIN TMonth AS SMALLINT CHECK (VALUE BETWEEN 1 AND 12);

CREATE DOMAIN PKfield AS INTEGER;

CREATE DOMAIN TYear AS SMALLINT CHECK (VALUE BETWEEN 1990 AND 2100);

Рисунок 3 - Созданные домены в БД

5. Создадим в рабочей БД таблицу Services, выполнив следующий запрос:

CREATE TABLE Services (ServiceCD PKFIELD NOT NULL, ServiceNMVARCHAR(30), PRIMARY KEY (ServiceCD));.

Подтвердим транзакцию и убедимся в наличии создаваемых объектов БД (DatabaseExplorer):

Рисунок 4 - Таблица Services

6. Вставим в таблицу услуг (Services) все строки с данными из одноименной таблицы учебной БД, используя аналогию со следующим запросом INSERT, который используется для вставки первой строки в таблицу услуг:

INSERT INTO Services (ServiceCD, ServiceNM) VALUES (1, 'Газоснабжение');.

7. Выведем все содержимое таблицы Services с помощью следующего запроса:

SELECT * FROMServices;

и убедимся в адекватности введенных данных:

Рисунок 5 - Содержимое таблицы Services

9. Сформируем скрипт по созданию и заполнению всех остальных таблиц учебной БД. Для этого в редактор скриптов утилиты IBExpert (Инструменты/Редактор скриптов) загрузим файл со скриптом создания базы данных и удалим в нем запросы, выполненные в пп. 2-6. Выполним полученный скрипт, установив галочку Использовать текущее соединение.

10. Просмотрим на экране всю информацию о созданных объектах БД:

Рисунок 6 - Результат выполнения скрипта создания БД

Извлечем из таблицы NachislSumma все записи о начислениях абоненту с ФИО Тимошкина Н.Г. (номер лицевого счета '080270'). Для этого нужно выполнить следующий запрос:

SELECT * FROMNachislSummaWHEREAccountCD = '080270';

Рисунок 7 - Выборка записей о начислениях абоненту Тимошкину Н.Г. из таблицы NachislSumma

11. Выполним запрос, который извлекает из таблицы PaySumma всю информацию об оплатах услуг, произведенных абонентами за период позже 2011 г.:

SELECT * FROM PaySumma WHERE PayYear> 2011;.

Рисунок 8 - Информация об оплатах услуг, произведённых абонентами за период позже 2011 г.

12. Выполним запрос, вычисляющий сумму значений всех оплат, произведенных каждым абонентом:

Рисунок 9 - Информация о суммах значений всех оплат, произведённых каждым абонентом

SELECT AccountCD, SUM(PaySum) AS Summa_Abonent FROM PaySumma GROUP BY AccountCD;.

13. Индивидуальное задание (Вариант 13): определить способ реализации (декларативный или процедурный) ограничений целостности между таблицами Abonent, Executor, Request. Проверить ограничения ссылочной целостности (запрещающее, каскадное, обновления), путем внесения в данные этих таблиц соответствующих изменений.

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

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

В таблицах Abonent, Executor, Recuest определены следующие декларативные ограничения целостности:

1. В таблицах Abonent, Executor, Requestcd используется домен PKField для ограничения значений атрибутов executorcd, streetcd, requestcd, failurecd. В таблице Request используется домен bool для ограничения значений атрибута executed.В таблицах Abonent, Executor, Requestcd.

2. В таблицах Abonent, Executor, Requestcd заданы первичные ключи accountcd, executorcd, requestcd соответственно.

3. В таблицах Abonent и Requestcd заданы атрибуты streetcd, accountcd, executercd и falurecd для ограничения значений, атрибуты ссылаются на значения из других таблиц БД: Street, Abonent, Executor, Disrepair.

Для таблицы Abonent определен процедурое ограничения целостности - триггер TD_Abonent.

Ограничения ссылочной целостности:

Запрещающее ограничение ссылочной целостности

В таблицах Abonent, Executor, Request запрещающее ограничение при удалении и изменении не срабатывает.

Запрещающее ограничение ссылочной целостности срабатывает при добавлении и изменении записи в дочерних таблицах Abonent и Request, если отсутствует такой первичный ключ в родительских таблицах Street, Abonent, Executor:

Error Message:

----------------------------------------

violation of FOREIGN KEY constraint "INTEG_11" on table "ABONENT".

Foreign key reference target does not exist.

Каскадное ограничение.

При обновлении первичного ключа таблиц Abonent и Executor срабатывало "каскадное" обновление. Обновлялись внешние ключи accountcd, executorcd в таблице Request.

Пример из таблицы Abonent (изменен лицевой счет абонента Аксенов C.A.).

Ограничения обновления. В таблице Request ограничение обновления срабатывает при удалении ключа в родительских таблицах Abonent, Executor, в результате значение соответствующих внешних ключей в дочерней таблице Request изменяется на NULL.

В таблицу Abonent был добавлен Абонент с лицевым счетом 000001. Строка с таким лицевым счетом была добавлена в таблицу Request:

Этого абонента из таблицы Abonent удалили, значение этого лицевого счета в таблице Request обновилось на NULL:

Отключиться от БД с фиксацией изменений. В отчете зафиксировать размер файла БД (в байтах). Создать резервную копию БД копии (Службы/Резервирование БД). В отчете зафиксировать размер (в байтах) файла резервной копии БД. ibexpert sql запрос

Размер файла БД: 933 888 байт.

Размер файла резервной копии: 25 600 байт.

Восстановить БД из резервной копии (Службы/Восстановление БД). В отчете зафиксировать размер (в байтах) файла восстановленной БД. Сравнить его с размерами файлов первоначальной БД и ее резервной копии. Скопировать резервную копию БД на внешний носитель данных для использования ее в последующих лабораторных работах.

Размер файла БД: 933 888 байт.

Размеры первоначальной и восстановленной БД совпадают.

Лабораторная работа №2

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

Лабораторное задание. Изучить теоретическую часть, изложенную в разд. 3.1 и 3.2 учебного пособия [1]. В ходе самостоятельной подготовки сформулировать задания в терминах предметной области на получение информации, имеющей определенное прикладное значение, из таблиц, заданных вариантом в лабораторной работе № 1:

· c каждым типом возвращаемого элемента в предложении SELECT;

· c каждым видом проверки в условии поиска WHERE;

· c группировкой, в том числе и с HAVING;

· c сортировкой;

· c использованием одной встроенной функции из каждой группы.

Построить и выполнить соответствующие однотабличные запросы. Защитить лабораторную работу.

Порядок выполнения работы:

1. Убедившись, что сервер СУБД Firebird загружен, загрузим утилиту IBExpert.

2. Восстановим учебную БД из резервной копии, созданной в лабораторной работе №1.

3. Подключимся к учебной БД.

4. Построим согласно сформулированным заданиям и выполним с помощью SQL-редактора утилиты IBExpert однотабличные запросы к соответствующим таблицам учебной БД.

4.1 Запросы с каждым типом возвращаемого элемента в предложении SELECT.

4.1.1 Составим запрос на выборку всех столбцов из таблицы: вывести всю информацию о зарегистрированных абонентах.

Select *

From Abonent

4.1.2 Запрос с возвращением столбцов, констант, переменных и выражений. Вывести следующую информацию об абонентах: номер лицевого счета, ФИО, номер дома в формате `номер дома '+ номер дома, дата просмотра.

Select phone

From Abonent

Результат:

4.1.3 Сформировать запрос на выборку ФИО абонента, лицевого счета и константы "Лицевой счет":

Select FIO, 'Лицевой счет', accountcd

From Abonent

4.1.4 Сформировать запрос на выборку ФИО абонента и выражения "Номер телефона" со значением атрибута phone

Select FIO, 'номер телефона - '||Phone

From Abonent

4.1.5 Сформировать запрос о сумме оплаты, включающей поля с лицевым счетом абонента, месяца, года оплаты, суммы оплаты и поля, выводящего значение "Сумма больше 50" и "Сумма меньше 50" в зависимости от суммы оплаты

SELECT accountcd, paymonth, payyear, paysum, CASE

WHEN paysum>50 THEN 'Сумма больше 50'

ELSE 'Сумма меньше 50'

END

FROM paysumma

4.2 Запросы с каждым видом проверки в условии поиска WHERE.

4.2.1 Сформировать запрос об абонентах, проживающих на улице 3

Select *

From Abonent

Where Streetcd=3

4.2.2. Сформировать запрос об оплате услуг за январь, февраль, март

Select *

From Paysumma

Where paymonth between 1 and 3

4.2.3 Запрос с проверкой на соответствие шаблону. Сформировать запрос об абонентах, имеющих инициалы Е.В.

Select *

From Abonent

Where FIO like ' %Е.В. %'

4.2.3 Запрос с проверкой на наличие последовательности символов. Сформировать запрос об абонентах, имеющих лицевые счета с последовательностью символов "69".

Select *

From Abonent

Where accountcd containing '69'

4.2.4 Запрос с проверкой на совпадение с началом строки. Сформировать запрос об абонентах, номера телефонов которых начинаются с 68

Select *

From Abonent

Where phone starting '68'

4.2.5 Запрос с проверкой на членство во множестве. Сформировать запрос об оплате услуг за декабрь январь и февраль

Select *

From Paysumma

Where paymonth in(12,1,2)

4.2.6 Запрос с проверкой значения на NULL. Сформировать запрос об абонентах без телефонных номеров

Select *

From Abonent

Where Phone is NULL

4.2.7 Запрос с проверкой двух значений на отличие. Сформировать запрос об оплате услуг за все имеющиеся в БД года кроме 2012.

Select *

From Paysumma

Where payyear is distinct from 2012

4.2.8 Запрос с проверкой на соответствие регулярному выражению. Сформировать запрос об абонентах с ФИО, начинающихся с "Т" и заканчивающихся на "а"

Select *

From Abonent

Where FIO similar to 'Т %а %'

4.2.9 Запрос с составным условием поиска. Сформировать запрос об оплате услуг абонентами с лицевыми счетами, начинающимися на "0" за июнь, июль и август 2011 года

Select *

From Paysumma

Where accountcd starting '0' and paymonth in(6,7,8) and payyear=2011

4.3 Запросы с группировкой, в том числе и с HAVING.

4.3.1 Запрос с группировкой. Сформировать запрос о максимальной сумме оплаты каждого абонента

Select Accountcd, max(paysum)

From paysumma

Group by Accountcd

4.3.2 Запрос с группировкой и условием отбора групп. Сформировать запрос о минимальной сумме оплаты каждого абонента за 2011 год

Select Accountcd, min(paysum), payyear

From paysumma

Group by Accountcd, payyear

Having payyear=2011

4.4 Запрос с сортировкой

Сформировать запрос об сумме оплаты за 2011 год в порядке возрастания суммы оплаты

Select *

From paysumma

Where payyear=2011

Order by paysum

Сформировать запрос о названии улиц в обратном алфавитном порядке

Select *

From Street

Order by streetnm desc

4.5 Запросы с использованием одной встроенной функции из каждой группы.

4.5.1 Запрос с использованием скалярных функций. Сформировать запрос, содержащий фамилии исполнителей заявок без инициалов.

Select SUBSTRING(Fio FROM 1 FOR position(' ',fio)-1)

From Executor

4.5.2 Запрос с использованием агрегатных функций. Сформировать запрос, содержащий общую сумму оплаты за услугу 1

Select SUM(Paysum)

From Paysumma

Where servicecd=1

4.5.3. Запрос с использованием функции для списка значений. Сформировать запрос об оплате содержащий, лицевой счет абонента, месяц и год оплаты, сумму оплаты, если сумма оплаты меньше 50 выводить 50

select Accountcd, paymonth, payyear, maxvalue(paysum,50) as PAYSUMM

from Paysumma

Лабораторная работа №3

Цель работы: приобретение практических навыков построения запросов на определение данных.

Выполнение работы:

1. Подключиться к учебной БД. Выполнить нижеприведенные действия с объектами учебной БД, заданными вариантом индивидуального задания.

Abonent, Executor, Request; Pg (справочник марок газовых плит); D:C, U:SD.

2. Проанализировать операторы CREATE TABLE и ALTER TABLE, использованные для создания дочерней таблицы учебной БД, выполнив пункт "Извлечение метаданных" меню "Инструменты" утилиты IBExpert. Результатом анализа должна быть информация о первичных и внешних ключах таблицы, которую необходимо занести в отчет.

Информация о первичных и внешних ключах таблиц Abonent, Executor, Request:

/************************************************************/

/* Primary Keys */

/************************************************************/

ALTER TABLE ABONENT ADD PRIMARY KEY (ACCOUNTCD);

ALTER TABLE EXECUTOR ADD PRIMARY KEY (EXECUTORCD);

ALTER TABLE REQUEST ADD PRIMARY KEY (REQUESTCD);

/************************************************************/

/* Foreign Keys */

/************************************************************/

ALTER TABLE ABONENT ADD FOREIGN KEY (STREETCD) REFERENCES STREET (STREETCD) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE REQUEST ADD FOREIGN KEY (ACCOUNTCD) REFERENCES ABONENT (ACCOUNTCD) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE REQUEST ADD FOREIGN KEY (EXECUTORCD) REFERENCES EXECUTOR (EXECUTORCD) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE REQUEST ADD FOREIGN KEY (FAILURECD) REFERENCES DISREPAIR (FAILURECD) ON DELETE SET NULL ON UPDATE CASCADE;

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

Таблицы Abonent, Executor - родительские

Таблица Request - дочерняя

Целостности сущностей. Каждое отношение в таблицах Abonent, Executor, Request описывают экземпляры только одной сущности. В таблицах заданы первичные ключи: Accountcd, Executorcd и Requestcd. Первичные ключи полностью идентифицировать каждую сущность, в таблицах отсутствуют неопределенные значения в составе первичного ключа, в таблицах отсутствуют поля с множественным характером значений атрибута.

Целостность ссылок. В таблицах Abonent, Executor, Request определена ссылочная целостность: столбцы Accountcd и Executorcd, которые являются внешними ключами, они ссылаются на первичные ключи таблиц Abonent и Executor. Ссылочная целостность обеспечивает сохранность между таблицами при добавлении, удалении или изменении записей посредством запрещающего, каскадного механизмов и механизма обновления.

Для таблиц Executor, Abonent и Request определены условия ссылочной целостности: D:SN и U:C.

При обновлении первичного ключа таблицы Abonent будет выполнено обновление внешнего ключа таблицы Request. В качестве примера работы каскадного механизма в таблице Abonent был изменен первичный ключ 443090 на 444444:

После этого изменился внешний ключ в таблице Request.

Доменная целостность гарантирует наличие в определенном столбце только допустимых значений, ограничивая тип, формат или диапазон. Примером доменной целостности в учебной БД являются, например, ограничения типа и диапазона значений requestcd, executorcd, failurecd, streetcd и executed в таблицах Abonent, Executor и Request. Доменная целостность реализуется доменами PKFIELD и BOOL:

Целостность, определяемая пользователем определена триггером TD_Abonent. Триггер запускается после удаления строки в таблице Abonent. Если в таблицах NachislSumma или PaySumma имеются записи с внешним ключом AccountCD, ссылающимся на удаляемую строку таблицы Abonent, то триггер вызывает исключение Del_Restrict и операция удаления прерывается.

4. Создать таблицу - справочник марок одного из типов газового оборудования, состоящую из двух столбцов (один из которых должен быть определен как первичный ключ). Эта таблица должна быть родительской по отношению к таблице Abonent, т.е. иметь первичный ключ, на который ссылается соответствующий внешний ключ таблицы Abonent. Определение первичного ключа создаваемой таблицы и внешнего ключа таблицы Abonent реализовать посредством запроса ALTER TABLE таким образом, чтобы обеспечить условия ссылочной целостности, заданные вариантом индивидуального задания. При этом создаваемым ограничениям должно быть дано имя. Проверить работу созданных правил изменения внешнего ключа. Удалить созданную таблицу.

Pg (справочник марок газовых плит); D:C, U:SD.

4.1 Создание таблицы

CREATE TABLE PG(PGCD PKFIELD NOT NULL PRIMARY KEY, PGNM VARCHAR(30));

INSERT INTO PG (PGCD,PGNM) VALUES (1, 'Hotpoint-Aristonэ');

INSERT INTO PG (PGCD,PGNM) VALUES (2, 'Gefest');

INSERT INTO PG (PGCD,PGNM) VALUES (3, 'Hansa');

INSERT INTO PG (PGCD,PGNM) VALUES (4, 'Deluxe');

Select *

From PG

4.2 Определение первичного ключа создаваемой таблицы и внешнего ключа таблицы Abonent

ALTER TABLE ABONENT ADD PGCD PKFIELD

ALTER TABLE ABONENT ADD FOREIGN KEY (PGCD) REFERENCES PG (PGCD)

ON DELETE CASCADE ON UPDATE SET DEFAULT

4.3 Проверка ограничений ссылочной целостности:

При удалении любой строки в родительской таблице PG срабатывает каскадное ограничение ссылочной целостности.

Удалить строчки из таблицы Abonent не удается из-за триггера TD_Abonent

При изменении значения первичного ключа в таблице PG сработало ограничение ссылочной целостности обновления - в дочерней таблице Abonent соответствующее значение внешнего ключа изменилось на NULL:

4.4 Удаление таблицы

ALTER TABLE ABONENT drop PGCD

DROP TABLE PG

5. Создать индекс по символьному полю родительской таблицы, отсортированный в обратном алфавитном порядке.

CREATE DESC INDEX INDEX1_FIO_ABONENT ON ABONENT(FIO)

CREATE DESC INDEX INDEX2_FIO_EXECUTOR ON EXECUTOR(FIO)

6. Добавить к выбранным столбцам двух таблиц учебной БД соответственно 2 ограничения CHEK, запрещающие хранение ошибочных или не имеющих смысла значений. При создании ограничений каждому из них присвоить уникальное имя. Убедиться, что ограничения добавлены в таблицу и проверить их действие. Изменить одно из ограничений. Убедиться, что ограничение изменено и проверить его действие. Удалить добавленные ограничения.

6.1 Ограничение FIO_4LENGTH, не позволяющее добавлять ФИО абонентов длиной менее 4 символов

ALTER TABLE ABONENT ADD CONSTRAINT

FIO_4LENGTH CHECK (octet_length(FIO)>=4)

При вводе ФИО длиной 2 символа появилось сообщение:

Error Message:

----------------------------------------

Operation violates CHECK constraint FIO_4LENGTH on view or table ABONENT.

At trigger 'CHECK_51'.

6.2 Ограничение DATE_REQUEST, не позволяющее добавлять заявки без даты подачи

ALTER TABLE REQUEST ADD CONSTRAINT

DATE_REQUEST CHECK (incomingdate is not null)

При добавлении в таблицу Request новой заявки без даты подачи вышло сообщение

Error Message:

----------------------------------------

Operation violates CHECK constraint DATE_REQUEST on view or table REQUEST.

At trigger 'CHECK_56'.

Измененное ограничение DATE_REQUEST не позволяет добавлять заявки c датой, меньшей текущей

ALTER TABLE REQUEST drop CONSTRAINT DATE_REQUEST

ALTER TABLE REQUEST ADD CONSTRAINT

DATE_REQUEST CHECK (incomingdate>=current_date)

При добавлении в таблицу Request новой заявки с датой подачи 23.12.2013 вышло сообщение

Error Message:

----------------------------------------

Operation violates CHECK constraint DATE_REQUEST on view or table REQUEST.

At trigger 'CHECK_59'.

Удаление ограничений

ALTER TABLE REQUEST drop CONSTRAINT DATE_REQUEST

ALTER TABLE REQUEST drop CONSTRAINT FIO_4LENGTH

7. Установить выбранному столбцу таблицы учебной БД значение по умолчанию. Убедиться, что изменения добавлены в таблицу и проверить их действие. Изменить значение по умолчанию и проверить его действие. Удалить добавленное значение по умолчанию.

Значение по умолчанию, устанавливающее значение номера дома в таблице Abonent

ALTER TABLE Abonent ALTER COLUMN houseno SET DEFAULT 1

Значение по умолчанию автоматически присвоилось:

Значение по умолчанию изменено:

ALTER TABLE Abonent ALTER COLUMN houseno SET DEFAULT 2

ALTER TABLE Abonent ALTER COLUMN houseno drop DEFAULT

8. Создать комментарий для одного объекта учебной БД, выбранного самостоятельно. Изменить текст комментария. Удалить комментарий.

COMMENT ON TABLE executor is 'Таблица исполнителей заявок'

COMMENT ON TABLE executor is 'Таблица исполнителей ремонтных заявок'

COMMENT ON TABLE executor is ''

9. Создать к таблицам учебной БД представления каждого типа и получить данные, возвращаемые ими. Исследовать работу запросов RECREATE VIEW, ALTER VIEW и CREATE OR ALTER VIEW. Удалить созданные представления.

9.1 Горизонтальное представление.

Представление об абонентах, проживающих на улице 4

CREATE view ABONENT_STREET4

AS

Select *

From ABONENT

Where STREETCD=4

9.2 Вертикальное представление. Представление о ремонтных заявках, с номером заявки, код исполнителя и признаком погашения.

CREATE view req_1

AS

Select requestcd, executorcd, executed

from request

9.3 Смешанное представление. Представление об абонентах, проживающих на улице 4, содержащее лицевой счет, ФИО, номер улицы

CREATE view ABONENT_STREET4_fio

AS

Select accountcd, FIO, streetcd

From ABONENT

Where STREETCD=4

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

CREATE VIEW RERUEST_ZAYAVKI(

SCHET_ABONENTA,

ZAYAVKI)

AS

Select accountcd, count(accountcd)

from request

group by accountcd

9.5 Соединенное представление. Представление об абонентах и наименованиях улиц, на которых абоненты проживают

CREATE VIEW ABONENT_STREET(

ACCOUNTCD,

FIO,

STREETNM)

AS

Select accountcd,fio,streetnm

from abonent A, street S

where A.streetcd=S.streetcd

Удаление представлений:

DROP VIEW abonent_street4

DROP VIEW req_1

DROP VIEW abonent_street4_fio

DROP VIEW reruest_zayavki

DROP VIEW abonent_street

Лабораторная работа №4

Цель работы. Приобретение практических навыков построения запросов на модификацию данных.

Лабораторное задание. Изучить теоретическую часть, изложенную в гл. 5 учебного пособия [1]. Восстановить учебную БД из резервной копии. Построить и выполнить запросы к таблицам учебной БД на модификацию данных. Защитить лабораторную работу.

Порядок выполнения работы:

1. Убедившись, что сервер СУБД Firebird загружен, загрузим утилиту IBExpert.

2. Восстановим учебную БД из резервной копии, созданной в лабораторной работе № 1.

3. Подключимся к учебной БД.

4. Создаем таблицу Pattern, используя следующий запрос:

CREATE TABLE Pattern (Fint INTEGER, Fchar30 VARCHAR(30),

Fchar40 VARCHAR (40), Fchar8 VARCHAR (8),Fnumeric NUMERIC(18,4),

Fdate DATE)

Таблица Pattern предназначена для временного хранения данных разного типа, извлекаемых из таблиц учебной БД. Если некоторые поля таблицы Pattern не используются, то их значения будут установлены в NULL.

5. Помещаем в таблицу Pattern данные из таблиц учебной БД, используя многострочный операторINSERT, в соответствии с заданиями, приведенными ниже. Если при выполнении заданий возникнет необходимость отмены изменений, то следует отменить текущую транзакцию.

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

INSERT INTO pattern (fchar30, fnumeric)

SELECT nachislmonth||'.2012', AVG(nachislsum)

FROM nachislsumma

WHERE nachislyear=2012

GROUP BY nachislmonth

5.2 Поместить в таблицу Pattern информацию об абонентах, проживающих на улице, выбранной самостоятельно. При этом столбец Fchar30 должен содержать ФИО абонента, а столбец Fchar40 - номер дома и номер квартиры, в которой проживает абонент.

INSERT INTO pattern (fchar30, fchar40)

SELECT fio, 'Номер дома '||houseno||' Номер кв.'||flatno

FROM abonent

WHERE streetcd=4

5.3 Поместить в таблицу Pattern информацию о заявках и сделавших их абонентах, проживающих на улице п. 5.2. При этом столбец Fnumeric должен содержать номер заявки, столбец Fdate - дату подачи заявки, столбец Fchar30 - ФИО абонента, сделавшего заявку, столбец Fchar8 - номер лицевого счета абонента, а столбец Fchar40 - название улицы, на которой проживает данный абонент.

INSERT INTO pattern (fnumeric, fchar8, fchar30, fchar40, fdate)

SELECT R.requestcd, A.accountcd,A.fio,S.streetnm,R.incomingdate

FROM request R, abonent A,street S

WHERE A.streetcd=S.streetcd and R.accountcd=A.accountcd and S.streetcd=4

6. С помощью однострочного оператора INSERT поместить в таблицу ремонтных заявок новую заявку от абонента, выбранного самостоятельно. Код заявки должен вычисляться как следующий по порядку. Исполнителем этой заявки назначить мастера, выбранного самостоятельно. В качестве даты подачи заявки задать дату, равную текущей, уменьшенной на один месяц. В качестве неисправности указать любую из таблицы неисправностей. Дата выполнения заявки не должна быть определена.

INSERT INTO request(requestcd, accountcd, executorcd, failurecd, incomingdate, executed)

VALUES((

SELECT MAX(requestcd)FROM request)+1,

'443690',3,3,DATEADD(MONTH,-1,CURRENT_DATE),0)

7. Установить для новой заявки дату выполнения равной текущей дате.

UPDATE request

SET executiondate= CURRENT_DATE

WHERE requestcd=(

SELECT MAX(requestcd)FROM request)

8. Добавить в таблицу регистрации фактов оплаты за услуги новый факт оплаты, принятой от абонента, выбранного самостоятельно. Код факта оплаты должен вычисляться как следующий по порядку, а значения остальным параметрам оплаты задать произвольно.

INSERT INTO paysumma (payfactcd, accountcd, servicecd, paydate, paymonth, payyear, paysum)

VALUES((

SELECT MAX(payfactcd)

FROM paysumma)+1, '443690',2,'23.09.2014', 10,2014,600)

9. Построить и выполнить запросы в соответствии с вариантом индивидуального задания. Каждое задание должно быть выполнено с помощью одного запроса. После выполнения каждого запроса и просмотра изменений, которые им произведены, необходимо отменять текущую транзакцию. После выполнения запросов, удаляющих строки из таблиц, необходимо просматривать их содержимое, чтобы проследить за изменениями, которые обусловлены наличием связей "родитель-потомок" (первичный-внешний ключ) между таблицами учебной БД. Выполнить проверку правильности выполнения каждого задания.

1. Перевести факты оплаты, сделанные с 05.02.2012 по 26.11.2012, на текущую дату.

UPDATE paysumma SET paydate=CURRENT_DATE

WHERE paydate BETWEEN '05.02.2012' and '26.11.2012'

2. Удалить информацию о платежах всех абонентов, в ФИО которых встречается буква К (без учета регистра символов).

DELETE FROM paysumma

WHERE ACCOUNTCD in

(SELECT ACCOUNTCD

FROM abonent

WHERE FIO LIKE ' %к %' or FIO LIKE ' %К %')

3. Поменять ФИО исполнителя ремонтных заявок Шубин В.Г. на свои ФИО.

UPDATE executor

SET FIO='Свою фамилию'

WHERE FIO='Шубин В.Г.'

4. Удалить все ремонтные заявки, которые были поданы абонентом с ФИО Конюхов В.С.

DELETE FROM request

WHERE ACCOUNTCD =

(SELECT ACCOUNTCD

FROM abonent

WHERE FIO='Конюхов В.С. ')

5. Уменьшить на 5 руб. значение начислений за 2013 г. для абонентов, которые в 2010 г. платили 2 и более раз.

UPDATE nachislsumma

SET NACHISLSUM = NACHISLSUM -5

WHERE NACHISLYEAR=2013 and ACCOUNTCD IN

(SELECT ACCOUNTCD

FROM paysumma

WHERE paydate<='31.12.2010' and paydate>='01.01.2010'

GROUP BY ACCOUNTCD

HAVING COUNT(ACCOUNTCD)>=2)

Размещено на Allbest.ru

...