Запросы к базе данных Университет

База данных "Университет"

Минимальный список характеристик:
•    Номер, ФИО, адрес и должность преподавателя, ученая степень;
•    код, название, количество часов, тип контроля и раздел предмета (дисциплины);
•    код, название, номер заведующего кафедрой;
•    номер аудитории, где преподаватель читает свой предмет, дата, время, группа.
Один преподаватель может вести несколько дисциплин и одна дисциплина может вестись несколькими преподавателями.
Примечание: Циклы дисциплин: гуманитарный, общеинженерный, математический, компьютерный и т.д.
Выборки:
•    Выбрать преподавателя, который был "без работы" весной 2001г.
•    Определить возможные "накладки" аудиторий в расписании.
•    Вывести расписание занятий группы 'АП-17а' на март 2001г.
•    Определить для каждой группы долю дисциплин каждого цикла в процентах.
Распределим эти минимальные характеристики по разным таблицам. Основные сущности – это группы, преподаватели, предметы и кафедры. Таблицы «Группы» и «Предметы» связывает таблица «Предметы группы», к которой относятся также поля «Количество часов» и «Тип контроля» (у разных групп разное количество часов одного предмета и разный тип контроля). А группы, предметы и преподавателей связывает таблица «Расписание», где еще есть поля дата, время и аудитория. Вот такая получилась схема:

По-моему, достаточно все логично. Связи «один-ко-многим».
 Переходим к запросам.

1. Нужно выбрать преподавателя, который не работал весной 2001 года.

Сделаем двумя способами – один запрос напишем на sql без конструктора, а второй – с помощью двух запросов в конструкторе.

Итак, первый способ:

SELECT Преподаватели.ФИО
FROM Преподаватели
WHERE (((Преподаватели.Номер) Not In (SELECT Преподаватели.Номер
FROM Преподаватели LEFT JOIN Расписание ON Преподаватели.Номер = Расписание.Преподаватель
WHERE (((Расписание.Дата) Between #3/1/2001# And #5/31/2001#))
GROUP BY Преподаватели.Номер)));

Тут выбираются преподаватели, которых нет (Not In) в списке преподов, работавших весной 2001 года (то есть у них нет записей в таблице «Расписание» за указанный период).

Второй способ. Делаем запрос с группировкой («Работают») – выбираем преподавателей, работавших в нужный период.

SELECT Преподаватели.Номер, Преподаватели.ФИО
FROM Преподаватели LEFT JOIN Расписание ON Преподаватели.Номер = Расписание.Преподаватель
WHERE (((Расписание.Дата) Between #3/1/2001# And #5/31/2001#))
GROUP BY Преподаватели.Номер, Преподаватели.ФИО;

Во втором запросе используем соединение всех записей из таблицы «Преподаватели» и записей с совпадающими полями из запроса «Работают». Определяем пустые значения во втором поле и отбираем записи только с пустыми значениями.

SELECT Преподаватели.ФИО
FROM Преподаватели LEFT JOIN Работают ON Преподаватели.Номер = Работают.Номер
WHERE (((IsNull([Работают]![Номер]))=Yes));

Получилось то же самое, что и в первом способе, но работы больше.

2. Определить возможные "накладки" аудиторий в расписании.

Делаем запрос с группировкой («Накладки») из одной таблицы – «Расписание». Группируем по дате, времени и аудитории. Подсчитываем количество (Count) по номеру записи. Отбираем только совпадающие записи, то есть количество должно быть больше 1.

SELECT Расписание.Дата, Расписание.Время, Расписание.Аудитория, Count(Расписание.[Код записи]) AS [Count-Код записи]
FROM Расписание
GROUP BY Расписание.Дата, Расписание.Время, Расписание.Аудитория
HAVING (((Count(Расписание.[Код записи]))>1));

И создаем еще один запрос из таблицы «Расписание» и запроса «Накладки». В качестве условия отбора ставим значения даты, времени и номер аудитории. Для информативности добавляем еще группу, предмет и преподавателя.

SELECT Расписание.Дата, Расписание.Время, Расписание.Аудитория, Расписание.Группа, Расписание.Преподаватель, Расписание.Предмет
FROM Расписание, Накладки
WHERE (((Расписание.Дата)=[Накладки]![Дата]) AND ((Расписание.Время)=[Накладки]![Время]) AND ((Расписание.Аудитория)=[Накладки]![Аудитория]));

Вот, как пример:

3. Вывести расписание занятий группы 'АП-17а' на март 2001г.

Может, я что-то не так понял, но по сравнению с остальными заданиями этот запрос слишком простой. Делаем запрос из таблицы «расписание»:

SELECT Расписание.Дата, Расписание.Время, Расписание.Аудитория, Расписание.Предмет, Расписание.Преподаватель
FROM Группы INNER JOIN Расписание ON Группы.[Код группы] = Расписание.Группа
WHERE (((Расписание.Дата) Between #3/1/2001# And #3/31/2001#) AND ((Группы.Название)="АП17а"));

4. Определить для каждой группы долю дисциплин каждого цикла в процентах

Сначала делаем вспомогательный запрос, в котором определяем сколько всего предметов у каждой группы:

SELECT [Предметы группы].Группа, Count([Предметы группы].Предмет) AS [Count-Предмет]
FROM Предметы INNER JOIN [Предметы группы] ON Предметы.[Код предмета] = [Предметы группы].Предмет
GROUP BY [Предметы группы].Группа;

Затем в группировочном запросе высчитаем процентов предметов каждого цикла (математическая операция):

SELECT [Предметы группы].Группа, Предметы.[Цикл предмета], Count([Предметы группы].[Предмет])*100/[Count-Предмет] AS [Процент предметов]
FROM [Всего предметов] INNER JOIN (Группы INNER JOIN (Предметы INNER JOIN [Предметы группы] ON Предметы.[Код предмета] = [Предметы группы].Предмет) ON Группы.[Код группы] = [Предметы группы].Группа) ON [Всего предметов].Группа = Группы.[Код группы]
GROUP BY [Предметы группы].Группа, Предметы.[Цикл предмета], [Всего предметов].[Count-Предмет];

Результат запроса: