Лабораторная работа 4.2. Создание запросов

Лабораторная работа 4.2. Создание запросов

Запрос - это объект, позволяющий пользователю получить нужные данные из одной или нескольких таблиц. Для создания запроса Вы можете использовать бланк QBE (Запрос по образцу) или написать инструкцию SQL. Можно создавать запросы на выборку, обновление, удаление или добавление данных. С помощью запросов можно также создавать новые таблицы, используя данные из одной или нескольких существующих таблиц.

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

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

Выполнение:

1.1 В окне базы данных Учебная перейдите на вкладку Запросы. Дважды щелкните на значке Создание запроса в режиме конструктора. В появившемся окне Добавление таблицы выделите на вкладке Таблицы таблицу Сотрудники и щелкните на кнопке Добавить, а затем Закрыть. На экране появится окно конструктора запросов с загруженной таблицей Сотрудники (таблицы отображаются в верхней части окна конструктора запросов).

R Добавить в запрос новую таблицу можно и после перехода в режим конструктора запросов: для этого достаточно выполнить команду меню Запрос / Отобразить таблицу… или нажать кнопку .

1.2 Определите поля запроса: дважды щелкните мышью по полю ФИО в окне таблицы Сотрудники, и оно появится в первом столбце бланка запроса - таблицы в нижней части окна конструктора запросов (или зацепите мышью поле ФИО и перетащите его на первый столбец бланка запроса). Аналогичными действиями поместите в следующие столбцы бланка запроса поля Табномер, Отдел, Должность, Начислено.

1.3 Задайте условие отбора: в строку Условие отбора для поля ФИО введите строку: П* (после нажатия Enter введенный текст автоматически преобразуется в следующий: Like "П*"). Запрос в режиме конструктора примет вид (рис. 4.12).

R Символ «*» используется в шаблонах (образцах) строк для обозначения любого количества любых символов; операция Like обеспечивает сравнение строки с шаблоном. В нашем случае это сравнение будет истинным в том случае, если поле ФИО начинается с буквы «П» (оставшаяся часть фамилии может быть произвольной). Например, шаблон «*ск*» позволяет отобрать только те строки, в которых содержится текст «ск», а с помощью комбинации операции отрицания Not и операции сравнения (Not Like "*ск*") можно отобрать строки, не содержащие данного текста.

R В шаблонах строк можно также использовать специальный символ «?», обозначающий ровно один произвольный символ, и символ « # », обозначающий ровно одну цифру.

Рис. 4.12. Запрос в режиме конструктора для задания 1

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

R Для возврата в режим конструктора запросов можно нажать кнопку  или выполнить команду Вид / Конструктор.

1.5 Закройте окно созданного запроса. В результате появится сообщение Сохранить изменения макета или структуры объекта «запроса ‘Запрос1’»? Выберите Да. В окне Сохранение нажмите ОК. Таким образом, вы сохранили данный запрос под предлагаемым именем Запрос1.

2 Перечислите менеджеров второго или третьего отделов.

Указания:

2.1 Каждый запрос создается заново. Поэтому снова дважды щелкните на значке Создание запроса в режиме конструктора.

2.2 В данном запросе необходимо задать составное условие отбора: в строку Условие отбора для поля Должность введите слово: менеджер (кавычки будут поставлены автоматически), для поля Отдел введите условие: 2 Or 3 (что означает «второй или третий» отдел) (рис. 4.13).

Рис. 4.13. Запрос в режиме конструктора для задания 2

3 Выдайте сведения об инженерах третьего отдела или тех сотрудниках, у кого зарплата больше 5000 р.

Указания:

• Для поля Начислено частное условие отбора (>5000) поместите в строке Или (расположенной ниже строки Условие отбора), так как условия отбора в данном запросе задаются для нескольких полей (рис. 4.14).

Рис. 4.14. Запрос в режиме конструктора для задания 3

4 Представить фамилии тех сотрудников, которым начислено менее 2000 р. и тех, которым начислено более 7000 р.

Указания:

• Есть два способа задать несколько частных условий для одного поля, связанных оператором Or. Можно ввести все условия в одну ячейку строки Условие отбора, соединив их оператором Or (рис. 4.15). Другой вариант: ввести каждое частное условие в отдельную ячейку строки Или (рис. 4.16).

Рис. 4.15. Запрос в режиме конструктора для задания 4

 с вводом условий в одну строку

Рис. 4.16. Запрос в режиме конструктора для задания 4

 с вводом частного условия в строку Или

5 Перечислить всех сотрудников первого отдела.

6 Определите должности сотрудников, фамилии которых начинаются с буквы «О» или «Л» и зарплата (поле Начислено) которых не превышает 4500 р.

7 Выдайте фамилии сотрудников первого или второго отделов, должность которых начинается с «Инж», а также сотрудников третьего отдела, зарплата которых превышает 2000 р.

8 Определите, в каких отделах работают сотрудники, зарплата которых лежит в диапазоне от 6000 до 9000 р.

Указания:

• В строке Условие отбора для соответствующего поля введите: >=6000 And <=9000 или используйте предикат Between, определяющий диапазон значений: Between 6000 And 9000.

9 Выдайте фамилии и табельный номер сотрудников отдела, начальником которого является Сидоров.

Указания:

• В данном запросе будут участвовать обе таблицы - Сотрудники и Отделы.

10 Выдайте должности отдела, телефон начальника которого 277-88-99.

11 Создайте вычисляемое поле К выдаче, в котором будет вычисляться сумма, выдаваемая сотруднику с учетом 13 % подоходного налога к Начислено.

Выполнение:

11.1 Определение вычисляемого поля в запросе: перейдите на второй пустой столбец бланка запроса (в первый столбец внесите поле ФИО таблицы «Сотрудники»), введите в строку Поле текст: К выдаче:Начислено*(1-0,13), нажмите Enter. После нажатия Enter текст будет преобразован в следующий: К выдаче: [Начислено]*(1-0,13), т.е. имена всех полей будут заключены в квадратные скобки (эти скобки можно было сразу указывать при написании текста) (рис. 4.17).

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

Рис. 4.17. Запрос в режиме конструктора для задания 11

11.2 Настройка свойств вычисляемого поля: оставаясь в столбце К выдаче, выполните команду Вид / Свойства или нажмите кнопку ; в появившемся окне Свойства поля на вкладке Общие определите свойство Формат поля как Денежный, после чего закройте окно свойств.

11.3 Выполните созданный запрос и сохраните его.

12 Создайте вычисляемое поле Премия с учетом 30 %-ной премии.

13 Создайте вычисляемое поле БезНадбавки с учетом 7 %-ного штрафа.

14 Укажите для каждого отдела среднюю зарплату и количество сотрудников в нем.

Выполнение:

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

Поместите в бланк запроса поля, участвующие в данном запросе: Отдел, Начислено, ФИО из таблицы Сотрудники.

На панели инструментов нажмите кнопку Групповые операции  или войдите в меню Вид / Групповые операции. В результате в бланке запроса появится строка Групповая операция, и для всех полей в данной строке будет установлен вариант Группировка.

В поле Начислено замените с помощью выпадающего списка вариант Группировка на Avg, а в поле ФИО - на Count.

R Смысл данных настроек следующий: так как для поля Отдел установлена группировка, то при выполнении запроса все записи с одинаковым значением поля Отдел будут «сгруппированы» в результирующей таблице в одну строку. Значение Avg, указанное в поле Начислено, означает, что в данном поле будет вычисляться среднее значение всех записей для каждого отдела; значение  Count, указанное в поле ФИО, означает, что в данном поле будет подсчитываться количество записей каждого отдела.

14.2 Измените названия полей Начислено и ФИО в бланке запроса на Средняя зарплата и Количество работников. Для этого во втором столбце бланка запроса перед словом Начислено наберите Средняя зарплата и поставьте двоеточие, нажмите Enter. Аналогичные действия выполните для третьего столбца (рис. 4.18).

Рис. 4.18. Запрос в режиме конструктора для задания 14

14.3 Поставьте курсор во второй столбец бланка запроса и измените свойства поля, задав Формат поля - Денежный.

14.4 Запустите на выполнение созданный запрос.

15 Посчитайте среднюю зарплату для каждой должности.

16 Укажите, сколько человек работает в отделе, начальником которого является Петров.

17 Посчитайте среднюю зарплату в отделе, начальник которого - Иванов.

18 Укажите максимальную зарплату в каждом отделе.