Лабораторный практикум по курсу “СУБД” для студентов гуманитарных факультетов (стр. 5 )

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

Например, синтаксис (правило обращения) функции DateDiff следующий:

DateDiff(interval;date1; date2[, firstdayofweek[, firstweekofyear]])

Первые три параметра объяснялись выше, а параметры firstdayofweek и firstweekofyear необязательные (в синтаксисе это указывается тем, что они располагаются за квадратными скобками). Если эти параметры опущены, то их значения подставятся из значений по умолчанию.

Эти параметры позволяют задать следующую информацию:

Firstdayofweek (задает, что считать первым днем недели) может иметь значения от 0 до 7

0	Использует установки системы по умолчанию
1	Воскресенье (по умолчанию)
2	Понедельник
3	Вторник
4	Среда
5	Четверг
6	Пятница
7	Суббота

Firstweekofyear (задает, что считать первой неделей года) может иметь значения от 0 до 7

0	Использует установки системы по умолчанию
1	Начинается с недели, в которой есть день 1 января (по умолчанию).
2	Начинается с первой недели, в которой есть, по крайней мере, 4 дня нового года
3	Начинается с первой полной недели года

Проще всего получить справочную информацию по синтаксису функций можно следующим образом:

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

2.  В левом окне Построителя раскрыть папку Функции –> Встроенные функции

3.  Выбрать во втором столбце нужную группу функций

4.  Выбрать в последнем столбце нужную функцию и нажать кнопку Справка в окне построителя выражений.

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

Создайте следующие запросы:

1.  Найдите заказы, которые исполнялись больше 10 дней (для этого необходимо создать вычисляемое поле Срок исполнения и на него наложить ограничение > 10)

2.  Найдите заказы, которые не оплатили (наложить условие IS NULL на поле ДатаОплаты)

3.  Добавьте к основным полям таблицы Заказы названия дня недели для даты размещения заказа. Для этого надо воспользоваться двумя функциями WeekDayName() и функцией WeekDay(). Посмотрите какие параметры у этих функций – и вы увидите, что функция WeekDayName имеет числовой параметр (то есть ей надо задать значение номер дня недели от 1 до 7), поэтому и необходимо из даты сначала выделить номер дня недели, а затем по номер определить имя дня недели. В результате выражение будет записываться так:

День размещения заказа: WeekDayName(WeekDay(ДатаРазмещения))

 

Лабораторная работа № 9.

Тема: Запросы в Access (продолжение).

ЗАПРОСЫ С ПАРАМЕТРАМИ.

Как вы познакомились выше, в запросе можно задать условие с конкретными значениями требуемого поля непосредственно в бланке. Однако для решения практических задач пользователь должен иметь возможность вводить значение поля в диалоговом режиме в процессе выполнения запроса. Для этого необходимо определить параметр запроса.

Определение параметра для значения поля.

Имя параметра запроса может задаваться непосредственно в поле Условие отбора в квадратных скобках. При выполнении запроса это имя появится в диалоговом окне Введите значение параметра.

В запросе “Найдите заказы, которые исполнялись больше 10 дней” вместо условия > 10 введите параметр условие: > [Задайте количество дней]

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

Задание.

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

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

Указание! Поле Скидка задана в процентном формате и поэтому вы должны задавать значение 0,01, 0,03 и т. д.

Многотабличные запросы на выборку.

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

Задание.

4.  Создайте запрос, в котором бы отбиралась вся информация о Заказах и Магазинах, сделавших заказы.

5.  Измените этот запрос, так чтобы в него вошла информация обо всех Магазинах и Заказах, для тех магазинов, которые сделали заказы.

Указание. Для выполнения этого запроса необходимо вспомнить, что есть три типа соединения таблиц!

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

Когда вы установите этот вид соединения, то дуга, соединяющая две таблицы в верхней части бланка QBE примет вид направленной стрелки.

6.  На основании предыдущего запроса найдите магазины, которые не сделали заказов Указание. В условие отбора для поля КодЗаказа введите условие is NULL и получите список магазинов, которые ничего не заказали. Можно послать им уведомление!

7.  Создайте запрос на основе таблицы Продукты и ТипыПродуктов, в котором отбирались бы продуктов определенного типа (тип должен быть параметром запроса)

Лабораторная работа № 10.

Тема: Запросы в Access(Запросы на группирование)

Запросы на группирование.

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

Например, если мы сгруппируем таблицу Продукты по ТипуПродуктов, то у нас получится 5 групп (столько у нас категорий продуктов).

Результатом этого запроса будет следующая таблица:

По умолчанию, поле задания параметра группировки является скрытым, но если, имея какой-нибудь запрос в режиме конструктора, обратиться в системное меню ВИД и кликнуть там мышью по режиму ГРУППОВЫЕ ОПЕРАЦИИ, то в бланке запроса появится дополнительная строка с аналогичным наименованием.

По умолчанию, в строке " Групповые операции" СУБД устанавливает условие "Группировка", в соответствие с которым данные по каждому полю группируются, но результат не выводится. Если же нужен некий конкретный результат, то вместо "группировки" следует применить одну из девяти групповых функций (см. таблицу):

Наименование	Назначение
SUM	Вычисляет сумму всех значений заданного поля в каждой группе. Применяется только для числовых и денежных полей.
AVG	Вычисляет среднее арифметическое всех значений данного поля в каждой группе. Применяется только для числовых и денежных полей.
MIN	Возвращает наименьшее значение в рамках каждой группы. Применяется для любых полей, кроме логических и "мемо". В случае текстового содержания, возвращается наименьшее символьное значение независимо от регистра.
MAX	Возвращает наибольшее значение в рамках каждой группы. Применяется для любых полей, кроме логических и "мемо". В случае текстового содержания, возвращается наибольшее символьное значение независимо от регистра.
COUNT	Возвращает число записей, в которых значение данного поля отличается от Null. Хотя при использовании конструкции Count (*) можно подсчитать число всех записей, включая "нулевые".
STDEV	Подсчитывает статистическое стандартное отклонение для всех значений данного поля в каждой группе. Функция применяется только для числовых и денежных полей. Если в группе менее двух записей, возвращается значение Null.
VAR	Подсчитывает статистическую дисперсию для всех значений данного поля в каждой группе. Применяется только к числовым и денежным полям. Если в группе менее двух записей, возвращается значение Null.
FIRST	Возвращает первое значение этого поля в группе.
LAST	Возвращает последнее значение этого поля в группе.

Кроме стандартных, в Access допустимы и два дополнительных варианта: "Выражение" и "Условие".

Выбрав вариант "Выражение" в строке "Поле", можно задать конкретное выражение, например Max [(наименование поля)] - Min[(наименование поля)], на соответствие с которым программа должна проводить проверку. В данном случае она покажет максимальную величину разброса некоторого параметра, хранимого в данном поле. Выбор "Условия" активизирует ссылку на строку " Условие отбора", что позволяет в значительной мере расширить число возможных комбинаций вариантов выполнения групповых операций. Так, например, применение режима "Условие" в графе " Калории" позволяет сначала получить из общего массива только конкретные данные, а уже к ним применить всю мощь аппарата запросов Access, причем сделать все это в одном запросе, а не в несколько приемов.

Задание.

1.  Подсчитайте количество продуктов каждого типа. Отсортируйте по количеству продуктов (Таблица Продукты).

2.  Подсчитайте, на какую сумму сделаны заказы продуктов (Таблица ПродуктыЗаказа).

3.  Определите сети магазинов (несколько магазинов с одним названием). В какой сети больше всего магазинов.

4.  В какие магазины заказали одинаковые продукты

Указания. Этот запрос выполняется в два этапа:

·  На первом этапе находим, какие одинаковые продукты были заказаны. Этот запрос выполняется по таблицы ПродуктыЗаказа

Результирующая таблица будет следующая:

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

ПЕРЕКРЕСТНЫЙ ЗАПРОС.

Это специальный тип запроса, в котором можно группировать по строкам и столбца. Для создания этого запроса создадим новую таблицу Сотрудники, содержащую следующие данные:

Фамилия	Месяц	Заработок
Иванов	Январь	350
Петров	Январь	600
Сидоров	Январь	250
Иванов	Январь	400
Иванов	Январь	370
Сидоров	Январь	300
Иванов	Февраль	500
Петров	Февраль	380
Сидоров	Февраль	320
Сидоров	Февраль	500

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

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

Создание перекрестного запроса в режиме мастера.

Выбираем тип нового запроса Перекрестный запрос.

Выполните следующие действия:

1.  Выберите таблицу Сотрудники

2.  Выберите поле Фамилия в качестве заголовков строк в перекрестной таблице

3.  Выберите поле Месяц в качестве заголовков столбцов в перекрестной таблице

4.  Задайте в следующем окне поле Зарплата и тип вычисления Сумма

И готово!

В результате вы получите вот такую перекрестную таблицу.

Открыв таблицу в режиме конструктора, вы увидите следующее:

Внимание! Посмотрите, какие новые строки добавлены в бланк таблицы.

Что означает строка Групповая операция

Перекрестная таблица

Создание перекрестного запроса в режиме конструктора.

Для создания запроса в режиме конструктора задайте имя таблицы Сотрудники, откройте пустой бланк запроса и установите тип запроса Перекрестный через контекстное меню или выбрав из списка на панели инструментов. После этого определите столбцы Фамилия, Месяц, Зарплата и вычисляемое поле Итог: Зарплата.

В строке Групповая операция задайте для первых двух столбцов Группировка, для остальных двух – SUM.

В строке Перекрестная таблица задайте для 1-го столбца – заголовки строк, 2-го столбца – заголовки столбцов, 3-го – значения, 4-го – заголовки строк.

Посмотрите полученный результат.

Задание.

1.  Создайте многотабличный запрос по таблицам Продукты, ПродуктыЗаказа, Заказы, Магазины. Результирующие столбцы – Название продуктов, Название магазинов и Количество продуктов. Сделайте перекрестную таблицу для получения информации о том, какое количество продуктов одного наименования было заказано магазинами.

Лабораторная работа № 11.

ТЕМА: ЗАПРОСЫ В ACCESS(ЗАПРОСЫ ДЕЙСТВИЯ)

При работе с данными часто бывает необходимо автоматизация. Например, в большой базе данных (10 000 записей) необходимо заменить название некоторой улицы, являющейся частью адреса (она была переименована). Вручную, сделать это на 100% корректно практически невозможно. Для автоматизации решения таких проблем подходящим средством является создание специальной формы запросов, в результате выполнения которых СУБД определенным образом модифицирует исходные данные, хранящиеся в базе.

Такие запросы называются запросы-действие, и с их помощью можно выполнить следующие задачи:

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

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

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

- создать новую таблицу на основе исходных таблиц или запросов.

Запрос-действие является весьма удобным, полезным и чрезвычайно необходимым рабочим инструментом любого человека, который тем или иным образом сталкивается с СУБД.

Создание, и тем более выполнение, любого запроса-действия в СУБД Microsoft Access всегда состоит из двух этапов:

·  Сначала нужно создать запрос-выборку для того, чтобы проверить правильно ли отбираются записи, правильно ли вы сформулировали запрос;

·  Затем превратить его в запрос-действие и выполнить еще раз – отменить действие будет не возможно!

Смысл в этом следующий: просмотрев результаты выборки, пользователь (разработчик) может даже чисто визуально убедиться в том, что все пройдет "как задумано" и что потом не придется расстраиваться после неправильно заданного действия. Так как запрос-выборка не модифицирует данные, то в случае ошибки вам понадобится лишь заново переделать сам запрос.

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

Итак, сначала делаем запрос-выборку (например, мы решили не работать с магазинами сети Копеечка) и, просмотрев итог выборки в режиме таблицы и убедившись, что запрос работает правильно (выбирает именно те записи, которые мы собираемся удалить), можно превратить запрос-выборку в запрос на удаление.

После этого нажимаем ! на панели инструментов и готово!

Задание.

1.  Сделайте копию таблицы Продукты – Продукты_копия (копирование выполняется стандартным в Windows способом – Выделить -> Копировать-> Вставить, задав имя).

2.  Удалите из новой таблицы продукты с калорийностью > 500.

3.  Удалите из этой же таблицы продукты, в названиях которых есть Десерт или Смесь. Вспомнить как нужно употребить Like.

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

Если вы хотите выполнить запрос на обновление данных, то эта процедура выполняется так - запрос открываем в режим конструктора и на панели инструментов выбираете инструмент для установления ТИПА ЗАПРОСА, в раскрывающемся списке выбрать ОБНОВЛЕНИЕ.

В нижней части бланка запроса можно увидеть новую строку – эта строка называется "Обновление".

Все, что в ней записано, будет занесено в указанные поля вместо того, что в этих полях хранилось до выполнения запроса-обновления!

Задание.

В таблице Улицы есть несколько улиц, для которых не задан тип. Найдите эти улицы и задайте тип “улица”. Вспомните, что пустые значения можно найти используя выражение IS NULL. В таблице ПродуктыЗаказа для продуктов, которых заказано >=25, обновите поле Скидка и сделайте ее 1%(введите значение - 0,01).

Запросы на добавление.

1. На примере рассмотрим, как в новую таблицу МагазиныСоветскогоРайона с структурой такой же как и Магазины, добавить информацию о магазинах Советского района. Для этого создадим пустую таблицу МагазиныСоветскогоРайона. Скопируйте таблицу Магазины и вставьте с параметром “Только структура”

Далее,

·  Начните создавать запрос в режиме конструктора и укажите таблицу Магазины в качестве основы для создания запроса;

·  Измените тип запроса на ЗАПРОС НА ДОБАВЛЕНИЕ

·  В открывшемся окне укажите, куда будете добавлять записи:

В нижней части бланка конструктора запросов сопоставьте имена полей-источников информации и имена полей-получателей информации. В строке Условия отбора задайте “Советский район” для того, чтобы из основной таблицы были отобраны записи с магазинами только Советского района. После этого выполните запрос и посмотрите на содержимое таблицы МагазиныСоветскогоРайона. Выделите в отдельную таблицу магазины других районов города.

2. Создайте в режиме конструктора таблицу ДиректораМагазинов, содержащую два поля

1. НомерДиректора – счетчик, ключевое

2. Фамилия – текстовый, 50 символов

В эту таблицу добавьте фамилии директоров магазинов из таблицы Магазины.

Запросы на создание новой таблицы.

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

1.  Создать запрос на выборку.

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

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

Номер заказа и Общую цену заказа.

Указания. Найдите соответствующий запрос (по таблице ПродуктыЗаказа).и переделайте его в запрос на создание новой таблицы.

Затем этот запрос преобразовать в запрос на группирование, для поля КодЗаказа сделайте Группировку, а для поля Общая цена – суммирование. Далее запрос преобразуйте в запрос на создание новой таблицы ОплатаПоЗаказам и выполните запрос. Перейдите на вкладку Таблицы и посмотрите новую таблицу.

Лабораторная работа № 12.

ТЕМА: СВОДНЫЕ ТАБЛИЦЫ.

Сводные таблицы и диаграммы предназначены для интерактивного анализа данных различной сложности и объема. Они позволяют провести глубокий и всесторонний анализ данных удобными простыми средствами.

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

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

Рассмотрим создание сводной таблицы.

Создадим запрос ВсяИнформация по таблицам - Заказы, Магазины, ПродуктыЗаказа, Продукты, ТипыПродуктов,, в который включите информацию о следующих полях:

Заказы.КодЗаказа

Магазины.Название

Магазины.РайонГорода

Заказы.ДатаРазмещения

Заказы.ДатаНазначения

Заказы.ДатаИсполнения

Продукты.Название

ТипыПродуктов.Названия

ПродуктыЗаказа.Цена

ПродуктыЗаказа.Количество

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

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

На рисунке ниже показан экран для создания сводной таблицы: