Основы SQL: создание переносимого кода

Расписание курса

Аннотация:

В курсе изучаются создание приложений с использованием стандартов SQL:2016 (ISO/IEK 9075) для работы с реляционными базами данных. Реляционные базы данных поддерживают стандарты SQL, а также унаследованный синтаксис, функции, операторы, типы данных и объектов. При создании приложений может использоваться унаследованный синтаксис и стандартный. Стандартный синтаксис позволяет создавать переносимые приложения, работающие одинаково во всех базах данных.

В курсе изучаются:

• Логика транзакций и уровни изоляции, стандартные команды, устанавливающие нужный уровень изоляции транзакций, влияние на производительность выбранного уровня
• Стандартные типы данных SQL, выбор подходящего типа данных и размерности типа с точки зрения переносимости, компактности, быстроты доступа, индексирования, использования функций, приведения типа к другому типу
• Логика использования, стандартные команды создания, поддержки: таблиц, временных таблиц, внешних таблиц, представлений, материализованных представлений, последовательностей
• Типы индексов, условия их использования, выбор подходящего типа индекса
• Написание команд по стандартам SQL:2016
• Различия в выполнении команд в базах данных разных производителей при использовании пустых значений, типов данных, функций и использование синтаксиса, устраняющего различия
• Замена унаследованного синтаксиса операторов, выражений и функций на соответствующие стандартам SQL:2016

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

Практические задания выполняются с использованием графического средства разработки JDeveloper в базах данных разных типов. Также изучается использование утилит командной строки стандарта  ISO/IEC 9075-3:2016 Part 3: Call-Level Interface (SQL/CLI) -  sqlcl , sqlplus , psql.

Аудитория:

• Разработчики приложений
• Администраторы баз данных
• Аналитики

Курс предназначен:

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

Требования к подготовке слушателей:
Навыки работы с компьютером (знакомство с графическим интерфейсом)

Программа курса:

1. Реляционные базы данных

• Цели курса
• Хранение данных
• Концепция реляционной базы данных
• Таблицы
• Модель "сущность-связь" (ER-модель)
• Обозначения в ER-диаграммах
• Связи между таблицами
• Ограничения целостности
• ER-диаграмма
• Стандартные типы данных SQL
• Работа с БД с помощью SQL
• Язык структурированных запросов (SQL)
• Команды SQL
• Доступ к БД
• Соединение с БД из java-программ
• JDeveloper
• Навигатор соединений JDeveloper
• Просмотр объектов базы данных
• Использование sqlcl и sqlplus
• Вызов скрипта из sqlcl и sqlplus
• Генерация скрипта в JDeveloper
• Результат: скрипт создания таблиц

2. Извлечение данных командой SELECT

• Базовая команда SELECT
• Выборка всех столбцов
• Выборка конкретных столбцов
• Написание команд SQL
• Арифметические выражения
• Арифметические операции
• Неопределенное значение (NULL)
• Неопределенные значения в арифметических выражениях
• Определение псевдонимов столбцов
• Использование псевдонимов столбцов
• Оператор конкатенации || и CONCAT
• Символьные литералы
• Использование символьных литералов
• Апострофы в литералах
• Дублирование строк
• Вывод структуры таблицы
• Вывод содержимого таблицы в файл

3. Создание и изменение структуры таблиц

• Рассматриваемые вопросы
• Типы данных
• CHAR или VARCHAR
• Работа с датами
• Типы данных для работы с датами
• Соответствие типов данных БД Oracle и PostgreSQL
• Работа с датами
• Объекты базы данных
• Обращения к объектам других схем
• Создание таблиц
• Изменение таблиц
• Опция DEFAULT
• Индексы
• Индекс типа B-дерево
• Создание B-tree индекса
• Перестройка индексов
• Удаление индексов
• Ограничения целостности
• Нарушение ограничений целостности
• Ограничение NOT NULL
• Ограничение UNIQUE
• Ограничение PRIMARY KEY
• Определение ограничений
• Ограничение FOREIGN KEY
• Свойства  FOREIGN KEY
• Ограничение CHECK
• Создание таблицы с использованием подзапроса
• Удаление таблицы
• Временные таблицы

4. Фильтрация и сортировка данных

• Ограничение количества выбираемых строк
• Использование предложения WHERE
• Операторы сравнения
• Использование операторов сравнения
• BETWEEN..AND
• IN
• LIKE
• Использование LIKE
• Использование условия IS NULL
• Логические условия
• Использование оператора AND
• OR
• NOT
• Приоритеты операторов
• Использование предложения ORDER BY
• Сортировка

5. Использование однострочных функций

• Функции преобразования регистра символов
• Использование функций преобразования регистра
• Функции манипулирования символами
• Пример использования функций
• Числовые функции
• Использование функции ROUND
• Использование функции TRUNC
• Использование арифметических операторов с датами
• Функции SQL:2016  для работы с датами
• Дополнительные функции для работы с датами в PostgreSQL (orafce)
• Неявное преобразование типов данных
• Явное преобразование типов данных
• Вложенные функции
• Использование функции NULLIF
• Использование функции COALESCE
• Условные выражения
• Выражение CASE
• Использование выражения CASE

6. Выборка данных из нескольких таблиц

• Рассматриваемые вопросы
• Выборка данных из нескольких таблиц
• Соединение таблиц
• Создание натуральных соединений
• Выборка записей с помощью натуральных соединений
• Создание соединений с использованием выражения USING
• Имена столбцов, используемых для соединения
• Выборка записей с использованием выражения USING
• Различение столбцов с одинаковыми именами
• Использование псевдонимов таблиц
• Создание соединений с помощью выражения ON
• Выборка записей с использованием выражения ON
• Рефлексивное соединение с использованием выражения ON
• Дополнительные условия соединения
• Создание трехсторонних соединений при помощи выражения ON
• Не-эквисоединения
• Выборка записей с помощью не-эквисоединений
• Внешние соединения
• Сравнение внутреннего (INNER) и внешнего (OUTER) соединений
• Левое внешнее соединение
• Правое внешнее соединение

7. Использование групповых функций

• Агрегирование
• Использование функций AVG и SUM
• Использование функций MIN и MAX
• Использование функции COUNT
• Использование ключевого слова DISTINCT
• Групповые функции и NULL
• Создание групп строк
• Выражение GROUP BY
• Использование выражения GROUP BY
• Группировка по нескольким столбцам
• Использование GROUP BY с несколькими столбцами
• Ошибочные запросы с групповыми функциями
• Вложение групповых функций
• Исключение групп
• Исключение групп: предложение HAVING
• Использование выражения HAVING
• Вложенные групповые функции
• Итоги

8. Подзапросы

• Рассматриваемые вопросы
• Задачи, решаемые подзапросами
• Синтаксис подзапросов
• Использование подзапроса
• Рекомендации по использованию подзапросов
• Типы подзапросов
• Однострочные подзапросы
• Выполнение однострочных подзапросов
• Использование групповых функций в подзапросах
• Предложение HAVING с подзапросами
• Что неправильно в этой команде?
• Будет ли выполнена эта команда?
• Многострочные подзапросы
• Использование оператора ANY в многострочных подзапросах
• Использование оператора ALL в многострочных подзапросах
• Неопределенные значения в подзапросе
• Inline Views или подзапрос
• Команда WITH
• Пример использования команды WITH

9. Объединение результатов выборки

• Рассматриваемые вопросы
• Операторы над множествами
• Оператор UNION
• Использование оператора UNION
• Оператор UNION ALL
• Использование оператора UNION ALL
• Оператор INTERSECT
• Использование оператора INTERSECT
• Оператор MINUS/EXCEPT
• Рекомендации
• БД и операции над множествами
• Пример согласования команд SELECT
• Команда WITH и рекурсивный запрос
• GROUPING SETS
• GROUPING SETS: пример
• ROLLUP и CUBE
• Использование ROLLUP и CUBE вместе с GROUP BY
• ROLLUP
• ROLLUP: пример
• CUBE
• CUBE: пример
• Функция GROUPING
• Функция GROUPING: пример
• Составные столбцы
• Составные столбцы: пример
• Сцепленные группировки
• Сцепленные группировки: пример

10 Аналитические функции

• Окна
• ORDER BY в окне
• Функции ранжировки
• ROW_NUMBER()
• Функция RANK()
• DENSE_RANK()
• CUME_DIST() и PERCENT_RANK()
• CUME_DIST
• PERCENT_RANK
• NTILE
• LAG и LEAD
• FIRST_VALUE и LAST_VALUE
• NTH_VALUE
• LISTAGG/STRING_AGG

11. Внесение изменений в данные

• Манипулирование данными с помощью SQL
• Команды манипулирования данными (DML)
• Вставка новой строки в таблицу
• Синтаксис команды INSERT
• Вставка новых строк
• Вставка строк с неопределенными значениями
• Вставка специальных значений
• Вставка конкретных значений даты и времени
• Использование подзапросов при манипулировании данными
• Копирование строк из другой таблицы
• Изменение данных в таблице
• Синтаксис команды UPDATE
• Обновление строк в таблице
• Обновление двух столбцов с помощью подзапроса
• Обновление строк на основе значений из другой таблицы
• Обзор возможности явного задания ключевого слова DEFAULT
• Явное задание значения с помощью ключевого слова DEFAULT
• Удаление строки из таблицы
• Команда DELETE
• Удаление строк из таблицы
• Удаление строк на основе значений из другой таблицы
• Команда TRUNCATE
• Команда MERGE
• Синтаксис команды MERGE
• Слияние строк
• Обзор многотабличных команд DML

12. Транзакции

• Транзакции базы данных
• Атомарность
• Согласованность чтения
• Реализация согласованности
• Уровни изоляции транзакций
• Уровень READ COMMITED
• Уровень SERIALIZABLE
• Распределённые транзакции
• Блокировки
• Механизм блокирования
• Одновременный доступ
• Механизм формирования очередей
• Конфликты блокировок
• Возможные причины конкуренции блокировок
• Взаимоблокировки
• SELECT FOR UPDATE
• Примеры SELECT FOR UPDATE
• FOR UPDATE и изоляция транзакций
• Фиксация и откат транзакций
• Завершение транзакции
• Управление транзакциями
• Откат изменений до точки сохранения
• Состояние данных до выполнения команды COMMIT или ROLLBACK
• Состояние данных после выполнения команды COMMIT
• Состояние данных после выполнения команды ROLLBACK
• Откат на уровне команды

13. Коррелированные подзапросы

• Рассматриваемые вопросы
• Многостолбцовые подзапросы
• Сравнения столбцов
• Подзапрос с парным сравнением
• Подзапрос с непарным сравнением
• Выражения со скалярными подзапросами
• Примеры скалярных подзапросов
• Связанные подзапросы
• Использование связанных подзапросов
• Использование оператора EXISTS
• Пример: поиск служащих, которым подчиняется хотя бы один служащий
• Использование оператора NOT EXISTS
• Связанная команда UPDATE
• Использование связанной команды UPDATE
• Связанная команда DELETE
• Использование связанной команды DELETE

14. Создание объектов БД

• Представления
• Преимущества представлений
• Создание представления
• Правила обновления строк таблиц через  представления
• Материализованные представления
• Связи между БД
• Внешние таблицы
• Использование внешних таблиц
• Последовательности
• Создание последовательности
• Автоматическая генерация значений столбца
• Автоинкрементальные столбцы

15. Информация от объектах в БД

• Метаданные в БД
• Получение метаданных в Java-приложениях
• БД Oracle
• Структура словаря данных
• Как использовать представления словаря
• Представления USER_OBJECTS и ALL_OBJECTS
• Представление USER_OBJECTS
• Информация о таблицах
• Информация о столбцах
• Информация об ограничениях целостности
• Информация о представлениях
• Информация о последовательностях
• БД PostgreSQL
• Информационная схема
• Пример обращения к INFORMATION_SCHEMA
• Системные каталоги и представления
• Статистики