| № | Практическое задание | Коды |
|---|
| 1 | Создайте таблицу employees с полями id, name, salary, hire_date. | CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary NUMERIC(10, 2),
hire_date DATE DEFAULT CURRENT_DATE
);
|
| 2 | Вставьте три записи в таблицу employees. | INSERT INTO employees (name, salary, hire_date) VALUES
('Иван Петров', 75000.00, '2023-01-15'),
('Мария Смирнова', 82000.00, '2023-03-20'),
('Алексей Иванов', 65000.00, '2023-06-10');
|
| 3 | Выберите все строки и столбцы из таблицы employees. | |
| 4 | Выберите сотрудников с зарплатой больше 70000. | SELECT name, salary
FROM employees
WHERE salary > 70000;
|
| 5 | Увеличьте зарплату сотрудника 'Иван Петров' на 10%. | UPDATE employees
SET salary = salary * 1.1
WHERE name = 'Иван Петров';
|
| 6 | Удалите сотрудника с id = 3. | DELETE FROM employees
WHERE id = 3;
|
| 7 | Добавьте столбец department в таблицу employees, затем переименуйте его в dept. | ALTER TABLE employees
ADD COLUMN department VARCHAR(50);
ALTER TABLE employees
RENAME COLUMN department TO dept;
|
| 8 | Удалите таблицу employees (если она существует). | DROP TABLE IF EXISTS employees;
|
| 9 | Удалите все строки из таблицы employees, сохранив её структуру. | TRUNCATE TABLE employees;
|
| 10 | Выберите уникальные отделы из employees, отсортированные по алфавиту, и ограничьте вывод 5 записями. | SELECT DISTINCT dept
FROM employees
ORDER BY dept
LIMIT 5
-- или с OFFSET:
-- LIMIT 5 OFFSET 0;
|
| 11 | Найдите сотрудников с зарплатой от 50000 до 100000 из отделов 'IT' или 'Sales', исключая 'John'. | SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 100000
AND (dept = 'IT' OR dept = 'Sales')
AND NOT name = 'John';
|
| 12 | Найдите сотрудников из отделов 'HR', 'IT' и 'Finance'. | SELECT name, dept, salary
FROM employees
WHERE dept IN ('HR', 'IT', 'Finance');
|
| 13 | Найдите сотрудников, нанятых в первом квартале 2023 года. | SELECT name, hire_date
FROM employees
WHERE hire_date BETWEEN '2023-01-01' AND '2023-03-31';
|
| 14 | Найдите сотрудников, чьи имена начинаются с 'А'. | SELECT name
FROM employees
WHERE name LIKE 'А%';
|
| 15 | Найдите сотрудников, у которых не указан отдел. | SELECT name, dept
FROM employees
WHERE dept IS NULL;
|
| 16 | Выведите сотрудников, отсортированных по зарплате по убыванию, затем по имени по возрастанию. | SELECT name, salary
FROM employees
ORDER BY salary DESC, name ASC;
|
| 17 | Выведите трёх самых высокооплачиваемых сотрудников. | SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
-- или с FETCH:
-- FETCH FIRST 3 ROWS ONLY;
|
| 18 | Выведите сотрудников с 6-й по 10-ю позицию в рейтинге зарплат. | SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;
|
| 19 | Категоризируйте зарплаты: низкая (<50000), средняя (50000-100000), высокая (>100000). | SELECT name, salary,
CASE
WHEN salary < 50000 THEN 'Низкая'
WHEN salary BETWEEN 50000 AND 100000 THEN 'Средняя'
ELSE 'Высокая'
END AS category
FROM employees;
|
| 20 | Выведите сотрудников, отсортированных по отделу, поместив NULL в начало списка. | SELECT name, dept
FROM employees
ORDER BY dept NULLS FIRST;
-- или NULLS LAST (по умолчанию для ASC):
|
| 21 | Посчитайте общее количество сотрудников и количество сотрудников с указанным отделом. | SELECT
COUNT(*) AS total,
COUNT(dept) AS with_dept
FROM employees;
|
| 22 | Найдите суммарную, среднюю, минимальную и максимальную зарплату. | SELECT
SUM(salary) AS total_salary,
ROUND(AVG(salary), 2) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees;
|
| 23 | Найдите среднюю зарплату по каждому отделу. | SELECT dept, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept;
|
| 24 | Найдите отделы, где средняя зарплата превышает 80000. | SELECT dept, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 80000;
|
| 25 | Вычислите сумму зарплат по отделам и общий итог. | SELECT dept, SUM(salary) AS total
FROM employees
GROUP BY ROLLUP(dept)
ORDER BY dept;
|
| 26 | Вычислите сумму зарплат по всем комбинациям отдела и должности. | SELECT dept, position, SUM(salary) AS total
FROM employees
GROUP BY CUBE(dept, position)
ORDER BY dept, position;
|
| 27 | Получите итоги отдельно по отделам и отдельно по должностям. | SELECT dept, position, SUM(salary) AS total
FROM employees
GROUP BY GROUPING SETS ((dept), (position))
ORDER BY dept, position;
|
| 28 | Посчитайте количество сотрудников с зарплатой выше 70000 и среднюю зарплату в IT-отделе. | SELECT
COUNT(*) FILTER (WHERE salary > 70000) AS high_earners,
ROUND(AVG(salary) FILTER (WHERE dept = 'IT'), 2) AS avg_it_salary
FROM employees;
|
| 29 | Сгруппируйте сотрудников по отделу и должности, подсчитав количество в каждой группе. | SELECT dept, position, COUNT(*) AS cnt
FROM employees
GROUP BY dept, position
ORDER BY dept, position;
|
| 30 | Напишите запрос, демонстрирующий порядок: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. | SELECT dept, COUNT(*) AS cnt
FROM employees
WHERE salary > 40000
GROUP BY dept
HAVING COUNT(*) > 1
ORDER BY cnt DESC
LIMIT 5;
|
| 31 | Объедините таблицы employees и departments, чтобы получить название отдела для каждого сотрудника. | SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
|
| 32 | Выведите всех сотрудников и их отделы, включая тех, у кого отдел не указан. | SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
|
| 33 | Выведите все отделы и сотрудников в них, включая отделы без сотрудников. | SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;
|
| 34 | Выведите всех сотрудников и все отделы, показывая несоответствия с обеих сторон. | SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;
|
| 35 | Создайте декартово произведение сотрудников и проектов для назначения. | SELECT e.name, p.project_name
FROM employees e
CROSS JOIN projects p;
|
| 36 | Найдите пары сотрудников, работающих в одном отделе (исключая пару с самим собой). | SELECT a.name AS employee1, b.name AS employee2, a.dept
FROM employees a
INNER JOIN employees b ON a.dept = b.dept AND a.id < b.id;
|
| 37 | Объедините таблицы employees и departments по одноимённым столбцам (dept_id). | SELECT *
FROM employees
NATURAL JOIN departments;
|
| 38 | Для каждого отдела найдите трёх самых высокооплачиваемых сотрудников. | SELECT d.dept_name, e.name, e.salary
FROM departments d
CROSS JOIN LATERAL (
SELECT name, salary
FROM employees
WHERE dept_id = d.id
ORDER BY salary DESC
LIMIT 3
) e;
|
| 39 | Найдите сотрудников, не привязанных ни к одному отделу. | SELECT e.name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM departments d WHERE d.id = e.dept_id
);
|
| 40 | Найдите отделы, в которых есть хотя бы один сотрудник. | SELECT d.dept_name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.dept_id = d.id
);
|
| 41 | Найдите сотрудников, их отделы, проекты и менеджеров, добавив индексы для ускорения. | CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_proj_lead ON projects(lead_id);
SELECT e.name, d.dept_name, p.project_name, m.name AS manager
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN projects p ON p.team_id = d.id
LEFT JOIN employees m ON e.manager_id = m.id;
|
| 42 | Найдите сотрудников с зарплатой выше средней по компании. | SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
|
| 43 | Найдите сотрудников, чья зарплата выше средней в их отделе. | SELECT e.name, e.salary, e.dept
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) FROM employees WHERE dept = e.dept
);
|
| 44 | Найдите отделы, где нет сотрудников младше 25 лет. | SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e
WHERE e.dept_id = d.id AND e.age < 25
);
|
| 45 | Найдите сотрудников, работающих в проектах с бюджетом более 100000. | SELECT name
FROM employees
WHERE id IN (
SELECT employee_id FROM project_assignments
WHERE project_id IN (SELECT id FROM projects WHERE budget > 100000)
);
|
| 46 | Найдите сотрудников, чья зарплата больше зарплаты любого (ANY) сотрудника из 'HR', и тех, кто получает больше всех (ALL) в 'Sales'. | SELECT name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept = 'HR')
AND salary > ALL (SELECT salary FROM employees WHERE dept = 'Sales');
|
| 47 | Найдите отделы с максимальной зарплатой выше 90000, используя производную таблицу. | SELECT dept, max_sal
FROM (
SELECT dept, MAX(salary) AS max_sal
FROM employees
GROUP BY dept
) AS dept_stats
WHERE max_sal > 90000;
|
| 48 | Выведите имя сотрудника и количество сотрудников в его отделе. | SELECT name,
(SELECT COUNT(*) FROM employees WHERE dept = e.dept) AS dept_count
FROM employees e;
|
| 49 | Найдите отделы со средней зарплатой выше общей средней, используя CTE. | WITH dept_avg AS (
SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
)
SELECT dept, avg_sal
FROM dept_avg
WHERE avg_sal > (SELECT AVG(salary) FROM employees);
|
| 50 | Сгенерируйте последовательность чисел от 1 до 10. | WITH RECURSIVE numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
|
| 51 | Сравните два подхода: найдите сотрудников, работающих в отделе 'IT'. | -- Через JOIN (обычно эффективнее)
SELECT e.name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.dept_name = 'IT';
-- Через подзапрос
SELECT name
FROM employees
WHERE dept_id = (SELECT id FROM departments WHERE dept_name = 'IT');
|
| 52 | Пронумеруйте сотрудников в каждом отделе по убыванию зарплаты. | SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees;
|
| 53 | Присвойте ранги сотрудникам по зарплате, показав разницу между RANK и DENSE_RANK. | SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
|
| 54 | Разделите сотрудников на 4 квартиля по зарплате. | SELECT name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
|
| 55 | Для каждого сотрудника выведите зарплату предыдущего и следующего по размеру зарплаты. | SELECT name, salary,
LAG(salary) OVER (ORDER BY salary) AS prev_salary,
LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employees;
|
| 56 | В каждом отделе покажите самого высоко- и низкооплачиваемого сотрудника. | SELECT name, dept, salary,
FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS highest_paid,
LAST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_paid
FROM employees;
|
| 57 | Выведите зарплату сотрудника вместе со средним значением по отделу и общей суммой. | SELECT name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
SUM(salary) OVER () AS total_salary
FROM employees;
|
| 58 | Для каждого отдела подсчитайте нарастающий итог зарплат (по возрастанию зарплаты). | SELECT name, dept, salary,
SUM(salary) OVER (PARTITION BY dept ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;
|
| 59 | Выведите сотрудников, отсортированных по дате найма, с порядковым номером внутри отдела. | SELECT name, dept, hire_date,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY hire_date) AS hire_seq
FROM employees;
|
| 60 | Вычислите скользящую среднюю зарплату за 3 соседних записи (текущая + предыдущая + следующая). | SELECT name, salary,
AVG(salary) OVER (ORDER BY salary
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM employees;
|
| 61 | Найдите двух самых высокооплачиваемых сотрудников в каждом отделе. | SELECT name, dept, salary
FROM (
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn <= 2;
|
| 62 | Создайте B-tree индекс на столбце email для ускорения поиска. | CREATE INDEX idx_employees_email ON employees(email);
|
| 63 | Создайте B-tree индекс для поддержки сортировки по дате найма. | -- B-tree используется по умолчанию, подходит для:
-- равенства (=), диапазонов (<, >, BETWEEN), сортировки (ORDER BY)
CREATE INDEX idx_emp_hire_date ON employees(hire_date);
-- Проверка плана выполнения:
EXPLAIN ANALYZE SELECT * FROM employees WHERE hire_date > '2023-01-01';
|
| 64 | Создайте уникальный индекс на email, чтобы гарантировать отсутствие дубликатов. | CREATE UNIQUE INDEX idx_employees_email_unique ON employees(email);
-- Попытка вставить дубликат вызовет ошибку:
-- INSERT INTO employees (email) VALUES ('ivan@example.com'), ('ivan@example.com');
|
| 65 | Создайте индекс для запросов, фильтрующих по отделу и зарплате. | CREATE INDEX idx_emp_dept_salary ON employees(dept, salary);
-- Этот индекс ускорит запрос:
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE dept = 'IT' AND salary > 70000;
|
| 66 | Создайте индекс только для активных сотрудников (status = 'active'). | CREATE INDEX idx_emp_active ON employees(hire_date)
WHERE status = 'active';
-- Ускоряет запросы, фильтрующие активных сотрудников:
SELECT * FROM employees
WHERE status = 'active' AND hire_date < '2022-01-01';
|
| 67 | Создайте индекс, покрывающий запрос поиска по email с возвратом имени. | -- Включаем дополнительный столбец в INCLUDE (только для PostgreSQL)
CREATE INDEX idx_emp_email_cover ON employees(email) INCLUDE (name);
-- Запрос будет выполнен только по индексу (Index Only Scan):
EXPLAIN ANALYZE
SELECT name FROM employees WHERE email = 'ivan@example.com';
|
| 68 | Проанализируйте план выполнения запроса с поиском по диапазону зарплат. | EXPLAIN ANALYZE
SELECT name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 80000
ORDER BY salary;
|
| 69 | Сравните планы выполнения для разных объёмов данных. | -- Seq Scan (полное сканирование) — когда выбрано много строк
EXPLAIN ANALYZE SELECT * FROM employees;
-- Index Scan — когда выбрано мало строк
EXPLAIN ANALYZE SELECT * FROM employees WHERE id = 42;
-- Bitmap Index Scan — средний случай
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 90000;
|
| 70 | Создайте индексы разных типов для соответствующих задач. | -- BRIN: для больших таблиц с естественно упорядоченными данными
CREATE INDEX idx_emp_hire_brin ON employees USING BRIN(hire_date);
-- GIN: для массивов и полнотекстового поиска
CREATE INDEX idx_emp_skills_gin ON employees USING GIN(skills);
-- GiST: для геоданных и полнотекстового поиска
CREATE INDEX idx_locations_gist ON locations USING GIST(coord);
|
| 71 | Выполните обслуживание индексов и сбор статистики. | -- Сбор статистики для оптимизатора
ANALYZE employees;
-- Перестроение индекса (Pg 12+)
REINDEX INDEX idx_employees_email;
-- Проверка размера индексов
SELECT relname, pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE relkind = 'i'
ORDER BY pg_relation_size(oid) DESC;
|
| 72 | Создайте таблицу с первичным ключом из одного столбца. | CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price NUMERIC(10, 2)
);
|
| 73 | Создайте таблицу orders с внешним ключом на customers. | CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
order_date DATE DEFAULT CURRENT_DATE,
amount NUMERIC(10, 2)
);
|
| 74 | Добавьте ограничение уникальности на email сотрудника. | ALTER TABLE employees
ADD CONSTRAINT uq_employees_email UNIQUE (email);
|
| 75 | Создайте таблицу, в которой некоторые поля обязательны для заполнения. | CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
body TEXT NOT NULL,
published_at TIMESTAMP
);
|
| 76 | Добавьте проверку, что зарплата положительная и возраст больше 18. | ALTER TABLE employees
ADD CONSTRAINT chk_positive_salary CHECK (salary > 0),
ADD CONSTRAINT chk_adult_age CHECK (age >= 18);
|
| 77 | Создайте таблицу со значениями по умолчанию для нескольких столбцов. | CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
status VARCHAR(20) DEFAULT 'new',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
priority INTEGER DEFAULT 5
);
|
| 78 | Запретите бронирование одного переговорного помещения на пересекающееся время. | CREATE TABLE bookings (
room_id INTEGER,
start_time TIMESTAMP,
end_time TIMESTAMP,
booked_by VARCHAR(100),
EXCLUDE USING gist (
room_id WITH =,
tstzrange(start_time, end_time) WITH &&
)
);
|
| 79 | Создайте внешний ключ, проверяемый только в конце транзакции. | CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE transactions (
id SERIAL PRIMARY KEY,
from_account INTEGER REFERENCES accounts(id) DEFERRABLE INITIALLY DEFERRED,
to_account INTEGER REFERENCES accounts(id) DEFERRABLE INITIALLY DEFERRED,
amount NUMERIC(10, 2)
);
-- Вставка без предварительного создания счетов возможна в транзакции:
BEGIN;
INSERT INTO transactions (from_account, to_account, amount) VALUES (1, 2, 100.00);
INSERT INTO accounts (id, name) VALUES (1, 'Alice'), (2, 'Bob');
COMMIT;
|
| 80 | Создайте внешние ключи с каскадным удалением и обнулением при удалении родителя. | CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
created_by INTEGER REFERENCES users(id) ON DELETE CASCADE
);
-- При удалении категории: category_id = NULL
-- При удалении пользователя: товары удалятся автоматически
|
| 81 | Временно отключите и снова включите проверку внешних ключей для таблицы. | -- Отключение (требует SUPERUSER)
ALTER TABLE orders DISABLE TRIGGER ALL;
-- Загрузка данных ...
INSERT INTO orders (customer_id) VALUES (999); -- нарушает FK
-- Включение и проверка
ALTER TABLE orders ENABLE TRIGGER ALL;
-- Поиск нарушений
SELECT * FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
|
| 82 | Выполните перевод средств между счетами в одной транзакции. | BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
-- При ошибке: ROLLBACK;
|
| 83 | Вставьте несколько записей с точкой сохранения для частичного отката. | BEGIN;
INSERT INTO logs (message) VALUES ('Шаг 1 выполнен');
SAVEPOINT sp1;
INSERT INTO logs (message) VALUES ('Шаг 2 выполнен');
-- Если шаг 2 ошибочный:
ROLLBACK TO SAVEPOINT sp1;
INSERT INTO logs (message) VALUES ('Шаг 2 (исправленный)');
COMMIT;
|
| 84 | Продемонстрируйте READ COMMITTED vs REPEATABLE READ. | -- Сессия 1 (READ COMMITTED — по умолчанию)
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1; -- видит 1000
-- (в сессии 2 обновляют balance = 900 и коммитят)
SELECT balance FROM accounts WHERE id = 1; -- видит 900 (неповторяющееся чтение)
COMMIT;
-- Сессия 1 (REPEATABLE READ)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- видит 1000
-- (в сессии 2 обновляют balance = 900 и коммитят)
SELECT balance FROM accounts WHERE id = 1; -- всё ещё 1000 (снапшот)
COMMIT;
|
| 85 | Предотвратите фантомное чтение и аномалии сериализации. | BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Если две сессии одновременно выполняют этот код,
-- одна из них получит ошибку сериализации:
UPDATE products SET stock = stock - 1 WHERE id = 10;
INSERT INTO order_items (order_id, product_id) VALUES (1, 10);
COMMIT;
-- При ошибке: повторить транзакцию
|
| 86 | Создайте ситуацию взаимоблокировки и обработайте её. | -- Сессия 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Сессия 2 делает:
-- UPDATE accounts SET balance = balance - 200 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Если сессии обновляют ресурсы в разном порядке, возникает deadlock,
-- PostgreSQL автоматически завершит одну из транзакций с ошибкой
|
| 87 | Заблокируйте запись при расчёте зарплаты, чтобы избежать гонки. | BEGIN;
-- FOR UPDATE — эксклюзивная блокировка (другие не могут читать/писать)
SELECT * FROM employees WHERE id = 10 FOR UPDATE;
UPDATE employees SET salary = salary * 1.1 WHERE id = 10;
COMMIT;
-- FOR SHARE — разделяемая блокировка (другие могут читать, но не изменять)
SELECT * FROM employees WHERE dept = 'IT' FOR SHARE;
|
| 88 | Пропустите заблокированные строки или завершите запрос с ошибкой при блокировке. | -- SKIP LOCKED: взять только свободные строки (очередь задач)
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY priority DESC
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- NOWAIT: завершиться с ошибкой, если строка заблокирована
SELECT * FROM employees WHERE id = 5 FOR UPDATE NOWAIT;
|
| 89 | Используйте консультативную блокировку для координации между сессиями. | -- Сессия 1
SELECT pg_advisory_lock(12345);
-- Выполнение критической секции
UPDATE employees SET salary = salary * 1.1;
-- Сессия 2 (ждёт)
SELECT pg_advisory_lock(12345); -- блокируется, пока сессия 1 не отпустит
-- Сессия 1
SELECT pg_advisory_unlock(12345);
-- Альтернатива: pg_try_advisory_lock (неблокирующая)
|
| 90 | Проверьте видимость строк через системные столбцы. | -- PostgreSQL не перезаписывает строки, а создаёт новые версии.
-- Проверка mvp (минимальный xmin):
SELECT xmin, xmax, ctid, name, salary
FROM employees
ORDER BY ctid;
-- Количество "мёртвых" кортежей (нуждаются в VACUUM):
SELECT n_dead_tup, n_live_tup, relname
FROM pg_stat_user_tables
WHERE relname = 'employees';
|
| 91 | Вставьте сотрудника или обновите его зарплату, если он уже существует. | INSERT INTO employees (email, name, salary)
VALUES ('ivan@example.com', 'Иван Петров', 80000.00)
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name,
salary = EXCLUDED.salary;
|
| 92 | Синхронизируйте таблицу staging с основной таблицей. | MERGE INTO employees AS target
USING employees_staging AS source
ON target.email = source.email
WHEN MATCHED THEN
UPDATE SET salary = source.salary, name = source.name
WHEN NOT MATCHED THEN
INSERT (email, name, salary)
VALUES (source.email, source.name, source.salary);
|
| 93 | Вставьте запись и сразу получите её id и другие поля. | INSERT INTO employees (name, salary, dept)
VALUES ('Ольга Новикова', 72000.00, 'IT')
RETURNING id, name, salary, created_at;
-- Также работает с UPDATE и DELETE:
UPDATE employees SET salary = salary * 1.1 WHERE dept = 'IT'
RETURNING id, name, salary AS new_salary;
|
| 94 | Создайте таблицу с вычисляемыми столбцами (stored и virtual). | CREATE TABLE invoices (
id SERIAL PRIMARY KEY,
quantity INTEGER NOT NULL,
unit_price NUMERIC(10, 2) NOT NULL,
total NUMERIC(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);
INSERT INTO invoices (quantity, unit_price) VALUES (3, 150.00);
SELECT * FROM invoices; -- total = 450.00
|
| 95 | Создайте таблицы с массивом навыков и JSONB-полем, выполните поиск по ним. | -- Массивы
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
skills TEXT[]
);
INSERT INTO employees (name, skills) VALUES
('Иван', ARRAY['Python', 'SQL', 'Docker']),
('Мария', ARRAY['Java', 'SQL', 'Kubernetes']);
SELECT name FROM employees WHERE 'SQL' = ANY(skills);
SELECT name FROM employees WHERE skills @> ARRAY['Python', 'Docker'];
-- JSONB
CREATE TABLE events (
id SERIAL PRIMARY KEY,
payload JSONB
);
INSERT INTO events (payload) VALUES
('{"user_id": 42, "tags": ["premium", "vip"]}');
SELECT payload->>'user_id' AS uid FROM events;
SELECT * FROM events WHERE payload @> '{"tags": ["vip"]}';
CREATE INDEX idx_events_payload ON events USING GIN(payload);
|
| 96 | Выведите иерархию категорий (родитель-потомок) древовидной структуры. | CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
parent_id INTEGER REFERENCES categories(id)
);
INSERT INTO categories VALUES
(1, 'Электроника', NULL),
(2, 'Компьютеры', 1),
(3, 'Ноутбуки', 2),
(4, 'Планшеты', 2),
(5, 'Телефоны', 1);
WITH RECURSIVE cat_tree AS (
SELECT id, name, parent_id, 1 AS level, name::TEXT AS path
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.level + 1,
ct.path || ' -> ' || c.name
FROM categories c
JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT * FROM cat_tree ORDER BY path;
|
| 97 | Создайте индекс для полнотекстового поиска по статьям. | CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
body TEXT,
search_vector TSVECTOR
);
-- Заполнение tsvector
UPDATE articles SET search_vector =
to_tsvector('russian', title || ' ' || body);
-- Индекс GIN для ускорения
CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);
-- Поиск
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('russian', 'база & данных');
-- Триггер для автоматического обновления
CREATE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector := to_tsvector('russian', NEW.title || ' ' || NEW.body);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_articles_fts
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();
|
| 98 | Создайте партиционированную таблицу для логов по месяцам. | CREATE TABLE logs (
id SERIAL,
level TEXT,
message TEXT,
created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE logs_2024_q1 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE logs_2024_q2 PARTITION OF logs
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE logs_2024_q3 PARTITION OF logs
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE logs_2024_q4 PARTITION OF logs
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
-- Запрос автоматически использует только нужные партиции
SELECT * FROM logs WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
|
| 99 | Создайте базовую таблицу vehicles и наследников cars/motorcycles. | CREATE TABLE vehicles (
id SERIAL PRIMARY KEY,
brand VARCHAR(100),
model VARCHAR(100),
year INTEGER
);
CREATE TABLE cars (
doors INTEGER,
fuel_type VARCHAR(20)
) INHERITS (vehicles);
CREATE TABLE motorcycles (
has_sidecar BOOLEAN DEFAULT FALSE
) INHERITS (vehicles);
INSERT INTO cars (brand, model, year, doors, fuel_type) VALUES
('Toyota', 'Camry', 2023, 4, 'petrol');
INSERT INTO motorcycles (brand, model, year, has_sidecar) VALUES
('Harley', 'Sportster', 2022, FALSE);
-- Запрос к родительской таблице включает и потомков
SELECT * FROM vehicles; -- покажет и cars, и motorcycles
|
| 100 | Настройте уведомление при добавлении нового сотрудника. | -- Создание функции-триггера для уведомлений
CREATE FUNCTION notify_new_employee() RETURNS TRIGGER AS $$
BEGIN
PERFORM pg_notify(
'new_employee',
json_build_object('id', NEW.id, 'name', NEW.name)::TEXT
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_new_employee
AFTER INSERT ON employees
FOR EACH ROW
EXECUTE FUNCTION notify_new_employee();
-- В приложении (или psql):
LISTEN new_employee;
-- При вставке:
INSERT INTO employees (name, salary) VALUES ('Новый Сотрудник', 60000);
-- В консоли появится: Asynchronous notification "new_employee" ...
|
SQL — Язык структурированных запросов: вопросы, практические задания и ответы с кодом
Темы
Вопросы и ответы
Практические задания
Лицензия
MIT