SQL

Question 1

Что такое SQL

Answer 1

SQL - structured query language
Язык запросов для работы с реляционными бд.
Состоит из нескольких групп команд:
1) DML (insert, update, delete)
2) DDL (create, truncate, alter, drop)
3) DCL (grant, revoke)
4) TCL (start/begin transaction, checkpoint, commit, rollback)

Question 2

Что такое PRIMARY KEY?

Answer 2

Первичный ключ – столбец (или набор столбцов), уникально идентифицирующий каждую строку в таблице:

Гарантирует уникальность значений
Не допускает NULL значений
Автоматически создает кластеризованный индекс (в большинстве СУБД)

CREATE TABLE Users (
id INT PRIMARY KEY,
name VARCHAR(50)
);

Question 3

Для чего нужен FOREIGN KEY?

Answer 3

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

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES Users(id)
);

Question 4

Разница между CHAR и VARCHAR?

Answer 4

CHAR – фиксированная длина (дополняется пробелами до указанного размера)
VARCHAR – переменная длина (хранит только фактическое количество символов)
Char быстрее, потому что длина фиксированная, если же мы знаем, что длина может меняться, то лучше использовать varchar

Question 5

Разница кластеризованного и некластеризованного индекса

Answer 5

Индекс - это структура (обычно B-tree), которая позволяет быстро находить необходимые записи. Это происходит за счет бинарного поиска.

Кластеризованный индекс (обычно создается на Primary key). Данный индекс характеризуется тем, что таблица строиться по данному индексу. Дополнительно в памяти отдельная структура не создается.

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

Кластеризованный индекс - это упорядоченый порядок книг на полке.
Некластеризованный - отдельные карточки, где на полке какая книга стоит.

Question 6

Как правильно проверять NULL значения?

Answer 6

Для проверки NULL используется оператор IS NULL вместо сравнения с = NULL:
– Правильно:
SELECT * FROM Employees WHERE phone IS NULL;
Также можно использовать coalesce, ifnull()

Question 7

Типы ограничений Constraint

Answer 7

1) Unique
2) Primary key
3) Foreign key
4) Not null
5) Check
6) Default

Question 8

Разница между DELETE, TRUNCATE и DROP?

Answer 8

Delete - удалить запись в таблице, работает медленно, возможно откатиться и вернуть прежние значения, автоинкремент сохраняется
Truncate - удалить все записи таблицы, работает быстро, откатиться обычно нельзя, (в postgres и sql server в транзакции), автоинкремент обнуляется. (по сути очистка таблицы до первоначального состояния)
Drop - полное удаление таблицы, работает мгновенно, вернуть значения нельзя.

Question 9

Как выбрать уникальные записи без DISTINCT?

Answer 9

Воспользовать group by, который сгруппирует все записи по уникальным значениям

Question 10

Как найти дубликаты email?

Answer 10

select email, count() from t1
group by email
having count() > 1;

Question 11

Как удалить дубликаты?

Answer 11

DELETE FROM t t1
WHERE EXISTS (
SELECT 1
FROM t t2
WHERE t1.email = t2.email
AND t1.id > t2.id
);

Question 12

Что такое SELF JOIN? Пример

Answer 12

Соединение таблицы саму с собой.
Пример: поиск сотрудников и их менеджеров.

Question 13

Виды логических JOIN

Answer 13

1) INNER JOIN (дефолт)
2) LEFT JOIN
3) RIGHT JOIN
4) FULL OUTER JOIN
5) SELF JOIN
6) CROSS JOIN
7) NATURAL JOIN (сам соединяет схожие по названию колонки, не надежен)

Question 14

Как работает INSERT INTO SELECT?

Answer 14

Может вставлять записи запросом:
INSERT INTO t1(id, name, salary)
SELECT id, name, salary
FROM other_table;

Question 15

Как обновить данные из другой таблицы?

Answer 15

UPDATE first_table t1
JOIN other_table t2 using(id)
SET t1.age = t2.age;

Question 16

Как добавить столбец в таблицу?

Answer 16

ALTER TABLE Employees
ADD COLUMN birth_date DATE;

Question 17

Разница между WHERE и HAVING?

Answer 17

WHERE фильтрует строки до группировки
HAVING фильтрует результаты после группировки

Question 18

Как вывести топ-3 самых дорогих товаров?

Answer 18

– MySQL
SELECT * FROM Products
ORDER BY price DESC
LIMIT 3;

– SQL Server
SELECT TOP 3 * FROM Products
ORDER BY price DESC;

Question 19

Уровни изолированности

Answer 19

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

Read uncommitted (полный доступ) - могут читать незакоммиченные данные. Не используется
Read committed (по дефолту в постгрес) - не могут видеть незакомиченные данные.
REPEATABLE read (повторное чтнение) - если транзакция берет данные из места, то каждый select получает одни и те же данные, они как заморожены.
SERIALIZABLE - полный запрет. Гарантирует, что транзакции выполняться так, словно они последовательные. Минусы: может проседать производительность.

Question 20

Оконные фукнции

Answer 20

Функции, которые позволяют обрабатывать групповые вычисления не схлопывая строки в одну, как это делает group by.

Шаблон оконной функции:
WINDOW_FUNCTION(column_name)
OVER (
PARTITION BY partition_column
ORDER BY order_column
ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW
)

Question 21

Как посчитать скользящее среднее

Answer 21

SELECT date, amount,
AVG(amount) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM Sales;

Question 22

Разница между RANK() и DENSE_RANK()?

Answer 22

RANK() – пропускает номера при одинаковых значениях (1, 2, 2, 4)
DENSE_RANK() – не пропускает номера (1, 2, 2, 3)

Question 23

Как работает LAG() and LEAD()

Answer 23

Ответ: Доступ к данным из предыдущей/следующей строки:

sql

SELECT date, amount,
LAG(amount) OVER (ORDER BY date) AS prev_amount,
LEAD(amount) OVER (ORDER BY date) AS next_amount
FROM Sales;

Question 24

Как рассчитать долю от общей суммы? Окно

Answer 24

```sql
SELECT category, amount,
amount / SUM(amount) OVER () AS total_share
FROM Sales;
~~~

Question 25

Как найти накопленную сумму?

Answer 25

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS cumulative_sum FROM Sales;

Question 26

Разница между COUNT(*) и COUNT(column)?

Answer 26

COUNT(*) – считает все строки COUNT(column) – считает строки с не-NULL значениями в указанном столбце

Question 27

Что такое покрывающий индекс?

Answer 27

Индекс, содержащий все поля, необходимые для запроса, что позволяет избежать чтения самой таблицы: -- Создание покрывающего индекса CREATE INDEX idx_cover ON Orders (customer_id, order_date, amount); -- Запрос использует только индекс SELECT customer_id, order_date, amount FROM Orders WHERE customer_id = 123;

Question 28

Как проверить план выполнения запроса?

Answer 28

-- MySQL EXPLAIN SELECT * FROM Employees WHERE department_id = 5; -- PostgreSQL EXPLAIN ANALYZE SELECT * FROM Employees WHERE department_id = 5; -- SQL Server SET SHOWPLAN_TEXT ON; GO SELECT * FROM Employees WHERE department_id = 5;

Question 29

Когда индексы не используются

Answer 29

1) При использовании функций над полями, если нет функционального индекса: WHERE UPPER(name) = 'ИВАН' 2) При условиях (когда отбирается >15-20% данных) 3) При маленьких таблицах 4) При оператарах, которые делают полную выборку данных (not in, not exists, not like)

Question 30

Что такое статистика запросов?

Answer 30

Статистика запросов — это набор данных о таблицах и колонках (количество строк, уникальность значений, гистограммы, частоты), которые оптимизатор использует для построения оптимального плана выполнения запроса. На основе статистики оптимизатор решает: 1) какие индексы использовать, 2) какой порядок join выбрать, 3) какие таблицы читать первыми, 4) как оценить селективность условий.

Question 31

Как работает составной индекс? (создание индекса, примеры эффективных и неэффективных запросов)

Answer 31

Индекс по нескольким столбцам, эффективный для запросов с префиксными полями (первой частью): -- Создание индекса CREATE INDEX idx_composite ON Employees (department_id, salary); -- Эффективные запросы SELECT * FROM Employees WHERE department_id = 5; SELECT * FROM Employees WHERE department_id = 5 AND salary > 50000; -- Неэффективные запросы SELECT * FROM Employees WHERE salary > 50000;

Question 32

Как написать фильтр для записей created_at не старше 30 дней с сегодня

Answer 32

WHERE created_at > NOW() - INTERVAL '30 days'

Question 33

Как найти сумму всех транзакций пользователя user_id? (оконная функция)

Answer 33

SUM(amount) OVER (PARTITION BY user_id)

Question 34

Как посчитать среднюю сумму за последние 3 часа? (amount, user_id, event_time) (оконная функция)

Answer 34

AVG(amount) OVER ( PARTITION BY user_id ORDER BY event_time RANGE BETWEEN INTERVAL '3 hours' PRECEDING AND CURRENT ROW )

Question 35

Как оптимизировать запрос с JOIN?

Answer 35

1) Использовать индексы на полях соединения 2) Фильтровать данные до соединения (с помощью cte или подзапросов) 3) Использовать наименьшую из таблиц первой (это делается из-за того, что левая таблица загружается в память) 4) Избегать преобразования типов в условиях JOIN

Question 36

Риски избыточных индексов?

Answer 36

1) Замедление операций INSERT/UPDATE/DELETE 2) Увеличение размера базы данных (памяти)

Question 37

Стратегии резервного копирования?

Answer 37

1( Полное резервное копирование (Full): Копирование всех данных 2) Дифференциальное (Differential): Копирование изменений с момента последнего полного бэкапа (как ком собирает данные) (быстрое восстановление, максимальная отказоустойчивость) 3) Инкрементальное (Incremental): Копирование изменений с момента последнего бэкапа любого типа (чтобы восстановить, нужно по цепочке собрать данные) (большие данные, частые бэкапы)

Question 38

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

Answer 38

from join where group by having window functions select distinct order by limit/offset/fetch

Question 39

Рекурсивный cte

Answer 39

WITH RECURSIVE EmployeeHierarchy AS ( -- Якорь рекурсии SELECT id, name, manager_id, 1 AS level FROM Employees WHERE manager_id IS NULL UNION ALL -- Рекурсивная часть SELECT e.id, e.name, e.manager_id, eh.level + 1 FROM Employees e JOIN EmployeeHierarchy eh ON e.manager_id = eh.id ) SELECT * FROM EmployeeHierarchy;

Question 40

Что такое триггер? Типы

Answer 40

Ответ: Автоматически выполняемый код при событиях в БД: Типы по времени: BEFORE, AFTER, INSTEAD OF Типы по событию: INSERT, UPDATE, DELETE Типы по уровню: ROW-LEVEL (исполнится для каждой строки), STATEMENT-LEVEL (исполнится один раз) CREATE TRIGGER update_timestamp BEFORE UPDATE ON Orders FOR EACH ROW SET NEW.updated_at = NOW();

Question 41

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

Answer 41

1. **Нет `RETURN`** — процедура не возвращает значение. 2. **Вызываются через `CALL`**, а не через `SELECT`. 3. **Часто изменяют данные** (`INSERT`, `UPDATE`, `DELETE`). 4. **Могут работать с транзакциями** (`COMMIT`, `ROLLBACK`) — то, чего нельзя в функциях. 5. Могут иметь **`IN`, `OUT`, `INOUT` параметры**, через которые можно передавать или получать значения.

Question 42

Что такое материализованное представление?

Answer 42

Ответ: Представление, которое физически хранит результаты запроса и периодически обновляется: Ускоряет сложные запросы Требует обновления при изменении данных Полезно для отчетности и аналитики ``` -- PostgreSQL CREATE MATERIALIZED VIEW sales_summary AS SELECT product_id, SUM(amount) FROM Sales GROUP BY product_id; -- Обновление REFRESH MATERIALIZED VIEW sales_summary; ```

Question 43

Как приводить типы данных в sql

Answer 43

column :: int

Question 44

Материализуется ли cte? И как указать не материализованное cte?

Answer 44

До 12 версии Postgres - да, после - нет. with cte as not materialized(...)

Question 45

Создание индекса и удаление

Answer 45

CREATE INDEX index_name ON table_name (column); DROP INDEX index_name;

Question 46

Что если использовать join без on, то какой вид join будет использован?

Answer 46

Будет использован cross join, то бишь декартовое произведение. А на практике, я проверил, будет ошибка.

Question 47

Как отключить определенный тип join?

Answer 47

SET enable_hashjoin = off; SET enable_nestejoin = off; SET enable_mergejoin = off;

Question 48

Разница между index scan, bitmap scan и seq scan

Answer 48

Seq Scan – последовательное сканирование всей таблицы. Используется когда: * нет подходящего индекса; * условие низко-селективное (выбирается большая часть таблицы); * таблица маленькая (индекс не даёт выигрыша). Index Scan – поиск строк через индекс с последующим обращением к таблице по TID. Эффективен когда: *выбирается малая доля строк (высокая селективность); * индекс точный и хорошо подходит под условие. Недостаток: много случайных чтений (random I/O). Bitmap Index Scan + Bitmap Heap Scan – оптимизатор строит битовую карту строк, удовлетворяющих условиям, а затем читает их пачками. Используется когда: * выбирается “средний” процент строк; * несколько индексных условий AND/OR; * random I/O слишком дорогой, а пачки читать выгоднее.

Question 49

Разница физических соединения join

Answer 49

**Hash Join** Используется, когда: - выполняется соединение по равенству (=) - индексов нет или слишком дорого их использовать - хотя бы одна сторона помещается в память (или частично) Плюсы: - быстрый при больших объёмах данных - хорошо работает параллельно - не требует индексов Минусы: - требует много памяти (work_mem) - может перейти на дисковые batch-и → это замедляет --- **Merge Join** Используется, когда: - обе таблицы отсортированы по join-ключу (или могут быть отсортированы) - join по “=”, “<”, “>”, “<=”, “>=” Плюсы: - один из самых быстрых join’ов - стабилен для больших таблиц - может быть очень эффективным при использовании индексов Минусы: - требует сортировки, если нет индекса - работает только с упорядочиваемыми условиями --- **Nested Loop** Используется, когда: - одна таблица маленькая (малое количество строк) - по второй можно быстро искать через индекс Плюсы: - лучший выбор при высокоселективном запросе - быстрый, когда индекс хороший Минусы: - без индекса — может работать очень медленно - не оптимален для больших выборок

Question 50

Типы индексов

Answer 50

1) B-tree - почти всегда используется 2) Hash - работает только с операциями (=). Редка используется, почти всегда B-tree 3) Gin - для поиска в тексте в таких операциях как like, rglike

Question 51

Селективность

Answer 51

Селективность — это доля строк таблицы, которые соответствуют условию фильтра или join’а. Оптимизатор PostgreSQL использует селективность для оценки стоимости плана (`cost`) и выбора оптимального способа доступа к данным. Например: - высокая селективность (мало строк) → выгодно использовать Index Scan или Nested Loop, - низкая селективность (много строк) → лучше Seq Scan или Hash Join. Если статистика устарела или неточная, оптимизатор может выбрать неоптимальный план.

Question 52

Как использовать distinct on

Answer 52

Позволяет вывести одну первую запись для группы. Аналог: select user_id, max(amout) from users group by user_id;

Question 53

Какое минимальное и максимальное возможное количество строк в результате: SELECT COUNT(*) FROM A FULL JOIN B ON A.id = B.id

Answer 53

5/15

Question 54

Как заджойнить таблицу саму с собой?

Answer 54

``` select * from table_name t1 join table_name t2 on t1.id = t2.id; ```

Question 55

Как округлять вниз и как просто округлять?

Answer 55

FLOOR(column) Просто округлять round(column, 2) Важно: column должно быть numeric

Question 56

Как вывести все агрегированные записи, даже где count() == 0

Answer 56

Нужно использовать left join на все айдюки

Question 57

Fact tables and dimension tables

Answer 57

Fact tables - центральные таблицы, хранят внешние ключи на dimension table. Dimension tables - таблицы измерений, хранят основную информацию о объектах.

Question 58

Как настроить партицирование

Answer 58

-- По диапазону дат CREATE TABLE Sales ( id INT, sale_date DATE, amount DECIMAL ) PARTITION BY RANGE (sale_date); -- Создание партиций CREATE PARTITION sales_q1 VALUES LESS THAN ('2025-04-01'); CREATE PARTITION sales_q2 VALUES LESS THAN ('2025-07-01');

Question 59

Что за vacuum в postgres?

Answer 59

Позволяет удалять старые строки в postgres.

Question 60

Какие ошибки решают транзакции?

Answer 60

1) Грязное чтение 2) Non-repeatable read (два select в одной транзакции дают разные результаты) 3) Фантомное чтение (два select в одной транзакции дают разное количество строк) 4) Update lost (два update из разных транзакций затирают друг друга) Решение: uncomited read 1 - commited read 1, 2, частично остальные - repeatable read 1, 2, 3, 4 - serializable

Question 61

Что вернет sum(null)?

Answer 61

null

Question 62

Функциональный блок с циклом па датам внутри

Answer 62

``` DO $$ DECLARE d DATE; BEGIN FOR d IN SELECT generate_series(DATE '2025-12-05', DATE '2025-12-13', INTERVAL '1 day')::DATE LOOP RAISE NOTICE 'Date: %', d; END LOOP; END $$; ```

Question 63

Чем ROW_NUMBER, RANK, DENSE_RANK отличаются?

Answer 63

ROW_NUMBER - задает порядок от 1 до n (если будут одинаковые записи, то первая будет i, вторая i + 1) [1 2 3 4] RANK - задает ранк каждой строке (если записи одинаковые, то они имеют один и тот же номер, но следующая запись будет i + (сколько записей пропустили)) [1 1 1 4] DENSE_RANK - то же, что и RANK, только ничего не пропускает [1 1 1 2]

Question 64

Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).

Answer 64

Три решения: 1) Оконные функции 2) Через max 3) Через limit offset with c1 as ( select salary, dense_rank() over (order by salary desc) as rnk from Employee ) select max(salary) as SecondHighestSalary from c1 where rnk = 2; with c1 as ( select salary from Employee where salary != (select max(salary) from Employee) ) select max(salary) as SecondHighestSalary from c1; select max(salary) as SecondHighestSalary from ( select distinct salary from Employee order by salary desc limit 1 offset 1) t1;