Рекомендованные книги для параллельного чтения с курсом:
- Алан Бьюли — Изучаем 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
Практика
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов:
- Выберите поля Department_ID и Department_Name из таблицы Departments и напишите рядом с ними однострочные комментарии с описанием этих полей.
- Перед запросом из задания 1 напишите многострочный комментарий с описанием запроса.
- К получившемуся запросу из задания 2 добавьте фильтр Manager_ID, равный 100, и перед ним сделайте многострочный комментарий, описывающий вашу доработку запроса.
2.4 Агрегатные функции. Введение
Практика
Откройте инструмент разработчика Oracle SQL Developer, подключитесь к базе данных через пользователя HR и напишите несколько запросов:
- Выберите сумму минимальных зарплат (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 Вычисление среднего
Практика
Откройте инструмент разработчика 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 — латинская фраза, означающая «специально для этого», «по особому случаю». Как правило, фраза обозначает способ решения специфической проблемы или задачи, который невозможно приспособить для решения других задач и который не вписывается в общую стратегию решений, составляет некоторое исключение.
Коллега попросил вас сделать следующее:
- Посчитать количество регионов из таблицы регионов (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). Результат отсортировать по полю количества менеджеров в порядке возрастания.
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 выгрузка без которой он не сможет предоставить план по расширению штата.
Задание от коллеги, который сидит у большого цветка:
- Создайте запрос, который выбирает уникальные наборы данных из таблицы локаций (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) замените, пожалуйста, на их наименования, чтобы было красиво и понятно.
Для успешной сдачи домашнего задания сделайте все задание, и на каждый пункт и подпункт сделайте скриншот, который после приложите в специальном окне.
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 выгрузки.
Что нужно сделать
Ваш тимлид решил дать Вам задание, чтобы освежить в память конструкции соединений и попросил сделать несколько запросов.
- Через полное соединение свяжите истории работ Jonathon Taylor со справочником работ и выберите те работы на которых он не бывал.
- Создайте подобие календаря дней недели, перемножив три сгенеренные структуры.
- 12 месяцев через таблицу dual;
- 5 недель через таблицу dual;
- 7 дней недель, тоже через таблицу dual.
- Соберите отчет в котором регион Европа цепляется внутренним соединением к странам, эта выборка левым соединением цепляется к локациям, далее эта выборка цепляется правым соединением к отделам и далее выборка полным соединением цепляется к истории работ с условием идентификатора сотрудника 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 выгрузки.
Что нужно сделать
В этот раз Вам не повезло… На Вас вышел отдел сопровождения баз данных. Они хотят, чтобы Вы немного поправили код, поэтому они выкатили список требований к запросам, которые надо сделать, чтобы они установили программы в боевую среду.
- Выберите сумму зарплат по отделам из таблицы сотрудников и преобразуйте полученное значение в строку с денежной маской.
- Преобразуйте дату найма сотрудников из таблицы сотрудников до первого января года найма.
- Отдельным запросом преобразуйте полученные значения в задании 2 в текст со значением года.
- Отдельным запросом преобразуйте полученные значения в задании 2 в число со значением года.
- Вычислите минимальный, максимальный и средний годы найма из данных задания 2.
- Создайте таблицу hurricane (Справочник ураганов) с полями: name - текст на 64 символа (Наименовани), report_year - дата (Год, в который приходил ураган), victims - число (Количество жертв).
- Наложить на поле наименования урагана ограничение использования метки null.
- Очистить таблицу ураганов функцией truncate.
- Удалить таблицу ураганов.
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 выгрузки.
Что нужно сделать
Вы решили прибраться в своем проекте и сделать небольшой рефакторинг. Вечером Вы написали себе записку, что надо сделать и вот, следующее утро, Вы сели делать приборку по списку.
- Удалить представление security.duty.
- Удалить таблицу hr.duty.
- Создать таблицу security.duty с автоинкрементным полем id.
- Дать права на чтение с возможностью передавать права дальше таблицы hr.employees схеме security.
- Создать представление 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 выгрузки.
Что нужно сделать
Кажется Ваш вес в компании стал очень сильно вырос за последнее время. Вчера вечером в курилки Вы встретили архитектора программных продуктов. Этот задумчивый и немногословный парень сказал, что у него нет сил и времени, а нужно сделать архитектуру зоны выгрузки для корпоративного хранилища данных.
Он попросил Вас это сделать, Вы просто кивнули в ответ. Сегодня Вы смотрите в то что Вам нужно сделать, собираетесь с мыслями и приступаете к программированию.
- Создайте схему ods (operational data store).
- Создайте в схеме ods таблицы в третьей нормальной форме со всеми связями и индексами.
- Вам необходимо создать следующие таблицы:
- таблица-справочник клиентов со списком полей:
1. идентификатор
2. имя
3. фамилия
4. телефон
5. дата рождения - таблица-справочник счетов со списком полей:
1. идентификатор
2. идентификатор клиента, которому создан счет
3. номер счета
4. дата открытия
5. дата закрытия (у открытых счетов - null) - таблица-справочник карт со списком полей:
1. идентификатор
2. идентификатор счета к которому прикреплена
3. номер карты
4. месяц окончания действия
5. год окончания действия - таблица проводок со списком полей:
1. дата
2. сумма
3. идентификатор клиента
4. идентификатор карты - Заполнить несколько строк для тестирования.
- Сделать описание логической модели как на рисунке.
