Тест для молодого бойца PostgreSQL

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

В интернете я редко встречал тесты, которые охватывают типичные рабочие моменты и тонкости, связанные с обработкой данных.

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

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

Приступим!

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

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

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

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

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

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

 SELECT * FROM any_table WHERE parent_id = parent_id;

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

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

4. Какие результаты будут у запросов?

-- А)
SELECT * FROM (
    VALUES (1),
           (1)
    ) x(y)
UNION
(
    SELECT 2
    UNION ALL
    SELECT 2
);
-- Б)* что покажет данный "простой" запрос
SELECT wtf_ FROM pg_stat_activity AS wtf_ ;

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

6. Имеется таблица «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

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

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

SELECT * INTO wtf FROM pg_stat_activity;

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

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

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

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

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

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

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

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

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

Пользователь 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 или COMMIT? Какие результаты получит User_2 в этих двух случаях?

Заключение

Чертова дюжина пунктов подошла к концу.

P.S. Решил пока опубликовать без ответов, чтобы было немного интереснее. Открою их завтра немного позже…

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

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

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

Автор: postgres

Источник