Задачи и решения для бойца PostgreSQL

Приветствую всех любителей SQL!

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

Публикация несет образовательный характер, и является продолжением моей прошлой статьи Курс молодого бойца PostgreSQL.
Подойдет для тех, кто хорошо освоил все базовые навыки и хочет развиваться дальше.

Представленные ответы подходят для PostgreSQL (большинство задач подойдут и для других СУБД, но результаты и решения могут быть иными. Даже интересно, где возникнут отличия)

Постарайтесь ответить самостоятельно, перед открытием спойлера.

Поехали!

Буду стараться помечать звездочкой, что-то сугубо для PostgreSQL* (таких моментов не много)

1. Немного о числовых операциях

1.1 Выполнятся ли эти запросы? Какие результаты они вернут?

-- А) Начнем с простого запроса
SELECT 3/2;
-- Б)
SELECT min('Какой-то текст'::TEXT) , avg('Какой-то текст'::TEXT);
-- В)* Почему данный запрос может вернуть FALSE, возможно ли такое поведение СУБД?
SELECT 7.2 = (3.8::FLOAT + 3.4)
-- Г)
SELECT (20/25)*25.0;

1.2 Дана таблица "table_2" (с единственным столбцом "value"(INTEGER)) состоящая из следующих 5 строк:

Какой результат вернет запрос:

SELECT (avg(value)*count(*)) - sum(value) FROM table_2;

2. Общие вопросы

2.1 В каких случаях запрос может вернуть не всё содержимое таблицы? (parent_id INTEGER, таблица наполнена разнообразными данными)

 SELECT * FROM any_table WHERE parent_id = parent_id;

А как поведет себя запрос ниже? Какие данные он выведет? *PostgreSQL

 SELECT * FROM any_table WHERE parent_id IS NOT DISTINCT FROM parent_id;

2.2. Какой результат будет у запроса?

-- А)
SELECT * FROM (
    SELECT 1
    UNION ALL
    SELECT 1
    ) x(y)
UNION
(
    SELECT 2
    UNION ALL
    SELECT 2
);

2.3 Напишите запрос, который покажет завтрашнюю дату.

2.4 Операторы UPDATE, DELETE , INSERT и MERGE созданы для манипулирования данными в таблицах. А является ли выполнение SELECT .. «безопасным»? Может ли какой-либо запрос повлиять на данные в таблице?

3. Only PostgreSQL

3.1 Опишите, что произойдет при выполнении данного запроса в SQL диалоге:

SELECT * INTO wtf FROM pg_stat_activity;

CREATE TABLE wtf AS
 SELECT * FROM pg_stat_activity;

3.2 что покажет данный «простой» запрос

SELECT wtf_ FROM pg_stat_activity AS wtf_ ;

4. Работа с текстом. Регулярные выражения

Думаю нужно уметь не только строить запросы, но и представлять результаты в нужном виде.
Регулярные выражения — это отдельная огромная тема, со множеством качественных статей. Поэтому я лишь покажу примеры, без подробных объяснений.

4.1. Допустим, есть таблица "table_5" с текстовым столбцом "X" и множеством разнообразных строк. Каким запросом можно получить любые последние 10 символов каждой строки?

4.2 Имеется таблица «table_6» с текстовым столбцом «X». В таблице содержится одна строка(весь текст только на английском и русском языке):

'Lorem 3 Ipsum 23 standard 7 dummy 17 text Ultimate Answer of Life ?? 777'

А) Напишите запрос, который вернет символы с 42-го по 68-ый из этой строки
Б) Как вытащить только ЗАГЛАВНЫЕ (русские или английские) буквы в строке с помощью SQL?
В) Как посчитать сумму чисел (не цифр) в строке с помощью SQL

WITH table_6(X) AS(
    SELECT 'Lorem 3 Ipsum 23 standard 7 dummy 17 text Ultimate Answer of Life ?? 777'::TEXT
    )
SELECT X FROM table_6

 -- ТУТ должен быть WITH из "SQL наброска"
-- А)
SELECT SUBSTRING(LEFT(X,68) FROM 42 ) FROM table_6 -- 1 вариант
SELECT SUBSTRING(X, 42, (68-42)+2) FROM table_6   -- 2 вариант
-- 3 вариант с Вас

-- Б) Все просто, заменяем всё кроме заглавных букв на пустоту
 SELECT regexp_replace(X,'[^A-ZА-ЯЁ]', '','g') FROM table_6 
-- Буква 'Ё' обычно не входит в диапазон А-Я
-- без параметра 'g' замена произойдет лишь 1 раз

-- В) Без регулярных выражений задача может показаться кошмаром
-- С помощью regexp_matches и жадного поиска получаем массивы чисел в столбик, и суммируем вытащив из массива
-- *возможно в других СУБД функции могут иначе называться
SELECT sum(x[1]::INT) 
    FROM (
              SELECT regexp_matches(X,'[0-9]+','g') FROM table_6
              ) AS y(x)

4.3 Как заменить в тексте (ячейке таблицы) все двойные (тройные и более) пробелы на одинарный пробел? (по традиции: таблица "table_7" со столбцом "X") (P.S. достаточно будет написать SELECT возвращающий нужный результат, а не UPDATE table_7 ...)

WITH table_7(X) AS (SELECT 'Lorem     3    Ipsum 23  standard  7     
  dummy    11
   text'::TEXT)
-- 1 вариант. Заменяем только пробелы (2 и более подряд)
SELECT regexp_replace(X, '( ){2,}', ' ', 'g') FROM table_7

-- 2 вариант. Заменяем все пробельные символы (табуляция, неразрывный пробел, перевод строки и т.д.) на один пробел, даже если эти символы чередуются
SELECT regexp_replace(X, 's+', ' ', 'g') FROM table_7 

-- Отчаянный вариант! Думаю те, кто когда-либо искал решение подобной задачи, натыкались на такое "изящное" решение. Без использования регулярных выражений..
-- Работает весьма хитро, на любом количестве пробелов, главное, чтобы текст не содержал используемых подстановочных символов
-- Не рекомендую такое использовать, но для разминки ума стоит понять, как оно работает
SELECT replace(replace(replace(X, ' ', '<>'), '><', ''), '<>', ' ') FROM table_7

4.4 Имеется строка "X" в которой допущены опечатки. Вместо русских букв (е, о, с, С ) были использованы внешне похожие на них символы английского алфавита. Произведите замену данных символов с помощью SQL.

P.S. Строка должна содержать только русские символы, и переживать за возможное изменение английских слов не стоит.

(Если возникают трудности с заменой всех символов, то замените хотя бы один)

Пример строки:

X = 'Cтрoитeльствo или рeкoнcтрукция oбъeкта'

-- Несомненно, Replace(Replace(Replace(.. потрясающее решение, но
-- специальная функция гораздо изящнее для такого случая (1 символ на 1 символ)
-- *возможно TRANSLATE имеет другое название в других СУБД

SELECT TRANSLATE('Cтрoитeльствo или рeкoнcтрукция oбъeкта', 'Cceo', 'Ссео')

4.5 Напишите запрос, который преобразует строку:
'иВАнОв ИВан иВановиЧ' к виду 'Иванов Иван Иванович'

-- Все просто, когда имеется такая функция
SELECT initcap('иВАнОв  ИВан  иВановиЧ')
*Возможно в других СУБД имеются аналоги

SELECT string_agg(LOWER(LEFT(x,1)) || UPPER(SUBSTRING(x from 2)), ''  ORDER BY rn) 
    FROM 
  (SELECT * FROM regexp_split_to_table('  иВАнОв  ИВан  иВановиЧ
                           4 TesT', 'y') WITH ORDINALITY y(x, rn) ) AS z
-- *Решение для PostgreSQL, но смысл везде такой же
-- Придумал на скорую руку, без использования процедурного языка
-- возможно неуклюжее и не производительное решение, но для данного примера подходит
-- сохраняет все пробельные символы между словами.
-- WITH ORDINALITY нужен для сохранения порядка символов (доступен с версии 9.4)

-- Разбираем строку по границам слова
-- получаем отдельно строки со словами и разделяющими их символами
-- обрабатываем..

5. Чуть-чуть о транзакциях

Транзакции очень важная вещь в СУБД, достаточно важно понимать основные моменты.

Попробую смоделировать пример:

Допустим, есть таблица «goods» с которой собираются работать два пользователя.
В ней имеется целочисленный столбец discount равный 10 для всех строк.
Настройки базы данных стандартные (READ COMMITTED — чтение зафиксированных данных).

Пользователь User_1 открывает транзакцию, выполняет следующий запрос:

BEGIN;
UPDATE goods
SET discount = discount + 5;

Секундой позже, другой пользователь (User_2)
Выполняет без открытия транзакции почти такой же запрос:

UPDATE goods
SET discount = discount + 10;

Как думаете, что произойдет при следующих раскладах:

А) Какой результат получит User_2, если User_1 оставит транзакцию открытой (т.е. не подтвердит транзакцию / не откатит изменения)?
Что увидит User_1 при запросе:

SELECT discount FROM goods LIMIT 1;

Б) Что произойдет, если User_1 сделает ROLLBACK? Какие результаты получит User_2?

В) Что произойдет, если User_1 сделает COMMIT? Какие результаты получит User_2?

Заключение

Думаю, что затронул достаточно интересные моменты.

Надеюсь задачи помогут промотивировать начинающих, ведь скучно что-либо изучать без конкретных целей/задач/направлений.

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

Жду каких-либо дополнений, решений особо интересных задач(можно своих) и прочих комментариев!

Спасибо за внимание! Желаю успехов в изучении SQL!

Автор: postgres

Источник