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

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

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

Подпишитесь на наш ТЕЛЕГРАМ КАНАЛ РАБОТА ДЛЯ ТЕСТИРОВЩИКА.ВАКАНСИИ

1. Что такое подзапрос SQL и каково его назначение?

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

Синтаксис подзапроса в SQL выглядит следующим образом:

SELECT 
  column_name(s)
FROM 
  table_name
WHERE 
  column_name operator
    (SELECT 
        column_name(s)
    FROM 
        table_name
    WHERE condition);

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

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

Чтобы лучше разобраться, давайте рассмотрим пример. Представим сценарий, в котором есть две таблицы: одна из них — “students”, а другая — “grade” (оценка). Мы хотим вывести имена всех студентов, которые получили оценку выше среднего. Вот как мы можем использовать подзапрос для решения этой задачи:

SELECT name
FROM students
WHERE grade > (SELECT AVG(grade) FROM grades);

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

Подзапросы можно использовать и с другими операторами SQL, такими как SELECT, INSERT, UPDATE и DELETE. Они могут быть вложены в несколько уровней для получения более сложных данных. Важно отметить, что подзапросы влияют на производительность, поэтому важно использовать их с умом и оптимизировать при необходимости.

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

2. Что такое индекс в SQL и как он повышает производительность?

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

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

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

Пример

Рассмотрим пример таблицы “employees” со столбцами “id”, “name”, “department” и “salary”. Если мы хотим найти всех сотрудников отдела “sales”, которые зарабатывают более 50 000 долларов в год, мы можем написать такой запрос:

SELECT id, name
FROM employees
WHERE department = 'sales' AND salary > 50000;

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

Чтобы создать индекс, используем оператор CREATE INDEX:

CREATE INDEX idx_employees_dept_salary ON employees (department, salary);

Это создает индекс “idx_employees_dept_salary” для таблицы “employees”, в который включены столбцы “department” и “salary”. Это означает, что индекс содержит копию столбцов “department” и “salary” для каждой строки таблицы, отсортированных в определенном порядке.

В итоге механизм БД ищет в индексе все строки, в которых в столбце “department” есть “sales”, а затем просматривает столбец “salary” для каждой из этих строк, чтобы узнать, не превышает ли зарплата 50 000 долларов. Механизм БД может сделать это очень быстро, потому что он просматривает только соответствующие строки в индексе, а не сканирует всю таблицу.

Заключение

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

3. Что такое оптимизатор SQL-запросов и как он работает?

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

Оптимизатор запросов изучает синтаксис запроса, определяет таблицы и столбцы и выбирает наилучший способ получения и объединения данных. При этом он рассматривает несколько стратегий и оценивает их на основе стоимости, которая обычно измеряется в терминах дискового ввода-вывода, использования процессора и памяти. Цель оптимизатора — найти план выполнения, который требует наименьшего количества ресурсов для получения и обработки данных.

Рассмотрим следующий SQL-запрос:

SELECT *
FROM orders
WHERE customer_id = 123
AND order_date >= '2022-01-01'

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

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

В целом оптимизатор SQL-запросов — это важный компонент СУБД, позволяющий пользователям создавать запросы, не заботясь о деталях реализации, и при этом добиваться высокой производительности.

4. Что такое хранимая процедура и каковы её преимущества?

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

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

CREATE PROCEDURE get_customers
AS
BEGIN
    SELECT * FROM customers;
END

Эта хранимая процедура извлекает все строки из таблицы “customers” и возвращает их вызывающей стороне. Процедура может быть выполнена одной командой:

EXEC get_customers;

Преимущества хранимых процедур

1. Повышенная производительность. Хранимые процедуры компилируются и оптимизируются движком базы данных, что может привести к более быстрому выполнению операции, чем специальные SQL-запросы.
2. Возможность повторного использования. Хранимые процедуры можно вызывать из нескольких приложений и использовать для выполнения сложных операций с базой данных, что сокращает объем кода, который необходимо писать и поддерживать.
3. Безопасность. Хранимые процедуры можно использовать для контроля доступа к конфиденциальным операциям базы данных, ограничивая доступ определенным пользователям или ролям. Это помогает предотвратить несанкционированный доступ.
4. Удобство обслуживания. Хранимые процедуры можно изменять, не затрагивая использующие их приложения. Это облегчает внесение изменений в схему базы данных или логику запросов без разрушения существующих приложений.
5. Согласованность. Хранимые процедуры позволяют обеспечивать согласованность операций с базой данных, инкапсулируя сложную логику и применяя бизнес-правила. Это помогает предотвратить возникновение различных ошибок.

Предположим, что у вас есть хранимая процедура, которая вставляет нового клиента в БД:

CREATE PROCEDURE insert_customer
    @name varchar(50),
    @email varchar(50),
    @phone varchar(20)
AS
BEGIN
    INSERT INTO customers (name, email, phone)
    VALUES (@name, @email, @phone);
END

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

5. Что такое транзакции и зачем они нужны?

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

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

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

Пример транзакции SQL

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;

COMMIT TRANSACTION;

В этой транзакции выполняются два оператора для перевода 100 единиц валюты со счета 123 на счет 456. Оператор BEGIN TRANSACTION начинает транзакцию, а оператор COMMIT TRANSACTION завершает её. Если во время транзакции произойдет ошибка, например сбой питания или нарушение ограничений, то база данных автоматически откатит транзакцию и отменит все внесенные изменения.

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

6. Что такое представление SQL и как оно используется?

Представление (VIEW) — это виртуальная таблица, которая является производной от одной или нескольких таблиц в базе данных. Такие виртуальные таблицы используются для упрощения сложных запросов, представляя данные в более организованном и осмысленном виде. Они также могут использоваться для соблюдения политики безопасности, скрытия конфиденциальной информации и сокращения объема данных, которые необходимо извлекать из БД.

Для создания представления можно использовать оператор CREATE VIEW. Вот пример представления, которое извлекает информацию из двух таблиц:

CREATE VIEW employee_salary AS
SELECT employee_name, salary
FROM employee_info
JOIN salary_info ON employee_info.employee_id = salary_info.employee_id;

В этом примере представление “employee_salary” объединяет данные из двух таблиц “employee_info” и “salary_info”, соединяя их по столбцу “employee_id”. Представление возвращает только два столбца, “employee_name” и “salary”, и скрывает остальные столбцы из исходных таблиц.

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

SELECT * FROM employee_salary;

Этот запрос вернет набор результатов с двумя столбцами, “employee_name” и “salary”, который включает всех сотрудников в “employee_info” таблице.

Важность представлений

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

1. Упрощение сложных запросов. Представления позволяют скрывать сложность объединений, подзапросов и других продвинутых функций. Это облегчает написание и сопровождение SQL-запросов с течением времени.
2. Усиление безопасности. Представления можно использовать для обеспечения соблюдения политики безопасности, скрывая конфиденциальную информацию от пользователей, не имеющих разрешения на доступ к ней.
3. Улучшение производительности. С помощью представлений можно уменьшать объем данных, которые необходимо извлечь из БД, отфильтровывая всё лишнее. Это позволяет повысить производительность запросов и снизить нагрузку на сервер.
4. Уменьшение дублирования кода. Представления можно использовать для устранения дублирования кода, предоставляя единый источник информации для сложных SQL-запросов. Это облегчает сопровождение и обновление кода SQL с течением времени.
5. Упрощение анализа данных. Представления помогают более наглядно представлять данные аналитикам и бизнес-пользователям. Это позволяет лучше понять сложные взаимосвязи между данными и принять более обоснованные решения.

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

7. Что такое функция и чем она отличается от хранимой процедуры?

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

Функции обычно используются для решения следующих задач:

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

Вот несколько примеров часто используемых функций SQL:

• COUNT: возвращает количество строк, соответствующих заданному условию в таблице
• AVG: вычисляет среднее значение из набора числовых значений
• MAX: возвращает наибольшее значение из набора значений
• MIN: возвращает наименьшее значение из набора значений

Отличия функций и хранимых процедур

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

1. Возвращаемое значение. Функции всегда возвращают значение, в то время как хранимые процедуры могут этого не делать.
2. Входные параметры. Функции могут иметь входные параметры, но не могут иметь выходные. Хранимые процедуры могут иметь как входные, так и выходные параметры.
3. Выполнение. SQL-функции выполняются как часть оператора, в то время как хранимые процедуры выполняются как независимые единицы кода.
4. Сложность. Функции обычно проще и направлены на выполнение конкретных задач, в то время как хранимые процедуры могут быть более сложными и включать в себя несколько операторов SQL и логику программирования.

Примеры

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

-- SQL Function Example
CREATE FUNCTION get_avg_salary (@dept_name VARCHAR(50))
RETURNS INT
AS
BEGIN
   DECLARE @result INT
   SELECT @result = AVG(salary)
   FROM employee_info
   WHERE department = @dept_name
   RETURN @result
END

Пример хранимой процедуры:

CREATE PROCEDURE insert_employee_info
    @name VARCHAR(50),
    @dob DATE,
    @salary INT
AS
BEGIN
    INSERT INTO employee_info (name, dob, salary)
    VALUES (@name, @dob, @salary)
END

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

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

8. Что такое денормализация и когда её целесообразно использовать?

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

Когда использовать денормализацию?

Денормализация должна применяться с умом и только в тех случаях, когда она уместна. Вот несколько сценариев:

1. Высокопроизводительные приложения. Денормализация часто используется в высокопроизводительных приложениях, где важен быстрый доступ к данным. С её помощью можно продублировать данные в разных таблицах и избежать соединений.
2. Отчетность и аналитика. Приложения для создания отчетов и аналитики часто требуют сложных запросов, включающих множество соединений по многим таблицам. Денормализация позволяет повысить производительность таких запросов за счет сокращения их количества.
3. Высокая нагрузка. Денормализация может помочь уменьшить нагрузку на сервер, сократив количество запросов, необходимых для получения данных, или уменьшить сложность этих запросов. Это достигается дублированием данных в разных таблицах.

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

1. Определите “горячие точки”.  Перед денормализацией базы данных важно определить “горячие точки”, где производительность является критической. Это поможет определить, какие таблицы необходимо денормализовать, а какие следует оставить нормализованными.
2. Используйте денормализацию с умом. Слишком большое количество избыточных данных может привести к несоответствиям и затруднить обслуживание базы данных с течением времени.
3. Сохраняйте данные последовательными.  При дублировании данных в таблицах важно сохранять их последовательность, чтобы избежать несоответствий. Этого можно добиться с помощью триггеров или хранимых процедур, обновляющих данные во всех связанных таблицах при внесении изменений.
4. Используйте индексы. Денормализация может привести к созданию больших таблиц с большим количеством данных. Чтобы обеспечить высокую производительность запросов, важно использовать индексы для оптимизации запросов и сокращения времени, затраченного на их выполнение.

Заключение

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

9. Что такое кластерный индекс и чем он отличается от некластерного?

Индексы — одни из важнейших компонентов базы данных. Они помогают ускорить выполнение запросов и повысить производительность. Индексы бывают двух видов: кластерные и некластерные.

Что такое кластерный индекс?

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

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

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

Что такое некластерный индекс?

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

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

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

Отличия кластерного и некластерного индексов

Ключевыми различиями между кластерными и некластерными индексами являются:

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

Пример

Давайте разберем пример. Предположим, у нас есть таблица “Employees”, содержащая следующие столбцы:

• EmployeeID (первичный ключ)
• FirstName
• LastName
• BirthDate
• HireDate

Если мы хотим создать кластерный индекс для столбца “EmployeeID”, данные в таблице будут упорядочены на основе значений в столбце “EmployeeID”.

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

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

10. Что такое триггер и как он используется?

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

Что такое SQL-триггер?

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

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

Как работает SQL-триггер?

Когда происходит событие, СУБД автоматически вызывает триггер, который затем выполняет набор операторов SQL, определенных в триггере.

Триггеры определяются для каждой таблицы и создаются с помощью оператора CREATE TRIGGER. Они могут запускаться до или после наступления события и выполняться один раз для каждой затронутой строки либо один раз для каждого оператора.

Синтаксис создания триггера:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[REFERENCING NEW AS new OLD AS old]
[FOR EACH ROW]
WHEN (condition)
DECLARE
{Variable declarations}
BEGIN
{SQL statements}
END;

Пример триггера

Допустим, у нас есть таблица “Orders”, которая содержит следующие столбцы:

• OrderID
• CustomerID
• OrderDate
• TotalAmount

Мы хотим создать триггер, который будет обновлять таблицу “Customer” каждый раз, когда в таблицу “Orders” будет вставлен новый заказ. Триггер будет обновлять столбец “TotalAmount” таблицы “Customer” с общей суммой всех заказов, которые разместил клиент.

Для создания этого триггера мы можем использовать следующий SQL-код:

CREATE TRIGGER update_customer_total_amount
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
  UPDATE Customer
  SET TotalAmount = TotalAmount + NEW.TotalAmount
  WHERE CustomerID = NEW.CustomerID;
END;

В этом триггере мы используем предложение AFTER INSERT, чтобы указать, что триггер должен выполняться после вставки новой строки в таблицу “Orders”. Затем мы используем предложение FOR EACH ROW, чтобы указать, что триггер должен выполняться один раз для каждой строки, затронутой оператором вставки. Наконец, мы используем ключевое слово NEW для ссылки на новую вставленную строку и обновляем столбец “TotalAmount” таблицы “Customer” для соответствующего клиента.

Заключение

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

11. Что такое ограничение SQL и какие распространенные типы вы знаете?

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

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

Ограничение NOT NULL

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

Пример:

CREATE TABLE Employees (
  EmployeeID int NOT NULL,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  Age int
);

Ограничение UNIQUE

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

Пример:

CREATE TABLE Customers (
  CustomerID int PRIMARY KEY,
  Email varchar(50) UNIQUE,
  FirstName varchar(50),
  LastName varchar(50)
);

Ограничение PRIMARY KEY

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

Пример:

CREATE TABLE Products (
  ProductID int PRIMARY KEY,
  ProductName varchar(50),
  Price decimal(10, 2),
  Description varchar(255)
);

Ограничение FOREIGN KEY

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

Пример:

CREATE TABLE Orders (
  OrderID int PRIMARY KEY,
  CustomerID int,
  OrderDate date,
  TotalAmount decimal(10, 2),
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Ограничение CHECK

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

Пример:

CREATE TABLE Employees (
  EmployeeID int PRIMARY KEY,
  FirstName varchar(50),
  LastName varchar(50),
  Age int,
  Salary decimal(10, 2),
  CHECK (Age >= 18 AND Salary >= 0)
);

Заключение

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

12. Что такое оператор UNION и для чего он используется?

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

Синтаксис:

SELECT column1, column2, …
FROM table1
UNION
SELECT column1, column2, …
FROM table2;

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

Рассмотрим две таблицы, Table1 и Table2, которые содержат информацию о сотрудниках и их зарплатах.

Table1:

ID	Name	Salary
1	John	50000
2	Mary	60000
3	Jack	55000

Table2:

ID	Name	Salary
4	Mark	65000
5	Jane	55000
6	Eric	70000

Чтобы объединить результаты двух таблиц, мы можем использовать оператор UNION следующим образом:

SELECT ID, Name, Salary
FROM Table1
UNION
SELECT ID, Name, Salary
FROM Table2;

Приведенный выше оператор SQL вернет следующий результат:

ID	Name	Salary
1	John	50000
2	Mary	60000
3	Jack	55000
4	Mark	65000
5	Jane	55000
6	Eric	70000

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

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

13. Что такое оператор CASE и как он используется?

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

Синтаксис оператора CASE выглядит следующим образом:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Оператор CASE оценивает условия в том порядке, в котором они записаны. Если условие выполнено, возвращается соответствующий результат. Если ни одно из условий не выполняется, возвращается результат по умолчанию.

Пример применения CASE

Рассмотрим следующую таблицу, содержащую информацию о сотрудниках и их зарплатах:

Name	Salary
John	50000
Mary	60000
Jack	55000
Mark	65000
Jane	55000
Eric	70000

Мы можем использовать CASE, чтобы вернуть новый столбец, который классифицирует зарплату каждого сотрудника как “High”, “Medium” или “Low”. Синтаксис оператора CASE для этого примера выглядит следующим образом:

SELECT
  Name,
  Salary,
  CASE
    WHEN Salary >= 60000 THEN 'High'
    WHEN Salary >= 55000 AND Salary < 60000 THEN 'Medium'
    ELSE 'Low'
  END AS Salary_Category
FROM
  employees;

В этом SQL-коде мы использовали CASE для оценки каждой строки из столбца “Salary”. Если зарплата больше или равна 60000, результат – “High”. Если зарплата больше или равна 55000 и меньше 60000, результат будет “Medium”. Если ни одно из этих условий не выполняется, результат будет “Low”.

Вывод вышеприведенного кода будет выглядеть следующим образом:

Name	Salary	Salary_Category
John	50000	Low
Mary	60000	High
Jack	55000	Medium
Mark	65000	High
Jane	55000	Medium
Eric	70000	High

Как мы видим, CASE позволил нам разделить зарплаты сотрудников на высокие, средние и низкие, что облегчает анализ и работу с данными.

Заключение

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

14. Что такое GROUP BY и как оно используется?

Предложение GROUP BY используется для группировки строк с одинаковыми значениями в итоговые строки. Например, “найти общий объем продаж по месяцам” или “найти среднюю зарплату по отделам”. GROUP BY используется в сочетании с оператором SELECT и агрегатными функциями (такими как SUM, COUNT, AVG и т. д.) для создания сводных отчетов.

Синтаксис предложения GROUP BY выглядит следующим образом:

SELECT column1, column2, ..., aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

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

Пример применения GROUP BY

Рассмотрим таблицу, содержащую информацию о сотрудниках и их зарплатах.

Name	Department	Salary
John	HR	50000
Mary	Sales	60000
Jack	HR	55000
Mark	Sales	65000
Jane	HR	55000
Eric	Sales	70000

Мы можем использовать предложение GROUP BY, чтобы сгруппировать сотрудников по отделам и вычислить среднюю зарплату для каждого отдела. SQL-код для этой операции выглядит следующим образом:

SELECT
  Department,
  AVG(Salary) AS Average_Salary
FROM
  employees
GROUP BY
  Department;

Здесь мы использовали GROUP BY для группировки сотрудников по отделам. Функция AVG используется для расчета средней зарплаты для каждого отдела. Вывод выглядит следующим образом:

Department	Average_Salary
HR	53333.33
Sales	65000.00

Как мы видим, GROUP BY позволило нам сгруппировать сотрудников по отделам и вычислить среднюю зарплату для каждого отдела. Такой тип запроса полезен для анализа данных и создания сводных отчетов.

15. Что такое ORDER BY и как оно используется?

Предложение ORDER BY используется в сочетании с оператором SELECT для сортировки результатов запроса по возрастанию или убыванию на основе одного или нескольких столбцов.

Синтаксис предложения ORDER BY выглядит следующим образом:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 ASC|DESC, column2 ASC|DESC, ...;

ORDER BY сортирует результаты оператора SELECT по указанным столбцам в порядке возрастания (ASC) или убывания (DESC).

Пример применения ORDER BY

Рассмотрим таблицу, содержащую информацию о товарах и их ценах:

Product	Category	Price
Laptop	Electronics	1200
Phone	Electronics	800
TV	Electronics	1500
Sofa	Furniture	600
Chair	Furniture	100
Table	Furniture	400

Мы можем использовать ORDER BY для сортировки товаров по цене в порядке возрастания. SQL-код для этой операции выглядит следующим образом:

SELECT
  Product,
  Category,
  Price
FROM
  products
ORDER BY
  Price ASC;

Результат выполнения приведенного выше SQL-кода выглядит следующим образом:

Product	Category	Price
Chair	Furniture	100
Table	Furniture	400
Sofa	Furniture	600
Phone	Electronics	800
Laptop	Electronics	1200
TV	Electronics	1500

Заключение

Предложение ORDER BY – это важная часть SQL, которая позволяет сортировать результаты запроса по возрастанию или убыванию на основе одного или нескольких столбцов. Она используется в сочетании с оператором SELECT. Используя предложение ORDER BY, вы можете анализировать и манипулировать данными таким образом, чтобы их было проще понять и легче с ними работать.

Перевод статьи «Most asked SQL interview questions in Data Engineering Interviews (Part II)».