Полная версия
Самоучитель. Курс SQL. Базы данных. ORACLE
Результат:
В таблице ниже список основных элементов даты (и времени), которые можно использовать при составлении формата для функции to_char.
Теперь нам ни одна задача не страшна! Но это еще не все! Функция to_char может использоваться для преобразования числа в текст, а не только даты. После преобразования числа в текст, для него уже не будут доступны арифметические операции, но зато смотреться будет красиво.
Преобразование числа в текст делается, в основном, для его форматирования, красивого отображения, например, с пробелами–разделителями групп разрядов. Или можно всегда отображать значение с определенным количеством чисел после запятой. Для целого числа, например, после запятой будут отображаться нули. По мере развития отчетных систем, выводящих данные на печать или в отчеты, надобность форматирования чисел самой СУБД при выдаче данных, потеряла актуальность. Сами отчетные системы умеют отображать числа с любым форматированием и для них главное – само число, то есть сами данные. Тем не менее, для полноты темы, ниже мы рассмотрим использование функции to_char для преобразования числа в текст.
Выведем названия блюд столовой, их цены, а также преобразованные значения цен:
Получим:
Самое первое на что нужно обратить внимание в маске, это на разделитель целой и дробной части – D (англ. delimiter – разделитель). Далее слева и справа от разделителя мы видим цифры «0» и «9». «0» обозначает обязательное число. То есть в ценах блюд столовой до запятой должно быть минимум одно число. Даже если это будет 0. Например, цена 0 рублей 50 копеек. «9» обозначает необязательное число. В нашей маске после разделителя указаны два необязательных числа. При стоимости товара в ровно 20 рублей, после запятой нет чисел. Они бы были если товар стоил 20 рублей 55 копеек, но мы, исходя из того, какие цены у нас есть в столовой, заложили в формат, что после разделителя целой и дробной части, дальше чисел может не быть (в случае целых чисел). И поставили «9» и «9», показывая Ораклу, что нам нужны здесь два знака, но их в значении цены может не быть. Благодаря этому Оракл вывел цену в третьем столбце со знаками после запятой, даже, в случае целых чисел.
Рассмотрим еще один пример. Выведем суммы заработных плат, перечисляемых сотрудникам:
Получаем:
Спереди разделителя указано только одно обязательное число. Остальные все числа не обязательные. Через 3 цифры вставлен разделитель групп разрядов – буква «G». Это визуальный пробел для облегчения понимания размера числа.
7.3. Функция to_number
Функция to_number эквивалент функции to_char при преобразовании числа в текст, но делает обратное – преобразовывает текст в число. Пример:
Получаем:
8. Правильное обращение с NULL («пустыми» значениями)
Это еще одна очень важная тема! Ее знание не просто так спрашивают на собеседованиях! Выберем из таблицы CanteenDishes («Блюда столовой») строчки. Для повторения темы сортировки, заодно, упорядочим выбираемые блюда по цене в обратном порядке:
Получаем:
Здесь нужно обратить внимание на столбец «CookID» («Идентификатор повара»). Из всего ассортимента блюд нашей столовой, есть блюда, идентификатор повара у которых не заполнен. Значение в графе CookID пустое. Для таких строчек в этом столбце программа SQL Developer выводит нам «(null)». Важным моментом здесь является то, как выбирать данные, опираясь на этот столбец.
Представим, что перед нами стоит задача вывести все блюда, которые не готовятся в нашей столовой, то есть те, где не указан повар (CookID). Например, пирожное Тирамиссу повара нашей платной столовой сами не готовят. Оно закупное из продовольственной базы. Или, например, питьевая минеральная вода (не попала на скриншоте выше, не уместилась, так как отображены не все строчки). Ее также привозят в больших объемах в маленьких бутылочках и продают в столовой.
Итак, чтобы отобрать строчки из таблицы, где значение в это графе пустое, мы не можем написать запрос вида:
Вернее можем, но при попытке выполнить его, получим пустой набор данных. Все из–за того, что при сравнении с NULL операторы «равно» и «не равно» не допустимы. Результат всегда будет «Ложь».
Чтобы выбрать блюда столовой с проверкой на NULL в столбце CookID, необходимо использовать оператор IS или IS NOT. Корректируем предыдущий пример:
И вот они, данные:
Мы отобрали все блюда, у которых в графе CookID пусто (NULL). Если бы нам нужно было, наоборот, вывести блюда, у которых CookID указан, то мы бы написали:
Получить данные по блюдам, у которых указан CookID мы можем, также и запросом:
Строчки с пустым CookID не попадут в результирующий набор данных. Данная команда даст результат (мы увидим список блюд, у которых указан идентификатор повара), так как в нашем запросе значение графы CookID сравнивается с 0, а не с NULL. И поэтому можно обойтись и без IS или IS NOT. Только если нам нужно отобрать строки с пустым CookID, то тут, конечно, без IS NULL не обойтись.
С NULL не только нельзя сравнивать с помощью операторов «равно» и «не равно», но и нельзя применять арифметические операции, иначе результатом будет NULL. Ниже один из любимых вопросов на собеседовании по SQL на понимание принципов работы с NULL:
Правильным вариантом является только номер 5.
Обрати внимание, что в заголовке этой темы, я во фразе «пустые» значения слово «пустые» взял в кавычки. Это для того, чтобы показать тебе, что хоть при наличии значения NULL в каком-то столбце некоторой записи пусто, NULL обычно трактуется не сколько как пустота, сколько как неопределенность. Вот поэтому при прямом сравнении NULL = NULL будет ответ ложь (false), так как одна неопределенность не может быть равна другой неопределенности, ведь мы не знаем, что скрывается ни под одной из неопределенностей.
Практические задачи №1
1. Написать запрос, выводящий список сотрудников организации, упорядоченный по ФИО.
2. Написать запрос, выводящий список сотрудников организации, упорядоченный по дате рождения. Чтобы сначала вывелись самые молодые сотрудники.
3. Вывести список филиалов.
4. Вывести список блюд столовой, цена которых больше 80 рублей.
5. Вывести список сотрудников, работающих в филиале 1 или 2.
6. Вывести список сотрудников, работающих в первом филиале, родившихся не ранее 01.01.1980.
7. Вывести список сотрудников, фамилия которых начинается на букву «И».
8. Вывести список блюд столовой, цена которых в диапазоне от 70 до 100 рублей. Результат упорядочить по цене, по убыванию.
9. Вывести список блюд столовой, которые готовятся в собственной столовой организации, а не закупаются. У таких строчек указан повар (в столбце CookID присутствует идентификатор повара). Результат отсортировать по наименованию блюда.
Решения практических задач №1 на следующей странице.
Решение практических задач №1
1. Написать запрос, выводящий список сотрудников организации, упорядоченный по ФИО.
2. Написать запрос, выводящий список сотрудников организации, упорядоченный по дате рождения. Чтобы сначала вывелись самые молодые сотрудники.
3. Вывести список филиалов.
4. Вывести список блюд столовой, цена которых больше 80 рублей.
5. Вывести список сотрудников, работающих в филиале 1 или 2.
6. Вывести список сотрудников, работающих в первом филиале, родившихся не ранее 01.01.1980.
7. Вывести список сотрудников, фамилия которых начинается на букву «И».
8. Вывести список блюд столовой, цена которых в диапазоне от 70 до 100 рублей. Результат упорядочить по цене, по убыванию.
9. Вывести список блюд столовой, которые готовятся в собственной столовой организации, а не закупаются. У таких строчек указан повар (в столбце CookID присутствует идентификатор повара). Результат отсортировать по наименованию блюда.
Ещё больше практики и интересных задач в моем Telegram канале: https://t.me/sql_oracle_databases
9. Соединения таблиц с помощью JOIN
9.1. Что такое соединения. Назначение соединений
Почти всегда при выборке из базы данных недостаточно информации одной таблицы. Часто нужно выводящиеся данные одной таблицы дополнять сведениями из другой.
При выполнении запроса SELECT из таблицы продаж, среди прочих, мы получим столбцы «артикул реализованного товара» и «количество». В отчете, для которого мы составляем SQL–запрос, необходимо отображать не только артикул, но еще и название товара и даже единицу измерения. Но что же делать, эти сведения отсутствуют в таблице продаж. Эти данные лежат в совсем другой таблице – в таблице «товары»!
Для того, чтобы в результате выполнения запроса получить больше данных о товаре, (чтобы выводился не только его артикул), но еще и наименование и единица измерения, мы к данным из таблицы «продаж» добавим данные из таблицы «товары». Каждая выводящаяся строчка из таблицы продаж дополнится столбцами из таблицы «товары», того товара, артикул которго указан в выводящейся строчке из таблицы «продаж».
Для того, чтобы к данным, выводящимся из одной таблицы, уметь добавлять информацию из другой, в языке SQL существуют соединения (джоины) таблиц. Рассмотрим типы соединений.
9.2. LEFT JOIN. Левое внешнее соединение
При таком типе соединиения, данные из таблицы, которая указана в SQL–запросе левее (относительно фразы LEFT JOIN) будут выведены все. Запомнить – просто!
К этим данным будут присоединены данные второй таблицы, расположенной в запросе правее. Поясню на примере. Пусть нам нужно вывести сотрудников и их автомобили. Так как цель задачи в первую очередь вывести сотрудников и во втором столбце вывести их автомобили (при их ниличии), то выборка будет именно из таблицы сотрудников. И уже к выводящимся сотрудникам приджоиним их автомобили. Давайте рассмотрим таблицы:
В таблице «Автомобили сотрудников» столбец «ID» – это всего лишь сквозной идентификатор, первичный ключ (номер машины по-порядку). Он нас сейчас не интересует. А вот столбец «ID_PERS» – это внешний ключ, ссылающийся на таблицу «Сотрудники». Он нам сейчас будет нужен. Другими словами, в графе «ID_PERS» указан идентификатор сотрудника, которому принадлежит автомобиль. Согласно нему видно, что BMW X3 и Ford Mondeo принадлежат Петровой Надежде Анатольевне. А у Первого Николая Николаевича, сотрудника с идентификатором 4, нет ни одного автомобиля.
Конец ознакомительного фрагмента.
Текст предоставлен ООО «ЛитРес».
Прочитайте эту книгу целиком, купив полную легальную версию на ЛитРес.
Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.