Вычисляемые поля в запросах. Построитель выражений

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

Новое вычисляемое поле создается в «Бланке запроса». Вы­ражение записывается в пустую ячейку бланка с помощью «Построи­теля выражений»,который предназначен для составления математи­ческих, логических и других выражений.

Окно «Построитель выражений» вызывается нажатием кнопки «Построить»на панели инструментов.

Окно «Построитель выражений» включает в себя четыре области с собственными полосами прокрутки. Создаваемое выражение фор­мируется в верхней области – поле ввода выражения. Там же разме­щаются и кнопки с некоторыми операторами.

Три области поиска элементов для записи выражения размещены в нижней части окна. Левая область содержит список источников дан­ных (таблицы, запросы и др.) и средства для записи выражений (функции, операторы и др.); средняя область – элементы, которые входят в выбранный в левом окне объект; правая область предназначена для формирования нуж­ного объекта.

Поиск нужных элементов начинается с левой области. Списки объектов в левой, средней и правой областях имеют иерархическое подчинение.

Пример 1. Выведите все сделки с ТОДО, совершенные эконо-мистом-менеджером после 01.01.2004 года.

Структура запроса: НомерСделки, Дата, Операция, НазваниеКлиента, Фамилия, СтоимостьСделки.

Решение. Для получения вычисляемого поля Стоимость-Сделки воспользуемся вспомогательным запросом, в котором вычислим выражение СтоимостьТоваров, а затем сложим его с полем СтоимостьДоставки (стоимость сделки равна сумме сто-имостей всех товаров в этой сделке плюс стоимость доставки).

Вспомогательный запрос: подсчитать стоимость товаров для каждой сделки с ТОДО, совершенной экономистом-менеджером после 01.01.2004 года. Структура запроса: КодСделки (для связи с основным запросом), НомерСделки, Дата, Операция, НазваниеКлиента, Фамилия, Должность, СтоимостьТоваров.

Этапы создания вспомогательного запроса:

1. В «Конструкторе запроса» добавляем таблицы: «Сделки», «Клиенты», «Сотрудники», «СодержаниеСделки» и «Товары».

2. Выбираем поля результатов запроса: из таблицы «Сделки» — поля «КодСделки», «НомерСделки», «Дата»и«Операция»; из таблицы «Клиенты» – поле «НазваниеКлиента»; из таблицы «Сотрудники» – поля «Фамилия» и «Должность».

3. Для вычисления выражения СтоимостьТоваров необходимо поле КоличествоТовара умножить на поле ЦенаЗаЕдиницу.

Поэтому для записи выражения СтоимостьТоваров надо:

• перейти на строку «Поле » пустого столбца «Бланка запроса»;

• нажать кнопку « Построить » на панели инструментов;

• записать выражение:

[СодержаниеСделки]![КоличествоТовара]*[Товары]![Цена За­Единицу];

• нажать кнопку «ОК»; выраже­ние, которое записано с помощью «Построителя выражений », появляется в строке «Поле»Бланка запросов. Каждое поле итоговой таблицы должно иметь имя. MS Access автоматически присвоит имя Выражение1,которое отделяется от выражения двоеточием. Нужно заменить имя поля итога на СтоимостьТоваров.

Замечание. Если в выражении используются арифметические операторы (+, -, *, /), а одно из полей имеет значение Null, то результатом всего выражения также будет значение Null. Если записи в одном из использованных в выражении полей могут содержать значение Null, то эти значения Null можно преобразовать в нули с помощью функции Nz. Так, например, Nz(Содер-жаниеСделки!КоличествоТовара, 0) * Nz(Товары!ЦенаЗаЕдиницу, 0).

4. Задание критериев отбора:

• ввести выражение Like "ТОДО*" в ячейку «Условие отбора» для поля «НазваниеКлиента». Оператор Like означает «похожий» и применяется с символами шаблонов (см. табл. 4.1);

• ввести выражение «экономист-менеджер» в ячейку «Условие отбора» для поля «Должность»;

• с помощью « Построи­теля выражений » в ячейку «Условие отбора» ввести выражение Between #01.01.2004# And Now() для поля «Дата» (оператор Between означает «между», а функция Now() возвращает текущую системную дату).

Примечание. Для задания границ диапазона применяются следующие опера­торы: > (больше), >= (не меньше), < (меньше), <= (не больше), Beetween (между).

 

Таблица 4.2 — Символы шаблонов

Символ в образе	Соответствие в выражении
?	любой один текстовый символ
*	последовательность любых знаков
#	любая одна цифра
[список знаков]	любой один знак в «списке знаков»
[!список знаков]	любой один знак, который не входит в «список знаков»

5. Далее выполним вычисление суммы в группе записей (для подсчета суммы стоимостей всех товаров в каждой сделке).

Для этого надо на­жать на кнопку«Групповые операции»на панели инструментов. Пос­ле этого в «Бланке запроса» по­явится новая строка под названием «Групповые операции».В ней в соответствующей графе следует указать тип выполняемого вычисления, раскрывая список типов операций (например, Sum – сложение, Avg – среднее, Min – минимальное, Max – максимальное значение, Count – количество записей и т.п.).

Таким образом, в ячейке «Групповые операции » для поля «СтоимостьТоваров» выбираем операцию «Sum».

6. Сохраняем запрос под именем «Вспомогательный».

Этапы создания основного запроса

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

2. Связать запрос «Вспомогательный» и таблицу «Сделки» по полю «КодСделки».

3. Добавить в основной запрос поля результатов «НомерСделки», «Дата», «Операция», «НазваниеКлиента» и «Фамилия» из запроса «Вспомогательный».

4. В строку «Поле» пустого столбца Бланка запросов с помощью « Построителя выражений » введем выражение

СтоимостьСделки: Вспомогательный!СтоимостьТоваров +

Сдел­ки!СтоимостьДоставки

5. Сохранить запрос под именем «Основной»(рис. 4.7).

Замечания

1. Для выбора записей с таблицы, значения которых не соответствуют определенному условию одного из полей, предназначен оператор Not, который записывается перед сравниваемым значением. Например, для того чтобы выбрать все записи из таблицы «Клиенты», за исключением записей с отсутствующими или неизвестными данными о договоре,надо в строке «Условие отбора»поля «Договор» записать: Is Not Null.

 

Рис. 4.7. Запрос на выборку «Основной»

 

2 (пример задания диапазона символьных данных).Вывести список то­варов с наименованиями от буквы «А» до буквы «Д». Для этого в строке «Условия отбора» поля «НаименованиеТовара» необходимо ввести запись: Like "[А-Д]*".

3. Часто приходится выбирать записи по условию, которое задается для нескольких полей таблицы или по нескольким условиям для одного поля. В этом случае применяются «И-запросы» (выбор записей только при условии выполнения всех ус­ловий) и «ИЛИ-запросы» (выбор записей при выполнении хотя бы одного из условий). При задании «ИЛИ-запроса» каждое условие выборки должно раз­мещаться на отдельной строке «Бланка запроса». Так, например, запрос для отборатоваров с наименованиями от буквы «А» до буквы «Д» и ценой не больше 50 000 или больше 100 000 показан на рис. 4.8.

Рис. 4.8. Пример ИЛИ-запроса

 

Кроме запросов выбора, применяются и другие запросы: параметрические, перекрестные и запросы действия.

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

Для каждого поля, которое предполагается использовать как параметр, нужно ввести в ячейку строки «Условие отбора » выра-жение с текстом приглашения, заключенным в квадратные скобки. Например, для поля «Дата» можно ввести приглашения следующего вида: «Введите начальную дату:» и «Введите конечную дату:», чтобы задать границы диапазона значений:

Between [Введите начальную дату:] And [Введите конечную дату:].Для числового и денежного полей необходимо использовать функцию Val(строка) для преобразования введенного параметра в число. Например, для нахождения товаров, цена за единицу которых больше некоторой величины, вводится параметр: >Val ([Введите параметр:]).

Чтобы запросить у пользователя один или несколько знаков для поиска записей, которые начинаются с этих знаков или содержат их, можно использовать оператор LIKE и подстановочный знак (*). Например, следующее выражение выполняет поиск клиентов, начинающихся с указанной буквы:

Like [Введите первую букву клиента:] & "*"

Примечание. В перекрестном запросе необходимо указать тип данных для параметров (меню Запрос→команда Параметры).

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

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

 

Таблица 4.3 — Данные, отобранные запросом на выборку

НаименованиеТовара	НазваниеКлиента	Сумма
стол	ТОДО "Альфа"	63 000 р.
стул	ТОДО "Альфа"	145 000 р.
тумба	ТОДО "Альфа"	38 000 р.
стул	ТОДО "Гамма"	116 000 р.
кровать	ОАО "Бета"	140 000 р.
тумба	ОАО "Бета"	76 000 р.
стул	ОАО "Бета"	58 000 р.
шкаф	УП "Дельта"	109 000 р.
стол	УП "Дельта"	63 000 р.
стул	УП "Дельта"	58 000 р.
стул	ОАО "Бета"	232 000 р.
тумба	ОАО "Бета"	76 000 р.
кровать	ОАО "Бета"	140 000 р.

Таблица 4.4 — Представление данных в перекрестном запросе

НаименованиеТовара	ОАО «Бета»	ТОДО «Альфа»	ТОДО «Гамма"	УП "Дельта"
кровать	280 000 р.
стол		63 000 р.		63 000 р.
стул	290 000 р.	145 000 р.	116 000 р.	58 000 р.
тумба	152 000р.	38 000 р.
шкаф				109 000 р.

Последовательность действий при создании перекрестного запроса с помощью Мастера

1. На вкладке «Запросы» окна БД нажать кнопку «Создать».

2. В диалоговом окне «Новый запрос» выбрать в списке «Перекрестный запрос» и нажать кнопку «ОК»; откроется окно «Создание перекрестных таблиц».

3. Выбрать из списка запрос-источник и дальше выполнять инструкции в диалоговых окнах Мастера.

Последовательность действий при создании перекрестного запроса при помощи Конструктора

1. В режиме Конструктора создать запрос-источник.

2. На панели инструментов нажать кнопку « Тип запроса » и выбрать опцию « Перекрестный ».

3. Для полей, значения которых будут заголовками строк, в строке «Перекрестная таблица»выбрать ячейку «Заголовки строк»и оставить в строке «Групповая операция»значение «Группировка».

4. Для полей, значения которых будут представлены в запросе в качестве заголовков столбцов, в строке «Перекрестная таблица»выбрать ячейку «Заголовки столбцов».Значение «Заголовки столбцов»можно выбрать только для одного поля и оставить «Группировка».

Примечание. По умолчанию заголовки столбцов сортируются по алфавиту или числовому значению.

5. Для поля, значения которого нужно использовать при создании перекрестной таблицы, щелкните ячейку строки «Перек-рестная таблица» и выберите «Значение».

Примечание. Пункт «Значение» можно выбрать только для одного поля.

6. В строке «Групповая операция» выберите статистическую функцию, которая будет использована для заполнения перек-рестной таблицы (например, Sum, Avg или Count).

7. Далее выполните следующие действия:

─ для задания условия отбора, ограничивающего отбираемые заголовки строк до выполнения вычисления, необходимо ввести выражение в строку «Условие отбора » поля, для которого в ячейке строки «Перекрестная таблица» выбрано значение «Заголовки строк»;

─ для задания условия отбора, ограничивающего отбираемые записи до группировки заголовков строк и до заполнения перекрестной таблицы, необходимо добавить поле, для которого необходимо установить условие отбора, в бланк запроса; выбрать значение «Условие » в ячейке строки «Групповая операция»; оставить ячейку в строке «Перекрестная таблица » пустой; ввести выражение в ячейку строки «Условие отбора».

Примечание. Поля, имеющие значение «Условие » в строке «Групповая операция», не выводятся в результатах запроса.

Пример 2. Распределите суммы сделок по всем товарам (сумма=sum(количество*цена)), начинающимся с буквы «С», и всем клиентам, которые были совершены после 01.01.2004 года.

Решение представлено на рисунке 4.9.

 

Рис. 4.9. Перекрестный запрос

 

Запросы на изменение

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

Существует четыре типа запросов на изменение.

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

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

 

Рис. 4.10. Запрос на удаление

Рис. 4.11. Запрос на обновление

 

· На добавление. Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Например, появилось несколько новых клиентов, а также база данных, содержащая сведения о них. Чтобы не вводить все данные вручную, их можно добавить в таблицу «Клиенты».

· На создание таблицы. Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Запрос на создание таблицы полезен при создании таблицы для экспорта в другие базы данных MS Access или при создании архивной таблицы, содержащей старые записи.

Примечание. В окне базы данных MS Access перед именем запроса действия устанавливает восклицательный знак (!).

Замечание. Для просмотра записей предназначена кнопка «Вид»на панели инструментов. Для возвращения в режим Конструктора запросов требуется снова нажать кнопку «Вид»на панели инструментов. Любые изменения надо вносить в режиме Конструктора; для запуска запроса на изменение надо нажать кнопку « Запуск » (!) на па­нели инструментов.

Задания к лабораторным занятиям 7 – 13

 

1. База данных «Сессия».

1. Приведите схему данных созданной БД «Сессия» к виду, указанному на рис. 4.12.

 

Рис. 4.12. Схема данных БД «Сессия»

 

2. Изучите создание простого запроса на выборку мастером.

• Подсчитайте количество студентов, сдававших каждый предмет. Структура: Наименование, Count - Студенты.

• Подсчитайте средний балл по каждому факультету. Структура запроса: Название, Avg - Оценка.

Рассмотрите созданные с помощью мастера запросы в режиме Конструктора. Обратите внимание на:

─ схему данных запроса (верхняя часть окна Конструктора): список таблиц-источников и связь между ними;

─ бланк запроса (нижняя часть окна).

Ознакомьтесь с функциями каждой строки бланка запроса: «Поле», «Имя таблицы», «Групповая операция», «Сортировка», «Вывод на экран», «Условие отбора», «или».

Отсортируйте записи в запросах:

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

• по убыванию среднего балла.

Добавьте условие отбора таким образом, чтобы:

• подсчитать количество студентов, сдававших предметы «ТОХОД» или «ОИиВТ»;

• подсчитать средний балл по факультету «ФЭУ».

Добавьте в запрос о количестве студентов после поля «Наиме-нование»поле «ФИОПреподавателя», чтобы получилась струк-тура: «Наименование», «ФИОПреподавателя», Count - Студенты.

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

3. Создайте запросы с помощью Конструктора.

3.1.Выведите результаты по предмету «ТОХОД» учебного года 2004-2005, в которых экзамен был сдан на оценку «4» или «5».

Структура запроса: ФИОСтудента, Курс, Группа, Предмет, ДатаЭкзамена, Оценка (условия создайте при помощи Построи­теля выражений).

3.2. Выведите летние экзамены, которые сдали студенты факультета «ФЭУ» на оценку «3».

Структура запроса: ФИОСтудента, Название, Курс, Группа, Предмет, ДатаЭкзамена, Оценка (условие о летних экзаменах создайте с помощью функции Month и оператора And).

3.3. Выберите из базы данных всех студентов некоторого факультета (вводится как параметр) 2 курса.

Структура: ФИОСтудента, Название, Курс, Группа.

3.4. Выберите из базы данных всех студентов, которые обучаются на некотором курсе (вводится как параметр) и сдавали экзамен у преподавателя, фамилия которого начинается с некоторого набора символов (вводится как параметр).

Структура запроса: ФИОСтудента, Курс, Группа, Наименование, ФИОПреподавателя.

3.5. Определите средний балл каждого студента.

Структура: ФИОСтудента, НомерЗачетки, СреднийБалл.

3.6. Для каждого студента вычислите размер стипендии (средний балл сессии * 11000р.), когда даты начала и конца сессии вводятся как параметры.

Структура запроса: ФИОСтудента, Стипендия.

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

Структура вспомогательного запроса: Название, ФИОСтудента, Стипендия.

Структура основного запроса: Название, СуммаДенег.

3.8. Определите количество студентов, сдававший каждый предмет для каждого факультета в учебном году 2003-2004.

Структура запроса: Наименование (строки), Название (столбцы), ФИОСтудента (Count, значение).

3.9. Определите средний балл по каждому предмету для каждого года после 2001. Структура запроса: Наименование (строки), Годы (столбцы), Оценка (Avg, значение).

3.10. Из таблицы «Сессии» выберите всю информацию о неуспевающих студентах и поместите ее в таблицу «Двоечники».

3.11. Удалите из таблицы «Сессии»всех двоечников.

3.12. Измените в таблице «Двоечники»дату экзамена по некоторому предмету на текущую дату (вводится как параметр).

3.13. Добавьте записи за текущую дату из таблицы «Двоечники» в таблицу «Сессии».

2. База данных «Изготовление мебели».

1. Приведите схему данных БД «Изготовление мебели» к виду, указанному на рис. 4.13.

 

Рис. 4.13. Схема данных БД «Изготовление мебели»

 

2. Изучите создание простого запроса на выборку мастером.

• Подсчитайте в заявках суммарное количество каждого товара. Структура запроса: Товар, Sum - Количество.

• Подсчитайте среднюю цену за единицу товара по каждому клиенту. Структура запроса: Клиент, Avg - ЦенаЗаЕдиницу.

Рассмотрите созданные с помощью мастера запросы в режиме Конструктора. Обратить внимание на:

─ схему данных запроса (верхняя часть окна Конструктора): список таблиц-источников и связь между ними;

─ бланк запроса (нижняя часть окна).

Ознакомьтесь с функциями каждой строки бланка запроса: «Поле», «Имя таблицы», «Групповая операция», «Сортировка», «Вывод на экран», «Условие отбора», «или».

Отсортируйте записи в запросах:

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

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

Добавьте условие отбора таким образом, чтобы:

• подсчитать в заявках суммарное количество столов или стульев (для первого запроса);

• подсчитать среднюю цену за единицу товара по клиенту «ФЭУ» (для второго запроса).

Добавьте в запрос о средней цене после поля «Клиент» поле «ДатаЗаявки», чтобы получилась структура запроса: Клиент, ДатаЗаявки, Avg - ЦенаЗаЕдиницу.

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

3. Создайте запросы с помощью Конструктора.

3.1. Выведите все заявки на покупку столов, совершенные после 01.07.2005 года.

Структура запроса: ДатаЗаявки, Клиент, Товар, Коли-чество, ЦенаЗаЕдиницу, Стоимость (поле «Стоимость» создайте при помощи Построи­теля выражений).

3.2. Выведите весенние заказы, в которых был заказан товар в количестве, большем 3.

Структура запроса: ДатаЗаявки, Клиент, Товар, Коли-чество, ЦенаЗаЕдиницу, Стоимость (условие о весне создайте при помощи встроенной функции Month и оператора And).

3.3. Выберите из базы данных информацию о выполненных заявках товаров, название которых начинается с некоторого набора символов (вводится как параметр), за 2004 год.

Структура запроса: ДатаЗаявки, Клиент, Признак, Товар, Количество, Стоимость.

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

Структура запроса: ДатаЗаявки, Клиент, Стоимость.

3.5. Найдите суммарную стоимость заявок каждого клиента после 01.01.2003 года. Структура запроса: Клиент, Стоимость.

3.6. Определите среднюю стоимость осенних заявок.

Структура вспомогательного запроса: КодЗаявки, Сумм-Стоимость. Структура основного запроса: СредняяСтоимость.

3.7. Выберите из базы данных все заявки некоторого года (вводится как параметр), у которых стоимость не меньше средней стоимости осенних заявок.

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

3.8. Определите количество каждого товара, заказанного каждым клиентом сезона сентябрь 2003 года – август 2004 года.

Структура запроса: Клиент (строки), Товар (столбцы), Количество (Sum, значение).

3.9. Определите стоимость выполненных заявок по каждому месяцу для каждого года начиная с 2002 года.

Структура запроса: Месяцы (строки), Годы (столбцы), Стоимость (Sum, значение).

3.10. Из таблицы «СодержаниеЗаявки» выберите всю информацию о заявках с нулевым количеством товара и поместите ее (информацию) в таблицу «НулевыеЗаявки».

3.11. Удалите из таблицы «СодержаниеЗаявки» все заявки с нулевым количеством товара.

3.12. В таблице «НулевыеЗаявки»измените количество заказанных товаров на другое (вводится как параметр).

3.13. Добавьте записи из таблицы «НулевыеЗаявки»в таб-лицу «СодержаниеЗаявки».

 

3. База данных «Продажа компьютеров».

1. Приведите схему данных созданной БД «Продажа компьютеров» к виду, указанному на рис. 4.14.

Рис. 4.14. Схема данных БД «Продажа компьютеров»

 

2. Изучите создание простого запроса на выборку мастером.

• Подсчитайте суммарное количество проданных товаров по каждому типу. Структура: ТипТоваров, Sum - Количество.

• Подсчитайте среднюю цену за единицу товара по каждому производителю. Структура запроса: Производитель, Avg – Це-наЗаЕдиницу.

Рассмотрите созданные с помощью мастера запросы в режиме Конструктора. Обратить внимание на:

─ схему данных запроса (верхняя часть окна Конструктора): список таблиц-источников и связь между ними;

─ бланк запроса (нижняя часть окна).

Ознакомьтесь с функциями каждой строки бланка запроса: «Поле», «Имя таблицы», «Групповая операция», «Сортировка», «Вывод на экран», «Условие отбора», «или».

Отсортируйте записи в запросах по:

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

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

Добавьте условие отбора таким образом, чтобы:

• подсчитать суммарное количество проданных принтеров или мониторов;

• подсчитать среднюю цену за единицу товара по производителю «Intel».

Добавьте в запрос о средней цене после поля «Производитель» поле «Страна», чтобы получилась структура запроса: Производитель, Страна, Avg - ЦенаЗаЕдиницу.

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

3. Создайте запросы с помощью Конструктора.

3.1. Выведите продажи, совершенные после 01.09.2004 года, в которых были проданы принтеры.

Структура запроса: ДатаПродажи, Клиент, Товар, Количество, ЦенаЗаЕдиницу, Стоимость (поле «Стоимость» создайте при помощи Построи­теля выражений).

3.2. Выведите весенние продажи, в которых был продан товар в количестве, большем 1. Структура запроса: ДатаПродажи, Клиент, Товар, Количество, ЦенаЗаЕдиницу, Стоимость (условие о весенних месяцах создайте при помощи встроенной функции Month и оператора And).

3.3. Выберите из базы данных все продажи товаров, которые начинаются с некоторого набора символов (вводится как параметр), за 2004 год. Структура запроса: ДатаПродажи, Клиент, ТипТоваров, Количество, Стоимость.

3.4. Выберите из базы данных все продажи, в которых участвовали товары, произведенные в некоторой стране (вводится как параметр), и стоимость которых больше некоторой величины (вводится как параметр). Структура запроса: ДатаПродажи, ТипТоваров, Товар, Производитель, Количество, Стоимость.

3.5. Определите суммарную стоимость каждой продажи.

Структура запроса: ДатаПродажи, Клиент, Стоимость.

3.6. Определите среднюю стоимость летних продаж.

Структура вспомогательного запроса: КодПродажи, Сумм-Стоимость. Структура основного запроса: СредняяСтоимость.

3.7. Выберите из базы данных все продажи некоторого года (вводится как параметр), у которых стоимость больше средней стоимости летних продаж.

Структура вспомогательного запроса: ДатаПродажи, Клиент, Стоимость (группировка для расчета стоимости и условие на год продажи). Структура основного запроса: ДатаПродажи, Клиент, Стоимость (используются вспомогательный запрос и запрос 3.6 для условия отбора по стоимости продаж).

3.8. Определите количество компьютеров, проданных в каждый месяц каждого года, начиная с 2001 года.

Структура запроса: Месяцы (строки), Годы (столбцы), Количество (Sum, значение).

3.9. Определите стоимость продаж 2003 и 2004 годов по каждому типу товаров для каждой страны-производителя.

Структура запроса: ТипТоваров (строки), Страна (столбцы), Стоимость (Sum, значение).

3.10. Из таблицы «СодержаниеПродажи» выберите всю ин-формацию о продажах с нулевым количеством товара и поместите ее в таблицу «НулевыеПродажи».

3.11. Удалите из таблицы «СодержаниеПродажи» все продажи с нулевым количеством товара.

3.12. Измените в таблице «НулевыеПродажи» количество проданных товаров на другое (вводится как параметр).

3.13. Добавьте записи из таблицы «НулевыеПродажи» в таблицу «СодержаниеПродажи».

 

4. База данных «Продажа лекарств».

1. Приведите схему данных созданной БД «Продажа лекарств» к виду, указанному на рис. 4.15.

 

Рис. 4.15. Схема данных БД «Продажа лекарств»

 

2. Изучите создание простого запроса на выборку мастером.

• Подсчитайте суммарное количество проданных лекарств по каждой льготе. Структура: ПроцентОплаты, Sum - Количество.

• Подсчитайте среднюю цену за единицу лекарства по каждо-му изготовителю. Структура: Изготовитель, Avg-ЦенаЗаЕдиницу.

Рассмотрите созданные с помощью мастера запросы в режиме Конструктора. Обратите внимание на:

─ схему данных запроса (верхняя часть окна Конструктора): список таблиц-источников и связь между ними;

─ бланк запроса (нижняя часть окна).

Ознакомьтесь с функциями каждой строки бланка запроса: «Поле», «Имя таблицы», «Групповая операция», «Сортировка», «Вывод на экран», «Условие отбора», «или».

Отсортируйте записи в запросах по:

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

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

Добавьте условие отбора таким образом, чтобы:

• подсчитать суммарное количество проданных лекарств с процентами оплаты «50 %» или «10 %»;

• подсчитать среднюю цену за единицу товара по изготови-телю «Биохимик».

Добавьте в запрос о средней цене после поля «Изготовитель»поле «Страна», чтобы получилась структура запроса: Изготови-тель, Страна, Avg - ЦенаЗаЕдиницу.

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

3. Создайте запросы с помощью Конструктора.

3.1. Выведите продажи после 01.11.2004 года, в которых были проданы лекарства с неполной оплатой.

Структура запроса: ДатаПродажи, Клиент, Лекарство, ПроцентОплаты, Количество, ЦенаЗаЕдиницу, Стоимость (поле «Стоимость» создайте при помощи Построи­теля выражений).

3.2. Выведите летние продажи, в которых было продано лекарство в количестве, большем 1. Структура: ДатаПродажи, Клиент, Лекарство, Процент-Оплаты, Количество, ЦенаЗаЕдиницу, Стоимость (условие о летних месяцах создайте при помощи функции Month и оператора And).

3.3. Выберите из базы данных информацию о продажах лекарств, название которых начинаются с некоторого набора символов (вводится как параметр), за 2003 год.

Структура запроса: ДатаПродажи, Клиент, Лекарство, Количество, ПроцентОплаты, Стоимость.

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

Структура: ДатаПродажи, Лекарство, Страна, Стоимость.

3.5. Определите суммарную стоимость каждой продажи.

Структура запроса: ДатаПродажи, Клиент, Стоимость.

3.6. Определите среднюю стоимость осенне-зимних продаж. Структура вспомогательного запроса: КодПродажи, Сумм-Стоимость. Структура основного запроса: СредняяСтоимость.

3.7. Выберите из базы данных все продажи некоторого года (вводится как параметр), у которых стоимость больше средней стоимости для осенне-зимних продаж.

Структура вспомогательного запроса: ДатаПродажи, Клиент, Стоимость (группировка для расчета стоимости и условие на год продажи). Структура основного запроса: ДатаПродажи, Клиент, Стоимость (используются вспомогательный запрос и запрос 3.6 для условия отбора по стоимости продаж).

3.8. Определите количество каждого лекарства, проданного в каждый год, начиная с 2001 года. Структура запроса: Лекарство (строки), Годы (столбцы), Количество (Sum, значение).

3.9. Определите стоимость продаж 2003 и 2004 годов по каждому месяцу для каждого изготовителя. Структура: Месяцы (строки), Изготовитель (столбцы), Стоимость (Sum, значение).

3.10. Из таблицы «СодержаниеПродажи» выберите всю информацию о продажах лекарств с нулевым процентом оплаты и поместите ее в таблицу «Бесплатные».

3.11. Удалите из таблицы «СодержаниеПродажи» все продажи лекарств с нулевым процентом оплаты.

3.12. Уменьшите в таблице «Бесплатные» количество лекарства на 1, при условии, что количество лекарства больше 0.

3.13. Добавьте записи из таблицы «Бесплатные» в таблицу «СодержаниеПродажи».

5. База данных «Продажа обуви».

1. Приведите схему данных созданной БД «Продажа обуви» к виду, указанному на рис. 4.16.

 

Рис. 4.16. Схема данных БД «Продажа обуви»

 

2. Изучите создание простого запроса на выборку мастером.

• Подсчитайте суммарное количество проданной обуви каждого типа. Структура: ТипТоваров, Sum - Количество.

• Подсчитайте среднюю цену за пару обуви по каждому изготовителю. Структура: Изготовитель, Avg - ЦенаЗаЕдиницу.

Рассмотрите созданные с помощью мастера запросы в режиме Конструктора. Обратите внимание на:

─ схему данных запроса (верхняя часть окна Конструктора): список таблиц-источников и связь между ними;

─ бланк запроса (нижняя часть окна).

Ознакомьтесь с функциями каждой строки бланка запроса: «Поле», «Имя таблицы», «Групповая операция», «Сортировка», «Вывод на экран», «Условие отбора», «или».

Отсортируйте записи по:

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

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

Добавьте условие отбора таким образом, чтобы:

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

• подсчитать среднюю цену за пару обуви по изготовителю «Белвест».

Добавьте в запрос о суммарном количестве после поля «ТипТоваров»поле «Модель», чтобы получилась структура запроса: ТипТоваров, Модель, Sum - Количество.

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

3. Создайте запросы с помощью Конструктора.

3.1. Выведите продажи, совершенные после 01.03.2005 года, в которых были проданы полуботинки или сапоги.

Структура запроса: ДатаПродажи, Клиент, ТипТоваров, Модель, Количество, ЦенаЗаЕдиницу, Стоимость (поле «Стоимость» создайте при помощи Построи­теля выражений).

3.2. Выведите все летние продажи, в которых была продана обувь изготовителя «Неман».

Структура запроса: ДатаПродажи, Клиент, ТипТоваров, Модель, Изготовитель, Количество, ЦенаЗаЕдиницу, Стоимость (условие о летних продажах создайте при помощи встроенной функции Month и оператора And).

3.3. Выберите из базы данных информацию о продажах обуви, тип которой начинается с некоторого набора символов (вводится как параметр), за 2004 год.

Структура запроса: ДатаПродажи, Клиент, ТипТоваров, Изготовитель, Модель, Количество, Стоимость, Подарок.