---

Билет №25

CREATE TABLE suppliers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE cars (id INT PRIMARY KEY, model VARCHAR(100), supplier_id INT REFERENCES suppliers(id));
CREATE TABLE clients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE sales_contracts (id INT PRIMARY KEY, car_id INT REFERENCES cars(id), client_id INT REFERENCES clients(id), price DECIMAL);
CREATE TABLE extra_equipment (id INT PRIMARY KEY, car_id INT REFERENCES cars(id), name VARCHAR(100));

INSERT INTO suppliers (id, name) VALUES (1, 'ООО АвтоТорг'), (2, 'ЗАО Моторс'), (3, 'ИмпортАвто'), (4, 'Дилер Плюс'), (5, 'Глобал Авто');
INSERT INTO cars (id, model, supplier_id) VALUES (1, 'Toyota RAV4', 1), (2, 'KIA Sportage', 2), (3, 'Hyundai Tucson', 3), (4, 'BMW X3', 4), (5, 'Lada Vesta', 5);
INSERT INTO clients (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO sales_contracts (id, car_id, client_id, price) VALUES (1, 1, 1, 3500000), (2, 2, 2, 3000000), (3, 3, 3, 3200000), (4, 4, 4, 6000000), (5, 5, 5, 1500000);
INSERT INTO extra_equipment (id, car_id, name) VALUES (1, 1, 'Зимняя резина'), (2, 2, 'Сигнализация'), (3, 3, 'Коврики'), (4, 4, 'Тонировка'), (5, 5, 'Защита картера');

SELECT model FROM cars WHERE supplier_id = 1;
SELECT c.model, s.name FROM cars c JOIN suppliers s ON c.supplier_id = s.id;
SELECT car_id, COUNT(id) FROM extra_equipment GROUP BY car_id;
SELECT full_name FROM clients WHERE id IN (SELECT client_id FROM sales_contracts WHERE price > 4000000);
UPDATE sales_contracts SET price = 3600000 WHERE id = 1;

Билет №26

CREATE TABLE directors (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE movies (id INT PRIMARY KEY, title VARCHAR(100), dir_id INT REFERENCES directors(id));
CREATE TABLE actors (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE crews (id INT PRIMARY KEY, name VARCHAR(100), movie_id INT REFERENCES movies(id));
CREATE TABLE roles (id INT PRIMARY KEY, movie_id INT REFERENCES movies(id), actor_id INT REFERENCES actors(id), role_name VARCHAR(100));

INSERT INTO directors (id, full_name) VALUES (1, 'Михалков Н.С.'), (2, 'Бондарчук Ф.С.'), (3, 'Звягинцев А.П.'), (4, 'Быков Ю.А.'), (5, 'Балагов К.А.');
INSERT INTO movies (id, title, dir_id) VALUES (1, 'Утомленные солнцем', 1), (2, 'Сталинград', 2), (3, 'Левиафан', 3), (4, 'Дурак', 4), (5, 'Дылда', 5);
INSERT INTO actors (id, full_name) VALUES (1, 'Меньшиков О.Е.'), (2, 'Машков В.Л.'), (3, 'Серебряков А.В.'), (4, 'Хабенский К.Ю.'), (5, 'Безруков С.В.');
INSERT INTO crews (id, name, movie_id) VALUES (1, 'Съемочная группа 1', 1), (2, 'Съемочная группа 2', 2), (3, 'Съемочная группа 3', 3), (4, 'Съемочная группа 4', 4), (5, 'Съемочная группа 5', 5);
INSERT INTO roles (id, movie_id, actor_id, role_name) VALUES (1, 1, 1, 'Митя'), (2, 2, 2, 'Капитан'), (3, 3, 3, 'Николай'), (4, 4, 4, 'Следователь'), (5, 5, 5, 'Врач');

SELECT title FROM movies WHERE dir_id = 1;
SELECT m.title, d.full_name FROM movies m JOIN directors d ON m.dir_id = d.id;
SELECT movie_id, COUNT(id) FROM roles GROUP BY movie_id;
SELECT full_name FROM actors WHERE id IN (SELECT actor_id FROM roles WHERE role_name = 'Николай');
UPDATE movies SET title = 'Утомленные солнцем 2' WHERE id = 1;

Билет №27

CREATE TABLE branches (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE clients (id INT PRIMARY KEY, full_name VARCHAR(100), branch_id INT REFERENCES branches(id));
CREATE TABLE accounts (id INT PRIMARY KEY, client_id INT REFERENCES clients(id), balance DECIMAL);
CREATE TABLE cards (id INT PRIMARY KEY, acc_id INT REFERENCES accounts(id), card_num VARCHAR(20));
CREATE TABLE transactions (id INT PRIMARY KEY, acc_id INT REFERENCES accounts(id), amount DECIMAL);

INSERT INTO branches (id, name) VALUES (1, 'Сбер Отделение 1'), (2, 'ВТБ Отделение 2'), (3, 'Альфа Отделение 3'), (4, 'Тинькофф Отделение 4'), (5, 'Газпром Отделение 5');
INSERT INTO clients (id, full_name, branch_id) VALUES (1, 'Иванов И.И.', 1), (2, 'Петров П.П.', 2), (3, 'Сидоров С.С.', 3), (4, 'Смирнов А.А.', 4), (5, 'Кузнецова Е.В.', 5);
INSERT INTO accounts (id, client_id, balance) VALUES (1, 1, 150000), (2, 2, 5000), (3, 3, 300000), (4, 4, 20000), (5, 5, 0);
INSERT INTO cards (id, acc_id, card_num) VALUES (1, 1, '2202111122223333'), (2, 2, '4276111122223333'), (3, 3, '5469111122223333'), (4, 4, '4276444455556666'), (5, 5, '2202777788889999');
INSERT INTO transactions (id, acc_id, amount) VALUES (1, 1, -5000), (2, 2, 1000), (3, 3, -15000), (4, 4, 20000), (5, 5, 0);

SELECT balance FROM accounts WHERE balance > 100000;
SELECT c.full_name, b.name FROM clients c JOIN branches b ON c.branch_id = b.id;
SELECT acc_id, SUM(amount) FROM transactions GROUP BY acc_id;
SELECT full_name FROM clients WHERE id IN (SELECT client_id FROM accounts WHERE balance > 200000);
UPDATE accounts SET balance = 145000 WHERE id = 1;

Билет №28

CREATE TABLE categories (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE equipment (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE users (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE engineers (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE tickets (id INT PRIMARY KEY, user_id INT REFERENCES users(id), eng_id INT REFERENCES engineers(id), cat_id INT REFERENCES categories(id), eq_id INT REFERENCES equipment(id), status VARCHAR(50));

INSERT INTO categories (id, name) VALUES (1, 'Проблема с ПО'), (2, 'Проблема с железом'), (3, 'Доступ к сети'), (4, 'Замена картриджа'), (5, 'Установка ОС');
INSERT INTO equipment (id, name) VALUES (1, 'Ноутбук Lenovo'), (2, 'Монитор Dell'), (3, 'Принтер HP'), (4, 'Роутер Cisco'), (5, 'IP Телефон');
INSERT INTO users (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO engineers (id, full_name) VALUES (1, 'Инженер Алексей'), (2, 'Инженер Борис'), (3, 'Инженер Виктор'), (4, 'Инженер Григорий'), (5, 'Инженер Дмитрий');
INSERT INTO tickets (id, user_id, eng_id, cat_id, eq_id, status) VALUES (1, 1, 1, 1, 1, 'Открыта'), (2, 2, 2, 2, 2, 'В работе'), (3, 3, 3, 3, 3, 'Закрыта'), (4, 4, 4, 4, 4, 'Открыта'), (5, 5, 5, 5, 5, 'Отклонена');

SELECT * FROM tickets WHERE status = 'Открыта';
SELECT t.status, u.full_name FROM tickets t JOIN users u ON t.user_id = u.id;
SELECT eng_id, COUNT(id) FROM tickets GROUP BY eng_id;
SELECT full_name FROM users WHERE id IN (SELECT user_id FROM tickets WHERE status = 'В работе');
UPDATE tickets SET status = 'Закрыта' WHERE id = 1;

Билет №29

CREATE TABLE fields (id INT PRIMARY KEY, area DECIMAL);
CREATE TABLE crops (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE employees (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE machinery (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE harvests (id INT PRIMARY KEY, field_id INT REFERENCES fields(id), crop_id INT REFERENCES crops(id), emp_id INT REFERENCES employees(id), machine_id INT REFERENCES machinery(id), weight DECIMAL);

INSERT INTO fields (id, area) VALUES (1, 100.5), (2, 200.0), (3, 150.2), (4, 300.0), (5, 50.5);
INSERT INTO crops (id, name) VALUES (1, 'Пшеница'), (2, 'Рожь'), (3, 'Овес'), (4, 'Кукуруза'), (5, 'Подсолнечник');
INSERT INTO employees (id, full_name) VALUES (1, 'Тракторист Иванов'), (2, 'Комбайнер Петров'), (3, 'Агроном Сидоров'), (4, 'Водитель Смирнов'), (5, 'Механик Кузнецов');
INSERT INTO machinery (id, name) VALUES (1, 'Трактор МТЗ-82'), (2, 'Комбайн ДОН-1500'), (3, 'Сеялка СЗ-3.6'), (4, 'КАМАЗ'), (5, 'Плуг ПЛН');
INSERT INTO harvests (id, field_id, crop_id, emp_id, machine_id, weight) VALUES (1, 1, 1, 1, 1, 5000), (2, 2, 2, 2, 2, 10000), (3, 3, 3, 3, 3, 7000), (4, 4, 4, 4, 4, 15000), (5, 5, 5, 5, 5, 2000);

SELECT weight FROM harvests WHERE weight > 8000;
SELECT h.weight, c.name FROM harvests h JOIN crops c ON h.crop_id = c.id;
SELECT crop_id, SUM(weight) FROM harvests GROUP BY crop_id;
SELECT full_name FROM employees WHERE id IN (SELECT emp_id FROM harvests WHERE weight > 10000);
UPDATE fields SET area = 110.5 WHERE id = 1;

Билет №30

CREATE TABLE candidates (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE employers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE vacancies (id INT PRIMARY KEY, emp_id INT REFERENCES employers(id), title VARCHAR(100), salary DECIMAL);
CREATE TABLE resumes (id INT PRIMARY KEY, cand_id INT REFERENCES candidates(id), title VARCHAR(100));
CREATE TABLE applications (id INT PRIMARY KEY, vac_id INT REFERENCES vacancies(id), res_id INT REFERENCES resumes(id), status VARCHAR(50));

INSERT INTO candidates (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO employers (id, name) VALUES (1, 'Яндекс'), (2, 'СберТех'), (3, 'ВК'), (4, 'Тинькофф Центр Разработки'), (5, 'Озон');
INSERT INTO vacancies (id, emp_id, title, salary) VALUES (1, 1, 'Java Developer', 250000), (2, 2, 'Системный аналитик', 200000), (3, 3, 'QA Engineer', 150000), (4, 4, 'Frontend Developer', 220000), (5, 5, 'DevOps', 300000);
INSERT INTO resumes (id, cand_id, title) VALUES (1, 1, 'Резюме Java'), (2, 2, 'Резюме Аналитика'), (3, 3, 'Резюме Тестировщика'), (4, 4, 'Резюме React'), (5, 5, 'Резюме Linux');
INSERT INTO applications (id, vac_id, res_id, status) VALUES (1, 1, 1, 'Приглашение'), (2, 2, 2, 'Отказ'), (3, 3, 3, 'На рассмотрении'), (4, 4, 4, 'Оффер'), (5, 5, 5, 'Приглашение');

SELECT title, salary FROM vacancies WHERE salary > 200000;
SELECT v.title, e.name FROM vacancies v JOIN employers e ON v.emp_id = e.id;
SELECT vac_id, COUNT(id) FROM applications GROUP BY vac_id;
SELECT full_name FROM candidates WHERE id IN (SELECT cand_id FROM resumes WHERE id IN (SELECT res_id FROM applications WHERE status = 'Оффер'));
UPDATE vacancies SET salary = 260000 WHERE id = 1;