Материал: Лабораторная работа №19

Лабораторная работа №19. Конструирование запросов

Цель работы:

Освоить технологии конструирования запросов к базе данных. Освоить приёмы использования запросов.

Теоретическая часть

Запрос – это средство Access для выборки данных из базы данных в форме таблицы, выполняемой по заданному условию, а также для выполнения определённых действий над табличными данными.

Условие может определять:

• порядок сортировки выводимых данных;

• фильтрацию данных;

• вычисляемые поля;

• вывод данных из нескольких связанных таблиц и т.п.

Запросы по существу являются псевдотаблицами и их можно использовать также как и таблицы. Применение запросов позволяет избежать дублирования данных в таблицах и обеспечивает максимальную гибкость при поиске и отображении данных БД. С помощью запроса создаётся временная таблица – динамический набор данных. С помощью запроса можно осуществить выборку данных сразу из нескольких таблиц.

Запрос представляет собой обращение к данным для получения информации и выполнения действий с данными. Запрос можно использовать для просмотра, изменения и анализа данных, для выполнения расчётов, объединения данных из разных таблиц или добавления, изменения или удаления данных в таблице. Запросы можно также использовать для включения данных в форму или отчёт. В хорошо структурированной базе данных сведения, которые требуется представить с использованием формы или отчёта, зачастую хранятся в разных таблицах. С помощью запроса можно собрать необходимые данные перед проектированием формы или отчёта.

Все запросы можно разделить на 2 группы:

• запросы-выборки;

• запросы-действия.

Запросы-выборки извлекают данные из таблиц в соответствии с заданными условиями. Это наиболее часто используемый тип запроса. Запросы этого типа возвращают данные из одной или нескольких таблиц и отображают их в виде таблицы, записи в которой можно обновлять (с некоторыми ограничениями). Запросы-выборки можно также использовать для группировки записей и вычисления сумм, средних значений, подсчёта записей и нахождения других типов итоговых значений.

Основные виды запросов-выборок:

• запрос-выборка по одной таблице с заданными ключами сортировки (таблицы, рассортированы только по одному ключу);

• запрос с критерием поиска;

• запрос перекрёстный;

• запрос с параметром;

• запрос с вычисляемым полем;

• запрос с итогами;

• запрос к связанным таблицам.

Запросы-действия предназначены для выполнения требуемых действий над данными таблиц. Они позволяют добавлять, изменять или удалять данные. В Access существует 4 вида запросов-действий:

• запрос на удаление;

• запрос на замену (обновление);

• запрос на создание новой таблицы;

• запрос на добавление новых записей в таблицу.

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

Первая строка таблицы называется Поле. В ней содержатся имена полей. Одно и то же поле может записываться в строку таблицы описания запроса несколько раз. Поле может быть вычисляемым. Тогда в соответствующей ячейке содержится выражение для вычисления значения этого поля.

Вторая основная строка таблицы называется Сортировка. Она определяет способ сортировки по соответствующему полю (по возрастанию, по убыванию или без сортировки).

Третья строка таблицы Вывод на экран управляет выводом на экран значения соответствующего поля.

Четвёртая строка таблицы называется Условие отбора. Эта строка содержит критерии, по которым производится отбор записей в динамический набор данных.

При записи в запросе условия можно объединять критерии при помощи операций И (AND) или ИЛИ (OR).

Допускается создание вычисляемых полей в запросе с помощью ввода выражения в строку с именами полей.

Имя вычисляемого поля выводится перед выражением и отделяется от него двоеточием. В режиме таблицы данное имя становится заголовком столбца. Пользователь имеет возможность переименовать вычисляемое поле.

Например, для создания поля Сумма следует ввести в ячейку в строке Поле выражение: Сумма: [Цена]*[Количество]. При выполнении запроса в поле Сумма будет занесено новое значение, найденное путём перемножения содержимого полей Цена и Количество.

Вычисляемые поля позволяют:

• рассчитывать числовые значения и даты;

• комбинировать значения в текстовых полях;

• создавать подчинённые запросы;

• рассчитывать значения итоговых полей с помощью групповых функций.

Для вычисляемых полей допускается сортировка, задание условий отбора и расчёт итоговых значений, как и для любых других полей. Ввод данных в вычисляемые поля или их изменение в режимах таблицы или формы невозможен. При изменении значений полей, используемых в выражении, значение вычисляемого поля обновляется автоматически.

В Microsoft Access определена специальная функция Count, обеспечивающая подсчёт числа записей в запросе.

Запросы можно создавать с помощью Мастера запросов (Query Wizard) или Конструктора запросов (Query Design).

Общая постановка задачи

Изучить возможности программы MS Access для выполнения заданий, представленных ниже.

Список индивидуальных данных

Номер варианта и задания, если это требуется в лабораторной работе, определяется преподавателем.

Пример выполнения работы

Задание 1. Создать простой запрос для выборки сведений из базы данных, включающий следующие поля:

• НаименованиеФакультета;

• НомерГруппы;

• НаименованиеСпециальности;

• НомерЗачетнойКнижки;

• Фамилия.

1. Откройте базу данных Университет.

2. На вкладке Создание (Create) в группе Запросы (Queries) выберите Конструктор запросов (Query Design).

3. В появившемся диалоговом окне Добавление таблицы (Show Table) на вкладке Таблицы (Tables) добавьте таблицы Студенты, Группа, Специальность, Факультет. Для добавления таблицы можно выполнить двойной щелчок на соответствующей таблице. Затем закройте окно.

4. При необходимости отрегулируйте размеры и расположение окон с таблицами на схеме данных.

5. Перетащите необходимые названия полей (см. выше) из окон таблиц в бланк запроса, расположенный под схемой данных, соблюдая заданный порядок.

Примечание. Вместо перетаскивания названия поля можно использовать двойной щелчок левой кнопкой мыши по соответствующей строке в окне таблицы на схеме данных. То же самое можно получить, если использовать кнопку, разворачивающую список полей таблиц. Эта кнопка появляется при щелчке по ячейке в первой строке бланка.

6. Проверьте результат, запустив запрос кнопкой Выполнить (Run) на вкладке Конструктор (Design). Для просмотра полученной выборки можно также перейти в режим таблицы.

7. Вернитесь в режим конструктора запросов в случае, если выборка содержит ошибки, и откорректируйте запрос.

8. Закройте запрос. Появится диалоговое окно, в котором следует подтвердить необходимость его сохранения. Присвойте запросу имя Выборка_1. Сохранить запрос также можно, щёлкнув правой кнопкой мыши на вкладке с названием запроса и выбрав пункт меню Сохранить или нажав кнопку Сохранить на ленте.

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

Для выполнения этого задания необходимо на бланке запроса добавить поле Коммерческий и ввести по нему соответствующее условие. Присвойте запросу имя Выборка_2.

Задание 3. Создать запрос для подсчёта количества коммерческих студентов в каждой группе.

1. Создайте в режиме конструктора новый запрос с использованием таблиц: Факультет, Группа, Студенты.

2. Введите в 1-ый столбец бланка запроса поле НаименованиеФакультета, во 2-ой столбец – поле НомерГруппы, в 3-ий столбец – поле Коммерческий.

3. Установите в качестве условия отбора для 3-го столбца значение Да.

4. Установите для 1-го и 2-го столбцов бланка сортировку по возрастанию.

5. Отключите вывод на экран данных 3-го столбца.

6. Введите в 4-ый столбец поле Коммерческий и замените название столбца на КоличествоКоммерческих. Для этого новое и старое названия столбца следует разделить символом «двоеточие», т.е. ячейка с названием поля должна содержать:

КоличествоКоммерческих: Коммерческий.

7. Щёлкнув по кнопке Итоги (Totals) на вкладке Конструктор (Design), добавьте в бланк строку Групповая операция и выберите из списка в этой строке для 4-го столбца операцию Count.

8. Просмотрите выборку с подсчётом итогов, запустив запрос.

9. Вернитесь в режим конструктора запросов в случае, если выборка содержит ошибки, и откорректируйте запрос.

10. Сохраните запрос, присвоив ему имя

Подсчёт_коммерческих_по_группам.

Задание 4. Создать запрос-выборку для подсчёта количества коммерческих студентов по каждому факультету. Сохраните запрос, присвоив ему имя Подсчёт_коммерческих_по_факультетам.

Задание 5. Создать запрос, позволяющий увидеть выборку, отражающую количество студентов по каждому факультету и каждой группе. Заголовки столбцов должны соответствовать названиям факультетов, заголовки строк – номерам групп. Такой вид выборки может быть реализован перекрёстным запросом. Для применения подобного запроса желательно иметь в базе данных сведения по 5-6 группам, отнесённым к 3 факультетам (если нужно, добавьте записи в таблицы).

1. Создайте в режиме конструктора новый запрос с использованием таблиц: Факультет, Группа, Студенты.

2. Введите в 1-ый столбец бланка запроса поле НаименованиеФакультета, во 2-ой столбец – поле НомерГруппы, в 3-ий столбец – поле Коммерческий.

3. На вкладке Конструктор (Design) в группе Тип запроса (Query Type) выберите тип запроса – Перекрёстный (Crosstab Query Wizard).

4. Выберите значения в строке бланка Перекрёстная таблица, развернув список в ячейках: для 1-го столбца – Заголовки строк, для 2-го столбца – Заголовки столбцов, для 3-го столбца – Значение.

5. Выберите функцию Count для групповой операции в 3-м столбце.

6. Просмотрите перекрёстную выборку, щёлкнув по кнопке Выполнить (Run).

7. В режиме таблицы уменьшите ширину столбцов таблицы-выборки. Для этого выделите столбцы с данными по группам и на вкладке Главная (Home) в группе Записи (Records) щёлкните на кнопку Дополнительно, затем выберите Ширина поля, в открывшемся диалоговом окне выберите По ширине данных.

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

Количество_студентов_по_факультетам_и_группам.

Задание 6. Создать запрос, позволяющий увидеть перекрёстную выборку, отражающую количество коммерческих студентов по каждому факультету и каждой группе. Сохраните запрос, присвоив ему имя Количество_коммерческих_студентов_по_факультетам_и_группам.

Задание 7. Создать запрос для вывода списков групп, причём номер группы должен запрашиваться в процессе выполнения запроса. Такой запрос называется запросом с параметром. Параметром является номер группы. Значение параметра вводится в диалоговом окне. Для создания запроса необходимо ввести в ячейку с условием текст условия отбора в квадратных скобках.

1. Создайте в режиме конструктора новый запрос с использованием одной таблицы Студенты.

2. Введите в 1-ую строку бланка запроса все поля таблицы.

3. Введите в ячейку строки Условие отбора для поля НомерГруппы текст: [Введите номер группы:].

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

5. Сохраните запрос, присвоив ему имя Запрос_с_параметром.

Задание 8. Создать запрос для получения справки по стоимости обучения коммерческих студентов с учётом НДС. Пусть стоимость обучения с учётом НДС коммерческого студента равна стоимости обучения по выбранной им специальности, умноженной на (1+0,2), где 0,2 – величина НДС. Такой запрос называется запросом с вычисляемым полем.

1. Создайте в режиме конструктора новый запрос с использованием таблиц: Специальность, Группа, Студенты.

2. Введите в бланк запроса поля:

• НомерГруппы;

• НомерСпециальности;

• Фамилия;

• НомерЗачётнойКнижки;

• Коммерческий;

• СтоимостьОбучения.

3. Введите в поле Коммерческий в строку Условие отбора – Да и отключите вывод содержимого этого поля на экран.

4. Выполните запрос и убедитесь в правильности его исполнения. Вернитесь в режим конструктора.

5. Щёлкните мышью в строке Поле следующего свободного столбца бланка и на вкладке Конструктор (Design) в группе Настройка запроса щёлкните по кнопке Построитель. Будет вызвано окно Построителя выражений (Expression Builder).

6. Используя поле таблицы Специальность, введите в окно построителя выражение: НДС: [СтоимостьОбучения]*0,2, где НДС: – заголовок столбца с вычисляемым полем, [СтоимостьОбучения] – поле таблицы Специальность, 0,2 – величина НДС. Для записи выражения следует использовать инструменты построителя выражений. После ввода выражения щёлкните по кнопке OК.

7. Щёлкните мышью в ячейке поля следующего свободного столбца и перейдите в окно Построителя выражений. Введите в этом окне формулу: Итого: [СтоимостьОбучения]+[НДС].

Примечание. Для редактирования выражения в вычисляемом поле запроса можно использовать окно Область ввода (Zoom), которое вызывается комбинацией клавиш Shift+F2, если ячейка с полем активизирована.

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

9. Запустите запрос.

10. Отладив запрос, сохраните его под именем

Запрос_с_вычисляемым_полем.

Задание 9. Создать запрос на обновление базы данных. Необходимо создать запрос, при выполнении которого произойдет увеличение стоимости обучения по всем специальностям в 1,5 раза.

1. Создайте в режиме конструктора новый запрос. Выберите в качестве исходной таблицы одну таблицу – Специальность.

2. На вкладке Конструктор (Design) в группе Тип запроса (Query Type) выберите тип запроса – Обновление (Update To), или, щёлкнув правой кнопкой мыши по свободному месту схемы данных, выберите из контекстного меню пункт Тип запросаОбновление.

3. Выберите поле СтоимостьОбучения и введите в него с помощью Построителя выражений в строку Обновление выражение: [СтоимостьОбучения]*1,5.

4. Выполните запрос, подтвердив обновление записей.

5. Сохраните запрос под именем Запрос_на_обновление.

6. Закройте запрос и посмотрите изменённую таблицу.

Задание 10. Создать запрос на обновление базы данных. Необходимо создать запрос, при выполнении которого произойдет уменьшение стоимости обучения по двум специальностям на 10%.

Задание 11. Создать запрос на создание новой таблицы – Экзаменационная_ведомость. Необходимо сконструировать запрос, при выполнении которого можно будет создавать заполненные данными таблицы со следующими столбцами:

• НомерГруппы;

• КодДисциплины;

• Фамилия;

• НомерЗачётнойКнижки.