---

Билет №13

CREATE TABLE movies (id INT PRIMARY KEY, title VARCHAR(100));
CREATE TABLE halls (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE shows (id INT PRIMARY KEY, movie_id INT REFERENCES movies(id), hall_id INT REFERENCES halls(id), show_time TIMESTAMP);
CREATE TABLE clients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE tickets (id INT PRIMARY KEY, show_id INT REFERENCES shows(id), client_id INT REFERENCES clients(id), price DECIMAL);

INSERT INTO movies (id, title) VALUES (1, 'Аватар 2'), (2, 'Дюна 2'), (3, 'Чебурашка'), (4, 'Мастер и Маргарита'), (5, 'Холоп 2');
INSERT INTO halls (id, name) VALUES (1, 'Зал 1 IMAX'), (2, 'Зал 2 3D'), (3, 'Зал 3 VIP'), (4, 'Зал 4 Обычный'), (5, 'Зал 5 Обычный');
INSERT INTO shows (id, movie_id, hall_id, show_time) VALUES (1, 1, 1, '2023-10-25 18:00:00'), (2, 2, 2, '2023-10-25 19:00:00'), (3, 3, 3, '2023-10-25 12:00:00'), (4, 4, 4, '2023-10-25 20:00:00'), (5, 5, 5, '2023-10-25 21:00:00');
INSERT INTO clients (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO tickets (id, show_id, client_id, price) VALUES (1, 1, 1, 800), (2, 2, 2, 600), (3, 3, 3, 1500), (4, 4, 4, 400), (5, 5, 5, 400);

SELECT title FROM movies WHERE id = 1;
SELECT s.show_time, m.title FROM shows s JOIN movies m ON s.movie_id = m.id;
SELECT show_id, COUNT(id) FROM tickets GROUP BY show_id;
SELECT full_name FROM clients WHERE id IN (SELECT client_id FROM tickets WHERE price > 1000);
UPDATE tickets SET price = 850 WHERE id = 1;

Билет №14

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE employees (id INT PRIMARY KEY, full_name VARCHAR(100), dep_id INT REFERENCES departments(id));
CREATE TABLE projects (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE roles (id INT PRIMARY KEY, emp_id INT REFERENCES employees(id), proj_id INT REFERENCES projects(id), role_name VARCHAR(100));
CREATE TABLE skills (id INT PRIMARY KEY, emp_id INT REFERENCES employees(id), skill_name VARCHAR(100));

INSERT INTO departments (id, name) VALUES (1, 'Отдел разработки'), (2, 'Отдел тестирования'), (3, 'Отдел аналитики'), (4, 'Дизайн'), (5, 'DevOps');
INSERT INTO employees (id, full_name, dep_id) VALUES (1, 'Иванов И.И.', 1), (2, 'Петров П.П.', 2), (3, 'Сидоров С.С.', 3), (4, 'Смирнова А.А.', 4), (5, 'Кузнецов Е.В.', 5);
INSERT INTO projects (id, name) VALUES (1, 'Мобильное приложение'), (2, 'Веб-портал'), (3, 'CRM система'), (4, 'API шлюз'), (5, 'Миграция БД');
INSERT INTO roles (id, emp_id, proj_id, role_name) VALUES (1, 1, 1, 'Senior Developer'), (2, 2, 2, 'QA Engineer'), (3, 3, 3, 'Business Analyst'), (4, 4, 4, 'UI/UX Designer'), (5, 5, 5, 'DevOps Engineer');
INSERT INTO skills (id, emp_id, skill_name) VALUES (1, 1, 'Java'), (2, 2, 'Selenium'), (3, 3, 'SQL'), (4, 4, 'Figma'), (5, 5, 'Docker');

SELECT full_name FROM employees WHERE dep_id = 1;
SELECT e.full_name, d.name FROM employees e JOIN departments d ON e.dep_id = d.id;
SELECT proj_id, COUNT(id) FROM roles GROUP BY proj_id;
SELECT full_name FROM employees WHERE id IN (SELECT emp_id FROM skills WHERE skill_name = 'Java');
UPDATE projects SET name = 'Веб-сайт' WHERE id = 2;

Билет №15

CREATE TABLE countries (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE hotels (id INT PRIMARY KEY, name VARCHAR(100), country_id INT REFERENCES countries(id));
CREATE TABLE tours (id INT PRIMARY KEY, name VARCHAR(100), hotel_id INT REFERENCES hotels(id));
CREATE TABLE tourists (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE bookings (id INT PRIMARY KEY, tour_id INT REFERENCES tours(id), tourist_id INT REFERENCES tourists(id), status VARCHAR(50));

INSERT INTO countries (id, name) VALUES (1, 'Турция'), (2, 'Египет'), (3, 'Мальдивы'), (4, 'ОАЭ'), (5, 'Тайланд');
INSERT INTO hotels (id, name, country_id) VALUES (1, 'Rixos Premium', 1), (2, 'Savoy', 2), (3, 'Kurumba', 3), (4, 'Burj Al Arab', 4), (5, 'Pattaya Park', 5);
INSERT INTO tours (id, name, hotel_id) VALUES (1, 'Все включено Анталия', 1), (2, 'Дайвинг Шарм', 2), (3, 'Романтика Мальдив', 3), (4, 'Шоппинг Дубай', 4), (5, 'Экзотика Пхукет', 5);
INSERT INTO tourists (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO bookings (id, tour_id, tourist_id, status) VALUES (1, 1, 1, 'Оплачено'), (2, 2, 2, 'Бронь'), (3, 3, 3, 'Оплачено'), (4, 4, 4, 'Отменено'), (5, 5, 5, 'Ожидает оплаты');

SELECT name FROM hotels WHERE country_id = 1;
SELECT t.name, h.name FROM tours t JOIN hotels h ON t.hotel_id = h.id;
SELECT tour_id, COUNT(id) FROM bookings GROUP BY tour_id;
SELECT full_name FROM tourists WHERE id IN (SELECT tourist_id FROM bookings WHERE status = 'Оплачено');
UPDATE bookings SET status = 'Оплачено' WHERE id = 2;

Билет №16

CREATE TABLE clients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE trainers (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE directions (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE memberships (id INT PRIMARY KEY, client_id INT REFERENCES clients(id), price DECIMAL);
CREATE TABLE schedule (id INT PRIMARY KEY, trainer_id INT REFERENCES trainers(id), dir_id INT REFERENCES directions(id), class_time TIMESTAMP);

INSERT INTO clients (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO trainers (id, full_name) VALUES (1, 'Тренер Арнольд'), (2, 'Тренер Мария'), (3, 'Тренер Сергей'), (4, 'Тренер Анна'), (5, 'Тренер Денис');
INSERT INTO directions (id, name) VALUES (1, 'Тренажерный зал'), (2, 'Йога'), (3, 'Кроссфит'), (4, 'Пилатес'), (5, 'Бокс');
INSERT INTO memberships (id, client_id, price) VALUES (1, 1, 15000), (2, 2, 20000), (3, 3, 25000), (4, 4, 30000), (5, 5, 35000);
INSERT INTO schedule (id, trainer_id, dir_id, class_time) VALUES (1, 1, 1, '2023-10-25 10:00:00'), (2, 2, 2, '2023-10-25 11:00:00'), (3, 3, 3, '2023-10-25 12:00:00'), (4, 4, 4, '2023-10-25 13:00:00'), (5, 5, 5, '2023-10-25 14:00:00');

SELECT price FROM memberships WHERE price > 20000;
SELECT s.class_time, d.name FROM schedule s JOIN directions d ON s.dir_id = d.id;
SELECT trainer_id, COUNT(id) FROM schedule GROUP BY trainer_id;
SELECT full_name FROM clients WHERE id IN (SELECT client_id FROM memberships WHERE price > 25000);
UPDATE memberships SET price = 16000 WHERE id = 1;

Билет №17

CREATE TABLE owners (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE districts (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE properties (id INT PRIMARY KEY, address VARCHAR(200), owner_id INT REFERENCES owners(id), dist_id INT REFERENCES districts(id));
CREATE TABLE tenants (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE contracts (id INT PRIMARY KEY, prop_id INT REFERENCES properties(id), tenant_id INT REFERENCES tenants(id), monthly_rent DECIMAL);

INSERT INTO owners (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO districts (id, name) VALUES (1, 'Центральный'), (2, 'Северный'), (3, 'Южный'), (4, 'Восточный'), (5, 'Западный');
INSERT INTO properties (id, address, owner_id, dist_id) VALUES (1, 'ул. Ленина 1', 1, 1), (2, 'ул. Мира 2', 2, 2), (3, 'ул. Садовая 3', 3, 3), (4, 'ул. Новая 4', 4, 4), (5, 'ул. Старая 5', 5, 5);
INSERT INTO tenants (id, full_name) VALUES (1, 'Алексеев А.А.'), (2, 'Борисов Б.Б.'), (3, 'Васильев В.В.'), (4, 'Григорьев Г.Г.'), (5, 'Дмитриев Д.Д.');
INSERT INTO contracts (id, prop_id, tenant_id, monthly_rent) VALUES (1, 1, 1, 50000), (2, 2, 2, 40000), (3, 3, 3, 30000), (4, 4, 4, 35000), (5, 5, 5, 45000);

SELECT address FROM properties WHERE dist_id = 1;
SELECT p.address, d.name FROM properties p JOIN districts d ON p.dist_id = d.id;
SELECT prop_id, AVG(monthly_rent) FROM contracts GROUP BY prop_id;
SELECT full_name FROM tenants WHERE id IN (SELECT tenant_id FROM contracts WHERE monthly_rent > 40000);
UPDATE contracts SET monthly_rent = 55000 WHERE id = 1;

Билет №18

CREATE TABLE clients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE couriers (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE routes (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE parcels (id INT PRIMARY KEY, weight DECIMAL, client_id INT REFERENCES clients(id));
CREATE TABLE deliveries (id INT PRIMARY KEY, parcel_id INT REFERENCES parcels(id), courier_id INT REFERENCES couriers(id), route_id INT REFERENCES routes(id), status VARCHAR(50));

INSERT INTO clients (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO couriers (id, full_name) VALUES (1, 'Курьер Алексей'), (2, 'Курьер Борис'), (3, 'Курьер Виктор'), (4, 'Курьер Григорий'), (5, 'Курьер Дмитрий');
INSERT INTO routes (id, name) VALUES (1, 'Маршрут А'), (2, 'Маршрут Б'), (3, 'Маршрут В'), (4, 'Маршрут Г'), (5, 'Маршрут Д');
INSERT INTO parcels (id, weight, client_id) VALUES (1, 1.5), (2, 5.0), (3, 10.2), (4, 0.5), (5, 2.3);
INSERT INTO deliveries (id, parcel_id, courier_id, route_id, status) VALUES (1, 1, 1, 1, 'Доставлено'), (2, 2, 2, 2, 'В пути'), (3, 3, 3, 3, 'Сортировка'), (4, 4, 4, 4, 'Ожидает курьера'), (5, 5, 5, 5, 'Доставлено');

SELECT * FROM parcels WHERE weight > 2.0;
SELECT d.status, c.full_name FROM deliveries d JOIN couriers c ON d.courier_id = c.id;
SELECT courier_id, COUNT(id) FROM deliveries GROUP BY courier_id;
SELECT full_name FROM clients WHERE id IN (SELECT client_id FROM parcels WHERE weight > 5.0);
UPDATE deliveries SET status = 'Доставлено' WHERE id = 2;