---

Билет №7

CREATE TABLE doctors (id INT PRIMARY KEY, full_name VARCHAR(100), spec VARCHAR(100));
CREATE TABLE patients (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE history (id INT PRIMARY KEY, patient_id INT REFERENCES patients(id), diagnosis VARCHAR(100));
CREATE TABLE operations (id INT PRIMARY KEY, patient_id INT REFERENCES patients(id), doctor_id INT REFERENCES doctors(id), op_name VARCHAR(100));
CREATE TABLE treatment_sheets (id INT PRIMARY KEY, patient_id INT REFERENCES patients(id), description VARCHAR(200));

INSERT INTO doctors (id, full_name, spec) VALUES (1, 'Иванов И.И.', 'Хирург'), (2, 'Петров П.П.', 'Анестезиолог'), (3, 'Сидорова С.С.', 'Терапевт'), (4, 'Смирнов А.А.', 'Кардиолог'), (5, 'Кузнецова Е.В.', 'Невролог');
INSERT INTO patients (id, full_name) VALUES (1, 'Алексеев А.А.'), (2, 'Борисов Б.Б.'), (3, 'Васильев В.В.'), (4, 'Григорьев Г.Г.'), (5, 'Дмитриев Д.Д.');
INSERT INTO history (id, patient_id, diagnosis) VALUES (1, 1, 'Желчекаменная болезнь'), (2, 2, 'Грыжа'), (3, 3, 'Перелом'), (4, 4, 'Инфаркт'), (5, 5, 'Инсульт');
INSERT INTO operations (id, patient_id, doctor_id, op_name) VALUES (1, 1, 1, 'Холецистэктомия'), (2, 2, 1, 'Герниопластика'), (3, 3, 1, 'Остеосинтез'), (4, 4, 4, 'Шунтирование'), (5, 5, 5, 'Трепанация');
INSERT INTO treatment_sheets (id, patient_id, description) VALUES (1, 1, 'Постельный режим'), (2, 2, 'Перевязки'), (3, 3, 'Гипс'), (4, 4, 'Капельницы'), (5, 5, 'Реабилитация');

SELECT op_name FROM operations WHERE doctor_id = 1;
SELECT o.op_name, d.full_name FROM operations o JOIN doctors d ON o.doctor_id = d.id;
SELECT doctor_id, COUNT(id) FROM operations GROUP BY doctor_id;
SELECT full_name FROM patients WHERE id IN (SELECT patient_id FROM operations WHERE doctor_id = 4);
UPDATE treatment_sheets SET description = 'Выписка' WHERE id = 1;

Билет №8

CREATE TABLE libraries (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE halls (id INT PRIMARY KEY, name VARCHAR(100), lib_id INT REFERENCES libraries(id));
CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(200), author VARCHAR(100));
CREATE TABLE readers (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE loans (id INT PRIMARY KEY, book_id INT REFERENCES books(id), reader_id INT REFERENCES readers(id), loan_date DATE);

INSERT INTO libraries (id, name) VALUES (1, 'РГБ'), (2, 'РНБ'), (3, 'Библиотека им. Пушкина'), (4, 'Библиотека им. Чехова'), (5, 'Юношеская библиотека');
INSERT INTO halls (id, name, lib_id) VALUES (1, 'Читальный зал 1', 1), (2, 'Зал периодики', 2), (3, 'Детский зал', 3), (4, 'Научный зал', 4), (5, 'Компьютерный зал', 5);
INSERT INTO books (id, title, author) VALUES (1, 'Война и мир', 'Толстой Л.Н.'), (2, 'Преступление и наказание', 'Достоевский Ф.М.'), (3, 'Мастер и Маргарита', 'Булгаков М.А.'), (4, 'Евгений Онегин', 'Пушкин А.С.'), (5, 'Мертвые души', 'Гоголь Н.В.');
INSERT INTO readers (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO loans (id, book_id, reader_id, loan_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');

SELECT title FROM books WHERE author = 'Толстой Л.Н.';
SELECT l.loan_date, b.title FROM loans l JOIN books b ON l.book_id = b.id;
SELECT reader_id, COUNT(id) FROM loans GROUP BY reader_id;
SELECT full_name FROM readers WHERE id IN (SELECT reader_id FROM loans WHERE book_id = 3);
UPDATE loans SET loan_date = '2023-11-01' WHERE id = 1;

Билет №9

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE subjects (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE teachers (id INT PRIMARY KEY, full_name VARCHAR(100), dep_id INT REFERENCES departments(id));
CREATE TABLE students (id INT PRIMARY KEY, full_name VARCHAR(100), dep_id INT REFERENCES departments(id));
CREATE TABLE grades (id INT PRIMARY KEY, student_id INT REFERENCES students(id), subj_id INT REFERENCES subjects(id), grade INT);

INSERT INTO departments (id, name) VALUES (1, 'Кафедра ИТ'), (2, 'Кафедра Высшей математики'), (3, 'Кафедра Физики'), (4, 'Кафедра Иностранных языков'), (5, 'Кафедра Истории');
INSERT INTO subjects (id, name) VALUES (1, 'Базы данных'), (2, 'Математический анализ'), (3, 'Квантовая механика'), (4, 'Английский язык'), (5, 'История России');
INSERT INTO teachers (id, full_name, dep_id) VALUES (1, 'Иванов И.И.', 1), (2, 'Петров П.П.', 2), (3, 'Сидоров С.С.', 3), (4, 'Смирнова А.А.', 4), (5, 'Кузнецов Е.В.', 5);
INSERT INTO students (id, full_name, dep_id) VALUES (1, 'Алексеев А.А.', 1), (2, 'Борисов Б.Б.', 2), (3, 'Васильев В.В.', 3), (4, 'Григорьев Г.Г.', 4), (5, 'Дмитриев Д.Д.', 5);
INSERT INTO grades (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 dep_id = 1;
SELECT g.grade, s.name FROM grades g JOIN subjects s ON g.subj_id = s.id;
SELECT subj_id, AVG(grade) FROM grades GROUP BY subj_id;
SELECT full_name FROM students WHERE id IN (SELECT student_id FROM grades WHERE grade = 5);
UPDATE grades SET grade = 4 WHERE id = 1;

Билет №10

CREATE TABLE categories (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), cat_id INT REFERENCES categories(id), price DECIMAL);
CREATE TABLE customers (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE orders (id INT PRIMARY KEY, cust_id INT REFERENCES customers(id), order_date DATE);
CREATE TABLE order_items (id INT PRIMARY KEY, order_id INT REFERENCES orders(id), prod_id INT REFERENCES products(id), qty INT);

INSERT INTO categories (id, name) VALUES (1, 'Смартфоны'), (2, 'Ноутбуки'), (3, 'Планшеты'), (4, 'Аксессуары'), (5, 'Телевизоры');
INSERT INTO products (id, name, cat_id, price) VALUES (1, 'iPhone 15', 1, 100000), (2, 'MacBook Air', 2, 120000), (3, 'iPad Pro', 3, 80000), (4, 'AirPods Pro', 4, 25000), (5, 'Samsung TV', 5, 60000);
INSERT INTO customers (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO orders (id, cust_id, order_date) VALUES (1, 1, '2023-10-01'), (2, 2, '2023-10-05'), (3, 3, '2023-10-10'), (4, 4, '2023-10-15'), (5, 5, '2023-10-20');
INSERT INTO order_items (id, order_id, prod_id, qty) VALUES (1, 1, 1, 1), (2, 2, 2, 1), (3, 3, 3, 2), (4, 4, 4, 1), (5, 5, 5, 1);

SELECT name, price FROM products WHERE price > 50000;
SELECT p.name, c.name FROM products p JOIN categories c ON p.cat_id = c.id;
SELECT order_id, SUM(qty) FROM order_items GROUP BY order_id;
SELECT full_name FROM customers WHERE id IN (SELECT cust_id FROM orders WHERE id IN (SELECT order_id FROM order_items WHERE prod_id = 1));
UPDATE products SET price = 95000 WHERE id = 1;

Билет №11

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 tickets (id INT PRIMARY KEY, doc_id INT REFERENCES doctors(id), pat_id INT REFERENCES patients(id), slot_time TIMESTAMP);
CREATE TABLE reports (id INT PRIMARY KEY, ticket_id INT REFERENCES tickets(id), conclusion VARCHAR(500));

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 tickets (id, doc_id, pat_id, slot_time) VALUES (1, 1, 1, '2023-10-25 09:00:00'), (2, 2, 2, '2023-10-25 10:00:00'), (3, 3, 3, '2023-10-25 11:00:00'), (4, 4, 4, '2023-10-25 12:00:00'), (5, 5, 5, '2023-10-25 13:00:00');
INSERT INTO reports (id, ticket_id, conclusion) VALUES (1, 1, 'Здоров'), (2, 2, 'Направление на операцию'), (3, 3, 'ЭКГ в норме'), (4, 4, 'Рецепт на очки'), (5, 5, 'Промывание');

SELECT * FROM tickets WHERE slot_time > '2023-10-25 10:00:00';
SELECT t.slot_time, d.full_name FROM tickets t JOIN doctors d ON t.doc_id = d.id;
SELECT doc_id, COUNT(id) FROM tickets GROUP BY doc_id;
SELECT full_name FROM patients WHERE id IN (SELECT pat_id FROM tickets WHERE doc_id = 1);
UPDATE reports SET conclusion = 'Повторный прием' WHERE id = 1;

Билет №12

CREATE TABLE room_types (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE rooms (id INT PRIMARY KEY, room_num VARCHAR(10), type_id INT REFERENCES room_types(id));
CREATE TABLE guests (id INT PRIMARY KEY, full_name VARCHAR(100));
CREATE TABLE bookings (id INT PRIMARY KEY, guest_id INT REFERENCES guests(id), room_id INT REFERENCES rooms(id), price DECIMAL);
CREATE TABLE services (id INT PRIMARY KEY, booking_id INT REFERENCES bookings(id), service_name VARCHAR(100), cost DECIMAL);

INSERT INTO room_types (id, name) VALUES (1, 'Стандарт'), (2, 'Комфорт'), (3, 'Полулюкс'), (4, 'Люкс'), (5, 'Президентский');
INSERT INTO rooms (id, room_num, type_id) VALUES (1, '101', 1), (2, '202', 2), (3, '303', 3), (4, '404', 4), (5, '505', 5);
INSERT INTO guests (id, full_name) VALUES (1, 'Иванов И.И.'), (2, 'Петров П.П.'), (3, 'Сидоров С.С.'), (4, 'Смирнов А.А.'), (5, 'Кузнецова Е.В.');
INSERT INTO bookings (id, guest_id, room_id, price) VALUES (1, 1, 1, 5000), (2, 2, 2, 8000), (3, 3, 3, 12000), (4, 4, 4, 20000), (5, 5, 5, 50000);
INSERT INTO services (id, booking_id, service_name, cost) VALUES (1, 1, 'Завтрак', 1000), (2, 2, 'Парковка', 500), (3, 3, 'СПА', 3000), (4, 4, 'Трансфер', 2000), (5, 5, 'Мини-бар', 5000);

SELECT room_num FROM rooms WHERE type_id = 1;
SELECT b.price, r.room_num FROM bookings b JOIN rooms r ON b.room_id = r.id;
SELECT booking_id, SUM(cost) FROM services GROUP BY booking_id;
SELECT full_name FROM guests WHERE id IN (SELECT guest_id FROM bookings WHERE price > 10000);
UPDATE bookings SET price = 5500 WHERE id = 1;