Практические задачи по курсу Теория баз данных

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

1. Выдать имена факультетов из таблицы fakultety.
2. Выдать содержимое всех столбцов таблицы uch_cikly (Учебные циклы).

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

3. Выдать неповторяющиеся имена студентов из таблицы studenty.
4. Какие статусы (status) используются в таблице vyp_kaf (Выпускающие кафедры).

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

5. Получить название и продолжительность каждого учебного года таблицы uch_goda (напомним, что единица измерения дат - одни сутки).
6. Выдать фамилию и инициалы студентов (запрос оформить в виде выражения, чтобы получить в результате один столбец, например, "Сидоров С.С.", а не три: "Сидоров","С." и "С.").

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

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

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

Для преобразования числового значения или даты (expr) в текстовое значение по формату, заданному в fmt, используется функция TO_CHAR(expr[,fmt])

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

8. Выдать номер (otdel) аббревиатуру (kor_imia_otd) и название (imia_otd) всех отделов факультета Компьютерных технологий и управления (КТиУ).
9. Выдать "Фамилия, Имя, Отчество" всех студентов, фамилии которых начинаются на "Сидор".
10. Выдать "Фамилия, Имя, Отчество" всех студентов из запроса номер 9, отсортировав строки по: 1) имени, 2) имени и отчеству 3) фамилии, имени и отчеству.
11. Повторить предыдущие запросы задавая сортировку позициями, а не именами столбцов.
12. Выдать "Фамилия, Имя, Отчество" всех студентов с фамилиями, начинающимися на "Э", "Ю" и "Я" с упорядочением по фамилии (составить не менее двух вариантов запроса).
13. Выдать номера, аббревиатуры и имена отделов с номерами 102, 111 и 212.
14. Получить номера статусы и имена специализаций специальности 220100 (напомним, что номера специализаций отличаются от номера специальности двумя последними цифрами).
15. Для студентов третьего курса дневной формы обучения и специализаций специальности 220100 найти в таблице vyp_kaf номера рабочих планов (nom_plan) на 1999/2000 учебный год (nachalo = '01.09.1999').
16. Из таблицы plan получить список номеров дисциплин для одного из рабочих планов, полученных в запросе номер 15 (например, для плана 276).
17. Получить сведения о содержимом дисциплин (таблица discip), номера которых получены в запросе номер 16. Перечень должен содержать: номер и краткое название дисциплины, семестр, числа часов на лекции, лабораторные и практические занятия, самостоятельную работу студента, общий объем дисциплины и вид контроля. Выражение для определения общего объема дисциплины необходимо снабдить псевдонимом "Всего". Список упорядочить по номеру семестра и краткому имени дисциплины.
18. Вывести из таблицы vedomost ваши оценки. Список должен содержать оценку (ocenka), её преобразованное значение (отл => 5, хор => 4, удовл => 3, неуд => 2, зачет => зачет, незач => незачет, неявка => неявка), которому необходимо дать псевдоним - Оценка, порядковый номер (plan). Список упорядочить так, чтобы преобразованные оценки выводились начиная с 5.

Преобразование можно осуществить с помощью функции DECODE:
DECODE (expr, search1, result1 [, search2, result2] ... [default]), где значение выражения expr сравнивается с каждым из значений search. Если expr совпадает с каким-либо search, возвращается соответствующее значение result. Если ни одного совпадения не найдено, возвращается значение default (или NULL, если значение default опущено). Expr может иметь любой тип данных, но значения search должны иметь тот же тип, как у expr. Возвращаемое значение принудительно приводится к тому типу данных, как у result. При составлении запроса необходимо учитывать, что эквивалент оценки может быть задан как в текстовом (например, 'отл' => '5'), так и в цифровом (например, 'отл' => 5) виде.

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

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

20. Сколько "Алексеев" в таблице Студенты ? Результат выдать в виде "Алексей -", количество.
21. Для дисциплин из списка, полученного в запросе номер 16 (он уже использовался в запросе номер 17), определить для каждого семестра суммарное число часов на лекции, лабораторные и практические занятия, самостоятельную работу студента и общий объем дисциплин.
22. Определите вашу среднюю оценку (естественно, что в расчет должны входить лишь те оценки, которые имеют цифровой эквивалент). Создайте два запроса, в которых средняя оценки определяется путем деления суммы (функция SUM) оценок на их количество (функция COUNT) и путем использования функции AVG (среднее значение).
23. Сколько всего студентов с фамилией Иванов и сколько у них различных имен и различных отчеств. Для получения результата использовать один запрос со следующими псевдонимами столбцов: Всего, Разных_имен, Разных_отчеств. Проверку можно осуществить путем вывода списков Ивановых с неповторяющимися именами и с неповторяющимися отчествами.
24. По таблице discip определить максимальное число лекций, практических и лабораторных занятий в списке дисциплин рабочих планов, а затем вывести сведения о дисциплинах с максимальным числом: 1) лекций, 2) практических занятий и 3) лабораторных занятий.

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

25. 1) Выдать различные фамилии студентов и число студентов с каждой из этих фамилий, ограничив список фамилиями, встречающимися не менее 20 раз. 2) Выдать различные имена студентов и число студентов с каждым из этих имен, ограничив список именами, встречающимися не менее 100 раз. 3) Выдать различные отчества студентов и число студентов с каждым из этих отчеств, ограничив список отчествами, встречающимися не менее 100 раз.

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

26. Найти группы, в которых 4.7.1999 было менее дюжины обучающихся студентов.
27. В таблице grup_spec найти номера планов, по которым обучается (обучалось) более 4 групп.

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

28. Получить список неповторяющихся имен дисциплин для одного из рабочих планов, полученных в запросе номер 15 (например, для плана 276).
29. Улучшить "читабельность" запроса номер 18, заменив в списке порядковый номер плана на номер семестра и короткое название дисциплины из таблицы discip.
30. Улучшить "читабельность" запроса номер 29, заменив в списке короткое название дисциплины на её полное название из таблицы imia_disc.
31. Получить список студентов, находившихся 4.7.1999 на втором курсе дневной формы обучения специальности 220100. В список включить номер группы, признак, фамилию, имя и отчество. Список упорядочит по номеру группы и фамилии.
32. Получить список контрактных студентов, находившихся 4.7.1999 на втором курсе дневной формы обучения специальности 220100. Форма списка такая же, как в запросе номер 31.

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

33. Выявить студентов с одинаковыми фамилиями (начинающимися на букву "С"), именами и отчествами, но разными номерами (упорядочить по Фамилии,Имени,Отчеству и Номеру).

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

34. Реализовать запросы номера 24 так, чтобы максимальное число лекций, практических и лабораторных занятий из списка дисциплин рабочих планов прямо (без предварительного запоминания) учитывалось в запросах на получение сведений о дисциплинах с максимальным числом: 1) лекций, 2) практических занятий и 3) лабораторных занятий.
35. Реализовать запрос номер 31, используя подзапрос (в основном запросе оставить только те таблицы, данные из которых используются в результирующем списке).
36. Получить список бюджетных студентов, находившихся 4.7.1999 на втором курсе дневной формы обучения специальности 220100. Форма списка такая же, как в запросах номер 31, 32 и 35.
37. Реализовать запрос номер 33, используя подзапрос.
38. Вывести список студентов (с фамилиями, начинающимися на "Па"), данные о которых отсутствуют в таблице uchenik (список упорядочить по Фамилии,Имени и Отчеству). Составить не менее двух вариантов запросов с условиями, включающими IN и EXISTS).
39. Составить запрос на получение содержимого дисциплин, изучаемых в 1999/2000 учебном году студентами третьего курса дневной формы обучения специальности 220100 (см. запросы с номерами 15, 16 и 17). Перечень должен содержать: номер и краткое название дисциплины, аббревиатуру кафедры исполнителя, семестр, числа часов на лекции, лабораторные и практические занятия, самостоятельную работу студента, общий объем дисциплины и вид контроля. Выражение для определения общего объема дисциплины необходимо снабдить псевдонимом "Всего". Список упорядочить по номеру семестра и краткому имени дисциплины.

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

40. Получить список бюджетных и контрактных студентов, находившихся 4.7.1999 на втором курсе дневной формы обучения специальности 220100. В список включить номер группы, константу 'бюджет' (для бюджетного студента) или 'контракт' (для контрактного студента), признак, фамилию, имя и отчество. Список упорядочить по номеру группы и фамилии (по первому и четвертому столбцам).

Представления.

41. Создать представление "Дисциплины" для получения следующего содержимого дисциплин: номера и краткого названия дисциплины, аббревиатуры кафедры исполнителя, семестра, числа часов на лекции, лабораторные и практические занятия, самостоятельную работу студента, общий объем дисциплины и вид контроля. Всем перечисленным столбцам и выражениям дать, соответственно, следующие аббревиатуры: Номер, Дисциплина, Кафедра, Сем, Лек, Лаб, Прак, СРС, Всего и Контр.
42. Вывести двадцать первых строк представления Дисциплины. Для этого нужно воспользоваться псевдостолбцом ROWNUM, содержащим позицию строки среди строк, отобранных запросом.
43. Из представления Дисциплины получить содержимое дисциплин, изучаемых в 1999/2000 учебном году студентами третьего курса дневной формы обучения специальности 220100 (см. запрос номер 39).
44. Скорректировать запрос номер 43, оставив в списке только: Сем, Контр и Имя_дисциплины, где имя дисциплины должно быть получено из таблицы imia_disc.