Лабораторная работа №2. Создание запросов

Цель работы

1. Ознакомиться с процессом создания запросов в режиме конструктора.

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

Задачи

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

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

3. Просмотр SQL-кода запроса.

Методические указания

Чаще всего запросы предназначены для выполнения всевозможных выборок информации из базы данных с использованием различных критериев отбора данных, хотя это и не единственное их назначение. Запросы можно создавать с помощью мастеров, с помощью конструктора, а также путем ручного ввода кода SQL.

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

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

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

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

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

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

Кроме запроса на выборку имеются еще следующие типы запросов:

1. Запрос с группировкой данных. Позволяет сгруппировать данные на основе значений одного или нескольких полей и для каждой группы подсчитать итоговые данные (сумму, среднее, минимум, максимум и др.). Для создания запроса этого типа нужно после открытия окна конструктора нажать на панели инструментов кнопку Групповые операции ( ∑ ). При этом в бланк запроса добавится строка «Групповая операция», в которой для каждого включенного в запрос поля нужно либо оставить значение «Группировка», либо выбрать (из выпадающего списка) нужную функцию для подсчета итоговых данных по этому полю.

2. Перекрестный запрос – это особый тип запроса с группировкой данных. При этом одно или более полей определяют своими значениями перечень строк запроса. Одно поле определяет своими значениями перечень столбцов запроса. Еще одно поле используется для подсчета итоговых данных, которые будут располагаться на местах пересечения строк и столбцов. После выбора этого типа запросов (с помощью кнопки Тип запроса на панели инструментов) в бланк запроса добавляются строки «Групповая операция» и «Перекрестная таблица». Первая из них настраивается так же, как и в запросе с группировкой данных, а вторая – задает назначение рассмотренных выше полей в перекрестном запросе (возможные значения: «Заголовки строк», «Заголовки столбцов», «Значение»).

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

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

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

6. Запрос на удаление. Предназначен для удаления данных из таблиц. После выбора этого типа запросов в бланке запроса вместо строк «Сортировка» и «Вывод на экран» появляется строка «Удаление». В бланк запроса необходимо включить только поля, используемые для задания условий отбора. Если нужно удалить все записи из таблицы, то бланк запроса будет пустым (сама таблица будет содержаться в верхней части окна конструктора). После нажатия кнопки Запуск появится сообщение о том, сколько записей будет удалено.

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

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

Как уже говорилось, условие отбора данных представляет собой ограничение, налагаемое на значения поля, входящего в состав запроса. С точки зрения математической логики это предикат, то есть выражение логического типа, которое может принимать одно из двух значений: True (Истина) или False (Ложь). Те записи, для которых значение предиката есть истина, будут включены в результирующее множество строк.

Например:

Фамилия = " Иванов"

СуммаИзноса > 100000

ДатаВыбытия = " 05.18.2007"

Предикаты обычно включают в себя имена полей, константы, операторы сравнения (=, < >, <, < =, >, > =) и логические связки NOT, AND, OR (соответственно НЕ, И, ИЛИ), которые перечислены в порядке убывания их старшинства. В свою очередь операторы сравнения имеют более высокий приоритет, чем логические связки (что часто позволяет обходиться без использования круглых скобок).

Например:

Фамилия = " Иванов" OR Фамилия = " Петров"

СуммаИзноса > = 100000 AND СуммаИзноса < = 200000

Необходимо отметить, что имя поля можно в предикате не указывать, если только этот предикат принадлежит столбцу, связанному с этим же самым полем. Поэтому если предыдущие два предиката связаны с полями «Фамилия» и «СуммаИзноса» соответственно, то их можно записать компактнее:

" Иванов" OR " Петров"

> = 100000 AND < = 200000

Предикаты могут включать в себя операторы IN и BETWEEN. Первый из них задает множество значений, а второй – интервал значений, которые и определяют критерии отбора данных. Оператор BETWEEN всегда используется со связкой AND, разделяющей граничные значения интервала. Поэтому рассмотренные выше предикаты можно переписать так:

Фамилия IN (" Иванов"; " Петров" )

СуммаИзноса BETWEEN 100000 AND 200000

или в сокращенной форме:

IN (" Иванов"; " Петров" )

BETWEEN 100000 AND 200000

Для текстовых полей в предикатах может использоваться оператор LIKE, который задает строку символов в качестве критерия отбора данных. Эта строка может включать в себя метасимволы «*» и «? ». Первый из них обозначает любое число любых символов, а второй – любой один символ. Например:

Фамилия LIKE " А*"

Фамилия LIKE " Я?? о*ий"

Фамилия LIKE " И*ов" OR Фамилия LIKE " П*ов"

или в сокращенной форме:

" А*"

" Я?? о*ий"

" И*ов" OR " П*ов"

(обнаружив метасимволы, Access сам подставит оператор LIKE).

Зарезервированное слово NULL, которое означает, что значение поля не известно, используется в предикатах всегда со связкой IS. Например:

ДатаВыбытия IS NULL

СуммаИзноса IS NOT NULL

или в сокращенной форме:

NULL

NOT NULL

(Access сам подставит связку IS).

Необходимо отметить, что все предикаты, которые размещены в бланке запроса в одной и той же строке, Access будет соединять между собой связками AND. Если же предикаты размещены и в строках, расположенных ниже, то сначала будут выполнены все построчные соединения связками AND, и лишь затем полученные выражения будут соединены между собой связками OR. В итоге будет сформировано результирующее условие отбора данных.

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

1) открыть окно конструктора, не добавляя в него ни одной таблицы;

2) выбрать в выпадающем списке кнопки Вид строку «Режим SQL»;

3) в появившемся окне ввести текст SQL-команды;

4) сохранить запрос.

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

Варианты заданий

1. Создайте запрос с именем « Запрос на выборку 1 » со следующими свойствами.

Тип запроса: запрос на выборку.

Перечень полей в запросе: ГруппаОС, ИнвентарныйНомер, НаименованиеОС, Фамилия, Имя, Отчество, НормаАмортизации, УчитыватьПробег, ПервоначСтоимость, СуммаИзноса.

Сортировка: ГруппаОС (по убыванию), НаименованиеОС (по возрастанию).

Условие отбора данных: все записи, для которых значение поля «НормаАмортизации» менее 0, 5 или значение поля «ПервоначСтоимость» лежит в диапазоне от 20 000 000 до 100 000 000 и значение поля «СуммаИзноса» превышает 200 000.

2. Создайте запрос с именем « Запрос на выборку 2 » со следующими свойствами.

Тип запроса: запрос на выборку.

Перечень полей в запросе: ИнвентарныйНомер, НаименованиеОС, КодПодразделения, Подразделение, КодМатОтв, Фамилия, ПервоначСтоимость, СодержаниеЗолота, СодержаниеСеребра, СодержаниеПлатины, СодержПлатГруппы.

Сортировка: СодержаниеЗолота (по убыванию), СодержаниеСеребра (по убыванию).

Условие отбора данных: все записи, для которых поле «СодержаниеЗолота» содержит ненулевое значение или значение поля «СодержаниеСеребра» больше 3 и при этом хотя бы в одном из полей «СодержаниеПлатины», «СодержПлатГруппы» содержится ненулевое значение.

3. Создайте запрос с именем « Запрос на выборку 3 » со следующими свойствами.

Тип запроса: запрос на выборку.

Перечень полей в запросе: КодВидаОС, ВидОС, ПодвидОС, ИнвентарныйНомер, НаименованиеОС, КодПодразделения, Подразделение, ПервоначСтоимость, КодНормыАморт, НормаАмортизации, СуммаИзноса.

Сортировка: КодНормыАморт (по возрастанию), ПервоначСтоимость (по убыванию).

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

4. Создайте запрос с именем « Запрос параметрический 1 » со следующими свойствами.

Тип запроса: запрос на выборку.

Перечень полей в запросе: ПодвидОС, ИнвентарныйНомер, НаименованиеОС, КодПодразделения, Подразделение, ПервоначСтоимость, НормаАмортизации, СуммаИзноса.

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

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

5. Создайте запрос с именем « Запрос параметрический 2 » со следующими свойствами.

Тип запроса: запрос на выборку.

Перечень полей в запросе: КодПодразделения, Подразделение, КодМатОтв, Фамилия, Имя, Отчество, ИнвентарныйНомер, НаименованиеОС, РежимИспользования, ПервоначСтоимость, СуммаИзноса.

Сортировка: Фамилия (по возрастанию), НаименованиеОС (по возрастанию).

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

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

Тип запроса: запрос на выборку.

Перечень полей в запросе: ИнвентарныйНомер, НаименованиеОС, КодПодразделения, Подразделение, КодМатОтв, Фамилия, Имя, Отчество, ФИО.

Здесь поле «ФИО» является вычисляемым. Оно составляется из полей «Фамилия», «Имя» и «Отчество», разделяемых пробелами (для соединения в одном выражении текстовых полей и констант используйте оператор «& »).

Сортировка: ФИО (по возрастанию).

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

7. Создайте запрос с именем « Запрос с числовым вычисляемым полем » со следующими свойствами.

Тип запроса: запрос на выборку.

Перечень полей в запросе: КодВидаОС, ВидОС, ИнвентарныйНомер, НаименованиеОС, КодПодразделения, КодНормыАморт, НормаАмортизации, ПервоначСтоимость, СуммаИзноса, ОстаточнаяСтоимость, СрокАмортизации. Здесь поля «ОстаточнаяСтоимость» и «СрокАмортизации» являются вычисляемыми. Первое из них должно отображать разность между первоначальной стоимостью и суммой износа, а второе основывается на формуле:

СрокАмортизации: Int((ПервоначСтоимость – СуммаИзноса) / (ПервоначСтоимость * НормаАмортизации / 100 / 12))

Сортировка: НормаАмортизации (по возрастанию), ОстаточнаяСтоимость (по убыванию).

8. Создайте запрос с именем « Запрос с группировкой » со следующими свойствами.

Тип запроса: групповая операция.

Перечень полей в запросе: ГруппаОС, ВидОС, ИнвентарныйНомер, ПервоначСтоимость, СуммаИзноса.

Групповая операция: для полей «ГруппаОС» и «ВидОС» – Группировка; для поля «ИнвентарныйНомер» – Count; для полей «ПервоначСтоимость», «СуммаИзноса» – Sum.

Условие отбора данных: инвентарные карточки всех тех основных средств, которые содержат хотя бы один из драгметаллов (это условие отбора требует отдельного столбца в бланке запроса и создается по аналогии с созданием вычисляемого поля). Групповая операция – Условие (Where – для англоязычной версии).

Сортировка: СуммаИзноса (по убыванию).

9. Создайте запрос с именем « Запрос перекрестный » со следующими свойствами.

Тип запроса: перекрестный запрос.

Перечень полей в запросе: ГруппаОС, ВидОС, РежимИспользования, СуммаИзноса.

Групповая операция: для полей «ГруппаОС», «ВидОС», «РежимИспользования» – Группировка; для поля «СуммаИзноса» – Sum.

Перекрестная таблица: для полей «ГруппаОС» и «ВидОС» – Заголовки строк; для поля «РежимИспользования» – Заголовки столбцов; для поля «СуммаИзноса» – Значение.

Сортировка: ГруппаОС (по убыванию), ВидОС (по возрастанию).

Условие отбора данных: инвентарные карточки всех тех основных средств, которые поступили на предприятие за последние 5 лет (это условие отбора требует отдельного столбца в бланке запроса и создается по аналогии с созданием вычисляемого поля). Групповая операция – Условие (Where – для англоязычной версии).

10. Создайте запрос с именем « Запрос на обновление » со следующими свойствами.

Тип запроса: запрос на обновление.

Обновление: изменить значение поля «СуммаИзноса» в таблице «Инвентарные карточки ОС» на значение, вычисляемое по следующей формуле:

СуммаИзноса + ПервоначСтоимость * НормаАмортизации / 100 / 12

Условие отбора данных: все записи, для которых значение поля «УчитыватьПробег» принимает значение False и значение поля «ОстаточнаяСтоимость» больше нуля.

Здесь поле «ОстаточнаяСтоимость» является вычисляемым и должно отображать разность между первоначальной стоимостью и суммой износа.

11. Создайте запрос с именем « Запрос на обновление параметрический » со следующими свойствами.

Тип запроса: запрос на обновление.

СуммаИзноса + ПервоначСтоимость * НормаАмортизации / 100 * [Укажите месячный пробег в км] / 1000

Условие отбора данных: все записи, для которых значение поля «УчитыватьПробег» принимает значение True и значение поля «ОстаточнаяСтоимость» больше нуля.

12. Создайте запрос с именем « Запрос на создание таблицы » со следующими свойствами.

Тип запроса: запрос на создание таблицы.

Перечень полей в запросе: ВидОС, ИнвентарныйНомер, НаименованиеОС, Подразделение, ВидПодразделения, СодержаниеЗолота, СодержаниеСеребра, СодержаниеПлатины, СодержПлатГруппы.

Сортировка: СодержаниеПлатины (по убыванию), СодержПлатГруппы (по убыванию), СодержаниеЗолота (по убыванию), СодержаниеСеребра (по убыванию).

Условие отбора данных: инвентарные карточки всех тех основных средств, которые содержат хотя бы один из драгметаллов.

13. Создайте запрос с именем « Запрос на удаление » со следующими свойствами.

Тип запроса: запрос на удаление.

Условие отбора данных: все записи из таблицы «Инвентарные карточки ОС», для которых значение поля «КодНормыАморт» начинается комбинацией цифр 101 или 20 и значение поля «СуммаИзноса» больше значения, введенного пользователем с клавиатуры.

14. Создайте запрос с именем « Запрос на поиск записей без подчиненных » со следующими свойствами.

Тип запроса: записи без подчиненных.

Родительская таблица: Нормы амортизации.

Дочерняя таблица: Инвентарные карточки.

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

Сортировка: НормаАмортизации (по убыванию).

Условие отбора данных: все записи, для которых значение поля «КодВидаОС» равно 9 и значение поля «НормаАмортизации» попадает в интервал от 10 до 20.

15. Создайте запрос с именем « Запрос с усложненной выборкой » со следующими свойствами.

Тип запроса: запрос на выборку.

Перечень полей в запросе: ВидОС, ИнвентарныйНомер, НаименованиеОС, КодПодразделения, Подразделение, ПервоначСтоимость, СуммаИзноса, Год, Месяц, Пробег_км.

Сортировка: Год (по убыванию), Месяц (по убыванию), НаименованиеОС (по возрастанию).

Условие отбора данных: все записи, для которых значение поля «Пробег_км» лежит в диапазоне от 1 000 до 10 000 и которые удовлетворяют условию:

ПервоначСтоимость - СуммаИзноса > ПервоначСтоимость

* НормаАмортизации / 2

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

⇐ Предыдущая 123 4 5 Следующая ⇒