Интерактивный рабочий лист: Операторы манипулирования данными

Задание 2.1 Модификация записей по соединению (UPDATE ... FROM)

В клинике работают две таблицы: приемы врачей и платежные транзакции:

-- Журнал приемов
CREATE TABLE medical_appointments (
  appointment_id SERIAL PRIMARY KEY,
  patient_id INT NOT NULL,
  doctor_id INT NOT NULL,
  appointment_status VARCHAR(25) NOT NULL DEFAULT 'AWAITING_PAYMENT',
  created_at TIMESTAMP NOT NULL,
  confirmed_at TIMESTAMP
);

-- Платежи кассы/онлайн-банка
CREATE TABLE appointment_invoices (
  invoice_id SERIAL PRIMARY KEY,
  appointment_id INT UNIQUE NOT NULL,
  payment_status VARCHAR(20) NOT NULL, -- 'PAID', 'REJECTED', 'PENDING'
  payment_time TIMESTAMP NOT NULL
);

Требование: Напишите SQL-запрос UPDATE medical_appointments с использованием секции FROM appointment_invoices, который переводит статус приема в 'CONFIRMED' и заполняет поле confirmed_at = appointment_invoices.payment_time для всех приемов, находящихся в статусе 'AWAITING_PAYMENT', по которым зафиксирован платеж со статусом 'PAID'. Запрос должен возвращать через RETURNING поля appointment_id, patient_id и новый статус.

Задание 2.2 Селективное удаление отмененных приемов с фиксацией аудита (DELETE ... RETURNING)

Согласно регламенту архивации, отмененные приемы (appointment_status = 'CANCELLED'), созданные более 1 года назад (created_at < CURRENT_DATE - INTERVAL '1 year'), подлежат удалению из оперативной базы.

Требование: Составьте запрос DELETE FROM medical_appointments, который удаляет указанные устаревшие отмененные записи и одновременно возвращает через RETURNING поля appointment_id, patient_id, created_at для передачи в журнал аудита службы безопасности.

Задание 2.3 Аналитический вопрос

Почему саму обновляемую таблицу medical_appointments категорически нельзя указывать в списке таблиц секции FROM при выполнении запроса UPDATE в PostgreSQL?

Задание 3.1 Перенос исторических данных (INSERT INTO ... SELECT)

В банке функционирует таблица действующих договоров loan_contracts и архивная таблица loan_contracts_archive:

-- Действующие договоры
CREATE TABLE loan_contracts (
  contract_num VARCHAR(24) PRIMARY KEY,
  client_id INT NOT NULL,
  issued_amount NUMERIC(14, 2) NOT NULL,
  current_debt NUMERIC(14, 2) NOT NULL,
  status VARCHAR(20) NOT NULL, -- 'ACTIVE', 'OVERDUE', 'CLOSED'
  closed_at DATE
);

-- Архивная таблица
CREATE TABLE loan_contracts_archive (
  archive_id SERIAL PRIMARY KEY,
  contract_num VARCHAR(24) NOT NULL,
  client_id INT NOT NULL,
  total_settled NUMERIC(14, 2) NOT NULL,
  archived_date DATE NOT NULL DEFAULT CURRENT_DATE
);

Требование: Напишите SQL-запрос INSERT INTO loan_contracts_archive (...) SELECT ..., который осуществляет перенос всех договоров со статусом 'CLOSED' и датой закрытия старше 1 года (closed_at < CURRENT_DATE - INTERVAL '1 year'). Укажите явные списки столбцов и свяжите поля contract_num, client_id, issued_amount (в качестве total_settled).

Задание 3.2 Массовое начисление процентов по кредитам

В конце расчетного дня банк производит начисление процентов по всем активным договорам (status = 'ACTIVE' с остатком долга current_debt > 0). Дневная ставка составляет 0.05% от текущего долга (current_debt * 0.0005).

Требование: Напишите запрос UPDATE loan_contracts, который прибавляет рассчитанные дневные проценты к сумме current_debt только по активным договорам и возвращает через RETURNING перечень обновленных contract_num, client_id и новую задолженность current_debt.

Задание 3.3 Аналитический вопрос

В чем заключается опасность выполнения массового UPDATE по миллиону договоров в единой длинной транзакции в высоконагруженной системе? Как эта операция повлияет на журнал предзаписи WAL и фоновый процесс VACUUM?

Задание 4.1 Идемпотентная фиксация оценок (UPSERT)

В электронной ведомости действует таблица оценок с составным ограничением уникальности:

CREATE TABLE student_grades (
  grade_id SERIAL PRIMARY KEY,
  student_id INT NOT NULL,
  discipline_id INT NOT NULL,
  grade_value INT NOT NULL CHECK (grade_value BETWEEN 2 AND 5),
  teacher_id INT NOT NULL,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uq_student_discipline UNIQUE (student_id, discipline_id)
);

Требование: Напишите запрос INSERT INTO student_grades, выставляющий студенту student_id = 45 по дисциплине discipline_id = 8 оценку grade_value = 5 от преподавателя teacher_id = 12:

• Используйте конструкцию ON CONFLICT (student_id, discipline_id) DO UPDATE SET.
• В случае повторного выставления оценки по этой же дисциплине запрос должен переписать grade_value = EXCLUDED.grade_value, актуализировать teacher_id = EXCLUDED.teacher_id и выставить assigned_at = CURRENT_TIMESTAMP.
• Вернуть через RETURNING колонки grade_id, student_id, grade_value, assigned_at.

Задание 4.2 Очистка неподтвержденных профилей пользователей

Таблица регистрации студентов на портале содержит записи о заявках:

CREATE TABLE portal_registration_requests (
  request_id SERIAL PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  is_email_verified BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL
);

Требование: Составьте запрос DELETE FROM portal_registration_requests, который удаляет все профили, у которых email не был подтвержден (is_email_verified = FALSE) и с момента регистрации прошло более 30 дней (created_at < CURRENT_TIMESTAMP - INTERVAL '30 days'). Запрос должен возвращать через RETURNING список удаленных email для логирования.

Задание 4.3 Аналитический вопрос

Сравните физические последствия выполнения DELETE FROM student_grades; и TRUNCATE TABLE student_grades; с точки зрения сброса счетчика автоинкремента grade_id и блокировок других пользователей портала.