Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

3. Выберите только те названия городов из таблицы CITY, где в таблице MAN не более одного человека, используйте EXISTS.

4. Найдите количество автомобилей каждой марки, которые купили люди старше 29 лет. Используйте подзапрос с IN.

В прошлых шагах мы с вами рассмотрели возможности работы с подзапросами, а также различные типы подзапросов и их использование в языке SQL.

Перечислим типы подзапросов, с которыми мы познакомились:

— подзапросы как дополнительная колонка в основном запросе;

— подзапросы с оператором IN;

— подзапросы, которые идут после FROM в качестве источника данных, то есть как бы вместо таблицы.

Попробуем сейчас сочетать эти несколько типов подзапросов в одном главном большом запросе, в одной большой команде SELECT.

Сразу перейдем к практике.

Вывести на экран данные из таблицы MAN и таблицы AUTO: имя, фамилию (FIRSTNAME, LASTNAME) человека, цвет машины (COLOR), марку машины (MARK) для людей, чей возраст (YEAROLD) больше 35 лет, и как дополнительную колонку вывести с помощью подзапроса информацию о том, сколько машин этого же цвета (COLOR) в таблице AUTO.

Рисунок 121. Запрос MAN, AUTO сложный

Вывести на экран данные из таблицы MAN *, где имя человека (FIRSTNAME) больше шести букв и эти люди живут в городе с населением (PEOPLES) больше 1 миллиона человек.

Рисунок 122. Запрос MAN, CITY

Вывести на экран данные из таблицы CITY и таблицы MAN, где население (PEOPLES) больше 1 миллиона человек, вывести как дополнительный столбец город (CITYNAME), у которого код города больше на 1.

Рисунок 123. Запрос MAN, AUTO: сложный запрос

Подзапрос, который возвращает единственную запись, можно также использовать в конструкции WHERE при проверке на равенство значений.

Приведу пример:

Естественно, такой запрос должен возвращать одну-единственную строку, а также выводить в качестве результата только одну колонку.

При таком подходе считается стандартной практикой использование псевдонимов для подзапросов.

Если в одном из подзапросов будут сгруппированные данные с помощью GROUP BY, такой запрос будет работать?

Разумеется, будет, и вот пример такого запроса. Подобные запросы мы разбирали в предыдущем шаге.

1. Вывести все автомобили AUTO *, которые приобрели люди, проживающие в городе (CITY) с населением (POPULATION) больше 1 миллиона человек. Вывести как колонку, как дополнительное поле подзапрос «название города» (CITYNAME) рядом с маркой автомобиля.

2. Вывести все автомобили AUTO *, которые приобрели люди, проживающие в городе (CITY) с населением (PEOPLES) больше 1 миллиона человек. Вывести как колонку подзапрос «название города» (CITYNAME) рядом с маркой автомобиля.

Рассмотрим на этом шаге незаслуженно забытые, нечасто используемые на практике конструкции языка SQL — ANY, ALL. Тем не менее данные конструкции обладают исключительными возможностями.

Синтаксическая конструкция ANY — это предикат, который является верным, если каждое из всех значений, выведенных подзапросом, удовлетворяет условию для текущей строки внешнего запроса.

Давайте рассмотрим на примерах:

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

Рисунок 124. Работа предиката ANY

Вывести из таблицы AUTO те машины, для которых в таблице AUTO есть машины такого же цвета.

Рисунок 125. Работа предиката ANY: таблица AUTO

Предикат ALL является верным, если каждое значение, выбранное подзапросом, удовлетворяет условию в предикате внешнего запроса.

Ключевое отличие от предиката и ANY в том, что мы можем использовать операции больше и меньше (> или <).

Следующие запросы являются примером данного предиката.

Вывести на экран с помощью SELECT всех людей из таблицы MAN, чей возраст больше возраста каждого человека, который проживает в городе Владимир.

Рисунок 126. Работа предиката ALL: таблицы CITY, MAN

Вывести на экран с помощью SELECT всех людей из таблицы MAN, чей возраст меньше каждого человека, у которого автомобиль BMW.

Рисунок 127. Работа предиката ALL: таблицы AUTO, MAN

Обратите внимание, что конструкции ALL и ANY используются в WHERE как результат сравнения значения ячейки в заданной колонке со списком значений. То есть сначала название поля, а затем уже подзапрос.

Можно ли использовать ALL и ANY со знаком неравенства?

Да, можно, и вот примеры таких запросов.

Вывести на экран с помощью SELECT всех людей из таблицы MAN, чей возраст не равен возрасту каждого человека, у которого автомобиль BMW.

Вывести из таблицы AUTO машины, если в таблице AUTO нет машин такого же цвета.

Подобным образом можно оформить такой же запрос с конструкцией NOT.

1. Повторить запросы из этого шага.

2. Вывести все города, где количество проживающих людей больше, чем количество людей в городах с CITYCODE 5, 6, 7.

3. Вывести на экран с помощью SELECT всех людей из таблицы MAN, чей возраст меньше каждого человека, у которого автомобиль BMW.

Очень часто требуется при решении различных задач преобразовать данные от одного к другому типу, например преобразовать число к строке.

В SQL ORACLE диалекта существует несколько функций для преобразования типов данных.

Есть универсальная функция для преобразования различных типов данных к текстовому формату.

Универсальная функция TO_CHAR.

Рассмотрим несколько примеров работы функции TO_CHAR.

Синтаксис

Преобразование даты в текстовый формат

Примеры работы функции TO_CHAR для преобразования дат в строку

Показать текущий день недели.

Показать текущую дату как в примере 22-10-2018 23:11:11, преобразовав к строке.

Показать текущую дату как в примере 22.10.18 23:11:11, преобразовав к строке.

Показать текущую дату как в примере 2018.10.22 23:11:11, преобразовав к строке.

Преобразование числовых значений в текстовый формат

Примеры