Лабораторная работа №6 Создание сложных запросов с использованием подзапросов и агрегированных функций
Целью работы является формирование навыков создания запросов к базе данных. Следует изучить команду SELECT, агрегированные функции и использование подзапросов. При выборке данных могут быть использованы четыре связанные таблицы, находящиеся в схемеMAI:
таблица отделов OTDс первичным ключомnom_otdи полями:
nom_otd– номер отдела;
name_nach– фамилия начальника;
etaj– этаж, на котором находится отдел;
nom_tel– номер телефона отдела.
таблица сотрудников SOTRс первичным ключомnom_sotrи внешним ключомnom_otdи полями:
nom_sort– номер сотрудника;
nom_otd– номер отдела, где работает сотрудник;
name1 – фамилия сотрудника;
name2 – имя сотрудника;
name3 – отчество сотрудника;
zarpl– зарплата сотрудника;
adres– адрес сотрудника;
city– город сотрудника.
Таблица детей сотрудников DETIс составным первичным ключомnom_sort,name_childи внешним ключомnom_sotr:
nom_sotr– номер сотрудника;
name_child– имя ребенка;
voz- возраст ребенка;
nom_school– номер школы, где учится ребенок. Это поле может иметь пустое значение, если ребенок не учится в школе.
Таблица школ SHOOLс первичным ключомnom_schoolи полями:
nom_school– номер школы;
name_dir– фамилия директора школы;
phone- телефон школы.
Связь таблиц показана на рис. 4
Рис. 4
Варианты работы
Вариант 1
Выбрать сотрудников, у которых в школе с номером 10 учатся более двух детей.
Вариант 2
Выбрать сотрудников имеющих 2 и более детей, учащихся в школе, где директор – Иванов В.П.
Вариант 3
Выбрать школы, в которых учатся дети сотрудников, работающих в отделе N 119.
Вариант 4
Выбрать сотрудников, имеющих более 3 детей, один из которых учится в школе.
Вариант 5
Выбрать отделы, в которых более 3 сотрудников не имеют детей.
Вариант 6
Выбрать отделы, сотрудники которых имеют более одного ребенка с возрастом 20 лет.
Вариант 7
Выбрать отделы, в которых сотрудники имеют детей в возрасте менее 7 лет учатся в школе.
Вариант 8
Выбрать сотрудников, имеющих только двух детей, один из которых учится в школе, а другой - нет.
Вариант 9
Выбрать детей, родители которых не имеют однофамильцев среди сотрудников.
Вариант 10
Выбрать сотрудников, которые получают зарплату выше средней зарплаты его отдела.
Вариант 11
Выбрать сотрудников, дети которых учатся в разных школах.
Вариант 12
Выбрать сотрудников, имеющих детей одного возраста.
Контрольные вопросы
В какой части опции WHEREоператораSELECTдолжен находиться подзапрос?
В чем преимущество подзапросов?
Какая реляционная операция выполняется при многотабличной выборке?
Какая логическая операция используется, если подзапрос возвращает несколько строк?
С какими функциями используется опция группировки?
Лабораторная работа 7 Создание анонимного блока
Целью работы является создание анонимного блока, как основной программной единицы программирования сервера в системе ORACLE. При выводе данных необходимо форматирование вывода таким образом, что бы данные выводились в табличном виде (можно использовать функциюRPAD). Вывод данных должен осуществляться из таблиц, используемых в лабораторной работе №6.
Порядок выполнения
1. По варианту, заданному преподавателем, составить анонимный блок, реализующий условия варианта;
2. Выполнить анонимный блок в среде SQLPlusилиSQLWorksheet;
3. Проверить правильность работы анонимного блока;
4. Составить отчет, в котором привести анонимный блок и ответы на контрольные вопросы.