Базы данных | Задания для практического занятия 8

Создание запросов с использованием стандартных функций в PostgreSQL.

---

Вариант 1: Система Управления Библиотекой

Сценарий: Вы управляете базой данных для местной библиотеки. Вам нужно выполнить различные запросы, связанные с читателями, книгами и выдачами.

Схема Базы Данных:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS loans;
-- DROP TABLE IF EXISTS books;
-- DROP TABLE IF EXISTS members;

-- 1. Create the 'members' table
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,      -- For LOWER() and UNIQUE practice
    join_date DATE NOT NULL,
    membership_level VARCHAR(10) DEFAULT 'Bronze', -- For CASE or NULLIF
    postal_code VARCHAR(10)        -- Might have extra spaces, for TRIM()
);

-- 2. Create the 'books' table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100),
    isbn VARCHAR(20) UNIQUE,          -- May have hyphens/spaces, for REPLACE()
    publication_year INTEGER,       -- For EXTRACT() or CAST()
    list_price NUMERIC(7, 2)         -- For ROUND(), CEIL(), FLOOR(), ABS()
);

-- 3. Create the 'loans' table
CREATE TABLE loans (
    loan_id SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES books(book_id),
    member_id INTEGER REFERENCES members(member_id),
    loan_date DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date DATE NOT NULL,
    return_date DATE                -- NULL if not returned, for COALESCE(), AGE()
);

-- Sample Data Insertion --

INSERT INTO members (first_name, last_name, email, join_date, membership_level, postal_code) VALUES
('Arthur', 'Dent', 'a.dent@galaxy.net', '2021-03-15', 'Silver', ' SW1A0AA'),
('Ford', 'Prefect', 'ford.p@betelgeuse.com', '2021-03-15', 'Gold', 'BG5-XQ1 '),
('Zaphod', 'Beeblebrox', 'z.beeblebrox@president.gov', '2022-11-01', NULL, 'HHGTTG'), -- NULL level
('Trillian', 'Astra', 'trillian@heartofgold.spc', '2020-08-25', 'Gold', ' E1 6AN'),
('Marvin', 'Android', 'paranoid.android@sirius.cyb', '2023-01-10', 'Bronze', ' ROBOT01');

INSERT INTO books (title, author, isbn, publication_year, list_price) VALUES
('The Hitchhiker''s Guide', 'Douglas Adams', '0-345-39180-2', 1979, 15.99),
('Dirk Gently''s Holistic Detective Agency', 'Douglas Adams', ' 0-671-69464-4', 1987, 18.50), -- Leading space ISBN
('Good Omens', 'Terry Pratchett & Neil Gaiman', '0-575-04800-X ', 1990, 22.00), -- Trailing space ISBN
('Stardust', 'Neil Gaiman', '0-380-97728-1', 1999, 14.75),
('Hyperion', 'Dan Simmons', '978-0553283686', 1989, 19.95);

INSERT INTO loans (book_id, member_id, loan_date, due_date, return_date) VALUES
(1, 1, '2023-10-01', '2023-10-15', '2023-10-14'),
(2, 2, '2023-10-05', '2023-10-26', NULL), -- Overdue
(3, 1, '2023-10-10', '2023-10-31', NULL), -- Not yet due (assume today is Oct 26th)
(4, 3, '2023-09-15', '2023-10-06', '2023-10-10'), -- Returned late
(5, 4, '2023-10-20', '2023-11-10', NULL),
(1, 5, '2023-08-01', '2023-08-22', NULL); -- Very overdue

Задачи для Варианта 1:

1. Полные Имена Читателей: Выберите member_id и сгенерируйте строку с полным именем для каждого читателя, объединив их first_name и last_name, разделенные пробелом. Назовите этот столбец full_name.
2. Очищенные ISBN: Отобразите title и isbn для всех книг. Очистите isbn, удалив как начальные/конечные пробелы, так и любые дефисы (‘-‘). Назовите очищенный столбец cleaned_isbn. (Подсказка: Вам могут понадобиться TRIM и REPLACE).
3. Продолжительность Членства: Вычислите, как долго каждый читатель является частью библиотеки по состоянию на CURRENT_DATE. Отобразите first_name читателя, join_date и вычисленную продолжительность, используя AGE(). Назовите столбец продолжительности membership_duration.
4. Статус Выдачи: Для всех выдач отобразите loan_id, due_date и вычисленный status. Статус должен быть ‘Overdue’, если return_date имеет значение NULL И due_date раньше CURRENT_DATE. Статус должен быть ‘Returned’, если return_date не NULL. В противном случае статус должен быть ‘On Loan’. (Подсказка: Используйте CASE и проверьте на NULL).
5. Категории Цен Книг: Покажите title, list_price и price_category для каждой книги. Разделите на категории как ‘Budget’, если цена < 15,00 долларов США, ‘Standard’, если цена находится в диапазоне от 15,00 до 19,99 долларов США (включительно), и ‘Premium’, если 20,00 долларов США или больше.
6. Нижний Регистр Email Читателя: Отобразите first_name, last_name и email адрес для всех читателей, убедившись, что адрес электронной почты полностью в нижнем регистре. Назовите столбец email в нижнем регистре lower_email.
7. Сокращение Названия Книги: Покажите book_id и первые 15 символов title каждой книги. Если название длиннее 15 символов, добавьте ‘…’. Назовите это short_title. (Подсказка: Используйте SUBSTRING и, возможно, LENGTH и CONCAT/||).
8. Период Выдачи: Вычислите количество дней между loan_date и due_date для каждой выдачи. Отобразите loan_id и вычисленное количество дней как loan_period_days. (Подсказка: Вычитание дат дает целое число дней).
9. Цена Округлена Вверх: Отобразите title и list_price для каждой книги вместе с ценой, округленной ВВЕРХ до ближайшей целой суммы в долларах. Назовите это price_ceil.
10. Отображение Уровня Членства: Покажите member_id и first_name. Также отобразите membership_level. Если membership_level имеет значение NULL, отобразите ‘Standard’ вместо этого. Назовите этот столбец effective_level. (Подсказка: Используйте COALESCE).
11. Извлечение Квартала Присоединения: Отобразите first_name и join_date для каждого читателя. Также извлеките календарный квартал (1, 2, 3 или 4) из их join_date. Назовите это join_quarter. (Подсказка: Используйте EXTRACT(QUARTER FROM ...)).
12. Очистка Почтового Индекса: Отобразите member_id и postal_code для каждого читателя, удалив любые начальные или конечные пробелы. Назовите очищенный столбец trimmed_postal_code.
13. Абсолютная Разница Цен: Вычислите абсолютную разницу между list_price каждой книги и целевой ценой в 18,00 долларов США. Отобразите title, list_price и вычисленную absolute_difference.
14. Проверка Числового ISBN (Концептуально): Определите книги, у которых cleaned_isbn (из логики Задачи 2) содержит нечисловые символы (исключая потенциальный ‘X’ в конце, обычный для ISBN-10). Это более сложно; для этого задания просто проверьте, приводит ли REPLACE(TRIM(isbn), '-', '') к NULL при попытке преобразования к типу NUMERIC (используйте NULLIF в сочетании с проверкой или более продвинутой техникой, если она известна, в противном случае опишите логику). Для более простой версии: верните NULL с помощью NULLIF, если publication_year равен ровно 1990. Назовите это null_if_1990_pub.
15. Заполнение ID Читателя: Отобразите member_id для всех читателей, отформатированный как 5-значную строку с ведущими нулями (например, 1 становится ‘00001’). Назовите это formatted_member_id. (Подсказка: Используйте LPAD после приведения типа).

---

Вариант 2: Запись на курсы в университете

Описание: Вы управляете базой данных для записи на курсы в университете, отслеживая студентов, курсы и их оценки.

Схема базы данных:

-- Drop tables if they exist (optional)
-- Удалить таблицы, если они существуют (необязательно)
-- DROP TABLE IF EXISTS enrollments;
-- DROP TABLE IF EXISTS courses;
-- DROP TABLE IF EXISTS students;

-- 1. Create the 'students' table
-- 1. Создать таблицу 'students' (студенты)
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),         -- Allows NULLs, for COALESCE
    -- Допускает NULL, для COALESCE
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL, -- For LOWER(), UNIQUE
    -- Для LOWER(), UNIQUE
    date_of_birth DATE,              -- For AGE(), EXTRACT()
    -- Для AGE(), EXTRACT()
    major VARCHAR(50)                -- For CASE, NULLIF
    -- Для CASE, NULLIF
);

-- 2. Create the 'courses' table
-- 2. Создать таблицу 'courses' (курсы)
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_code VARCHAR(15) UNIQUE NOT NULL, -- e.g., ' CS 101 ', for TRIM()
    -- например, ' CS 101 ', для TRIM()
    course_name VARCHAR(100) NOT NULL,    -- For UPPER(), SUBSTRING()
    -- Для UPPER(), SUBSTRING()
    credits NUMERIC(3, 1),                -- e.g., 3.0, 1.5. For ROUND(), ABS()
    -- например, 3.0, 1.5. Для ROUND(), ABS()
    department VARCHAR(50)                -- For general queries
    -- Для общих запросов
);

-- 3. Create the 'enrollments' table
-- 3. Создать таблицу 'enrollments' (записи на курсы)
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    enrollment_date DATE DEFAULT CURRENT_DATE, -- For EXTRACT(), INTERVAL
    -- Для EXTRACT(), INTERVAL
    grade NUMERIC(4, 1)                        -- e.g. 85.5, 92.0. Allows NULL if not graded. For CEIL(), FLOOR(), CASE
    -- например, 85.5, 92.0. Допускает NULL, если оценка не выставлена. Для CEIL(), FLOOR(), CASE
);

-- Sample Data Insertion --
-- Вставка образцовых данных --

INSERT INTO students (first_name, middle_name, last_name, email, date_of_birth, major) VALUES
('Alice', 'Marie', 'Wonder', 'alice.wonder@uni.edu', '2003-04-10', 'Literature'),
('Bob', NULL, 'Builder', 'bob.the.builder@uni.edu', '2002-08-20', 'Engineering'),
('Charlie', 'Chaplin', 'Jr', 'charlie.jr@uni.edu', '2004-01-05', NULL), -- NULL Major
-- NULL специальность
('Dorothy', 'Gale', 'Oz', 'dorothy.oz@uni.edu', '2003-11-11', 'Meteorology'),
('Eve', 'Ada', 'Lovelace', 'eve.lovelace@uni.edu', '2002-12-15', 'Computer Science');

INSERT INTO courses (course_code, course_name, credits, department) VALUES
(' CS 101 ', 'Introduction to Programming', 3.0, 'Computer Science'), -- Spaces in code
-- Пробелы в коде
('LIT 205', 'Modernist Poetry', 3.0, 'Literature'),
(' ENG 310', 'Structural Analysis', 4.0, 'Engineering'), -- Leading space
-- Пробел в начале
('MATH 150 ', 'Calculus I', 4.5, 'Mathematics'), -- Trailing space
-- Пробел в конце
('CS 336', 'Database Systems', 3.0, 'Computer Science');

INSERT INTO enrollments (student_id, course_id, enrollment_date, grade) VALUES
(1, 2, '2023-09-05', 88.5),
(2, 3, '2023-09-05', 92.0),
(1, 4, '2023-09-06', 75.0),
(3, 1, '2023-09-07', NULL), -- Not graded yet
-- Еще не выставлена оценка
(4, 4, '2023-09-06', 65.5),
(5, 1, '2023-09-07', 95.0),
(5, 5, '2023-09-08', NULL); -- Not graded yet
-- Еще не выставлена оценка

Задачи для Варианта 2:

1. Инициалы студента и полное имя: Отобразите student_id. Создайте full_name (Имя Фамилия). Создайте строку initials, используя первую букву first_name и первую букву last_name в верхнем регистре (например, ‘AW’). Если middle_name существует, отобразите его, используя COALESCE (покажите ‘N/A’, если NULL), в противном случае пропустите отображение middle_name для этой задачи.
2. Очищенные коды курсов: Покажите course_name и course_code после удаления любых начальных или конечных пробелов. Присвойте очищенному коду псевдоним clean_code.
3. Расчет возраста студента: Рассчитайте текущий возраст каждого студента на основе их date_of_birth на CURRENT_DATE. Отобразите first_name, last_name, date_of_birth и рассчитанный age.
4. Статус оценки за курс: Отобразите enrollment_id, student_id, course_id и рассчитанный grade_status. Статус должен быть ‘Passed’, если grade >= 60, ‘Failed’, если grade < 60, и ‘Pending’, если grade равен NULL.
5. Категории кредитов курса: Отобразите course_name, credits и credit_category. Классифицируйте как ‘Low’, если credits < 3.0, ‘Standard’, если credits равны 3.0 или 3.5, и ‘High’, если credits > 3.5.
6. Стандартизированные электронные письма студентов: Покажите student_id, first_name и email, убедившись, что email полностью в нижнем регистре. Присвойте этому псевдоним standard_email.
7. Аббревиатура названия курса: Отобразите course_id и первые 20 символов course_name. Присвойте этому псевдоним short_course_name.
8. Семестр зачисления: Отобразите enrollment_id и enrollment_date. Определите семестр зачисления на основе месяца: Месяц 1-5 = ‘Spring’, Месяц 6-8 = ‘Summer’, Месяц 9-12 = ‘Fall’. Присвойте этому псевдоним enrollment_semester. (Подсказка: используйте EXTRACT и CASE).
9. Оценка, округленная в меньшую сторону: Отобразите enrollment_id и grade. Также покажите grade, округленную ВНИЗ до ближайшего целого числа. Присвойте этому псевдоним grade_floor. Обработайте случаи, когда оценка может быть NULL (она, вероятно, должна оставаться NULL).
10. Отображение специальности: Отобразите student_id и first_name. Используйте COALESCE, чтобы показать major студента; если major равен NULL, отобразите ‘Undeclared’. Присвойте этому псевдоним declared_major.
11. Лет с года рождения: Извлеките год рождения каждого студента. Рассчитайте количество полных лет, прошедших между годом их рождения и текущим годом. Отобразите first_name, date_of_birth и years_passed. (Подсказка: EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM date_of_birth)).
12. Абсолютная разница оценки от среднего: Рассчитайте абсолютную разницу между оценкой каждого студента по ‘Calculus I’ (Course ID 4) и средней оценкой по этому курсу (предположим, что среднее значение составляет 70.0 для этой задачи). Отобразите student_id, grade и abs_diff_from_avg. Включите только записи для Course ID 4 с ненулевыми оценками.
13. Поиск нулевых специальностей: Используйте NULLIF, чтобы вернуть NULL, если специальность студента - ‘Literature’. В противном случае верните специальность. Отобразите student_id, first_name, major и результат как null_if_literature.
14. Проверка четности идентификатора студента: Определите, является ли student_id нечетным или четным. Отобразите student_id и ‘Even’ или ‘Odd’ как id_parity. (Подсказка: используйте MOD или %).
15. Заполнение кода курса: Отобразите course_id и clean_code (из логики задачи 2). Создайте padded_code, заполнив clean_code справа пробелами до общей длины 10 символов. (Подсказка: используйте RPAD).

---

Вариант 3: Управление Проектами и Задачами

Сценарий: Вы управляете базой данных для отслеживания проектов, задач, назначенных сотрудникам, и их прогресса.

Схема Базы Данных:

-- Удалить таблицы, если они существуют (опционально)
-- DROP TABLE IF EXISTS tasks;
-- DROP TABLE IF EXISTS projects;
-- DROP TABLE IF EXISTS employees;

-- 1. Создать таблицу 'employees' (сотрудники)
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    emp_code VARCHAR(10) UNIQUE,     -- Например, 'EMP001', для SUBSTRING, CAST
    hire_date DATE NOT NULL,         -- Для AGE(), EXTRACT()
    hourly_rate NUMERIC(6, 2),       -- Для ROUND(), числовых операций
    department VARCHAR(50)           -- Для общих запросов, NULLIF
);

-- 2. Создать таблицу 'projects' (проекты)
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL UNIQUE, -- Для UPPER(), LENGTH()
    client_name VARCHAR(100),
    start_date DATE NOT NULL,
    deadline_date DATE,              -- Допускает NULL, для COALESCE, CASE
    budget NUMERIC(12, 2)            -- Для ABS(), CEIL()
);

-- 3. Создать таблицу 'tasks' (задачи)
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INTEGER REFERENCES projects(project_id),
    assigned_to_emp_id INTEGER REFERENCES employees(emp_id), -- Допускает NULL
    task_name VARCHAR(255) NOT NULL,
    estimated_hours NUMERIC(5, 1),     -- Например, 8.0, 2.5. Для ROUND()
    creation_date TIMESTAMP DEFAULT NOW(), -- Для функций даты/времени
    completion_date DATE               -- NULL, если не завершена. Для CASE, AGE()
);

-- Вставка Примерных Данных --

INSERT INTO employees (first_name, last_name, emp_code, hire_date, hourly_rate, department) VALUES
('Peter', 'Gibbons', 'EMP001', '2019-02-15', 35.00, 'Software'),
('Michael', 'Bolton', 'EMP002', '2018-07-01', 40.50, NULL), -- NULL Dept
('Samir', 'Nagheenanajar', 'EMP003', '2019-02-15', 38.75, 'Software'),
('Joanna', 'Planter', 'EMP004', '2020-11-20', 45.00, 'Management'),
('Bill', 'Lumbergh', 'EMP005', '2015-03-10', 75.00, 'Management');

INSERT INTO projects (project_name, client_name, start_date, deadline_date, budget) VALUES
('TPS Reports Automation', 'Initech', '2023-01-10', '2023-06-30', 50000.00),
('Website Redesign', 'Globex Corp', '2023-03-01', '2023-09-15', 75000.50),
('New Feature X', 'Initech', '2023-05-15', NULL, 30000.00), -- NULL Deadline
('Server Migration', 'Stark Industries', '2023-08-01', '2023-12-20', 120000.00);

INSERT INTO tasks (project_id, assigned_to_emp_id, task_name, estimated_hours, creation_date, completion_date) VALUES
(1, 1, 'Analyze current TPS report process', 8.0, '2023-01-12 10:00:00', '2023-01-20'),
(1, 3, 'Develop script for data extraction', 24.5, '2023-02-01 11:30:00', '2023-03-15'),
(2, 4, 'Create wireframes for new website', 16.0, '2023-03-05 09:00:00', NULL), -- Не завершена
(2, NULL, 'Content population', 40.0, '2023-06-01 14:00:00', NULL), -- Не назначена
(3, 1, 'Define requirements for Feature X', 4.0, '2023-05-16 15:00:00', '2023-05-25'),
(4, 5, 'Plan migration strategy', 12.0, '2023-08-02 16:20:00', NULL),
(1, 5, 'Oversee TPS report testing', 6.5, '2023-04-01 08:00:00', '2023-06-10');

Задачи для Варианта 3:

1. Полное Имя и Код Сотрудника: Отобразите emp_id. Объедините first_name и last_name в full_name. Также отобразите emp_code.
2. Название Проекта в Верхнем Регистре: Покажите project_id и project_name полностью в верхнем регистре, под псевдонимом upper_project_name.
3. Стаж Сотрудника: Вычислите продолжительность работы каждого сотрудника в компании на CURRENT_DATE. Отобразите emp_code, hire_date и вычисленный tenure, используя AGE().
4. Определение Статуса Задачи: Отобразите task_id, task_name, deadline_date (из соответствующего проекта - требуется JOIN, предположим, что вы можете посмотреть срок проекта вручную или просто использовать completion_date задачи). Предоставьте status: ‘Completed’, если completion_date NOT NULL, ‘Overdue’, если completion_date IS NULL И deadline_date проекта раньше CURRENT_DATE (если срок существует), ‘In Progress’ в противном случае. (Упрощено для задания: Используйте только completion_date. ‘Completed’, если NOT NULL, ‘Pending’, если NULL).
5. Категории Бюджета Проекта: Отобразите project_name, budget. Категоризируйте проекты на основе budget: ‘Small’, если budget < 40000, ‘Medium’, если budget находится между 40000 и 80000 (включительно), ‘Large’, если budget > 80000. Используйте псевдоним budget_category.
6. Оценка Времени в Часах Округлена: Покажите task_id, task_name и estimated_hours. Также отобразите estimated_hours, округленное до ближайшего целого числа. Используйте псевдоним rounded_hours.
7. Числовая Часть Кода Сотрудника: Извлеките числовую часть из emp_code (предполагая формат ‘EMP’, за которым следуют цифры). Отобразите emp_id, emp_code и извлеченное число как emp_number. (Подсказка: используйте SUBSTRING и, возможно, CAST).
8. Дней До Срока Окончания Проекта: Для проектов, у которых есть deadline_date, вычислите количество дней, оставшихся от CURRENT_DATE до крайнего срока. Отобразите project_name, deadline_date и days_remaining. Обработайте случаи, когда срок может быть в прошлом (результат будет отрицательным).
9. Бюджет Округлен Вверх: Отобразите project_name и budget. Также покажите budget, округленный ВВЕРХ до ближайшей 1000 долларов. Используйте псевдоним budget_ceil_k. (Подсказка: используйте CEIL осторожно, возможно, разделите, округлите вверх, затем умножьте, или используйте ROUND с соответствующей отрицательной точностью, если цель - ближайшая 1000, используйте CEIL, если строго ВВЕРХ). Будем придерживаться CEIL для концепции обучения: отобразите CEIL(budget) как budget_ceil_dollar.
10. Отображение Назначенного Сотрудника: Отобразите task_id и task_name. Используйте COALESCE, чтобы показать assigned_to_emp_id. Если он NULL, отобразите 0 вместо этого. Используйте псевдоним assigned_emp.
11. Месяц и Год Начала Проекта: Извлеките месяц и год из start_date для каждого проекта. Отобразите project_name, start_date, start_month и start_year.
12. Абсолютное Отклонение Бюджета: Вычислите абсолютную разницу между budget каждого проекта и стандартным бюджетом в размере 60 000 долларов. Отобразите project_name, budget и abs_budget_deviation.
13. Час Создания Задачи: Извлеките час (0-23) из отметки времени creation_date для каждой задачи. Отобразите task_id, creation_date и creation_hour.
14. Найти Сотрудников Не-Программистов: Используйте NULLIF для возврата NULL, если отдел сотрудника - ‘Software’. В противном случае верните отдел. Отобразите emp_id, first_name, department и результат как null_if_software.
15. Форматирование Почасовой Ставки: Отобразите emp_id, first_name и hourly_rate. Создайте новый строковый столбец с именем formatted_rate, который показывает hourly_rate с префиксом знака доллара (‘$’). (Подсказка: Используйте оператор конкатенации || и приведите hourly_rate к VARCHAR).

---

Вариант 4: Система заказов для электронной коммерции

Сценарий: Вы управляете базой данных для интернет-магазина. Вам нужно запрашивать информацию о клиентах, сведения о товарах, заказах и элементах в этих заказах.

Схема базы данных:

-- Удалить таблицы, если они существуют (необязательно)
-- DROP TABLE IF EXISTS order_items;
-- DROP TABLE IF EXISTS orders;
-- DROP TABLE IF EXISTS products;
-- DROP TABLE IF EXISTS customers;

-- 1. Создать таблицу 'customers'
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,   -- Для LOWER(), UNIQUE
    registration_date DATE NOT NULL DEFAULT CURRENT_DATE, -- Для AGE(), EXTRACT()
    address_line1 VARCHAR(255),
    city VARCHAR(100),
    postal_code VARCHAR(20)             -- Может содержать пробелы, для TRIM()
);

-- 2. Создать таблицу 'products' (отличается от структуры в учебнике)
CREATE TABLE products (
    product_sku VARCHAR(50) PRIMARY KEY, -- Например, 'ELEC-LAP-1001', 'BOOK-FIC-205A'
    product_name VARCHAR(150) NOT NULL, -- Для SUBSTRING(), UPPER()
    description TEXT,                    -- Для LENGTH() (на фрагменте)
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price > 0), -- Для ROUND(), CEIL(), FLOOR(), ABS()
    category VARCHAR(50),                -- Для CASE, NULLIF
    stock_added_date DATE                -- Для вычисления INTERVAL
);

-- 3. Создать таблицу 'orders'
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(), -- Для EXTRACT(), AGE()
    shipping_address VARCHAR(500),             -- Может отличаться от адреса клиента, возможно NULL
    order_status VARCHAR(20) DEFAULT 'Pending', -- Например, 'Pending', 'Shipped', 'Delivered', 'Cancelled'
    -- total_amount NUMERIC(12, 2) -- Обычно вычисляется, но может храниться
    discount_code VARCHAR(15) NULL            -- Для COALESCE
);

-- 4. Создать таблицу 'order_items' (связывает заказы и товары)
CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    product_sku VARCHAR(50) NOT NULL REFERENCES products(product_sku),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_per_unit NUMERIC(10, 2) NOT NULL -- Цена на момент заказа
);

-- Вставка примерных данных --

INSERT INTO customers (first_name, last_name, email, registration_date, postal_code) VALUES
('Jane', 'Doe', 'jane.d@email.com', '2022-01-15', ' 12345 '),
('John', 'Smith', 'J.Smith@email.com', '2021-11-30', '98765'),
('Peter', 'Jones', 'peterjones@email.com', '2023-05-20', ' SW1A 0AA'),
('Mary', 'Brown', 'mary.b@email.com', '2022-01-15', NULL); -- NULL postal code

INSERT INTO products (product_sku, product_name, description, unit_price, category, stock_added_date) VALUES
('ELEC-LAP-1001', 'UltraBook X1', 'A thin and light laptop.', 1299.99, 'Electronics', '2023-01-10'),
('BOOK-FIC-205A', 'The Forgotten Realm', 'A fantasy novel.', 19.95, 'Books', '2023-02-15'),
('HOME-KIT-050', 'Smart Coffee Maker', 'Brews coffee remotely.', 89.50, 'Home Goods', '2023-03-01'),
('ELEC-CAM-300', 'Action Camera Pro', NULL, 249.00, 'Electronics', '2023-01-10'), -- NULL description
('BOOK-NF-110B', 'History of Computing', 'Covers major milestones.', 35.00, 'Books', '2022-12-01');

INSERT INTO orders (customer_id, order_date, order_status, discount_code) VALUES
(1, '2023-10-01 10:30:00', 'Shipped', 'FALL10'),
(2, '2023-10-15 14:00:00', 'Pending', NULL),
(1, '2023-10-20 09:00:00', 'Pending', NULL),
(3, '2023-08-10 11:00:00', 'Delivered', NULL),
(4, '2023-10-25 16:00:00', 'Pending', 'NEWCUST5');

INSERT INTO order_items (order_id, product_sku, quantity, price_per_unit) VALUES
(1, 'ELEC-LAP-1001', 1, 1299.99),
(1, 'HOME-KIT-050', 1, 85.00), -- Цена немного отличается на момент заказа
(2, 'BOOK-FIC-205A', 2, 19.95),
(3, 'HOME-KIT-050', 1, 89.50),
(4, 'BOOK-NF-110B', 1, 35.00),
(5, 'ELEC-CAM-300', 1, 249.00);

Задачи для варианта 4:

1. Полное имя клиента: Выбрать customer_id и отобразить полное имя клиента, объединив first_name и last_name с пробелом между ними. Дать результату псевдоним full_name.
2. Стандартизированные адреса электронной почты: Отобразить customer_id и email для всех клиентов, убедившись, что email полностью в нижнем регистре. Дать псевдоним standard_email.
3. Продолжительность членства клиента: Вычислить, как долго зарегистрирован каждый клиент (с registration_date) по состоянию на CURRENT_DATE. Отобразить customer_id, registration_date и продолжительность, используя AGE(), с псевдонимом membership_duration.
4. Очищенные почтовые индексы: Отобразить customer_id и postal_code. Если postal_code не NULL, отобразить его после удаления любых начальных или конечных пробелов; в противном случае отобразить ‘N/A’. Дать этому псевдоним cleaned_postal_code. (Подсказка: используйте TRIM и COALESCE).
5. Анализ артикула товара: Для каждого товара извлечь часть категории (до первого ‘-‘) и часть основного кода (между первым и вторым ‘-‘). Отобразить product_sku, извлеченную sku_category и sku_core. (Подсказка: используйте SUBSTRING и, возможно, POSITION или фиксированную длину, если шаблон является согласованным). Упрощено для учебника: Предположим, что первая часть состоит из 4 символов, вторая - из 3. Извлеките SUBSTRING(product_sku FROM 1 FOR 4) как sku_category_prefix и SUBSTRING(product_sku FROM 6 FOR 3) как sku_type_prefix.
6. Ценовые категории товаров: Отобразить product_name, unit_price и вычисленный price_tier. Уровень должен быть ‘Budget’, если цена < 50 долларов США, ‘Mid-Range’, если цена от 50 до 250 долларов США (включительно), и ‘Premium’, если цена > 250 долларов США. Используйте оператор CASE.
7. Общая стоимость позиции заказа: Для каждой позиции в order_items вычислить общую стоимость (quantity * price_per_unit). Отобразить item_id, order_id, product_sku и вычисленный item_total.
8. Дней с момента заказа: Для каждого заказа вычислить количество полных дней, прошедших с order_date (для простоты игнорировать часть времени). Отобразить order_id, order_date и days_passed. (Подсказка: CURRENT_DATE - CAST(order_date AS DATE)).
9. Округленная цена за единицу: Отобразить product_name и unit_price, а также unit_price, округленную до ближайшего целого доллара. Дать этому псевдоним rounded_price.
10. Минимальная стоимость доставки: Отобразить product_name и unit_price. Вычислить гипотетическую минимальную стоимость доставки, округлив unit_price ВВЕРХ до ближайшего доллара (CEIL). Дать этому псевдоним min_ship_charge_base.
11. Отображение статуса заказа: Отобразить order_id и order_status. Создать столбец is_processing, который показывает TRUE, если order_status имеет значение ‘Pending’, и FALSE в противном случае. (Подсказка: используйте CASE или логическое выражение).
12. Час размещения заказа: Извлечь час (0-23) из order_date для каждого заказа. Отобразить order_id, order_date и order_hour.
13. Абсолютная разница в цене от 100 долларов США: Для каждого товара вычислить абсолютную разницу между его unit_price и 100 долларами США. Отобразить product_name, unit_price и abs_diff_from_100.
14. Отформатированная строка цены товара: Отобразить product_name и unit_price. Создать строковое представление цены с префиксом ‘$’. Дать этому псевдоним formatted_price. (например, ‘$1299.99’). Используйте '$' || unit_price::VARCHAR.
15. Заполненный нулями ID заказа: Отобразить order_id в формате 10-значной строки с ведущими нулями (например, 1 становится ‘0000000001’). Дать этому псевдоним formatted_order_id. Используйте LPAD.

---

Вариант 5: Система управления мероприятиями

Сценарий: Вы управляете базой данных компании по организации мероприятий, отслеживая мероприятия, места проведения, регистрации участников и платежи.

Схема базы данных:

-- Удалить таблицы, если они существуют (необязательно)
-- DROP TABLE IF EXISTS registrations;
-- DROP TABLE IF EXISTS attendees;
-- DROP TABLE IF EXISTS events;
-- DROP TABLE IF EXISTS venues;

-- 1. Создать таблицу 'venues'
CREATE TABLE venues (
    venue_id SERIAL PRIMARY KEY,
    venue_name VARCHAR(100) NOT NULL UNIQUE, -- Для UPPER(), LENGTH()
    address VARCHAR(255),
    city VARCHAR(100),
    capacity INTEGER CHECK (capacity > 0),     -- Для MOD(), числовых операций
    booking_cost NUMERIC(8, 2),            -- Для ROUND(), CEIL(), ABS()
    contact_phone VARCHAR(20)              -- Например, ' 123-456-7890 ', для TRIM(), REPLACE()
);

-- 2. Создать таблицу 'events'
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(150) NOT NULL,       -- Для SUBSTRING()
    venue_id INTEGER REFERENCES venues(venue_id),
    event_type VARCHAR(50),                 -- Например, 'Conference', 'Workshop', 'Webinar', 'Gala'
    start_datetime TIMESTAMP NOT NULL,      -- Для EXTRACT(), AGE(), INTERVAL
    end_datetime TIMESTAMP,                 -- Может быть NULL (например, однодневное мероприятие), проверить продолжительность начала/окончания
    base_ticket_price NUMERIC(7, 2) DEFAULT 0.00 -- Для CASE, NULLIF
);

-- 3. Создать таблицу 'attendees'
CREATE TABLE attendees (
    attendee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,   -- Для LOWER()
    signup_date DATE DEFAULT CURRENT_DATE,  -- Для AGE()
    company_name VARCHAR(100)             -- Допускает NULL, для COALESCE
);

-- 4. Создать таблицу 'registrations'
CREATE TABLE registrations (
    registration_id SERIAL PRIMARY KEY,
    event_id INTEGER NOT NULL REFERENCES events(event_id),
    attendee_id INTEGER NOT NULL REFERENCES attendees(attendee_id),
    registration_time TIMESTAMP DEFAULT NOW(), -- Для EXTRACT(), AGE()
    ticket_type VARCHAR(20) DEFAULT 'Standard', -- Например, 'Standard', 'VIP', 'EarlyBird'
    final_price NUMERIC(7, 2),                 -- Оплаченная цена, может отличаться от базовой цены
    payment_status VARCHAR(15)                 -- Например, 'Paid', 'Pending', 'Refunded', NULL
);

-- Пример вставки данных --

INSERT INTO venues (venue_name, capacity, booking_cost, contact_phone) VALUES
('Grand Hall', 500, 5000.00, ' 111-222-3333 '),
('Convention Center East Wing', 1500, 12000.50, '444-555-6666'),
('Cozy Corner Room', 50, 800.75, NULL), -- NULL phone
('Online Platform', 10000, 200.00, ' N/A ');

INSERT INTO events (event_name, venue_id, event_type, start_datetime, end_datetime, base_ticket_price) VALUES
('Annual Tech Conference 2024', 2, 'Conference', '2024-09-10 09:00:00', '2024-09-12 17:00:00', 499.00),
('Data Science Workshop', 1, 'Workshop', '2024-07-15 10:00:00', '2024-07-15 16:30:00', 150.00),
('Introduction to SQL Webinar', 4, 'Webinar', '2024-05-20 14:00:00', NULL, 0.00), -- Бесплатное мероприятие, NULL время окончания
('Charity Gala Dinner', 1, 'Gala', '2024-11-01 19:00:00', '2024-11-01 23:00:00', 250.50);

INSERT INTO attendees (first_name, last_name, email, company_name) VALUES
('Alice', 'Wonder', 'alice.w@example.com', 'Curious Inc.'),
('Bob', 'Marley', 'bob.m@example.com', NULL), -- NULL company
('Charlie', 'Day', 'charlie.d@example.com', 'Paddy''s Pub'),
('Diana', 'Prince', 'diana.p@example.com', 'Themyscira Exports');

INSERT INTO registrations (event_id, attendee_id, registration_time, ticket_type, final_price, payment_status) VALUES
(1, 1, '2024-03-15 10:00:00', 'EarlyBird', 399.00, 'Paid'),
(1, 2, '2024-04-01 11:30:00', 'Standard', 499.00, 'Paid'),
(2, 3, '2024-06-10 16:00:00', 'Standard', 150.00, 'Pending'),
(3, 1, '2024-05-01 09:00:00', 'Standard', 0.00, 'Paid'), -- Бесплатное мероприятие
(4, 4, '2024-09-01 14:20:00', 'VIP', 350.00, 'Paid'),
(2, 4, '2024-07-01 13:00:00', 'Standard', 150.00, NULL); -- NULL статус платежа

Задачи для варианта 5:

1. Полное имя участника: Выбрать attendee_id и отобразить полное имя участника, объединив first_name и last_name с пробелом. Задать псевдоним результату как full_name.
2. Очищенный телефон площадки: Отобразить venue_name и contact_phone. Показать contact_phone после удаления всех пробелов и дефисов. Если телефон NULL или ‘ N/A ‘, отобразить ‘Not Available’. Задать псевдоним этому как cleaned_phone. (Подсказка: Используйте REPLACE, TRIM, COALESCE).
3. Продолжительность мероприятия: Вычислить продолжительность каждого мероприятия (end_datetime - start_datetime). Если end_datetime имеет значение NULL, отобразить NULL. Отобразить event_name, start_datetime, end_datetime и вычисленную duration (как INTERVAL).
4. Время с момента регистрации: Для каждой регистрации вычислить, сколько времени прошло с момента ее регистрации (registration_time) относительно NOW(). Отобразить registration_id, registration_time и продолжительность, используя AGE(), с псевдонимом registration_age.
5. Категория цены мероприятия: Отобразить event_name и base_ticket_price. Создать price_category, используя CASE: ‘Free’, если цена равна 0, ‘Affordable’, если цена > 0 и <= 100, ‘Standard’, если цена > 100 и <= 300, ‘Premium’, если цена > 300.
6. Стандартизированные электронные письма участников: Отобразить attendee_id, first_name и email, убедившись, что email полностью в нижнем регистре. Задать псевдоним этому как standard_email.
7. Проверка вместимости площадки: Отобразить venue_name и capacity. Добавить столбец is_large_venue, который имеет значение TRUE, если capacity больше 1000, и FALSE в противном случае.
8. День начала мероприятия: Извлечь день недели (например, ‘Monday’, ‘Tuesday’) из start_datetime для каждого мероприятия. Отобразить event_name, start_datetime и start_day. (Подсказка: Используйте TO_CHAR с EXTRACT(DOW FROM ...) может понадобиться для названия, или просто покажите номер DOW из EXTRACT). Давайте придерживаться номера: EXTRACT(DOW FROM start_datetime) AS start_dow (0=Вс, 6=Сб в PG).
9. Стоимость площадки округленная вверх: Отобразить venue_name и booking_cost. Вычислить стоимость, округленную ВВЕРХ до ближайших $100. Задать псевдоним этому как cost_ceil_100. (Подсказка: CEIL(booking_cost / 100.0) * 100).
10. Отображение компании участника: Отобразить attendee_id, first_name. Использовать COALESCE, чтобы показать company_name; если оно NULL, отобразить ‘Individual Attendee’. Задать псевдоним этому как affiliation.
11. Абсолютная разница цен от базовой: Для регистраций вычислить абсолютную разницу между final_price и base_ticket_price мероприятия (требуется объединение registrations и events). Отобразить registration_id, base_ticket_price, final_price и price_difference.
12. Определить ожидающие платежи: Использовать NULLIF, чтобы вернуть registration_id только в том случае, если payment_status НЕ равен ‘Paid’. В противном случае вернуть NULL. Отобразить registration_id, payment_status и результат как unpaid_registration_id.
13. Короткое название мероприятия: Отобразить event_id и первые 30 символов event_name. Задать псевдоним этому как short_event_name. Использовать SUBSTRING.
14. Дополненный ID площадки: Отобразить venue_id, отформатированный как 5-значную строку с ведущими нулями. Задать псевдоним этому как formatted_venue_id. Использовать LPAD.
15. Дней до начала мероприятия: Для мероприятий, начинающихся в будущем, вычислить количество дней от CURRENT_DATE до start_datetime. Отобразить event_name, start_datetime и days_until_start. Обработать мероприятия в прошлом (результат может быть отрицательным или отфильтровать их). Использовать start_datetime::DATE - CURRENT_DATE.

---

Вариант 6: Блог-платформа

Сценарий: Вы управляете базой данных для многопользовательской блог-платформы, отслеживая пользователей, записи (посты), комментарии и категории.

Схема базы данных:

-- Удалить таблицы, если они существуют (необязательно)
-- DROP TABLE IF EXISTS comments;
-- DROP TABLE IF EXISTS posts;
-- DROP TABLE IF EXISTS categories;
-- DROP TABLE IF EXISTS users;

-- 1. Создать таблицу 'users' (пользователи)
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(30) UNIQUE NOT NULL,   -- Для LENGTH(), LOWER()
    email VARCHAR(100) UNIQUE NOT NULL,   -- Для LOWER()
    full_name VARCHAR(100),              -- Для CONCAT (если требуется разделить) или просто для отображения
    join_date DATE NOT NULL DEFAULT CURRENT_DATE, -- Для AGE(), EXTRACT()
    profile_bio TEXT                     -- Допускает NULL, для COALESCE, LENGTH()
    -- password_hash VARCHAR(255) NOT NULL -- Не используется в задачах
);

-- 2. Создать таблицу 'categories' (категории)
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(50) UNIQUE NOT NULL, -- Для UPPER(), REPLACE()
    slug VARCHAR(60) UNIQUE                -- Например, 'data-science-basics', для LOWER(), REPLACE()
);

-- 3. Создать таблицу 'posts' (записи)
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES users(user_id),
    category_id INTEGER REFERENCES categories(category_id),
    title VARCHAR(200) NOT NULL,           -- Для SUBSTRING()
    content TEXT,                         -- Для LENGTH()
    publish_datetime TIMESTAMP,           -- Допускает NULL для черновиков. Для EXTRACT(), AGE(), INTERVAL
    last_updated TIMESTAMP DEFAULT NOW(),
    status VARCHAR(15) DEFAULT 'Draft',   -- 'Draft', 'Published', 'Archived' - Для CASE, NULLIF
    word_count INTEGER                    -- Для MOD(), CEIL(), числовых операций
);

-- 4. Создать таблицу 'comments' (комментарии)
CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(post_id),
    commenter_id INTEGER REFERENCES users(user_id), -- NULL, если комментатор анонимный/не вошел в систему
    commenter_name VARCHAR(50),              -- Используется, если commenter_id равен NULL
    comment_text TEXT NOT NULL,
    comment_datetime TIMESTAMP DEFAULT NOW(), -- Для EXTRACT(), AGE()
    parent_comment_id INTEGER REFERENCES comments(comment_id) -- Для древовидных комментариев (NULL, если верхний уровень)
);

-- Вставка образцов данных --

INSERT INTO users (username, email, full_name, join_date, profile_bio) VALUES
('john_doe', 'john.doe@blog.com', 'John Doe', '2022-01-10', 'Tech enthusiast and blogger.'),
('jane_smith', 'Jane.Smith@blog.com', 'Jane Smith', '2022-03-15', NULL), -- NULL bio
('bob_coder', 'bob@coder.net', 'Bob Coder', '2023-05-01', 'Software developer sharing tips.'),
('reader_x', 'readerX@mail.com', NULL, '2023-10-01', NULL); -- Нет полного имени, нет биографии

INSERT INTO categories (category_name, slug) VALUES
('Technology', 'technology'),
('Data Science', 'data-science'),
('Tutorials', 'tutorials'),
(' Opinion Pieces ', 'opinion-pieces'); -- Slug needs generating/cleaning

INSERT INTO posts (author_id, category_id, title, content, publish_datetime, status, word_count) VALUES
(1, 1, 'The Future of AI', 'AI is evolving rapidly...', '2023-06-01 10:00:00', 'Published', 1250),
(3, 2, 'Introduction to PostgreSQL Functions', 'This post covers several useful SQL functions...', '2023-07-15 14:30:00', 'Published', 1800),
(1, 3, 'Setting Up a Python Dev Environment', 'Step-by-step guide...', NULL, 'Draft', 950), -- Черновик, нет даты публикации
(2, 4, 'Why Static Sites Are Great', 'My thoughts on static site generators...', '2023-09-01 11:00:00', 'Published', 780),
(3, 1, 'Old Tech Thoughts', 'Some musings on older tech...', '2022-11-20 09:00:00', 'Archived', 600); -- Заархивировано

INSERT INTO comments (post_id, commenter_id, commenter_name, comment_text, comment_datetime) VALUES
(1, 2, NULL, 'Great overview!', '2023-06-01 11:00:00'),
(2, 1, NULL, 'Very helpful, thanks Bob!', '2023-07-16 09:15:00'),
(1, NULL, 'Guest User', 'I disagree with point 2.', '2023-06-02 15:00:00'), -- Анонимный комментарий
(4, 3, NULL, 'Nice points, Jane!', '2023-09-01 12:30:00'),
(2, 4, NULL, 'Where can I learn more?', '2023-10-05 10:00:00');

Задачи для варианта 6:

1. Проверка длины имени пользователя: Отобразить user_id и username. Также показать длину каждого username, указав псевдоним username_length.
2. Генерация Slug категории: Отобразить category_name. Сгенерировать потенциальный slug, преобразовав category_name в нижний регистр, удалив начальные/конечные пробелы и заменив оставшиеся пробелы дефисами. Указать псевдоним generated_slug. (Использовать LOWER, TRIM, REPLACE).
3. Возраст учетной записи пользователя: Рассчитать, как долго зарегистрирован каждый пользователь (с join_date) по состоянию на CURRENT_DATE. Отобразить username, join_date и продолжительность, используя AGE(), указав псевдоним account_age.
4. Выдержка из записи: Для каждой записи отобразить post_id, title и первые 150 символов content. Если содержимое длиннее 150 символов, добавить ‘…’. Указать псевдоним post_excerpt. (Подсказка: использовать SUBSTRING, LENGTH, CASE, CONCAT/||).
5. Стандартизированные адреса электронной почты пользователей: Отобразить user_id, username и email, убедившись, что email полностью в нижнем регистре. Указать псевдоним standard_email.
6. Возраст публикации записи: Для опубликованных записей (publish_datetime NOT NULL) рассчитать, как давно они были опубликованы относительно NOW(). Отобразить post_id, title, publish_datetime и продолжительность, используя AGE(), указав псевдоним time_since_publish.
7. Анализ времени комментария: Для каждого комментария извлечь год, месяц и час из comment_datetime. Отобразить comment_id, comment_datetime, comment_year, comment_month и comment_hour.
8. Статус видимости записи: Отобразить post_id, title и status. Создать столбец is_visible, используя CASE: TRUE, если status равен ‘Published’, FALSE в противном случае.
9. Четность количества слов в записи: Отобразить post_id, title и word_count. Определить, является ли word_count ‘Even’ (четным) или ‘Odd’ (нечетным). Указать псевдоним word_count_parity. (Подсказка: использовать MOD или %).
10. Отображение биографии пользователя: Отобразить username и profile_bio. Использовать COALESCE, чтобы отобразить ‘No bio provided’ (Биография не предоставлена), если profile_bio равен NULL или пустой строке. (Подсказка: COALESCE(NULLIF(TRIM(profile_bio), ''), 'No bio provided')).
11. Абсолютное отклонение количества слов: Рассчитать абсолютную разницу между word_count каждой записи и целевой длиной в 1000 слов. Отобразить post_id, word_count и abs_diff_from_1000.
12. Определение неопубликованных записей: Использовать NULLIF, чтобы вернуть post_id, если status записи равен ‘Draft’ (Черновик) или ‘Archived’ (Заархивировано). В противном случае вернуть NULL. Отобразить post_id, status и результат как non_published_post_id.
13. Оценка времени чтения: Рассчитать предполагаемое время чтения в минутах, разделив word_count на 200 (среднее количество слов в минуту) и округлив результат ВВЕРХ до ближайшего целого числа. Отобразить post_id, word_count и estimated_read_minutes. (Использовать CEIL).
14. UserID с заполнением нулями: Отобразить user_id, отформатированный как 7-значная строка с ведущими нулями. Указать псевдоним formatted_user_id. Использовать LPAD.
15. Идентификация комментатора: Отобразить comment_id и comment_text. Показать идентификатор комментатора: если commenter_id не NULL, отобразить ‘User: ‘ (Пользователь:) с последующим commenter_id (приведенным к VARCHAR). Если commenter_id равен NULL, отобразить ‘Guest: ‘ (Гость:) с последующим commenter_name. Указать псевдоним commenter_identity. (Использовать CASE и COALESCE или вложенный CASE).

---

Вариант 7: Управление Авиарейсами

Сценарий: Вы работаете с базой данных небольшой авиакомпании, управляя информацией о рейсах, самолетах, аэропортах и бронированиях пассажиров.

Схема базы данных:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS bookings;
-- DROP TABLE IF EXISTS flights;
-- DROP TABLE IF EXISTS aircraft;
-- DROP TABLE IF EXISTS airports;
-- DROP TABLE IF EXISTS passengers;

-- 1. Create the 'airports' table
CREATE TABLE airports (
    airport_code CHAR(3) PRIMARY KEY,        -- e.g., 'JFK', 'LAX'. Fixed length.
    airport_name VARCHAR(100) NOT NULL,    -- For LENGTH(), UPPER()
    city VARCHAR(50) NOT NULL,
    country_code CHAR(2) NOT NULL          -- e.g., 'US', 'GB'. For LOWER()
);

-- 2. Create the 'aircraft' table
CREATE TABLE aircraft (
    aircraft_id SERIAL PRIMARY KEY,
    model_name VARCHAR(50) NOT NULL,       -- e.g., 'Boeing 737-800'
    manufacturer VARCHAR(50),
    seat_capacity INTEGER NOT NULL,        -- For MOD()
    range_miles NUMERIC(6, 0)              -- For numeric functions
);

-- 3. Create the 'flights' table
CREATE TABLE flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) UNIQUE NOT NULL, -- e.g., ' AA101 ', for TRIM(), SUBSTRING()
    departure_airport CHAR(3) REFERENCES airports(airport_code),
    arrival_airport CHAR(3) REFERENCES airports(airport_code),
    aircraft_id INTEGER REFERENCES aircraft(aircraft_id),
    scheduled_departure TIMESTAMP WITH TIME ZONE NOT NULL, -- For EXTRACT(), INTERVAL, date/time funcs
    scheduled_arrival TIMESTAMP WITH TIME ZONE NOT NULL,
    base_price NUMERIC(7, 2)                -- For ROUND(), CEIL(), FLOOR(), ABS()
);

-- 4. Create the 'passengers' table
CREATE TABLE passengers (
    passenger_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),             -- For COALESCE()
    last_name VARCHAR(50) NOT NULL,      -- For CONCAT()
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE                   -- For AGE()
);

-- 5. Create the 'bookings' table
CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    flight_id INTEGER REFERENCES flights(flight_id),
    passenger_id INTEGER REFERENCES passengers(passenger_id),
    booking_date DATE DEFAULT CURRENT_DATE,
    seat_number VARCHAR(4),              -- e.g., '12A'
    final_price NUMERIC(7, 2),           -- May differ from base_price due to fees/discounts
    status VARCHAR(15) DEFAULT 'Confirmed' -- e.g., Confirmed, Cancelled, Checked-In. For CASE(), NULLIF()
);


-- Sample Data Insertion --
-- Пример вставки данных --

INSERT INTO airports (airport_code, airport_name, city, country_code) VALUES
('JFK', 'John F. Kennedy International Airport', 'New York', 'US'),
('LAX', 'Los Angeles International Airport', 'Los Angeles', 'US'),
('LHR', 'London Heathrow Airport', 'London', 'GB'),
('ORD', 'O''Hare International Airport', 'Chicago', 'US'),
('ATL', 'Hartsfield-Jackson Atlanta International Airport', 'Atlanta', 'US');

INSERT INTO aircraft (model_name, manufacturer, seat_capacity, range_miles) VALUES
('Boeing 737-800', 'Boeing', 189, 3500),
('Airbus A320neo', 'Airbus', 195, 4000),
('Boeing 777-300ER', 'Boeing', 396, 7370),
('Embraer E175', 'Embraer', 88, 2200);

INSERT INTO flights (flight_number, departure_airport, arrival_airport, aircraft_id, scheduled_departure, scheduled_arrival, base_price) VALUES
(' AA101 ', 'JFK', 'LAX', 1, '2024-09-15 08:00:00-04', '2024-09-15 11:30:00-07', 350.00), -- Spaces
('BA202', 'LHR', 'JFK', 3, '2024-09-16 10:30:00+01', '2024-09-16 13:30:00-04', 680.50),
(' UA303', 'ORD', 'ATL', 2, '2024-09-15 14:00:00-05', '2024-09-15 16:45:00-04', 180.75), -- Leading space
('AA102 ', 'LAX', 'JFK', 1, '2024-09-17 13:00:00-07', '2024-09-17 21:30:00-04', 365.25), -- Trailing space
('DL404', 'ATL', 'ORD', 4, '2024-09-18 09:15:00-04', '2024-09-18 10:00:00-05', 175.00);

INSERT INTO passengers (first_name, middle_name, last_name, email, date_of_birth) VALUES
('James', 'Tiberius', 'Kirk', 'j.kirk@starfleet.com', '1985-03-22'),
('Leia', NULL, 'Organa', 'leia.o@rebellion.org', '1990-11-01'),
('Han', NULL, 'Solo', 'han.solo@falcon.net', '1988-07-10'),
('Sarah', 'Jane', 'Connor', 'sarah.connor@resistance.fut', '1984-05-13'),
('Ellen', 'Louise', 'Ripley', 'e.ripley@wey-yu.corp', '1980-01-07');

INSERT INTO bookings (flight_id, passenger_id, booking_date, seat_number, final_price, status) VALUES
(1, 1, '2024-07-10', '5A', 375.00, 'Confirmed'),
(2, 2, '2024-08-01', '22K', 710.50, 'Confirmed'),
(3, 3, '2024-08-15', '10C', 180.75, 'Cancelled'),
(1, 4, '2024-07-11', '5B', 380.00, 'Checked-In'),
(4, 5, '2024-09-01', NULL, 365.25, 'Confirmed'), -- NULL seat
(2, 3, '2024-08-20', '40A', 680.50, 'Confirmed');

Задачи для Варианта 7:

1. Полное имя пассажира: Отобразите passenger_id и создайте full_name, объединив first_name, middle_name (если оно существует, иначе пропустите) и last_name. Используйте COALESCE, чтобы элегантно обработать потенциальное значение NULL в middle_name (например, first || COALESCE(' ' || middle_name, '') || ' ' || last).
2. Очистка номеров рейсов: Покажите flight_id и flight_number после удаления любых начальных или конечных пробелов. Назовите очищенный столбец clean_flight_no.
3. Возраст пассажира: Рассчитайте текущий возраст каждого пассажира на основе их date_of_birth. Отобразите first_name, last_name, date_of_birth и вычисленный age.
4. Продолжительность полета: Рассчитайте запланированную продолжительность каждого рейса (разницу между scheduled_arrival и scheduled_departure). Отобразите flight_id, flight_number и вычисленную duration в виде INTERVAL.
5. Сводка по статусу бронирования: Отобразите booking_id, final_price и booking_summary. Используйте оператор CASE: если status имеет значение ‘Cancelled’, покажите ‘CANCELLED’. Если status имеет значение ‘Checked-In’, покажите ‘CHECKED-IN’. В противном случае покажите ‘CONFIRMED’.
6. Длина названия аэропорта: Отобразите airport_code и длину airport_name для всех аэропортов. Назовите столбец длины name_length.
7. Код авиакомпании из номера рейса: Извлеките первые 2 символа (обычно код авиакомпании) из clean_flight_no (из логики задачи 2). Отобразите clean_flight_no и извлеченный airline_code. (Используйте SUBSTRING для очищенного номера рейса).
8. Время до отправления: Для рейсов, запланированных на будущее, рассчитайте время, оставшееся до scheduled_departure от NOW(). Отобразите flight_number, scheduled_departure и time_to_departure. (Используйте scheduled_departure - NOW()).
9. Цена, округленная до ближайших 10 долларов: Отобразите flight_id и base_price. Также покажите base_price, округленную до ближайших 10 долларов. Назовите это как price_rounded_10. (Подсказка: ROUND(price, -1)).
10. Проверка назначения места: Отобразите booking_id и passenger_id. Используйте COALESCE, чтобы показать seat_number. Если seat_number имеет значение NULL, отобразите ‘Unassigned’. Назовите это как seat_status.
11. Месяц отправления и день недели: Извлеките месяц (числовой) и день недели (числовой, например, 0 для воскресенья, 6 для субботы в PostgreSQL) из времени scheduled_departure. Отобразите flight_number, scheduled_departure, departure_month и departure_dow.
12. Абсолютная разница в цене: Рассчитайте абсолютную разницу между final_price бронирования и base_price соответствующего рейса (требуется JOIN - предположим, что вы можете соединить bookings и flights по flight_id). Отобразите booking_id, final_price, base_price и price_difference.
13. Зануление определенного производителя самолетов: Используйте NULLIF, чтобы вернуть NULL, если manufacturer самолета имеет значение ‘Embraer’. В противном случае верните производителя. Отобразите aircraft_id, model_name, manufacturer и результат как null_if_embraer.
14. Четность вместимости самолета: Определите, является ли seat_capacity самолета нечетным или четным числом. Отобразите aircraft_id, model_name, seat_capacity и ‘Even’ или ‘Odd’ как capacity_parity. (Используйте MOD).
15. Форматирование идентификатора бронирования: Отобразите booking_id, отформатированный как 7-значная строка с ведущими нулями (например, 1 становится ‘0000001’). Назовите это как formatted_booking_id. (Подсказка: Используйте LPAD).

---

Вариант 8: Записи о пациентах больницы

Сценарий: Вы управляете упрощенной базой данных для клиники, отслеживая пациентов, врачей и приемы.

Схема базы данных:

-- Удалите таблицы, если они существуют (необязательно)
-- DROP TABLE IF EXISTS appointments;
-- DROP TABLE IF EXISTS doctors;
-- DROP TABLE IF EXISTS patients;

-- 1. Создайте таблицу 'patients'
CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_initial CHAR(1),            -- Одна буква инициала, допускает NULL. Для COALESCE
    last_name VARCHAR(50) NOT NULL,     -- Для CONCAT
    date_of_birth DATE NOT NULL,        -- Для AGE, EXTRACT
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')), -- M=Мужской, F=Женский, O=Другое
    phone_number VARCHAR(20),           -- Может иметь форматирование, например, '(123) 456-7890'. Для REPLACE
    email VARCHAR(100) UNIQUE          -- Для LOWER
);

-- 2. Создайте таблицу 'doctors'
CREATE TABLE doctors (
    doctor_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    specialty VARCHAR(100),             -- Для общих запросов, NULLIF
    office_number VARCHAR(10) UNIQUE,   -- Например, ' RM 301 ', для TRIM
    start_date DATE                   -- Для AGE
);

-- 3. Создайте таблицу 'appointments'
CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INTEGER REFERENCES patients(patient_id),
    doctor_id INTEGER REFERENCES doctors(doctor_id),
    appointment_datetime TIMESTAMP WITH TIME ZONE NOT NULL, -- Для EXTRACT, INTERVAL, CAST
    reason VARCHAR(255),                 -- Для LENGTH
    duration_minutes INTEGER DEFAULT 30, -- Для числовых операций
    billing_amount NUMERIC(8, 2),        -- Для ROUND, CEIL, FLOOR, ABS
    status VARCHAR(20) DEFAULT 'Scheduled' -- Например, Scheduled, Completed, Cancelled, No-Show. Для CASE
);

-- Вставка образцов данных --

INSERT INTO patients (first_name, middle_initial, last_name, date_of_birth, gender, phone_number, email) VALUES
('Gregory', 'H', 'House', '1959-06-11', 'M', '(609) 555-0100', 'g.house@ppth.org'),
('Lisa', NULL, 'Cuddy', '1968-10-15', 'F', '609-555-0101', 'L.Cuddy@ppth.org'),
('James', 'E', 'Wilson', '1965-05-22', 'M', ' 609.555.0102 ', 'jewilson@ppth.org'), -- Пробелы в телефоне
('Allison', NULL, 'Cameron', '1979-09-01', 'F', NULL, 'ACameron@ppth.org'), -- NULL в телефоне
('Robert', 'C', 'Chase', '1978-02-17', 'M', '6095550104', 'r.chase@ppth.org');

INSERT INTO doctors (first_name, last_name, specialty, office_number, start_date) VALUES
('Gregory', 'House', 'Nephrology, Infectious Disease', ' RM 201', '1995-04-01'), -- Пробел в начале
('Lisa', 'Cuddy', 'Endocrinology, Administration', ' ADMIN 01', '1998-07-15'), -- Смешанный регистр/пробелы
('James', 'Wilson', 'Oncology', ' ONC 305 ', '1992-11-01'), -- Пробелы в начале/конце
('Eric', 'Foreman', 'Neurology', 'NEURO 110', '2004-08-20');

INSERT INTO appointments (patient_id, doctor_id, appointment_datetime, reason, duration_minutes, billing_amount, status) VALUES
(1, 1, '2024-08-15 09:00:00-04', 'Consultation', 45, 250.00, 'Completed'),
(2, 2, '2024-08-15 10:00:00-04', 'Follow-up', 30, 150.50, 'Completed'),
(3, 3, '2024-09-20 14:30:00-04', 'Check-up', 30, NULL, 'Scheduled'), -- Будущее, счет еще не выставлен
(4, 1, '2024-08-01 11:00:00-04', 'Initial Visit', 60, 300.75, 'No-Show'),
(5, 4, '2024-09-25 16:00:00-04', 'Neurology consult', 45, NULL, 'Scheduled'), -- Будущее
(1, 3, '2023-12-10 09:30:00-05', 'Scan results', 20, 120.00, 'Completed'); -- Прошлое

Задачи для Варианта 8:

1. Полное имя пациента: Отобразите patient_id. Создайте строку full_name в формате ‘Фамилия, Имя И.’, где И - middle_initial. Если middle_initial равно NULL, опустите его. (например, ‘House, Gregory H.’, ‘Cuddy, Lisa’). Используйте CONCAT и COALESCE.
2. Стандартизованный номер телефона: Отобразите patient_id, first_name и phone_number. Создайте столбец clean_phone, удалив пробелы, скобки и дефисы из phone_number. (Подсказка: Используйте вложенные REPLACE). Обработайте NULL телефоны корректно (они должны остаться NULL).
3. Расчет возраста пациента: Вычислите текущий возраст каждого пациента в годах на основе их date_of_birth. Отобразите first_name, last_name, date_of_birth и целую часть возраста в годах (используйте EXTRACT(YEAR FROM AGE(...)) или аналогичное). Назовите столбец age_years.
4. Форматирование времени приема: Отобразите appointment_id, appointment_datetime. Преобразуйте appointment_datetime в строку, отформатированную как ‘YYYY-MM-DD HH24:MI’. Назовите столбец formatted_datetime. (Подсказка: Используйте TO_CHAR, если доступно, или объедините CAST со строковыми функциями). Для простоты с CAST: Приведите appointment_datetime::DATE и appointment_datetime::TIME отдельно и объедините.
5. Сводка статуса приема: Покажите appointment_id, patient_id, doctor_id и status_summary. Используйте CASE: Если status равен ‘Completed’, покажите ‘Done’. Если ‘Cancelled’ или ‘No-Show’, покажите ‘Missed’. Если ‘Scheduled’, покажите ‘Upcoming’.
6. Длина специальности врача: Отобразите doctor_id, last_name и длину строки specialty. Назовите длину specialty_length. Обработайте NULL специальности (длина должна быть 0 или NULL в зависимости от БД).
7. Домен электронной почты пациента: Извлеките доменное имя (часть после ‘@’) из адреса электронной почты пациента email. Отобразите patient_id, email и извлеченный email_domain. (Подсказка: Используйте SUBSTRING и POSITION('@' IN email)).
8. Дней с последнего приема: Для приемов со статусом ‘Completed’ рассчитайте количество дней, прошедших между appointment_datetime и CURRENT_DATE. Отобразите appointment_id, appointment_datetime и days_since_appointment.
9. Сумма счета округлена вверх: Отобразите appointment_id, billing_amount. Также покажите billing_amount, округленную ВВЕРХ до ближайшего целого доллара. Назовите это billed_ceil. Обработайте NULL суммы.
10. Отображение инициала пациента: Отобразите patient_id, first_name, last_name. Используйте COALESCE для отображения middle_initial; если он NULL, отобразите ‘-‘. Назовите столбец display_initial.
11. Год и квартал приема: Извлеките год и календарный квартал (1-4) из appointment_datetime. Отобразите appointment_id, appointment_datetime, appointment_year и appointment_quarter.
12. Абсолютная разница в счетах: Рассчитайте абсолютную разницу между billing_amount каждого приема и стандартной платой в размере 175,00 долларов США. Отобразите appointment_id, billing_amount и abs_diff_from_standard. Включите только приемы, где billing_amount не равен NULL.
13. Обратите в NULL конкретную специальность: Используйте NULLIF, чтобы вернуть NULL, если специальность врача - ‘Administration’. В противном случае верните специальность. Отобразите doctor_id, last_name, specialty и результат как null_if_admin.
14. Четность ID врача: Определите, является ли doctor_id нечетным или четным. Отобразите doctor_id, last_name и ‘Even’ или ‘Odd’ как id_parity. (Используйте MOD).
15. Очистить номер кабинета: Отобразите doctor_id, last_name и office_number. Создайте столбец clean_office_no, удалив все начальные/конечные пробелы и преобразовав результат в верхний регистр. (Используйте TRIM и UPPER).

---

Вариант 9: Сервис аренды автомобилей

Сценарий: Вы управляете базой данных для компании по прокату автомобилей, отслеживая транспортные средства, клиентов и договоры аренды.

Схема базы данных:

-- Удалить таблицы, если они существуют (необязательно)
-- DROP TABLE IF EXISTS rentals;
-- DROP TABLE IF EXISTS customers;
-- DROP TABLE IF EXISTS vehicles;
-- DROP TABLE IF EXISTS locations;

-- 1. Создать таблицу 'locations' (местоположения)
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    location_code VARCHAR(5) UNIQUE NOT NULL, -- например, ' LGA01', для TRIM
    address VARCHAR(255) NOT NULL,
    city VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20)             -- Для REPLACE
);

-- 2. Создать таблицу 'vehicles' (транспортные средства)
CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    vin VARCHAR(17) UNIQUE NOT NULL,        -- Vehicle Identification Number (VIN), для UPPER, SUBSTRING
    make VARCHAR(50) NOT NULL,              -- например, 'Toyota', 'Ford'
    model VARCHAR(50) NOT NULL,             -- например, 'Camry', 'Mustang'. Для NULLIF
    year INTEGER NOT NULL,                  -- Для EXTRACT (из даты), AGE (возраст транспортного средства)
    license_plate VARCHAR(10) UNIQUE,       -- например, ' ABC-123 '. Для TRIM, REPLACE
    daily_rate NUMERIC(6, 2) NOT NULL,      -- Для ROUND, CEIL, FLOOR, ABS
    current_mileage NUMERIC(7, 0) DEFAULT 0 -- Для числовых операций
);

-- 3. Создать таблицу 'customers' (клиенты)
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,         -- Для CONCAT, LENGTH
    email VARCHAR(100) UNIQUE NOT NULL,     -- Для LOWER
    date_of_birth DATE,                     -- Для AGE
    driver_license_no VARCHAR(20) UNIQUE,
    join_date DATE DEFAULT CURRENT_DATE     -- Для AGE, EXTRACT
);

-- 4. Создать таблицу 'rentals' (аренда)
CREATE TABLE rentals (
    rental_id SERIAL PRIMARY KEY,
    vehicle_id INTEGER REFERENCES vehicles(vehicle_id),
    customer_id INTEGER REFERENCES customers(customer_id),
    pickup_location_id INTEGER REFERENCES locations(location_id),
    dropoff_location_id INTEGER REFERENCES locations(location_id), -- Разрешает NULL, если совпадает с местом получения
    pickup_datetime TIMESTAMP WITH TIME ZONE NOT NULL, -- Для EXTRACT, INTERVAL, CAST
    return_datetime TIMESTAMP WITH TIME ZONE,           -- NULL, если в настоящее время арендован. Для COALESCE, CASE
    total_amount NUMERIC(8, 2)                  -- Рассчитывается при возврате. Разрешает NULL.
);

-- Вставка примера данных --

INSERT INTO locations (location_code, address, city, phone_number) VALUES
('JFK01', 'Building 78, JFK Airport', 'New York', '718-555-0110'),
('LAX01', '9020 Aviation Blvd', 'Los Angeles', '310-555-0120'),
('ORD01 ', '10000 Bessie Coleman Dr', 'Chicago', '773-555-0130'), -- Пробел в конце
(' MIA01', '4200 NW 21st St', 'Miami', '(305) 555-0140'); -- Пробел в начале

INSERT INTO vehicles (vin, make, model, year, license_plate, daily_rate, current_mileage) VALUES
('1GKS19EC5L0123456', 'Chevrolet', 'Malibu', 2020, ' NYC-101', 55.00, 35000), -- Пробел в начале номера
('JTDKBRFU0P0654321', 'Toyota', 'RAV4', 2023, 'CAL-202 ', 70.50, 12000), -- Пробел в конце номера
('1FA6P8CFXH5987654', 'Ford', 'Mustang', 2021, 'FLA-303', 95.75, 21000),
('WVWZZZAUZMW112233', 'Volkswagen', 'Golf', 2022, 'ILL-404', 60.00, 18500),
('JTDKBRFU0P0654ABC', 'Toyota', 'RAV4', 2023, NULL, 72.00, 9500); -- NULL номер

INSERT INTO customers (first_name, last_name, email, date_of_birth, driver_license_no, join_date) VALUES
('Walter', 'White', 'w.white@jpwynne.edu', '1958-09-07', 'NM123456', '2022-01-15'),
('Jesse', 'Pinkman', 'capncook@kryskay.com', '1984-08-24', 'NM654321', '2022-05-20'),
('Skyler', 'White', 's.white@accountant.net', '1970-08-11', 'NM789012', '2023-03-10'),
('Saul', 'Goodman', 'saul@bettercallsaul.com', '1960-11-07', NULL, '2021-11-01'), -- NULL лицензия
('Mike', 'Ehrmantraut', 'mike.e@security.pro', '1945-03-18', 'PA999888', '2023-06-01');

INSERT INTO rentals (vehicle_id, customer_id, pickup_location_id, dropoff_location_id, pickup_datetime, return_datetime, total_amount) VALUES
(1, 1, 1, 1, '2024-07-01 10:00:00-04', '2024-07-08 11:30:00-04', 412.50),
(2, 2, 2, 2, '2024-08-10 14:00:00-07', NULL, NULL), -- В настоящее время арендован
(3, 3, 4, 4, '2024-08-20 09:00:00-04', '2024-08-25 10:00:00-04', 574.50),
(4, 4, 3, NULL, '2024-09-05 12:00:00-05', NULL), -- В настоящее время арендован, NULL место возврата (предполагается то же самое)
(1, 5, 1, 3, '2024-09-10 15:30:00-04', NULL); -- В настоящее время арендован

Задачи для Варианта 9:

1. Полное имя клиента и длина: Отобразите customer_id. Создайте full_name путем объединения first_name и last_name. Также отобразите длину full_name. Дайте псевдоним name_length.
2. Очищенные номерные знаки: Покажите vehicle_id, make, model и license_plate. Создайте столбец clean_plate, удалив все начальные/конечные пробелы и преобразовав результат в верхний регистр. Обработайте NULL номерные знаки корректно.
3. Возраст клиента: Вычислите текущий возраст каждого клиента на основе их date_of_birth. Отобразите first_name, last_name, date_of_birth и вычисленный age. Обработайте NULL date_of_birth, если они были добавлены.
4. Продолжительность аренды (завершенные аренды): Для аренд, где return_datetime не NULL, рассчитайте продолжительность аренды. Отобразите rental_id, pickup_datetime, return_datetime и вычисленную rental_duration в виде интервала.
5. Статус транспортного средства: Отобразите vehicle_id, make, model. Определите статус транспортного средства. Проверьте, существует ли vehicle_id в таблице rentals с NULL return_datetime. Если да, статус - ‘Rented’ (Арендован). В противном случае статус - ‘Available’ (Доступен). (Требует LEFT JOIN и CASE или подзапрос). Упрощено для задания: основывайтесь только на приведенных примерах данных. Если транспортное средство появляется в rentals с NULL return_datetime, оно ‘Rented’, иначе ‘Available’. Используйте CASE.
6. VIN в верхнем регистре: Отобразите vehicle_id и номер vin, преобразованный полностью в верхний регистр. Дайте псевдоним upper_vin.
7. Аббревиатура марки транспортного средства: Покажите vehicle_id и первые 3 символа make. Дайте псевдоним make_abbr. (Используйте SUBSTRING).
8. Дней с момента присоединения клиента: Рассчитайте, сколько дней прошло с join_date каждого клиента. Отобразите customer_id, email, join_date и days_as_member. (Используйте CURRENT_DATE - join_date).
9. Ежедневная ставка, округленная в меньшую сторону: Отобразите vehicle_id, daily_rate. Также покажите daily_rate, округленную ВНИЗ до ближайшего целого доллара. Дайте этому псевдоним rate_floor.
10. Отображение местоположения возврата: Отобразите rental_id, pickup_location_id. Используйте COALESCE, чтобы показать dropoff_location_id. Если он NULL, покажите pickup_location_id вместо него (предполагая возврат в то же место). Дайте этому псевдоним effective_dropoff_id.
11. Месяц и час получения: Извлеките месяц (числовой) и час (0-23) из pickup_datetime. Отобразите rental_id, pickup_datetime, pickup_month и pickup_hour.
12. Абсолютная разница ставок: Рассчитайте абсолютную разницу между daily_rate каждого транспортного средства и средней ставкой в 75,00 долларов. Отобразите vehicle_id, make, model, daily_rate и abs_diff_from_avg.
13. Замена определенной модели на NULL: Используйте NULLIF, чтобы вернуть NULL, если model транспортного средства - ‘RAV4’. В противном случае верните модель. Отобразите vehicle_id, make, model и результат как null_if_rav4.
14. Проверка четности Customer ID: Определите, является ли customer_id четным или нечетным. Отобразите customer_id, first_name, last_name и ‘Even’ (Четный) или ‘Odd’ (Нечетный) как id_parity. (Используйте MOD).
15. Форматировать ежедневную ставку как текст: Отобразите vehicle_id, make, model и daily_rate. Создайте строку formatted_rate, которая показывает ставку в формате ‘$XX.YY в день’. (например, ‘$55.00 в день’). Используйте CAST и ||.

---

Вариант 10: Система Заказов Ресторана

Сценарий: Вы управляете базой данных для ресторана. Вам нужно запросить информацию о пунктах меню, заказах клиентов и деталях клиентов.

Схема Базы Данных:

-- Удаление таблиц, если они существуют (необязательно)
-- DROP TABLE IF EXISTS OrderItems;
-- DROP TABLE IF EXISTS Orders;
-- DROP TABLE IF EXISTS Customers;
-- DROP TABLE IF EXISTS MenuItems;

-- 1. Создание таблицы 'MenuItems'
CREATE TABLE MenuItems (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,         -- Для UPPER, LENGTH
    description TEXT,                        -- Разрешает NULL
    category VARCHAR(50),                    -- Для CASE
    price NUMERIC(6, 2) NOT NULL,            -- Для ROUND, CEIL, FLOOR, ABS
    prep_time_minutes INTEGER,               -- Для простых числовых операций
    item_code VARCHAR(20) UNIQUE             -- e.g., ' APP001 ', для TRIM
);

-- 2. Создание таблицы 'Customers'
CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    phone_number VARCHAR(20),                -- Может содержать '-', ' ', '(', ')' для REPLACE
    email VARCHAR(100) UNIQUE,               -- Для LOWER, COALESCE (возможно nullable)
    join_date DATE DEFAULT CURRENT_DATE      -- Для AGE, EXTRACT
);

-- 3. Создание таблицы 'Orders'
CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES Customers(customer_id), -- Может быть NULL для клиентов без регистрации
    order_time TIMESTAMP NOT NULL DEFAULT NOW(), -- Для EXTRACT, CAST
    status VARCHAR(20) DEFAULT 'Pending',     -- 'Pending', 'Preparing', 'Ready', 'Completed', 'Cancelled'
    table_number INTEGER,                     -- Для NULLIF
    is_takeaway BOOLEAN NOT NULL DEFAULT FALSE
);

-- 4. Создание таблицы 'OrderItems'
CREATE TABLE OrderItems (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES Orders(order_id),
    item_id INTEGER NOT NULL REFERENCES MenuItems(item_id),
    quantity INTEGER NOT NULL DEFAULT 1,
    special_requests VARCHAR(255)             -- Разрешает NULL, для COALESCE
);

-- Вставка Примеров Данных --

INSERT INTO MenuItems (item_name, description, category, price, prep_time_minutes, item_code) VALUES
('Classic Burger', 'Beef patty, lettuce, tomato, cheese', 'Burgers', 12.50, 10, ' BUR001 '),
('Veggie Delight Pizza', NULL, 'Pizza', 15.00, 15, 'PIZ005'),
('Caesar Salad', 'Romaine lettuce, croutons, parmesan, caesar dressing', 'Salads', 9.75, 7, ' SAL002'),
('French Fries', 'Crispy potato fries', 'Sides', 4.50, 5, 'SID001'),
('Spaghetti Carbonara', 'Pasta with egg, cheese, pancetta', 'Pasta', 14.00, 12, 'PAS003 '), -- Пробел в конце
('Chocolate Lava Cake', 'Warm chocolate cake with molten center', 'Desserts', 7.99, 8, ' DES001');

INSERT INTO Customers (first_name, last_name, phone_number, email, join_date) VALUES
('Walter', 'White', '505-123-4567', 'heisenberg@bb.net', '2022-01-15'),
('Jesse', 'Pinkman', '(505) 987-6543', 'CAPNCook@bb.net', '2022-05-20'),
('Skyler', 'White', NULL, 'skyler.white@carwash.com', '2022-01-15'),
('Gus', 'Fring', '505 555 0101', NULL, '2023-03-10'); -- NULL email

INSERT INTO Orders (customer_id, order_time, status, table_number, is_takeaway) VALUES
(1, '2023-10-26 19:05:00', 'Completed', 5, FALSE),
(2, '2023-10-26 19:15:00', 'Preparing', NULL, TRUE),
(NULL, '2023-10-26 19:20:00', 'Pending', 12, FALSE),
(1, '2023-09-10 12:30:00', 'Completed', 3, FALSE),
(3, '2023-10-27 08:00:00', 'Pending', NULL, TRUE);

INSERT INTO OrderItems (order_id, item_id, quantity, special_requests) VALUES
(1, 1, 1, 'Extra cheese'),
(1, 4, 1, NULL),
(2, 2, 1, 'No onions'),
(2, 3, 1, 'Dressing on the side'),
(3, 5, 2, NULL),
(4, 1, 1, NULL),
(4, 6, 1, 'Add ice cream');

Задачи для Варианта 10:

1. Полное Имя Клиента: Отобразить customer_id, first_name, last_name и объединить first_name и last_name в full_name.
2. Длина Имени Пункта Меню: Показать item_name и его длину в символах LENGTH для всех пунктов меню. Дать псевдоним столбцу длины name_length.
3. Очищенные Номера Телефонов: Отобразить customer_id и phone_number. Создать столбец cleaned_phone, удалив пробелы, дефисы, скобки ‘(‘ и ‘)’ из phone_number. (Подсказка: Несколько вызовов REPLACE).
4. Цена Пункта Меню Округленная: Показать item_name, price и price, округленную до ближайшего целого доллара (ROUND с 0 десятичными знаками). Дать псевдоним rounded_price.
5. День Недели Заказа: Отобразить order_id и order_time. Извлечь день недели (например, 0 для воскресенья, 1 для понедельника… 6 для субботы в PostgreSQL, используя DOW) из order_time. Дать псевдоним order_dow.
6. Длительность Членства: Вычислить, как долго каждый клиент (у которого есть join_date) зарегистрирован. Отобразить customer_id, join_date и длительность, используя AGE(). Дать псевдоним membership_age.
7. Очистка Кода Пункта Меню: Отобразить item_id и item_code, удалив все начальные и конечные пробелы. Дать псевдоним очищенному столбцу clean_item_code.
8. Отображение Специальных Запросов: Показать order_item_id. Использовать COALESCE для отображения special_requests; если NULL, отобразить ‘None’. Дать псевдоним requests_info.
9. Категоризация Времени Подготовки: Отобразить item_name, prep_time_minutes. Создать столбец prep_category: ‘Quick’, если < 8 минут, ‘Standard’, если 8-12 минут (включительно), ‘Long’, если > 12 минут. Обрабатывать NULL prep_time_minutes как ‘Unknown’.
10. Стандартизированные Адреса Электронной Почты Клиентов: Отобразить customer_id и email. Показать электронную почту в нижнем регистре, с псевдонимом lower_email. Если адрес электронной почты NULL, отобразить ‘N/A’ (Использовать LOWER и COALESCE).
11. Абсолютная Разница Цены от $10: Для каждого пункта меню вычислить абсолютную разницу между его price и $10.00. Отобразить item_name, price и abs_diff_from_10.
12. Время Заказа - Час: Извлечь час (0-23) из order_time для каждого заказа. Отобразить order_id, order_time и order_hour.
13. Идентификация Заказов за Столиком (Не на Вынос): Использовать NULLIF, чтобы вернуть NULL, если заказ is_takeaway равен TRUE. Объединить это с проверкой, что table_number не NULL, чтобы концептуально идентифицировать заказы не на вынос, назначенные столику. Упрощенная задача: Использовать NULLIF, чтобы вернуть NULL, если table_number равен 5. Отобразить order_id, table_number и null_if_table5.
14. Заказы Следующей Недели (Концептуально): Показать заказы, размещенные в течение последних 7 дней. Отобразить order_id, order_time. Использовать order_time >= CURRENT_DATE - INTERVAL '7 day'.
15. Форматирование Цены Пункта Меню: Отобразить item_name. Создать строку formatted_price, добавив префикс ‘$’ к price и приведя ее к VARCHAR. (например, ‘$12.50’).

---

Вариант 11: Сервис объявлений о недвижимости

Сценарий: Вы управляете базой данных для агентства недвижимости, отслеживая объекты недвижимости на продажу, агентов, клиентов и просмотры объектов.

Схема базы данных:

-- Удаление таблиц, если они существуют (необязательно)
-- DROP TABLE IF EXISTS Viewings;
-- DROP TABLE IF EXISTS Clients;
-- DROP TABLE IF EXISTS Agents;
-- DROP TABLE IF EXISTS Properties;

-- 1. Создание таблицы 'Properties'
CREATE TABLE Properties (
    property_id SERIAL PRIMARY KEY,
    address_line1 VARCHAR(255) NOT NULL,       -- Для общего использования
    city VARCHAR(100),
    postal_code VARCHAR(12),                   -- Например, ' SW1A 0AA ', для TRIM
    property_type VARCHAR(50),                 -- 'House', 'Apartment', 'Commercial' и т.д. Для CASE
    list_price NUMERIC(12, 2),                 -- Для ROUND, CEIL, ABS
    bedrooms INTEGER,                          -- Для MOD
    square_footage INTEGER,                    -- Для простых числовых операций
    list_date DATE NOT NULL,                   -- Для AGE, EXTRACT
    status VARCHAR(20) DEFAULT 'Active'        -- 'Active', 'Pending', 'Sold'
);

-- 2. Создание таблицы 'Agents'
CREATE TABLE Agents (
    agent_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),                   -- Для COALESCE
    last_name VARCHAR(50) NOT NULL,
    license_number VARCHAR(30) UNIQUE NOT NULL,-- Для LENGTH, SUBSTRING
    phone VARCHAR(20),
    email VARCHAR(100) UNIQUE NOT NULL,        -- Для LOWER
    hire_date DATE                             -- Для AGE
);

-- 3. Создание таблицы 'Clients'
CREATE TABLE Clients (
    client_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)                          -- Для REPLACE (очистка)
    -- preferred_contact_method VARCHAR(10) NULL -- Не используется в задачах для упрощения
);

-- 4. Создание таблицы 'Viewings'
CREATE TABLE Viewings (
    viewing_id SERIAL PRIMARY KEY,
    property_id INTEGER NOT NULL REFERENCES Properties(property_id),
    client_id INTEGER NOT NULL REFERENCES Clients(client_id),
    agent_id INTEGER NOT NULL REFERENCES Agents(agent_id),
    viewing_time TIMESTAMP NOT NULL,           -- Для EXTRACT, CAST to DATE
    client_feedback_score INTEGER              -- NULLable, 1-5. Для NULLIF
    -- feedback_notes TEXT NULL -- Не используется в задачах
);

-- Вставка образцов данных --

INSERT INTO Properties (address_line1, city, postal_code, property_type, list_price, bedrooms, square_footage, list_date, status) VALUES
('10 Downing St', 'London', ' SW1A 2AA ', 'House', 15000000.00, 10, 5000, '2023-01-15', 'Active'),
('221B Baker St', 'London', 'NW1 6XE', 'Apartment', 1200000.50, 3, 1500, '2023-03-01', 'Active'),
('1600 Pennsylvania Ave NW', 'Washington', ' 20500', 'House', 40000000.00, 132, 55000, '2022-11-10', 'Pending'), -- Пробел в почтовом коде
('1 Privet Drive', 'Little Whinging', ' GU15 3EJ', 'House', 350000.75, 4, 1200, '2023-05-20', 'Sold'), -- Пробел
('42 Wallaby Way', 'Sydney', ' NSW 2000 ', 'Commercial', 850000.00, 0, 2500, '2023-07-01', 'Active'); -- Пробелы

INSERT INTO Agents (first_name, middle_name, last_name, license_number, phone, email, hire_date) VALUES
('James', 'Tiberius', 'Kirk', 'NCC-1701-A', '555-1234', 'j.kirk@starfleet.org', '2018-04-01'),
('Jean-Luc', NULL, 'Picard', 'NCC-1701-D', '555-5678', 'jl.picard@starfleet.org', '2015-09-01'),
('Kathryn', 'Janeway', 'V', 'NCC-74656', '555-9101', 'K.JANEWAY@starfleet.org', '2019-11-15');

INSERT INTO Clients (first_name, last_name, email, phone) VALUES
('Frodo', 'Baggins', 'frodo@shire.net', '123-456-7890'),
('Samwise', 'Gamgee', 'sam.gamgee@shire.net', '123-456-7891'),
('Luke', 'Skywalker', 'luke@rebellion.org', '(987) 654-3210'),
('Leia', 'Organa', 'leia.organa@rebellion.org', NULL);

INSERT INTO Viewings (property_id, client_id, agent_id, viewing_time, client_feedback_score) VALUES
(1, 1, 2, '2023-02-10 14:00:00', 4),
(2, 3, 1, '2023-03-15 11:00:00', 5),
(2, 4, 1, '2023-03-18 15:30:00', NULL),
(4, 2, 3, '2023-06-01 10:00:00', 3),
(5, 1, 2, '2023-08-05 16:00:00', 2);

Задачи для Варианта 11:

1. Отображение полного имени агента: Показать agent_id, first_name, last_name. Использовать COALESCE для отображения middle_name; если NULL, показать пустую строку ‘’. Объединить имя, отчество (если есть) и фамилию в full_name.
2. Очистка почтового индекса объекта: Отобразить property_id и postal_code. Очистить postal_code, удалив начальные/конечные пробелы и преобразовав его в верхний регистр. Псевдоним: clean_postal_code.
3. Стаж агента: Рассчитать, как долго каждый агент работает в агентстве на CURRENT_DATE. Отобразить agent_id, hire_date и рассчитанный tenure.
4. Категоризация цены объекта: Отобразить property_id, list_price. Классифицировать по цене: ‘Affordable’, если < 500 000, ‘Mid-Range’, если от 500 000 до 1 999 999,99, ‘Luxury’, если >= 2 000 000. Псевдоним: price_category.
5. Стандартизированные email агентов: Показать agent_id и email, убедившись, что email полностью в нижнем регистре. Псевдоним: standard_email.
6. Возраст объявления объекта: Рассчитать, сколько дней объект находится в списке (от list_date до CURRENT_DATE). Отобразить property_id, list_date и разницу в днях. (Подсказка: CURRENT_DATE - list_date). Псевдоним: days_on_market.
7. Проверка длины номера лицензии: Отобразить agent_id, license_number и LENGTH license_number. Псевдоним: license_length.
8. Просмотры в этом году: Найти просмотры, которые произошли в текущем календарном году. Отобразить viewing_id, viewing_time. Использовать EXTRACT(YEAR FROM viewing_time) = EXTRACT(YEAR FROM CURRENT_DATE).
9. Цена, округленная вверх (до ближайшего миллиона): Отобразить property_id, list_price. Показать list_price, округленную ВВЕРХ до ближайшего миллиона. Псевдоним: price_ceil_million. (Подсказка: Разделить на 1 000 000, использовать CEIL, затем умножить обратно).
10. Извлечение последних 4 цифр телефона (концептуально): Для клиентов с номером телефона извлечь последние 4 цифры. Отобразить client_id, phone. (Подсказка: Используйте SUBSTRING и LENGTH. Требуется предположение о формате телефона или предварительная очистка). Упрощенная задача: Извлечь первые 3 символа first_name. Псевдоним: name_prefix.
11. Абсолютная разница цены от средней: Предположим, что средняя цена объекта недвижимости составляет 1 000 000 долларов. Рассчитать абсолютную разницу между list_price каждого объекта и этим средним значением. Отобразить property_id, list_price и abs_diff_from_avg.
12. Четное/Нечетное количество спален: Определить, имеет ли объект нечетное или четное количество спален. Отобразить property_id, bedrooms и ‘Odd’ или ‘Even’ как bedroom_parity. Обработать NULL bedrooms.
13. NULL оценки отзывов: Использовать NULLIF, чтобы вернуть NULL, если client_feedback_score для просмотра равен 3. Отобразить viewing_id, client_feedback_score и null_if_score_3.
14. Очистка номера телефона клиента: Отобразить client_id, phone. Использовать REPLACE для удаления символов ( , ) и - из номера телефона. Псевдоним: clean_phone.
15. Заполнение Agent ID: Отобразить agent_id в формате 4-значной строки с ведущими нулями (например, 1 становится ‘0001’). Псевдоним: formatted_agent_id. (Подсказка: LPAD и CAST).

---

Вариант 12: Членство в спортзале и бронирование классов

Сценарий: Вы управляете базой данных фитнес-центра, отслеживая членов, тренеров, фитнес-классы и бронирования классов.

Схема базы данных:

-- Удалить таблицы, если они существуют (необязательно)
-- DROP TABLE IF EXISTS Bookings;
-- DROP TABLE IF EXISTS Classes;
-- DROP TABLE IF EXISTS Trainers;
-- DROP TABLE IF EXISTS Members;

-- 1. Создать таблицу 'Members' (Члены)
CREATE TABLE Members (
    member_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,                 -- Для LOWER
    date_of_birth DATE,                        -- Для AGE, EXTRACT
    join_date DATE NOT NULL,                   -- Для AGE, EXTRACT
    membership_type VARCHAR(20),               -- 'Basic', 'Premium', 'VIP', NULL. Для COALESCE, CASE
    emergency_contact_name VARCHAR(100) NULL   -- Для COALESCE
);

-- 2. Создать таблицу 'Trainers' (Тренеры)
CREATE TABLE Trainers (
    trainer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    specialization VARCHAR(100),               -- Для общих запросов
    certification_code VARCHAR(20) UNIQUE,     -- например, ' ACSM-CPT ', для TRIM
    years_experience INTEGER                   -- Для числовых операций
);

-- 3. Создать таблицу 'Classes' (Классы)
CREATE TABLE Classes (
    class_id SERIAL PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL,          -- Для UPPER, SUBSTRING
    trainer_id INTEGER REFERENCES Trainers(trainer_id),
    schedule_time TIME NOT NULL,               -- Для EXTRACT
    schedule_day VARCHAR(10) NOT NULL,         -- 'Monday', 'Tuesday', и т.д.
    duration_minutes INTEGER NOT NULL,         -- Для ROUND, CEIL, FLOOR
    max_capacity INTEGER NOT NULL,
    difficulty_level VARCHAR(15)               -- 'Beginner', 'Intermediate', 'Advanced'. Для NULLIF
);

-- 4. Создать таблицу 'Bookings' (Бронирования)
CREATE TABLE Bookings (
    booking_id SERIAL PRIMARY KEY,
    member_id INTEGER NOT NULL REFERENCES Members(member_id),
    class_id INTEGER NOT NULL REFERENCES Classes(class_id),
    booking_time TIMESTAMP DEFAULT NOW(),      -- Для операций с датой/временем
    attendance_status VARCHAR(15) DEFAULT 'Booked', -- 'Booked', 'Attended', 'Cancelled', 'No-Show'. Для CASE
    waitlist_position INTEGER NULL             -- Для COALESCE
);

-- Вставка образцов данных --

INSERT INTO Members (first_name, last_name, email, date_of_birth, join_date, membership_type, emergency_contact_name) VALUES
('Arnold', 'Schwarzenegger', 'arnold@gov.com', '1947-07-30', '2020-01-10', 'VIP', 'Maria Shriver'),
('Dwayne', 'Johnson', 'TheRock@wwe.net', '1972-05-02', '2021-06-15', 'Premium', NULL),
('Jane', 'Fonda', 'jane.fonda@workout.com', '1937-12-21', '2022-03-01', 'Basic', 'Ted Turner'),
('Richard', 'Simmons', 'richard@sweatintotheoldies.org', '1948-07-12', '2022-08-20', NULL, 'Self'); -- NULL type

INSERT INTO Trainers (first_name, last_name, specialization, certification_code, years_experience) VALUES
('Jillian', 'Michaels', 'HIIT, Weight Loss', ' NASM-CPT ', 15), -- Пробелы
('Bob', 'Harper', 'CrossFit, Strength Training', 'CF-L1', 20),
('Tony', 'Horton', 'Functional Fitness, P90X', 'ACE-GFI', 25);

INSERT INTO Classes (class_name, trainer_id, schedule_time, schedule_day, duration_minutes, max_capacity, difficulty_level) VALUES
('Morning Yoga', 1, '07:00:00', 'Monday', 60, 20, 'Beginner'),
('Lunchtime HIIT', 1, '12:15:00', 'Wednesday', 45, 15, 'Intermediate'),
('Evening CrossFit', 2, '18:00:00', 'Tuesday', 75, 10, 'Advanced'),
('Total Body Conditioning', 3, '09:00:00', 'Friday', 55, 25, NULL), -- NULL difficulty
('Spin Cycle Express', 2, '17:30:00', 'Thursday', 30, 18, 'Intermediate');

INSERT INTO Bookings (member_id, class_id, booking_time, attendance_status, waitlist_position) VALUES
(1, 3, '2023-10-20 10:00:00', 'Booked', NULL),
(2, 2, '2023-10-21 11:00:00', 'Attended', NULL),
(3, 1, '2023-10-22 12:00:00', 'Booked', NULL),
(1, 5, '2023-10-23 13:00:00', 'Cancelled', NULL),
(4, 1, '2023-10-24 14:00:00', 'Booked', 1), -- В листе ожидания
(2, 3, '2023-10-25 15:00:00', 'No-Show', NULL);

Задания для варианта 12:

1. Полное имя участника: Отобразите member_id, first_name, last_name и объедините их в full_name.
2. Аббревиатура названия класса: Покажите class_id и первые 15 символов class_name. Присвойте псевдоним short_class_name.
3. Очистка кода сертификации тренера: Отобразите trainer_id и certification_code, удалив любые начальные или конечные пробелы. Присвойте псевдоним clean_cert_code.
4. Продолжительность класса в часах (округленно): Покажите class_id, duration_minutes. Вычислите продолжительность в часах (duration_minutes / 60.0) и округлите ее до 1 десятичного знака. Присвойте псевдоним duration_hours.
5. Расчет возраста участника: Вычислите текущий возраст каждого участника на основе date_of_birth. Отобразите member_id, first_name, date_of_birth и вычисленный age.
6. Продолжительность членства: Рассчитайте, как долго каждый участник является членом спортзала (от join_date до CURRENT_DATE). Отобразите member_id, join_date и membership_length, используя AGE().
7. Действительный тип членства: Отобразите member_id, first_name. Используйте COALESCE, чтобы отобразить membership_type; если NULL, отобразите ‘Standard’. Присвойте псевдоним effective_type.
8. Сводка статуса бронирования: Отобразите booking_id, attendance_status. Создайте status_summary: ‘Present’, если attendance_status равен ‘Attended’, ‘Absent’, если ‘No-Show’ или ‘Cancelled’, ‘Upcoming’, если ‘Booked’.
9. Категоризация времени класса: Отобразите class_id, schedule_time. Создайте time_category: ‘Morning’, если час < 12, ‘Afternoon’, если час равен 12-16, ‘Evening’, если час >= 17. (Подсказка: EXTRACT(HOUR FROM schedule_time)).
10. Стандартизированные электронные письма участников: Отобразите member_id и email во всех строчных буквах. Присвойте псевдоним lower_email.
11. Статус листа ожидания: Отобразите booking_id. Покажите ‘On Waitlist’, если waitlist_position NOT NULL, и ‘Confirmed Spot’, если waitlist_position IS NULL. Присвойте псевдоним spot_status. Используйте логику CASE или COALESCE.
12. Абсолютная разница опыта: Предположим, что эталоном для тренеров является опыт в 10 лет. Рассчитайте абсолютную разницу между years_experience каждого тренера и 10. Отобразите trainer_id, years_experience, abs_exp_diff.
13. Определение классов для начинающих: Используйте NULLIF, чтобы вернуть NULL, если difficulty_level класса равен ‘Beginner’. Отобразите class_id, class_name, difficulty_level и null_if_beginner.
14. Форматирование идентификатора участника: Отобразите member_id в виде 6-значной строки с ведущими нулями. Присвойте псевдоним formatted_member_id.
15. Продолжительность класса, округленная вверх: Отобразите class_id, duration_minutes. Покажите продолжительность, округленную ВВЕРХ до ближайших 10 минут. Присвойте псевдоним duration_ceil_10. (Подсказка: CEIL(duration_minutes / 10.0) * 10).

---

Вариант 13: Аналитика музыкального стримингового сервиса

Сценарий: Вы работаете в музыкальном стриминговом сервисе и вам необходимо проанализировать данные об исполнителях, альбомах, треках и привычках пользователей при прослушивании музыки.

Схема базы данных:

-- Drop tables if they exist (optional)
-- DROP TABLE IF EXISTS listen_history;
-- DROP TABLE IF EXISTS tracks;
-- DROP TABLE IF EXISTS albums;
-- DROP TABLE IF EXISTS artists;

-- 1. Create the 'artists' table
CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    artist_name VARCHAR(100) NOT NULL,     -- For UPPER, LENGTH, SUBSTRING
    country VARCHAR(50),                 -- For COALESCE
    primary_genre VARCHAR(50),           -- For CASE, NULLIF
    formation_year INTEGER              -- For EXTRACT (from date if converted), simple arithmetic
);

-- 2. Create the 'albums' table
CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    artist_id INTEGER REFERENCES artists(artist_id),
    album_title VARCHAR(150) NOT NULL,     -- For REPLACE, CONCAT
    release_date DATE,                     -- For AGE, EXTRACT, INTERVAL
    label_code VARCHAR(20) UNIQUE         -- e.g., ' LBL-001 ', for TRIM
);

-- 3. Create the 'tracks' table
CREATE TABLE tracks (
    track_id SERIAL PRIMARY KEY,
    album_id INTEGER REFERENCES albums(album_id),
    track_title VARCHAR(200) NOT NULL,
    track_number INTEGER,               -- For simple comparison
    duration_seconds INTEGER,           -- For numeric functions (ABS, ROUND, CEIL, FLOOR, MOD)
    is_explicit BOOLEAN DEFAULT FALSE   -- Added for potential CASE usage
);

-- 4. Create the 'listen_history' table (Simplified: assumes user_id exists elsewhere)
CREATE TABLE listen_history (
    listen_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL, -- Assume this user exists in another table
    track_id INTEGER REFERENCES tracks(track_id),
    listen_timestamp TIMESTAMP NOT NULL DEFAULT NOW() -- For EXTRACT, date/time ops
);

-- Sample Data Insertion --

INSERT INTO artists (artist_name, country, primary_genre, formation_year) VALUES
('The Cosmic Keys', 'USA', 'Psychedelic Rock', 1968),
('SynthWave Riders', 'France', 'Synthwave', 2015),
('Acoustic Echoes', NULL, 'Folk', 2005), -- NULL Country
('DJ Metronome', 'Germany', 'Techno', 1995),
('Lunar Quartet', 'Canada', 'Jazz', 2010);

INSERT INTO albums (artist_id, album_title, release_date, label_code) VALUES
(1, 'Galactic Dreams', '1970-05-20', ' VNYL-001 '),
(2, 'Neon Nights', '2017-11-01', ' ELEC-042'), -- Leading space
(3, 'Whispers in the Wood', '2008-03-15', 'FOLKREC-10'),
(4, 'Rhythm Sequence', '1999-09-09', 'TECHNO-X '), -- Trailing space
(2, 'Future Retro', '2020-08-25', 'ELEC-099 '); -- Trailing space

INSERT INTO tracks (album_id, track_title, track_number, duration_seconds, is_explicit) VALUES
(1, 'Intro Voyage', 1, 125, FALSE),
(1, 'Starlight Serenade', 2, 345, FALSE),
(2, 'City Drive', 1, 280, FALSE),
(2, 'Midnight Run (Explicit Edit)', 4, 310, TRUE), -- Explicit Track
(3, 'Forest Path', 3, 195, FALSE),
(4, 'Beat Pulse', 1, 450, FALSE),
(4, 'System Overload', 5, 390, FALSE),
(5, 'Retrograde Motion', 2, 295, FALSE);

INSERT INTO listen_history (user_id, track_id, listen_timestamp) VALUES
(101, 2, '2023-10-26 08:15:00'),
(102, 3, '2023-10-26 09:00:15'),
(101, 4, '2023-10-26 10:30:00'),
(103, 5, '2023-10-25 14:00:45'),
(102, 7, '2023-10-26 11:05:10'),
(101, 2, '2023-10-24 18:00:00'),
(104, 8, '2023-09-10 20:21:30');

Задачи для Варианта 13:

1. Длина имени исполнителя: Отобразите artist_id и artist_name для всех исполнителей, а также длину их имен. Назовите столбец с длиной name_length. Отсортируйте по длине в порядке убывания.
2. Форматированные названия треков: Покажите track_id и track_title во всех заглавных буквах. Назовите это upper_title.
3. Очищенные коды лейблов: Отобразите album_id и label_code для всех альбомов. Удалите все начальные и конечные пробелы из label_code. Назовите очищенный столбец clean_label_code.
4. Длительность трека в минутах: Отобразите track_id, track_title и duration_seconds. Вычислите приблизительную продолжительность в минутах, разделив duration_seconds на 60 и округлив до 1 знака после запятой. Назовите это duration_minutes_approx.
5. Возраст альбома: Вычислите возраст каждого альбома на основе его release_date относительно CURRENT_DATE. Отобразите album_title, release_date и вычисленный album_age с помощью AGE().
6. Отображение происхождения исполнителя: Покажите artist_id и artist_name. Используйте COALESCE, чтобы отобразить country; если country имеет значение NULL, отобразите ‘Unknown’. Назовите этот столбец origin_country.
7. Десятилетие формирования: Отобразите artist_name и formation_year. Определите десятилетие формирования (например, 1960-е, 1990-е, 2010-е). Назовите это formation_decade. (Подсказка: используйте целочисленное деление FLOOR(formation_year / 10) * 10 и конкатенацию).
8. Категории продолжительности трека: Отобразите track_id, track_title и duration_seconds. Классифицируйте треки по продолжительности: ‘Short’, если < 180 секунд, ‘Medium’, если >= 180 и < 300 секунд, ‘Long’, если >= 300 секунд. Назовите столбец duration_category.
9. Идентификатор трека с ненормативной лексикой: Покажите track_id, track_title и is_explicit. Создайте столбец display_title, который объединяет track_title с ‘ (Explicit)’, если is_explicit имеет значение TRUE, в противном случае просто покажите track_title. (Подсказка: используйте CASE и CONCAT/||).
10. Час прослушивания: Для каждой записи в listen_history извлеките час (0-23) из listen_timestamp. Отобразите listen_id, user_id, track_id и извлеченный listen_hour.
11. Сокращенное название альбома: Отобразите album_id и первые 20 символов album_title. Назовите это short_album_title.
12. Время с момента последнего прослушивания: Для каждого прослушанного трека найдите время, прошедшее с момента самого последнего прослушивания, зафиксированного в listen_history на момент NOW(). Отобразите track_id и вычисленное время с помощью AGE(MAX(listen_timestamp)). (Требуется агрегация - GROUP BY track_id).
13. Проверка даты выпуска: Добавьте 6 месяцев к release_date каждого альбома. Отобразите album_title, release_date и вычисленный release_plus_6m.
14. Обнуление жанра: Используйте NULLIF, чтобы вернуть NULL, если primary_genre исполнителя - ‘Techno’. В противном случае верните жанр. Отобразите artist_id, artist_name, primary_genre и результат как null_if_techno.
15. Заполнение Artist ID: Отобразите artist_id в формате 6-значной строки с ведущими нулями (например, 1 становится ‘000001’). Назовите это formatted_artist_id. (Подсказка: используйте LPAD и CAST).

---

Вариант 14: Система отслеживания ошибок (Bug Tracking System)

Сценарий: Вы управляете базой данных для отслеживания программных ошибок (bugs), включая затронутые продукты, участвующих разработчиков и статус исправлений.

Схема базы данных:

-- Удаление таблиц, если они существуют (необязательно)
-- DROP TABLE IF EXISTS bug_comments;
-- DROP TABLE IF EXISTS bugs;
-- DROP TABLE IF EXISTS developers;
-- DROP TABLE IF EXISTS products;

-- 1. Создание таблицы 'products'
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,        -- Для общего справочника
    current_version VARCHAR(20),               -- Например, ' v2.1.3-RC1 ', для TRIM, REPLACE
    release_date DATE                          -- Для AGE, EXTRACT
);

-- 2. Создание таблицы 'developers'
CREATE TABLE developers (
    dev_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,        -- Для LOWER
    hire_date DATE,                            -- Для AGE
    skill_level INTEGER                        -- 1=Младший, 2=Средний, 3=Старший, 4=Ведущий. Для CASE
);

-- 3. Создание таблицы 'bugs'
CREATE TABLE bugs (
    bug_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    reported_by_dev_id INTEGER REFERENCES developers(dev_id),
    assigned_to_dev_id INTEGER REFERENCES developers(dev_id), -- Допускает NULL
    summary VARCHAR(255) NOT NULL,             -- Для SUBSTRING, LENGTH
    severity INTEGER NOT NULL,                 -- 1(Низкая) до 5(Критическая). Для ABS, численных операций
    status VARCHAR(20) DEFAULT 'New',          -- 'New', 'Assigned', 'In Progress', 'Resolved', 'Closed'. Для NULLIF
    reported_date TIMESTAMP DEFAULT NOW(),     -- Для EXTRACT, операций с датами
    resolved_date DATE                         -- NULL, если не решена. Для COALESCE, AGE, CASE
);

-- 4. Создание таблицы 'bug_comments'
CREATE TABLE bug_comments (
    comment_id SERIAL PRIMARY KEY,
    bug_id INTEGER REFERENCES bugs(bug_id),
    author_dev_id INTEGER REFERENCES developers(dev_id),
    comment_text TEXT,                         -- Для LENGTH
    comment_date TIMESTAMP DEFAULT NOW()
);

-- Вставка образцов данных --

INSERT INTO products (product_name, current_version, release_date) VALUES
('Data Analyzer Pro', ' v3.0.1 ', '2022-08-15'),
('Secure Messenger', '1.5.0-beta', '2023-01-20'),
('Cloud Sync Service', ' v2.2.5 ', '2021-11-01');

INSERT INTO developers (first_name, last_name, email, hire_date, skill_level) VALUES
('Ada', 'Lovelace', 'ada.l@code.com', '2020-06-01', 3),
('Charles', 'Babbage', 'charles.b@code.com', '2019-11-15', 4),
('Grace', 'Hopper', 'grace.h@code.com', '2021-03-10', 3),
('Alan', 'Turing', 'Alan.Turing@code.com', '2022-09-01', 2); -- Email в смешанном регистре

INSERT INTO bugs (product_id, reported_by_dev_id, assigned_to_dev_id, summary, severity, status, reported_date, resolved_date) VALUES
(1, 1, 3, 'Incorrect calculation for average function', 4, 'Assigned', '2023-09-10 14:30:00', NULL),
(2, 4, 4, 'UI alignment issue on main screen (iOS only)', 2, 'In Progress', '2023-10-01 09:00:00', NULL),
(1, 2, 1, 'Data export fails for large datasets > 1GB', 5, 'Resolved', '2023-08-20 11:15:00', '2023-09-05'),
(3, 3, NULL, 'Intermittent sync failures under heavy load', 3, 'New', '2023-10-25 16:00:00', NULL), -- Не назначена
(2, 1, 4, 'Typo in welcome message : "Welocme"', 1, 'Resolved', '2023-09-28 10:00:00', '2023-09-29');

INSERT INTO bug_comments (bug_id, author_dev_id, comment_text, comment_date) VALUES
(1, 3, 'Confirmed the issue. Working on a fix.', '2023-09-11 10:00:00'),
(2, 4, 'Reproduced on iPhone 14 simulator. Looks like a constraint problem.', '2023-10-02 14:00:00'),
(3, 1, 'Patch applied and tested. Closing issue.', '2023-09-05 17:00:00'),
(5, 4, 'Fixed the typo. Simple change.', '2023-09-29 09:30:00');

Задания для варианта 14:

1. Полное имя и Email разработчика: Отобразите dev_id. Создайте full_name, объединив first_name и last_name. Отобразите email адрес полностью в нижнем регистре, используя псевдоним lower_email.
2. Очищенные версии продукта: Покажите product_id, product_name и current_version. Очистите current_version, удалив начальные/конечные пробелы и заменив дефисы (‘-‘) точками (‘.’). Используйте псевдоним clean_version для очищенной версии. (Подсказка: Используйте TRIM и REPLACE).
3. Возраст ошибки (открытые ошибки): Для ошибок, которые не решены или не закрыты (т.е., resolved_date IS NULL), вычислите, как долго они открыты с reported_date до NOW(). Отобразите bug_id, summary, reported_date и вычисленную open_duration, используя AGE().
4. Описание статуса ошибки: Отобразите bug_id и status. Предоставьте более описательный статус, используя CASE: Если ‘New’, отобразите ‘Ожидает рассмотрения’. Если ‘Assigned’ или ‘In Progress’, отобразите ‘Находится на стадии исследования’. Если ‘Resolved’, отобразите ‘Исправление ожидает проверки’. Если ‘Closed’, отобразите ‘Завершено’. В противном случае отобразите сам статус. Используйте псевдоним status_description.
5. Уровень квалификации разработчика: Отобразите dev_id, first_name и skill_level. Создайте столбец skill_name, используя CASE, чтобы перевести числовой skill_level (1=’Младший’, 2=’Средний’, 3=’Старший’, 4=’Ведущий’).
6. Аббревиатура описания ошибки: Отобразите bug_id и первые 50 символов summary. Если summary длиннее 50 символов, добавьте ‘…’. Используйте псевдоним short_summary. (Подсказка: Используйте SUBSTRING, LENGTH, CONCAT/||).
7. Округление серьезности в большую сторону: Отобразите bug_id и severity. Покажите severity, “округленную в большую сторону” до ближайшего кратного 2 (например, 1->2, 2->2, 3->4, 4->4, 5->6). Используйте псевдоним severity_group. (Подсказка: CEIL(severity / 2.0) * 2 может сработать, или используйте CASE).
8. Отображение назначенного разработчика: Отобразите bug_id и summary. Используйте COALESCE, чтобы показать assigned_to_dev_id. Если он NULL (не назначен), отобразите 0. Используйте псевдоним assignee_id.
9. Месяц и год отчета: Извлеките месяц (числовой) и год из временной метки reported_date для каждой ошибки. Отобразите bug_id, reported_date, reported_month и reported_year.
10. Дни до решения: Для ошибок, которые решены или закрыты (resolved_date IS NOT NULL), вычислите количество дней между reported_date (приведенной к DATE) и resolved_date. Отобразите bug_id, summary, reported_date, resolved_date и days_to_resolve. (Подсказка: Вычитание дат).
11. Абсолютная разница серьезности: Вычислите абсолютную разницу между severity каждой ошибки и целевым уровнем серьезности 3. Отобразите bug_id, severity и abs_diff_from_target.
12. Возраст продукта: Вычислите возраст каждого продукта на основе его release_date относительно CURRENT_DATE. Отобразите product_name, release_date и product_age.
13. Поиск ошибок, отличных от ‘New’: Используйте NULLIF, чтобы вернуть NULL, если status ошибки равен ‘New’. В противном случае верните статус. Отобразите bug_id, status и результат как null_if_new.
14. Проверка четности идентификатора ошибки: Определите, является ли bug_id четным или нечетным, используя оператор взятия остатка от деления (modulo). Отобразите bug_id и ‘Even’ или ‘Odd’ как id_parity.
15. Домен электронной почты разработчика: Извлеките имя домена (часть после ‘@’) из адреса электронной почты разработчика email. Отобразите dev_id, email и email_domain. (Подсказка: Используйте SUBSTRING и POSITION('@' IN email)).

---

Вариант 15: Система управления автопарком

Сценарий: Вы управляете базой данных для логистической компании, отслеживая транспортные средства, водителей, поездки и графики технического обслуживания.

Схема базы данных:

-- Удалить таблицы, если они существуют (необязательно)
-- DROP TABLE IF EXISTS maintenance;
-- DROP TABLE IF EXISTS trips;
-- DROP TABLE IF EXISTS vehicles;
-- DROP TABLE IF EXISTS drivers;

-- 1. Создать таблицу 'drivers'
CREATE TABLE drivers (
    driver_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    license_number VARCHAR(30) UNIQUE NOT NULL, -- e.g., ' CA-12345-X ', для TRIM, UPPER, REPLACE
    hire_date DATE NOT NULL,                    -- Для AGE, EXTRACT
    date_of_birth DATE,                         -- Для AGE
    contact_phone VARCHAR(20)                   -- e.g., '+1 (555) 123-4567', для REPLACE
);

-- 2. Создать таблицу 'vehicles'
CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    make VARCHAR(50),
    model VARCHAR(50),
    year INTEGER,                               -- Для простой арифметики
    license_plate VARCHAR(15) UNIQUE NOT NULL,  -- e.g., ' 7XYZ123 ', для TRIM, UPPER
    purchase_date DATE,                         -- Для AGE
    fuel_capacity_liters NUMERIC(6, 1),         -- e.g., 150.5. Для ROUND, CEIL, FLOOR, ABS
    last_service_date DATE                      -- Для COALESCE, INTERVAL
);

-- 3. Создать таблицу 'trips'
CREATE TABLE trips (
    trip_id SERIAL PRIMARY KEY,
    vehicle_id INTEGER REFERENCES vehicles(vehicle_id),
    driver_id INTEGER REFERENCES drivers(driver_id),
    start_datetime TIMESTAMP NOT NULL,          -- Для EXTRACT, date ops
    end_datetime TIMESTAMP,                     -- NULL, если поездка продолжается. Для COALESCE, AGE, CASE
    start_location_code VARCHAR(10),            -- e.g., 'WAREHSE-A'
    end_location_code VARCHAR(10),              -- e.g., 'CLIENT-X'
    distance_km NUMERIC(8, 2)                   -- Для ROUND, ABS
);

-- 4. Создать таблицу 'maintenance'
CREATE TABLE maintenance (
    maint_id SERIAL PRIMARY KEY,
    vehicle_id INTEGER REFERENCES vehicles(vehicle_id),
    service_date DATE NOT NULL,
    service_type VARCHAR(50),                   -- e.g., 'Oil Change', 'Tire Rotation'. Для CASE
    cost NUMERIC(7, 2),                         -- Для ROUND, числовые операции
    notes VARCHAR(255)                          -- Для LENGTH
);

-- Вставка примера данных --

INSERT INTO drivers (first_name, last_name, license_number, hire_date, date_of_birth, contact_phone) VALUES
('Max', 'Rockatansky', ' AU-R0AD-WAR ', '2018-05-15', '1980-11-01', '+61 (4) 5555-1234'),
('Ellen', 'Ripley', ' US-LV426-X ', '2019-09-01', '1975-01-07', '+1 (800) 254-3678'),
('Han', 'Solo', ' DL-44BLASTER ', '2020-01-20', NULL, NULL), -- Отсутствует дата рождения, телефон
('Sarah', 'Connor', ' CA-T800-TERM ', '2021-07-10', '1984-05-12', '+1 (213) 555-1984');

INSERT INTO vehicles (make, model, year, license_plate, purchase_date, fuel_capacity_liters, last_service_date) VALUES
('Ford', 'Falcon XB GT', 1973, ' MFP-008 ', '2018-04-01', 120.0, '2023-08-15'),
('Weyland-Yutani', 'M577 APC', 2179, ' LV-426 ', '2019-08-01', 500.5, '2023-05-01'), -- Год в далеком будущем
('Corellian', 'YT-1300', -20, ' FALCON ', '2019-12-01', 1000.0, NULL), -- Отрицательный год, NULL дата обслуживания
('Jeep', 'Gladiator Rubicon', 2020, ' SKYNET ', '2021-06-15', 83.3, '2023-10-01');

INSERT INTO trips (vehicle_id, driver_id, start_datetime, end_datetime, start_location_code, end_location_code, distance_km) VALUES
(1, 1, '2023-10-20 08:00:00', '2023-10-20 18:30:00', 'BASE-A', 'SITE-B', 450.75),
(2, 2, '2023-10-21 06:00:00', NULL, 'HADLEYS', 'ORBIT', 15.50), -- Поездка продолжается
(3, 3, '2023-10-22 10:00:00', '2023-10-22 10:15:00', 'MOS-EISLEY', 'ANCHORHEAD', 12.20), -- Короткая поездка
(4, 4, '2023-10-23 09:00:00', '2023-10-24 17:00:00', 'LA-2023', 'MEXICO', 2450.00),
(1, 1, '2023-10-25 07:00:00', NULL, 'SITE-B', 'BASE-A', NULL); -- Обратный маршрут продолжается, расстояние неизвестно

INSERT INTO maintenance (vehicle_id, service_date, service_type, cost, notes) VALUES
(1, '2023-08-15', 'Engine Tune-up', 450.00, 'Replaced spark plugs'),
(2, '2023-05-01', 'Armor Repair', 2500.50, 'Welded hull breach'),
(4, '2023-10-01', 'Oil Change', 120.99, 'Standard 5k service'),
(1, '2023-02-10', 'Tire Rotation', 75.00, NULL); -- NULL notes

Задания для Варианта 15:

1. Полное имя и номер лицензии водителя: Отобразите driver_id. Создайте full_name. Очистите license_number, удалив пробелы и преобразовав в верхний регистр. Назовите очищенную лицензию clean_license.
2. Очистка номерного знака транспортного средства: Покажите vehicle_id, make, model. Очистите license_plate, удалив пробелы и преобразовав в верхний регистр. Назовите clean_plate.
3. Стаж работы водителя: Рассчитайте, как долго каждый водитель работает в компании, используя hire_date и CURRENT_DATE. Отобразите driver_id, full_name (из задания 1), hire_date и tenure с использованием AGE().
4. Продолжительность поездки: Для завершенных поездок (end_datetime IS NOT NULL) рассчитайте продолжительность, используя AGE(end_datetime, start_datetime). Отобразите trip_id, start_datetime, end_datetime и trip_duration.
5. Возраст транспортного средства: Рассчитайте возраст каждого транспортного средства на основе его purchase_date. Отобразите vehicle_id, license_plate, purchase_date и vehicle_age.
6. Последняя проверка обслуживания: Отобразите vehicle_id, license_plate. Используйте COALESCE, чтобы показать last_service_date. Если значение NULL, отобразите purchase_date вместо этого. Назовите это effective_last_service.
7. Стандартизированный номер телефона: Отобразите driver_id и contact_phone. Создайте standard_phone, удалив пробелы, скобки (), дефисы - и ведущий знак +. Обработайте возможные NULL-значения (должны оставаться NULL). (Подсказка: Используйте цепочку функций REPLACE).
8. Расстояние в милях: Отобразите trip_id, distance_km. Рассчитайте приблизительное расстояние в милях (1 км = 0.621371 мили) и округлите его до 1 десятичного знака. Назовите distance_miles. Обработайте NULL-значения расстояний.
9. Округление емкости бака: Отобразите vehicle_id, fuel_capacity_liters. Покажите емкость, округленную ВВЕРХ (CEIL) и ВНИЗ (FLOOR) до ближайшего целого литра. Назовите fuel_ceil_l и fuel_floor_l.
10. Категория стоимости обслуживания: Отобразите maint_id, service_type, cost. Классифицируйте стоимость, используя CASE: ‘Low’, если < $100, ‘Medium’, если $100-$500 (включительно), ‘High’, если > $500. Назовите cost_category.
11. День недели начала поездки: Извлеките день недели (например, 0 для воскресенья, 1 для понедельника… или текст, зависящий от БД) из start_datetime для каждой поездки. Отобразите trip_id, start_datetime и start_dow. (Подсказка: Используйте EXTRACT(DOW FROM ...) или эквивалент).
12. Абсолютная разница в расстоянии: Рассчитайте абсолютную разницу между distance_km каждой поездки и стандартным расстоянием в 100 км. Отобразите trip_id, distance_km и abs_diff_from_100km. Игнорируйте поездки с NULL-значением расстояния.
13. Дата следующего обслуживания: Для каждого транспортного средства рассчитайте дату, которая на 6 месяцев позже, чем effective_last_service (из задания 6). Отобразите vehicle_id, effective_last_service и next_service_due. (Подсказка: Используйте INTERVAL).
14. Поиск длинных заметок: Используйте LENGTH, чтобы найти записи о техническом обслуживании, где поле notes длиннее 20 символов. Отобразите maint_id, notes и notes_length.
15. Четность ID водителя: Проверьте, является ли driver_id четным или нечетным, используя MOD. Отобразите driver_id, full_name (из задания 1) и id_parity (‘Odd’ или ‘Even’).

---

Инструкции по сдаче

• Создайте новый Google Doc. Пример
• Добавьте ссылку на ваш Google Doc здесь: Google Таблица