SQL-разработчик - Часть 1

Andrew.Kaluba

Рекомендованные книги для параллельного чтения с курсом:

Алан Бьюли — Изучаем SQL (o’reilly)
Джеймс Р. Грофф — SQL. Полное руководство
К. Дж. Дейт — SQL и реляционная теория

1.5 Домашняя работа

Задача Цель задания

Научиться делать первые шаги в Oracle Apex

Что нужно сделать

Отделу кадров жизненно необходим удобный инструмент для ведения списка сотрудников. Для работы с данными был выбран Oracle Apex и приложение HR, которое и создавалось для этих целей. Вам поставлены следующие задачи:

Создайте страницу с отображением данных (Interactive Grid) сотрудников (таблица Employees). Страница должна иметь ссылку на родительскую страницу Home для удобства навигации.
Продемонстрируйте её работоспособность после запуска, для этого:

скройте все колонки, заканчивающиеся на ID;
выберите всех сотрудников с зарплатой свыше 12 000;
отсортируйте сотрудников по фамилии (Last_name);
верните все скрытые колонки и уберите фильтр.

Создайте новую строку, впишите в неё свои данные, а в поле Employee_ID поставьте год своего рождения.
Удалите строку, созданную в пункте 4.

2.1 Расширенные возможности Apex

Практика

Добавьте на первую страницу приложения картинку и создайте кнопки перехода на страницы сотрудников и локаций.
Выполните по желанию: создайте пустую страницу, внутри неё создайте регион с Interactive Report на таблицу Countries.

2.2 Что такое SQL

Практика

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов:

Выберите все данные из таблицы Departments.
Выберите поля Department_ID и Department_Name из таблицы Departments.
Выберите поле Department_Name из таблицы Departments, где Department_ID > 120.
Выберите поля First_Name, Last_Name, Salary, Hire_Date из таблицы Employee, где Manager_ID равен 100.
Отсортируйте выборку из задания 3 по полям Hire_Date, Last_Name, First_Name в таком порядке.

2.3 Комментарии в SQL

Практика

Выберите поля Department_ID и Department_Name из таблицы Departments и напишите рядом с ними однострочные комментарии с описанием этих полей.
Перед запросом из задания 1 напишите многострочный комментарий с описанием запроса.
К получившемуся запросу из задания 2 добавьте фильтр Manager_ID, равный 100, и перед ним сделайте многострочный комментарий, описывающий вашу доработку запроса.

2.4 Агрегатные функции. Введение

Практика

Выберите сумму минимальных зарплат (min_salary) и дайте этому полю понятное название из таблицы работ (Jobs).
Выберите сумму максимальных зарплат (max_salary) и дайте этому полю понятное название из таблицы работ (Jobs).
Выберите сумму зарплат (salary) и дайте этому полю понятное название из таблицы сотрудников (Employees), где идентификатор работы (job_id) равен IT_PROG.

2.6 Домашняя работа

Задача Цель задания

Перед тем как мы продолжим улучшать жизнь придуманного, но очень требовательного отдела кадров, необходимо вспомнить SQL-команды, которые мы изучили в этом модуле.

Что входит в домашнее задание

Поработать с SQL-командами.
Создать отчёт для отдела кадров.

Работа с SQL-командами

Что нужно сделать

Выберите все поля и все данные из таблицы стран (Countries).
Выберите только поля идентификатора страны (country_id) и названия страны (country_name) и все данные из таблицы стран (Countries).
Выберите все поля и все данные из таблицы отделов (Departments).
Выберите только поля идентификатора отдела (department_id) и названия отдела (department_name) и все данные из таблицы отделов (Departments).
Выберите только поля идентификатора отдела (department_id) и названия отдела (department_name). Отфильтруйте данные по полю руководителя (manger_id) равный 200 из таблицы отделов (Departments).
Уберите фильтр из запроса, полученного в пункте 5, и сделайте сортировку по полю названия отдела (department_name) в порядке убывания.
К запросу из пункта 6 добавьте псевдоколонку с текстом ‘I am the best!’ и назовите эту колонку who_am_i.
Добавьте ещё одну псевдоколонку, в которой идентификатор локаций (location_id) будет делиться на идентификатор менеджера (manager_id), и назовите получившуюся колонку random_value.
Оставьте только поле идентификатора отдела (department_id) в блоке select и просуммируйте все значения (sum) в этой колонке. Назовите получившуюся колонку total_id.
Поменяйте поле идентификатора отдела (department_id) на поле идентификатора локаций (location_id).

Создание отчёта

Что нужно сделать

Чтобы отделу кадров было проще следить за выплатами зарплат сотрудникам, необходим отчёт. Вам поставлены следующие задачи:

Создайте страницу с отображением данных (Interactive Report) работ (таблица Jobs) на основе запроса.
Запрос должен отвечать следующим условиям:

два поля;
первое поле — сумма минимальных зарплат (min_salary);
второе поле — сумма максимальных зарплат (max_salary);
у получившихся полей должны быть собственные осмысленные наименования.

На той же странице создайте регион с отображением данных (Interactive Report) сотрудников (таблица Employees) на основе запроса.
В запросе из пункта 3 сделайте одно поле — сумма зарплат (Salary) с осмысленным названием.
В запросе из пункта 3 сделайте фильтр на идентификатор работы (job_id), который выбирается параметром из выпадающего списка, основанного на List of Values.
Для реализации пункта 5 сделайте List of Values на основе запроса к таблице работ (Jobs), который отображает наименование работы (job_title) и возвращает идентификатор работы (job_id).
После выбора значения регион с зарплатой сотрудников должен обновляться.

3.1 Group by и суммирование

Описание Практика

Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов.

Задание 1

Из данных в таблице Employees найдите суммарные зарплаты для каждого вида работ. Для этого в рамках одного запроса выполните группировку по job_id и примените нужную агрегатную функцию к полю зарплат salary.

Задание 2

Выборку из прошлого задания отсортируйте по номеру колонки с суммой.

Подсказка: для сортировки по номеру столбца поставьте нужную цифру в конструкцию order by.

Задание 3

В данных из таблицы Employees найдите суммарные комиссионные проценты для каждого отдела. Для этого в рамках одного запроса выполните группировку по department_id и примените нужную агрегатную функцию к полю комиссий commission_pct.

Задание 4

В данных из таблицы Employees найдите суммарные зарплаты для каждого отдела и внутри отделов для каждого руководителя. Для этого в рамках одного запроса выполните группировку по department_id и manager_id, а потом примените нужную агрегатную функцию к полю зарплат salary.

3.2 Вычисление среднего

Практика

Задание 1

Выберите среднюю минимальную заработную плату (min_salary) и среднюю максимальную заработную плату (max_salary) в одном запросе из таблицы работ (Jobs).

Задание 2

Выберите средний идентификатор региона (region_id) из таблицы стран (Countries).

Задание 3

Выберите среднюю заработную плату сотрудников (salary) с группировкой по идентификатору работы (job_id) из таблицы сотрудников (Employees).

3.3 Количество строк

Практика

Задание 1

Посчитайте количество стран через символ звёздочку из таблицы Countries.

Задание 2

Посчитайте количество видов работ через псевдоколонку из таблицы Jobs.

Задание 3

Посчитайте количество департаментов по полю Department_ID из таблицы Departments, сгруппированных по полю Location_ID.

Задание 4

Посчитайте количество департаментов по полю Manager_ID из таблицы Departments.

Оцените урок

3.5 Домашняя работа

Задача Цель задания

Потренироваться делать ad hoc выгрузки.

Что нужно сделать

Сегодня к вам подошёл сотрудник отдела кадров и попросил сделать для него ad hoc выгрузки, потому что пользоваться SQL он не умеет, а времени на разработку нового интерфейса нет. Эта выгрузка нужна ему только сейчас, а не постоянно.

Справка

Ad hoc — латинская фраза, означающая «специально для этого», «по особому случаю». Как правило, фраза обозначает способ решения специфической проблемы или задачи, который невозможно приспособить для решения других задач и который не вписывается в общую стратегию решений, составляет некоторое исключение.

Коллега попросил вас сделать следующее:

Посчитать количество регионов из таблицы регионов (Regions).
Найти сумму заработных плат (salary) по таблице сотрудников (Employees) в разрезе идентификаторов отделов (department_id).
Узнать среднее значение процента комиссий (commission_pct) из таблицы сотрудников (Employees) в разрезе руководителей (manager_id) и вида деятельности (job_id).
Посчитать количество комиссий (commission_pct) по таблице сотрудников (Employees) в разрезе руководителей (manager_id).
Найти среднее значение минимальной (min_salary) и максимальной (max_salary) заработной платы из таблицы работ (Jobs).
Посчитать количество городов (city) из таблицы локаций (Locations) в разрезе идентификаторов стран (country_id).
Найти количество штатов-провинций (state_province) по таблице локаций (Locations) в разрезе стран (country_id).
Посчитать количество отделов из таблицы отделов (Departments) в разрезе по руководителям (manager_id) с условием равенства локации (location_id) значению 1700.
Посчитать сумму зарплат из таблицы сотрудников (Employees) в разрезе менеджеров (manager_id) и отсортировать в порядке убывания по столбцу суммы.
Создать отчёт (SQL-запрос), который будет считать из таблицы Отделов (departments) среднее значение идентификатора отдела (department_id), количество менеджеров (manager_id), сумму идентификаторов локаций (location_id), сгруппированных по полю руководителя (manager_id). Результат отсортировать по полю количества менеджеров в порядке возрастания.

SQL-разработчик - Часть 1

1.5 Домашняя работа

2.1 Расширенные возможности Apex

2.2 Что такое SQL

2.3 Комментарии в SQL

2.4 Агрегатные функции. Введение

2.6 Домашняя работа

3.1 Group by и суммирование

3.2 Вычисление среднего

3.3 Количество строк

3.5 Домашняя работа

Report Page