Практическая работа № 22.
Тема: "Формирование запросов для поиска и сортировки информации в базе данных."
Цель работы: изучение приемов организации и использования запросов
Оборудование: ПК, Windows XP Professional, MS Access.
Задание:
1 В соответствие с заданием организовать запросы по отбору данных.
2 Продемонстрировать на компьютере запросы.
3 Ответить на контрольные вопросы.
4 Сделать вывод о проделанной работе.
Теоретические сведения:
Любая СУБД позволяет выполнять четыре простейшие операции с данными:
- добавлять в таблицу одну или несколько записей;
- удалять из таблицы одну или несколько записей;
- обновлять значения некоторых полей в одной или нескольких записях;
- находить одну или несколько записей, удовлетворяющих заданному условию.
Для выполнения этих операций используется механизм запросов. Результатом выполнения запросов является либо отобранное по определенным критериям множество записей, либо изменения в таблицах. Запросы к базе формируются на специально созданном для этого языке, который так и называется язык структурированных запросов (SQL — Structured Query Language).
Порядок выполнения:
1. Откройте созданную Вами ранее базу данных Фирма и проведите ее модификацию. Для этого, откройте таблицу Сотрудники и проведите ее редактирование:
- скопируйте запись с фамилией Орлова на восьмую. Для этого нажмите ПКМ на запись, выберите команду Копировать
Затем нажмите ПКМ на звездочку в восьмой строке и выберите команду Вставить
- во второй или третьей записи (в зависимости от Вашего пола) измените фамилию на свою;
- введите новую запись в режиме Ввод данных, для этого на ленте Главная во вкладке Записи выберите команду Создать (команда Создать будет активна только при выделенной любой строке таблицы!)
Результат:
2. Создайте запрос, в котором можно просмотреть телефоны сотрудников.
Для создания запроса выполните команду: вкладка ленты Создание — Мастер запросов — Простой запрос.
В появившемся диалоговом окне укажите таблицу Сотрудники и выберите поля Фамилия, Имя, Телефон. Нажмите кнопку Далее.
Введите имя запроса - Телефоны - и нажмите кнопку Готово.
Перед вами появится запрос, в котором можно просмотреть телефоны сотрудников. Обратите внимание, что в области перехода появился новый объект Телефоны, иконка которого отличается от иконки таблиц.
2 С помощью Конструктора создайте запрос Адреса клиентов. Для этого выполните команду: вкладка ленты Создание — Конструктор запросов.
6 В диалоговом окне Добавление таблиц выберите таблицу Клиенты и щелкните на кнопке Добавить, а затем - на кнопке Закрыть.
7 Чтобы перенести нужные поля в бланк запроса, необходимо по ним дважды щелкнуть левой кнопкой мыши (ЛКМ) в таблице. Щелкните ЛКМ по полям Код клиента, Название компании, Адрес.
8 Чтобы отсортировать записи в поле Название компании в алфавитном порядке, необходимо в раскрывающемся списке строки Сортировка выбрать пункт по возрастанию.
9 Сохраните запрос с именем Адреса клиентов. Для этого при закрытии запроса в появившемся диалоговом окне сохранения изменения макета выберите команду Да, и в следующем окне введите имя запроса Адреса клиентов.
10 Самостоятельно создайте запрос Дни рождения, в котором можно будет просмотреть дни рождения сотрудников. (для запроса выбрать поля Код сотрудника, Фамилия, Имя, Дата рождения)
11 Если нам нужно узнать, кто из сотрудников родился в конкретном месяце, то придется создать новый запрос или изменить условие в существующем запросе Дни рождения Допустим, мы хотим узнать, у кого из сотрудников день рождения в текущем месяце, например в апреле. Для этого откройте запрос Дни рождения в режиме Конструктора.
12 В строке Условие отбора для поля «Дата рождения» введите значение *.04.*. В данной записи * означают, что дата и год рождения могут быть любыми, а месяц 4-м (т. е. апрель). После сохранения окно запроса в режиме Конструктора должно выглядеть так, как оно представлено на рисунке:
13 Закройте Конструктор и просмотрите полученный результат.
Если в запросе Дни рождения нет ни одной записи, значит, в таблице Сотрудники нет ни одного человека, родившегося в апреле. Добавьте в таблицу Сотрудники несколько человек, родившихся в апреле, и посмотрите, как изменится запрос. Запросы автоматически обновляются при каждом открытии.
Если нам нужно узнать, кто из сотрудников родился в мае, то придется опять создать новый запрос или изменить условие в существующем запросе Дни рождения. Данная процедура является неудобной и занимает много времени. Если приходится часто выполнять запрос, но каждый раз с новыми значениями условий используют запрос с параметром. При запуске такого запроса на экран выводится диалоговое окно для ввода значения в качестве условия отбора.
14. Измените запрос Дни рождения, сделав его запросом с параметром. Чтобы создать запрос с параметром, пользователю необходимо ввести текст сообщения Like[Введите дату] в строке Условие отбора бланка запроса.
Запись Like[Введите дату] означает, что при открытии запроса появится диалоговое окно) с текстом «Введите дату» и полем для ввода условия отбора. Если ввести условие *.04.*, то в запросе появится список сотрудников, родившихся в апреле.
Запустите запрос еще раз и введите значение *.05.*, посмотрите, как изменился запрос.
15 Измените запрос «Телефоны» так, чтобы при его запуске выводилось диалоговое окно с сообщением «Введите фамилию». Поскольку в запросе нужно вывести конкретную фамилию, в условии отбора слово Like писать не надо.
Выполнение запроса:
16 Измените запрос «Телефоны» так, чтобы при его запуске запрашивались не только фамилия, но и имя сотрудника.
При выполнении запроса сначала появится диалоговое окно с сообщением «Введите фамилию», а затем, после ввода Фамилии диалоговое окно с сообщением «Введите имя»
17 Самостоятельно с помощью Конструктора запросов создайте запрос Выполненные заказы, содержащий следующие сведения: фамилия и имя сотрудника, название компании, с которой он работает, отметка о выполнении и сумма заказа. Данные запроса возьмите из нескольких таблиц.
Двойным щелчком ЛКМ выберите нужные поля из таблиц:
18 В условии отбора для логического поля Отметка о выполнении введите Да, чтобы в запросе отображались только выполненные заказы.
19 Сделайте так, чтобы столбец Отметка о выполнении не выводился на экран.
20 Создайте запрос Сумма заказа, в котором будут отображаться заказы на сумму более 50 000 руб.
Для подобных запросов в условии отбора можно использовать операторы сравнения >, <, =, >=, <=, < > и логические операторы And, Or, Not и др.
Результат:
21 Измените запрос, чтобы сумма заказа была от 20 000 до 50 000 руб.
Результат:
22 Создайте запрос для подсчета подоходного налога по каждой сделке.
Иногда в запросах требуется произвести некоторые вычисления, например, посчитать подоходный налог 13 % для каждой сделки.
Для этого откройте запрос Сумма заказа в режиме Конструктора.
В пустом столбце бланка запроса щелкните правой кнопкой мыши на ячейке Поле и в появившемся контекстном меню выберите команду Построить.
Перед вами появится окно Построитель выражений, который состоит из трех областей: поля выражения (1), кнопок операторов(2) и элементов выражения(3).
Сверху располагается поле выражения, в котором оно и создается. Вводимые в это поле элементы выбираются в двух других областях окна Построителя.
23 В левом списке откройте папку Запросы и выделите запрос Сумма заказа. В среднем списке выделите поле Сумма и нажмите кнопку Вставить. Идентификатор этого поля появится в поле выражения Построителя.
24 Щелкните на кнопке * и введите 0,13. Таким образом, мы посчитаем подоходный налог 13 %.
25 Нажмите кнопку ОК, после чего в ячейке свойства Поле появится значение «Выражение1:[Сумма]*0,13».
26 Замените Выражение1 на Налог и закройте Конструктор.
27 Откройте запрос и посмотрите, что у вас получилось.
28 Используя Построитель выражений, измените запрос Сумма заказа, чтобы можно было посчитать прибыль от заказов. Уберите Условие отбора в поле Сумма (чтобы выводились все выполненные заказы) и добавьте поле Прибыль, в котором будет вычисляться доход от заказа (т. е. сумма минус налог).
Для этого откройте запрос Сумма заказа в режиме Конструктора. Удалите Условие отбора в поле Сумма (чтобы выводились все выполненные заказы).
Добавьте поле Прибыль, в котором будет вычисляться доход от заказа (т. е. сумма минус налог). В пустом столбце бланка запроса щелкните правой кнопкой мыши на ячейке Поле и в появившемся контекстном меню выберите команду Построить. В левом списке откройте папку Запросы и выделите запрос Сумма заказа. В среднем списке выделите поле Сумма и нажмите кнопку Вставить. Щелкните на кнопке - .В среднем списке выделите поле Налог и нажмите кнопку Вставить. Нажмите кнопку Ок.
29 Замените Выражение1 на Прибыль и закройте Конструктор, сохранив изменения.
Просмотрите полученный результат:
30 Создайте запрос Менеджеры, с помощью которого в таблице Сотрудники найдите всех менеджеров фирмы.
31 Покажите работу преподавателю.
32 Ответьте на контрольные вопросы.
33 Сделайте вывод о проделанной работе.
Контрольные вопросы:
1 Для чего предназначены запросы?
2 Какие виды запросов вы знаете?
3 С помощью чего можно создавать запросы?
4 Для чего используют запрос с параметром?
5 Как можно сделать вычисления в запросах?
6 Что означает запись в условии запроса « =50»?
7 Можно ли создавать запросы на основе нескольких таблиц?