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

Цель работы

1. Научиться выполнять выбор данных из таблиц с помощью запросов различных типов (из нескольких таблиц с сортировкой, с условием отбора, с параметром, с созданием вычисляемого поля).

2. Приобрести навыки конструирования запросов с группировкой, перекрестных запросов.

Задание 1

Создание запросов на основе нескольких таблицс применением сортировки

С помощью КОНСТРУКТОРА создайте запрос ЗапПоступлениеСельхозПродукции, результирующая таблица которого имела бы структуру записи, подобную структуре записи таблицыПоступлениеСельхозПродукции, но объекты должны быть представлены своими наименованиями (взятыми из справочников). Добавьте поля:ЕдиницаИзмерения(изтаблицыСельхозПродукция)и СтоимостьПродукции,вычисляемое по формуле=[ЦенаТысРуб]*[Количество].В результирующую таблицу введите все записи таблицы ПоступлениеСельхрзпродукции(cм. рис. 9.2).Произведите сортировку по полямНаименПродукции– возрастание, ЦенаТысРуб –убывание.

Порядок выполнения задания

1. На вкладке Создание ленты меню в группе Запросынажмите кнопку .

2. В появившемся окне ДОБАВЛЕНИЕ ТАБЛИЦЫ добавьте таблицыПоставщики, СельхозПродукция, МатОтвЛица, ПоступлениеСельхозПродукции(двойным щелчком клавишей мыши по именам таблиц). Закройте окно ДОБАВЛЕНИЕ ТАБЛИЦЫ.

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

ДатаПост изтаблицыПоступлениеСельхозПродукции, НаименПоставщикаизтаблицы Поставщики, НаименПродукциииЕдиницаИзмеренияиз таблицы СельхозПродукция,ФамилияМОЛиз таблицыМатОтвЛица,Количествои ЦенаТысРубизтаблицыПоступлениеСельхозПродукции.

4. В строке Вывод на экран проверьте наличие флажков (V) для всех полей.

5. В строку Поле первой пустой колонки введите выражение:

СтоимостьПродукции:[ЦенаТысРуб]*[Количество]

6. Сохраните и выполните запрос.

7. Задайте порядок сортировки запроса.

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

б) В бланке запроса в строке Сортировка для поля, по которому следует рассортировать, нажмите кнопку РАСКРЫТИЯ СПИСКА и выберите порядок сортировки: По возрастанию илиПо убыванию. В нашем запросе для поля НаименПродукциивыбратьПо возрастанию, для поля ЦенаТысРуб–По убыванию. Окончательный вид бланка запроса изображен на рис.9.1. Схема выбора данных по запросу представлена на рис. 9.2.

8. Сохраните и выполните запрос.

Рис. 9.1.Запрос ЗапПоступлСельхозПрод в режиме КОНСТРУКТОРА

Рис. 9.2. Схема выбора данных по запросу ЗапПоступлСельхозПродукции

Задание 2

Создание запросов с условиями отбора

Условия отбора, позволяющие выбрать только определенные записи таблицы, задаются в строках Условие отбора, или и могут представлять собой выражения сравнения. В выражениях могут использоваться логические операторы NOT, AND, OR, а также конструкция BETWEEN.

Если выражения вводятся в одну строку нескольких столбцов Условие отбора, то они автоматически объединятся с помощью логического оператора AND. Выражения же, введенные в разные строки (Условия отбораи или), объединятся с помощью логического оператора OR.

Создайте запрос ЗапПостКлюква, структура результирующей таблицы которого идентична, ЗапПоступлениеСельхозПродукции,но в таблицу включите только данные о поступлении клюквы.

Порядок выполнения задания

1. СкопируйтеЗапПоступлСельхозПродс именем ЗапПоступлКлюква(последовательно нажав кнопкиКопировать и Вставить).

2. ОткройтеЗапПоступлКлюквав режиме КОНСТРУКТОРАи в строку Условие отбораполя НаименСельхозПродукциивведите значение“Клюква”. Макет ЗапПоступлКлюкваизображен на рис.9.3.

Рис. 9.3.ЗапросЗапПоступлКлюква в режиме КОНСТРУКТОРА

Задание 3

Создание запросов с параметрами

Создайте запросЗапПоступлПар, позволяющий просмотреть данные о поступлении заданной сельхозпродукции.

Порядок выполнения задания

1. Создайте запрос ЗапПоступлПарпо аналогии сЗапПоступлСельхозПрод, но в строку Условие отбора поля НаименПродукции вместо надписи Клюквавведите приглашение на ввод условия отбора в квадратных скобках, например, [Введите наименование сельхозпродукции]. Получился запрос с параметром. При выполнении запроса перед формированием таблицы будет выводиться заданное приглашение: «Введите наименование сельхозпродукции». И, вводя наименование требуемой продукции, можно получить данные о ее поступлении. Запрос ЗапПоступлПар в режиме конструктора изображен на рис. 9.4.

2. Выполните и сохраните запрос.

Рис. 9.4. Запрос ЗапЭкзаменыПар в режиме КОНСТРУКТОРА

Задание 4

Создание простого запроса с помощьюМАСТЕРА ЗАПРОСОВ

С помощью мастера запросов создайте запросЗапПоступлениеМас, содержащий сведения о поступлении продукции, результирующая таблица которого должна содержать сведения в следующем порядке:ДатаПост изтаблицыПоступлениеСельхозПродукции, НаименПродукции, ЕдиницаИзмеренияиз таблицы СельхозПродукция,ФамилияМОЛиз таблицыМатОтвЛица,НаименПоставщикаизтаблицы Поставщики, КоличествоиЦенаТысРубизтаблицыПоступлениеСельхозПродукции. Все объекты представляются своими наименованиями.

Порядок выполнения задания

1. На вкладке Создание ленты меню в группе Запросынажмите кнопку .

2. В окне НОВЫЙ ЗАПРОС выберите пункт Простой запрос.

3. Из нужных полей соответствующих таблиц скомпонуйте макет запроса, перемещая поля кнопкоймежду листами. Выберите поля: ДатаПост изтаблицыПоступлениеСельхозПродукции, НаименПродукции, ЕдиницаИзмеренияиз таблицы СельхозПродукция,ФамилияМОЛиз таблицыМатОтвЛица,НаименПоставщикаизтаблицы Поставщики, КоличествоиЦенаТысРубизтаблицыПоступлениеСельхозПродукции.НажатьДалее.

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

5. В следующем окне задайте имя запроса ЗапПоступлениеМас и выберите одно из предложенных действий:Открыть результат выполнения запроса;Изменить структуру запроса.НажмитеГотово.

Задание 5

Формирование запросов с группировкой

Создайте запросЗапПоступлениеГрупп, показывающий количество видов сельхозпродукции, поступившей от каждого поставщика.

Порядок выполнения задания

1. На вкладке Создание ленты меню в группе Запросынажмите кнопку .

2. В появившемся окне ДОБАВЛЕНИЕ ТАБЛИЦЫ нажмите вкладку Запросыидобавьте запросЗапПоступлСельхозПрод. Закройте окно ДОБАВЛЕНИЕ ТАБЛИЦЫ.

3. В строку Поле макета переместите поля НаименПоставщика и НаименПродукциииз ЗапПоступлСельхозПрод.

4. В строке Вывод на экран проверьте наличие флажков (V) для полей.

5. Нажмите кнопку ,в бланке запроса появится новая строка Групповая операция, в которой для всех полей указано Группировка.

6. В поле НаименПродукциивместо надписи Группировказадайте нужную функцию (Count), выбрав ее из списка функций, появившихся по щелчку клавишей мыши в правой части поля.

7. Сохраните и выполните запрос.

Задание 6

Создание перекрестного запроса

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

Порядок выполнения задания

1. На вкладке Создание ленты меню в группе Запросы нажмите кнопку .

2. В окне НОВЫЙ ЗАПРОС выберите пункт Перекрестный запрос.

В появившемся окне выполните следующие действия.

а) ВыберитеПоказать запросы, в качестве источника укажитеЗапПоступлСельхозПрод, нажмитеДалее.

б) Для наименования строк выберите поле ФамилиМОЛ,переместитекнопкой , нажмитеДалее.

в) Для наименования столбцов выберитеНаименПродукции, нажмитеДалее.

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

3. Выполните запрос и сохраните с именем ЗапПоступлениеПерекрестный.

Результирующая таблица перекрестного запроса ЗапПоступлениеПерекрестныйизображена на рис. 9.5.

Рис. 9.5.Результирующая таблица перекрестного запроса ЗапПоступлениеПерекрестный

Задания для самостоятельного выполнения

Кроме рассмотренных запросов создайте следующие запросы в базе данных УчетСельхозПродукции.

1. Создайте запрос ЗапПоступСамостПар для вывода данных о поступлении сельхозпродукции от заданного поставщика (по форме ЗапПоступлСельхозПрод).

2. Сконструируйте запрос ЗапПоступлСамостГрупп для вывода суммарной стоимости сельхозпродукции, поступившей от каждого поставщика.

3. Постройте перекрестный запрос ЗапПоступлСамостПерекр, показывающийколичество видов продукции, поступивших от каждого поставщика к каждому из материально ответственных лиц.

Контрольные вопросы

1. Перечислите виды запросов на выбор данных.

2. Перечислите поля, входящие в бланк запроса на выбор данных.

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

4. Как выбрать данные из нескольких таблиц в запросе?Как создать запрос с параметром?

5. Какая информация нужна для создания запроса с группировкой и перекрестного запроса. Является ли результатом выполнения запроса таблица БД?

ЛАБОРАТОРНАЯ РАБОТА № 10