Вопросы по SQL, которые часто задают на собеседовании. Часть 1

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

1. Что такое первичный ключ в SQL и почему он важен?

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

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

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

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

2. Что такое внешний ключ и как он используется для установления связей между таблицами?

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

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

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

Чтобы проиллюстрировать это, рассмотрим пример ниже.

У нас есть две таблицы: одна для сотрудников, другая для отделов. В таблице сотрудников (“employees”) есть столбец внешнего ключа, который ссылается на столбец ID отдела в таблице отделов (“departments”):

CREATE TABLE departments( 
    dept_id INT PRIMARY KEY, 
    dept_name VARCHAR(50) NOT NULL 
);

CREATE TABLE employees (
     emp_id INT PRIMARY KEY,
     emp_name VARCHAR(50) NOT NULL,
     dept_id INT, 
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id));

В данном примере таблица “departments” содержит информацию о различных отделах компании, а таблица “employees” – информацию о сотрудниках компании. Внешний ключ в таблице “employees” гарантирует, что каждый сотрудник связан с отделом, который существует в таблице “departments”.

Обеспечивая ссылочную целостность между двумя таблицами, внешний ключ предотвращает несогласованность данных и обеспечивает их точность. Например, если мы попытаемся удалить из таблицы “departments” отдел, с которым связаны сотрудники в таблице “employees”, база данных не позволит нам этого сделать, поскольку это нарушит ссылочную целостность, установленную внешним ключом.

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

3. В чем разница между базой данных и схемой?

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

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

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

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

4. Что такое выражение GROUP BY и как оно используется?

Выражение GROUP BY – это оператор SQL, используемый для группировки строк с одинаковыми значениями в одном или нескольких столбцах в итоговые строки, например, “найти общий объем продаж для каждого продукта”.

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

Например, если у вас есть таблица “Sales” (“Продажи”) со столбцами “Product” (“Продукт”), “Date” (“Дата”) и “Sales Amount” (“Сумма продаж”), и вы хотите найти общую сумму продаж для каждого продукта, вы можете использовать следующий SQL-запрос:

SELECT Product, SUM([Sales Amount]) as TotalSales
FROM Sales
GROUP BY Product;

В результате будет получена таблица с двумя столбцами: “Product” и “TotalSales”, где каждая строка представляет собой уникальный продукт и его общий объем продаж.

Оператор GROUP BY также можно использовать с несколькими столбцами, что позволит сгруппировать данные по каждой уникальной комбинации столбцов. Например:

SELECT Product, Date, SUM([Sales Amount]) as TotalSales
FROM Sales
GROUP BY Product, Date;

В результате будет получена таблица с тремя столбцами: “Product”, “Date” и “TotalSales”, где каждая строка представляет собой уникальную комбинацию продукта и даты, а также общее количество продаж.

В общем, оператор GROUP BY используется в SQL для группировки строк с одинаковыми значениями в одном или нескольких столбцах в сводные строки с использованием агрегатных функций для выполнения вычислений над сгруппированными данными.

5. Что такое self-join и когда используется?

Self-join в SQL – это тип операции соединения, при которой таблица объединяется сама с собой. Это полезно, когда у вас есть таблица со связанными данными в разных строках, которые вы хотите объединить на основе общего поля.

Например, рассмотрим таблицу “employees” (“Сотрудники”) со столбцами для ID и имени сотрудника, а также ID менеджера. Столбец ID менеджера содержит ID менеджера сотрудника. Чтобы получить список всех сотрудников с именем их менеджера, можно использовать self-join.

Вот пример запроса:

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;

В этом запросе мы соединяем таблицу “employees” с самой собой, используя столбец “manager_id”, чтобы сопоставить каждого сотрудника с его руководителем. В результирующей таблице будут столбцы с именем сотрудника и именем его менеджера.

Self-join также можно использовать для поиска связей между данными в одной таблице. Например, если у вас есть таблица продуктов со столбцами для ID продукта, названия и ID “родительского” продукта, соединение внутри таблицы может быть использовано для поиска всех “дочерних” продуктов данного “родительского” продукта.

6. В чем разница между внутренним и внешним соединением?

Cоединения (joins, джоины) используются для комбинации данных из нескольких таблиц на основе общего столбца или их взаимоотношений. Два наиболее распространенных типа соединений – это внутреннее (inner join) и внешнее (outer join) соединения.

Внутреннее соединение возвращает только совпадающие строки из обеих таблиц на основе условия соединения. Например, если у нас есть две таблицы A и B, и мы выполняем внутреннее объединение с использованием общего столбца C, будут возвращены только те строки, в которых C совпадает в обеих таблицах.

Вот пример запроса для внутреннего соединения:

SELECT A.column1, B.column2
FROM A
INNER JOIN B
ON A.C = B.C;

С другой стороны, внешнее соединение возвращает все строки из одной таблицы и совпадающие строки из другой таблицы. Если во второй таблице нет совпадающих строк, результат будет содержать NULL-значения для всех столбцов этой таблицы. Внешние соединения также делятся на левое внешнее (left outer join), правое внешнее (right outer join) и полное внешнее соединение (full outer join).

Вот пример запроса для левого внешнего соединения:

SELECT A.column1, B.column2
FROM A
LEFT OUTER JOIN B
ON A.C = B.C;

Этот запрос вернет все строки из таблицы A и совпадающие строки из таблицы B на основе столбца C. Если в таблице B нет совпадающих строк, результат будет содержать NULL-значения для column2.

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

7. В чем разница между коррелированным и некоррелированным подзапросом?

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

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

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Подзапрос (SELECT AVG(salary) FROM employees) является некоррелированным подзапросом, поскольку может выполняться независимо от внешнего запроса. Он извлекает среднюю зарплату всех сотрудников, а результат используется в операторе WHERE внешнего запроса, чтобы отфильтровать сотрудников, чья зарплата больше средней.

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

SELECT first_name, last_name, 
  (SELECT SUM(amount) FROM sales WHERE sales.employee_id = employees.employee_id) AS total_sales
FROM employees;

Подзапрос (SELECT SUM(amount) FROM sales WHERE sales.employee_id = employees.employee_id) является коррелированным, поскольку ссылается на столбец employee_id из внешнего запроса. Он оценивается для каждого сотрудника во внешнем запросе, и результат используется для расчета общего объема продаж для каждого сотрудника.

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

8. Что такое обобщенное табличное выражение (CTE) и как оно используется?

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

CTE определяются с помощью ключевого слова WITH, за которым следует имя CTE и подзапрос, определяющий его. Синтаксис CTE выглядит следующим образом:

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name

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

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

В целом, CTE – это мощная функция SQL, которая позволяет упростить сложные запросы и повысить производительность.

9. В чем разница между операторами DELETE и TRUNCATE?

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

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

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

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

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

10. Что такое временная таблица и как она используется?

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

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

Временные таблицы можно создать с помощью оператора CREATE TEMPORARY TABLE. Они могут быть созданы в памяти или на диске, в зависимости от системы базы данных и конфигурации.

Временные таблицы можно использовать как обычные таблицы в SQL-запросах и заполнять данными с помощью операторов INSERT. Их также можно объединять с другими таблицами или использовать в подзапросах.

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

11. В чем разница между предложениями HAVING и WHERE?

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

Предложение WHERE используется для фильтрации данных перед их группировкой или агрегированием и применяется в операторах SELECT, UPDATE и DELETE. Оно фильтрует данные на основе условий, которые применяются к отдельным строкам.

Например, если вы хотите получить данные обо всех сотрудниках, чья зарплата превышает 50 000 долларов, вы можете использовать предложение WHERE в операторе SELECT:

SELECT *
FROM employees
WHERE salary > 50000;

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

Допустим, вы хотите получить среднюю зарплату сотрудников в каждом отделе и показать только те отделы, где средняя зарплата превышает 50 000 долларов. Вы можете использовать предложение HAVING в операторе SELECT:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

В этом примере предложение GROUP BY группирует данные по отделам, а функция AVG вычисляет среднюю зарплату для каждого отдела. Предложение HAVING фильтрует результаты, показывая только те отделы, в которых средняя зарплата превышает 50 000 долларов.

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

12. Что такое оконная функция и как она используется?

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

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

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

SELECT 
    date, 
    region, 
    product, 
    sales_amount,
    AVG(sales_amount) OVER (
        PARTITION BY region, product 
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as rolling_avg_sales
FROM 
    sales_data

В этом запросе функция AVG() используется в качестве оконной функции для расчета скользящего среднего значения продаж. Предложение PARTITION BY делит данные на разделы или группы по регионам и продуктам, а предложение ORDER BY сортирует данные по дате внутри каждого раздела. Предложение ROWS BETWEEN задает диапазон строк, которые должны быть включены в оконную рамку, в данном случае текущая строка и две предыдущие.

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

13. В чем разница между транзакцией и batch?

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

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

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

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

14. В чем разница между скалярной и табличной функцией?

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

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

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

Одно из ключевых различий между скалярными и табличными функциями заключается в том, что скалярные функции можно вызывать внутри запроса, то есть использовать их как часть предложений SELECT, WHERE или ORDER BY. Табличные функции должны вызываться как часть предложения FROM, поскольку они возвращают таблицу.

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

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

15. Что такое нормализация и почему она важна?

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

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

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

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

Нормализация обычно выполняется с помощью серии шагов, известных как нормальные формы. Наиболее часто используемые нормальные формы – это первая нормальная форма (1НФ), вторая нормальная форма (2НФ) и третья нормальная форма (3НФ). Каждая нормальная форма строится на основе предыдущей, с каждым шагом добавляя все больше правил и ограничений для обеспечения целостности и непротиворечивости данных.

Тут ссылка на вторую часть этой серии вопросов для собеседования по SQL