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

в 6:37, , рубрики: postgres, postgresql, sql, tips and tricks, Администрирование баз данных, базы данных, Программирование, СУБД, тестовые задачи, тесты

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


Приветствую всех любителей 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;

Ответы на 1-ый блок

временно_скрыт

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

value
5
5
NULL
5
5

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

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

Варианты ответов на 2-ой вопрос

  • -4
  • 0
  • NULL
  • 5
  • Вызовет ошибку, т.к. не указан GROUP BY
  • Ни один из перечисленных

Ответ на 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;

Ответы на 3-ий блок

временно_скрыт

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

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

Ответы на 4-ый блок

временно_скрыт

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

Ответ на 5-ый вопрос

временно_скрыт

6. Имеется таблица «table_6» с текстовым столбцом «X». В таблице содержится одна строка:

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

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

Ответы на 6-ой блок

временно_скрыт

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

Ответ на 7-ой вопрос

временно_скрыт

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

SELECT * INTO wtf FROM pg_stat_activity;

Ответ на 8-ой вопрос

временно_скрыт

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

Ответ на 9-ый вопрос

временно_скрыт

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

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

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

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

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

Ответ на 10-ый вопрос

временно_скрыт

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

Ответ на 11-ый вопрос

временно_скрыт

Бонусное задание для тех, кто справился

Круто, если есть готовая функция
А сможете преобразовать наоборот? (желательно не теряя отступов)
'иВАнОв ИВан иВановиЧ' преобразовать к 'иВАНОВ иВАН иВАНОВИЧ'
а инвертировать регистр?

Ответ на бонусное задание

временно_скрыт

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

Ответ на 12-ый вопрос

Вопрос может показаться примитивным, однако…
В самом начале изучения SQL, у меня складывалось мнение, что этот оператор может только показывать данные, но:

Помимо того, что SELECT способен заблокировать таблицу на изменение (BEGIN; SELECT… FOR UPDATE)

SELECT способен вызывать функции, которые могут выполнять практически любые манипуляции.
Новичкам нужно это понимать сразу, а не после выполнения «маленького информационного» запроса на Production-сервере

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 в этих двух случаях?

Ответы на 13-ый блок

временно_скрыт

Заключение

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

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

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

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

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

Автор: postgres

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js