Привет, Хабр! Если вы читаете этот материал, скорее всего, вы рассматриваете перспективы карьерного роста в области системного анализа, и возможно, готовитесь к собеседованию. Важным аспектом работы системного аналитика является умение эффективно работать с базами данных, и SQL — это ключевой инструмент для этого. Будьте готовы, что на техническом собеседовании вас могут попросить назвать операторы SQL, написать запросы для извлечения, обновления или удаления данных, создания новых таблиц и многого другого. Давайте посмотрим на некоторые примеры таких заданий.

***

В этой статье мы предлагаем познакомиться с примерами вопросов по теории и типовыми задачами по SQL, которые могут встретиться на собеседовании на должность системного аналитика (СА). Эти примеры помогут проверить и улучшить ваши навыки в SQL, а также лучше подготовиться к собеседованию.

После публикации статьи 25 вопросов и ответов по терминам REST API на собеседовании по вакансии системного аналитика нам оставили комментарии, что изучать варианты ответов по типовым задачам — малоэффективно. Может привести к догматичности и не развивает кандидата. Что-то вроде натаскивания по карточкам на экзамен в ГИБДД. К слову, свой метод подготовки никто из комментаторов не предложил.

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

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

В компании SSP SOFT открыты вакансии системного аналитика, разработчиков на Java, React и Python, 1С, инженеров DevOps и QA — приглашаем посетить нашу страницу на hh.ru.

А теперь — переходим к вопросам по теории и практике SQL.

1. Что такое язык SQL и для чего он используется?

2. Объясните понятие реляционных баз данных

3. Что такое «схема данных» в реляционных базах данных?

4. Объясните, что такое индекс в SQL БД и для чего используется

5. Какими характеристиками обладает NoSQL база данных?

6. Какие основные типы операторов используются в SQL для выборки, вставки, обновления и удаления данных?

7. Назовите основные операторы создания и изменения таблиц, а также агрегации данных

8. Что такое транзакция в SQL и каковы основные свойства транзакций (ACID)?

9. Какие основные категории типов данных используются в SQL? Назовите пример хотя бы одного типа данных для категорий

10. Объясните разницу между управлением версиями и управлением изменениями в базе данных SQL

11. Что такое подзапросы в SQL-запросах? Приведите пример подзапроса и объясните его действие

12. Объясните, почему оптимизация SQL-запросов с помощью индексов может ускорить поиск и фильтрацию данных

13. Объясните пример кода по создание индекса из предыдущей задачи

14. Что такое "шаблон проектирования" в контексте базы данных SQL?

15. Предложите пример шаблона проектирования “ER” для базы данных сервиса онлайн продажи авиабилетов

Примеры вопросов по теории и практике SQL к собеседованию

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

Если вы уже бывали на собеседованиях, то наверняка заметили, что интервьюер часто задает уточняющие вопросы вслед за основным заданием. Мы как раз проиллюстрируем это в вопросах 2-4. Например, вы ответили на вопрос «Что такое реляционная БД?», а вас попросили уточнить понятие схемы данных как одной из характеристик реляционной БД. Вы ответили и на этот вопрос, при этом упомянули про понятие «индекса БД». Интервьюер может дальше спросить, что означает индекс БД.  Точно также, если упомянули термин ACID, — с большой вероятностью получите уточняющий вопрос «Что такое ACID?» и т.д.

Но есть и хорошая новость — скорее всего, вы не получите более 3-х вложенных вопросов по SQL, т.к. время собеседования ограничено. Тем не менее, надо быть готовыми объяснить каждый термин, который вы упоминаете в ответе.

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

1. Что такое язык SQL и для чего он используется?

Ответ: SQL (Structured Query Language) — это язык программирования, который используется для управления реляционными базами данных. Реляционные базы данных организованы в виде таблиц, состоящих из строк и столбцов, и SQL позволяет выполнять различные операции с данными в таких таблицах.

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

2. Объясните понятие реляционных баз данных

Ответ: Базы данных разделяют на SQL (реляционные) и NoSQL (не реляционные). Выбор системного архитектора между SQL и NoSQL зависит от конкретных потребностей проекта. Реляционные базы данных обычно подходят для проектов с жесткой схемой данных и сложными запросами, в то время как базы данных NoSQL могут быть полезными в случаях, когда структура данных меняется часто или когда требуется обработка больших объемов данных.

Вот основные характеристики реляционной базы данных:

• Структурированные данные: Данные хранятся в таблицах с четкой структурой, где каждая таблица имеет определенные типы данных.

• Схема данных: Реляционные базы данных обычно имеют строгую схему данных, которая определяет структуру таблиц и связи между ними.

• Язык SQL: Для работы с данными используется язык SQL, который обеспечивает мощные возможности для запросов, операций с данными и транзакций.

• ACID-совместимость: Реляционные базы данных обычно обеспечивают ACID-совместимость (atomicity, consistency, isolation, durability  — Атомарность, Согласованность, Изолированность и Долгосрочность) для гарантирования целостности данных.

3. Что такое «схема данных» в реляционных базах данных?

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

Основные компоненты схемы данных в реляционных базах данных включают:

1. Таблицы: Таблицы представляют собой основные структурные элементы базы данных, и каждая таблица имеет уникальное имя. Каждая строка в таблице представляет отдельную запись, а столбцы определяют атрибуты этой записи.

2. Столбцы: Столбцы определяют атрибуты данных, которые будут храниться в таблице. Каждый столбец имеет свой тип данных, который определяет, какого типа информацию можно вводить в этот столбец (например, числа, текст и так далее).

3. Ключи: Ключи используются для уникальной идентификации записей в таблице. Основные ключи (Primary Key) гарантируют уникальность значений в столбце и обеспечивают быстрый доступ к данным. Внешние ключи (Foreign Key) создают связи между таблицами, позволяя ссылаться на записи в других таблицах.

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

5. Индексы: Индексы создаются для улучшения производительности запросов к базе данных. Они позволяют быстро находить записи в таблице, ускоряя операции поиска и сортировки.

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

4. Объясните, что такое индекс в SQL БД и для чего используется

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

Индексы в SQL используются для ускорения операций по выборке данных (SELECT).  При операциях записи, таких как UPDATE и DELETE, системе приходится обновлять индекс при изменении данных.

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

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

5. Какими характеристиками обладает NoSQL база данных?

Ответ: Разница между SQL и NoSQL базами данных заключается в структуре данных, способе организации и модели хранения информации. Вот основные характеристики NoSQL БД (нереляционной базы данных):

• Неструктурированные данные: Данные могут храниться в формате JSON, XML, или других нереляционных форматах, и их структура может быть гибкой.

• Гибкая схема: NoSQL базы данных позволяют добавлять и изменять поля в записях без строгой схемы данных.

• Разные модели организации данных: Существуют разные типы NoSQL баз данных, включая ключ-значение, столбцовые, документные и графовые базы данных, каждая из которых предоставляет разные модели организации данных.

Считается, что нереляционные базы данных могут обладать большей производительностью и масштабируемостью в сравнении с SQL БД. В частности, NoSQL БД бывает эффективнее для операций обработки особо больших объемов данных.

6. Какие основные типы операторов используются в SQL для выборки, вставки, обновления и удаления данных?

Ответ: В SQL используются следующие основные типы операторов для выборки, вставки, обновления и удаления данных:

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

• Операторы вставки, обновления и удаления данных:
  INSERT INTO: Используется для вставки новых записей в таблицу.
  UPDATE: Обновляет существующие записи в таблице.
  DELETE: Удаляет записи из таблицы.

7. Назовите основные операторы создания и изменения таблиц, а также агрегации данных

Ответ: Для выполнения различных операций с данными в реляционных базах данных используются следующие операторы:

• Операторы создания и изменения схемы данных:
  CREATE TABLE: Создает новую таблицу в базе данных.
  ALTER TABLE: Изменяет структуру существующей таблицы (например, добавляет столбцы или меняет их типы).
  DROP TABLE: Удаляет таблицу из базы данных.

• Операторы агрегации данных:
  GROUP BY: Группирует строки на основе значений в одном или нескольких столбцах.
  COUNT, SUM, AVG, MAX, MIN: Выполняют агрегатные функции над данными в группах.

• Оператор объединения данных:
  JOIN: Используется для объединения данных из нескольких таблиц на основе заданных условий.

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

8. Что такое транзакция в SQL и каковы основные свойства транзакций (ACID)?

Ответ: В SQL транзакция представляет собой логическую единицу работы с базой данных, которая состоит из одной или нескольких операций базы данных. Транзакции используются для обеспечения целостности данных и надежности в многопользовательской среде. Основные свойства транзакций в SQL описываются аббревиатурой ACID:

1.  Атомарность (Atomicity):
   - Это свойство гарантирует, что транзакция выполняется как неделимая операция. То есть все операции внутри транзакции либо успешно завершаются, либо отменяются (откатываются), и не оставляют базу данных в неконсистентном состоянии. Это обеспечивает целостность данных.

2. Согласованность (Consistency):
   - Это свойство гарантирует, что транзакция переводит базу данных из одного согласованного состояния в другое согласованное состояние. То есть транзакция должна соблюдать все правила и ограничения базы данных, и она не должна нарушать целостность данных.

3. Изолированность (Isolation):
   - Это свойство гарантирует, что выполнение одной транзакции не влияет на выполнение других параллельных транзакций. Транзакции должны быть изолированы друг от друга, чтобы избежать конфликтов и вмешательства.

4. Долгосрочность (Durability):
   - Это свойство гарантирует, что результаты успешно завершенных транзакций сохраняются даже в случае сбоя системы. То есть данные, измененные в рамках транзакции и подтвержденные оператором COMMIT, остаются постоянно и не могут быть утеряны.

Свойства ACID обеспечивают надежное и предсказуемое поведение базы данных в условиях сбоев и многопользовательского доступа. Когда транзакция выполняется, она либо успешно завершается и все ее изменения сохраняются (подтверждены оператором COMMIT), либо отменяется (откатывается) в исходное состояние, не оставляя данные в неконсистентном или поврежденном состоянии. Эти свойства транзакции — ключевой принцип обеспечения надежности и целостности данных в реляционных базах данных.

9. Какие основные категории типов данных используются в SQL? Назовите по одному примеру для типов данных

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

1. Числовые типы данных:

   • INT (целое число): Хранит целые числа.

2. Типы данных с плавающей точкой:

   • FLOAT: Хранит числа с плавающей точкой одинарной точности.

3. Символьные типы данных:

   • TEXT: Хранит текстовые данные.

4. Дата и временные типы данных:

   • TIMESTAMP: Хранит дату и время.

5. Бинарные типы данных:

   • BINARY: Хранит бинарные данные фиксированной длины.

6. Булевы типы данных:

   • BOOLEAN: Хранит значения истина (TRUE) или ложь (FALSE).

7. Географические типы данных:

   • POINT: Хранит точки на карте (географические координаты). 

8. JSON: Хранит JSON-данные (структурированный формат данных).

Это общий список основных типов данных, доступных в SQL. Как правило, ответ кандидата сочтут успешным, если будет названо хотя бы 4-5 категорий типов данных сразу, а остальные типы названы в ходе уточняющих и наводящих вопросов интервьюера.

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

10. Объясните разницу между управлением версиями и управлением изменениями в базе данных SQL

Ответ: Управление версиями и управление изменениями в базах данных SQL представляют собой два различных подхода к обновлению структуры базы данных.

1. Управление версиями базы данных:

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

2. Управление изменениями в базе данных:

    — Управление изменениями связано с процессом внесения изменений в схему базы данных, таким образом, чтобы эти изменения были отслеживаемыми и безопасными. Включает в себя создание скриптов (например, SQL‑скрипты) для внесения изменений в существующую структуру базы данных. Эти изменения могут включать в себя создание новых таблиц, добавление столбцов, изменение типов данных и т. д.

Оба эти подхода часто используются совместно в разработке и сопровождении баз данных. Управление версиями позволяет отслеживать состояние базы данных во времени, в то время как управление изменениями позволяет вносить изменений в БД с минимальным воздействием на работающие системы. 

11. Что такое подзапросы в SQL-запросах? Приведите пример подзапроса и объясните его действие

Ответ: Подзапрос (subquery) в SQL — это запрос, который включен в другой запрос. Подзапросы могут использоваться для выполнения операций вложенного запроса и передачи результатов этого запроса внешнему запросу. Подзапросы позволяют извлекать данные из одного запроса и использовать их в другом.

Подзапросы могут использоваться в различных частях SQL-запроса, к примеру в операторе SELECT. Здесь подзапрос может быть использован в списке выбора для извлечения значения, которое будет включено в результаты основного запроса.
Пример:

SELECT column1, column2, (SELECT column3 FROM table2 WHERE condition) AS subquery_result
FROM table1
WHERE condition;

Давайте прокомментируем этот кусок кода, как если бы вы объясняли это интервьюеру:
Результат основного запроса будет содержать столбцы `column1` и `column2` из таблицы `table1`, а также дополнительный столбец `subquery_result`, который представляет собой значение из столбца `column3` таблицы `table2`, выбранное с использованием подзапроса. Результирующий столбец `subquery_result` будет включен в каждую строку результата основного запроса.

12. Объясните, почему оптимизация SQL-запросов с помощью индексов может ускорить поиск и фильтрацию данных

Ответ: Оптимизация SQL-запросов с использованием индексов позволяет обеспечивать более высокую производительность баз данных:

1. Ускорение поиска данных:

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

   Пример кода для создания индекса:

CREATE INDEX idx_column_name ON table_name(column_name);

1. Улучшение производительности оператора JOIN:

    — Индексы улучшают производительность операторов JOIN. Когда две таблицы объединяются, индексы на столбцах, используемых для объединения, позволяют быстро находить соответствующие строки и уменьшают число строк для обработки.

2. Ускорение фильтрации данных:

    — Индексы ускоряют фильтрацию данных при использовании операторов WHERE. СУБД быстрее определяет, какие строки удовлетворяют заданным условиям без полного сканирования таблицы.

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

13. Объясните пример кода по создание индекса из предыдущей задачи

Ответ: В данном образце кода `CREATE INDEX idx_column_name ON table_name(column_name);` создается индекс на столбце `column_name` в таблице `table_name`. Создание индекса может значительно ускорить работу оператора `WHERE`, который фильтруют данные по данному столбцу. 

Вот как это происходит:

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

Вторым условием ускорения является то, что при наличии индекса база данных может эффективно использовать его для выполнения операций сравнения, например, равенства (`=`), больше (`>`), меньше (`<`), между (`BETWEEN`), и т. д. Это сокращает количество строк, которые нужно обработать, что в свою очередь улучшает производительность запроса.

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

14. Что такое "шаблон проектирования" в контексте базы данных SQL?

Ответ: В контексте баз данных SQL, "шаблон проектирования" —это шаблон организации структуры базы данных, создания связей между таблицами и выполнения типичных операций обработки данных. Одним из наиболее распространенных шаблонов проектирования для баз данных является "Сущность-Связь" (Entity-Relationship, ER)

 В модели ER присутствуют следующие акторы:

• Сущность представляет собой объект, который может быть идентифицирован в контексте информационной системы. Сущности соответствуют таблицам в базе данных. 

• Атрибуты — это характеристики или свойства сущности. Атрибуты соответствуют полям (столбцам) в таблице базы данных.

• Связи определяют, как сущности связаны друг с другом. Например, сущность "Студент" может быть связана со сущностью "Курс" через связь "Регистрация на курс". Эти связи могут быть один-к-одному, один-ко-многим, многие-ко-многим.

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

15. Предложите пример шаблона проектирования “ER” для базы данных сервиса онлайн-продажи авиабилетов

Ответ: Пример "шаблона проектирования" Entity-Relationship (ER) для базы данных онлайн-продажи авиабилетов:

1. Сущности:

   • Customer (Покупатель)

   • Flight (Рейс)

   • Ticket (Билет)

   • Airline (Авиакомпания)

2. Связи между сущностями:

   • Покупатели бронируют билеты на рейсы, связь "Customer buys Ticket”.

   • Билеты относятся к конкретным рейсам, связь "Ticket is associated with Flight".

   • Рейсы выполняются авиакомпаниями, связь "Flight is operated by Airline".

3. Атрибуты сущностей:

   • Customer может иметь атрибуты CustomerID, FirstName, LastName, Email.

   • Flight может иметь атрибуты FlightID, DepartureCity, ArrivalCity, DepartureTime, ArrivalTime.

   • Ticket может иметь атрибуты TicketID, Price, SeatNumber.

   • Airline может иметь атрибуты AirlineID, AirlineName, ContactInfo.

4. Пример SQL-кода для шаблона:

CREATE TABLE Customers (
       CustomerID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       Email VARCHAR(100)
   );
   CREATE TABLE Flights (
       FlightID INT PRIMARY KEY,
       DepartureCity VARCHAR(50),
       ArrivalCity VARCHAR(50),
       DepartureTime DATETIME,
       ArrivalTime DATETIME,
       AirlineID INT,
       FOREIGN KEY (AirlineID) REFERENCES Airlines(AirlineID)
   );
   CREATE TABLE Tickets (
       TicketID INT PRIMARY KEY,
       Price DECIMAL(10, 2),
       SeatNumber VARCHAR(10),
       FlightID INT,
       CustomerID INT,
       FOREIGN KEY (FlightID) REFERENCES Flights(FlightID),
       FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
   );
   CREATE TABLE Airlines (
       AirlineID INT PRIMARY KEY,
       AirlineName VARCHAR(100),
       ContactInfo VARCHAR(255)
   );

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

Заключение

Итак, мы подошли к концу нашего туториала "15 примеров задач по SQL на собеседовании по вакансии системного аналитика". Надеемся, что эта статья помогла вам получить некоторое представление о том, какие вопросы могут быть заданы на собеседовании и как на них отвечать.

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

В качестве примера таких ресурсов можно порекомендовать вот это видео. Оно интересно тем, что кроме примеров вопросов и ответов непосредственно по теме SQL, в нем даются полезные советы как отвечать на вопросы из категории Soft Skills — чем вас заинтересовала нанимающая компания, про желаемую зарплату, о причинах смены работы и др.
Включите субтитры и нажмите "Перевести", чтобы читать русские субтитры.

Мы знаем, что статья была довольно объемной, и хотели бы выразить вам свою благодарность за то, что вы уделили время для ее чтения. Надеемся, что этот материал был полезным и информативным для вас.

Всех интересующихся вакансиями системного аналитика, разработчиков на Java, React и Python, 1С, инженеров DevOps и QA — приглашаем посетить нашу страницу на hh.ru.

Спасибо за чтение и удачи вам на собеседованиях!

Автор: Сергей Березин