4. Выполнение лабораторной работы

По аналогии с примерами, приведенными в п. 3 реализовать запросы а) .. е), указанные в варианте задания.

5. Содержание отчета

Содержание отчета:

— скриншоты окон конструктора запросов;

— скриншоты окон с наборами данных, возвращаемыми запросами.

6. Варианты заданий

Варианты заданий приведены в ПРИЛОЖЕНИИ.

Лабораторная работа 4. Разработка запросов на языке SQL. Часть 1.

1. Операторselect

Простейший вариант оператора был приведен в лабораторной работе 2, в более общем случае синтаксис оператора выглядит следующим образом:

select <список выбора>

from <список таблиц>

where <условие>

go

Здесь <список выбора> — список столбцов таблиц или выражений с их участием, значения которых должен вернуть оператор, если <список выбора> содержит более одного элемента, они разделяются запятыми, для выборки всех столбцов указывается *; <список таблиц> содержит перечень таблиц, из которых осуществляется выборка, если в списке более одного элемента, они разделяются запятыми, в разделе from могут использоваться также представления и производные таблицы (см. следующие работы); <условие> аналогично условиям, рассмотренным в операторах delete и update, в условиях могут использоваться подзапросы, кванторы и др. возможности (см. следующие работы).

Оператор select реализует в языке SQL все операторы, предусмотренные реляционной алгеброй (РА). Пусть в БД существуют таблицы, созданные и модифицированные так, как описано в лабораторной работе 2, а именно таблица Группа:

Id	Номер	Факультет
7	3002К	3
8	4001	4
9	4002КФс	4
10	5001	5

таблица Студент:

Ном_Зач	ФИО	Группа
11	Лисичкин	8
22	Сыроежкин	8
33	Груздев	9
44	Сморчков	9
55	Волнушкин	10
77	Строчков	10
87	Краснов	7
88	Белов	7

таблица Дисциплина:

Id	Наименование
1	АСУ
2	БД
3	ФиЛП

таблица Успеваемость:

Id	Ном_Зач	Оценка	Id_Дисциплины
1	11	5	2
2	11	4	3
3	22	4	2
4	22	4	3
5	33	3	2
6	33	4	3
7	44	4	2
8	44	3	3
9	55	4	2
10	55	5	1
11	77	5	2
12	77	4	1
13	88	3	2
14	88	3	1
16	87	5	1

тогда операцию выборки списка групп 4-го факультета, осуществит оператор:

select * from Группа where Факультет = 4

go

проекцию, возвращающую список всех групп, выполнит оператор:

select Номер from Группа

go

соединение, формирующее список групп и студентов, реализует оператор:

select * from Группа,Студент

where Id = Группа

go

оператор:

select Номер 'Группа', ФИО, Наименование 'Дисциплина', Оценка

from Группа, Студент, Дисциплина, Успеваемость

where Группа.Id = Группа and

Студент.Ном_Зач = Успеваемость.Ном_Зач and

Дисциплина.Id = Id_Дисциплины and

Номер = '4001'

go

использует комбинацию соединения выборки и проекции, что бы вернуть список оценок студентов группы 4001. Как следует из примера, в случае, если столбцы различных таблиц имеют одинаковые имена, для уточнения в качестве префикса может использоваться имя таблицы, отделяемое от имени столбца точкой. Для удобства восприятия пользователем набора данных, возвращаемого запросом, столбцам можно присваивать псевдонимы, используя директиву as или просто разделяя пробелом имя столбца (выражение) и псевдоним. Если заданы, псевдонимы подставляются в заголовок набора данных, возвращаемого запросом.

По умолчанию для списка выбора действует директива all, поэтому запрос «По каким дисциплинам были выставлены оценки?»:

select Наименование 'Есть оценки'

from Дисциплина, Успеваемость

where Дисциплина.Id = Id_Дисциплины

go

вернет каждую дисциплину столько раз, сколько записей встречается в таблице Успеваемость, а запрос:

select distinct Наименование 'Есть оценки'

from Дисциплина, Успеваемость

where Дисциплина.Id = Id_Дисциплины

go

исключит повторяющиеся значения.

Роль, аналогичную операции переименования атрибутов РА, играют псевдонимы таблиц, указываемые в разделе from и отделяемые от имени таблицы директивой as или пробелом. Псевдонимы необходимы, например, при выполнении соединения таблицы с собой. Запрос «Кто из студентов получил одинаковые оценки по БД и по ФиЛП?», может быть реализован в SQL с использованием псевдонимов:

select ФИО

from Студент, Дисциплина Д1, Дисциплина Д2,

Успеваемость У1, Успеваемость У2

where Д1.Наименование = 'БД' and Д2.Наименование = 'ФиЛП' and

Д1.Id = У1.Id_Дисциплины and Д2.Id = У2.Id_Дисциплины and

У1.Ном_Зач = Студент.Ном_Зач and

У2.Ном_Зач = Студент.Ном_Зач

go

В примере две записи из таблицы Дисциплина рассматриваются под псевдонимами Д1 и Д2, а две связанные с ними записи таблицы Успеваемость под псевдонимами У1 и У2, после чего выполняется их соединение.

Следует учитывать, что при использовании двух псевдонимов для одной таблицы, каждая запись может быть «выбрана» вначале под первым, а затем под вторым. Данную ситуацию иллюстрирует запрос «Какие студенты получили одинаковые оценки по БД?», если в выражении С1.Ном_Зач > С2.Ном_Зач заменить > на < >, каждая пара студентов войдет в результирующий набор данных дважды:

select С1.ФИО, С2.ФИО, У1.Оценка

from Студент С1, Студент С2, Дисциплина,

Успеваемость У1, Успеваемость У2

where Наименование = 'БД' and

Дисциплина.Id = У1.Id_Дисциплины and

Дисциплина.Id = У2.Id_Дисциплины and

У1.Оценка = У2.Оценка and

У1.Ном_Зач = С1.Ном_Зач and У2.Ном_Зач = С2.Ном_Зач and

С1.Ном_Зач > С2.Ном_Зач

go

Для удобства восприятия пользователем набора данных, возвращаемого запросом, он может быть отсортирован. Порядок сортировки задается директивой order by <столбец 1> [ { asc | desc } ] [ , <столбец 2> [ { asc | desc } ] [ , … ] ], здесь asc задает порядок сортировки по возрастанию (действует по умолчанию), desc — по убыванию. Пример использования сортировки показывает запрос:

select С1.ФИО, С2.ФИО, У1.Оценка

from Студент С1, Студент С2, Дисциплина,

Успеваемость У1, Успеваемость У2

where Наименование = 'БД' and

Дисциплина.Id = У1.Id_Дисциплины and

Дисциплина.Id = У2.Id_Дисциплины and

У1.Оценка = У2.Оценка and

У1.Ном_Зач = С1.Ном_Зач and У2.Ном_Зач = С2.Ном_Зач and

С1.Ном_Зач > С2.Ном_Зач

order by У1.Оценка desc, С1.ФИО, С2.ФИО

go