---

DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS shippings;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;



CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    budget NUMERIC(12, 2) DEFAULT 0.00,
    location TEXT
);

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)
);

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)
);

INSERT INTO departments(id, name, budget, location) VALUES
  (0, 'Backend', 12212, 'ru'),
  (1, 'DataSience', 342, 'en'),
  (2, 'Frontend', 3232, 'fr');
  

INSERT INTO employees (id, first_name, last_name, email, hire_date, salary, department_id, is_remote, tech_stack) VALUES
(1, 'Иван', 'Иванов', 'ivan.ivanov@company.com', '2025-01-15', 150000.00, 0, 0, '["Python", "Django", "PostgreSQL"]'),
(2, 'Анна', 'Петрова', 'anna.petrova@company.com', '2025-03-20', 180000.00, 0, 1, '["Go", "Docker", "gRPC"]'),
(3, 'Сергей', 'Сидоров', 'sergey.sidorov@company.com', '2024-11-01', 200000.00, 1, 0, '["Python", "PyTorch", "Pandas"]'),
(4, 'Елена', 'Смирнова', 'elena.smirnova@company.com', '2025-05-10', 160000.00, 1, 1, '["SQL", "Tableau", "R"]'),
(5, 'Алексей', 'Кузнецов', 'alex.kuznetsov@company.com', '2026-02-01', 140000.00, 2, 0, '["JavaScript", "React", "Redux"]'),
(6, 'Ольга', 'Попова', 'olga.popova@company.com', '2026-06-15', 135000.00, 2, 1, '["TypeScript", "Vue.js", "Tailwind"]');

INSERT INTO tasks (id, title, employee_id, status, priority, deadline) VALUES
(101, 'Настроить CI/CD пайплайн', 2, 'done', 4, '2026-04-01'),        -- Выполнена
(102, 'Разработать API для авторизации', 1, 'in_progress', 5, '2026-11-15'), -- В процессе, дедлайн в будущем
(103, 'Исправить баг с сессиями', 1, 'new', 3, '2026-03-10'),        -- Новая, ДЕДЛАЙН ПРОСРОЧЕН
(104, 'Обучить baseline модель рекомендаций', 3, 'done', 4, '2026-02-28'), -- Выполнена
(105, 'Оптимизировать SQL-запрос отчетов', 4, 'in_progress', 3, '2026-05-20'), -- В процессе, ДЕДЛАЙН ПРОСРОЧЕН
(106, 'Подготовить датасет для валидации', 3, 'new', 2, '2026-12-01'),   -- Новая, дедлайн в будущем
(107, 'Сверстать главную страницу', 5, 'done', 4, '2026-08-01'),      -- Выполнена
(108, 'Интегрировать стейт-менеджер', 6, 'in_progress', 3, '2026-11-30'), -- В процессе, дедлайн в будущем
(109, 'Провести рефакторинг компонентов', 5, 'new', 2, '2026-06-01'),  -- Новая, ДЕДЛАЙН ПРОСРОЧЕН
(110, 'Написать Unit-тесты для фронтенда', 6, 'new', 1, '2026-12-25');  -- Новая, дедлайн в будущем


UPDATE employees
SET salary = salary *1.15
WHERE department_id = (
 SELECT id from Departments where name = 'DataSience'
);


UPDATE Tasks
SET status = 'in_progress'
where deadline < '2026-10-27' AND status != 'done';

DELETE FROM tasks 
WHERE status = 'new'
and priority = 1;

SELECT e.first_name, d.name, t.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  ;

SELECT d.name, COUNT(e.id), AVG(e.salary), MAX(e.salary)
from Departments d LEFT JOIN Employees e on d.id = e.department_id
GROUP BY e.id, d.name;

SELECT d.name, COUNT(e.id), AVG(e.salary), MAX(e.salary)
from Departments d LEFT JOIN Employees e on d.id = e.department_id
GROUP BY e.id, d.name
HAVING AVG(e.salary) > 100000 AND COUNT(e.id) >1;


https://www.programiz.com/sql/online-compiler