Практическая работа № 4

Цель работы:

• Освоить создание на языке SQL следующих видов запросов

  • На выборку информации.

  • Корректирующих запросов

• Освоить создание специальных запросов

  • Поиск записей, не имеющих подчиненных

  • Поиск повторяющихся записей

  • Создание перекрестных запросов

Продолжительность работы: 2 часа.

Задание

В данной работе база данных, созданная в предыдущих работах, используется для выполнения следующих заданий:

1. Определить на какой кафедре и в какой должности работает определенный сотрудник.

2. Вывести список зав. кафедрами.

3. Получить списочный состав сотрудников, работающих на заданной кафедре.

4. Получить список сотрудников данной должности.

5. Вывести список сотрудников заданного возраста.

6. Определить количество сотрудников, работающих на каждой кафедре.

7. Определить среднюю зарплату сотрудников по каждой кафедре.

8. Увеличить оклады сотрудников в должности лаборант на 30% (запрос на обновление)

9. Удалить из списка сотрудников тех сотрудников, которые являются стажерами (запрос на удаление)

10. Определить на каких кафедрах нет стажеров (поиск записей, не имеющих подчиненных)

11. На каких кафедрах имеются несколько стажеров (поиск повторяющихся записей)

12. Подсчитать для каждой кафедры кол-во сотрудников по должностям (перекрестной запрос).

Порядок выполнения работы

Задание 1. Определить на какой кафедре и в какой должности работает определенный сотрудник.

Порядок работы:

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите режим Конструктор, щелкните по кнопке ОК.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса ВЫБОРКА

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор SELECT;

• Введите между словом SELECT и знаком ; следующее предложение

SELECT Кафедры.Наименование_кафедры_полное, Сотрудник.ФИО, должность.[название должности]

FROM Кафедры INNER JOIN (должность INNER JOIN Сотрудник ON должность.Код_должности = Сотрудник.Должность) ON Кафедры.Код_кафедры = Сотрудник.Код_кафедры

WHERE (Сотрудник.ФИО=[Задайте фамилию сотрудника]);

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Сотрудник.

• Щелкните по кнопке для представления запроса.

• Введите фамилию Попов. Получите запрос на Попова.

• Закройте запрос, предварительно сохранив его.

Задание 2. Вывести список зав. кафедрами.

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите режим Конструктор, щелкните по кнопке ОК.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса ВЫБОРКА

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор SELECT;

• Введите между словом SELECT и знаком ; следующее предложение

SELECT Кафедры.Наименование_кафедры_полное, Сотрудник.ФИО, должность.[название должности]

FROM Кафедры INNER JOIN (должность INNER JOIN Сотрудник ON должность.Код_должности = Сотрудник.Должность) ON Кафедры.Код_кафедры = Сотрудник.Код_кафедры

WHERE (((должность.[название должности])="Зав.кафедрой"));

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Зав.кафедрами.

• Щелкните по кнопке для представления запроса.

• Закройте запрос, предварительно сохранив его.

Задание 3. Получить списочный состав сотрудников, работающих на заданной кафедре.

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните по кнопке Создать.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса ВЫБОРКА

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор SELECT;

• Введите между словом SELECT и знаком ; следующее предложение

SELECT Кафедры.Наименование_кафедры_полное, Сотрудник.ФИО, должность.[название должности]

FROM Кафедры INNER JOIN (должность INNER JOIN Сотрудник ON должность.Код_должности = Сотрудник.Должность) ON Кафедры.Код_кафедры = Сотрудник.Код_кафедры

WHERE ((кафедры.[наименование_кафедры_краткое])=[Краткое название кафедры]);

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Список кафедры.

• Щелкните по кнопке для представления запроса.

• Введите код интересующей вас кафедры. Получите запрос на сотрудников кафедры.

• Закройте запрос, предварительно сохранив его.

Задание 4. Получить список сотрудников в данной должности.

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите режим Конструктор, щелкните по кнопке ОК.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса ВЫБОРКА

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор SELECT;

• Введите между словом SELECT и знаком ; следующее предложение

SELECT Кафедры.Наименование_кафедры_полное, Сотрудник.ФИО, должность.[название должности]

FROM Кафедры INNER JOIN (должность INNER JOIN Сотрудник ON должность.Код_должности = Сотрудник.Должность) ON Кафедры.Код_кафедры = Сотрудник.Код_кафедры

WHERE ((должность.[название должности])=[Укажите название должности]);

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Раздел.

• Щелкните по кнопке для представления запроса.

• Введите название должности. Получите список сотрудников, работающих в данной должности.

• Закройте запрос, предварительно сохранив его.

Задание 5. Создайте запрос на выборку сотрудников заданного возраста.

Порядок работы:

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите режим Конструктор, щелкните по кнопке ОК.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса ВЫБОРКА

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор SELECT;

• Введите между словом SELECT и знаком ; следующее предложение

SELECT Кафедры.Наименование_кафедры_полное, Сотрудник.ФИО, должность.[название должности]

FROM Кафедры INNER JOIN (должность INNER JOIN Сотрудник ON должность.Код_должности = Сотрудник.Должность) ON Кафедры.Код_кафедры = Сотрудник.Код_кафедры

WHERE ((Year(Date())-Year([Дата_рождения]))=[укажите возраст]);

• В поле запроса с датой рождения записано для определения возраста следующее выражение: Возраст: YEAR(DATE())-YEAR[дата_рождения].

• В Условие отбора поместите [Укажите возраст].

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Возраст.

• Щелкните по кнопке для представления запроса и введите возраст.

• Закройте запрос, предварительно сохранив его.

Задание 6. Определить количество сотрудников, работающих на каждой кафедре.

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите режим Конструктор, щелкните по кнопке ОК.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса ВЫБОРКА

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор SELECT;

• Введите между словом SELECT и знаком ; следующее предложение

SELECT Кафедры.Наименование_кафедры_полное, Count(Сотрудник.ФИО) AS [Кол-во сотрудников]

FROM Кафедры LEFT JOIN Сотрудник ON Кафедры.Код_кафедры = Сотрудник.Код_кафедры

GROUP BY Кафедры.Наименование_кафедры_полное;

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Групповой запрос.

• Щелкните по кнопке для представления запроса.

• Закройте запрос, предварительно сохранив его.

Задание 7. Определить среднюю зарплату сотрудников каждой кафедры.

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите режим Конструктор, щелкните по кнопке ОК.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса ВЫБОРКА

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор SELECT;

• Введите между словом SELECT и знаком ; следующее предложение

SELECT Кафедры.Наименование_кафедры_полное, AVG(Сотрудник.Оклад) AS [Средняя зарплата]

FROM Кафедры LEFT JOIN Сотрудник ON Кафедры.Код_кафедры = Сотрудник.Код_кафедры

GROUP BY Кафедры.Наименование_кафедры_полное;

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Групповой запрос.

• Щелкните по кнопке для представления запроса.

• Закройте запрос, предварительно сохранив его.

Задание 8. Увеличить оклады сотрудников в должности лаборант на 30% (запрос на обновление)

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите режим Конструктор, щелкните по кнопке ОК.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса ОБНОВЛЕНИЕ

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор UPDATE;

• Введите между словом UPDATE и знаком ; следующее предложение

UPDATE Сотрудник SET Сотрудник.Оклад = Сотрудник.Оклад*1.3

WHERE Сотрудник.должность=5;

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Обновление.

• Щелкните по кнопке для представления запроса.

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

• Закройте запрос, предварительно сохранив его.

Задание 9. Удалить из списка сотрудников тех сотрудников, которые являются стажерами (запрос на удаление)

• Выберите закладку Запрос, если находитесь в другом окне.

• Щелкните мышкой по кнопке Создать.

• В появившемся диалоговом окне выберите режим Конструктор, щелкните по кнопке ОК.

• Щелкните по кнопке Закрыть.

• Выберите пункт меню ЗАПРОС и в появившемся всплывающем меню выберите тип запроса УДАЛЕНИЕ

• Затем щелкните по пункту меню ВИД и в появившемся меню выберите режим SQL

• В появившемся окне запрос на выборку вы увидите оператор DELETE;

• Введите между словом DELETE и знаком ; следующее предложение

DELETE FROM Сотрудник WHERE Сотрудник.должность=9;

• Щелкните по кнопке Сохранить. Сохраните запрос с именем Обновление.

• Щелкните по кнопке для представления запроса.

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

• Закройте запрос, предварительно сохранив его.

Задание 10. Определить на каких кафедрах не имеется стажеров (поиск записей, не имеющих подчиненных)

Для выполнения данного запроса сначала следует создать дополнительный запрос на выборку из таблицы Сотрудник стажеров, создав следующий запрос на языке SQL

SELECT *

FROM Сотрудник

WHERE Сотрудник.Должность=[Задайте код стажера];

и сохранив его под именем st.

Затем для определения кафедр, на которых нет стажеров, создадим с помощью Мастера запросов запрос на Записи без подчиненных, действуя в следующей последовательности:

• Выбрать в окне базы данных вкладку Запрос

• Щелкнуть по кнопке Создать

• В появившемся диалоговом окне Новый запрос выберите Записи без подчиненных и щелкнув по кнопке ОК

• Затем надо выбрать основную таблицу в паре «основная-подчиненная». В нашем случае это будет таблица Кафедры. Основная и подчиненная таблицы должны быть предварительно связаны в схеме данных. После чего нажимает кнопку Далее.

• Далее выбирается подчиненная таблица. Мы выбираем запрос - это будет предварительно созданный запрос st. Щелчок по клавише Далее.

• Определяем поля, по которым связаны выбранные таблицы. В нашем случае в обеих таблицах выбираем поле Код_кафедры. Затем щелкаем по кнопке Далее.

• Для отображения результатов запроса в следующем окне выберем поле Название кафедры_полное и щелкнем по кнопке Далее.

• В следующем окне зададим имя запроса и выберем действие Просмотреть результаты запроса. Щелкнем по кнопке Готово.

Задание 11. На каких кафедрах имеется несколько стажеров (поиск повторяющихся записей)

Для создания подобного запроса воспользуемся созданным в задании 10 вспомогательным запросом ST. После чего выполним следующую последовательность действий:

• Выбрать в окне базы данных вкладку Запрос

• Щелкнуть по кнопке Создать

• В появившемся диалоговом окне Новый запрос выберите Повторяющиеся записи и щелкните по кнопке ОК

• Запускается Мастер Поиск повторяющихся записей. На первом этапе выберем запрос ST и нажмем Далее

• На втором шаге выбираем поля с повторяющимися значениями. В нашем запросе это будут поля Код_кафедры и Код_должности. Затем жмем Далее

• На третьем шаге ничего не выбираем, а просто нажимаем кнопку Далее.

• Задаем имя запроса и выбираем действие Просмотр результатов запроса. Нажимаем кнопку Готово.

• Просматриваем результаты запроса и записываем затем текст запроса на SQL

Задание 12. Подсчитать для каждой кафедры кол-во сотрудников по должностям (перекрестной запрос).

• Выбрать в окне базы данных вкладку Запрос

• Щелкнуть по кнопке Создать

• В появившемся диалоговом окне выберите Перекрестный запрос, щелкнув по кнопке ОК

• В окне Создание перекрестных запросов выделите таблицу Сотрудник и щелкните по кнопке Далее

• Выберите поле, значения которого будут использоваться в качестве заголовок строк, - Код-кафедры. Щелчок по кнопке Далее

• Выберите поле, значения которого будут использоваться в качестве заголовка столбцов, например, Должность. Щелкните по кнопке Далее.

• Выберите функцию, по которой будут вычисляться значения. Это будет в данном случае Число. Щелкните по кнопке Далее.

• Задайте имя запроса и щелкните по кнопке Готово.

• Просмотрите результаты запроса и, перейдя в режим SQL, перепишите созданный запрос на SQL. Обратите внимание на соединение между таблицами и объясните его смысл.

Самостоятельное задание для защиты лабораторной работы.

Составить следующие запросы на выборку на языке SQL:

1. На какой кафедре работает больше 10 сотрудников.

2. На какой кафедре работает меньше трех сотрудников

3. Получить список значений окладов в данной организации. Списк расположить в порядку убывания значений окладов.

4. У кого самая большая зарплата на кафедре Х. Запрос реализовать как параметрический.

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

6. Какая кафедра является самой многочисленной?

7. Какова численность мужчин и женщин в данной организации?

8. Определить две самые крупные по численности кафедры.

9. Сколько сотрудников работают в организации более 10 лет?

10. Получить список сотрудников, получающих минимальную в организации зарплату?

Задание 11. Завершите работу с Access.

Порядок работы:

• Выполните команду Файл - Выход.

• Если вы производили редактирование в базе данных, появится вопрос о сохранении изменений. Ответьте на него утвердительно.

Контрольные вопросы

1. Какие существуют виды запросов?

2. Что может служить источником данных для запросов?

3. Как задаются условия отбора записей в запросах?

4. Особенности выполнения запросов на связанных таблицах

5. Что такое вычисляемые поля и как они вводятся в запрос?

6. Как упорядочить данные в ответе?

7. Что такое параметрический запрос?

8. Какие разновидности корректирующих запросов вы знаете?

9. Что такое группировка в запросе?

10. Какие типы объединения таблиц вы знаете?

11. В чем смысл поиска записей, не имеющих подчиненных?

12. Как и для чего создаются перекрестные запросы?