---

Билет №19

CREATE TABLE branches (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE cars (id INT PRIMARY KEY, model VARCHAR(100), branch_id INT REFERENCES branches(id));
CREATE TABLE clients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE leases (id INT PRIMARY KEY, car_id INT REFERENCES cars(id), client_id INT REFERENCES clients(id), amount DECIMAL);
CREATE TABLE accidents (id INT PRIMARY KEY, lease_id INT REFERENCES leases(id), description VARCHAR(200));

INSERT INTO branches (id, name) VALUES (1, 'Филиал Аэропорт'), (2, 'Филиал Вокзал'), (3, 'Филиал Центр'), (4, 'Филиал Север'), (5, 'Филиал Юг');
INSERT INTO cars (id, model, branch_id) VALUES (1, 'Hyundai Solaris', 1), (2, 'Kia Rio', 2), (3, 'Toyota Camry', 3), (4, 'BMW X5', 4), (5, 'Mercedes E-Class', 5);
INSERT INTO clients (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO leases (id, car_id, client_id, amount) VALUES (1, 1, 1, 2000), (2, 2, 2, 2500), (3, 3, 3, 5000), (4, 4, 4, 15000), (5, 5, 5, 20000);
INSERT INTO accidents (id, lease_id, description) VALUES (1, 1, 'Царапина бампера'), (2, 2, 'Скол на стекле'), (3, 3, 'ДТП легкое'), (4, 4, 'Прокол колеса'), (5, 5, 'Без повреждений');

SELECT model FROM cars WHERE branch_id = 3;
SELECT c.model, b.name FROM cars c JOIN branches b ON c.branch_id = b.id;
SELECT car_id, COUNT(id) FROM leases GROUP BY car_id;
SELECT full_name FROM clients WHERE id IN (SELECT client_id FROM leases WHERE amount > 10000);
UPDATE leases SET amount = 2200 WHERE id = 1;

Билет №20

CREATE TABLE owners (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE pets (id INT PRIMARY KEY, name VARCHAR(100), owner_id INT REFERENCES owners(id));
CREATE TABLE vets (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE appointments (id INT PRIMARY KEY, pet_id INT REFERENCES pets(id), vet_id INT REFERENCES vets(id), app_date DATE);
CREATE TABLE vaccinations (id INT PRIMARY KEY, pet_id INT REFERENCES pets(id), vaccine VARCHAR(100));

INSERT INTO owners (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO pets (id, name, owner_id) VALUES (1, 'Бобик', 1), (2, 'Мурзик', 2), (3, 'Шарик', 3), (4, 'Рекс', 4), (5, 'Барсик', 5);
INSERT INTO vets (id, full_name) VALUES (1, 'Доктор Айболит', 1), (2, 'Доктор Попов', 2), (3, 'Доктор Смирнова', 3), (4, 'Доктор Васильев', 4), (5, 'Доктор Ильина', 5);
INSERT INTO appointments (id, pet_id, vet_id, app_date) VALUES (1, 1, 1, '2023-10-01'), (2, 2, 2, '2023-10-05'), (3, 3, 3, '2023-10-10'), (4, 4, 4, '2023-10-15'), (5, 5, 5, '2023-10-20');
INSERT INTO vaccinations (id, pet_id, vaccine) VALUES (1, 1, 'От бешенства'), (2, 2, 'Комплексная'), (3, 3, 'От клещей'), (4, 4, 'От бешенства'), (5, 5, 'От лишая');

SELECT name FROM pets WHERE owner_id = 1;
SELECT p.name, o.full_name FROM pets p JOIN owners o ON p.owner_id = o.id;
SELECT vet_id, COUNT(id) FROM appointments GROUP BY vet_id;
SELECT full_name FROM owners WHERE id IN (SELECT owner_id FROM pets WHERE id IN (SELECT pet_id FROM vaccinations WHERE vaccine = 'От бешенства'));
UPDATE appointments SET app_date = '2023-10-02' WHERE id = 1;

Билет №21

CREATE TABLE venues (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE artists (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE events (id INT PRIMARY KEY, name VARCHAR(100), venue_id INT REFERENCES venues(id), artist_id INT REFERENCES artists(id));
CREATE TABLE spectators (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE tickets (id INT PRIMARY KEY, event_id INT REFERENCES events(id), spec_id INT REFERENCES spectators(id), price DECIMAL);

INSERT INTO venues (id, name) VALUES (1, 'Лужники'), (2, 'Олимпийский'), (3, 'Крокус Сити Холл'), (4, 'ВТБ Арена'), (5, 'Ледовый дворец');
INSERT INTO artists (id, name) VALUES (1, 'Баста'), (2, 'Кипелов'), (3, 'Руки Вверх'), (4, 'Ленинград'), (5, 'Ария');
INSERT INTO events (id, name, venue_id, artist_id) VALUES (1, 'Большой концерт Басты', 1, 1), (2, 'Юбилейный тур', 2, 2), (3, 'Дискотека 90-х', 3, 3), (4, 'Прощальный тур', 4, 4), (5, 'Симфонический концерт', 5, 5);
INSERT INTO spectators (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO tickets (id, event_id, spec_id, price) VALUES (1, 1, 1, 5000), (2, 2, 2, 3000), (3, 3, 3, 7000), (4, 4, 4, 10000), (5, 5, 5, 2000);

SELECT price FROM tickets WHERE price > 5000;
SELECT e.name, v.name FROM events e JOIN venues v ON e.venue_id = v.id;
SELECT event_id, SUM(price) FROM tickets GROUP BY event_id;
SELECT full_name FROM spectators WHERE id IN (SELECT spec_id FROM tickets WHERE price > 7000);
UPDATE tickets SET price = 5500 WHERE id = 1;

Билет №22

CREATE TABLE masters (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE clients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE services (id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL);
CREATE TABLE appointments (id INT PRIMARY KEY, master_id INT REFERENCES masters(id), client_id INT REFERENCES clients(id), app_date DATE);
CREATE TABLE receipts (id INT PRIMARY KEY, app_id INT REFERENCES appointments(id), amount DECIMAL);

INSERT INTO masters (id, full_name) VALUES (1, 'Мастер Анна'), (2, 'Мастер Ольга'), (3, 'Мастер Елена'), (4, 'Мастер Сергей'), (5, 'Мастер Дмитрий');
INSERT INTO clients (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петрова П.П.'), (3, 'Сидорова С.С.'), (4, 'Смирнова А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO services (id, name, price) VALUES (1, 'Стрижка мужская', 1000), (2, 'Стрижка женская', 1500), (3, 'Окрашивание', 3000), (4, 'Маникюр', 1200), (5, 'Педикюр', 1800);
INSERT INTO appointments (id, master_id, client_id, app_date) VALUES (1, 1, 1, '2023-10-01'), (2, 2, 2, '2023-10-05'), (3, 3, 3, '2023-10-10'), (4, 4, 4, '2023-10-15'), (5, 5, 5, '2023-10-20');
INSERT INTO receipts (id, app_id, amount) VALUES (1, 1, 1000), (2, 2, 1500), (3, 3, 3000), (4, 4, 1200), (5, 5, 1800);

SELECT name, price FROM services WHERE price > 1500;
SELECT a.app_date, m.full_name FROM appointments a JOIN masters m ON a.master_id = m.id;
SELECT master_id, COUNT(id) FROM appointments GROUP BY master_id;
SELECT full_name FROM clients WHERE id IN (SELECT client_id FROM appointments WHERE id IN (SELECT app_id FROM receipts WHERE amount > 2000));
UPDATE services SET price = 1100 WHERE id = 1;

Билет №23

CREATE TABLE foremen (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE sites (id INT PRIMARY KEY, name VARCHAR(100), foreman_id INT REFERENCES foremen(id));
CREATE TABLE building_materials (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE budgets (id INT PRIMARY KEY, site_id INT REFERENCES sites(id), total_cost DECIMAL);
CREATE TABLE stages (id INT PRIMARY KEY, site_id INT REFERENCES sites(id), stage_name VARCHAR(100));

INSERT INTO foremen (id, full_name) VALUES (1, 'Прораб Иванов'), (2, 'Прораб Петров'), (3, 'Прораб Сидоров'), (4, 'Прораб Смирнов'), (5, 'Прораб Кузнецов');
INSERT INTO sites (id, name, foreman_id) VALUES (1, 'ЖК Альфа', 1), (2, 'ЖК Бета', 2), (3, 'ЖК Гамма', 3), (4, 'ТЦ Омега', 4), (5, 'Склад Дельта', 5);
INSERT INTO building_materials (id, name) VALUES (1, 'Кирпич'), (2, 'Цемент'), (3, 'Арматура'), (4, 'Песок'), (5, 'Щебень');
INSERT INTO budgets (id, site_id, total_cost) VALUES (1, 1, 10000000), (2, 2, 20000000), (3, 3, 15000000), (4, 4, 50000000), (5, 5, 5000000);
INSERT INTO stages (id, site_id, stage_name) VALUES (1, 1, 'Фундамент'), (2, 2, 'Возведение стен'), (3, 3, 'Кровля'), (4, 4, 'Отделка'), (5, 5, 'Благоустройство');

SELECT * FROM budgets WHERE total_cost > 15000000;
SELECT s.name, f.full_name FROM sites s JOIN foremen f ON s.foreman_id = f.id;
SELECT site_id, COUNT(id) FROM stages GROUP BY site_id;
SELECT full_name FROM foremen WHERE id IN (SELECT foreman_id FROM sites WHERE id IN (SELECT site_id FROM budgets WHERE total_cost > 20000000));
UPDATE budgets SET total_cost = 11000000 WHERE id = 1;

Билет №24

CREATE TABLE classes (id INT PRIMARY KEY, name VARCHAR(10));
CREATE TABLE teachers (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE students (id INT PRIMARY KEY, full_name VARCHAR(100), class_id INT REFERENCES classes(id));
CREATE TABLE subjects (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE gradebook (id INT PRIMARY KEY, student_id INT REFERENCES students(id), subj_id INT REFERENCES subjects(id), grade INT);

INSERT INTO classes (id, name) VALUES (1, '10А'), (2, '10Б'), (3, '11А'), (4, '11Б'), (5, '9В');
INSERT INTO teachers (id, full_name) VALUES (1, 'Учитель Иванов'), (2, 'Учитель Петров'), (3, 'Учитель Сидорова'), (4, 'Учитель Смирнова'), (5, 'Учитель Кузнецова');
INSERT INTO students (id, full_name, class_id) VALUES (1, 'Ученик Алексеев', 1), (2, 'Ученик Борисов', 2), (3, 'Ученик Васильев', 3), (4, 'Ученик Григорьев', 4), (5, 'Ученик Дмитриев', 5);
INSERT INTO subjects (id, name) VALUES (1, 'Алгебра'), (2, 'Геометрия'), (3, 'Физика'), (4, 'Химия'), (5, 'Биология');
INSERT INTO gradebook (id, student_id, subj_id, grade) VALUES (1, 1, 1, 5), (2, 2, 2, 4), (3, 3, 3, 3), (4, 4, 4, 5), (5, 5, 5, 4);

SELECT full_name FROM students WHERE class_id = 1;
SELECT s.full_name, c.name FROM students s JOIN classes c ON s.class_id = c.id;
SELECT subj_id, AVG(grade) FROM gradebook GROUP BY subj_id;
SELECT full_name FROM students WHERE id IN (SELECT student_id FROM gradebook WHERE grade = 5);
UPDATE gradebook SET grade = 4 WHERE id = 1;