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

Задание 2 (2 семестр)

В предложенной в приложении схеме выполнить следующие задания.

Вариант 1.

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

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

3. Создайте процедуру, которая дополняет таблицу BUS учебной базы данных таким образом, чтобы в ней оказались отчеты всех агентств за каждый квартал. Для формирования значений идентификаторов строк отчетов используйте последовательность.

Вариант 2.

1. Напишите сценарий для считывания и вывода на экран данных о турах (не более трех), у которых класс меньше среднего. Создайте два варианта циклов - обычный и курсорный цикл FOR.

2. Создайте процедуру учёта данных по турам стоимостью свыше 10000 рублей и подсчёта итоговой квартальной суммы по каждому агентству с занесением данных в отдельную таблицу аудита.

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

Вариант 3.

1. Определите массив с целочисленным индексированием на основе таблицы Доходов и выведите 10 случайных записей из массива с указанием суммарных доходов по выведенным данным.

НЕ нашли? Не то? Что вы ищете?

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

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

DDL

-- создание таблиц учебной БД

-- создание таблицы туристических агентств AGEN

create table AGEN

(AID number(4),

ANAME varchar2(30),

CITY varchar2(30),

RATING number);

-- создание таблицы туров - TOUR

create table TOUR

(TID number(4),

TNAME varchar2(30),

COUNTRY varchar2(30),

TCLASS number(2),

AID number(4));

-- создание таблицы доходов - BUS

create table BUS

(BID number(4),

BDATE date,

AMT number,

AID number(4),

TID number(4));

-- определение первичного ключа таблицы agen

ALTER TABLE agen

ADD (CONSTRAINT pk_agn PRIMARY KEY (aid));

-- определение первичного и внешнего ключей таблицы tour

ALTER TABLE tour

ADD (CONSTRAINT pk_tou PRIMARY KEY (tid),

CONSTRAINT fk_ta FOREIGN KEY (aid)

REFERENCES agen(aid));

-- определение первичного и внешних ключей таблицы bus

ALTER TABLE bus

ADD (CONSTRAINT pk_bus PRIMARY KEY (bid),

CONSTRAINT fk_ba FOREIGN KEY (aid)

REFERENCES agen(aid),

CONSTRAINT fk_bt FOREIGN KEY (tid)

REFERENCES tour(tid));

select table_name, column_name, constraint_name

from user_cons_columns;

---

DML

-- Заполнение таблиц учебной БД

-- заполнение таблицы agen

INSERT INTO agen("AID","ANAME","CITY","RATING")

VALUES(101,'Irtysh','Omsk',50);

INSERT INTO agen("AID","ANAME","CITY","RATING")

VALUES(102,'Vostok','Novosibisk',50);

INSERT INTO agen("AID","ANAME","CITY","RATING")

VALUES(104,'Ermak','Omsk',45);

INSERT INTO agen("AID","ANAME","CITY","RATING")

VALUES(106,'Valday','Moscow',60);

INSERT INTO agen("AID","ANAME","CITY","RATING")

VALUES(107,'Kedr','Tomsk',45);

INSERT INTO agen("AID","ANAME","CITY","RATING")

VALUES(108,'Saltan','Samara',55);

-- заполнение таблицы tour

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(201,'Grate Wall','China',2,102);

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(202,'Sea Star','Italy',1,101);

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(203,'Pattaya','Thailand',4,107);

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(205,'Attica','Greece',3,108);

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(206,'Paris','France',1,106);

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(208,'Alvor','Portugal',1,104);

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(209,'Majorka','Spain',1,106);

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(210,'Antalya','Turkey',2,106);

INSERT INTO tour("TID","TNAME","COUNTRY","TCLASS","AID")

VALUES(211,'Red Sea','Egypt',4,102);

-- заполнение таблицы bus