Практическая работа № 10. БД «Школа2»: Создание и использование запросов
Цели работы: научиться создавать запросы на выборку различными способами; научиться создавать запросы: на обновление, на добавление, на удаление, на создание таблицы; научиться создавать перекрестные запросы.
Пошаговое выполнение работы:
1. Загрузитe Microsoft Access.
2. Откройте базу данных Школа2.accdb.
3. Если Вы включили в параметры запуска Заставку,то она появится перед вами. Закройте ее. На экране возникнет диалоговое окно с основными элементами базы данных. В базе данных должны быть три таблицы: Список,Личные данные,Группы.
4. Создайте запрос на выборку (таблицы Личные данные и Список) с именем таблицы Номера телефонов.
5. Выберите поля Фамилия,Имя и Отчествоиз таблицы Списоки Номер Телефона– из таблицы Личные данные(рис. 1).
Рис. 1.
6. Создайте телефонную книгу для всех учащихся, фамилии которых начинаются на букву «В». Перейдите в режим Конструктора и в столбце Фамилия задайте условие отбора Like «Б*». Щелкните по кнопке ! для представления запроса. Наберите новое имя запроса: Выборка по В. Теперь в меню базы данных в окне Запросы будет показано два запроса.
7. Составьте запрос на адреса только девочек, имя которых «Анна». Сохраните запрос с именем Анна.
8. Составьте запрос на телефоны учащихся, отчество которых начинается на букву «А». Сохраните запрос с именем Выборка по А.
9. Составьте ведомость для выплаты стипендии всем учащимся, которые учатся без троек. Для этого нужно выбрать записи, в которых оценки по предметам 4 или 5.
10. Создайте запрос на выборку (таблицы Личные данные и Список) с именем таблицы Успеваемость.
11. Выберите поля Фамилия,Имя и Отчествоиз таблицы Списоки поля Word, Excel, Access – из таблицы Личные данные.
12. В строке Условие отборапод полями Word,ExcelиAccessпоставьте 4 Or 5 (рис. 2).
Рис. 2.
13. Составьте запрос на учеников групп 101, у которых оценка по курсу «Освоение базы Access» 4 или 5; сохраните запрос с именем Успеваемость1.
14. Составьте запрос на учеников групп 102 и 103, которые имеют оценку по курсу «Освоение программы Word» и «Освоение программы Excel» 4 или 5; сохраните запрос с именем Успеваемость2.
15. Составьте запрос на выборку всех записей, кроме тех, которых указана фамилия Баранова с использованием окна Построителя выражений. Выделите запрос Номера телефонов. Откройте в режиме Конструктора. Удалите поле Номер Телефона. Добавьте поле Адрес. Сохраните запрос с именем Адрес.
16. Поставьте курсор в ячейку Условие отбора в столбце Фамилия. Удалите запись в этой ячейке и щелкните по кнопке Построить. Появится окно, в котором можно строить сложные запросы. Выберите Операторы / Логические / Not. Наберите вручную «Баранова» и щелкните по кнопке . В поле Условие отборапоявится данное выражение. Щелкните по кнопке ! для представления запроса. Закройте запрос, сохранив его с именем не Баранова.
17. Создайте вычисляемое поля Среднее по оценкам Word и Excel.
18. Создадим запрос Среднее по таблицам Список(поля Фамилия и Имя)иЛичные данные(поля Word и Excel).
19. Откройте запрос в режиме Конструктора. Поставьте курсор на клетку правее Excel(на линии Поле). Щелкните по кнопке Построить.В появившемся окне напечатайте вручную выражение: Среднее: ([Word]+[Excel])/2, и щелкните по кнопке .Это выражение подставится в новое поле. Сохраните запрос с именем Среднее.Щелкните по кнопке ! для представления запроса. Новое поле будет иметь имя Среднее.Закройте запрос.
20. Предположим, что школа № 3 стала лицеем, а школа № 5 - гимназией. Нужно исправить значения полей в таблице Список:заменить номер школы 3 на слово Лицей,а номер школы 5 – на слово Гимназия. Это невозможно будет сделать при существующей структуре данных, так как поле Школаобъявлено числовым, а выполняется попытка заменить его на слово. Необходимо предварительно сменить тип поля Школана текстовый. Для этого: откройте таблицу Списокв Конструкторе;исправьте тип поля Школана текстовый;Сохраните таблицу и закройте ее.
21. Постройте запрос на обновление. Создадим запрос в режиме Конструктора.Добавьте таблицу Список.Выберите поле Школаиз таблицы Список,щелкнув по нему мышкой два раза. Выберите команду Обновление (рис. 3).Введите условие замены: в поле Школазаменить все цифры 3 на слово Лицей.В поле Обновление поставьте – Лицей, а в поле Условие отбора цифру 3. Щелкните по кнопке ! для выполнения запроса. Подтвердите обновление записей. Если Вы сейчас перейдете в режим таблицы,то, естественно, не увидите ни одной записи, так как цифры 3 заменились на слово Лицей,а условие – на выбор школы 3 не дает ни одной записи. Вернитесь в режим Конструктора, удалите в строке Условие отборацифру 3, посмотрите в режиме Таблицырезультат замены. Закройте запрос, сохранив его с именем Лицей.
Рис. 3.
22. Откройте таблицу Список. Просмотрите результат применения запроса.
23. Создайте запрос Гимназия, меняющий значения поля школа 5 на слово Гимназия (по аналогии с запросом Лицей).
24. Прошел год, и ученики перешли в следующий класс, некоторые из них закончили школу, пришли новые. Нужно обновить базу данных.
25. Создайте новую таблицу с названием Новая группаи текстовыми полями Фамилия,Имя,Отчествои числовым полем Учебная группас полем подстановок из таблицы Группы, на вопрос о создании ключевого поля ответьтеНет.Заполните значениями таблицу: Сидорова Анна Ивановна,Петрова Инна Сергеевна,Сергеева Ирина Петровнаи Куликова Ольга Дмитриевна, учатся в группе номер 101.Закройте таблицу, предварительно сохранив ее.
26. Теперь записи из таблицы Новая группанужно добавить в таблицу Список. Откройте схему данных, и проверьте, чтобы связь таблиц Список-Личные данные была один-к-одному, Если это не так, то дважды щелкнете по изменяемой связи и в окне Изменение связей удалите условия Каскадное удаление связанных полейиКаскадное обновление связанных полей.Закройте окно Схема данных.
27. Постройте запрос на добавление. Создадим запрос в режиме Конструктора.Добавьте таблицу Новая группа.Выберите те поля, которые нужно добавить (Фамилия, Имя, Отчество, Учебная группа)согласно рис. 4 (Если у какого-либо поля поставить условие отбора, то добавятся только записи, удовлетворяющие этому условию). Выберите команду Добавление (рис. 3). В появившемся диалоговом окне выберите имя таблицы Список,в которую будут добавляться данные. Эта таблица может быть из текущей базы данных или из любой другой. В данном случае у нас одна база данных, поэтому щелкните по кнопке .Щелкните по кнопке ! выполнения запроса. Подтвердите выполнение запроса. Закройте запрос, сохранив его с именем Добавление.
28.
Рис. 4.
29. Откройте таблицу Список и убедитесь, что данные записи были добавлены.
30. Нужно удалить записи тех учеников, которые закончили школу (в данном случае тех, у которых класс был 11). Предварительно разорвите связь Список-Личные данные.
31. Постройте запрос на удаление. Создайте запрос в режиме Конструктора.Добавьте таблицу Список.Тип запроса– ´ ! Удаление(рис. 3)Выберите поле Класс из таблицы Список,щелкнув по нему мышкой два раза. Введите Условие отбора - 11 (рис. 5).
Рис. 5.
32. Для предварительного просмотра удаляемых записей перейдите в режим Таблица. Перейдите в режим Конструктора и щелкните по кнопке ! для выполнения запроса. Появится сообщение, что процесс удаления будет необратим. Подтвердите удаление записей. Закройте запрос, сохранив его с именем Удаление. Откройте таблицу Список и убедитесь, что данные записи были удалены.
33. Создайте запросы на обновление с именами 10_класс, 9_класс,которые меняют класс на единицу больше: 10 на 11; 9 на 10). Заполните недостающие данные для добавленных записей, указав для них класс 9.
34. Бывают ситуации, когда из старых таблиц нужно составить новые с другим набором полей. Создайте таблицу успеваемости для учеников разных групп, поместив поля Фамилия,Имя,Отчествоиз таблицы Списоки поля Word,Excel и Access – из таблицы Личные данные.
35. Снова создайте связь Список-Личные данные.
36. Создайте запрос в режиме Конструктора.Добавьте таблицы СписокиЛичные данные. Тип запроса– Создание таблицы.Задайтеимя таблицы Успеваемость3. Выберите поля Фамилия,Имя,Отчествои Учебная группаиз таблицы Списоки поля Word,Excelи Access- из таблицы Личные данные. Щелкните по кнопке ! для выполнения запроса. Подтвердите выполнение запроса. Закройте запрос, сохранив его с именем НоваяТаблица.
37. Откройте таблицу Успеваемостьи убедитесь, что записи были добавлены. Причем добавлено только 10 записей, так как в таблицу Личные данныедополнительные записи не вошли.
38. Необходимо подсчитать для экзаменационной ведомости, сколько в группе, занимающейся изучением программы Word, получено «троек», «четверок» и «пятерок». Для подобных целей используется перекрестный запрос.
39. Создайте перекрестный запрос. В окне Создание перекрестных запросоввыделите таблицу Успеваемость3 и щелкните по кнопке .Выберите поле, значения которого будут использоваться в качестве заголовков строк – Учебная группа.Щелкните по кнопке .Выберите поле, значения которого будут использоваться в качестве заголовков столбцов, например Word.Щелкните по кнопке .Выберите функцию, по которой будут вычисляться значения ячеек на пересечении столбцов и строк (в данном случае Числопо полю – Фамилия). Здесь вы можете добавить итоговое поле для каждой строки (в данном случае это поле покажет общее количество оценок по каждой группе). Щелкните по кнопке .Задайте имя запроса Word.Щелкните по кнопке .
40. Составьте аналогичные запросы для оценок, полученных группой по изучению Excelи Access.
Предъявите преподавателю:
запросы 9_класс,10_класс,Access, Excel,Word,Адрес, Анна,Выборка по А,Выборка по В,Гимназия,Добавление,Лицей,Удаление,НоваяТаблица,Номера телефонов,не Баранова,Среднее,Успеваемость1,Успеваемость2иУспеваемость3на экране дисплея.
Ответьте на вопросы преподавателя.