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

Создание и использование представлений (VIEW) в SQL.

Вариант 1: Интернет-магазин книг

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

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

-- Таблица авторов
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    nationality VARCHAR(50)
);

-- Таблица книг
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT REFERENCES authors(author_id),
    genre VARCHAR(50),
    price NUMERIC(8, 2),
    publication_year INT,
    stock_count INT DEFAULT 0
);

-- Таблица клиентов
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    registration_date DATE DEFAULT CURRENT_DATE
);

-- Таблица заказов
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'Pending' -- например, Pending (В ожидании), Shipped (Отправлен), Delivered (Доставлен)
);

-- Таблица позиций заказа (Связывает заказы и книги)
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    book_id INT REFERENCES books(book_id),
    quantity INT NOT NULL,
    price_at_purchase NUMERIC(8, 2) NOT NULL -- Цена на момент оформления заказа
);

-- Примеры данных
INSERT INTO authors (author_name, nationality) VALUES
('Jane Austen', 'British'),
('George Orwell', 'British'),
('Haruki Murakami', 'Japanese'),
('Leo Tolstoy', 'Russian');

INSERT INTO books (title, author_id, genre, price, publication_year, stock_count) VALUES
('Pride and Prejudice', 1, 'Romance', 12.99, 1813, 50),
('1984', 2, 'Dystopian', 10.50, 1949, 35),
('Norwegian Wood', 3, 'Fiction', 14.00, 1987, 20),
('War and Peace', 4, 'Historical Fiction', 19.95, 1869, 15),
('Emma', 1, 'Romance', 11.50, 1815, 40),
('Animal Farm', 2, 'Political Satire', 9.00, 1945, 60);

INSERT INTO customers (first_name, last_name, email, registration_date) VALUES
('Alice', ' Wonderland', 'alice.w@mail.com', '2023-01-10'),
('Bob', 'The Builder', 'bob.b@mail.com', '2023-02-15'),
('Charlie', 'Chaplin', 'charlie.c@mail.com', '2023-03-20');

INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2023-10-01 10:00:00', 'Shipped'),
(2, '2023-10-05 14:30:00', 'Pending'),
(1, '2023-10-06 09:15:00', 'Pending');

INSERT INTO order_items (order_id, book_id, quantity, price_at_purchase) VALUES
(1, 1, 1, 12.99), -- Алиса покупает 'Гордость и предубеждение'
(1, 2, 1, 10.50), -- Алиса покупает '1984'
(2, 3, 2, 14.00), -- Боб покупает 2 шт. 'Норвежский лес'
(3, 6, 1, 9.00);  -- Алиса покупает 'Скотный двор'

Задания:

Создайте представление book_catalog: Создайте представление с именем book_catalog, которое показывает book_id, title, author_name и price для всех книг. Используйте соединение (JOIN) таблиц books и authors.
Создайте представление customer_emails: Создайте представление с именем customer_emails, отображающее только first_name, last_name и email всех клиентов.
Создайте представление low_stock_alert: Создайте представление с именем low_stock_alert, которое выводит title и stock_count для книг, у которых stock_count меньше 20.
Создайте представление pending_orders_summary: Создайте представление с именем pending_orders_summary, которое отображает order_id, customer_id и order_date для всех заказов со статусом ‘Pending’.
Создайте представление detailed_order_info: Создайте представление с именем detailed_order_info, показывающее order_id, order_date, first_name и last_name клиента, title книги, quantity и price_at_purchase. Это потребует соединения (JOIN) таблиц orders, customers, order_items и books.
Создайте представление author_book_count: Создайте представление с именем author_book_count, которое показывает author_name каждого автора и общее количество уникальных наименований книг (COUNT(b.book_id)), которые у него есть в таблице books. Сгруппируйте результаты по имени автора (GROUP BY author_name).
Запросите представление book_catalog: Напишите запрос для выбора всех книг из представления book_catalog, где автор (‘author_name’) — ‘George Orwell’.
Запросите представление detailed_order_info: Напишите запрос для поиска всех записей в представлении detailed_order_info, относящихся к order_id 1.
Измените представление customer_emails: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление customer_emails, добавив в него также столбец registration_date.
Удалите представление low_stock_alert: Удалите представление low_stock_alert из базы данных (используя DROP VIEW).

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

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

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

-- Таблица сотрудников
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_role VARCHAR(50), -- например, Разработчик, Менеджер, QA-тестировщик
    hire_date DATE
);

-- Таблица проектов
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(150) NOT NULL UNIQUE,
    start_date DATE,
    deadline DATE,
    budget NUMERIC(12, 2)
);

-- Таблица задач
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INT REFERENCES projects(project_id),
    task_description TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'To Do', -- например, To Do (К выполнению), In Progress (В работе), Done (Выполнено), Blocked (Заблокировано)
    priority INT -- например, 1 (Высокий), 2 (Средний), 3 (Низкий)
);

-- Таблица назначений (Связывает сотрудников и задачи)
CREATE TABLE assignments (
    assignment_id SERIAL PRIMARY KEY,
    task_id INT REFERENCES tasks(task_id),
    employee_id INT REFERENCES employees(employee_id),
    assigned_date DATE DEFAULT CURRENT_DATE,
    hours_logged NUMERIC(5, 2) DEFAULT 0
);

-- Примеры данных
INSERT INTO employees (emp_name, emp_role, hire_date) VALUES
('John Smith', 'Manager', '2020-05-15'),
('Alice Brown', 'Developer', '2021-08-20'),
('Bob White', 'Developer', '2021-09-01'),
('Carol Green', 'QA Tester', '2022-01-10');

INSERT INTO projects (project_name, start_date, deadline, budget) VALUES
('Website Redesign', '2023-09-01', '2024-03-01', 50000.00),
('Mobile App Launch', '2023-10-15', '2024-06-15', 75000.00),
('Internal CRM Update', '2023-11-01', '2024-02-01', 30000.00);

INSERT INTO tasks (project_id, task_description, status, priority) VALUES
(1, 'Define new site structure', 'Done', 1),
(1, 'Develop homepage mockups', 'In Progress', 1),
(1, 'Implement user authentication', 'To Do', 2),
(2, 'Setup development environment', 'Done', 1),
(2, 'Code core features', 'In Progress', 1),
(3, 'Analyze current CRM issues', 'In Progress', 2);

INSERT INTO assignments (task_id, employee_id, assigned_date, hours_logged) VALUES
(1, 1, '2023-09-05', 10.0), -- Джон управлял задачей 1
(2, 2, '2023-09-10', 15.5), -- Элис работает над задачей 2
(3, 3, '2023-09-15', 0.0),  -- Боб назначен на задачу 3
(4, 2, '2023-10-16', 8.0),  -- Элис выполнила задачу 4
(5, 3, '2023-10-20', 25.0), -- Боб работает над задачей 5
(6, 1, '2023-11-02', 5.0);  -- Джон работает над задачей 6

Задания:

Создайте представление active_projects: Создайте представление с именем active_projects, показывающее project_name, start_date и deadline для всех проектов, у которых deadline еще не наступил (или равен NULL, предполагая, что NULL означает бессрочно - для простоты сосредоточимся на deadline > CURRENT_DATE).
Создайте представление employee_roles: Создайте представление с именем employee_roles, отображающее только emp_name и emp_role для всех сотрудников.
Создайте представление high_priority_tasks: Создайте представление с именем high_priority_tasks, которое выводит task_description и status для задач с priority = 1.
Создайте представление project_task_list: Создайте представление с именем project_task_list, которое отображает project_name и task_description для всех задач. Используйте соединение (JOIN) таблиц projects и tasks.
Создайте представление employee_assignments_detailed: Создайте представление с именем employee_assignments_detailed, показывающее emp_name, project_name, task_description и assigned_date. Это потребует соединения (JOIN) таблиц employees, assignments, tasks и projects.
Создайте представление project_task_status_count: Создайте представление с именем project_task_status_count, которое показывает project_name каждого проекта и количество задач в каждом status (например, ‘To Do’, ‘In Progress’, ‘Done’). Сгруппируйте результаты по project_name и status (GROUP BY project_name, status). (Подсказка: COUNT(*) или COUNT(t.task_id)).
Запросите представление employee_roles: Напишите запрос для выбора всех сотрудников из представления employee_roles, у которых роль (‘emp_role’) — ‘Developer’.
Запросите представление employee_assignments_detailed: Напишите запрос для поиска всех назначений в представлении employee_assignments_detailed для проекта ‘Website Redesign’, отсортированных по assigned_date.
Измените представление active_projects: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление active_projects, добавив в него также столбец budget.
Удалите представление high_priority_tasks: Удалите представление high_priority_tasks из базы данных (используя DROP VIEW).

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

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

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

-- Таблица факультетов
CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    building VARCHAR(50)
);

-- Таблица курсов
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_code VARCHAR(10) NOT NULL UNIQUE, -- например, CS101, MATH203
    course_title VARCHAR(150) NOT NULL,
    dept_id INT REFERENCES departments(dept_id),
    credits INT CHECK (credits > 0) -- кредиты (зачетные единицы)
);

-- Таблица студентов
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_year INT -- год поступления
);

-- Таблица записей на курсы
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade CHAR(1) -- например, A, B, C, D, F (или NULL, если оценка еще не выставлена)
);

-- Примеры данных
INSERT INTO departments (dept_name, building) VALUES
('Computer Science', 'Engineering Hall'),
('Mathematics', 'Science Wing'),
('History', 'Arts Building');

INSERT INTO courses (course_code, course_title, dept_id, credits) VALUES
('CS101', 'Introduction to Programming', 1, 3),
('CS305', 'Databases', 1, 4),
('MATH201', 'Calculus I', 2, 4),
('HIST101', 'World History I', 3, 3),
('MATH202', 'Calculus II', 2, 4);

INSERT INTO students (first_name, last_name, email, enrollment_year) VALUES
('Peter', 'Pan', 'peter.p@uni.edu', 2022),
('Wendy', 'Darling', 'wendy.d@uni.edu', 2021),
('James', 'Hook', 'james.h@uni.edu', 2022),
('Mary', 'Poppins', 'mary.p@uni.edu', 2023);

INSERT INTO enrollments (student_id, course_id, enrollment_date, grade) VALUES
(1, 1, '2023-09-01', 'A'), -- Питер записывается на CS101
(1, 3, '2023-09-01', 'B'), -- Питер записывается на MATH201
(2, 1, '2023-09-01', 'B'), -- Венди записывается на CS101
(2, 4, '2023-09-01', NULL), -- Венди записывается на HIST101
(3, 1, '2023-09-02', 'C'), -- Джеймс записывается на CS101
(3, 2, '2023-09-02', NULL), -- Джеймс записывается на CS305 (Базы данных)
(1, 2, '2024-01-15', NULL); -- Питер записывается на CS305 позже

Задания:

Создайте представление course_directory: Создайте представление с именем course_directory, которое показывает course_code, course_title, credits и dept_name для всех курсов. Используйте соединение (JOIN) таблиц courses и departments.
Создайте представление student_contact_info: Создайте представление с именем student_contact_info, отображающее student_id, first_name, last_name и email всех студентов.
Создайте представление ungraded_enrollments: Создайте представление с именем ungraded_enrollments, которое выводит enrollment_id, student_id и course_id для всех записей на курсы, где grade равно NULL (оценка не выставлена).
Создайте представление cs_courses: Создайте представление с именем cs_courses, которое отображает course_code и course_title для всех курсов, предлагаемых факультетом ‘Computer Science’.
Создайте представление student_enrollment_details: Создайте представление с именем student_enrollment_details, показывающее first_name и last_name студента, course_code, course_title курса и grade (оценку). Это потребует соединения (JOIN) таблиц students, enrollments и courses.
Создайте представление department_course_count: Создайте представление с именем department_course_count, которое показывает dept_name каждого факультета и общее количество курсов (COUNT(c.course_id)), предлагаемых этим факультетом. Сгруппируйте результаты по названию факультета (GROUP BY department name).
Запросите представление course_directory: Напишите запрос для выбора всех курсов из представления course_directory, которые имеют 4 кредита (credits).
Запросите представление student_enrollment_details: Напишите запрос для поиска всех записей на курсы в представлении student_enrollment_details для студента ‘Peter Pan’, отсортированных по course_code.
Измените представление student_contact_info: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление student_contact_info, добавив в него также столбец enrollment_year.
Удалите представление ungraded_enrollments: Удалите представление ungraded_enrollments из базы данных (используя DROP VIEW).

Вариант 4: Музыкальный стриминговый сервис

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

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

-- Таблица исполнителей
CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    artist_name VARCHAR(150) NOT NULL UNIQUE,
    country VARCHAR(50)
);

-- Таблица альбомов
CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    album_title VARCHAR(200) NOT NULL,
    artist_id INT REFERENCES artists(artist_id),
    release_year INT,
    genre VARCHAR(50)
);

-- Таблица треков
CREATE TABLE tracks (
    track_id SERIAL PRIMARY KEY,
    track_title VARCHAR(200) NOT NULL,
    album_id INT REFERENCES albums(album_id),
    duration_seconds INT CHECK (duration_seconds > 0),
    track_number INT -- Позиция трека в альбоме
);

-- Таблица пользователей
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    join_date DATE DEFAULT CURRENT_DATE
);

-- Таблица плейлистов
CREATE TABLE playlists (
    playlist_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    playlist_name VARCHAR(100) NOT NULL,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_public BOOLEAN DEFAULT FALSE
);

-- Таблица треков плейлиста (Связующая таблица, соединяющая плейлисты и треки)
CREATE TABLE playlist_tracks (
    playlist_track_id SERIAL PRIMARY KEY,
    playlist_id INT REFERENCES playlists(playlist_id),
    track_id INT REFERENCES tracks(track_id),
    added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(playlist_id, track_id) -- Запрет на добавление одного и того же трека дважды в один плейлист
);

-- Примеры данных
INSERT INTO artists (artist_name, country) VALUES
('Queen', 'UK'),
('Daft Punk', 'France'),
('Led Zeppelin', 'UK'),
('Taylor Swift', 'USA');

INSERT INTO albums (album_title, artist_id, release_year, genre) VALUES
('A Night at the Opera', 1, 1975, 'Rock'),
('Discovery', 2, 2001, 'Electronic'),
('Led Zeppelin IV', 3, 1971, 'Hard Rock'),
('1989', 4, 2014, 'Pop'),
('News of the World', 1, 1977, 'Rock');

INSERT INTO tracks (track_title, album_id, duration_seconds, track_number) VALUES
('Bohemian Rhapsody', 1, 355, 1),
('You''re My Best Friend', 1, 171, 4),
('One More Time', 2, 320, 1),
('Digital Love', 2, 301, 3),
('Stairway to Heaven', 3, 482, 4),
('Black Dog', 3, 295, 1),
('We Will Rock You', 5, 121, 1),
('We Are The Champions', 5, 177, 2),
('Shake It Off', 4, 219, 6);

INSERT INTO users (username, email, join_date) VALUES
('musicfan1', 'fan1@email.com', '2023-01-15'),
('rocklover', 'rock@email.com', '2023-03-22'),
('djcool', 'dj@email.com', '2023-05-10');

INSERT INTO playlists (user_id, playlist_name, is_public) VALUES
(1, 'Workout Mix', TRUE),
(2, 'Classic Rock Anthems', TRUE),
(1, 'Chill Vibes', FALSE);

INSERT INTO playlist_tracks (playlist_id, track_id, added_date) VALUES
(1, 3, '2023-06-01 10:00:00'), -- Плейлист 'Workout Mix': One More Time
(1, 7, '2023-06-01 10:01:00'), -- Плейлист 'Workout Mix': We Will Rock You
(1, 9, '2023-06-01 10:02:00'), -- Плейлист 'Workout Mix': Shake It Off
(2, 1, '2023-06-05 15:30:00'), -- Плейлист 'Classic Rock Anthems': Bohemian Rhapsody
(2, 5, '2023-06-05 15:31:00'), -- Плейлист 'Classic Rock Anthems': Stairway to Heaven
(2, 6, '2023-06-05 15:32:00'), -- Плейлист 'Classic Rock Anthems': Black Dog
(3, 4, '2023-07-11 09:00:00'); -- Плейлист 'Chill Vibes': Digital Love

Задания:

Создайте представление full_track_info: Создайте представление с именем full_track_info, показывающее track_title, artist_name, album_title, release_year и duration_seconds. Это требует соединения (JOIN) таблиц tracks, albums и artists.
Создайте представление user_summary: Создайте представление с именем user_summary, показывающее только username и join_date для всех пользователей.
Создайте представление long_tracks: Создайте представление с именем long_tracks, перечисляющее track_title и duration_seconds для всех треков продолжительностью более 300 секунд (5 минут).
Создайте представление public_playlists_overview: Создайте представление с именем public_playlists_overview, которое отображает playlist_name, username создателя и creation_date для всех плейлистов, у которых is_public имеет значение TRUE. Используйте соединение (JOIN) таблиц playlists и users.
Создайте представление playlist_details: Создайте представление с именем playlist_details, показывающее playlist_name, track_title, artist_name и added_date для треков в плейлистах. Это потребует соединения (JOIN) таблиц playlists, playlist_tracks, tracks, albums и artists.
Создайте представление artist_album_count: Создайте представление с именем artist_album_count, которое показывает artist_name каждого исполнителя и общее количество альбомов (COUNT(al.album_id)), которые у него есть в таблице albums. Сгруппируйте результаты по имени исполнителя (GROUP BY artist_name).
Запросите представление full_track_info: Напишите запрос для выбора всей информации о треках из представления full_track_info для исполнителя (‘artist_name’) ‘Queen’.
Запросите представление playlist_details: Напишите запрос для поиска всех треков в представлении playlist_details, принадлежащих плейлисту с именем ‘Workout Mix’, упорядоченных по added_date.
Измените представление user_summary: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление user_summary, добавив в него также email пользователя.
Удалите представление long_tracks: Удалите представление long_tracks из базы данных (используя DROP VIEW).

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

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

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

-- Таблица аэропортов
CREATE TABLE airports (
    airport_code CHAR(3) PRIMARY KEY, -- например, LHR, JFK, CDG
    airport_name VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    country VARCHAR(50)
);

-- Таблица авиакомпаний
CREATE TABLE airlines (
    airline_id SERIAL PRIMARY KEY,
    airline_name VARCHAR(100) NOT NULL UNIQUE,
    iata_code CHAR(2) UNIQUE -- например, BA, AA, AF
);

-- Таблица рейсов
CREATE TABLE flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL, -- например, BA215, AA100
    airline_id INT REFERENCES airlines(airline_id),
    origin_airport CHAR(3) REFERENCES airports(airport_code),
    destination_airport CHAR(3) REFERENCES airports(airport_code),
    departure_time TIMESTAMP,
    arrival_time TIMESTAMP,
    status VARCHAR(20) DEFAULT 'Scheduled' -- например, Scheduled (По расписанию), Delayed (Задержан), Cancelled (Отменен), Departed (Вылетел), Arrived (Прибыл)
);

-- Таблица пассажиров
CREATE TABLE passengers (
    passenger_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    passport_number VARCHAR(20) UNIQUE,
    nationality VARCHAR(50)
);

-- Таблица бронирований
CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    flight_id INT REFERENCES flights(flight_id),
    passenger_id INT REFERENCES passengers(passenger_id),
    booking_date DATE DEFAULT CURRENT_DATE,
    seat_number VARCHAR(4) -- например, 12A, 30F
);

-- Примеры данных
INSERT INTO airports (airport_code, airport_name, city, country) VALUES
('LHR', 'Heathrow Airport', 'London', 'UK'),
('JFK', 'John F. Kennedy International Airport', 'New York', 'USA'),
('CDG', 'Charles de Gaulle Airport', 'Paris', 'France'),
('LAX', 'Los Angeles International Airport', 'Los Angeles', 'USA');

INSERT INTO airlines (airline_name, iata_code) VALUES
('British Airways', 'BA'),
('American Airlines', 'AA'),
('Air France', 'AF');

INSERT INTO flights (flight_number, airline_id, origin_airport, destination_airport, departure_time, arrival_time, status) VALUES
('BA175', 1, 'LHR', 'JFK', '2024-05-10 10:00:00', '2024-05-10 13:00:00', 'Scheduled'),
('AA101', 2, 'JFK', 'LAX', '2024-05-10 15:00:00', '2024-05-10 18:30:00', 'Scheduled'),
('AF008', 3, 'CDG', 'JFK', '2024-05-11 09:00:00', '2024-05-11 11:30:00', 'Scheduled'),
('BA287', 1, 'LHR', 'LAX', '2024-05-11 14:00:00', '2024-05-11 17:15:00', 'Delayed');

INSERT INTO passengers (first_name, last_name, passport_number, nationality) VALUES
('Clark', 'Kent', 'US123456', 'USA'),
('Lois', 'Lane', 'US654321', 'USA'),
('Bruce', 'Wayne', 'UK987654', 'UK'),
('Diana', 'Prince', 'FR112233', 'France');

INSERT INTO bookings (flight_id, passenger_id, booking_date, seat_number) VALUES
(1, 1, '2024-04-01', '10A'), -- Кларк на рейсе BA175
(1, 2, '2024-04-02', '10B'), -- Лоис на рейсе BA175
(2, 1, '2024-04-05', '22F'), -- Кларк на рейсе AA101
(3, 4, '2024-04-10', '05C'), -- Диана на рейсе AF008
(4, 3, '2024-04-15', '01A'); -- Брюс на рейсе BA287 (задержан)

Задания:

Создайте представление flight_schedule_detailed: Создайте представление с именем flight_schedule_detailed, показывающее flight_number, airline_name, название аэропорта отправления (orig_airport_name), название аэропорта назначения (dest_airport_name), departure_time и arrival_time. Это потребует соединения (JOIN) таблиц flights, airlines и airports (дважды, с использованием псевдонимов - aliases).
Создайте представление passenger_list: Создайте представление с именем passenger_list, отображающее только first_name, last_name и nationality всех пассажиров.
Создайте представление delayed_flights: Создайте представление с именем delayed_flights, которое выводит flight_number, origin_airport и destination_airport для рейсов со статусом ‘Delayed’.
Создайте представление jfk_departures: Создайте представление с именем jfk_departures, отображающее flight_number, destination_airport и departure_time для всех рейсов, вылетающих из ‘JFK’.
Создайте представление booking_manifest: Создайте представление с именем booking_manifest, показывающее flight_number, departure_time, first_name и last_name пассажира, а также seat_number. Используйте соединение (JOIN) таблиц bookings, passengers и flights.
Создайте представление airline_flight_count: Создайте представление с именем airline_flight_count, которое показывает airline_name каждой авиакомпании и общее количество рейсов (COUNT(f.flight_id)), связанных с этой авиакомпанией в таблице flights. Сгруппируйте результаты по названию авиакомпании (GROUP BY airline name).
Запросите представление flight_schedule_detailed: Напишите запрос для выбора всех рейсов из представления flight_schedule_detailed, выполняемых авиакомпанией ‘British Airways’.
Запросите представление booking_manifest: Напишите запрос для поиска всех бронирований пассажиров, перечисленных в представлении booking_manifest, для рейса BA175.
Измените представление passenger_list: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление passenger_list, добавив в него также столбец passport_number.
Удалите представление delayed_flights: Удалите представление delayed_flights из базы данных (используя DROP VIEW).

Вариант 6: Инвентаризация товаров в электронной коммерции

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

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

-- Таблица поставщиков
CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(150) NOT NULL UNIQUE,
    contact_email VARCHAR(100),
    country VARCHAR(50)
);

-- Таблица категорий
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT
);

-- Таблица товаров
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    sku VARCHAR(50) UNIQUE, -- Артикул (Единица складского учета)
    category_id INT REFERENCES categories(category_id),
    supplier_id INT REFERENCES suppliers(supplier_id),
    unit_price NUMERIC(10, 2) CHECK (unit_price >= 0)
);

-- Таблица складов
CREATE TABLE warehouses (
    warehouse_id SERIAL PRIMARY KEY,
    warehouse_name VARCHAR(100) NOT NULL,
    location_city VARCHAR(50),
    capacity_sqm INT -- Квадратные метры
);

-- Таблица инвентаризации (Уровни запасов товаров на складах)
CREATE TABLE inventory (
    inventory_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    warehouse_id INT REFERENCES warehouses(warehouse_id),
    quantity_on_hand INT NOT NULL DEFAULT 0 CHECK (quantity_on_hand >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(product_id, warehouse_id) -- Гарантирует только одну запись для товара на каждом складе
);

-- Примеры данных
INSERT INTO suppliers (supplier_name, contact_email, country) VALUES
('TechGadgets Inc.', 'sales@techgadgets.com', 'USA'),
('Office Supplies Co.', 'contact@officesupplies.co', 'Canada'),
('Home Decor Ltd.', 'info@homedecor.ltd', 'UK');

INSERT INTO categories (category_name, description) VALUES
('Electronics', 'Consumer electronics and accessories'),
('Office', 'Stationery, furniture, and equipment for offices'),
('Home Goods', 'Items for household use and decoration');

INSERT INTO products (product_name, sku, category_id, supplier_id, unit_price) VALUES
('Wireless Mouse', 'WM-101', 1, 1, 25.50),
('Mechanical Keyboard', 'MK-205', 1, 1, 75.00),
('Stapler (Heavy Duty)', 'STP-HD-50', 2, 2, 15.99),
('Desk Lamp (LED)', 'DL-LED-01', 2, 2, 35.00),
('Throw Pillow (Velvet)', 'TP-VL-BLUE', 3, 3, 19.95),
('Wall Clock (Modern)', 'WC-MOD-003', 3, 3, 45.00);

INSERT INTO warehouses (warehouse_name, location_city, capacity_sqm) VALUES
('Main Distribution Center', 'Chicago', 10000),
('West Coast Hub', 'Los Angeles', 5000),
('East Coast Depot', 'New York', 6000);

INSERT INTO inventory (product_id, warehouse_id, quantity_on_hand) VALUES
(1, 1, 500), -- Беспроводная мышь в Главном РЦ
(1, 2, 200), -- Беспроводная мышь в Хабе Западного побережья
(2, 1, 150), -- Клавиатура в Главном РЦ
(3, 1, 300), -- Степлер в Главном РЦ
(3, 3, 100), -- Степлер в Складе Восточного побережья
(4, 2, 50),  -- Настольная лампа в Хабе Западного побережья
(5, 1, 80),  -- Декоративная подушка в Главном РЦ
(6, 3, 40);  -- Настенные часы в Складе Восточного побережья

Задания:

Создайте представление full_product_catalog: Создайте представление с именем full_product_catalog, показывающее product_name, sku, category_name, supplier_name и unit_price. Это потребует соединения (JOIN) таблиц products, categories и suppliers.
Создайте представление supplier_contacts: Создайте представление с именем supplier_contacts, показывающее только supplier_name и contact_email для всех поставщиков.
Создайте представление low_stock_items: Создайте представление с именем low_stock_items, перечисляющее product_id, warehouse_id и quantity_on_hand для всех записей инвентаризации, где quantity_on_hand меньше 50.
Создайте представление electronics_catalog: Создайте представление с именем electronics_catalog, отображающее product_name, sku и unit_price для всех товаров, принадлежащих к категории ‘Electronics’.
Создайте представление warehouse_inventory_details: Создайте представление с именем warehouse_inventory_details, показывающее warehouse_name, location_city, product_name, sku и quantity_on_hand. Соедините (JOIN) таблицы inventory, products и warehouses.
Создайте представление category_product_count: Создайте представление с именем category_product_count, которое показывает category_name каждой категории и общее количество товаров (COUNT(p.product_id)), перечисленных в этой категории. Сгруппируйте результаты по названию категории (GROUP BY category_name).
Запросите представление full_product_catalog: Напишите запрос для выбора всех товаров из представления full_product_catalog, поставляемых ‘TechGadgets Inc.’.
Запросите представление warehouse_inventory_details: Напишите запрос для поиска всех сведений об инвентаризации в представлении warehouse_inventory_details для склада ‘Main Distribution Center’, упорядоченных по product_name.
Измените представление supplier_contacts: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление supplier_contacts, добавив в него также столбец country поставщика.
Удалите представление low_stock_items: Удалите представление low_stock_items из базы данных (используя DROP VIEW).

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

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

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

-- Таблица врачей
CREATE TABLE doctors (
    doctor_id SERIAL PRIMARY KEY,
    doc_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(100),
    phone_number VARCHAR(20)
);

-- Таблица пациентов
CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    contact_number VARCHAR(20) UNIQUE
);

-- Таблица приёмов (записей на приём)
CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INT REFERENCES patients(patient_id),
    doctor_id INT REFERENCES doctors(doctor_id),
    appointment_datetime TIMESTAMP NOT NULL,
    reason_for_visit TEXT,
    status VARCHAR(20) DEFAULT 'Scheduled' -- например, Scheduled (Запланирован), Completed (Завершен), Cancelled (Отменен), No-Show (Неявка)
);

-- Таблица диагнозов
CREATE TABLE diagnoses (
    diagnosis_id SERIAL PRIMARY KEY,
    appointment_id INT REFERENCES appointments(appointment_id) UNIQUE, -- Предполагается один основной диагноз на приём для простоты
    condition_name VARCHAR(150) NOT NULL,
    diagnosis_date DATE DEFAULT CURRENT_DATE,
    treatment_plan TEXT
);

-- Примеры данных
INSERT INTO doctors (doc_name, specialization, phone_number) VALUES
('Dr. Evelyn Reed', 'Cardiology', '555-0101'),
('Dr. Samuel Green', 'Pediatrics', '555-0102'),
('Dr. Anya Sharma', 'General Medicine', '555-0103');

INSERT INTO patients (first_name, last_name, date_of_birth, contact_number) VALUES
('Michael', 'Jones', '1985-06-15', '555-0201'),
('Sarah', 'Chen', '1992-11-22', '555-0202'),
('David', 'Lee', '2018-03-10', '555-0203'),
('Laura', 'Martinez', '1970-01-30', '555-0204');

INSERT INTO appointments (patient_id, doctor_id, appointment_datetime, reason_for_visit, status) VALUES
(1, 1, '2023-10-20 09:00:00', 'Chest pain evaluation', 'Completed'),
(2, 3, '2023-10-21 11:30:00', 'Annual check-up', 'Completed'),
(3, 2, '2023-10-22 14:00:00', 'Vaccination', 'Completed'),
(1, 3, '2023-10-28 10:00:00', 'Follow-up consultation', 'Scheduled'),
(4, 1, '2023-11-05 15:00:00', 'Blood pressure check', 'Scheduled');

INSERT INTO diagnoses (appointment_id, condition_name, diagnosis_date, treatment_plan) VALUES
(1, 'Angina Pectoris', '2023-10-20', 'Prescribe medication, recommend lifestyle changes.'),
(2, 'Healthy', '2023-10-21', 'Continue healthy habits, return next year.'),
(3, 'Up-to-date Immunizations', '2023-10-22', 'No further action needed.');

Задания:

Создайте представление patient_directory: Создайте представление с именем patient_directory, показывающее patient_id, first_name, last_name и contact_number для всех пациентов.
Создайте представление doctor_specializations: Создайте представление с именем doctor_specializations, выводящее doc_name и specialization для всех врачей.
Создайте представление upcoming_appointments: Создайте представление с именем upcoming_appointments, которое отображает appointment_id, полное имя пациента (конкатенация first_name и last_name), doc_name и appointment_datetime для всех приёмов со статусом ‘Scheduled’ и appointment_datetime в будущем (используйте > CURRENT_TIMESTAMP). Требует соединения (JOIN) таблиц patients, appointments и doctors.
Создайте представление patient_diagnosis_history: Создайте представление с именем patient_diagnosis_history, показывающее полное имя пациента, appointment_datetime, condition_name и treatment_plan. Это требует соединения (JOIN) таблиц patients, appointments и diagnoses.
Создайте представление cardiology_patients: Создайте представление с именем cardiology_patients, которое выводит first_name, last_name и contact_number пациентов, у которых был приём у врача со специализацией (‘specialization’) ‘Cardiology’. Требует соединения (JOIN) таблиц patients, appointments и doctors. Используйте DISTINCT, чтобы избежать дубликатов, если пациент посещал кардиолога несколько раз.
Создайте представление doctor_appointment_load: Создайте представление с именем doctor_appointment_load, показывающее doc_name и общее количество завершенных (‘Completed’) приёмов (COUNT(ap.appointment_id)), связанных с каждым врачом. Сгруппируйте по имени врача (GROUP BY doc_name). Включите врачей, даже если у них 0 завершенных приёмов (используйте LEFT JOIN от doctors к appointments).
Запросите представление upcoming_appointments: Напишите запрос для выбора всех предстоящих приёмов из представления upcoming_appointments, запланированных к врачу ‘Dr. Evelyn Reed’.
Запросите представление patient_diagnosis_history: Напишите запрос для поиска всех диагнозов, зарегистрированных для пациента ‘Michael Jones’, используя представление patient_diagnosis_history, отсортированных по appointment_datetime.
Измените представление patient_directory: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление patient_directory, добавив в него также столбец date_of_birth.
Удалите представление doctor_specializations: Удалите представление doctor_specializations из базы данных (используя DROP VIEW).

Вариант 8: Агентство по прокату автомобилей

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

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

-- Таблица клиентов
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    drivers_license_no VARCHAR(50) UNIQUE NOT NULL,
    member_since DATE DEFAULT CURRENT_DATE
);

-- Таблица пунктов проката
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    location_name VARCHAR(100) NOT NULL, -- например, Филиал в аэропорту, Офис в центре города
    city VARCHAR(50),
    address VARCHAR(255)
);

-- Таблица автомобилей
CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    make VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    year INT,
    license_plate VARCHAR(15) UNIQUE NOT NULL,
    category VARCHAR(20), -- например, Седан, Внедорожник, Минивэн, Люкс
    current_location_id INT REFERENCES locations(location_id),
    daily_rental_rate NUMERIC(6, 2)
);

-- Таблица аренд
CREATE TABLE rentals (
    rental_id SERIAL PRIMARY KEY,
    vehicle_id INT REFERENCES vehicles(vehicle_id),
    customer_id INT REFERENCES customers(customer_id),
    pickup_location_id INT REFERENCES locations(location_id),
    return_location_id INT REFERENCES locations(location_id), -- Может отличаться от пункта выдачи
    pickup_datetime TIMESTAMP NOT NULL,
    expected_return_datetime TIMESTAMP NOT NULL,
    actual_return_datetime TIMESTAMP, -- NULL, если автомобиль арендован в данный момент
    total_amount NUMERIC(8, 2) -- Рассчитывается при возврате
);

-- Примеры данных
INSERT INTO customers (first_name, last_name, email, drivers_license_no, member_since) VALUES
('Arthur', 'Dent', 'adent@galaxy.com', 'DL12345', '2022-01-10'),
('Ford', 'Prefect', 'fprefect@guide.com', 'DL67890', '2022-02-15'),
('Zaphod', 'Beeblebrox', 'zbeeblebrox@prez.gov', 'DL11111', '2023-03-20');

INSERT INTO locations (location_name, city, address) VALUES
('Airport Branch', 'Metropolis', '1 Airport Rd'),
('Downtown Office', 'Metropolis', '123 Main St'),
('Suburb Depot', 'Gotham', '45 Sidekick Ln');

INSERT INTO vehicles (make, model, year, license_plate, category, current_location_id, daily_rental_rate) VALUES
('Toyota', 'Camry', 2022, 'ABC-123', 'Sedan', 1, 55.00),
('Ford', 'Explorer', 2023, 'XYZ-789', 'SUV', 2, 75.00),
('Honda', 'Civic', 2021, 'DEF-456', 'Sedan', 1, 50.00),
('Chevrolet', 'Suburban', 2023, 'GHI-000', 'SUV', 3, 80.00);

INSERT INTO rentals (vehicle_id, customer_id, pickup_location_id, return_location_id, pickup_datetime, expected_return_datetime, actual_return_datetime, total_amount) VALUES
(1, 1, 1, 1, '2023-10-15 09:00:00', '2023-10-18 09:00:00', '2023-10-18 08:45:00', 165.00),
(2, 2, 2, 2, '2023-10-20 12:00:00', '2023-10-25 12:00:00', NULL, NULL), -- Арендован в данный момент
(3, 1, 1, 2, '2023-10-22 10:00:00', '2023-10-24 10:00:00', NULL, NULL); -- Арендован в данный момент

Задания:

Создайте представление vehicle_inventory: Создайте представление с именем vehicle_inventory, показывающее license_plate, make, model, category, daily_rental_rate и location_name текущего местоположения автомобиля (current_location_id). Соедините (JOIN) таблицы vehicles и locations.
Создайте представление customer_details: Создайте представление с именем customer_details, показывающее customer_id, полное имя (объединенные first_name и last_name), email и drivers_license_no.
Создайте представление active_rentals_summary: Создайте представление с именем active_rentals_summary, перечисляющее rental_id, полное имя клиента, make и model автомобиля, а также expected_return_datetime для всех аренд, где actual_return_datetime IS NULL. Требуется соединение (JOIN) таблиц rentals, customers и vehicles.
Создайте представление available_vehicles_now: Создайте представление с именем available_vehicles_now, показывающее license_plate, make, model, category, daily_rental_rate и location_name для автомобилей, которые НЕ арендованы в данный момент (т.е. их vehicle_id отсутствует в таблице rentals со значением actual_return_datetime IS NULL). Вам может потребоваться подзапрос или подход с использованием LEFT JOIN.
Создайте представление rental_history_customer: Создайте представление с именем rental_history_customer, показывающее полное имя клиента, make и model автомобиля, pickup_datetime, actual_return_datetime и total_amount для завершенных аренд (actual_return_datetime IS NOT NULL). Требуется соединение (JOIN) таблиц rentals, customers и vehicles.
Создайте представление location_vehicle_count: Создайте представление с именем location_vehicle_count, которое показывает location_name каждого пункта проката и общее количество автомобилей (COUNT(v.vehicle_id)), находящихся в данный момент в этом пункте (current_location_id). Сгруппируйте результаты по названию пункта проката (GROUP BY location name). Включите пункты проката с 0 автомобилей.
Запросите представление available_vehicles_now: Напишите запрос для выбора всех доступных автомобилей категории ‘SUV’ из представления available_vehicles_now, находящихся в пункте ‘Airport Branch’.
Запросите представление active_rentals_summary: Напишите запрос для поиска деталей активной аренды для клиента ‘Arthur Dent’, используя представление active_rentals_summary.
Измените представление customer_details: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление customer_details, добавив в него также дату member_since.
Удалите представление rental_history_customer: Удалите представление rental_history_customer из базы данных (используя DROP VIEW).

Вариант 9: Платформа для блогов

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

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

-- Таблица пользователей
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица категорий
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

-- Таблица постов
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    author_id INT REFERENCES users(user_id),
    category_id INT REFERENCES categories(category_id),
    title VARCHAR(255) NOT NULL,
    content TEXT,
    published_datetime TIMESTAMP, -- NULL, если черновик
    status VARCHAR(15) DEFAULT 'Draft' -- например, Draft (Черновик), Published (Опубликован), Archived (Архивирован)
);

-- Таблица комментариев
CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(post_id) ON DELETE CASCADE, -- Удалять комментарии при удалении поста
    commenter_id INT REFERENCES users(user_id), -- NULL для анонимных комментариев
    commenter_name VARCHAR(50), -- Используется, если commenter_id равен NULL
    comment_text TEXT NOT NULL,
    comment_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Примеры данных
INSERT INTO users (username, email, registration_date) VALUES
('john_doe', 'john.doe@email.com', '2023-01-15 10:00:00'),
('jane_smith', 'jane.s@email.com', '2023-02-20 11:00:00'),
('blog_admin', 'admin@blog.com', '2023-01-01 09:00:00');

INSERT INTO categories (category_name, description) VALUES
('Technology', 'Latest tech news and reviews'),
('Travel', 'Adventures around the world'),
('Food', 'Recipes and restaurant reviews');

INSERT INTO posts (author_id, category_id, title, content, published_datetime, status) VALUES
(1, 1, 'Understanding SQL Views', 'Views are virtual tables...', '2023-10-26 14:00:00', 'Published'),
(2, 2, 'Trip to the Mountains', 'The scenery was breathtaking...', '2023-11-01 09:30:00', 'Published'),
(1, 1, 'Introduction to Docker', 'Docker helps containerize apps...', NULL, 'Draft'),
(2, 3, 'Best Pizza Places', 'Reviewing local pizza joints...', '2023-11-05 16:00:00', 'Published');

INSERT INTO comments (post_id, commenter_id, commenter_name, comment_text, comment_datetime) VALUES
(1, 2, NULL, 'Great explanation, thanks!', '2023-10-26 15:00:00'), -- Джейн комментирует пост Джона
(1, NULL, 'Visitor', 'Very helpful article.', '2023-10-27 10:00:00'), -- Анонимный комментарий
(2, 1, NULL, 'Looks like an amazing trip!', '2023-11-01 11:00:00'); -- Джон комментирует пост Джейн

Задания:

Создайте представление published_posts_feed: Создайте представление с именем published_posts_feed, которое показывает post_id, title, username автора, category_name и published_datetime для всех постов со статусом ‘Published’. Упорядочите по published_datetime по убыванию. Требуется соединение (JOIN) таблиц posts, users и categories.
Создайте представление user_profiles: Создайте представление с именем user_profiles, отображающее user_id, username и email для всех зарегистрированных пользователей.
Создайте представление draft_posts_list: Создайте представление с именем draft_posts_list, которое выводит post_id, title и username автора для всех постов, находящихся в статусе ‘Draft’. Соедините (JOIN) таблицы posts и users.
Создайте представление post_comment_summary: Создайте представление с именем post_comment_summary, показывающее post_id, title и comment_text, вместе с идентификатором комментатора (используйте COALESCE(u.username, c.commenter_name, 'Anonymous') AS commenter_display_name). Требует соединения (JOIN) таблиц posts, comments и, опционально, users (используйте LEFT JOIN для users).
Создайте представление category_overview: Создайте представление с именем category_overview, которое выводит category_name и его description.
Создайте представление author_post_statistics: Создайте представление с именем author_post_statistics, показывающее username каждого автора и общее количество постов (COUNT(p.post_id)), которые он создал (независимо от статуса). Сгруппируйте по username (GROUP BY username). Включите авторов с 0 постов (используйте LEFT JOIN от users к posts).
Запросите представление published_posts_feed: Напишите запрос для выбора постов из представления published_posts_feed, принадлежащих к категории ‘Technology’.
Запросите представление post_comment_summary: Напишите запрос для поиска всех комментариев, связанных с постом под названием ‘Understanding SQL Views’, используя представление post_comment_summary.
Измените представление user_profiles: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление user_profiles, добавив в него также столбец registration_date.
Удалите представление draft_posts_list: Удалите представление draft_posts_list из базы данных (используя DROP VIEW).

Вариант 10: Библиотечная система

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

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

-- Таблица авторов
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    birth_year INT
);

-- Таблица книг
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT REFERENCES authors(author_id),
    isbn VARCHAR(20) UNIQUE,
    genre VARCHAR(50),
    publication_year INT,
    total_copies INT DEFAULT 1, -- Общее количество экземпляров
    available_copies INT DEFAULT 1 CHECK (available_copies <= total_copies AND available_copies >= 0) -- Доступное количество экземпляров
);

-- Таблица читателей
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    join_date DATE DEFAULT CURRENT_DATE, -- Дата регистрации
    membership_status VARCHAR(15) DEFAULT 'Active' -- например, Active (Активен), Expired (Просрочен), Suspended (Приостановлен)
);

-- Таблица выдач
CREATE TABLE loans (
    loan_id SERIAL PRIMARY KEY,
    book_id INT REFERENCES books(book_id),
    member_id INT REFERENCES members(member_id),
    loan_date DATE DEFAULT CURRENT_DATE, -- Дата выдачи
    due_date DATE NOT NULL, -- Срок возврата
    return_date DATE -- Дата возврата (NULL означает, что книга еще не возвращена)
);

-- Примеры данных
INSERT INTO authors (author_name, birth_year) VALUES
('J.R.R. Tolkien', 1892),
('Isaac Asimov', 1920),
('Ursula K. Le Guin', 1929),
('Frank Herbert', 1920);

INSERT INTO books (title, author_id, isbn, genre, publication_year, total_copies, available_copies) VALUES
('The Hobbit', 1, '978-0547928227', 'Fantasy', 1937, 5, 3),
('Foundation', 2, '978-0553293357', 'Science Fiction', 1951, 3, 1),
('A Wizard of Earthsea', 3, '978-0547773742', 'Fantasy', 1968, 4, 4),
('Dune', 4, '978-0441172719', 'Science Fiction', 1965, 2, 0),
('The Left Hand of Darkness', 3, '978-0441478125', 'Science Fiction', 1969, 3, 3);

INSERT INTO members (first_name, last_name, email, join_date, membership_status) VALUES
('Arthur Dent', 'Ford', 'a.dent@galaxy.net', '2022-08-15', 'Active'),
('Zaphod', 'Beeblebrox', 'z.beeblebrox@galaxy.net', '2023-01-20', 'Active'),
('Trillian', 'Astra', 't.astra@galaxy.net', '2022-08-15', 'Expired');

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, 1, '2023-10-10', '2023-10-24', NULL),      -- 'Основание', сейчас на руках
(1, 2, '2023-10-12', '2023-10-26', NULL),      -- 'Хоббит', сейчас на руках
(4, 2, '2023-09-15', '2023-09-29', NULL),      -- 'Дюна', просроченная выдача
(4, 1, '2023-08-01', '2023-08-15', '2023-08-14'); -- 'Дюна', была выдана и возвращена ранее

Задания:

Создайте представление available_books_list: Создайте представление с именем available_books_list, показывающее book_id, title, isbn и author_name для всех книг, у которых available_copies > 0. Используйте соединение (JOIN) таблиц books и authors.
Создайте представление active_member_contacts: Создайте представление с именем active_member_contacts, отображающее member_id, first_name, last_name и email для читателей, у которых membership_status равен ‘Active’.
Создайте представление overdue_loans_alert: Создайте представление с именем overdue_loans_alert, которое выводит loan_id, book_id (при желании можно добавить title через JOIN), member_id, loan_date и due_date для всех выдач, где return_date равно NULL И due_date раньше текущей даты (CURRENT_DATE).
Создайте представление book_details_with_author: Создайте простое представление с именем book_details_with_author, соединяющее таблицы books и authors, чтобы показать book_id, title, isbn, genre, publication_year и author_name.

Создайте представление member_loan_history: Создайте представление с именем member_loan_history, показывающее полное имя читателя (first_name ’ ‘ last_name), title книги, loan_date, due_date и return_date. Это потребует соединения (JOIN) таблиц members, loans и books.

Создайте представление author_book_inventory: Создайте представление с именем author_book_inventory, показывающее author_name каждого автора и общее количество экземпляров книг (SUM(b.total_copies)), связанных с ним в библиотеке. Сгруппируйте результаты по author_name (GROUP BY author_name).
Запросите представление available_books_list: Напишите запрос для выбора всех книг из представления available_books_list с жанром ‘Fantasy’.
Запросите представление member_loan_history: Напишите запрос для поиска всех записей о выдачах в представлении member_loan_history для читателя ‘Arthur Dent Ford’, отображая только те выдачи, которые еще не были возвращены (return_date IS NULL).
Измените представление active_member_contacts: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление active_member_contacts, добавив в него также столбец join_date.
Удалите представление overdue_loans_alert: Удалите представление overdue_loans_alert из базы данных (используя DROP VIEW).

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

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

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

-- Таблица мест проведения
CREATE TABLE venues (
    venue_id SERIAL PRIMARY KEY,
    venue_name VARCHAR(150) NOT NULL,
    address TEXT,
    capacity INT CHECK (capacity > 0)
);

-- Таблица мероприятий
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(200) NOT NULL,
    venue_id INT REFERENCES venues(venue_id),
    event_date TIMESTAMP NOT NULL, -- Дата и время мероприятия
    description TEXT,
    category VARCHAR(50) -- например, Conference (Конференция), Workshop (Семинар), Networking (Нетворкинг), Webinar (Вебинар)
);

-- Таблица участников
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,
    company VARCHAR(100)
);

-- Таблица регистраций
CREATE TABLE registrations (
    registration_id SERIAL PRIMARY KEY,
    event_id INT REFERENCES events(event_id),
    attendee_id INT REFERENCES attendees(attendee_id),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ticket_type VARCHAR(20) DEFAULT 'Standard', -- например, Standard (Стандартный), VIP, Early Bird (Раннее бронирование)
    UNIQUE(event_id, attendee_id) -- Гарантирует, что участник регистрируется только один раз на каждое мероприятие
);

-- Примеры данных
INSERT INTO venues (venue_name, address, capacity) VALUES
('Grand Conference Hall', '123 Main St, Cityville', 500),
('Tech Incubator Hub', '456 Tech Ave, Cityville', 150),
('Downtown Convention Center', '789 Market St, Cityville', 2000);

INSERT INTO events (event_name, venue_id, event_date, description, category) VALUES
('Future of AI Conference', 1, '2024-05-10 09:00:00', 'Exploring advancements in AI and ML.', 'Conference'),
('Web Development Workshop', 2, '2024-05-15 10:00:00', 'Hands-on workshop for modern web tech.', 'Workshop'),
('Digital Marketing Summit', 1, '2024-06-20 09:30:00', 'Strategies for online growth.', 'Conference'),
('Startup Networking Night', 2, '2024-06-25 18:00:00', 'Connect with local entrepreneurs.', 'Networking');

INSERT INTO attendees (first_name, last_name, email, company) VALUES
('Alice', 'Smith', 'alice.s@techcorp.com', 'TechCorp'),
('Bob', 'Johnson', 'bob.j@innovate.com', 'Innovate LLC'),
('Charlie', 'Davis', 'charlie.d@startup.io', 'Startup Inc.'),
('Diana', 'Miller', 'diana.m@freelance.com', NULL);

INSERT INTO registrations (event_id, attendee_id, registration_date, ticket_type) VALUES
(1, 1, '2024-03-01 11:00:00', 'Early Bird'), -- Алиса на конференцию по ИИ
(1, 2, '2024-03-05 14:20:00', 'Standard'),   -- Боб на конференцию по ИИ
(2, 1, '2024-03-10 09:00:00', 'Standard'),   -- Алиса на семинар по веб-разработке
(2, 3, '2024-03-11 16:45:00', 'Standard'),   -- Чарли на семинар по веб-разработке
(3, 2, '2024-04-01 10:15:00', 'VIP'),        -- Боб на саммит по маркетингу
(4, 4, '2024-05-01 12:00:00', 'Standard');   -- Диана на нетворкинг-вечер

Задания:

Создайте представление upcoming_events_schedule: Создайте представление с именем upcoming_events_schedule, показывающее event_name, event_date, venue_name и address для всех мероприятий, запланированных на сегодня или позже (event_date >= CURRENT_DATE). Используйте соединение (JOIN) таблиц events и venues.

Создайте представление attendee_directory: Создайте представление с именем attendee_directory, показывающее attendee_id, полное имя (first_name ’ ‘ last_name), email и company для всех участников.

Создайте представление events_at_tech_hub: Создайте представление с именем events_at_tech_hub, которое выводит event_name, category и event_date для всех мероприятий, проходящих в месте проведения (‘venue_name’) ‘Tech Incubator Hub’.
Создайте представление conference_attendee_list: Создайте представление с именем conference_attendee_list, показывающее event_name, first_name, last_name и email участников специально для мероприятий, у которых category равна ‘Conference’. Требуется соединение (JOIN) таблиц registrations, attendees и events.
Создайте представление detailed_registration_report: Создайте представление с именем detailed_registration_report, показывающее registration_id, event_name, event_date, полное имя участника, email участника, registration_date и ticket_type. Это потребует соединения (JOIN) таблиц registrations, events и attendees.
Создайте представление event_registration_count: Создайте представление с именем event_registration_count, показывающее event_name каждого мероприятия и общее количество зарегистрированных участников (COUNT(r.attendee_id)). Сгруппируйте результаты по event_name (GROUP BY event_name). Включите мероприятия с нулевым количеством регистраций, если это применимо (используйте соответствующий JOIN).
Запросите представление upcoming_events_schedule: Напишите запрос для выбора мероприятий из представления upcoming_events_schedule, которые проходят в ‘Grand Conference Hall’.
Запросите представление detailed_registration_report: Напишите запрос для поиска всех регистраций в представлении detailed_registration_report для мероприятия ‘Future of AI Conference’, показывая только те, у которых ticket_type равен ‘Early Bird’.
Измените представление attendee_directory: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление attendee_directory так, чтобы email отображался перед названием компании (company).
Удалите представление events_at_tech_hub: Удалите представление events_at_tech_hub из базы данных (используя DROP VIEW).

Вариант 12: Система учета абонементов в спортзал

Сценарий: Вы управляете базой данных для фитнес-центра. Система отслеживает членов клуба (members), типы их абонементов (membership types), предлагаемые фитнес-классы (fitness classes), тренеров (trainers), проводящих занятия, и посещаемость занятий членами клуба (member attendance). Представления (views) помогут персоналу управлять абонементами, расписаниями и отслеживать популярность классов.

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

-- Таблица типов абонементов
CREATE TABLE membership_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL UNIQUE, -- например, Basic (Базовый), Premium (Премиум), Annual (Годовой), Off-Peak (Внепиковый)
    monthly_fee NUMERIC(6, 2) NOT NULL
);

-- Таблица членов клуба
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    join_date DATE DEFAULT CURRENT_DATE,
    type_id INT REFERENCES membership_types(type_id),
    status VARCHAR(15) DEFAULT 'Active' -- например, Active (Активен), Inactive (Неактивен), Frozen (Заморожен)
);

-- Таблица тренеров
CREATE TABLE trainers (
    trainer_id SERIAL PRIMARY KEY,
    trainer_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(100) -- например, Yoga (Йога), Strength Training (Силовые тренировки), Cardio (Кардио)
);

-- Таблица классов (занятий)
CREATE TABLE classes (
    class_id SERIAL PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL,
    trainer_id INT REFERENCES trainers(trainer_id),
    schedule_day VARCHAR(10), -- например, Monday (Понедельник), Tuesday (Вторник)
    schedule_time TIME,
    duration_minutes INT DEFAULT 60,
    max_capacity INT
);

-- Таблица журнала посещаемости
CREATE TABLE attendance_log (
    log_id SERIAL PRIMARY KEY,
    member_id INT REFERENCES members(member_id),
    class_id INT REFERENCES classes(class_id),
    attendance_date DATE DEFAULT CURRENT_DATE -- Записывает конкретную дату посещения членом клуба
);

-- Примеры данных
INSERT INTO membership_types (type_name, monthly_fee) VALUES
('Basic', 30.00),
('Premium', 50.00),
('Annual', 500.00); -- Допустим для простоты, что годовой оплачивается вперед, но отслеживаем 'тип'

INSERT INTO members (first_name, last_name, email, join_date, type_id, status) VALUES
('Clark', 'Kent', 'clark.k@dailyplanet.com', '2023-01-10', 2, 'Active'),
('Lois', 'Lane', 'lois.l@dailyplanet.com', '2023-01-10', 2, 'Active'),
('Bruce', 'Wayne', 'bruce.w@waynecorp.com', '2022-11-01', 3, 'Active'),
('Diana', 'Prince', 'diana.p@museum.org', '2023-03-15', 1, 'Frozen');

INSERT INTO trainers (trainer_name, specialization) VALUES
('John Doe', 'Strength Training'),
('Jane Smith', 'Yoga & Pilates'),
('Robert Paulson', 'Cardio & HIIT');

INSERT INTO classes (class_name, trainer_id, schedule_day, schedule_time, duration_minutes, max_capacity) VALUES
('Morning Yoga', 2, 'Monday', '08:00:00', 60, 20),
('Power Lifting', 1, 'Tuesday', '18:00:00', 75, 15),
('HIIT Blast', 3, 'Wednesday', '19:00:00', 45, 25),
('Evening Pilates', 2, 'Thursday', '18:30:00', 60, 20),
('Circuit Training', 1, 'Friday', '17:00:00', 60, 20);

INSERT INTO attendance_log (member_id, class_id, attendance_date) VALUES
(1, 1, '2023-10-02'), -- Кларк посетил Morning Yoga 2 октября
(2, 1, '2023-10-02'), -- Лоис посетила Morning Yoga 2 октября
(1, 2, '2023-10-03'), -- Кларк посетил Power Lifting 3 октября
(3, 5, '2023-10-06'), -- Брюс посетил Circuit Training 6 октября
(1, 1, '2023-10-09'); -- Кларк посетил Morning Yoga 9 октября

Задания:

Создайте представление active_members_info: Создайте представление с именем active_members_info, показывающее member_id, first_name, last_name, email и type_name для всех членов клуба, у которых status равен ‘Active’. Используйте соединение (JOIN) таблиц members и membership_types.
Создайте представление member_primary_contact: Создайте представление с именем member_primary_contact, показывающее только first_name, last_name и email всех членов клуба, независимо от их статуса.
Создайте представление yoga_pilates_schedule: Создайте представление с именем yoga_pilates_schedule, которое выводит class_name, schedule_day, schedule_time и trainer_name для классов, проводимых тренерами со специализацией ‘Yoga & Pilates’ (или конкретно ‘Jane Smith’). Используйте соединение (JOIN) таблиц classes и trainers.
Создайте представление class_schedule_detailed: Создайте представление с именем class_schedule_detailed, которое отображает class_name, trainer_name, specialization, schedule_day, schedule_time, duration_minutes и max_capacity. Используйте соединение (JOIN) таблиц classes и trainers.
Создайте представление member_attendance_history: Создайте представление с именем member_attendance_history, показывающее полное имя члена клуба (full name), class_name и attendance_date. Требуется соединение (JOIN) таблиц members, attendance_log и classes.
Создайте представление class_popularity_report: Создайте представление с именем class_popularity_report, показывающее class_name каждого класса и общее количество зарегистрированных посещений (COUNT(al.log_id)) для этого класса. Сгруппируйте результаты по class_name (GROUP BY class_name).
Запросите представление active_members_info: Напишите запрос для выбора всех членов клуба из представления active_members_info, у которых тип абонемента (‘type_name’) — ‘Premium’.
Запросите представление member_attendance_history: Напишите запрос для поиска всех записей о посещениях в представлении member_attendance_history для ‘Clark Kent’, отсортированных по attendance_date в порядке убывания (descending).
Измените представление member_primary_contact: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление member_primary_contact, добавив в него также столбец join_date члена клуба.
Удалите представление yoga_pilates_schedule: Удалите представление yoga_pilates_schedule из базы данных (используя DROP VIEW).

Вариант 13: База данных рецептов

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

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

-- Таблица кухонь мира
CREATE TABLE cuisines (
    cuisine_id SERIAL PRIMARY KEY,
    cuisine_name VARCHAR(50) NOT NULL UNIQUE -- например, Italian (Итальянская), Mexican (Мексиканская), Indian (Индийская)
);

-- Таблица ингредиентов
CREATE TABLE ingredients (
    ingredient_id SERIAL PRIMARY KEY,
    ingredient_name VARCHAR(100) NOT NULL UNIQUE,
    category VARCHAR(50) -- например, Vegetable (Овощ), Meat (Мясо), Spice (Специя), Dairy (Молочные продукты)
);

-- Таблица рецептов
CREATE TABLE recipes (
    recipe_id SERIAL PRIMARY KEY,
    recipe_name VARCHAR(150) NOT NULL,
    cuisine_id INT REFERENCES cuisines(cuisine_id),
    prep_time_minutes INT, -- Время подготовки
    cook_time_minutes INT, -- Время приготовления
    instructions TEXT
);

-- Таблица ингредиентов рецепта (связь Многие-ко-многим между Рецептами и Ингредиентами)
CREATE TABLE recipe_ingredients (
    recipe_ingredient_id SERIAL PRIMARY KEY,
    recipe_id INT REFERENCES recipes(recipe_id),
    ingredient_id INT REFERENCES ingredients(ingredient_id),
    quantity VARCHAR(50) -- например, '2 стакана', '1 ч.л.', '100г'
);

-- Таблица пользовательских оценок
CREATE TABLE user_ratings (
    rating_id SERIAL PRIMARY KEY,
    recipe_id INT REFERENCES recipes(recipe_id),
    user_id INT, -- Предполагается, что ID пользователей существуют в другом месте, здесь упрощено
    rating INT CHECK (rating >= 1 AND rating <= 5), -- например, от 1 до 5 звезд
    rating_date DATE DEFAULT CURRENT_DATE
);


-- Примеры данных
INSERT INTO cuisines (cuisine_name) VALUES
('Italian'), ('Mexican'), ('Indian');

INSERT INTO ingredients (ingredient_name, category) VALUES
('Tomato', 'Vegetable'), ('Onion', 'Vegetable'), ('Garlic', 'Vegetable'),
('Pasta', 'Grain'), ('Chicken Breast', 'Meat'), ('Chili Powder', 'Spice'),
('Cumin', 'Spice'), ('Olive Oil', 'Oil'), ('Cheese', 'Dairy');

INSERT INTO recipes (recipe_name, cuisine_id, prep_time_minutes, cook_time_minutes, instructions) VALUES
('Spaghetti Bolognese', 1, 20, 40, 'Brown meat, add onions, garlic, tomatoes... Simmer... Serve over pasta.'),
('Chicken Tacos', 2, 15, 20, 'Cook chicken with spices... Warm tortillas... Assemble tacos.'),
('Simple Pasta Pomodoro', 1, 10, 15, 'Sauté garlic in olive oil, add tomatoes... Simmer... Toss with pasta.'),
('Chicken Curry', 3, 25, 35, 'Sauté onions, garlic, ginger... Add spices, chicken, tomatoes, coconut milk... Simmer.');

INSERT INTO recipe_ingredients (recipe_id, ingredient_id, quantity) VALUES
(1, 1, '1 can'), (1, 2, '1 medium'), (1, 3, '2 cloves'), (1, 4, '400g'), (1, 5, '500g'), (1, 8, '2 tbsp'),
(2, 5, '2 medium'), (2, 2, '1 small'), (2, 6, '1 tbsp'), (2, 7, '1 tsp'),
(3, 1, '1 large can'), (3, 3, '3 cloves'), (3, 8, '3 tbsp'), (3, 4, '300g'), (3, 9, '50g grated'),
(4, 5, '600g'), (4, 2, '1 large'), (4, 3, '4 cloves'), (4, 7, '1.5 tsp');

INSERT INTO user_ratings (recipe_id, user_id, rating, rating_date) VALUES
(1, 101, 5, '2023-10-01'),
(1, 102, 4, '2023-10-02'),
(2, 101, 4, '2023-10-05'),
(3, 103, 5, '2023-10-06'),
(1, 103, 4, '2023-11-01');

Задания:

Создайте представление recipe_summary: Создайте представление с именем recipe_summary, которое показывает recipe_name, cuisine_name, prep_time_minutes и cook_time_minutes. Используйте соединение (JOIN) таблиц recipes и cuisines.
Создайте представление ingredient_list: Создайте представление с именем ingredient_list, показывающее только ingredient_name и category для всех ингредиентов, отсортированное по category, а затем по ingredient_name.
Создайте представление quick_italian_recipes: Создайте представление с именем quick_italian_recipes, перечисляющее recipe_name для итальянских рецептов (cuisine_name = ‘Italian’), где общее время (prep_time_minutes + cook_time_minutes) меньше 45 минут.
Создайте представление recipe_ingredient_details: Создайте представление с именем recipe_ingredient_details, которое отображает recipe_name, ingredient_name и quantity (количество), необходимое для каждого ингредиента в рецепте. Используйте соединение (JOIN) таблиц recipes, recipe_ingredients и ingredients.
Создайте представление average_recipe_ratings: Создайте представление с именем average_recipe_ratings, показывающее recipe_name и среднюю оценку (AVG(rating)), округленную до 2 знаков после запятой, с псевдонимом average_rating. Включайте только те рецепты, у которых есть хотя бы одна оценка. Сгруппируйте результаты по recipe_name (GROUP BY recipe_name).
Создайте представление recipes_using_chicken: Создайте представление с именем recipes_using_chicken, которое выводит recipe_name для всех рецептов, использующих ‘Chicken Breast’ (Куриная грудка). Используйте соединение (JOIN) таблиц recipes, recipe_ingredients и ingredients.
Запросите представление recipe_summary: Напишите запрос для выбора всех рецептов из представления recipe_summary, принадлежащих к ‘Mexican’ кухне.
Запросите представление average_recipe_ratings: Напишите запрос для поиска рецептов в представлении average_recipe_ratings со средней оценкой (average_rating) больше 4.0.
Измените представление ingredient_list: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление ingredient_list, добавив в него также столбец ingredient_id.
Удалите представление quick_italian_recipes: Удалите представление quick_italian_recipes из базы данных (используя DROP VIEW).

Вариант 14: Агентство недвижимости

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

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

-- Таблица агентов
CREATE TABLE agents (
    agent_id SERIAL PRIMARY KEY,
    agent_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    hire_date DATE
);

-- Таблица объектов недвижимости
CREATE TABLE properties (
    property_id SERIAL PRIMARY KEY,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(50),
    zip_code VARCHAR(10),
    property_type VARCHAR(50), -- например, House (Дом), Apartment (Квартира), Condo (Кондоминиум)
    bedrooms INT,
    bathrooms NUMERIC(3,1), -- например, 2.5 для 2 полных ванных комнат и 1 туалета
    square_footage INT,
    listing_price NUMERIC(12, 2),
    listing_date DATE,
    status VARCHAR(20) DEFAULT 'For Sale', -- например, For Sale (В продаже), Under Contract (В процессе сделки), Sold (Продано)
    agent_id INT REFERENCES agents(agent_id) -- Агент, ответственный за объявление
);

-- Таблица клиентов
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    client_type VARCHAR(10) -- 'Buyer' (Покупатель) или 'Seller' (Продавец)
);

-- Таблица просмотров
CREATE TABLE viewings (
    viewing_id SERIAL PRIMARY KEY,
    property_id INT REFERENCES properties(property_id),
    client_id INT REFERENCES clients(client_id), -- Клиент, который просматривал
    agent_id INT REFERENCES agents(agent_id), -- Агент, который проводил просмотр
    viewing_date TIMESTAMP,
    client_feedback TEXT
);

-- Примеры данных
INSERT INTO agents (agent_name, email, phone, hire_date) VALUES
('Alice Green', 'alice.g@realestate.com', '555-1111', '2019-03-15'),
('Bob White', 'bob.w@realestate.com', '555-2222', '2020-07-01'),
('Carol Black', 'carol.b@realestate.com', '555-3333', '2021-11-20');

INSERT INTO properties (address, city, zip_code, property_type, bedrooms, bathrooms, square_footage, listing_price, listing_date, status, agent_id) VALUES
('123 Oak St', 'Metropolis', '12345', 'House', 3, 2.0, 1800, 350000.00, '2023-08-01', 'For Sale', 1),
('456 Maple Ave', 'Metropolis', '12346', 'Apartment', 2, 1.0, 950, 180000.00, '2023-09-10', 'For Sale', 2),
('789 Pine Ln', 'Gotham', '67890', 'House', 4, 2.5, 2400, 480000.00, '2023-07-15', 'Sold', 1),
('101 Birch Rd', 'Metropolis', '12345', 'Condo', 2, 2.0, 1200, 250000.00, '2023-10-05', 'Under Contract', 3);

INSERT INTO clients (client_name, email, phone, client_type) VALUES
('David King', 'david.k@email.com', '555-4444', 'Buyer'),
('Eva Queen', 'eva.q@email.com', '555-5555', 'Seller'), -- Продавец дома 123 Oak St (подразумевается)
('Frank Prince', 'frank.p@email.com', '555-6666', 'Buyer');

INSERT INTO viewings (property_id, client_id, agent_id, viewing_date, client_feedback) VALUES
(1, 1, 1, '2023-08-10 14:00:00', 'Liked the backyard, kitchen needs update.'),
(2, 1, 2, '2023-09-15 11:00:00', 'Good location, but a bit small.'),
(1, 3, 1, '2023-08-12 16:30:00', 'Very interested, considering an offer.'),
(4, 3, 3, '2023-10-10 10:00:00', 'Loved the condo amenities.');

Задания:

Создайте представление active_listings: Создайте представление с именем active_listings, показывающее property_id, address, city, listing_price и agent_name для всех объектов недвижимости со статусом ‘For Sale’. Используйте соединение (JOIN) таблиц properties и agents.
Создайте представление agent_contact_list: Создайте представление с именем agent_contact_list, показывающее только agent_name, email и phone для всех агентов.
Создайте представление large_homes_metropolis: Создайте представление с именем large_homes_metropolis, которое выводит address и square_footage для объектов недвижимости в городе ‘Metropolis’, имеющих 3 или более спален (bedrooms) и площадь (square_footage) более 2000.
Создайте представление viewing_schedule: Создайте представление с именем viewing_schedule, которое отображает viewing_date, property_address (из properties.address), client_name и agent_name агента, проводившего просмотр. Используйте соединение (JOIN) таблиц viewings, properties, clients и agents.
Создайте представление agent_listing_count: Создайте представление с именем agent_listing_count, показывающее agent_name каждого агента и общее количество объектов недвижимости (COUNT(p.property_id)), которые он в данный момент выставляет на продажу (статус = ‘For Sale’). Сгруппируйте результаты по agent_name (GROUP BY agent_name). Включите агентов, даже если у них 0 активных объявлений.
Создайте представление client_viewing_history: Создайте представление с именем client_viewing_history, которое выводит client_name и address объектов недвижимости, которые клиент просматривал, вместе с viewing_date. Используйте соединение (JOIN) таблиц clients, viewings и properties.
Запросите представление active_listings: Напишите запрос для выбора всех объявлений из представления active_listings, которыми занимается агент ‘Alice Green’.
Запросите представление viewing_schedule: Напишите запрос для поиска всех просмотров в представлении viewing_schedule, которые произошли после ‘2023-09-01’, отсортированных по дате.
Измените представление agent_contact_list: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление agent_contact_list, добавив в него также столбец hire_date.
Удалите представление large_homes_metropolis: Удалите представление large_homes_metropolis из базы данных (используя DROP VIEW).

Вариант 15: Производственная линия

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

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

-- Таблица компонентов
CREATE TABLE components (
    component_id SERIAL PRIMARY KEY,
    component_name VARCHAR(100) NOT NULL UNIQUE,
    supplier VARCHAR(100),
    cost NUMERIC(8, 2)
);

-- Таблица продуктов
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_sku VARCHAR(50) NOT NULL UNIQUE, -- Артикул (Stock Keeping Unit)
    product_name VARCHAR(150) NOT NULL,
    assembly_time_minutes INT -- Примерное время сборки в минутах
);

-- Таблица спецификаций (Связывает Продукты и Компоненты)
CREATE TABLE bill_of_materials (
    bom_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    component_id INT REFERENCES components(component_id),
    quantity_required INT NOT NULL
);

-- Таблица производственных линий
CREATE TABLE production_lines (
    line_id SERIAL PRIMARY KEY,
    line_name VARCHAR(50) NOT NULL UNIQUE, -- например, Линия А, Линия Б
    location VARCHAR(100) -- например, Здание 1, Этаж 2
);

-- Таблица журнала производства
CREATE TABLE production_log (
    log_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    line_id INT REFERENCES production_lines(line_id),
    units_produced INT NOT NULL,
    production_date DATE DEFAULT CURRENT_DATE,
    shift INT -- например, 1, 2, 3 (Смена)
);

-- Таблица контроля качества
CREATE TABLE quality_control (
    qc_id SERIAL PRIMARY KEY,
    log_id INT REFERENCES production_log(log_id), -- Связь с конкретным производственным запуском
    check_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(10) NOT NULL, -- 'Pass' (Прошел) или 'Fail' (Не прошел)
    notes TEXT
);

-- Примеры данных
INSERT INTO components (component_name, supplier, cost) VALUES
('Casing A', 'SupplierX', 5.50),
('Circuit Board V1', 'SupplierY', 12.75),
('Power Supply Unit', 'SupplierX', 8.00),
('Screw Pack M3', 'SupplierZ', 0.50),
('Display Panel', 'SupplierY', 25.00);

INSERT INTO products (product_sku, product_name, assembly_time_minutes) VALUES
('WIDGET-001', 'Standard Widget', 30),
('GADGET-X', 'Advanced Gadget', 75),
('DOODAD-BLUE', 'Blue Doodad', 20);

INSERT INTO bill_of_materials (product_id, component_id, quantity_required) VALUES
(1, 1, 1), (1, 2, 1), (1, 3, 1), (1, 4, 4), -- Компоненты для 'Widget'
(2, 1, 1), (2, 2, 2), (2, 3, 1), (2, 4, 8), (2, 5, 1), -- Компоненты для 'Gadget'
(3, 1, 1), (3, 4, 2); -- Компоненты для 'Doodad'

INSERT INTO production_lines (line_name, location) VALUES
('Assembly Line 1', 'Building A - West Wing'),
('Assembly Line 2', 'Building B - East Wing'),
('Testing Bench', 'Building A - QC Area');

INSERT INTO production_log (product_id, line_id, units_produced, production_date, shift) VALUES
(1, 1, 100, '2023-11-01', 1),
(2, 2, 50, '2023-11-01', 1),
(1, 1, 120, '2023-11-01', 2),
(3, 1, 200, '2023-11-02', 1);

INSERT INTO quality_control (log_id, check_timestamp, status, notes) VALUES
(1, '2023-11-01 10:00:00', 'Pass', 'Sample check ok.'), -- Проверка образца пройдена.
(2, '2023-11-01 11:30:00', 'Pass', NULL),
(3, '2023-11-01 18:00:00', 'Fail', '3 units had cosmetic defects.'), -- У 3 единиц обнаружены косметические дефекты.
(3, '2023-11-01 18:05:00', 'Pass', 'Reworked units passed re-inspection.'), -- Доработанные единицы прошли повторную проверку.
(4, '2023-11-02 09:45:00', 'Pass', NULL);

Задания:

Создайте представление product_component_list: Создайте представление с именем product_component_list, которое показывает product_name, component_name и quantity_required для каждого продукта. Используйте соединение (JOIN) таблиц products, bill_of_materials и components.
Создайте представление component_suppliers: Создайте представление с именем component_suppliers, отображающее только component_name и supplier для всех компонентов, отсортированное по поставщику (supplier).
Создайте представление failed_qc_checks: Создайте представление с именем failed_qc_checks, которое выводит log_id, check_timestamp и notes для всех проверок контроля качества со статусом (status) ‘Fail’.
Создайте представление daily_production_summary: Создайте представление с именем daily_production_summary, которое отображает production_date, product_name, line_name и units_produced. Используйте соединение (JOIN) таблиц production_log, products и production_lines.
Создайте представление product_component_cost: Создайте представление с именем product_component_cost, показывающее product_name и общую стоимость его компонентов (сумма bom.quantity_required * c.cost), с псевдонимом total_component_cost. Сгруппируйте результаты по product_name (GROUP BY product_name). Используйте соединение (JOIN) таблиц products, bill_of_materials и components.
Создайте представление line_production_totals: Создайте представление с именем line_production_totals, которое показывает line_name и общее количество произведенных единиц (units_produced) для каждой производственной линии по всем датам/продуктам, зарегистрированным в журнале. Сгруппируйте результаты по line_name (GROUP BY line_name).
Запросите представление product_component_list: Напишите запрос для выбора всех компонентов, необходимых для ‘Advanced Gadget’, из представления product_component_list.
Запросите представление daily_production_summary: Напишите запрос для поиска всех производственных записей в представлении daily_production_summary за дату ‘2023-11-01’.
Измените представление component_suppliers: Используйте CREATE OR REPLACE VIEW, чтобы изменить представление component_suppliers, добавив в него также столбец стоимости компонента (cost).
Удалите представление failed_qc_checks: Удалите представление failed_qc_checks из базы данных (используя DROP VIEW).

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

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