Лабораторная работа 3.
Работа с данными при помощи запросов.
Поиск данных по условию.
Задание 1. Создать запрос к таблицам базы данных Автомагазин, который отберет данные об автомобилях, произведенных не ранее 1997 г. и поставленных фирмой АвтоВАЗ.
- Загрузите программу Access и откройте базу данных Автомагазин. Выбрав вкладку Запросы, щелкните кнопку «Создание запроса» в режиме конструктора.
- В окне Добавление таблицы, выделяя таблицы Поставщики, а затем - Автомобили и щелкая кнопку «Добавить», добавьте обе таблицы базы данных Автомагазин. Щелкнув кнопку «Закрыть», закройте окно Добавление таблицы.
- Перетаскивая поля из таблиц Автомобили и Поставщики в бланк запроса, определите поля таблиц для запроса, порядок их размещения. В строке Вывод на экран включите флаг отображения полей. В строке Условие отбора в столбце Фирма задайте условие отбора «АвтоВАЗ »,а в столбце Год выпуска задайте условие отбора > 1997, как показано на рис. 4.
Рис. 4. Сформированный запрос в режиме конструктора
- Перейдем в режим таблицы и просмотрим записи базы данных, отобранные согласно созданному запросу. Закроем окно запроса, сохранив макет запроса под именем Запрос АвтоВАЗ не старше 1997 г.
Задание 2. Создать запрос с параметром Поиск автомобилей по марке.
- Для создания запроса с параметром, выбрав вкладку Запросы, щелкните кнопку «Создание запроса в режиме конструктора». В окне Добавление таблицы, выделяя таблицы Поставщики, а затем - Автомобили и щелкая кнопку «Добавить», добавьте обе таблицы базы данных Автомагазин. Щелкнув кнопку «Закрыть», закройте окно Добавление таблицы. Перетаскивая поля из таблиц Автомобили и Поставщики в бланк запроса, определите поля таблиц для запроса, порядок их размещения. В строке Вывод на экран включите флаг отображения полей. В строке Условие отбора в столбце поля Фирма, которое предполагается использовать как параметр, введите в ячейку строки Условие отбора текст приглашения [Введите марку автомобиля]. Сохраните запрос, задав ему имя Запрос нужной марки авто.
- Для проверки действия запроса выберите в меню Вид опцию Режим таблицы. В окне Введите значение параметра введите значение искомой марки автомобиля, например, АвтоВАЗ.
- Закройте окно таблицы-запроса и завершите работу программы MS Access.
Задание 3. Поиск данных по условию
Поиск данных по условию можно выполнить различными способами.
Способ первый. Вы создаете запрос по каждому из вопросов, которые предлагаются, и сохраняете его в виде таблицы. Для создания запроса выполните следующие действия: Запрос — Создать — Новый запрос — Таблица/ запрос — Класс — Добавить. — Закрыть. После этого ввести условия запроса.
Способ второй. Таблица — Класс — Открыть. На панели инструментов нажмите кнопку Изменить фильтр, и с помощью построителя выражений введите условия запроса. Затем на панели инструментов Запроса нажмите кнопку Применить фильтр. В результате на экране появится динамический набор записей, удовлетворяющих введенному условию. Но при дальнейших манипуляциях этот набор не сохранится. Если вы хотите сохранить данный набор записей, то выполните следующие действия. В окне Фильтра откройте меню Файл и выберите команду Сохранить как запрос.
Способ третий. В режиме таблиц выведите курсор мыши за пределы таблицы и нажмите правую мыши. В контекстном меню выберите команду Изменить фильтр. Все остальные действия точно такие же, как во втором способе.
Для того чтобы ответить на все предлагаемые ниже вопросы, необходимо знать операторы задания шаблонов, примеры которых показаны в следующей таблице.
Примеры использования символов задания шаблонов
| Использование символов | Образец | Поиск в Microsoft Access |
| Вопросительный знак (?); в качестве шаблона для любого символа. | За?ор | Забор Затор |
| Звездочка (*); в качестве шаблона для любой группы символов. | Д*нь | День Добрый день Длинная тень |
| Звездочка (*); в качестве шаблона для любой группы начальных символов. | *й | 128й Последний |
| Знак фунта (#); в качестве шаблона для любой цифры. | #-й | 5-й 8-й |
| Восклицательный знак (!) после первой скобки; для поиска символа, который не входит в указанный набор символов. | Иванов[!аы] | Иванову [но не Иванова или Ивановы] |
Примеры условных выражений
| Условные выражения | Вывод записей, которые |
| Саратов | имеют значение Саратов |
| Not Саратов | не имеют значение Саратов |
| In (Саратов, Томск, Уфа) | имеют значение Саратов, Томск или Уфа |
| <М | начинаются с букв А-Л |
| >=М | начинаются с букв М-Я |
| имеют числовое значение 100 | |
| <=20 | имеют числовое значение 20 |
| Date() | имеют значение текущей даты |
| >=01.01.94 | имеют значение даты позднее 01.01.94 |
| Beetween 01.01.93 AND 31.12.93 | имеют значение года 1993 |
| *.02.* | имеют значение месяца Февраль |
| Null | содержат в поле пустое значение |
| Is Not Null | имеют не пустое значение в поле |
| Like "Р*" | начинаются с буквы Р |
- Откройте новую БД и создайте в ней таблицу «Класс» и внесите в нее следующие поля.
| Поле | Тип данных | Описание |
| № | Счетчик | |
| Фамилия | Текстовый | |
| Имя | Текстовый | |
| Дата | Дата | Дата рождения |
| Пол (м) | Логический | Пол мужской? |
| Улица | Текстовый | |
| Дом | Числовой | |
| Квартира | Числовой | |
| Класс | Числовой | |
| Группа | Текстовый | Группа здоровья по физкультуре |
| Хобби | Текстовый | |
| Глаза | Текстовый | Цвет глаз |
- Заполните таблицу.
| № | Фамилия | Имя | Дата | пол | Улица | Дом | Квартира | Класс | Группа | Хобби | Глаза |
| Суханов | Сергей | 16.02.81 | М | Чердынская | основная | тяжелая атлетика | зеленые | ||||
| Пирогов | Юрий | 5.12.83 | М | Куйбышева | основная | футбол | голубые | ||||
| Лебедева | Светлана | 16.06.85 | Ж | Пушкина | специальная | вязание | карие | ||||
| Голдобин | Сергей | 23.05.88 | М | Леонова | основная | лыжи | голубые | ||||
| Ельшина | Наталья | 24.05.82 | Ж | Чердынская | специальная | чтение | серые | ||||
| Суханова | Наталия | 20.12.86 | Ж | Ленина | подготовительная | шитье | зеленые | ||||
| Петрова | Света | 18.04.82 | Ж | Пушкина | основная | лыжи | серые | ||||
| Горина | Ольга | 20.12.84 | Ж | Свиязева | подготовительная | аэробика | карие | ||||
| Попов | Михаил | 7.07.87 | М | Леонова | подготовительная | голубые | |||||
| Сергеев | Александр | 30.11.89 | М | Куйбышева | основная | каратэ | зеленые | ||||
| Павлова | Елена | 13.12.85 | Ж | Пушкина | основная | аэробика | карие | ||||
| Емельянова | Наталья | 25.05.81 | Ж | Попова | основная | шитье | зеленые | ||||
| Евдокимов | Михаил | 18.08.80 | М | Чердынская | основная | футбол | зеленые | ||||
| Евсеева | Елена | 14.10.84 | Ж | Ленина | основная | лыжи | серые | ||||
| Суханова | Светлана | 29.07.80 | Ж | Куйбышева | основная | аэробика | карие |
- Ответьте на вопросы
1) Кто учится в 8 классе?
2) Кто родился в 1984 году?
3) Кто живет на улице Пушкина?
4) У кого номер дома меньше 50?
5) У кого мужские имена?
6) Кто родился весной?
7) У кого фамилия начинается на букву С?
8) Кто является одиннадцатиклассником?
9) Кто не занимается тяжелой атлетикой?
10) У кого сегодня день рождения?
11) У кого номер квартиры меньше 12?
12) Кто не определил свое хобби?
13) Кто родился до 01.01.85?
14) Кто живет на улице Пушкина, Леонова или Свиязева?
15) У кого фамилия начинается с букв А-К?
16) Кто является однофамильцами с Сухановым Сергеем?
17) Кто увлекается аэробикой и лыжами?
18) Кто из мальчиков живет на улице Чердынской?
19) У кого имена начинаются на букву С, а фамилия — на букву П?
20) У кого карие глаза и этот человек не умеет вязать?
21) Кто учится в старших классах?
22) У кого из специальной группы женское имя?
23) Кто не живет на улице Ленина и не занимается в подготовительной группе?
24) Кто из мальчиков с голубыми глазами занимается каратэ?
25) У кого номер класса кратен 3, и он живет на улице Пушкина или Ленина?
26) Кто с серыми глазами учится в классе с нечетным номером, или число в дате его рождения больше 15?
27) У кого номер класса простое число, или специальная группа, и его имя начинается на Н?
28) Кто из основной группы не живет на улице Пушкина или Ленина?
29) У кого из девочек, родившихся в мае или в феврале, номер дома больше 36?
30) У кого из основной группы имя начинается на буквы С или М, но фамилия не начинается на букву П?
Лабораторная работа 4.
Работа со связанными таблицами
Задание Работа со связанными таблицами с использованием подстановки.
- Создадим базу данных, которая будет хранить результаты экзаменов студентов одной группы за один семестр. Можно обойтись и одной таблицей, в которую включить поля «Фамилия», «Имя», «Дисциплина», «Преподаватель», «Оценка». Но в таком случае многие данные будут повторяться, т.к. каждый студент сдает несколько экзаменов, и каждый экзамен сдают многие студенты.
- Поэтому создадим три таблицы — «Студенты», «Предметы», «Оценки» и заполним их. В первых двух таблицах первое поле имеет целый тип, а остальные — текстовый тип. В третьей таблице поля имеют числовой тип.
Таблица «Предметы»
| Код предмета | Название | Преподаватель |
| Программирование | Половина | |
| Психология | Сивак | |
| Статистика | Полыгалова | |
| Информационные системы | Клигман |
Таблица «Студенты»
| Код студента | Фамилия | Имя |
| Антонова | Марина | |
| Бирих | Яков | |
| Волков | Константин | |
| Волошина | Светлана | |
| Кашина | Наталья | |
| Ленц | Алексей | |
| Матвеев | Роман | |
| Наумова | Татьяна | |
| Юдинцева | Оксана |
Таблица «Оценки»
| Код оценки | Код студента | Код предмета | Оценка |
- Щелкните на пиктограмму «Схема данных». Вы увидите созданные нами таблицы и связи, которые создал Access. Он автоматически связал поля с одинаковыми именами и совместимыми типами. В других случаях это можно сделать вручную, с помощью мыши — достаточно перетащить одно поле на другое и получить между ними связь.
- Обратите внимание, что выставлять оценки неудобно. Чтобы выставить К. Волкову «отлично» по численным методам, надо помнить коды студента и предмета. К счастью, в Access можно оперировать с кодами, а информацию выдавать в текстовой форме. Сделаем это для предметов.
- Выберите в таблице «Оценки» поле «Код предмета» и укажите тип «мастер подстановок». В качестве таблицы источника данных выберите «Предметы» и поля «Код» и «Название». Теперь в режиме таблицы можно выбирать названия из списка.
- Сделайте то же самое для студентов. А теперь сделаем так, чтобы подстановка выполнялась из двух полей, то есть выводила фамилию и имя. Для этого выберите поле «Код студента», щелкните на вкладке «Подстановка», выберите «Источник строк» и щелкните кнопку с многоточием справа. Откроется построитель запросов, работа с которым не отличается от работы с обычным запросом. Вместо поля «Фамилия» введите строку Выражение1: [Фамилия] & " " & [Имя]. Теперь при подстановке видна и фамилия студента и его имя.
- Добавьте еще двух-трех студентов в таблицу «Студенты». Перейдите в таблицу «Оценки». Обратите внимание, что добавленные студенты оказались в конце списка. Чтобы упорядочить список по алфавиту, вызовите построитель запросов для поля «Код студента» и установите сортировку по возрастанию. Сделайте то же самое для поля «Код предмета».
- Поле «Код оценки» нам (в отличие от Access) неинтересно. Выделите этот столбец в режиме таблицы и выберите команду Формат/ширина столбца. Установите ее в ноль. Этого же результата можно добиться «перетягиванием» правой границы столбца к левой.
- Обратите внимание, что Access позволяет хранить несколько записей о сдаче одним и тем же студентом одного и того же предмета. Сделаем так, чтобы набор студент-предмет стал уникальным. Для этого в режиме конструктора установим для этих полей опцию «ключевое поле». Это можно сделать через пункт меню Правка или через панель инструментов.
- Изменим подписи к полям «Код студента» и «Код предмета». Для этого выберите строку «Подпись» на вкладке «Общие». Теперь таблица «Оценки» выглядит так:
Таблица «Оценки»
| Студент | Предмет | Оценка |
| Антонова Марина | Информационные системы | |
| Бирих Яков | Информационные системы | |
| Волков Константин | Информационные системы | |
| Волошина Светлана | Информационные системы | |
| Антонова Марина | Психология | |
| Бирих Яков | Психология | |
| Волков Константин | Психология | |
| Волошина Светлана | Психология |
Задания
- Измените ввод в поле Оценка, чтобы вместо цифр выставлялось «отлично», «хорошо» и т. д. Для этого создайте отдельную таблицу и используйте мастер подстановок. При создании таблицы учтите, что существует четыре варианта оценки: «1 — отлично», «2 — хорошо», «3 — удовлетворительно», «4 — неудовлетворительно». Вызовите схему данных и установите связь.
- Заполните таблицу «Оценки».
- Рассчитайте средний балл каждого студента. Для этого поместите в конструктор запросов таблицы «Студенты» и «Оценки». Выберите поле «Студент», а вместо «Оценка» введите «6-Оценка». Это необходимо, т.к. отличной оценке соответствует 1, хорошей — 2 и т.д. Найдите на панели инструментов кнопку «Групповые операции» и нажмите ее. Примените групповую операцию «среднее» — Avg. Access автоматически присвоило построенному выражению имя «Выражение-1». Измените его на «Средний балл».
- Выведите список студентов, сдавших экзамены на оценки "хорошо" и "отлично". Для этого проделайте те же операции, что и в предыдущем пункте, но в качестве групповой операции выберите Min — нахождение минимума — и задайте условие на отбор >3. Снимите флажок вывода столбца минимальной оценки на экран.
- Сохраните результаты работы в файле students.mdb.
Лабораторная работа 5.