Порядок выполнения работы. 5. Работа с данными при помощи запроса - выборки

5. Работа с данными при помощи запроса - выборки.

Запустите Access. Откройте БД Teach. Щелкните по корешку Запрос, а затем по кнопке Создать и выберите способ создание запроса в режиме конструктора, без помощи Мастера.

1.1 Создание запроса на основе одной таблицы БД.

Пусть надо выяснить, какие дисциплины и в каких группах ведет преподаватель Никитин Е.В. Из списка таблиц в окне Добавление таблицы выберите DIS_GR и щелкните по кнопке Добавить. Закройте окно. В окне запроса на выборку:

l Перетащите из таблицы или выберите из раскрывающихся списков в бланке QBE запроса последовательно поля PR, DIS, GR.

l - В условие отбора для поля PR введите “Никитин Е.В.”.

l Закройте окно конструирования запроса, сохраните запрос под именем Nik.

l Выполните запрос, дважды щелкнув по его имени в списке запросов, убедитесь, что результирующая таблица содержит 3 столбца: Ф.И.О., Дисциплина, Группа и 2 строки.

l Перейдите в режим конструирования запроса, щелкните по индикатору «Вывод на экран» поля PR, чтобы сбросить его, и установите сортировку по возрастанию для поля DIS.

l Сохраните запрос под именем Nik2. Для этого выполните команду Файл4Сохранить как.

l Выполните запрос Nik2, оцените результат.

l Перейдите еще раз в режим конструирования запроса Nik и поменяйте значение в строке Условие отбора для поля PR на параметр [ФИО преподавателя?].

l Сохраните запрос под именем PR.

l Выполните запрос PR несколько раз, вводя разные фамилии и инициалы преподавателей.

1.2 Создание итогового запроса.

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

l Щелкните по корешку Запрос. Выберите ручной (без помощи Мастера) способ создания нового запроса, из списка таблиц выберите PREP. Щелкните по кнопке Добавить и закройте окно добавления.

l Оказавшись в окне конструирования запроса, поместите в бланк QBE запроса 2 поля: Categ и Nagr.

l Щелкните по кнопке å панели инструментов. В бланке QBE запроса должна появиться новая строка – «Групповая операция».

l Для поля Categ оставьте в этой строке установку по умолчанию (Группировка), а для поля Nagr выберите из списка функцию агрегирования MAX.

l Измените свойство «Подпись поля» для поля Nagr на «Максимальная нагрузка» с помощью команды Вид4Свойства.

l Сохраните запрос под именем QGR1 и выполните его.

l Оценив результаты запроса, создайте на его основе запрос, который позволяет получить разброс нагрузки (разницу между максимальной и минимальной) для преподавателей каждой категории кроме профессоров. Для этого внесите следующие изменения:

l Вместо MAX для поля Nagr выберите Выражение и введите в строку «поле» выражение

l MAX([Nagr])-MIN([Nagr]).

l Для поля Categ в строке «Групповая операция» должно быть «Группировка», а в строке Условие отбора введите <> “профессор”.

l Сохраните запрос под именем QGR2, выполните его и оцените результаты.

1.3 Создание многотабличного запроса.

Пусть требуется получить ответ на вопрос, какие преподаватели ведут дисциплину ОП и имеют нагрузку не более 700 часов. Для создания такого запроса потребуются таблицы PREP и DIS_GR.

l Щелкните по корешку Запрос, затем по кнопке Создать и выберите строку Конструктор.

l В открывшемся окне добавления таблицы выберите таблицы PREP и DIS_GR. Проверьте правильность установки связей между ними. Связь должна быть 1:n.

l Перетащите в бланк QBE запроса поля FIO и Nagr из таблицы PREP и поле DIS из таблицы DIS_GR.

l Щелкните по индикатору «Вывод на экран» поля DIS, чтобы сбросить флажок, поле не должно появиться в результате запроса.

l В строке «Условие отбора» для поля Nagr укажите <=700, а для поля DIS введите “ОП”.

l Закройте окно конструирования запроса, сохраните запрос под именем MT1.

l Выполните запрос. Результат должен содержать три строки.

1.4 Использование Мастера запросов.

1.4.1 Найдите с помощью Мастера всех преподавателей, которые ничего не преподают. Для этого:

l Добавьте в таблицу PREP 2-3 строки с новыми фамилиями.

l В окне создания запроса выберите из списка мастеров «Записи без подчиненных».

l Укажите, что таблица, в которой исследуются записи, - это PREP, а подчиненная ей таблица - DIS_GR.

l В качестве полей, выводимых на экран, укажите фамилию и категорию преподавателя.

l Попросите Мастера показать вам созданный запрос в режиме конструктора (Изменение структуры запроса), а затем выполните его. Результатом запроса должен быть список преподавателей с указанием их категорий, который вы внесли в таблицу PREP перед началом работы с Мастером.

1.4.2 Создайте с помощью Мастера перекрестный запрос на базе таблицы Pred, которая находится в БД Dis. Результатом такого запроса должна быть таблица, названия строк которой – это названия предметов, а названия столбцов – номера семестров, на пересечении строк и столбцов должно находиться количество часов, отводимых на данный предмет в данном семестре.

l Импортируйте таблицу Pred в БД Teach.

l Дополните ее данными таким образом, чтобы некоторые предметы читались в нескольких семестрах.

l В окне БД выберите корешок Запросы и щелкните по кнопке Создать.

l В списке типов запросов выберите Перекрестный запрос.

l В открывшемся диалоговом окне выберите таблицу Pred, перейдите в следующее окно.

l В качестве заголовков строк выберите поле с кодом или названием предмета, перейдите в следующее окно.

l Для использования в качестве заголовков столбцов выберите значения поля Семестр, перейдите в следующее окно.

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

l Завершите формирование запроса, задав ему имя CrossQ, посмотрите, как выглядит результат запроса.

6. Модификация данных при помощи запросов - действий.

2.1 Запрос на создание новой таблицы.

Мы уже использовали этот тип запроса в лабораторной работе 2 для создания таблицы, являющейся соединением таблиц Prep и Dis_Gr. Теперь используем такой запрос для получения из перекрестного запроса CrossQ таблицы Pred1 с полями: Предмет, Всего часов. Для этого:

l Находясь в окне БД, выберите корешок Запросы и щелкните по кнопке Создать.

l В режиме конструктора выберите запрос CrossQ в качестве основы нового запроса и добавьте его в окно запроса.

l Создайте запрос - выборку с полями, содержащими коды или названия предметов и общее число часов, отводимых на каждый предмет, которое содержится в поле итоговых значений запроса CrossQ, сохраните новый запрос под именем CreateQ, проверьте правильность его выполнения.

l Преобразуйте полученный запрос в запрос на создание таблицы (Запрос4Создание таблицы…), указав для создаваемой таблицы имя Pred1. Новая таблица создается в БД Teach.

l Выполните запрос и убедитесь, что в БД Teach появилась новая таблица Pred1.

l Переименуйте поля полученной таблицы в Предмет и Всего часов соответственно.

2.2 Запрос на обновление.

Такой запрос используется с целью внесения одинаковых изменений в несколько записей. Увеличим, например, на 10 процентов нагрузку всех ассистентов. Для этого:

l Находясь в окне БД Teach, создайте запрос – выборку, чтобы отобрать из таблицы Prep всех ассистентов, сохраните запрос под именем Update и убедитесь в правильности его выполнения.

l Преобразуйте созданный запрос в запрос на обновление, для этого, находясь в режиме конструирования запроса, из меню Запрос надо выбрать строку Обновление (Запрос4Обновление).

l В бланке QBE-запроса появится строка «Обновление», на пересечении этой строки со столбцом Nagr введите выражение [Nagr] * 1,1.

l Сохраните изменения, выполните запрос и убедитесь, что ожидаемые изменения в таблице Prep произошли.

2.3 Запрос на добавление

Данные или их часть могут добавляться в таблицу из другой таблицы этой или другой базы данных. Можно также добавить в таблицу одну строку, задав значения полей в виде констант непосредственно в бланке QBE - запроса. Воспользуемся этой возможностью для добавления в таблицу Pred1 строки: Основы экономики, 40. Для этого:

l Откройте окно конструирования нового запроса и выберите команду Запрос4Добавление.

l В появившемся диалоговом окне введите имя таблицы, в которую будут добавляться данные, это таблица Pred1.

l В бланке QBE - запроса появится строка «Добавление», укажите в ней последовательно имена полей, выбирая их из разворачивающихся списков.

l В строку «Поле» в столбец Предмет поместите значение “Основы экономики”, а в столбец Всего часов – значение 40.

l Сохраните запрос под именем InsertQ и выполните его.

l Убедитесь, что в таблице Pred1 появилась новая строка.

2.4 Запрос на удаление

Такой запрос предназначен для удаления из указанной таблицы всех строк или строк, соответствующих заданному условию. Удалим, например, из таблицы Pr_Dis все строки, касающиеся преподавателя Малышева. Для этого:

l Создайте новый запрос-выборку в режиме запроса, добавив в окно запроса таблицу Pr_Dis и перетащив поочередно все поля этой таблицы в бланк QBE – запроса, в строку «Условие отбора» поля FIO необходимо ввести "Малышев С.В.".

l Сохраните запрос под именем DelQ и выполните его, чтобы убедиться в том, что выбираются строки, удовлетворяющие условию.

l Преобразуйте запрос - выборку в запрос на удаление, выполнив команду Запрос4Удаление.

l Сохраните изменения и выполните запрос, убедитесь, что информация о Малышеве исчезла из таблицы.

Задание для самостоятельной работы.

1. Создайте на основе таблицы Dis_Gr запрос, позволяющий ответить на вопрос, кто кроме Малышева С.В. работает на ФАВТ и преподает дисциплину БДиБЗ. Ответом должна быть таблица, содержащая единственный столбец - Ф.И.О. и единственную строку - Наумов С.А.

2. Создайте запрос, результатом которого будет список преподавателей, у которых закончился срок контракта. Учтите, что контракт заключается на пять лет.

3. Измените запрос QGR2, созданный в 1.2, таким образом, чтобы выводилась информация только о тех категориях преподавателей, для которых разница между максимальной и минимальной нагрузкой составляет более 50 часов.

4. Создайте запрос, который выводит информацию о средней нагрузке преподавателей каждой из категорий.

5. Измените запрос МТ1, созданный в 1.3, таким образом, чтобы полное название дисциплины и значение нагрузки могли задаваться как значения параметров. Не забудьте добавить в окно запроса еще одну таблицу – PRED и связать ее с таблицей Dis_Gr.

6. В таблице Адрес1 БД Студенты_Занятия с помощью запроса на обновление поменяйте все номера телефонов с кодом города 095, начинающиеся на 5. Теперь номер должен начинаться с 3. При формировании выражения в строке обновления рекомендуется использовать функцию Right, возвращающую n правых символов заданной строки.

7. При помощи запроса на создание таблицы преобразуйте таблицу Адрес1 в таблицу Адрес2, идентичную таблице Адрес, то есть сделайте так, чтобы названия улиц, номера домов и квартир оказались в одном столбце.

Дата добавления: 2015-05-09