SQL-разработчик - Часть 2
Andrew.Kaluba4.1 Максимальное и минимальное значение
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.
Задание 1
Посчитайте самую большую зарплату из колонки минимальной границы зарплаты (min_salary) и самую маленькую зарплату из колонки максимальной границы зарплаты (max_salary) из таблицы работ (jobs).
Задание 2
Найдите минимальное и максимальное наименование страны (country_name) из таблицы стран (countries)
Задание 3
Найдите максимальную и минимальную зарплаты (salary) из таблицы сотрудников (employees) сгруппированные по идентификатору отдела (department_id).
4.2 Группировка одинаковых данных
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов.
Задание 1
Сделайте запрос, выбирающий уникальные идентификаторы локаций (location_id) из таблицы отделов (departments). И сравните их количество с количеством строк в этой таблице.
Задание 2
Сделайте запрос, выбирающий уникальные идентификаторы работ (job_id) из таблицы работ (jobs). И сравните их количество с количеством строк в этой таблице.
Задание 3
Сделайте запрос, выбирающий уникальные наименования Штатов/Провинций (state_province) из таблицы локаций (locations). И сравните их количество с количеством строк в этой таблице.
4.3 Условия после агрегации данных
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов.
Задание 1
Сделайте запрос, который берет данные из таблицы сотрудников (employees) и разбивает их на группы по отделам (department_id). Также, посчитайте суммы зарплат в отделах и выберите те отделы, где сумма зарплат больше 120000.
Задание 2
Сделайте запрос, который берет данные из таблицы локаций (locations) и разбивает их на группы по идентификаторам стран (country_id). Также, посчитайте количество штатов и провинций (state_province) внутри стран и выберите те страны, где
количество штатов и провинций равен нулю.
4.4 Подзапросы
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов.
Задание 1
Сделайте запрос, выбирающий сотрудников (таблица employees) с зарплатой более 9000.
Задание 2
Сделайте запрос из здания 1 подзапросом и в запросе, который будет сверху сделайте фильтр на имя (first_name) John (first_name = ‘John’).
Задание 3
Преобразуйте запрос из задания 2 в запрос с использованием CTE (Common Table Expression). Таким образом, чтобы в первом запросе выбирались сотрудники с зарплатой более 9000, а во втором - накладывался фильтр на имя.
Задание 4
Создайте запрос из таблицы сотрудников (employees) в котором выберите только имена (first_name), фамилии (last_name) и телефонные номера (phone_number). При этом на выборку наложите фильтр по двум полям одновременно - это будут идентификаторы отдела (department_id) и вида деятельности (job_id). Значения для этих фильтров необходимо выбрать подзапросом из таблицы истории смены типов деятельности (job_history), которые нужно будет отфильтровать по сотруднику и идентификатором (employee_id) равным 176.
4.5 Домашняя работа
Задача Цель задания
Потренироваться делать ad hoc выгрузки.
Что нужно сделать
Сегодня к Вам прибежал представитель отдела кадров и ему нужна Ad hoc выгрузка без которой он не сможет предоставить план по расширению штата.
Задание от коллеги, который сидит у большого цветка:
Создайте запрос, который выбирает уникальные наборы данных из таблицы локаций (locations) и представляет поля штат/провинция (state_province) и идентификатор страны (country_id).
Найдите повторяющиеся наборы строк из предыдущего пункта.
Создайте запрос, который считает количество городов (city) в каждой стране (country_id), а потом выбирает те страны, где количество больше двух. Это задание необходимо сделать с помощью конструкции with, иначе коллега ничего не поймет.
5.2 Аналитические функции. Сумма с нарастающим итогом
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.
Задание 1
Создайте запрос, в котором используется аналитическая функция sum для подсчета общей суммы минимальных зарплат (min_salary) в таблице работ (jobs).
Задание 2
Создайте запрос, который использует функцию count и конструкцию аналитических функций, и посчитайте количество городов (city) с секционированием по странам (country_id) в таблице работ (locations).
Задание 3
Возьмите запрос, который Вы сделали в задании 2 и измените аналитическую функцию для получения набегающего итога с сортировкой по полю почтового индекса (postal_code).
5.3 Аналитические функции. row_number()
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов.
Задание 1
Пронумеруйте строки по всей таблице стран (countries) с сортировкой по названию страны (country_name).
Задание 2
Пронумеруйте строки по таблице сотрудников (employees) с секционированием по идентификатору отдела (department_id) и сортировкой по имени (first_name).
Задание 3
Выберете максимальное (агрегатная функция) наименование отдела (departent_name) из таблицы отделов (departments), где нумерация строк, секционированная по идентификатору локации (location_id) и отсортированная по наименованию отдела (department_id) по убыванию, больше единицы.
5.4 Аналитические функции. lead/lag
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов.
Задание 1
Создайте запрос, который показывает фамилию (last_name) и имя (first_name), а также фамилию (last_name) сотрудника, которого наняли (hire_date) следующим (lead) и имя (first_name) сотрудника, которого наняли (hire_date) следующим (lead) из таблицы сотрудников (employees).
Задание 2
Переделайте запрос из задания 1 на выбор предыдущей строки (lag) и передайте аргументы сдвига - 2, а значения по-умолчанию - ‘не пришел’.
Задание 3
Добавьте в запрос из задания 2 секционирование в оконные функции по отделам (department_id).
5.5 Домашняя работа
Задача Цель задания
Потренироваться делать ad hoc выгрузки.
Что нужно сделать
Ваш просмотр нового ролика по обучению языкам программирования прервало оповещение о сообщении в почте.
Вы открывает письмо от начальника отдела кадров, а там сухо написано:
Добрый день!
Напишите, пожалуйста, за меня SQL запрос, потому что я не умею.
В этом запросе нужно выбрать данные по сотрудникам (employees), отобразить все поля и добавить вычисляемые поля:
Индекс строки, он же номер строки (row_number), через всю таблицу с сортировкой по уникальному идентификатору (employee_id).
Сумму с нарастающим итогом (sum) заработной платы (salary) с секцией по руководителям (manager_id) и сортировкой по номеру телефона (phone_number).
Комиссию (comission_pct) из предыдущей строки (lag) со значением по-умолчанию 0 и секционированием и сортировкой как в пункте 2.
Очередность (row_number) приема на работу (hire_date) в каждый отдел (department_id).
6.2 Объединение данных
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к Базе Данных через пользователя HR и напишите несколько запросов.
Задание 1
Создайте выборку, используя таблицу dual, в которой будет 4 столбца id, code, name и population. Положите данные о четырех штатах США в эту выборку, где id - номер штата, code - двухсимвольный код штата, name - официальное название штата и population - количество жителей.
Задание 2
Выберите столбцы идентификатора и названия из таблицы стран с фильтром на идентификатор региона равный единице. И присоедините Словению и Словакию к этому списку.
Подсказка: Словению и Словакию нужно создать с помощью таблицы dual.
Задание 3
Объедините выборку из таблицы сотрудников (employees) по полям идентификаторов сотрудника (employee_id), отдела (department_id) и даты приема на работу (hire_date) с фильтром по идентификатору сотрудника (employee_id) равный 101. c выборкой из таблицы истории работы сотрудников (job_history) по полям идентификаторов сотрудника (employee_id), отдела (department_id) и даты начала работы в этой должности (start_date) с фильтром по идентификатору сотрудника (employee_id) равный 101.
6.3 Вычитание наборов данных
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов.
Задание 1
Выберите данные по полю наименования отдела (department_name) из таблицы отделов (departments) с фильтром идентификатора локаций (location_id) равный 1700. И вычтите выборку с полем наименования отдела (department_name) из таблицы отделов (departments) с фильтром идентификатора локаций (location_id) не равный 1700.
Подсказка: символы не равенства являются != или <>.
Задание 2
Сделайте обратное вычитание выборок из задания 2 и сравните результаты.
Задание 3
Сделайте выборку из таблицы регионов (regions) по всем полям и вычтите из нее вот эту:
select 3 as region_id,
'Asia' as region_name
from dual
union all
select 2 as region_id,
'Americas' as region_name
from dual;
Сделайте выводы о необходимости скобок в синтаксисе и манипуляциях с объединением.
6.4 Соединение таблиц
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов.
Задание 1
Соедините таблицы стран (countries) и локаций (locations) с условием равенства по полям идентификатору стран (country_id).
Задание 2
Соедините таблицы типов работ (jobs) и истории работ (job_history) с условием равенства по полям вида работ (job_id).
Задание 3
Соедините таблицы отделов (departments) и локаций (locations) с условием равенства по полям идентификатора локаций (location_id).
Задание 4
Соедините таблицы отделов (departments) и истории работ (job_history) с условием равенства по полям идентификатора отдела (department_id).
6.5 Соединение таблиц. left outer join
Задание 1
Соедините левым соединением таблицы стран (countries) и локаций (locations) с условием равенства по полям идентификатору стран (country_id).
Задание 2
Соедините левым соединением таблицы (left outer join) истории работ (job_history) и типов работ (jobs) с условием равенства по полям вида работ (job_id). При этом, для таблицы работ (jobs) поставьте лимит на количество строк (rownum) меньше десяти.
Задание 3
Из результата задания 2 выберите данные, которые можно получить только для левого соединения (left outer join).
Задание 4
Из результата задания 2 сделайте результирующую выборку, напоминающую внутреннее соединение. (left outer join с условием в блоке where)
6.6 Домашняя работа
Задача
Цель задания
Потренироваться делать ad hoc выгрузки.
Что нужно сделать
Сегодня Ваш тимлид попросил закончить за него работу, он сделал основную часть, но у отдела кадров продолжают расти их “хотелки”.
Он перевесил на Вас задачу в task tracker, в которой миллион комментариев и несколько открытых просьб, написанных в свободной форме.
Ваш тимлид оставил запрос, посчитайте сколько отделов осталось, в которых есть руководитель. Для этого нужно воспользоваться функциями minus и count.
select department_id,
department_name,
manager_id,
location_id
from departments
where manager_id is null;
Создайте запрос с нашими адресами (locations), но так, чтобы страны были написаны не идентификатором, а нормально (countries) и регион пусть тоже будет не идентификатором.
Слепите, пожалуйста, список работ (job_history) и текущую работу (employees) для Jennifer Whalen, чтобы мы могли посмотреть ее карьерный путь. И идентификаторы работ (jobs), отделов (departments), сотрудников (employees) замените, пожалуйста, на их наименования, чтобы было красиво и понятно.
Для успешной сдачи домашнего задания сделайте все задание, и на каждый пункт и подпункт сделайте скриншот, который после приложите в специальном окне.