PostgreSQL: случайно решил пройти неизвестный базовый тест в интернете 

Как я случайно стал участником теста по PostgreSQL

Однажды, в очередной раз серфя просторы интернета в поисках чего-нибудь интересного (или просто прокрастинируя, но это уже другая история), я наткнулся на любопытный материал по PostgreSQL. «А почему бы не освежить знания?» — подумал я, и через пару минут уже оказался на странице с тестированием. Тест обещал базовый уровень, всего 10 вопросов, и был посвящён таким темам, как создание и модификация таблиц, отношения между ними и формирование запросов. В общем, всё то, что должно быть в арсенале любого уважающего себя SQL-шника.

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

Дисклеймер

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

Администрация, запросила разъяснения у заявителя. Но тут мы сталкиваемся с привычной практикой, когда сначала блокируют, а вопросы начинают задавать уже потом. В общем, классика жанра.

Поэтому, чтобы оставаться на связи и быть в курсе новых материалов, приглашаю вас подписаться на мой Telegram-канал. Там никто ничего не банит, и свежая информация всегда под рукой!

Вопрос 1 из 10

Какой тип данных нужно использовать на месте пропуска [...], если планируется хранить JSON-строки, при условии что запросы на извлечение данных будут простыми?

Варианты ответа:

1. text::json
2. text check (json_field is json)
3. varchar(json)
4. json
5. jsonb

Обоснование:

• text::json: Такой синтаксис не поддерживается в SQL (например, PostgreSQL), так как JSON не является подтипом text.
• text check (json_field is json): Этот подход требует добавления ограничения, которое вручную проверяет формат JSON, что неэффективно. Встроенные типы json или jsonb уже предоставляют встроенную валидацию и оптимизацию.
• varchar(json): Такой вариант синтаксиса не существует, так как JSON — это отдельный тип данных, а не подтип varchar.
• json: Этот тип данных используется для хранения JSON-объектов. Он идеально подходит для простых операций извлечения данных, так как хранит данные в исходном текстовом формате без дополнительной обработки.
• jsonb: Этот тип данных также используется для хранения JSON, но в бинарном формате. Он предоставляет больше возможностей для индексации и сложных операций, но может быть избыточным для простых запросов.

Правильный ответ:

json

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

Вопрос 2 из 10

Чем отличается тип данных char(n) от varchar(n)?

Варианты ответа:

1. При вставке записей для строк char используются одинарные кавычки, а для varchar — двойные.
2. Скорость поиска строковых шаблонов данных char(n) выше, чем varchar(n).
3. С типом данных varchar невозможно использовать запросы с оператором LIKE.
4. Тип данных varchar(n) и char(n) имеют различия в объеме памяти, занимаемой строками длиной (x < n).
5. Тип данных char(n) не позволяет хранить строку длиной n в отличие от varchar(n).

Обоснование:

• Вариант 1: Некорректно. Использование одинарных или двойных кавычек в SQL не связано с типом данных, а определяется синтаксисом SQL.
• Вариант 2: Частично верно, но не всегда. Скорость зависит от конкретной реализации и объема данных. В современных системах разница минимальна.
• Вариант 3: Некорректно. Оператор LIKE работает с обоими типами данных, включая varchar.
• Вариант 4: Верно. char(n) всегда занимает фиксированное количество памяти (n), даже если строка короче, тогда как varchar(n) занимает только необходимое количество памяти, что делает его более эффективным для хранения коротких строк.
• Вариант 5: Некорректно. Оба типа данных могут хранить строки длиной до n, но способ хранения различается.

Правильный ответ:

Тип данных varchar(n) и char(n) имеют различия в объеме памяти, занимаемой строками длиной (x < n).
char(n) использует фиксированный объем памяти, а varchar(n) — динамический, что делает последний более гибким для строк переменной длины.

Вопрос 3 из 10

Какую строчку нужно добавить на месте пропуска [...], чтобы при создании таблицы Person для колонки age действовало ограничение: все значения в колонке строго больше нуля?

Варианты ответа:

1. integer positive
2. integer > 0
3. integer check > 0
4. integer check (age > 0)
5. integer unsigned

Обоснование:

• integer positive: Некорректно, так как SQL не поддерживает такой синтаксис для типа данных.
• integer > 0: Некорректно, так как это не синтаксис SQL для указания ограничения на значения столбца.
• integer check > 0: Некорректно, так как синтаксис check требует указания конкретного выражения в скобках.
• integer check (age > 0): Верно. SQL поддерживает добавление ограничения через ключевое слово check, где указывается условие. В данном случае, условие (age > 0) гарантирует, что все значения будут больше нуля.
• integer unsigned: Некорректно, так как PostgreSQL (и другие системы) не поддерживают unsigned для типа integer. Этот синтаксис применим в MySQL, но только для ограничения на ненулевые значения, а не для указания строгого условия > 0.

Правильный ответ:

integer check (age > 0)
Это стандартный синтаксис для добавления ограничения на значения в колонке.

Вопрос 4 из 10

В таблице some_table в колонке id используется тип данных serial. Вам нужно изменить его на bigserial. Какая команда позволит это сделать?

Варианты ответа:

1. alter table some_table update column id with type bigserial
2. alter table some_table set column id type bigserial
3. alter column some_table.id set type bigserial
4. alter table some_table change column 'id' type to bigserial
5. alter table some_table alter column id type bigserial

Обоснование:

• alter table some_table update column id with type bigserial: Некорректно. SQL не поддерживает конструкцию update column в контексте изменения типа данных.
• alter table some_table set column id type bigserial: Некорректно. Синтаксис set column не применяется для изменения типа столбца.
• alter column some_table.id set type bigserial: Некорректно. Сначала указывается таблица, затем применяется команда alter column для конкретного столбца.
• alter table some_table change column 'id' type to bigserial: Некорректно. Синтаксис change column используется в MySQL, а не в PostgreSQL.
• alter table some_table alter column id type bigserial: Некорректно. PostgreSQL не позволяет напрямую менять тип serial на bigserial, так как это не просто тип данных, а совокупность типа bigint и последовательности (sequence). Нужно сначала вручную отвязать последовательность и удалить столбец.

Правильный ответ:

Из предложенных вариантов в списке нет корректного ответа, так как PostgreSQL не позволяет напрямую изменить тип serial на bigserial. Тем не менее, если необходимо выбрать наиболее близкий к корректному синтаксису ответ, то:

Правильный ответ: alter table some_table alter column id type bigserial

Хотя эта команда сама по себе работать не будет, она ближе к реальному синтаксису SQL, чем остальные варианты.

Для изменения типа с serial на bigserial в PostgreSQL следует вручную отвязать последовательность, удалить столбец и создать новый.

Пример команд:

Вопрос 5 из 10

В существующую таблицу person необходимо добавить колонку fullname. Колонка должна быть конкатенацией двух полей — firstname и secondname, разделенных пробелом. Какая команда позволит этого достичь?

Варианты ответа:

1. alter table person add column fullname text generated by (concat(fullname, ' ', secondname))
2. alter table person add column fullname text generated always as (firstname || ' ' || secondname) stored
3. alter table person add column fullname '$firstname $secondname'
4. alter table person add column fullname text generated from concat(fullname, ' ', secondname)
5. update table person add column fullname text as fullname || ' ' || secondname

Обоснование:

• Вариант 1: Некорректно. Синтаксис generated by не поддерживается в SQL (например, PostgreSQL или MySQL).
• Вариант 2: Верно. В PostgreSQL команда generated always as используется для создания вычисляемых колонок. Оператор || применяется для конкатенации строк.
• Вариант 3: Некорректно. Такой синтаксис не существует; строки в SQL не добавляются в колонку напрямую в виде шаблонов.
• Вариант 4: Некорректно. Конструкция generated from отсутствует в SQL.
• Вариант 5: Некорректно. Оператор update используется для изменения данных, но не подходит для добавления вычисляемых колонок.

Правильный ответ:

alter table person add column fullname text generated always as (firstname || ' ' || secondname) stored

Это корректный способ добавить вычисляемую колонку в PostgreSQL, которая автоматически объединяет значения полей firstname и secondname, разделяя их пробелом.

Вопрос 6 из 10

Вы создали таблицу some_table. После чего вы выполнили команду. Что НЕ будет удалено автоматически?

Варианты ответа:

1. Индексы
2. Последовательность my_sequence
3. Представления
4. Триггеры
5. Ссылающиеся записи в других таблицах

Обоснование:

• Индексы: При удалении колонки с ключом или уникальным ограничением индексы, связанные с этой колонкой, удаляются автоматически.
• Последовательность my_sequence: Последовательность, связанная с колонкой через default nextval(), НЕ будет удалена автоматически. Она продолжит существовать в базе данных.
• Представления: Если представления зависят от удаляемой колонки, они будут затронуты командой CASCADE и удалены.
• Триггеры: Триггеры, привязанные к колонке, также будут удалены.
• Ссылающиеся записи в других таблицах: Если другие таблицы ссылаются на эту колонку через внешние ключи, они будут обработаны с использованием CASCADE.

Правильный ответ:

Последовательность my_sequence
Автоматически связанная последовательность не удаляется при удалении колонки и требует явного удаления, если она больше не нужна.

Вопрос 7 из 10

На сайте есть каталог товаров, отсортированных по убыванию цены. С помощью комбинации каких операторов можно организовать пагинацию?

Варианты ответа:

1. order by, limit, where
2. limit, where
3. limit, offset
4. limit, offset, order by
5. offset, having

Обоснование:

• order by, limit, where: Некорректно. Оператор where используется для фильтрации данных, но он не влияет на пагинацию.
• limit, where: Некорректно. Пагинация требует указания смещения (offset) для пропуска записей, что отсутствует в этом варианте.
• limit, offset: Частично верно. Эти операторы позволяют ограничить количество записей и пропустить определенное количество строк. Однако для сортировки (например, по убыванию цены) необходим order by.
• limit, offset, order by: Верно. Эта комбинация позволяет сортировать записи (order by), ограничивать количество выводимых записей (limit) и пропускать определенное количество строк для реализации пагинации (offset).
• offset, having: Некорректно. Оператор having используется для фильтрации агрегированных данных и не имеет отношения к пагинации.

Правильный ответ:

limit, offset, order by
Эта комбинация используется для сортировки, ограничения количества строк и смещения записей, что является основой для реализации пагинации.

Вопрос 8 из 10

Таблица была заполнена 10 тысячами записей, данные не удалялись, колонка id устанавливалась в дефолтное значение. Какие id мы получим в результате следующего запроса?

Варианты ответа:

1. 899, 900, 901, 902
2. Невозможно узнать заранее
3. 901, 902, 903
4. 900, 901, 902, 903
5. 899, 900, 901

Обоснование:

• Команда order by id упорядочивает записи по значению столбца id в возрастающем порядке.
• Ключевое слово offset 900 пропускает первые 900 строк таблицы.
• Ключевое слово limit 3 возвращает следующие 3 строки после пропущенных.

Так как колонка id имеет тип serial и данные не удалялись, значения id идут последовательно от 1 до 10000.

После пропуска 900 строк следующими тремя записями будут записи с id = 901, 902, 903.

Правильный ответ:

901, 902, 903

Запрос вернет три строки, начиная с 901-го значения.

Вопрос 9 из 10

Вы создаете таблицу. Какое минимальное ограничение нужно вставить на месте пропуска [ ... ], чтобы возможно было использовать reference на эту колонку из другой таблицы?

Варианты ответа:

1. foreign key
2. serial primary
3. unique
4. unique, not null
5. not null

Обоснование:

• foreign key: Это ограничение применяется в связанной таблице, а не в основной. Оно указывает на связь между таблицами, но не используется для определения ограничений в исходной таблице.
• serial primary: Это сочетание автоматически создает первичный ключ (primary key) для колонки, который подразумевает уникальность и отсутствие NULL. Это корректно, но не является минимальным требованием.
• unique: Указывает, что значения в колонке должны быть уникальными. Однако для создания внешнего ключа недостаточно — требуется также запрет NULL.
• unique, not null: Это минимальное ограничение. Оно гарантирует, что значения в колонке будут уникальными и не NULL, что является обязательным для использования в качестве внешнего ключа.
• not null: Это ограничение лишь запрещает NULL, но не обеспечивает уникальность, необходимую для ссылки.

Правильный ответ:

unique, not null

Для использования колонки в качестве ссылки (reference) требуется, чтобы она была уникальной и не содержала NULL.

Вопрос 10 из 10

Что нужно вставить на место [ ... ], чтобы предотвратить какие-либо действия в зависимой таблице при удалении связанных строк в главной таблице и генерировать ошибку?

Варианты ответа:

1. ON DELETE SET NULL
2. ON UPDATE RESTRICT
3. ON UPDATE SET DEFAULT
4. ON DELETE NO ACTION
5. ON DELETE CASCADE

Обоснование:

• ON DELETE SET NULL: При удалении строки в главной таблице связанные строки в зависимой таблице получают значение NULL в соответствующей колонке. Это не предотвращает действие.
• ON UPDATE RESTRICT: Это ограничение относится только к обновлению значений и не связано с удалением записей.
• ON UPDATE SET DEFAULT: Аналогично, это применяется для обновлений, но не блокирует удаление.
• ON DELETE NO ACTION: Верно. Это предотвращает любые действия в зависимой таблице и генерирует ошибку, если в главной таблице пытаются удалить строку, на которую есть ссылка.
• ON DELETE CASCADE: Это автоматически удаляет связанные строки в зависимой таблице, что не соответствует требованию предотвратить действия.

Правильный ответ:

ON DELETE NO ACTION

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

Заключение: Тест — пройден, любопытство — разогрето

Ну что ж, вот и подошёл к концу мой импровизированный экскурс в мир базовых вопросов по PostgreSQL.

А теперь, в лучших традициях интернет-странствий, пойду дальше бороздить просторы сайтов в поисках новых тестов. Кто знает, вдруг наткнусь на ещё один шедевр, который предложит проверить мои знания или, возможно, даже подкинет пару новых фишек по PostgreSQL. А вдруг и MySQL подтянется к вечеринке?

Так что оставайтесь на связи — где-то там, за поворотом, уже ждёт очередной случайный тест и новый повод для сарказма. До встречи в следующей серии! 😊