SQL-разработчик - Часть 3
Andrew.Kaluba7.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.