---

Билет №1

CREATE TABLE faculties (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100), faculty_id INT REFERENCES faculties(id));
CREATE TABLE groups (id INT PRIMARY KEY, name VARCHAR(50), department_id INT REFERENCES departments(id));
CREATE TABLE students (id INT PRIMARY KEY, full_name VARCHAR(100), group_id INT REFERENCES groups(id));
CREATE TABLE grades (id INT PRIMARY KEY, student_id INT REFERENCES students(id), subject VARCHAR(100), grade INT);

INSERT INTO faculties (id, name) VALUES (1, 'Информационных технологий'), (2, 'Экономики'), (3, 'Юридический'), (4, 'Медицинский'), (5, 'Строительный');
INSERT INTO departments (id, name, faculty_id) VALUES (1, 'Программной инженерии', 1), (2, 'Бухгалтерского учета', 2), (3, 'Уголовного права', 3), (4, 'Хирургии', 4), (5, 'Архитектуры', 5);
INSERT INTO groups (id, name, department_id) VALUES (1, 'ПИ-101', 1), (2, 'БУ-201', 2), (3, 'УП-301', 3), (4, 'ХИР-401', 4), (5, 'АРХ-501', 5);
INSERT INTO students (id, full_name, group_id) VALUES (1, 'Иванов Иван', 1), (2, 'Петрова Анна', 2), (3, 'Сидоров Алексей', 3), (4, 'Смирнова Елена', 4), (5, 'Кузнецов Дмитрий', 5);
INSERT INTO grades (id, student_id, subject, grade) VALUES (1, 1, 'Базы данных', 5), (2, 2, 'Макроэкономика', 4), (3, 3, 'Правоведение', 5), (4, 4, 'Анатомия', 3), (5, 5, 'Начертательная геометрия', 4);

SELECT full_name FROM students WHERE group_id = 1;
SELECT s.full_name, g.name FROM students s JOIN groups g ON s.group_id = g.id;
SELECT subject, AVG(grade) FROM grades GROUP BY subject;
SELECT full_name FROM students WHERE id IN (SELECT student_id FROM grades WHERE grade = 5);
UPDATE grades SET grade = 4 WHERE id = 4;

Билет №2

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE positions (id INT PRIMARY KEY, title VARCHAR(100));
CREATE TABLE employees (id INT PRIMARY KEY, full_name VARCHAR(100), dep_id INT REFERENCES departments(id), pos_id INT REFERENCES positions(id));
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL);
CREATE TABLE sales (id INT PRIMARY KEY, emp_id INT REFERENCES employees(id), prod_id INT REFERENCES products(id), qty INT);

INSERT INTO departments (id, name) VALUES (1, 'Молочный'), (2, 'Мясной'), (3, 'Хлебобулочный'), (4, 'Бакалея'), (5, 'Напитки');
INSERT INTO positions (id, title) VALUES (1, 'Продавец'), (2, 'Кассир'), (3, 'Менеджер'), (4, 'Грузчик'), (5, 'Директор');
INSERT INTO employees (id, full_name, dep_id, pos_id) VALUES (1, 'Смирнов И.А.', 1, 1), (2, 'Козлова Е.В.', 2, 2), (3, 'Морозов Д.С.', 3, 3), (4, 'Волков А.А.', 4, 4), (5, 'Лебедева О.Н.', 5, 5);
INSERT INTO products (id, name, price) VALUES (1, 'Молоко Домик в деревне', 90.0), (2, 'Колбаса Докторская', 450.0), (3, 'Хлеб Бородинский', 50.0), (4, 'Гречка Макфа', 120.0), (5, 'Сок Добрый Яблоко', 110.0);
INSERT INTO sales (id, emp_id, prod_id, qty) VALUES (1, 1, 1, 2), (2, 2, 2, 1), (3, 3, 3, 3), (4, 4, 4, 5), (5, 5, 5, 2);

SELECT name, price FROM products WHERE price > 100;
SELECT e.full_name, d.name FROM employees e JOIN departments d ON e.dep_id = d.id;
SELECT prod_id, SUM(qty) FROM sales GROUP BY prod_id;
SELECT full_name FROM employees WHERE id IN (SELECT emp_id FROM sales WHERE qty > 2);
UPDATE products SET price = 95.0 WHERE id = 1;

Билет №3

CREATE TABLE locations (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE troop_types (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE units (id INT PRIMARY KEY, name VARCHAR(100), loc_id INT REFERENCES locations(id), troop_id INT REFERENCES troop_types(id));
CREATE TABLE companies (id INT PRIMARY KEY, name VARCHAR(100), unit_id INT REFERENCES units(id));
CREATE TABLE personnel (id INT PRIMARY KEY, full_name VARCHAR(100), comp_id INT REFERENCES companies(id));

INSERT INTO locations (id, name) VALUES (1, 'Московская область'), (2, 'Ленинградская область'), (3, 'Ростовская область'), (4, 'Мурманская область'), (5, 'Приморский край');
INSERT INTO troop_types (id, name) VALUES (1, 'Сухопутные войска'), (2, 'ВМФ'), (3, 'ВКС'), (4, 'ВДВ'), (5, 'РВСН');
INSERT INTO units (id, name, loc_id, troop_id) VALUES (1, 'В/Ч 12345', 1, 1), (2, 'В/Ч 23456', 2, 2), (3, 'В/Ч 34567', 3, 3), (4, 'В/Ч 45678', 4, 4), (5, 'В/Ч 56789', 5, 5);
INSERT INTO companies (id, name, unit_id) VALUES (1, '1-я рота', 1), (2, '2-я рота', 2), (3, '3-я рота', 3), (4, '4-я рота', 4), (5, '5-я рота', 5);
INSERT INTO personnel (id, full_name, comp_id) VALUES (1, 'Рядовой Иванов', 1), (2, 'Сержант Петров', 2), (3, 'Лейтенант Сидоров', 3), (4, 'Капитан Смирнов', 4), (5, 'Майор Кузнецов', 5);

SELECT name FROM units WHERE loc_id = 1;
SELECT u.name, l.name FROM units u JOIN locations l ON u.loc_id = l.id;
SELECT comp_id, COUNT(id) FROM personnel GROUP BY comp_id;
SELECT full_name FROM personnel WHERE comp_id IN (SELECT id FROM companies WHERE unit_id = 1);
UPDATE personnel SET full_name = 'Ефрейтор Иванов' WHERE id = 1;

Билет №4

CREATE TABLE resorts (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE housing_types (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE tours (id INT PRIMARY KEY, name VARCHAR(100), resort_id INT REFERENCES resorts(id), house_id INT REFERENCES housing_types(id));
CREATE TABLE clients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE vouchers (id INT PRIMARY KEY, tour_id INT REFERENCES tours(id), client_id INT REFERENCES clients(id), price DECIMAL);

INSERT INTO resorts (id, name) VALUES (1, 'Сочи'), (2, 'Ялта'), (3, 'Анапа'), (4, 'Геленджик'), (5, 'Кисловодск');
INSERT INTO housing_types (id, name) VALUES (1, 'Отель 5 звезд'), (2, 'Отель 4 звезды'), (3, 'Пансионат'), (4, 'Санаторий'), (5, 'Гостевой дом');
INSERT INTO tours (id, name, resort_id, house_id) VALUES (1, 'Летний отдых', 1, 1), (2, 'Крымский бриз', 2, 2), (3, 'Семейный', 3, 3), (4, 'Морской', 4, 5), (5, 'Оздоровительный', 5, 4);
INSERT INTO clients (id, full_name) VALUES (1, 'Новиков И.И.'), (2, 'Морозова А.С.'), (3, 'Волков П.П.'), (4, 'Лебедева Е.А.'), (5, 'Соколов В.В.');
INSERT INTO vouchers (id, tour_id, client_id, price) VALUES (1, 1, 1, 150000), (2, 2, 2, 120000), (3, 3, 3, 80000), (4, 4, 4, 60000), (5, 5, 5, 95000);

SELECT * FROM vouchers WHERE price > 100000;
SELECT t.name, r.name FROM tours t JOIN resorts r ON t.resort_id = r.id;
SELECT tour_id, AVG(price) FROM vouchers GROUP BY tour_id;
SELECT full_name FROM clients WHERE id IN (SELECT client_id FROM vouchers WHERE price > 100000);
UPDATE vouchers SET price = 85000 WHERE id = 3;

Билет №5

CREATE TABLE garages (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE routes (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE staff (id INT PRIMARY KEY, full_name VARCHAR(100), role VARCHAR(50));
CREATE TABLE drivers (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE vehicles (id INT PRIMARY KEY, plate VARCHAR(20), model VARCHAR(50), garage_id INT REFERENCES garages(id), route_id INT REFERENCES routes(id), driver_id INT REFERENCES drivers(id));

INSERT INTO garages (id, name) VALUES (1, 'Северный парк'), (2, 'Южный парк'), (3, 'Центральный гараж'), (4, 'Восточный парк'), (5, 'Западный гараж');
INSERT INTO routes (id, name) VALUES (1, 'Маршрут 15'), (2, 'Маршрут 24'), (3, 'Маршрут 3'), (4, 'Маршрут 11'), (5, 'Маршрут 7');
INSERT INTO staff (id, full_name, role) VALUES (1, 'Иванов А.', 'Механик'), (2, 'Петров Б.', 'Диспетчер'), (3, 'Сидоров В.', 'Автомойщик'), (4, 'Смирнов Г.', 'Охранник'), (5, 'Кузнецов Д.', 'Электрик');
INSERT INTO drivers (id, full_name) VALUES (1, 'Васильев В.В.'), (2, 'Попов П.П.'), (3, 'Ильин И.И.'), (4, 'Сергеев С.С.'), (5, 'Романов Р.Р.');
INSERT INTO vehicles (id, plate, model, garage_id, route_id, driver_id) VALUES (1, 'А123ВЕ', 'ЛиАЗ-5292', 1, 1, 1), (2, 'В456РО', 'НефАЗ-5299', 2, 2, 2), (3, 'С789МК', 'ПАЗ-3205', 3, 3, 3), (4, 'Е012ТХ', 'КАВЗ-4270', 4, 4, 4), (5, 'О345УА', 'ГАЗель NEXT', 5, 5, 5);

SELECT model, plate FROM vehicles WHERE garage_id = 1;
SELECT v.model, r.name FROM vehicles v JOIN routes r ON v.route_id = r.id;
SELECT garage_id, COUNT(id) FROM vehicles GROUP BY garage_id;
SELECT full_name FROM drivers WHERE id IN (SELECT driver_id FROM vehicles WHERE route_id = 3);
UPDATE routes SET name = 'Маршрут 3А' WHERE id = 3;

Билет №6

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE doctors (id INT PRIMARY KEY, full_name VARCHAR(100), dep_id INT REFERENCES departments(id));
CREATE TABLE patients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE staff (id INT PRIMARY KEY, full_name VARCHAR(100), dep_id INT REFERENCES departments(id));
CREATE TABLE history (id INT PRIMARY KEY, doctor_id INT REFERENCES doctors(id), patient_id INT REFERENCES patients(id), diagnosis VARCHAR(200));

INSERT INTO departments (id, name) VALUES (1, 'Терапевтическое'), (2, 'Хирургическое'), (3, 'Неврологическое'), (4, 'Офтальмологическое'), (5, 'Кардиологическое');
INSERT INTO doctors (id, full_name, dep_id) VALUES (1, 'Д-р Быков А.Е.', 1), (2, 'Д-р Купитман И.Н.', 2), (3, 'Д-р Романенко Г.В.', 3), (4, 'Д-р Лобанов С.А.', 4), (5, 'Д-р Левин Б.А.', 5);
INSERT INTO patients (id, full_name) VALUES (1, 'Тихонов А.А.'), (2, 'Макарова В.И.'), (3, 'Зайцев К.Л.'), (4, 'Мельникова О.П.'), (5, 'Крюков Н.Н.');
INSERT INTO staff (id, full_name, dep_id) VALUES (1, 'Медсестра Люба', 1), (2, 'Санитар Петя', 2), (3, 'Регистратор Маша', 3), (4, 'Уборщица Зина', 4), (5, 'Лаборант Катя', 5);
INSERT INTO history (id, doctor_id, patient_id, diagnosis) VALUES (1, 1, 1, 'ОРВИ'), (2, 2, 2, 'Аппендицит'), (3, 3, 3, 'Остеохондроз'), (4, 4, 4, 'Миопия'), (5, 5, 5, 'Ишемия');

SELECT diagnosis FROM history WHERE doctor_id = 1;
SELECT d.full_name, dep.name FROM doctors d JOIN departments dep ON d.dep_id = dep.id;
SELECT doctor_id, COUNT(id) FROM history GROUP BY doctor_id;
SELECT full_name FROM patients WHERE id IN (SELECT patient_id FROM history WHERE diagnosis = 'ОРВИ');
UPDATE history SET diagnosis = 'Грипп' WHERE id = 1;