Практическая работа № 3. БД «Торговая фирма»: Построение запросов на выборку

Цель работы: Изучить способы построения запросов на выборку.

Теоретические сведения:

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

2. При создании запроса надо выполнить в общем случае четыре операции:

- указать, какие поля и из каких таблиц надо включить в запрос;

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

- описать групповые операции над записями исходных таблиц (выполнить объединение набора записей в группу по какому-либо признаку);

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

3. После выполнения запроса на выборку создается таблица, содержащая отобранные данные. Но эта таблица не сохраняется в БД, а существует только во время выполнения запроса.

4. При изменении данных в запросе будут внесены соответствующие изменения и в исходную таблицу.

5. Запрос может быть создан не только по таблицам, но и по другим запросам.

6. Создание запроса можно осуществить в режиме конструктора или с использованием Мастера запросов.

Пошаговое выполнение работы:

1. Загрузитe Microsoft Access.

2. На вкладке Файл выберите команду открытия базы данных – Открыть базу данных, созданную в практической работе № 2.

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

4. На экране появится бланк запроса. В верхней части бланка должны размещаться списки полей таблиц или запросов, на основе которых создается новый запрос. В окне Добавление таблиц поочередно выбираем из списка имена нужных таблиц, нажимая кнопку , и закрываем окно Добавление таблиц. Исходя из нашей задачи, выберите в качестве исходной таблицу Продукты.

5. В нижней части бланка выполняется вся работа по созданию запроса. Каждый столбец бланка соответствует одному полю запроса. Чаще всего поле выбирается из исходной таблицы. Имя поля задается из предложенного списка в ячейке строки Поле или перетаскивается из верхней части бланка. Поочередно укажите из таблицы Продукты все поля, кроме поля Код продукта.

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

Третья строка - сортировка, указывает способ упорядочивания записей по данному полю. Укажите сортировку по алфавиту поля Наименование.

Четвертая строка определяет, будут ли значения данного поля выведены на экран при выполнении запроса. Все поля следует вывести.

Условие отбора пока не задаем.

	Верхняя часть бланка.
Нижняя часть бланка.

Рис. 1.

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

7. Создадим простой запрос по одной таблице с помощью Мастера. Предположим, что требуется просмотреть список всех клиентов фирмы без указания их кодов. Для этого следует в меню Создание выбрать – Мастер запросов. В первом окне Мастера (рис. 2) из списка поля «Таблицы и запросы» надо выбрать таблицу Клиенты, по которой будет строиться запрос. В списке Доступные поля отобразится перечень полей выбранной таблицы. Необходимые для построения запроса поля поочередно следует переместить в список Выбранные поля с помощью кнопки .В нашем случае это поля НаименованиеиАдрес.Затем нажать кнопку . На заключительном шаге Мастер позволяет задать имя запроса – Информация о клиентах и указать на дальнейшие действия: просмотреть результат запроса или заняться изменением его структуры. Выберем первый вариант и нажмем кнопку . После просмотра результатов работы запроса, закройте его.

Рис. 2.

8. Создадим запрос с вычислениями. Допустим, требуется выдать список имеющихся на складе продуктов и их стоимость. Откроем запрос Информация о продуктах в режиме конструктора. Нас интересует наименование продукта и его стоимость. Стоимость определяется как произведение цены на количество продукта. Поля Стоимость нет в исходной таблице, по которой был построен запрос, его следует сформировать как вычисляемое. Для этого надо установить курсор в первую строку пустого столбца нижней части бланка запроса и ввести выражение:

Стоимость: [цена]*[кол-во на складе]

9. Можно ввести выражение с помощью Построителя выражений,который вызывается кнопкой Построить Панели инструментов. В верхней части окна построителя (рис. 3) формируется выражение, в нижней части окна выбираются поля, участвующие в создании выражения. Список слева в нижней части окна представляет папки всех имеющихся в БД таблиц, запросов, форм и отчетов. Первая папка представляет текущий объект (у нас это запрос Информация о продуктах). После выделениялюбого из объектов в средней панели отображаются его поля и другие элементы управления в средней панели. При выборе одного из этих элементов в правой панели отображаются его свойства. В нашем примере можно указать на исходную таблицу Продукты и двойным щелчком поочередно выбрать нужные для построения выражения поля. После подготовки выражения нажмите . В поле Вывод на экран текущего столбца бланка запроса надо установить флажок, а в столбцах Цена и Кол-во на складе можно снять флажки. Сохраните созданный запрос под именем Стоимость(Файл /Сохранить объект как), выполните созданный запрос и закройте его.

Рис. 3.

10. Создадим запрос с условием отбора. По таблице Продукты отберем список продуктов, количество которых на складе не менее 2000 единиц. Для этого надо открыть запрос Информация о продуктах в режиме Конструктора, в поле Условие отбора столбца Кол-во на складе задать условие >= 2000. Можно задать несколько условий отбора по нескольким полям. Если все условия отбора будут заданы в одной строке, то Microsoft Access воспримет это как логическое "И", если в разных – то как логическое "ИЛИ". В поле Вывод на экран текущего столбца бланка запроса надо установить флажок. Сохранив под именем Кол-во не меньше 2000,выполните созданный запрос и закройте его.

11. Создайте запрос с условием отбора для определения списка продуктов, цена которых не меньше 20 р. и количество 1000 единиц, и сохраните его под именем Дороже 20 рублей. Выполните созданный запрос и закройте его.

12. Требуется сформировать запрос для определения списка продуктов, цена которых ниже средней и сохранить его под именем Дешевые продукты.

Если при формировании запроса необходимо указать значения больше/меньше среднего по полю, то в качестве условия отбора в соответствующем столбце записывается выражение:

оператор отношения(SELECT AVG ([имя поля]) FROM [имя таблицы])

13. Создадим запрос с параметром по условию отбора. Такой запрос позволяет задавать конкретное условие отбора непосредственно при выполнении этого запроса. Условие в общем виде выглядит так:

оператор отношения [текст сообщения].

14. По таблице Продукты надо выдать список товаров, количество которых на складе не менее заданного пользователем в запросе. Сначала требуется создать простой запрос, включающий все поля таблицы Продукты, затем открыть его в режиме конструктора и в качестве условия отбора столбца Кол-во на складезаписать выражение: >=[на складе не менее].Сохраните запрос под именемПродукты по количествуи выполните его. При выполнении этого запроса программа будет запрашивать интересующее Вас количество продуктов.

15. Задание параметра в диапазоне. По таблице Заказы определите объем продаж за задаваемый период времени. Следует построить простой запрос по таблице Заказы, содержащий поля Номер заказа,Код продукта,Кол-во,Дата поставки. Затем в режиме конструктора надо записать условие отбора в столбце Дата поставки следующего вида: