Практическая работа № 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 на экране дисплея.

Ответьте на вопросы преподавателя.