Занятие №8 (контрольное)

На контрольном занятии необходимо создать java-класс, включающий в себя следующие обязательные действия:

1. Подготовить предложение SELECT для выполнения запроса в соответствии с выданным преподавателем вариантом.

2. Выполнить запрос и получить результат в объект типа ResultSet.

3. Создать объект типа CachedRowSet и заполнить его результатами запроса, находящимися в объекте типа ResultSet, методом populate().

4. Сохранить объект типа CachedRowSet в файле, имя которого должно совпадать с идентификатором студента, выполняющего задание, и находиться в корне его домашнего каталога.

Название класса должно совпадать с идентификатором студента, выполняющего задание. Класс и исходный файл с расширением .java должны находится в корне домашнего каталога студента, выполняющего задание.

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

На защите необходимо быть готовым продемонстрировать альтернативную реализацию любого, указанного преподавателем, шага выполненного контрольного задания; уметь восстанавливать сохраненный объект типа CachedRowSet и выводить на экран содержащиеся в нем данные.

Время выполнения контрольного задания – 1 час 20 минут.

Варианты запросов.

(Инфологическая модель базы данных "Учебный процесс" приведена в файле inf_mod.doc)

Простая выборка.

1. Выдать содержимое всех столбцов таблицы Н_ЦИКЛЫ_ДИСЦИПЛИН.

2. Выдать содержимое столбцов АББРЕВИАТУРА и НАИМЕНОВАНИЕ той же таблицы.

3. Получить перечень квалификаций, присваиваемых выпускникам нашего университета.

Исключение дубликатов.

4. Выдать неповторяющиеся имена людей из таблицы Н_ЛЮДИ.

5. Какие состояния студентов (признаки) используются в таблице Н_УЧЕНИКИ.

Выборка вычисляемых значений.

6. Из таблицы Н_УЧЕБНЫЕ_ГОДА получить названия и продолжительность каждого учебного года (напомним, что единица измерения дат - одни сутки).

7. Используя сведения из таблицы Н_ИЗМ_ЛЮДИ, определить:

а) целое число дней, прошедших с момента изменения фамилии до текущей даты (SYSDATE);

б) целое число месяцев, прошедших с момента изменения фамилии до текущей даты.

• Для округления используется функция ROUND(expr [,m]), возвращающая expr, округленное до m-го десятичного знака; если m опущено, то оно принимается равным 0, а если m < 0, то округляются цифры левее десятичной точки.

• Для получения количества месяцев между датами d1 и d2 используется функция MONTHS_BETWEEN (d1,d2). Если d1 > d2, то результат положителен, иначе отрицателен.

Выборка с использованием фразы WHERE и упорядочением.

8. Выдать фамилию и инициалы людей (запрос оформить в виде выражения, чтобы получить в результате один столбец, например, "Сидоров С.С.", а не три: "Сидоров","С." и "С.").

• Для выделения из строки "str" "len" символов, начиная с "pos", используется функция SUBSTR(str,pos[,len]). Если "len" отсутствует, то выдются символы от "pos" до конца "str". При "pos" < 0 первый выделяемый символ определяется не от начала, а от конца "str".

• Для соединения текстовых значений используется оператор ||, а значения текстовых констант должны быть заключены в апострофы (например, точка и пробел: '.' и ' ').

• Для ограничения числа строк используйте псевдостолбец ROWNUM (WHERE ROWNUM < … или WHERE <= …) и ограничьте результат 50-ю строками.

9. Выдать номер, фамилию и инициалы людей (запрос оформить в виде выражения, чтобы получить, в результате один столбец, например, "104567 Сидоров С.С.").

• Для преобразования числового значения или даты (expr) в текстовое значение по формату, заданному в fmt (с необязательным указанием национального языка nlsparam), используется функция TO_CHAR (expr [, fmt [, 'nlsparam']] ). Если fmt опущено, то expr преобразуется в строку такой длины, которая вмещает только значащие цифры. С форматами можно познакомиться в Кратком справочнике по Oracle ( http://www.cs.ifmo.ru , http://www.cis.ifmo.ru).

• Для ограничения числа строк используйте псевдостолбец ROWNUM (WHERE ROWNUM < … или WHERE <= …) и ограничьте результат 50-ю строками.

10. Выдать номер (ИД) Короткое_имя и название (имя_в_имин_падеже) всех отделов факультета Компьютерных технологий и управления.

11. Выдать "Фамилия, Имя, Отчество" всех людей, фамилии которых начинаются на "Яков".

12. Выдать "Фамилия, Имя, Отчество" всех людей из запроса номер 11, отсортировав строки по: 1) имени, 2) имени и отчеству 3) фамилии, имени и отчеству.

13. Повторить предыдущие запросы задавая сортировку позициями, а не именами столбцов.

14. Выдать " фамилия, имя, отчество" всех людей с фамилиями, начинающимися на "Э", "Ю" и "Я", с упорядочением по фамилии (составить не менее двух вариантов запроса).

15. Выдать номера, короткие имена и названия отделов с номерами 102, 111 и 212.

16. Получить из таблицы н_напр_спец код и наименование специализаций специальности 220100 (напомним, что номера специализаций отличаются от номера специальности двумя последними цифрами).

17. По таблице Н_УЧЕНИКИ познакомиться с особенностями процесса обучения студентов с номерами 114052,114746,115264,116483,117127,118705.

• Для получения ФИО этих студентов можно воспользоваться функцией

Человек(члвк_ид, padej , priz IN NUMBER DEFAULT 0, nach DATE DEFAULT SYSDATE)

выводящей фамилию имя и отчество (priz=0) или фамилию и инициалы (priz=1) человека с

номером члвк_ид в заданном падеже ('И','Р','Д','В','Т','П') и на заданную дату

(по умолчанию устанавливается priz=0 и текущая (системная) дата – SYSDATE.

• Для соединения текстовых значений используется оператор ||, а значения текстовых констант должны быть заключены в апострофы (например, точка и пробел: '.' и ' ').

18. Вывести из таблицы Н_ВЕДОМОСТЬ ваши оценки. Список должен содержать оценку, её преобразованное значение (5 → отлично, 4 → хорошо, 3 → удовлетворительно, 2 → неудовлетвори-тельно, зачет → зачет, незач → незачет, осв → освобождение, неявка → неявка, 99 → диплом с отли-чием, . → выдача экзаменационного листа, - → отсутствие данных), которому необходимо дать псевдоним – Эквивалент, дату получения оценки и ссылку (сэс_ид) на идентификатор таблицы Н_СОДЕРЖИМОЕ_ЭЛЕМЕНТОВ_СТРОК. Зная сэс_ид можно получить имя дисциплины, по которой получена оценка. Список упорядочить по сэс_ид и дате.

Преобразование оценок можно осуществить с помощью функции DECODE:

DECODE (expr, search1, result1 [, search2, result2] ... [default]),

где значение выражения expr сравнивается с каждым из значений search.

• Если expr совпадает с каким-либо search, возвращается соответствующее значение result.

• Если ни одного совпадения не найдено, возвращается значение default (или NULL, если значение default опущено).

• Expr может иметь любой тип данных, но значения search должны иметь тот же тип, как у expr.

• Возвращаемое значение принудительно приводится к тому типу данных, как у result.

19. Вывести список тех ваших оценок, которые заданы цифрами 5, 4, 3, 2. Список должен содержать оценку, её удвоенный цифровой эквивалент (псевдоним - Оценка*2), дату получения оценки и ссылку (сэс_ид) на идентификатор таблицы Н_СОДЕРЖИМОЕ_ЭЛЕМЕНТОВ_СТРОК. Зная сэс_ид можно получить имя дисциплины, по которой получена оценка. Список упорядочить по сэс_ид и дате.

• Следует иметь в виду, что любое имя Oracle может в чистом виде содержать лишь буквы, цифры и три символа: $, #, _. Имена, содержащие другие символы (включая пробелы) необходимо заключать в кавычки, например, "Оценка*2".

• Для преобразования текстового значения (char) в число по формату в fmt (с необязательным указанием нацязыка nlsparam), используется функция TO_NUMBER (char [, fmt [, 'nlsparam']]). С форматами можно познакомиться в Кратком справочнике по Oracle ( http://www.cs.ifmo.ru , http://www.cis.ifmo.ru).

20. Выполнить запрос 4, удалив из результата пробелы и точки (отсутствие имени у некоторых студентов), а также однобуквенные имена, получившиеся при вводе инициалов: ' ', '.', 'А', 'А.', 'Б', 'Б.', 'В', 'В.', 'Г', 'Г.', 'Д', 'Д.', 'Е', 'Е.', 'Ж', 'Ж.', 'З', 'З.', 'И', 'И.', 'К', 'К.', 'Л', 'Л.', 'М', 'М.', 'Н.', 'Н.', 'О', 'О.', 'П', 'П.', 'Р', 'Р.', 'С', 'С.', 'Т', 'Т.', 'У', 'У.', 'Ф', 'Ф.', 'Х', 'Х.', 'Ц', 'Ц.', 'Ш', 'Ш.', 'Э', 'Э.', 'Ю', 'Ю.'

Агрегирование данных.

21. Сколько "Алексеев" в таблице Н_люди? Результат выдать в виде одной текстовой строки вида: "Алексей - 123".

22. Определить количество различных имен людей в таблице Н_ЛЮДИ, удалив из результата пробелы, точки и однобуквенные имена (см. запрос 20).

23. Определите вашу среднюю оценку (естественно, что в расчет должны входить лишь те оценки, которые имеют цифровой эквивалент). Создайте два запроса, в которых средняя оценки определяется с помощью функции среднее значение (функция AVG) и путем деления суммы (функция SUM) оценок на их количество (функция COUNT). В результате необходимо оставить два десятичных знака после запятой.

• Для округления используется функция ROUND(expr [,m]), возвращающая expr, округленное до m-го десятичного знака; если m опущено, то оно принимается равным 0, а если m < 0, то округляются цифры левее десятичной точки.

24. Сколько всего людей с фамилией Иванов и сколько у них различных имен и различных отчеств. Для получения результата использовать один запрос со следующими псевдонимами столбцов: Всего, Разных_имен, Разных_отчеств. В результат не должны включаться имена (отчества) в виде пробелов, точек и однобуквенных имен (см. запрос 20).

Проверку можно осуществить путем вывода списков Ивановых с неповторяющимися именами и с неповторяющимися отчествами.

Агрегирование данных с использованием фразы HAVING.

25. Выдать различные:

1. фамилии людей и число людей с каждой из этих фамилий, ограничив список фамилиями, встречающимися не менее 50 раз.

2. имена людей и число людей с каждым из этих имен, ограничив список именами, встречающимися не менее 300 раз.

3. отчества людей и число людей с каждым из этих отчеств, ограничив список отчествами, встречающимися не менее 300 раз.

Списки упорядочить по уменьшению количества фамилий (имен или отчеств).

26. Найти группы, в которых 4.7.2004 было менее десяти обучающихся студентов.

27. В таблице Н_ГРУППЫ_ПЛАНОВ найти номера планов, по которым обучается (обучалось) более 4 групп.

Естественное соединение таблиц

28. Используя опыт, полученный при реализации запроса 23, выведите таблицу со средними оценками студентов вашей группы (Номер, ФИО, Ср_оценка).

• При составлении запроса следует учитывать, что группы с таким же номером существовали и в прошлые годы, а присутствие студента в вашей группе должно быть подтверждено утвержденным приказом.

29. Выполнить предыдущий запрос используя для получения номера, фамилии, имени и отчества студента не функцию Человек, а текстовую строку, полученную из таблицы Н_ЛЮДИ примерно так, как формировался запрос 8.

30. Составить запрос, позволяющий получить таблицу направлений (специальностей и специализаций), имеющую следующий вид:

ИД	Уров	Дата_ГОС	Направление/специальность		Тип стандарта
68	3	01.03.1995	552800	Информатика и вычислительная техника	Направление подготовки магистров
143	3	01.03.1995	552811	Базы данных	Магистерская программа
144	3	01.03.1995	552813	Сети ЭВМ и телекоммуникации	Магистерская программа
145	3	01.03.1995	552820	Системы реального времени	Магистерская программа
700	3	01.03.2000	220100	Вычислительные машины, комплексы, системы и сети	Специальность
1251	3	01.03.2000	220104	Системы телекоммуникаций и компьютерной безопасности	Специализация
792	3	01.03.2000	220109	Технология разработки программных систем	Специализация
1250	3	01.03.2000	220111	Открытые информационно-вычислительные системы	Специализация
1249	3	01.03.2000	220112	Информационно-управляющие системы	Специализация

31. Преобразовать предыдущий запрос так, чтобы в получаемой таблице перед столбцом «Тип стандарта» появился столбец «Квалификация»

32. Получить список студентов, зачисленных первого сентября позапрошлого учебного года на первый курс очной формы обучения специальности 220100. В результат включить:

• номер группы,

• номер, фамилию, имя и отчество студента,

• номер и состояние пункта приказа,

• признак, характеризующий состояние студента,

• дату конца действия этого пункта,

Результат упорядочить по номеру группы и фамилии.

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

34. Получить список студентов по условиям п. 33, но только на конец учебного года (31 августа).

Соединение таблицы со своей копией

35. Выявить людей с одинаковыми фамилиями, именами и отчествами, но разными номерами. Список должен содержать упорядоченные строки с уникальными сочетаниями номера, фамилии, имени и отчества.

Вложенные подзапросы

36. Преобразовать запрос п. 32 так, чтобы во фразе FROM осталось соединение только тех таблиц, столбцы которых входят в списки фраз SELECT и ORDER BY. Остальные таблицы, данные из которых нужны для отбора нужных строк результата, необходимо разместить во фразе WHERE, не используя их соединений. Для организации подзапросов использовать предикат IN (проверка на принадлежность).

37. Преобразовать предыдущий запрос, используя для организации подзапросов предикат EXISTS (проверка на существование).

38. Преобразовать запрос п. 29 так, чтобы во фразе FROM осталось соединение только тех таблиц, столбцы которых входят в списки фраз SELECT и GROUP BY. Остальные таблицы, данные из которых нужны для отбора нужных строк результата, необходимо разместить во фразе WHERE.

39. Вывести упорядоченный по ФИО список людей (с фамилиями, начинающимися на "До"), не являющихся или не являвшихся студентами СПбГУИТМО (т.е. данные о которых отсутствуют в таблице Н_УЧЕНИКИ). Составить не менее двух вариантов запросов с условиями IN и EXISTS.

40. Выполнить запрос п. 35, используя для его реализации вложенный подзапрос, а не соединение таблицы со своей копией (в запросе нельзя использовать DISTINCT).

Объединение запросов

41. Сформировать запрос (единственный) для получения таблицы вида:

	Кол-во
1. Круглых отличников	362
2. Учеников без троек	1692
3. Круглых троечников	281