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

1. Какими средствами в СУБД Access реализуется обеспечение целостности данных?

2. Какое поле таблицы может быть объявлено первичным ключом? Может ли первичный ключ таблицы состоять из нескольких полей?

3. Перечислите основные объекты СУБД Access.

4. Перечислите основные типы полей таблиц СУБД Access.

5. Какой тип поля СУБД Access позволяет включать в него несколько значений?

Лабораторная работа № 9 конструирование запросов к базе данных

Цель работы

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. Постройте перекрестный запрос ЗапПоступлСамостПерекр, показывающий количество видов продукции, поступивших от каждого поставщика к каждому из материально ответственных лиц.