Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных. Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими

• Название: Литература для вузов isbn 9785941577705 в книге рассматриваются основные понятия баз данных и систем управления ими
• Анкор: Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных.pdf
• Дата: 16.04.2018
• Размер: 11.62 Mb.
• Имя файла: Кириллов В.В., Громов Г.Ю. - Введение в реляционные базы данных.pdf
• Тип: Литература; #18127
• страница: 25 из 28

Глава
21
"Итоговая успеваемость"
21.1. Описание предметной области
"Итоговая
успеваемость"
По рассмотренным ранее учебным планам производится обучение студентов.
В разд. 19.2.5 отмечалось, что каждая студенческая группа "привязывается" к рабочему учебному плану, в соответствии с которым должно проводиться обучение студентов, включенных в эту группу. Если, например, студенты какой-либо группы должны заниматься по разным планам (они выбрали раз- личные магистерские программы или дисциплины по выбору), то такая груп- па должна быть привязана к нескольким рабочим учебным планам, а кон- кретные студенты группы к одному из этих планов.
Привязку студентов к соответствующим группам и рабочим учебным пла- нам производят в приказах по "движению" студентов (переходы из группы в группу, уход в академический отпуск и возврат из него, отчисление и т. п.).
На рис. 21.1 приведены примеры таких приказов.
В тех случаях, когда все студенты группы занимаются по одному плану, то его номер не указывается в приказе, а выбирается из базы данных по номеру группы (см. рис. 20.10). В противном случае в приказе указывается либо но- мер плана, либо специализация, по которой будет производиться обучение конкретного студента (см. приказ № 314 на рис. 21.1).
В пунктах приказов также указывается одна или две даты. Это может быть дата успешного завершения обучения или отчисления, зачисления (перево- да) в группу (в этом случае подразумевается, что студент будет обучаться в этой группе до конца соответствующего учебного года или конца дейст- вия рабочего учебного плана), даты начала и окончания академического отпуска и т. п.

Глава 21. "Итоговая успеваемость"
383
Рис. 21.1. Примеры приказов по "движению" студентов
В предисловии отмечалось, что в данном курсе будет рассматриваться только часть интегрированной БД информационной системы СПбГУ ИТМО, касаю- щаяся некоторых сторон организации и планирования учебного процесса. По- этому затронем здесь лишь то, что непосредственно относится к итоговой ус- певаемости: краткие сведения о студентах (без адресов, личных документов и пр.) и некоторые аспекты чрезвычайно важной системы подготовки приказов.
При подготовке пункта студенческого приказа информационная система
СПбГУ ИТМО автоматически создает по тексту пункта значение одного из признаков этого пункта: академ
(в академическом отпуске), обучен
(обучает- ся), отчисл
(отчислен), диплом
(окончил с защитой диплома) и пр. Кроме то- го, система устанавливает для этого пункта состояние проект
, которое позво- ляет модифицировать текст пункта до тех пор, пока он не попадет в текст приказа и не будет утвержден вместе с другими пунктами. Теперь все изме- нения пункта могут производиться только путем его отмены (перевод в со- стояние отменен
) и размещения в новом приказе пункта с текстом "Во изме- нение …" со ссылкой на отмененный пункт.

Часть
VII.
Пример создания базы данных "UCHEB"
384
Следует отметить, что студент может одновременно обучаться в нескольких группах по разным рабочим учебным планам (например, будучи студентом дневного отделения, получать второе образование на вечернем отделении на- шего университета). В этом случае у такого студента должно быть несколько зачетных книжек и несколько учебных карточек для фиксации наборов оценок.
Напомним, что в итоговую успеваемость студента включаются оценки, кото- рые должны быть им получены в соответствии с данными столбцов
Формы итогового контроля базового учебного плана (см. рис. 19.3) или рабочих учебных планов, построенных по форме, показанной на рис. 19.6 (т. е. без форм текущего контроля).
Для информирования студентов и преподавателей, а также подготовки экза- менационных ведомостей (рис. 20.2), составляется расписание консультаций и экзаменов, проводимых в сессию.
Рис. 21.2.
Экзаменационная ведомость

Глава 21. "Итоговая успеваемость"
385
Если по тем или иным причинам студент не явился на плановый экзамен (за- чет) или получил неудовлетворительную оценку, то для внеплановой сдачи он должен получить в деканате экзаменационный лист, позволяющий ему явиться в установленный срок к указанному преподавателю (рис. 21.3) для сдачи (пересдачи) экзамена (зачета).
Рис. 21.3.
Экзаменационный лист
Рис.
21.4.
Журнал успеваемости группы

Часть
VII.
Пример создания базы данных "UCHEB"
386
Оценки из экзаменационных (зачетно-экзаменационных или зачетных) ведо- мостей и листов переносятся сотрудниками деканатов в групповые журналы успеваемости (рис. 21.4) и учебные карточки студентов, где кроме общих сведений о студенте помещаются все данные об его оценках по всем изучен- ным дисциплинам.
Анализ этих оценок позволяет выявить неуспевающих студентов и те дисци- плины, по которым ими не получены положительные оценки.
При завершении обучения по этим оценками составляется приложение к ди- плому или справка о прослушанных курсах, если студент отчисляется из университета.
21.2. Инфологическая модель
"Итоговая
успеваемость"
Здесь, в отличие от разд. 20.3, сначала представим ER-диаграмму "Итоговая успеваемость" (рис. 21.5), а затем приведем ее описание.
Так как оценивается успешность выполнения студентом учебных планов, то в ER-диаграмму включено восемь уже рассмотренных ранее сущностей, ко- торые, так или иначе, связаны с вновь созданными.
Начнем описание с сущности
Люди
, в которую включены атрибуты с основ- ными данными не только о студентах, но и о любом человеке, связанном с университетом. Дело в том, что студент может быть одновременно и со- трудником (например, студент оформлен на должность лаборанта кафедры, сотрудник обучается на вечернем отделении университета и пр.), выпускник может остаться в университете в качестве аспиранта или сотрудника и т. п.
Кроме того, ведомости и экзаменационные листы выписываются на препода- вателей, сведения о которых также должны существовать в указанной сущ- ности. Идентификатор этой сущности (
ИД
) — шестизначное целое число, ко- торое используется в качестве табельного номера сотрудника и (или) номера
(учетного имени) студента.
Так как некоторые люди меняют фамилии (а иногда имена, отчества и даже пол), то в модель включена сущность
Изм_люди
. Когда производится какое- либо изменение записи сущности
Люди
, то ее данные, существовавшие до внесения изменений, переносятся в
Изм_люди с указанием даты, до которой они были актуальными. Например, студентка Слепнева (номер 119743) до 11.06.2004 имела в информационной системе фамилию Притчина.

Глава 21. "Итоговая успеваемость"
387
_
_
_
_
_
_
_
_
_
_ _
_
_
_
_
_
_
_ _ _
_
_
_
_
_
_ _ _
_
_
_
_ _
_
_
_
_
_
_
_ _
_
_ _ _
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_ _
_
_
_ _
_ _
_ _
_
_
_
_
_
_
Рис. 21.5.
ER- диаграмма "Итоговая успеваемость"
В разд. 21.1 рассматривались приказы по "движению" контингента студен- тов. В момент создания пунктов таких приказов производится заполнение

Часть
VII.
Пример создания базы данных "UCHEB"
388
одной или нескольких записей сущности
Ученики
. Мы ограничимся упоми- нанием информации о пунктах приказов, данной в описании сущности
Уче- ники
(рис. 21.6), не рассматривая здесь достаточно сложной системы подго- товки приказов.
Рис. 21.6.
Описание сущности Ученики
Так как студент может обучаться более чем по одному учебному плану (см.
разд. 21.1), то
Ученики связаны с сущностью
Люди не прямо, а через сущность
Обучения
. В этой сущности кроме первичного составного ключа (
ЧЛВК_ИД
и
ВИД_ОБУЧ_ИД
) хранится и номер зачетной книжки (
НЗК
), соответствующий тому или иному виду обучения, данные о которых размещены в справочнике
Виды_обучения
(
1
— Основное образование,
2
— Второе образование и т. д.).
Итоговые оценки, получаемые студентами по изучаемым ими дисципли- нам, вводятся в сущность
Ведомости
, снабженную двумя справочниками

Глава 21. "Итоговая успеваемость"
389
Типы_ведомостей
(
1
— Ведомость,
2
— Экзаменационный лист,
3
— Переза- чет) и
Оценки
(
'5'
— отлично, и т. д.,
'зачет'
— зачет,
'незач'
— незачет и пр.), а также в сущность
Экз_листы_номера
Атрибутами сущности
Ведомость являются:

'члвк_ид'
— идентификатор студента;

'тв_ид'
— указатель на
ТИПЫ_ВЕДОМОСТЕЙ.ИД
сущности
Типы_ведомостей
;

'номер_документа'
— номер ведомости или экзаменационного листа;

'срок_сдачи'
— дата, до которой должен быть сдан экзамен или зачет;

'оценка'
— оценка, полученная при сдаче;

'дата'
— дата сдачи;

'состояние'
— признак, позволяющий включать (
актуальна
) или не включать (
неактуальна
, например уже пересдана) оценку в процедуры анализа успеваемости;

'сэс_ид'
— указатель на
СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК.ИД
сущности
Содержания элементов строк
, с помощью которого (через сущности
Элементы строк
,
Строки планов и
Дисциплины
) определяется название дисциплины, по которой получена данная оценка;

'отд_ид'
— указатель на идентификатор
ОТДЕЛЫ.ИД
сущности
Отделы
, с помощью которого определяется деканат, который сформировал ве- домость;

'ид'
— искусственный первичный уникальный идентификатор сущности.
Атрибутами сущности
Экз_листы_номера являются:

'члвк_ид'
и 'группа'
— идентификатор студента и номер его группы;

'преподаватель'
— идентификатор преподавателя, которому поручается принять экзамен (зачет);

'дата'
— дата окончания срока действия экзаменационного листа;

'учгод' и 'семестр'
— учебный год и семестр (весенний или осенний), в котором проводился плановый экзамен (зачет) по указанной в экзамена- ционном листе дисциплине;

'сэс_ид'
— указатель на
СОДЕРЖАНИЯ_ЭЛЕМЕНТОВ_СТРОК.ИД
сущности
Содержания элементов строк
, с помощью которого (через сущности
Элементы строк
,
Строки планов и
Дисциплины
) определяется название дисциплины, по которой должен быть принят экзамен (зачет);

'отд_ид'
— указатель на идентификатор
ОТДЕЛЫ.ИД
сущности
Отделы
, с помощью которого определяется деканат, который выдал экзаменацион- ный лист;

Часть
VII.
Пример создания базы данных "UCHEB"
390

'ид'
— искусственный первичный уникальный идентификатор сущности, одновременно являющийся номером экзаменационного листа.
Кроме рассмотренных ранее основных сущностей есть еще три вспомога- тельных:
Ведомости_номера
,
Сессия и
Учебные_года
Первая из них содержит сведения, позволяющие создать "шапку" ведомости
(рис. 21.2): номер ведомости (
'номер'
), учебный год (
'учгод'
), группу
(
'группа'
), дату (
'дата'
) и факультет (
'отд_ид'
), а также атрибут 'эст_ид'
— указатель на идентификатор
ЭЛЕМЕНТЫ_СТРОК.ИД
, сущности
Элементы строк
, с помощью которого (через сущности
Строки планов и
Дисциплины
) определяется название дисциплины.
Вторая содержит сведения для создания расписаний экзаменов и консультаций.
Третья содержит даты начала и конца учебных годов, начиная с
1996/1997
21.3. Объединенная инфологическая
модель "
UCHEB"
Напомним, что практическое изучение данной дисциплины будет проводить- ся с использованием тех таблиц базы данных информационной системы
СПбГУ ИТМО, аналоги которых были рассмотрены в разд. 20.3 и 21.2.
Необходимость в описании объединенной модели (рис. 21.7) возникла в свя- зи с тем, что при описании моделей "Учебные планы" и "Итоговая успевае- мость" для упрощения были опущены некоторые детали, чрезвычайно важ- ные для эксплуатации системы, но почти не влияющие на понимание особенностей ее функционирования.
Все сущности этой модели снабжены префиксом "
Н_
", который позволяет от- личать их от сущностей, не входящих в состав основных сущностей информа- ционной системы СПбГУ ИТМО. Кроме того, все слова в их именах и именах их атрибутов соединены символом "
_
" (подчеркивание), так как по правилам
Oracle имена должны состоять из символьной строки длиной не более 30 сим- волов или из нескольких слов, заключенных в двойные кавычки: "…".
Практически во все сущности добавлены атрибуты кто_создал
, когда_ создал
, кто_изменил и когда_изменил
. Когда в сущность добавляется новая запись, то в атрибуты кто_создал и кто_изменил вводится имя пользователя, создавшего эту запись (например,
KTY
— имя деканата факультета КТиУ), а в атрибуты когда_создал и когда_изменил
— дата и время создания записи
(например,
28.06.2004 16:27:54
).

Глава 21. "Итоговая успеваемость"
391
_ _ _
_
_
_
_
_
_
_ _
_
_
_
_
_ _
_
_
_
_
_
_
_
_
_
_ _
_
_
_
_
_
_ _
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_ _
_
_
_
_
_
_
_ _
_
_
_
_
_
_ _ _
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_ _ _
_
_
_
_
_ _
_
_ _ _
_ _
_
_
_ _
_
_
_
_
_ _ _
_
_
_
_
_
_ _
_
_
_
_
_
_ _
_
_
_
_
_
_
_
_
_
_
_ _
_
_
_
_
_ _
_
_
_
_
_ _
_
_
_
_
_ _ _
_ _ _
_ _ _
_ _ _
_ _ _
_ _ _
_
_
_
_
_
_
_
_
_
_
_
_ _
_
_
_ _
_
_ _ _
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_ _ _
_ _ _
_ _ _
_ _ _
_ _ _
_ _ _
_
_
_
_
_
_
_
_ _
_
_
_
_
_
_ _
_
_ _
_
_
_
_
_
_ _
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_
_ _
_
_
_
_
_ _
_ _
_ _
_
_
_
_
_
_
Рис. 21.7.
ER- диаграмма "Учебный процесс"

Часть
VII.
Пример создания базы данных "UCHEB"
392
В сущностях
Н_ОТДЕЛЫ
и
Н_ФОРМЫ_ОБУЧЕНИЯ
вместо атрибутов
НАИМЕНОВАНИЕ
включены атрибуты:
ИМЯ_В_ИМИН_ПАДЕЖЕ
,
ИМЯ_В_РОД_ПАДЕЖЕ
,
ИМЯ_В_ДАТ_ПАДЕЖЕ
,
ИМЯ_В_ВИН_ПАДЕЖЕ
,
ИМЯ_В_ТВОР_ПАДЕЖЕ
и
ИМЯ_В_ПРЕД_ПАДЕЖЕ
. Это сделано для того, чтобы при создании текстов приказов (см. рис. 21.1) можно было бы ввести в него наименование отдела и (или) формы обучения в нужном па- деже. (Отметим, что фамилии, имена и отчества вводятся в приказ в нужном падеже с помощью специальной функции.)
В сущность
Н_ПЛАНЫ
добавлены атрибуты с планами приема и проходными баллами, которые используются при планировании приема (планы для аби- туриентов).
В сущности
Н_ЛЮДИ
и
Н_ИЗМ_ЛЮДИ
добавлены атрибуты:

'ПИН'
— номер страхового свидетельства Государственного пенсионного страхования;

'ИНН'
— идентификационный номер налогоплательщика;

'ИНОСТРАН'
— признак гражданства (
0
— гражданин России,
1
— не гра- жданин России);

'ДАТА_СМЕРТИ'
— дата из свидетельства о смерти или запредельная дата
09.09.9999

Глава 22
Работаем с
SQL
Здесь приведен перечень запросов, которые должны быть "переведены" на язык SQL и реализованы во время лабораторных занятий по дисциплине "Базы данных".
22.1. Запросы
Инфологическая модель базы данных "Учебный процесс" показана на рис. 21.7.
Простая выборка
1.
Выдать содержимое всех столбцов таблицы
Н_ЦИКЛЫ_ДИСЦИПЛИН
2.
Выдать содержимое столбцов
Аббревиатура и
Наименование той же таб- лицы.
3.
Получить перечень квалификаций, присваиваемых выпускникам нашего университета.
Исключение дубликатов
4.
Выдать неповторяющиеся имена людей из таблицы
Н_ЛЮДИ
5.
Какие состояния студентов (признаки) используются в таблице
Н_УЧЕНИКИ
? Выборка вычисляемых значений.
6.
Из таблицы
Н_УЧЕБНЫЕ_ГОДА
получить названия и продолжительность каж- дого учебного года (напомним, что единица измерения дат — одни сутки).
7.
Используя сведения из таблицы
Н_ИЗМ_ЛЮДИ
, определить: целое число дней, прошедших с момента изменения фамилии до теку- щей даты (
SYSDATE
);

Часть
VII.
Пример создания базы данных "UCHEB"
394
целое число месяцев, прошедших с момента изменения фамилии до те- кущей даты.
Для округления используется функция
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 преобразуется в строку та- кой длины, которая вмещает только значащие цифры. С форматами можно познакомиться в разд.
4.6
. Для ограничения числа строк используйте псев- достолбец
ROWNUM
(
WHERE ROWNUM < …
или
WHERE <= …
) и ограничьте ре- зультат 50
- ю строками.
10.
Выдать номер (
ИД
),
Короткое_имя и название (
Имя_в_имин_падеже
) всех отделов факультета Компьютерных технологий и управления.

Глава 22. Работаем с
SQL
395
11.
Выдать фамилию, имя, отчество всех людей, фамилии которых начина- ются на "
Яков ".
12.
Выдать фамилию, имя, отчество всех людей из запроса номер 11, отсор- тировав строки по: 1) имени; 2) имени и отчеству; 3) фамилии, имени и отчеству.
13.
Повторить предыдущие запросы, задавая сортировку позициями, а не именами столбцов.
14.
Выдать фамилию, имя, отчество всех людей с фамилиями, начинающи- мися на "Э", "Ю" и "Я", с упорядочением по фамилии (составить не ме- нее двух вариантов запроса).
15.
Выдать номера, короткие имена и названия отделов с номерами 102, 111 и 212.
16.
Получить из таблицы
Н_НАПР_СПЕЦ
код и наименование специализаций специальности 230101 (напомним, что номера специализаций отличаются от номера специальности двумя последними цифрами).
17.
По таблице
Н_УЧЕНИКИ
познакомиться с особенностями процесса обуче- ния студентов с номерами 118843,119299,119457,119490,120224.
Для получения ФИО этих студентов можно воспользоваться функцией
Че- ловек (члвк_ид, 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 сравнивается с каждым из зна-

Часть
VII.
Пример создания базы данных "UCHEB"
396
чений 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']])
. С форматами можно познакомиться в разд.
4.6.
20.
Выполнить запрос 4, удалив из результата пробелы и точки (отсутствие имени у некоторых студентов), а также однобуквенные имена, получив- шиеся при вводе инициалов:
' '
,
'.'
,
'А'
,
'А.'
,
'Б'
,
'Б.'
,
'В'
,
'В.'
,
'Г'
,
'Г.'
,
'Д'
,
'Д.'
,
'Е'
,
'Е.'
,
'Ж'
,
'Ж.'
,
'З'
,
'З.'
,
'И'
,
'И.'
,
'К'
,
'К.'
,
'Л'
,
'Л.'
,
'М'
,
'М.'
,
'Н.'
,
'Н.'
,
'О'
,
'О.'
,
'П'
,
'П.'
,
'Р'
,
'Р.'
,
'С'
,
'С.'
,
'Т'
,
'Т.'
,
'У'
,
'У.'
,
'Ф'
,
'Ф.'
,
'Х'
,
'Х.'
,
'Ц'
,
'Ц.'
,
'Ш'
,
'Ш.'
,
'Э'
,
'Э.'
,
'Ю'
,
'Ю.'
Агрегирование данных
21.
Сколько Алексеев в таблице
Н_ЛЮДИ
? Результат выдать в виде одной тек- стовой строки вида:
"Алексей - 123"
22.
Определить количество различных имен людей в таблице
Н_ЛЮДИ
, удалив из результата пробелы, точки и однобуквенные имена (см. запрос 20).
23.
Определите вашу среднюю оценку (естественно, что в расчет должны входить лишь те оценки, которые имеют цифровой эквивалент). Создайте два запроса, в которых средняя оценки определяется с помощью функции среднее значение (функция
AVG
) и путем деления суммы (функция
SUM
)

Глава 22. Работаем с
SQL
397
оценок на их количество (функция
COUNT
). В результате необходимо ос- тавить два десятичных знака после запятой.
Для округления используется функция
ROUND(expr [,m])
, возвращающая expr
, округленное до m
- го десятичного знака; если m
опущено, то оно прини- мается равным
0
, а если m<0
, то округляются цифры левее десятичной точки.
24.
Сколько всего людей с фамилией Иванов и сколько у них различных имен и различных отчеств. Для получения результата использовать один запрос со следующими псевдонимами столбцов:
Всего
,
Разных_имен
,
Разных_отчеств
. В результат не должны включаться имена (отчества) в виде пробелов, точек и однобуквенных имен (см. запрос 20).
Проверку можно осуществить путем вывода списков Ивановых с неповто- ряющимися именами и с неповторяющимися отчествами.
Агрегирование данных с использованием фразы
HAVING
25.
Выдать различные: фамилии людей и число людей с каждой из этих фамилий, ограничив список фамилиями, встречающимися не менее 50 раз; имена людей и число людей с каждым из этих имен, ограничив список именами, встречающимися не менее 300 раз; отчества людей и число людей с каждым из этих отчеств, ограничив список отчествами, встречающимися не менее 300 раз.
Списки упорядочить по уменьшению количества фамилий (имен или от- честв).
26.
Найти группы, в которых 4.7.2004 было менее десяти обучающихся сту- дентов.
27.
В таблице
Н_ГРУППЫ_ПЛАНОВ
найти номера планов, по которым обучается
(обучалось) более 4 групп.
Естественное соединение таблиц
28.
Используя опыт, полученный при реализации запроса 23, выведите таблицу со средними оценками студентов вашей группы (
Номер
,
ФИО
,
Ср_оценка
).
При составлении запроса следует учитывать, что группы с таким же номе- ром существовали и в прошлые годы, а присутствие студента в вашей группе должно быть засвидетельствовано утвержденным приказом.

Часть
VII.
Пример создания базы данных "UCHEB"
398
29.
Выполнить предыдущий запрос, используя для получения номера, фами- лии, имени и отчества студента не функцию
Человек
, а текстовую строку, полученную из таблицы
Н_ЛЮДИ
примерно так, как формировался запрос 8.
30.
Составить запрос, позволяющий получить таблицу направлений (специ- альностей и специализаций), имеющую вид, показанный в табл. 22.1.
Таблица 22.1. Направления, специальности и специализации
ИД
Уровень
Дата_ГОС
Направление/
специальность
Тип стандарта
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
Информационно
- управляющие системы
Специализация

Глава 22. Работаем с
SQL
399
31.
Преобразовать предыдущий запрос так, чтобы в получаемой таблице пе- ред столбцом
Тип стандарта появился столбец
Квалификация
32.
Получить список студентов, зачисленных первого сентября позапрошло- го учебного года на первый курс очной формы обучения специальности
230101. В результат включить: номер группы; номер, фамилию, имя и отчество студента; номер и состояние пункта приказа; признак, характеризующий состояние студента; дату конца действия этого пункта.
Результат упорядочить по номеру группы и фамилии.
33.
Получить список студентов, по условиям п. 32, сохранив в нем только те строки, в которых состояние пунктами приказа равно 'утвержден'
34.
Получить список студентов по условиям п. 33, но только на конец учеб- ного года (31 августа).
Соединение таблицы со своей копией
35.
Выявить людей с одинаковыми фамилиями, именами и отчествами, но разными номерами. Список должен содержать упорядоченные строки с уникальными сочетаниями номера, фамилии, имени и отчества.
Вложенные подзапросы
36.
Преобразовать запрос п. 32 так, чтобы во фразе
FROM
осталось соединение только тех таблиц, столбцы которых входят в списки фраз
SELECT
и
ORDER
BY
. Остальные таблицы, данные из которых нужны для отбора требуемых строк результата, следует разместить во фразе
WHERE
, не используя их со- единений. Для организации подзапросов использовать предикат
IN
(про- верка на принадлежность).
37.
Преобразовать предыдущий запрос, используя для организации подза- просов предикат
EXISTS
(проверка на существование).
38.
Преобразовать запрос п. 29 так, чтобы во фразе
FROM
осталось соединение только тех таблиц, столбцы которых входят в списки фраз
SELECT
и
GROUP
BY
. Остальные таблицы, данные из которых нужны для отбора нужных строк результата, необходимо разместить во фразе
WHERE
39.
Вывести упорядоченный по ФИО список людей (с фамилиями, начи- нающимися на "До"), не являющихся или не являвшихся студентами

Часть
VII.
Пример создания базы данных "UCHEB"
400
СПбГУ ИТМО (т. е. данные о которых отсутствуют в таблице
Н_УЧЕНИКИ
).
Составить не менее двух вариантов запросов с условиями
IN
и
EXISTS
40.
Выполнить запрос п. 35, используя для его реализации вложенный подза- прос, а не соединение таблицы со своей копией (в запросе нельзя исполь- зовать
DISTINCT
).
Объединение запросов
41.
Сформировать запрос (единственный) для получения табл. 22.2.
Таблица 22.2. Итоговая успеваемость
Кол
-
во
1. Круглых отличников
362 2. Учеников без троек
1692 3. Круглых троечников
281
22.2. Ответы к некоторым запросам
8.
SELECT фамилия||' '|| SUBSTR (имя,1,1)||'.'|| SUBSTR (отчество,1,1)||'.'
FROM н_люди WHERE ROWNUM <= 50;
17.
SELECT члвк_ид,человек(члвк_ид,'И'),признак,состояние,начало,конец,группа
FROM н_ученики
WHERE члвк_ид IN (118843,119299,119457,119490,120224)
ORDER BY члвк_ид,начало;
26
SELECT группа,COUNT(*)
FROM н_ученики
WHERE '4.7.2007' BETWEEN начало AND конец
AND признак = 'обучен'
AND состояние = 'утвержден'
GROUP BY группа HAVING COUNT(*) < 10;

Глава 22. Работаем с
SQL
401
28.
SELECT v.члвк_ид номер, человек(v.члвк_ид,'И') ФИО,
ROUND(AVG(TO_NUMBER(v.оценка)),2) Ср_оценка
FROM н_ведомости v, н_ученики u
WHERE v.члвк_ид = u.члвк_ид
AND SYSDATE BETWEEN u.начало AND u.конец
AND u.состояние = 'утвержден'
AND v.оценка IN('5','4','3','2')
AND v.состояние = 'актуальна'
AND u.группа = '5101'
GROUP BY v.члвк_ид;
32.
SELECT u.группа,u.члвк_ид,l.фамилия,l.имя,l.отчество,u.п_пркок_ид,u.признак,u.ко нец
FROM н_ученики u,н_люди l,н_планы p,н_напр_спец n,н_направления_специал ns, н_формы_обучения f
WHERE u.план_ид = p.ид
AND p.напс_ид = ns.ид
AND ns.нс_ид = n.ид
AND p.фо_ид = f.ид
AND u.члвк_ид = l.ид
AND n.код_напрспец = '230101'
AND p.курс = 1 AND f.наименование = 'Очная'
AND p.учебный_год = '2005/2006'
AND u.начало = '1.9.2005'
AND u.состояние = 'утвержден'
ORDER BY u.группа,l.фамилия;
36.
SELECT u.группа,u.члвк_ид,l.фамилия,l.имя,l.отчество,u.п_пркок_ид, u.признак,u.конец
FROM н_ученики u,н_люди l
WHERE u.члвк_ид = l.ид
AND u.начало = '1.9.2005'
AND u.состояние='утвержден'
AND u.план_ид IN

Часть
VII.
Пример создания базы данных "UCHEB"
402
(SELECT p.ид
FROM н_планы p
WHERE p.ид = u.план_ид
AND p.курс = 1
AND p.учебный_год = '2005/2006'
AND p.фо_ид IN
(SELECT f.ид
FROM н_формы_обучения f
WHERE f.наименование = 'Очная')
AND p.напс_ид IN
(SELECT ns.ид
FROM н_направления_специал ns
WHERE ns.ид = p.напс_ид
AND ns.нс_ид IN
(SELECT n.ид
FROM н_напр_спец n
WHERE n.код_напрспец = '230101')))
ORDER BY u.группа,l.фамилия;