3. Запросы на выборку к таблицам бд

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

• простые запросы на выборку;

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

• запросы с групповыми операциями (запросы с итогами);

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

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

Запрос с групповой операцией отличается от простого запроса на выборку тем, что позволяет группировать данные по заданному полю и вычислять групповые итоги (осуществлять групповые операции) по заданным поля в группе. Возможно применение условий отбора. В MS Access предусмотрены следующие групповые операции:

• Sum – сумма значений группы;

• Avg – среднее значение для группы;

• Max, Min – максимальное или минимальное значение в группе;

• Count – количество непустых значений в группе;

• StDev – среднеквадратичное отклонение в группе;

• Var – дисперсия значений поля в группе;

• First, Last – значение поля из первой и последней записи в группе.

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

По способу создание запросы можно разделить на QBE-запросы (Query by Example – запросы по образцу) и SQL-запросы. Первые строятся с помощью конструктора запросов, вторые с помощью операторов и функций языка SQL (Structured Query Language – язык структурированных запросов). Дополнительным средством создания запросов в MS Access является мастер запросов. Запросы можно создавать к таблицам, к другим запросам на выборку, одновременно к таблицам и запросам.

Результатом выполнения запроса на выборку является новая виртуальная (временная) таблица, не сохраняемая в базе данных. В запросе хранится структура запроса: таблицы, список полей, условия отбора записей и т.д., то есть фактически инструкция по поиску и отбору записей.

Последовательность действий при создании простого запроса на выборку:

• определить, в какой таблице (или таблицах) содержатся искомые данные;

• определить, по каким полям, каких таблиц будет происходить отбор данных, сформулировать критерии отбора;

• запустить конструктор запросов, добавить выбранные таблицы;

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

• указать в таблице конструктора запросов поля, содержащие искомые данных;

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

• сохранить запрос под выбранным именем и запустить его.

Для создания запроса с групповыми операциями или перекрестного запроса необходимо дополнительно:

• определить, по каким полям будет осуществляться группировка данных;

• определить вид групповой операции и поле, к которому она будет применена.

Операторы, используемые для создания условий отбора записей в запросах, и примеры условий отбора представлены в таблице 2.

Таблица 2 – Условия отбора записей

Оператор	Пример
> < >= <= <> And Or	“Пылесос” (знак = можно не указывать) <>”Иванов” “директор” Or “бухгалтер” Or “менеджер” Or “сторож” 450 >= 1200 <> 420 >100 And <200 <=#01.12.2010# >#01.07.2010# >=#01.02.2011# And <#01.03.2011# #12.10.2010# Or #12.11.2010# <>ложь Is Null – незаполненные ячейки
Between проверка интервала для числового, денежного значения или даты	Between 375 And 750 Between #01.02.2011# And #01.03.2011#
In проверка на равенство любому значению из списка	In (258;32;16) In (“Иванов”;”Петров”;”Сидоров”;”Степанов”) In (#12.06.2010#;#12.07.2010#;#12.08.2010#;#12.09.2010#)
Like разрешает использовать образцы и символы шаблона: * – любое количество символов; ? – один любой символ; # – одна любая цифра	Like “A*” Like “A*ов” Like “*телевизор*” Like “*ов” Like “??????ов” Like "[ИПС]*" (текстовое значение начинается с любого из указанных символов) Like "[!ИПС]*" (текстовое значение не начинается с любого из указанных символов) Like "[И-С]*" (текстовое значение начинается с букв от И до С)

Практическое задание 3. Создание запросов на выборку к таблицам базы данных

Запустите MS Access 2007 и откройте базу данных по учёту торговли, созданную на предыдущем занятии. Создайте запросы к таблицам базы данных, позволяющие получить заданную информацию. Таблицы БД, включаемые в запрос, и критерии отбора записей определяйте самостоятельно.

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

1. Получите список товаров, которых нет в наличии.

2. Определите, какие товары имеют стоимость больше Y рублей (стоимость Y задайте самостоятельно).

3. Используя сортировку, определите, какой товар имеет наибольшую стоимость.

4. Получите список поставок в хронологическом порядке с указанием кода товара и номера склада.

5. Получите список поставщиков одного выбранного Вами региона с указанием адреса и телефона.

6. Определите номера телефонов поставщиков Х и Y (наименования поставщиков задайте самостоятельно)

7. Определите, на каких складах находятся товары, поставленные Y числа (дату поставки Y задайте самостоятельно).

8. Получите список поставок, выполненных в январе, марте и мае текущего года.

9. Выясните, делал ли поставки в первом квартале текущего года поставщик №… (номер поставщика задайте самостоятельно).

10. Выясните, поступал ли товар X на склад №… в феврале текущего года (код товара X и номер склада задайте самостоятельно).

Пример: Получить список товаров, которых нет в наличии.

Запрос однотабличный, все необходимые поля находятся в таблице Товар.

Выполняем команду вкладка Создание → Конструктор запросов. В появившемся окне Добавление таблиц выбираем таблицу Товар и добавляем её в запрос (рисунок 10).

В таблице конструктора (она находится в нижней части) указываем поля Код_товара, Наименование, Цена (в них находятся необходимые данные) и поле Наличие, которое служит для задания условия отбора записей. Условие отбора – нет (это логическое значение, без кавычек). Указать необходимые поля можно двойным щелчком мыши по выбранному полю таблицы (в схеме данных запроса), или использовать раскрывающиеся списки строк Имя таблицы и Поле таблицы конструктора. Созданный запрос сохраняем и запускаем.

Заполненный конструктор запросов представлен на рисунке 11.

Рисунок 10 – Добавление таблицы в Конструктор запросов

Рисунок 11 – Простой однотабличный запрос на выборку

Простые многотабличные запросы

1. Определите адреса и телефоны поставщиков, выполнивших поставки в течение одного (любого) месяца.

2. Получите список товаров поставленных поставщиками X, Y, Z в феврале и марте (наименования поставщиков задайте самостоятельно).

3. Определите адреса и номера телефонов контактных лиц поставщиков X, Y, Z (наименования поставщиков задайте самостоятельно).

4. Склады Б и В попали в зону стихийного бедствия. Какие товары пострадали?

5. Получите список товаров поставленных во втором квартале текущего года. Список должен содержать дату поставки, наименование поставщика, наименование товара, его код и цену.

6. Выясните, поставлял ли поставщик Х товар Y на склад №… в апреле – мае текущего года (наименования поставщика и товара, а также номер склада задайте самостоятельно).

7. Вы хотите приобрести товар X. К каким контактным лицам следует обратиться?

8. В данный момент некоторые товары отсутствуют. К каким поставщикам и контактным лицам поставщиков следует обратиться?

9. Получите список товаров поставки, которых не совершались (если будет получена пустая таблица – таких товаров нет).

10. Определите, есть ли поставщики, которые не совершали поставок.

Пример: Определить адреса и телефоны поставщиков, выполнивших поставки в феврале месяце.

Все необходимые поля находятся в таблицах Поставщики и Поставки.

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

В таблице конструктора указываем поля Наименование, Адрес, Телефон из таблицы Поставщики (в них находятся необходимые данные) и поле Дата_поставки из таблицы Поставки, оно необходимо для задания условия отбора записей. Условие отбора по дате – >=#01.02.2011# And <#01.03.2011#. Заполненный конструктор представлен на рисунке 12.

Созданный запрос сохраняем и запускаем его на выполнение.

Рисунок 12 – Простой многотабличный запрос на выборку

Пример: Получить список товаров поставки, которых не совершались.

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

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

Двойным щелчком левой клавишей мыши по связи между таблицами в схеме данных запроса открываем окно Параметры объединения. Устанавливаем второй способ объединения – все записи из таблицы Товар и только те записи из таблицы Поставки, в которых связанные поля совпадают (рисунок 13)

В таблице конструктора указываем поля Наименование, Код_товара из таблицы Товар и поле Код_товара (или любое другое) из таблицы Поставки. Для поля Код_товара из таблицы Поставки задаём условие отбора Is Null – незаполненные ячейки. Заполненный конструктор представлен на рисунке 14.

Созданный запрос сохраняем и запускаем его на выполнение.

Рисунок 13 – Параметры объединения записей в запросе

Рисунок 14 – Простой многотабличный запрос с объединением записей

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

1. Получите список товаров, новая стоимость которых превышает Y рублей (цены выросли на 30%). Для преобразования результата вычислений в денежный тип данных используйте функцию CCur().

2. Вычислите стоимость товаров с учётом НДС. Запрос должен содержать код и наименование товара, его цену и цену с учётом НДС. Используйте в вычисляемом поле функцию CCur().

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

4. Вычислите новую стоимость товаров поставленных после 15 марта текущего года (цены выросли на 20%).

5. Рассчитайте величину Стоимости для каждой выполненной поставки (цена каждого товара с учётом НДС). Расчётная формула: Цена * (1 + Ставка НДС) * Количество.

6. Используя функцию Weekday(), определите наименования товаров, которые были поставлены в каждый понедельник мая и июня (данная функция преобразует дату в номер дня в неделе, счёт дней начинается с воскресенья).

Пример: Получить список товаров, новая стоимость которых превышает 500 рублей (цены выросли на 30%). В запросе использовать функцию CCur().

Запрос однотабличный, все необходимые поля находятся в таблице Товар.

Запускаем Конструктор запросов, добавляем таблицу в запрос.

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

=CCur([Товар]![Цена]*1,25)

дополнительно для вычисляемого поля добавляем условия отбора записей. Заполненный конструктор представлен на рисунке 15.

Для создания вычисляемых полей можно использовать Построитель выражений. Он позволяет выбирать поля таблиц и запросов базы данных, автоматически создаёт ссылки на выбранные поля и размещает ссылки в рабочем окне Построителя. Кроме этого можно выбирать из списка встроенные функции MS Access. Основные этапы работы с Построителем представлены на рисунках 16 и 17.

Созданный запрос сохраняем и запускаем.

Рисунок 15 – Запрос с вычисляемым полем

Рисунок 16 – Построитель выражений, выбор функции

Рисунок 17 – Построитель выражений, создание выражения

Запросы с групповыми операциями

1. Определите, сколько поставщиков находится в каждом регионе.

2. Определите, скольких товаров нет в наличии.

3. Определите последние даты поставок для всех товаров.

4. Определите, сколько единиц каждого товара было поставлено в марте и апреле текущего года.

5. Определите, сколько поставок было выполнено поставщиком X в течение первого квартала.

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

Пример: Определить, сколько поставщиков находится в каждом регионе.

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

Запускаем Конструктор запросов, добавляем в запрос указанную таблицу. Добавляем в запрос групповые операции (итоги).

В таблице конструктора указываем поле Регион, даём указание сгруппировать записи по содержимому этого поля (групповая операция – Группировка). Указываем поле Регион ещё раз, выбираем функцию для расчёта количества записей в полученных группах (групповая операция – функция Count). Заполненный конструктор представлен на рисунке 18.

Созданный запрос сохраняем и запускаем его на выполнение.

Рисунок 18 – Запрос с групповыми операциями

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

1. С помощью мастера запросов создайте перекрёстный запрос на основе данных таблицы Поставки. Для этого:

• запустите мастер командой вкладка Создание → Мастер запросов (рисунок 10);

• в первом окне мастера выберите тип запроса – перекрёстный запрос, нажмите Ok;

• во втором окне выберите таблицу Поставки и нажмите Далее;

• в следующих окнах выберите поле Номер_поставщика, которое будет использовано в качестве заголовков строк и поле Номер_склада, которое будет использовано в качестве заголовков столбцов;

• в предпоследнем окне мастера выберите поле Количество и функцию Сумма, подтвердите вычисление итогового значения для каждой строки;

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

Комбинированные запросы на выборку

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

• запрос на определение количества поставок по регионам;

• запрос для определения суммарного количества поставок (может быть выполнен к предыдущему запросу или к таблице Поставки);

• запрос с вычисляемым полем и расчётной формулой Количество поставок по регионам / Суммарное количество поставок * 100.

2. Определите номера телефонов поставщиков выполнивших поставок на сумму, превышающую Х рублей. Подсказка – предварительно создайте промежуточный запрос на определение общей суммы поставок для каждого поставщика (данный запрос можно выполнить к запросу 5 из группы Запросы с вычисляемыми полями, если необходимо дополните запрос 5 новыми полями).

3. Определите номер телефона контактного лица поставщика, выполнившего наибольшее количество поставок в первом квартале года.

4. Определите, название самого дорогого товара, находящегося на каждом из складов.

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

Анализ данных с помощью сводных диаграмм

1. В режиме сводной диаграммы выполните анализ списка товаров, поставленных во втором квартале текущего года (запрос 5 из группы Простые многотабличные запросы). Настройте сводную диаграмму:

• запустите указанный запрос, на вкладке Главная выберите режим Сводная диаграмма;

• в появившемся макете выберите и перетащите с помощью левой клавиши мыши поле Код товара в область категорий, поле Цена в область данных, Дата поставки по месяцам в область фильтра.

2. Измените сумму по полю Цена на среднее значение (для этого воспользуйтесь пунктом Автовычисления контекстного меню, которое появится после нажатия правой клавишей мыши на кнопку Сумма «Цена»).

3. Замените поле Код товара на поле Наименование поставщика. Оцените произошедшие изменения.

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