Вопросы 11-20 для разработчиков
Ответ: Нормализация организует реляционные данные для минимизации избыточности и предотвращения аномалий обновления/вставки/удаления путем разделения таблиц на основе зависимостей, сохраняя при этом смысл.
| UNION | UNION ALL |
|---|---|
| Объединяет результаты из нескольких SELECT запросов и удаляет дублирующиеся строки | Объединяет результаты из нескольких SELECT запросов и сохраняет все дублирующиеся строки |
| Выполняет DISTINCT операцию, поэтому может быть медленнее | Не удаляет дубликаты, поэтому работает быстрее |
| Используется когда нужны уникальные результаты | Используется когда нужны все результаты, включая дубликаты |
-- UNION - удаляет дубликаты
SELECT emp_name FROM employees WHERE salary > 50000
UNION
SELECT emp_name FROM employees WHERE dept_id = 10;
-- UNION ALL - сохраняет дубликаты
SELECT emp_name FROM employees WHERE salary > 50000
UNION ALL
SELECT emp_name FROM employees WHERE dept_id = 10;
| Clustered Index | Non-Clustered Index |
|---|---|
| Хранит строки таблицы в физическом порядке ключа индекса | Хранит индексные данные отдельно от таблицы со ссылками на строки |
| Только один кластерный индекс на таблицу | Можно создать несколько некластерных индексов на таблице |
| Лучше для запросов диапазона и сортировки | Лучше для фильтрации, объединений и быстрого поиска |
| Обычно создается на первичном ключе | Обычно создается на часто ищущихся столбцах |
-- Clustered Index (на первичном ключе)
CREATE CLUSTERED INDEX idx_emp_id
ON employees(emp_id);
-- Non-Clustered Index (на часто ищущихся столбцах)
CREATE NONCLUSTERED INDEX idx_emp_name
ON employees(emp_name);
-- Составной Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_emp_dept_salary
ON employees(dept_id, salary);
Ответ: Сопоставление шаблонов в SQL выполняется с использованием оператора LIKE с подстановочными символами.
-- Начинается с 'A'
SELECT * FROM employees WHERE emp_name LIKE 'A%';
-- Заканчивается на 'son'
SELECT * FROM employees WHERE emp_name LIKE '%son';
-- Содержит 'john' где-нибудь
SELECT * FROM employees WHERE emp_name LIKE '%john%';
-- Ровно 4 буквы
SELECT * FROM employees WHERE emp_name LIKE '____';
-- Начинается на J, заканчивается на n
SELECT * FROM employees WHERE emp_name LIKE 'J%n';
-- Вторая буква 'a'
SELECT * FROM employees WHERE emp_name LIKE '_a%';
Ответ: Бегущий итог рассчитывается с использованием оконной функции SUM() с клаузой OVER(). Это добавляет значение каждой строки к накопленному итогу, сохраняя отдельные строки.
SELECT
product_id,
sale_date,
amount,
SUM(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
) AS running_total
FROM sales;
-- Этот запрос вычисляет кумулятивный итог продаж для каждого продукта в хронологическом порядке
ROW_NUMBER() - номер строкиRANK() - ранг с пропускамиDENSE_RANK() - ранг без пропусковLAG() - значение из предыдущей строкиLEAD() - значение из следующей строкиОтвет: Correlated subquery — это подзапрос, который ссылается на столбцы из внешнего запроса. Он выполняется один раз для каждой строки, обработанной внешним запросом.
Найти сотрудников, зарплата которых больше средней зарплаты их отдела:
SELECT e.employee_id, e.name, e.salary, e.department_id
FROM Employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM Employees e2
WHERE e2.department_id = e.department_id
);
e2.department_id = e.department_id - это связь с внешним запросомВозвращает сотрудников, зарплата которых выше средней зарплаты их отдела.
EXISTS - Найти клиентов, которые сделали хотя бы один заказ:
SELECT CustomerID
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.CustomerID = c.CustomerID
);
NOT EXISTS - Найти клиентов, которые не разместили никакие заказы:
SELECT CustomerID
FROM Customers c
WHERE NOT EXISTS (
SELECT 1
FROM Orders o
WHERE o.CustomerID = c.CustomerID
);
IN - Найти сотрудников, работающих в определенных отделах:
SELECT emp_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'NYC');
Возвращает клиентов, которые имеют хотя бы один заказ.
Ответ: Anti-join возвращает строки из одной таблицы, которые не имеют совпадающих строк в другой таблице. Обычно реализуется с использованием NOT EXISTS или LEFT JOIN с IS NULL.
SELECT c.CustomerID, c.CustomerName
FROM Customers c
WHERE NOT EXISTS (
SELECT 1
FROM Orders o
WHERE o.CustomerID = c.CustomerID
);
SELECT c.CustomerID, c.CustomerName
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;
LEFT JOIN возвращает все строки из левой таблицы (Customers)WHERE o.CustomerID IS NULL фильтрует только те строки, где совпадения не былоВозвращает клиентов, которые не разместили никакие заказы.
| ROW_NUMBER() | RANK() | DENSE_RANK() |
|---|---|---|
| Присваивает уникальный номер каждой строке | Присваивает одинаковый ранг дублирующимся значениям | Присваивает одинаковый ранг дублирующимся значениям |
| Дублирующиеся значения получают разные номера | Дублирующиеся значения получают одинаковый ранг | Дублирующиеся значения получают одинаковый ранг |
| Нет пропусков в нумерации | Пропускает следующий ранг после дубликатов | Не пропускает следующий ранг после дубликатов |
SELECT
emp_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
-- Если у нас есть зарплаты: 100, 90, 90, 80
-- ROW_NUMBER(): 1, 2, 3, 4
-- RANK(): 1, 2, 2, 4 (пропускает 3)
-- DENSE_RANK(): 1, 2, 2, 3 (не пропускает)
Ответ: LAG и LEAD — это оконные функции, используемые для доступа к значениям из предыдущей или следующей строки без использования self-join.
-- LAG - получить значение из предыдущей строки
LAG(column_name, offset, default_value) OVER (
PARTITION BY partition_column
ORDER BY order_column
)
-- LEAD - получить значение из следующей строки
LEAD(column_name, offset, default_value) OVER (
PARTITION BY partition_column
ORDER BY order_column
)
Пример 1: Сравнение продаж текущего месяца с предыдущим:
SELECT
month,
sales,
LAG(sales, 1, 0) OVER (ORDER BY month) AS prev_month_sales,
sales - LAG(sales, 1, 0) OVER (ORDER BY month) AS sales_difference
FROM monthly_sales;
Пример 2: Найти следующую зарплату сотрудника:
SELECT
emp_name,
salary,
LEAD(salary) OVER (ORDER BY emp_id) AS next_emp_salary
FROM employees;
Пример 3: Анализ тенденций по отделам:
SELECT
dept_id,
emp_name,
salary,
LAG(salary) OVER (PARTITION BY dept_id ORDER BY salary) AS prev_salary,
LEAD(salary) OVER (PARTITION BY dept_id ORDER BY salary) AS next_salary
FROM employees;