Задачи и решения для бойца 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 (     VALUES (1),            (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 length(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!