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

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

Задачи

SQL

Задание 1.

Есть две таблицы: A и B.

Сколько строк получится при следующих запросах:

SELECT * FROM A LEFT JOIN B ON A.Name = B.Name
SELECT * FROM A RIGHT JOIN B ON A.Name = B.Name
Решение

1) 10 строк

2) 8 строк

Задание 2.

Вывести идентификаторы пользователей и последнюю пару вопрос-ответ по каждому пользователю, где есть текстовый ответ ассистента.

import duckdb as db

conn = db.connect()
conn.execute('''
-- Есть таблица, в которой хранятся реквесты и респонсы:

CREATE TABLE MESSAGES (
ID           integer      NOT NULL,
MESSAGE_ID   integer      NOT NULL,
USER_ID      varchar(10)  NOT NULL,
MESSAGE_TYPE varchar(10)  NOT NULL,
QUERY        varchar(40)  NOT NULL,
CREATED      timestamp    NOT NULL)
;

INSERT INTO MESSAGES VALUES
(1,	111, 'user_1',	'REQUEST',	'привет', '2021-07-21 12:51:39.0000'),
(2,	222, 'user_2',	'REQUEST',	'перевод', '2021-07-21 12:52:03.0000'),
(3,	111, 'user_1',	'RESPONSE',	'Приветствую тебя!', '2021-07-21 12:52:48.0000'),
(4,	333, 'user_3',	'REQUEST',	'включи yputube', '2021-07-21 12:53:57.0000'),
(5,	444, 'user_1',	'REQUEST',	'как дела', '2021-07-21 13:05:13.0000');
''')
conn.query('''
# Ваш код

''').df()
Решение
conn.query('''
WITH LastRequest AS (SELECT USER_ID, MESSAGE_ID, QUERY, CREATED, ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY CREATED DESC) AS row_num
FROM MESSAGES
WHERE MESSAGE_TYPE = 'REQUEST')

SELECT lr.USER_ID, lr.MESSAGE_ID, lr.QUERY AS LAST_Request, res.QUERY AS Last_Response
FROM LastRequest lr
JOIN (
  SELECT MESSAGE_ID, QUERY
  FROM MESSAGES
  WHERE MESSAGE_TYPE = 'RESPONSE'
) res
ON lr.MESSAGE_ID = res.MESSAGE_ID
WHERE lr.row_num = 1
ORDER BY lr.USER_ID;
''').df()

Задание 3.

Есть две таблицы:

Orders (order_id – PK, promocode_id – FK) - заказы

Promocodes (promocode_id – PK, name – UQ, discount) – промокоды

Вопросы:

  1. Какая доля заказов с промокодами?

  2. Какой самый популярный промокод (название) и число его использований?

Решение

Ответ на вопрос №1:

SELECT p.name AS name_promocode, COUNT(*) AS usage_count
FROM orders o
JOIN promocodes p ON o.promocode_id = p.promocode_id
GROUP BY p.promocode_id, p.name
ORDER BY usage_count DESC
LIMIT 1

Ответ на вопрос №2:

SELECT p.name AS name_promocode, COUNT(*) AS usage_count
FROM orders o
JOIN promocodes p ON o.promocode_id = p.promocode_id
GROUP BY p.promocode_id, p.name
ORDER BY usage_count DESC
LIMIT 1

Задание 4.

Если в таблице "А" 10 строк, а в таблице "Б" – 100 строк. Сколько строк будет в SELECT
(id – PK)?

 SELECT * FROM A LEFT JOIN B ON A.id = B.id
Решение

Количество строк в результате может варьироваться от 10 до 1000. Поскольку мы сравниваем строки по id, возможно, что в таблице «B» будут строки с такими же id, как и в таблице «A».

Задание 5.

Напишите SQL-запрос, чтобы из таблицы изъять имена, которые начинаются на букву А.

Решение
SELECT name FROM table WHERE name LIKE "A%";

или

SELECT name FROM table WHERE LOWER(name) LIKE "a%";


Вопросы

SQL

  1. Назовите виды JOIN.

Ответ

Существуют следующие типы соединений (5 видов):

  1. INNER JOIN или просто JOIN - внутреннее соединение. В результате остаются только те строки, для которых нашлось соответствие. До сих пор мы использовали только этот тип соединений.

  2. LEFT JOIN - левое внешнее соединение. Работает как JOIN, но если для строки таблицы, находящейся по левую сторону ключевого слова LEFT JOIN, не нашлось ни одной строки в таблице, находящейся по правую сторону LEFT JOIN, то строка все равно добавляется в результат, а значения столбцов правой таблицы равны null.

  3. RIGHT JOIN - правое внешнее соединение. Работает как JOIN, но если для строки таблицы, находящейся по правую сторону ключевого слова RIGHT JOIN, не нашлось ни одной строки в таблице, находящейся по левую сторону RIGHT JOIN, то строка все равно добавляется в результат, а значения столбцов левой таблицы равны null.

  4. FULL JOIN - полное внешнее соединение. Если для какой-либо из таблиц не нашлось строки в другой таблице, то строка все равно попадает в результат, а значения столбцов другой таблицы равны null.

  5. CROSS JOIN - перекрестное (или декартово) произведение. Каждая строка одной таблицы соединяется с каждой строкой второй таблицы, давая тем самым в результате все возможные сочетания строк двух таблиц. Аналогичного результата можно достичь просто перечислив таблицы в FROM через запятую.

  1. Что такое Primary Key и какой он может быть (из чего состоять)?

Ответ

Первичный ключ (англ. primary key) — это специальное поле (или набор полей) в таблице базы данных, которое уникально идентифицирует каждую запись (строку) в этой таблице. Простым языком, PK — это уникальный "идентификационный номер" для каждой строки в таблице, который не может повторяться.
Он может быть одиночным, т.е. состоять из 1 столбца, а может быть составным, т.е. состоять из 2 и более столбцов.

Основные характеристики первичного ключа:

  1. Уникальность: Значение первичного ключа должно быть уникальным для каждой строки в таблице. Никакие две строки не могут иметь одно и то же значение PK.

  2. Не может быть пустым: Значение первичного ключа не может быть NULL.

  3. Идентификация: Используется для быстрой и точной идентификации записи в таблице.

  1. В чем заключается различие между условиями HAVING и WHERE в SQL?

Ответ

В SQL условия HAVING и WHERE используются для фильтрации данных, но они применяются на разных этапах выполнения запроса и имеют разные цели:

  1. WHERE:

    • Применяется для фильтрации строк перед их группировкой и агрегацией.

    • Используется с отдельными строками данных.

    • Пример: SELECT * FROM table WHERE condition;

  2. HAVING:

    • Применяется для фильтрации результатов после выполнения группировки (с использованием GROUP BY).

    • Используется для фильтрации агрегированных данных (например, с функциями SUM, COUNT, AVG и т.д.).

    • Пример: SELECT column1, COUNT() FROM table GROUP BY column1 HAVING COUNT() > 1;

Таким образом, WHERE используется для фильтрации исходных данных, а HAVING — для фильтрации агрегированных результатов.

  1. Объясните разницу между командами DELETE и TRUNCATE?

Ответ

Команда DELETE — это DML-операция, которая удаляет записи из таблицы, соответствующие заданному условию:

DELETE FROM table_name WHERE condition;

При этом создаются логи удаления, то есть операцию можно отменить. А вот команда TRUNCATE — это DDL-операция, которая полностью пересоздаёт таблицу, и отменить такое удаление невозможно:

TRUNCATE TABLE table_name;

  1. Как найти дубли в поле email?

Ответ
SELECT email, COUNT(email) 
FROM customers 
GROUP BY email 
HAVING COUNT(email) > 1;

Функция COUNT() возвращает количество строк из поля email. Оператор HAVING работает почти так же, как и WHERE, вот только применяется не для всех столбцов, а для набора, созданного оператором GROUP BY.

  1. Что такое выражение GROUP BY в SQL, и в каких ситуациях оно используется?

Ответ

Выражение GROUP BY в SQL используется для группировки строк в наборе результатов по одному или нескольким столбцам. Это особенно полезно, когда вам нужно выполнять агрегатные функции, такие как COUNT(), SUM(), AVG(), MAX(), или MIN(), на группах данных.

Пример использования:

Предположим, у вас есть таблица sales, которая содержит информацию о продажах, включая колонны salesperson (имя продавца) и amount (сумма продажи). Вы хотите узнать общую сумму продаж для каждого продавца. Для этого можно использовать выражение GROUP BY следующим образом:

SELECT salesperson, SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson;

Разбор примера:

  • SELECT salesperson, SUM(amount): выбираем имя продавца и сумму всех продаж для этого продавца.

  • FROM sales: из таблицы sales.

  • GROUP BY salesperson: группируем строки по имени продавца.

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

Когда использовать GROUP BY:

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

  2. Отчеты: При создании отчетов, показывающих агрегированные данные, такие как суммарные продажи по месяцам или количество заказов по клиентам.

  3. Оптимизация: Для уменьшения объема данных при работе с большими наборами данных, группируя и агрегируя информацию, прежде чем ее передать в приложение для дальнейшей обработки.

  1. Что такое индексы в SQL? Какие преимущества и недостатки?

Ответ

Индексы в SQL — это специальные структуры данных, которые используются для ускорения выполнения запросов. Они создаются на основе одного или нескольких столбцов таблицы и позволяют быстро находить строки соответствующие условиям запроса.

Плюсы индексов:

  1. Ускорение запросов: Индексы значительно уменьшают время выполнения запросов SELECT, особенно тех, что включают WHERE, JOIN, и ORDER BY операторы.

  2. Быстрый доступ к данным: Поиск данных по индексам осуществляется быстрее, так как они упорядочены.

  3. Уменьшение нагрузок: Благодаря индексам сервер базы данных загружается меньше, так как нужные данные находятся быстрее.

Минусы индексов:

  1. Дополнительное место: Индексы требуют дополнительного дискового пространства.

  2. Замедление операций записи: Вставка, обновление и удаление данных могут замедляться, так как индексы должны обновляться соответственно.

  3. Сложность управления: Неправильное или чрезмерное использование индексов может действительно навредить производительности базы данных.

Индексы являются мощным инструментом для оптимизации производительности запросов, однако важно балансировать между их числом и негативным влиянием на операции записи. Компетентное управление индексами может заметно повысить эффективность работы любой системы баз данных.

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

Удачи вам на собеседованиях!

Комментарии (12)


  1. kalapanga
    23.05.2024 09:57
    +7

    Автор, Вы реально считаете, что в первом задании правильный ответ 12 и 10 строк?


    1. Marina_Chernysheva Автор
      23.05.2024 09:57

      Спасибо, вы абсолютно правы, была опечатка, скопировала ответ из другого примера аналогичной задачи. Уже исправила


    1. kalapanga
      23.05.2024 09:57

      Исправили - хорошо! Надеюсь и остальные примеры перепроверили (их я не проверял).


  1. zodchiy
    23.05.2024 09:57

    Блокировки, транзакции, изолированность, кластерный/некластерный индексы, типы ключей и их плюсы и минусы, подзапросы, оконные функции, index seek/index scan/trable scan, нормализация.


  1. santjagocorkez
    23.05.2024 09:57
    +1

    1. Почти все ответы по join неправильные, ответы даны для специальных случаев outer join, тогда как в левой части определения конструкция указана без квалификатора, что по умолчанию означает inner

    2. Primary key — это не поле и не набор полей, это ограничение таблицы (constraint)

    3. Truncate, по крайней мере, в PostgreSQL, транзакционный, как и большая часть DDL (хоть и, к сожалею, не вся). Поправка: транзакционность здесь распространяется только на возможность отката изменений, а вот отсутствие данных параллельные транзакции будут наблюдать сразу, по крайней мере, до отката транзакции

    Что-то утомительно стало почти в каждом ответе видеть ошибку, причём, грубую.

    PS: duckdb — это какое-то лютое поделие, которое, судя по документации, сваяли ради нового ключевого слова, при том, что в SQLite3 проблема решается стандартным синтаксисом, хоть и, конечно, в большее количество слов. А чтобы пользователи не роптали, прикрутили в драйвер зависимость от пандас и простенькое апи, чтобы туда выплевывать результат. Забыв про этом умолчать, что импорт пандас занимает дикое количество времени, в лямбдах Амазона, например, насколько секунд.


    1. sshikov
      23.05.2024 09:57

      1. Primary key — это не поле и не набор полей, это ограничение таблицы (constraint)

      И еще их может быть несколько (unique). И еще оно может быть выключено. Из чего автоматически следует, что значения в колонках не будут уникальными, и потом при включении constraint произойдет что-либо типа валидации.


  1. Akina
    23.05.2024 09:57
    +2

    Задание 3 - вот хотелось бы у автора узнать, как абсолютно один и тот же код может быть решением для двух в принципе разных вопросов?

    Задание 3 вопрос 2 - ответ на вопрос обязан предусмотреть, что два и более промокодов могут иметь одинаковое количество. WITH TIES либо его эмуляция.

    Задание 4 - ответ неверный. В условии указано, что поле id является первичным ключом. Следовательно, выходной набор будет иметь строго 10 записей, никакого дублирования по b.id, который по условию есть первичный ключ, быть не может в принципе.

    Задание 5 - оба ответа неверны. Первый - в зависимости от collation, второй - всегда.

    Вопрос 1 - ответ неверный. Есть ещё LATERAL JOIN, в различных вариациях.

    Вопрос 2 - значение первичного ключа не "должно быть уникальным", а "уникально в пределах имеющихся данных". Либо данные разрушены.

    Вопрос 3 - ничто не мешает использовать HAVING для фильтрации неагрегированных данных. Более того, в некоторых (достаточно редких на практике) условиях это необходимо делать, чтобы получить возможность использовать имеющийся композитный индекс.

    Вопрос 4 - безальтернативное утверждение, что TRUNCATE нельзя откатить, в общем случае неверно. Возможность отката зависит от СУБД и некоторых других факторов.

    Ну и хотелось бы понять, каким боком этот опус прислонился к хабу Recovery Mode.

    Да, и плюс написанное ранее, само собой.


  1. Batalmv
    23.05.2024 09:57

    SELECT name FROM table WHERE name LIKE "A%";

    или

    SELECT name FROM table WHERE LOWER(name) LIKE "a%";

    Я бы вот тут засомневался, так ли уж правильно приводить второй запрос, разве чтобы потом порассуждать почему это не лучшая идея

    А вообще вопросы примитивные, разве что для QA подходят, или студентов


  1. ptr128
    23.05.2024 09:57

    Зачем кувыркаться через ROW_NUMBER(), если есть DISTINCT ON?


    1. Akina
      23.05.2024 09:57

      В случае Постгресса - да. Но кто ещё это понимает?


      1. ptr128
        23.05.2024 09:57

        Явно указанный в задаче DuckDB.


  1. titan_pc
    23.05.2024 09:57

    "Сколько строк выведет программа?" Вопросы как будто для школьников... Чат джпт план собеса составлял 100%