10 продвинутых SQL вопросов с собеседований, на которые вы должны знать ответ.

Вступление

SQL (Structured Query Language) – это стандартный язык программирования, используемый для управления базами данных и манипулирования ими. Это необходимый навык для любого специалиста по обработке данных, поскольку он позволяет им эффективно извлекать и анализировать данные, хранящиеся в базе данных. SQL является распространённой темой на технических собеседованиях на должности, связанные с работой с данными, подобно аналитикам данных, инженерам по обработке данных и администраторам баз данных.

Продолжаем серию статей по вопросам с SQL собеседований для аналитика данных.

1. SQL-запрос для поиска n-й по величине зарплаты/платежа или 3-й по величине зарплаты/платежа

Чтобы найти n-ую самую высокую зарплату, вы можете использовать подзапрос с функцией DENSE_RANK() для вычисления ранга каждой зарплаты, а затем отфильтровать результаты, чтобы включить только строку с рейтингом, равным n:

SELECT 
  * 
FROM 
  (
    SELECT 
      name, 
      salary, 
      DENSE_RANK() OVER (
        ORDER BY 
          salary DESC
      ) as salary_rank 
    FROM 
      employees
  ) subquery 
WHERE 
  salary_rank = n;

Вы также можете использовать условия LIMIT и OFFSET , чтобы найти n-ую самую высокую зарплату, следующим образом:

SELECT 
  name, 
  salary 
FROM 
  employees 
ORDER BY 
  salary DESC 
LIMIT 
  1 OFFSET (n - 1);

Например, чтобы найти 3-ю по величине зарплату, вы могли бы использовать следующий запрос:

SELECT 
  name, 
  salary 
FROM 
  employees 
ORDER BY 
  salary DESC 
LIMIT 
  1 OFFSET 2;

2. Как вы оптимизируете SQL-запросы для повышения производительности?

Существует несколько способов оптимизации SQL-запросов для повышения производительности, в том числе:

Индексация

Создание индекса для столбца или группы столбцов может значительно повысить скорость запросов.

Секционирование

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

Нормализация

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

Использование соответствующих типов данных

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

Использование соответствующих типов JOIN

Использование правильного типа JOIN (например: INNER JOIN, OUTER JOIN, CROSS JOIN) может повысить производительность запросов, объединяющих несколько таблиц.

Использование соответствующих агрегатных функций

Использование соответствующих агрегатных функций (например: SUM, AVG, MIN, MAX) может повысить производительность запросов, которые выполняют вычисления на больших наборах данных. Некоторые агрегатные функции, подобные COUNT, более эффективны, чем другие, поэтому важно выбрать подходящую функцию для вашего запроса.

3. Как вы используете функции LAG и LEAD в SQL? Можете ли вы привести пример их использования?

Функции LAG() и LEAD() – это оконные функции в SQL, которые позволяют сравнивать значения в строке со значениями в предыдущей или следующей строке соответственно. Они полезны для вычисления текущих итогов или для сравнения значений в таблице со значениями в предыдущей или последующей строке.

Функция LAG() принимает два аргумента: столбец, который нужно вернуть, и количество строк, которые нужно вернуть. Например:

SELECT 
  name, 
  salary, 
  LAG(salary, 1) OVER (
    ORDER BY 
      salary DESC
  ) as prev_salary 
FROM 
  employees;

Функция LEAD() работает аналогичным образом, но принимает значения, находящиеся после текущей строки. Например:

SELECT 
  name, 
  salary, 
  LEAD(salary, 1) OVER (
    ORDER BY 
      salary DESC
  ) as next_salary 
FROM 
  employees

4. Объясните концепцию ETL и ELT в SQL

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

ELT (Извлечение, загрузка, преобразование) аналогичен ETL, но фаза преобразования выполняется после загрузки данных в целевую систему, а не до этого. Это позволяет целевой системе выполнять преобразования, которые могут быть более эффективными и масштабируемыми, чем выполнение преобразований в инструменте ETL. ELT часто используется в современных инфраструктурах обработки данных, которые используют мощные механизмы обработки данных (такие как Apache Spark или Apache Flink) для выполнения фазы преобразования.

5. Можете ли вы объяснить разницу между предложениями WHERE и HAVING в SQL

Предложения WHERE и HAVING используются для фильтрации строк из инструкции SELECT. Основное различие между ними заключается в том, что предложение WHERE используется для фильтрации строк перед операцией group by, в то время как предложение HAVING используется для фильтрации строк после операции group by.

SELECT 
  department, 
  SUM(salary) 
FROM 
  employees 
GROUP BY 
  department 
HAVING 
  SUM(salary) > 100000;

В этом примере предложение HAVING используется для фильтрации любых отделов, где сумма зарплат сотрудников меньше 100 000. Это делается после операции group by, поэтому это влияет только на строки, представляющие каждый отдел.

SELECT 
  * 
FROM 
  employees 
WHERE 
  salary > 50000;

В этом примере предложение WHERE используется для фильтрации любых сотрудников с зарплатой менее 50000. Это делается перед любой операцией group by, поэтому это влияет на все строки в таблице employees.

6. Объясните разницу между операциями TRUNCATE, DROP и DELETE в SQL

TRUNCATE

Операция TRUNCATE удаляет все строки из таблицы, но не влияет на структуру таблицы. Процесс происходит быстрее, чем при использовании DELETE, потому что оно не генерирует никаких журналов отмены или повтора и не запускает никаких триггеров удаления.

Вот пример использования оператора TRUNCATE:

TRUNCATE TABLE employees;

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

DROP

Операция DROP удаляет таблицу из базы данных и удаляет все данные в таблице. Она также удаляет все индексы, триггеры и ограничения, связанные с таблицей.

Вот пример использования инструкции DROP:

DROP 
  TABLE employees;

Эта инструкция удаляет таблицу employees из базы данных, и все данные в таблице удаляются безвозвратно. Структура таблицы также будет удалена.

DELETE

Операция DELETE  удаляет одну или несколько строк из таблицы. Это позволяет вам указать предложение WHERE для выбора строк для удаления. Она также генерирует журналы отмены и повтора и запускает триггеры отмены.

Вот пример использования инструкции DELETE:

DELETE FROM 
  employees 
WHERE 
  salary & lt;
50000;

Эта инструкция удаляет все строки из таблицы employees, где зарплата меньше 50 000. Структура таблицы остается неизменной, а удалённые строки можно восстановить с помощью журналов отмены.

7. Что более эффективно – соединение или подзапрос?

Как правило, более эффективно использовать соединение, а не подзапрос при объединении данных из нескольких таблиц. Это связано с тем, что метод JOIN позволяет базе данных выполнять запрос более эффективно за счёт использования индексов в объединённых таблицах.

SELECT 
  * 
FROM 
  orders o 
WHERE 
  o.customer_id IN (
    SELECT 
      customer_id 
    FROM 
      customers 
    WHERE 
      country = 'US'
  );

Запрос использует подзапрос для выбора соответствующих идентификаторов клиентов из таблицы customers, а затем использует оператор IN для фильтрации таблицы orders на основе этих идентификаторов.

8. Как вы используете оконные функции в SQL?

В SQL оконная функция – это функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце. Оконная функция используется для выполнения вычислений по строкам, и их можно использовать в операторах SELECT, UPDATE и DELETE, а также в предложениях WHERE и HAVING инструкции SELECT.

Вот пример использования оконной функции в инструкции SELECT:

SELECT 
  name, 
  salary, 
  AVG(salary) OVER (PARTITION BY department_id) as avg_salary_by_department 
FROM 
  employees

Этот оператор возвращает результирующий набор с тремя столбцами: имя, зарплата и средняя заработная плата в отделе. Столбец avg_salary_by_department рассчитывается с использованием функции AVG window, которая вычисляет среднюю заработную плату для каждого отдела. Предложение PARTITION BY указывает, что окно разделено по department_id, что означает, что средняя зарплата рассчитывается отдельно для каждого отдела.

9. Объясните нормализацию

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

Первая нормальная форма (1NF)
  • Каждая ячейка в таблице содержит одно значение, а не список значений.
  • Каждый столбец в таблице имеет уникальное имя.
  • Таблица не содержит никаких повторяющихся групп столбцов.
Вторая нормальная форма (2NF)
  • Находится в первой нормальной форме.
  • У него нет никаких частичных зависимостей.
Третья нормальная форма (3NF)
  • Находится во второй нормальной форме.
  • У него нет никаких переходных зависимостей.
Нормальная форма Бойса-Кодда (BCNF)
  • Находится в третьей нормальной форме.
  • Каждый определитель (атрибут, который определяет значение другого атрибута) является ключом-кандидатом (столбец или набор столбцов, которые можно использовать в качестве первичного ключа).

10. Объясните эксклюзивную блокировку и блокировку обновления в SQL

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

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

sqlhub – разбор реальных задач SQL в нашем телеграм канале.

+1
1
+1
16
+1
2
+1
0
+1
1

Ответить

Ваш адрес email не будет опубликован. Обязательные поля помечены *