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

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

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

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

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

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

  1. Создайте страницу с отображением данных (Interactive Grid) сотрудников (таблица Employees). Страница должна иметь ссылку на родительскую страницу Home для удобства навигации.
  2. Продемонстрируйте её работоспособность после запуска, для этого:
  • скройте все колонки, заканчивающиеся на ID;
  • выберите всех сотрудников с зарплатой свыше 12 000;
  • отсортируйте сотрудников по фамилии (Last_name);
  • верните все скрытые колонки и уберите фильтр.
  1. Создайте новую строку, впишите в неё свои данные, а в поле Employee_ID поставьте год своего рождения.
  2. Удалите строку, созданную в пункте 4.

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

Практика

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

2.2 Что такое SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 Вычисление среднего

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

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

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

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

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

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

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

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

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

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

Оцените урок

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

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

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

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

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

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

4.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 выгрузка без которой он не сможет предоставить план по расширению штата.

Задание от коллеги, который сидит у большого цветка:

  1. Создайте запрос, который выбирает уникальные наборы данных из таблицы локаций (locations) и представляет поля штат/провинция (state_province) и идентификатор страны (country_id).
  2. Найдите повторяющиеся наборы строк из предыдущего пункта.
  3. Создайте запрос, который считает количество городов (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), отобразить все поля и добавить вычисляемые поля:

  1. Индекс строки, он же номер строки (row_number), через всю таблицу с сортировкой по уникальному идентификатору (employee_id).
  2. Сумму с нарастающим итогом (sum) заработной платы (salary) с секцией по руководителям (manager_id) и сортировкой по номеру телефона (phone_number).
  3. Комиссию (comission_pct) из предыдущей строки (lag) со значением по-умолчанию 0 и секционированием и сортировкой как в пункте 2.
  4. Очередность (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) замените, пожалуйста, на их наименования, чтобы было красиво и понятно.
Для успешной сдачи домашнего задания сделайте все задание, и на каждый пункт и подпункт сделайте скриншот, который после приложите в специальном окне.

7.2 Соединение таблиц. right outer join

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

Задание 1
Сделайте запрос, в котором правым соединением присоедините к таблице отделов (departments) таблицу локаций (locations).

Задание 2
Сделайте запрос, в котором правым соединением присоедините к таблице истории работ (job_history) таблицу типов работ (jobs) с условием на тип работы программистов (IT_PROG).

Задание 3
Создайте запрос, в котором будут соединяться таблицы регионов (regions) и стран (countries) левым соединением и этот набор данных нужно присоединить к таблице локаций (locations) с фильтром на страны - Канада, Мексика, США.

7.3 Соединение таблиц. full outer join

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

Задание 1
Соедините полным соединением таблицы стран (countries) и локаций (locations) с условием равенства по полям идентификатору стран (country_id) и условием включения стран Бразилии и Аргентины.

Задание 2
Соедините полным соединением таблицы истории работ (job_history) и типов работ (jobs) с условием равенства по полям вида работ (job_id). При этом, для таблицы работ (jobs) поставьте лимит на количество строк (rownum) меньше десяти.

Задание 3
Не меняя типа соединения из результата задания 2 выберите данные, которые можно получить только для левого соединения.

Задание 4
Не меняя типа соединения из результата задания 2 выберите данные, которые можно получить только для правого соединения.

7.4 Декартово произведение. cross join

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

Задание 1
Соедините перекрестным соединением таблицы сотрудников (employees) и регионов (regions) .

Задание 2
Соедините перекрестным соединением таблицы стран (countries), работ (jobs) и отделов (departments).

Задание 3
Соедините перекрестным соединением таблицу истории работ (job_history) и выборку

select 1553 as id, ‘print’ as txt from dual
union all
select 1868 as id, ‘type’ as txt from dual;

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

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

Что нужно сделать
Ваш тимлид решил дать Вам задание, чтобы освежить в память конструкции соединений и попросил сделать несколько запросов.

  1. Через полное соединение свяжите истории работ Jonathon Taylor со справочником работ и выберите те работы на которых он не бывал.
  2. Создайте подобие календаря дней недели, перемножив три сгенеренные структуры.
  • 12 месяцев через таблицу dual;
  • 5 недель через таблицу dual;
  • 7 дней недель, тоже через таблицу dual.
  1. Соберите отчет в котором регион Европа цепляется внутренним соединением к странам, эта выборка левым соединением цепляется к локациям, далее эта выборка цепляется правым соединением к отделам и далее выборка полным соединением цепляется к истории работ с условием идентификатора сотрудника 176 и 200. После этого нужно полученную выборку соединить со справочником работ перекрестным запросом.

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

8.2 Хранение данных в таблицах

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

Задание 1
Создайте таблицу accounts - таблицу банковских счетов работников. Поля в этой таблице будут: employee_id - число (идентификатор сотрудника), account_num - текст на 20 символов (номер счета), open_date - дата (дата открытия), close_date - дата (дата закрытия).

Задание 2
Создайте таблицу duty - таблицу дежурства работников. Поля в этой таблице будут: employee_id - число (идентификатор сотрудника), duty_date - дата (дата дежурства).

Задание 3
Создайте таблицу employee_cars - таблицу автомобилей работников. Поля в этой таблице будут: employee_id - число (идентификатор сотрудника), car_number - текст на 16 символов (номер автомобиля), car_maker - текст на 32 символа (марка автомобиля), car_model - текст на 64 символа (модель автомобиля).

8.4 Преобразование типов

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

Задание 1

Преобразуйте текст ‘55523’ в число, используя таблицу dual…

Задание 2

Преобразуйте текст ‘Skillbox’ в число, используя таблицу dual.

Задание 3

Преобразуйте число 2432352 в текст, используя таблицу dual и маску ‘$999,999,999.00’.

Задание 4

Преобразуйте дату date’1930-08-02’ в текст, используя таблицу dual и маску ‘dd.MON.yyyy’

Задание 5

Преобразуйте дату date’1922-08-02’ в текст, используя таблицу dual и маску ‘dd.mon.yyyy’.

Задание 6

Преобразуйте текст ’1812-08-26’ в дату.

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

Задача

Цель задания

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

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

В этот раз Вам не повезло… На Вас вышел отдел сопровождения баз данных. Они хотят, чтобы Вы немного поправили код, поэтому они выкатили список требований к запросам, которые надо сделать, чтобы они установили программы в боевую среду.

  1. Выберите сумму зарплат по отделам из таблицы сотрудников и преобразуйте полученное значение в строку с денежной маской.
  2. Преобразуйте дату найма сотрудников из таблицы сотрудников до первого января года найма.
  3. Отдельным запросом преобразуйте полученные значения в задании 2 в текст со значением года.
  4. Отдельным запросом преобразуйте полученные значения в задании 2 в число со значением года.
  5. Вычислите минимальный, максимальный и средний годы найма из данных задания 2.
  6. Создайте таблицу hurricane (Справочник ураганов) с полями: name - текст на 64 символа (Наименовани), report_year - дата (Год, в который приходил ураган), victims - число (Количество жертв).
  7. Наложить на поле наименования урагана ограничение использования метки null.
  8. Очистить таблицу ураганов функцией truncate.
  9. Удалить таблицу ураганов.

9.2 Разделение ролей

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

Задание 1
Создайте схему accountant как показано в уроке.

Задание 2
Создайте схему security как показано в уроке.

9.3 Представление

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

Задание 1
Дайте права схеме accountant на select к таблице hr.accounts (была создана в модуле 8, практике после урока 1).

Задание 2
Создайте представление accounts в схеме accountant, основанную на запросе к таблице hr.accounts.

Задание 3
Дайте права схеме security на select к таблице hr.duty и hr.employees_cars (были созданы в модуле 8, практике после урока 1).

Задание 4
Создайте представление duty и cars в схеме security, основанные на запросах к таблицам hr.duty и hr.employees_cars соответственно.

9.4 Последовательности

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

Задание 1
Создайте последовательность counter.

Задание 2
Выберите несколько раз следующее значение из последовательности counter.

Задание 3
Выберите несколько раз текущее значение из последовательности counter.

Задание 4
Создайте таблицу inventory - таблицу инвентаря работников. Поля в этой таблице будут: equip_id - число, автоинкрементное (идентификатор оборудования), employee_id - число (идентификатор сотрудника), equip_name - текст на 256 символов.

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

Задача

Цель задания

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

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

Вы решили прибраться в своем проекте и сделать небольшой рефакторинг. Вечером Вы написали себе записку, что надо сделать и вот, следующее утро, Вы сели делать приборку по списку.

  1. Удалить представление security.duty.
  2. Удалить таблицу hr.duty.
  3. Создать таблицу security.duty с автоинкрементным полем id.
  4. Дать права на чтение с возможностью передавать права дальше таблицы hr.employees схеме security.
  5. Создать представление security.duty_employees на основе запроса с соединением таблиц hr.employees и security.duty по полю employee_id и выводом полей с натуральными значениями (без идентификаторов).

Для успешной сдачи домашнего задания сделайте все задание и на каждый пункт и подпункт сделайте скриншот, который после приложите в GitLab.

10.1 Нормальные формы и ключи

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

Задание 1

Создайте схему seller.

Задание 2

Создайте таблицу seller.maker (Производитель автомобилей) полями: id - число, автоинкрементное поле (Идентификатор производителя), name - текст на 32 символа (Наименование производителя).

Задание 3

Создайте уникальный индекс maker_pk на поле id таблицы maker.

Задание 4

Создайте ограничение primary key maker_pk на поле id таблицы maker.

Задание 5

Создайте таблицу seller.model (Модели автомобилей) полями: id - число, автоинкрементное поле (Идентификатор модели), name - текст на 32 символа (Наименование производителя), maker_id - число (Идентификатор производителя автомобилей). Создайте ограничение foreign key поля maker_id на поле id таблицы maker.

Задание 6

Создайте уникальный индекс model_pk на поле id таблицы model.

10.2 Построчная вставка данных

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

Задание 1

Заполните таблицу seller.maker по списку производителей автомобилей из гит.

Задание 2

Зафиксируйте транзакцию.

10.3 Изменение данных

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

Задание 1

Обновить (update) таблицу seller,maker по условию наименования производителя - ВАЗ, обновить на Lada.

Задание 2

Посчитать (count) количество строк во всей таблице seller,maker.

Задание 3

Удалить (delete) из таблицы seller,maker строки по условию наименования производителя - Lada

Задание 4

Посчитать (count) количество строк во всей таблице seller,maker.

Задание 5

Вставить в таблицу seller.maker производителя ВАЗ.

Задание 6

Зафиксировать транзакцию.

10.4 Создание данных через SELECT

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

Задание 1

Создать таблицу seller.model_tmp через create as select таблицы seller.model.

Задание 2

Заполнить таблицу seller.model_tmp преобразуя натуральное значение произволителя в ID. Для этого выполните SQL скрипт script_of_models.sql

Задание 3

Вставьте в таблицу seller.models через insert as select данные из таблицы seller.models_tmp.

Задание 4

Зафиксируйте транзакцию.

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

Задача

Цель задания

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

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

Кажется Ваш вес в компании стал очень сильно вырос за последнее время. Вчера вечером в курилки Вы встретили архитектора программных продуктов. Этот задумчивый и немногословный парень сказал, что у него нет сил и времени, а нужно сделать архитектуру зоны выгрузки для корпоративного хранилища данных.

Он попросил Вас это сделать, Вы просто кивнули в ответ. Сегодня Вы смотрите в то что Вам нужно сделать, собираетесь с мыслями и приступаете к программированию.

  1. Создайте схему ods (operational data store).
  2. Создайте в схеме ods таблицы в третьей нормальной форме со всеми связями и индексами.
  3. Вам необходимо создать следующие таблицы:
  4. таблица-справочник клиентов со списком полей:
    1. идентификатор
    2. имя
    3. фамилия
    4. телефон
    5. дата рождения
  5. таблица-справочник счетов со списком полей:
    1. идентификатор
    2. идентификатор клиента, которому создан счет
    3. номер счета
    4. дата открытия
    5. дата закрытия (у открытых счетов - null)
  6. таблица-справочник карт со списком полей:
    1. идентификатор
    2. идентификатор счета к которому прикреплена
    3. номер карты
    4. месяц окончания действия
    5. год окончания действия
  7. таблица проводок со списком полей:
    1. дата
    2. сумма
    3. идентификатор клиента
    4. идентификатор карты
  8. Заполнить несколько строк для тестирования.
  9. Сделать описание логической модели как на рисунке.