---

Ниже представлено полное решение практического задания «Проектирование и анализ БД IT-компании».
Скрипт написан в соответствии со стандартами SQLite / PostgreSQL (судя по типам данных INTEGER PRIMARY KEY, TEXT, NUMERIC и флагу is_remote типа INTEGER).
Этап 1: DDL – Проектирование схемы базы данных
Создание таблиц departments, employees и tasks со всеми ограничениями (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, DEFAULT):
-- Включение поддержки внешних ключей (актуально для SQLite)
PRAGMA foreign_keys = ON;

-- 1. Таблица departments (Отделы)
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    budget NUMERIC(12, 2) DEFAULT 0.00,
    location TEXT
);

-- 2. Таблица employees (Сотрудники)
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary NUMERIC(10, 2) CHECK (salary > 0),
    department_id INTEGER,
    is_remote INTEGER DEFAULT 0 CHECK (is_remote IN (0, 1)),
    tech_stack TEXT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 3. Таблица tasks (Задачи)
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    employee_id INTEGER,
    status TEXT CHECK (status IN ('new', 'in_progress', 'done')),
    priority INTEGER CHECK (priority >= 1 AND priority <= 5),
    deadline DATE,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

Этап 2: DML – Наполнение и модификация данных
1. Множественная вставка (INSERT)
Наполнение таблиц тестовыми данными (с валидным JSON в tech_stack, а также прошедшими и будущими датами в deadline):
-- Вставка 3 отделов
INSERT INTO departments (id, name, budget, location) VALUES
(1, 'Backend', 5000000.00, 'Москва'),
(2, 'Data Science', 7500000.00, 'Новосибирск'),
(3, 'Frontend', 4000000.00, 'Санкт-Петербург');

-- Вставка 6 сотрудников (с JSON в поле tech_stack)
INSERT INTO employees (id, first_name, last_name, email, hire_date, salary, department_id, is_remote, tech_stack) VALUES
(1, 'Иван', 'Иванов', 'ivanov@itcompany.ru', '2024-02-15', 180000.00, 1, 0, '{"Python": "senior", "Docker": "middle", "SQL": "senior"}'),
(2, 'Анна', 'Смирнова', 'smirnova@itcompany.ru', '2024-05-10', 220000.00, 2, 1, '{"Python": "expert", "ML": "senior", "PyTorch": "senior"}'),
(3, 'Петр', 'Кузнецов', 'kuznetsov@itcompany.ru', '2025-01-20', 140000.00, 2, 1, '{"Python": "middle", "SQL": "middle", "Pandas": "senior"}'),
(4, 'Мария', 'Попова', 'popova@itcompany.ru', '2023-11-01', 160000.00, 3, 0, '{"JavaScript": "senior", "React": "senior", "TypeScript": "middle"}'),
(5, 'Алексей', 'Соколов', 'sokolov@itcompany.ru', '2025-03-12', 195000.00, 1, 1, '{"Go": "senior", "Python": "senior", "Kubernetes": "middle"}'),
(6, 'Елена', 'Новикова', 'novikova@itcompany.ru', '2025-08-01', 120000.00, 3, 0, '{"JavaScript": "middle", "Vue": "middle", "CSS": "senior"}');

-- Вставка 9 задач (часть задач с дедлайном в прошлом, у сотрудника №6 задач нет для проверки LEFT JOIN)
INSERT INTO tasks (id, title, employee_id, status, priority, deadline) VALUES
(1, 'Разработка REST API авторизации', 1, 'in_progress', 5, '2026-11-15'),
(2, 'Оптимизация SQL-запросов отчетов', 1, 'new', 3, '2026-01-10'),        -- просрочена
(3, 'Обучение модели рекомендаций', 2, 'in_progress', 5, '2026-12-01'),
(4, 'Очистка датасета логов', 2, 'done', 2, '2026-02-01'),                 -- в прошлом, но уже done
(5, 'Построение дашборда метрик', 3, 'new', 4, '2026-03-01'),              -- просрочена
(6, 'Настройка CI/CD пайплайна', 5, 'new', 1, '2026-11-20'),               -- статус 'new', приоритет 1 (под удаление)
(7, 'Верстка главной страницы', 4, 'done', 3, '2026-02-15'),
(8, 'Миграция на микросервисы', 5, 'in_progress', 5, '2026-11-30'),
(9, 'Обновление документации API', 3, 'new', 1, '2026-12-10');             -- статус 'new', приоритет 1 (под удаление)

2. Обновление данных (UPDATE)
Повышение зарплаты на 15% отделу 'Data Science' и перевод просроченных незавершенных задач в 'in_progress':
-- Повышение зарплаты на 15% сотрудникам отдела 'Data Science' через подзапрос
UPDATE employees
SET salary = salary * 1.15
WHERE department_id = (
    SELECT id FROM departments WHERE name = 'Data Science'
);

-- Изменение статуса просроченных задач на 'in_progress', если они еще не 'done'
UPDATE tasks
SET status = 'in_progress'
WHERE deadline < CURRENT_DATE 
  AND status != 'done';

3. Удаление данных (DELETE)
Удаление задач со статусом 'new' и приоритетом 1:
DELETE FROM tasks
WHERE status = 'new' 
  AND priority = 1;

Этап 3: DQL – Аналитика и сложные выборки
1. Детальный список (LEFT JOIN)
Вывод сотрудников, их отделов и задач (включая сотрудников без задач) с сортировкой по имени:
SELECT 
    e.first_name,
    e.last_name,
    d.name AS department_name,
    t.title AS task_title,
    t.deadline
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN tasks t ON e.id = t.employee_id
ORDER BY e.first_name ASC;

2. Агрегация по отделам (GROUP BY)
Количество сотрудников, средняя и максимальная зарплата по каждому отделу:
SELECT 
    d.name AS department_name,
    COUNT(e.id) AS employee_count,
    ROUND(AVG(e.salary), 2) AS avg_salary,
    MAX(e.salary) AS max_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name;

3. Фильтрация групп (HAVING)
Отделы со средней зарплатой выше 100 000 и числом сотрудников больше 1:
SELECT 
    d.name AS department_name,
    COUNT(e.id) AS employee_count,
    ROUND(AVG(e.salary), 2) AS avg_salary,
    MAX(e.salary) AS max_salary
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name
HAVING AVG(e.salary) > 100000 
   AND COUNT(e.id) > 1;

4. Анализ задач
Количество задач в разрезе статусов и приоритетов по убыванию количества:
SELECT 
    status,
    priority,
    COUNT(id) AS task_count
FROM tasks
GROUP BY status, priority
ORDER BY task_count DESC;

5. Работа со специальными типами (JSON в tech_stack)
Поиск сотрудников, владеющих Python на уровне 'senior' или 'expert':
-- Вариант 1: Для SQLite (функция json_extract)
SELECT first_name, last_name
FROM employees
WHERE json_extract(tech_stack, '$.Python') IN ('senior', 'expert');

-- Вариант 2: Для PostgreSQL (приведение к jsonb и оператор ->>)
-- SELECT first_name, last_name
-- FROM employees
-- WHERE (tech_stack::jsonb ->> 'Python') IN ('senior', 'expert');

6. Лимит и смещение (LIMIT и OFFSET)
Топ-3 самых высокооплачиваемых сотрудников со 2-го по 4-е место (пропуск первого):
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 1;

Этап 4: DDL – Эволюция схемы и теоретический блиц
Задание А: Модификация структуры (ALTER)
Добавление столбца phone, переименование is_remote в work_from_home и проверка:
-- 1. Добавление нового столбца phone типа TEXT
ALTER TABLE employees ADD COLUMN phone TEXT;

-- 2. Переименование столбца is_remote в work_from_home
ALTER TABLE employees RENAME COLUMN is_remote TO work_from_home;

-- 3. Проверка изменений
SELECT * FROM employees LIMIT 1;

Задание Б: Теоретический блиц
Ответы оформлены в виде SQL-комментариев --, как требуется в задании:
-- Вопрос 1: В чем ключевая разница между DELETE FROM table и TRUNCATE TABLE table 
-- с точки зрения скорости и сброса автоинкремента?
--
-- Ответ:
-- 1. Скорость: DELETE — это DML-операция, которая удаляет строки по одной, 
--    записывает каждую удаленную строку в лог транзакций и вызывает триггеры ON DELETE, 
--    поэтому работает медленно на больших таблицах. TRUNCATE — это DDL-операция, 
--    которая быстро освобождает страницы данных целиком (без построчного сканирования), 
--    поэтому выполняется практически мгновенно.
-- 2. Автоинкремент: DELETE сохраняет текущее значение счетчика автоинкремента 
--    (новые записи продолжат нумерацию со старого значения). TRUNCATE (в большинстве СУБД 
--    по умолчанию или с опцией RESTART IDENTITY) сбрасывает счетчик автоинкремента к начальному значению (обычно 1).


-- Вопрос 2: Почему в запросе нельзя использовать псевдоним столбца, 
-- заданный в SELECT, внутри условия WHERE?
--
-- Ответ:
-- Из-за логического порядка выполнения (Order of Execution) инструкций в SQL-запросе. 
-- СУБД выполняет блок WHERE задолго до блока SELECT:
-- 1. FROM / JOIN (сборка источников данных)
-- 2. WHERE (первичная фильтрация строк)
-- 3. GROUP BY (группировка)
-- 4. HAVING (фильтрация групп)
-- 5. SELECT (вычисление выражений и назначение псевдонимов AS)
-- 6. ORDER BY (сортировка — здесь псевдонимы уже доступны)
-- 7. LIMIT / OFFSET
-- На этапе выполнения WHERE псевдонимы из SELECT еще просто не существуют.