МЕТОДИЧКА

11

Используйте построитель выражений. Найдите оператор BETWEEN.

Упражнения

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

4.2.Вывести адреса всех домов с лифтами.

4.3.Вывести адреса всех квартир, площадь которых превышает некоторое

значение.

4.4.Выявить все трехкомнатные квартиры в домах без лифтов.

4.5.Вывести данные обо всех квартирах на улице Белова, количество комнат в которых меньше двух.

4.6.Вывести информацию обо всех квартирах, построенных до 1990 г.

Работа № 5

Запросы с обработкой текстовых данных

Для сравнения строки символов с образцом (шаблоном) применяется оператор Like.

Правила формирования шаблона:

символ «?» (знак вопроса) замещает любой один (и только один) символ;

символ «*» (звездочка) замещает любое количество символов, в том числе нулевое количество.

Задание 5.1. Вывести сведения обо всех домах на улице Белова.

Добавить в запрос таблицы УЛИЦЫ, ДОМА

Перенести в строку Поле нижней панели окна Конструктора нужные поля из таблиц УЛИЦЫ, ДОМА.

Для поля Улица задать условие отбора: =”Белова” или просто ”Белова”. Можно Like “Белова”. Испробуйте все 3 варианта.

Задание 5.2. Вывести данные о жителях города с фамилией, начинающейся на «Во», родившихся до 1976 г.

Добавить в запрос таблицу ЛЮДИ.

Перенести в строку Поле нижней панели окна Конструктора нужные поля из таблицы ЛЮДИ.

12

Для поля Фам задать условие отбора Like “Во*” (это условие означает поиск текста, у которого первые два символа «Во»). При вводе условия следить за языком набираемого текста.

Для поля ДатаРожд в строке Условие отбора задать <01.01.1976.

Задание 5.3. Вывести сведения о людях, в имени которых одновременно присутствуют буквы «О» и «г» (именно в таком порядке).

Указание: условие отбора для поля Имя: Like «О*г*».

Позаботьтесь, чтобы в таблице были люди с именами Олег, Ольга, Игорь. Просмотрите, каков будет результат, если после буквы «г» не поставить

звездочку.

Задание 5.4. Вывести сведения о людях, фамилия которых состоит из 6-и букв.

Указание: Условие отбора для поля Фам: Like “??????”.

Упражнения

5.1.Вывести данные обо всех квартирах на улице Белова, количество комнат в которых меньше двух.

5.2.Вывести сведения обо всех людях с фамилией, оканчивающейся буквой «в».

5.3.Вывести сведения обо всех людях, в фамилии которых есть буквы «а»

или «в».

5.4.Вывести сведения обо всех квартирах на улицах Белой и Новой.

5.5.Вывести сведения обо всех домах, находящихся на улицах, в названии которых больше 5-и букв.

5.6.Вывести сведения обо всех людях, в фамилии которых нет буквы

«и».

Работа № 6

Сортировка, группировка данных в запросах

6.1. Сортировка записей в запросах

Сортировка – это упорядочение записей в порядке возрастания или убывания значения поля.

Задание 6.1. Вывести список жителей города в алфавитном порядке (упорядочить только по фамилии).

13

Включить в запрос поля Фам, Имя, Отч из таблицы ЛЮДИ.

Щелкнуть в строке Сортировка поля Фам раскрыть список вариантов сортировки выбрать по возрастанию.

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

Задание 6.2. Вывести список жителей города в алфавитном порядке (упорядочить по фамилии, имени отчеству).

Выполняется так же, как и задание 6.1, только надо еще задать сортировку по полям Имя, Отч.

6.2. Группировка данных

Группировка – это обработка всех значений выбранного поля с помощью одной из агрегатных функций:

-Sum – сумма;

-Max – максимальное значение;

-Min – минимальное значение;

-Avg – среднее значение;

-Count – количество.

Задание 6.3. Вывести сведения о количестве домов на каждой улице города.

Добавить в запрос таблицы УЛИЦЫ, ДОМА.

Включить в запрос поля Улица, Дом.

Задать группировку: выполнить команду меню Вид/Групповые операции (или инструмент Групповые операции). В нижней панели конструктора запроса появится строка Групповая операция.

Для поля Улица в строке Групповая операция оставим параметр

Группировка в строке Групповая операция для поля Дом раскроем список выберем параметр Count (количество).

Исполнить запрос. Обратить внимание на заголовок поля Дом.

Для поля Дом сделать заголовок «Количество домов» (в строке Поле в

столбце Дом записать: Количество домов: Дом.

Задание 6.4. Для каждого дома определить общую площадь квартир.

Добавить в запрос таблицы УЛИЦЫ, ДОМА, КВАРТИРЫ.Включить в запрос поля Улица, Дом, Площ.

Задать группировку Для поля Площ в строке Групповая операция задать Sum (сумма).

14

Для поля Площ сделать заголовок Общая площадь.

Исполнить запрос.

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

Упражнения

6.1.Вывести сведения о количестве квартир в каждом доме.

6.2.Вывести сведения о количестве квартир на каждой улице. Указание: в запрос включить таблицы УЛИЦЫ, ДОМА, КВАРТИРЫ, но для обработки выбрать только поля Улица и Кв, чтобы группировка произвелась

для улиц. Если включить поле Дом, то группировка произведется по домам, как это было в упр. 6.1.

6.3.Определить общее количество домов в городе.

6.4.Определить общее количество квартир в городе.

6.5.Определить общее количество женщин в городе.

6.6.Определить среднюю площадь одной квартиры города.

Работа № 7

Параметрические запросы (запросы с параметром)

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

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

Задание 7.1. Вывести сведения о жителях города с фамилией, задаваемой в качестве параметра запроса.

Включить в запрос все поля таблицы ЛЮДИ.

В строке Условие отбора в поле Фам в квадратных скобках поместить текст, выводимый в качестве приглашения (пусть это будет [Введите фамилию]).

15

Исполнить запрос. Обратить внимание на текст приглашения в диалоговом окне. Задать интересующую нас фамилию.

Задание 7.2. Выявить жителей города, о которых известна лишь часть фамилии.

Включить в запрос таблицу ЛЮДИ.

В строке Условие отбора в поле Фам в квадратных скобках поместить

Like [Задайте параметры поиска фамилии].

Исполнить запрос. Параметр запроса в нашем случае оформляется по правилам, существующим для оператора Like (звездочки, знаки вопроса и т.п.). Например, “Ив*” – отбор людей, фамилия которых начинается на «Ив».

Задание 7.3. Выявить жителей города, о которых известны лишь часть фамилии и примерный возраст.

Включить в запрос таблицу ЛЮДИ.

Оформить запрос согласно данным рисунка 7.1.

Рисунок 7.1

Исполнить запрос. В качестве параметра-фамилии можно задать, например, Ив* или Во*, если известны лишь первые две буквы. Если в качестве параметра дата рождения задать 01.01.1980, то будут выведены сведения о людях, родившихся до этой даты (обратите внимание на знак «<» перед квадратными скобками).

Упражнения

7.1.Вывести сведения обо всех домах, находящихся на улицах, фрагмент названия которых вводится в качестве параметра. При исполнении запроса опробовать варианты: «известна первая буква», «известна последняя буква», «известно количество букв, например, 5», «известно, что в названии есть некоторая комбинация букв, например «ова».

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

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

7.4.Создать запрос, выводящий сведения о домах, в качестве параметров

вкотором выступают дата постройки и общая площадь всех квартир дома.

16

Предусмотреть сортировку по дате постройки. Опробовать разные варианты значений параметров.

Указание: для нахождения общей площади квартир использовать группировку.

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

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

Работа № 8

Вычисления в запросах

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

Задание 8.1. Определить количество краски, необходимое для окраски полов каждого дома, если на 1 кв. м расходуется 0,3 кг.

В запросе создадим вычисляемое поле с именем Необходимо краски, кг.

Добавить в запрос таблицы УЛИЦЫ, ДОМА, КВАРТИРЫ.

Включить в запрос поля Улица, Дом, Кв, Площ.

В свободной клетке строки Поле задать вычисляемое поле: Необходимо краски, кг:[площ]*0,3. Еще раз заметим, что задаваемое имя поля от вычисляемого выражения отделяется двоеточием. Имена полей, участвующих в запросе в качестве операндов, желательно заключать в квадратные скобки.

Установить флажок вывода поля на экран.

Использование функций в выражениях

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

17

Задание 8.2. Определить, из скольких букв состоит фамилия каждого жителя города.

Используем функцию Len(), аргументом которой является строка символов, а результатом – количество символов в этой строке.

Включить в запрос таблицу ЛЮДИ вставить поле Фам.

Добавить вычисляемое поле Количество букв: Len(Фам). При формировании вычисляемого поля попробуйте найти функцию Len в списке функций Построителя выражений.

Задание 8.3. Определить возраст (полное количество лет) каждого жителя города.

Здесь нам придется использовать 2 функции:

-Date() – возвращает текущую дату в формате день.месяц.год;

-Year(дата в формате день.месяц.год) – возвращает значение года.

Добавить в запрос таблицу ЛЮДИ.

Включить в запрос поля Фам, Имя, ДатаРожд, Пол.

Добавить вычисляемое поле Возраст: Year(Date()) - Year(ДатаРожд). Year(Date()) дает нам текущий год, а Year(ДатаРожд) – год рождения.

Исполнить запрос. Если внимательно изучим результаты, то увидим, что неполный год засчитывается как полный, т.е. не учитываются месяцы. И не удивительно, ведь в расчетах участвуют только годы.

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

Добавить в запрос таблицы УЛИЦЫ, ДОМА.

Включить в запрос поля Улица, Дом.

Добавить вычисляемое поле четность: iif(Дом Mod 2 =0; “четный”; “”). Здесь iif – функция с тремя аргументами:

iif (усл; выр1; выр2),

где усл – условие (логическое выражение);

– выр1, выр2 – выражения.

Если логическое выражение усл имеет значение ИСТИНА, то результат функции iif равен выр1, в противном случае выр2.

Операция Mod дает остаток от деления. В нашем случае если остаток от деления номера дома на 2 равен нулю (Дом – число четное), то поле четность получит значение «четное», в противном случае оно получит значение «пустая строка» (внутри пары кавычек нет никакого текста).

18

Упражнения

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

8.2.Для каждой квартиры города определить необходимое количество лампочек для освещения (по 3 лампочки в каждой комнате, по одной лампочке

вкухне, прихожей, санузле).

8.3.Вывести сведения обо всех людях, фамилия которых состоит более чем из 6-и букв.

8.4.Выявить все дома без лифтов, которым более 10 лет.

Работа № 9

Создание запросов с использованием нескольких таблиц

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

Таблица ПРОПИСКА будет содержать 3 поля:

-КодПроп (код прописки) – счетчик, ключевое поле;

-КодЧел (код человека) – числовой – длинное целое, значение по умолчанию – пусто;

-КодКв (код квартиры) – числовой – длинное целое, значение по умолчанию – пусто;

Для поля КодЧел свойство Индексированное поле задать равным ДА

(совпадения не допускаются), а для поля КодКв – ДА (совпадения допускаются).

Задание 9.2. Связать таблицу ПРОПИСКА с таблицами ЛЮДИ и КВАРТИРЫ.

В схему данных включить таблицы ЛЮДИ (если ее там нет) и ПРОПИСКА:

Установить связь между таблицами ЛЮДИ и ПРОПИСКА по полю

КодЧел.

Установить связь между таблицами КВАРТИРЫ и ПРОПИСКА по полю КодКв.

19

Схема связей должна иметь вид (рисунок 9.1).

Рисунок 9.1

Задание 9.3. Ввести данные в таблицу ПРОПИСКА (прописать жителей города). Предусмотреть прописку в одной квартире как одного человека, так и нескольких человек.

Для удобства ввода данных в таблицу ПРОПИСКА можно порекомендовать следующее:

Открыть одновременно таблицы ЛЮДИ, КВАРТИРЫ, ПРОПИСКА.

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

Вводить данные в таблицу ПРОПИСКА, используя информацию из таблиц ЛЮДИ, КВАРТИРЫ.

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

Здесь необходимо добавить в запрос таблицы УЛИЦЫ, ДОМА, КВАРТИРЫ, ЛЮДИ и связующую их таблицу ПРОПИСКА и включить в запрос нужные поля из этих таблиц.

Задание 9.5. Вывести сведения о количестве жителей, прописанных в каждой квартире города.

Добавить в запрос таблицы УЛИЦЫ, ДОМА, КВАРТИРЫ, ПРОПИСКА.

Включить в запрос поля Улица, Дом, Кв, КодЧел.

Для полей Улица, Дом, Кв установить групповую операцию Группировка, а для поля КодЧел – Count (количество).

20

Задание 9.6. Вывести сведения о количестве жильцов каждого дома.

Добавить в запрос таблицы УЛИЦЫ, ДОМА, КВАРТИРЫ, ПРОПИСКА.

Включить в запрос поля Улица, Дом, КодЧел.

Для полей Улица, Дом, установить групповую операцию Группировка, а для поля КодЧел – Count (количество).

Задание 9.7. Вычислить среднюю площадь, приходящуюся на одного жильца каждой квартиры города.

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

Добавить в запрос таблицы УЛИЦЫ, ДОМА, КВАРТИРЫ, ПРОПИСКА.

Включить в запрос поля Улица, Дом, Кв, Площ, КодЧел.

Полю КодЧел присвоить псевдоним КолЛюд (для использования в вычислениях).

Добавить вычисляемое поле

средняя площадь: [Площ]/[КолЛюд]

Для полей Улица, Дом, Кв, Площ установить групповую операцию Группировка, для поля КолЛюд – Count (количество), для поля средняя площадь – групповую операцию Выражение.

Упражнения

9.1.Вывести все данные, включая и адрес, о жителях города с фамилией Иванов (или другой).

9.2.Вывести все данные, включая и адрес, о мужчинах, родившихся до

1980 года.

9.3.Вычислить среднюю площадь, приходящуюся на одного жильца каждого дома.

9.4.Вычислить среднюю площадь, приходящуюся на одного жителя

города.

9.5.Вычислить, сколько комнат в среднем приходится на одного жителя

города.

9.6.Создать параметрический запрос, который позволит лишь по части фамилии определить адрес человека.

9.7.Создать параметрический запрос, который позволит по части фамилии и примерной дате рождения определить адрес человека.