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

Использование агрегатных функций.

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

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS customers;

-- Создаем таблицу 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,
    registration_date DATE DEFAULT CURRENT_DATE,
    city VARCHAR(50)
);

-- Создаем таблицу Books (Книги)
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100) NOT NULL,
    genre VARCHAR(50),
    price NUMERIC(8, 2) CHECK (price > 0),
    stock_count INT DEFAULT 0
);

-- Создаем таблицу Purchases (Покупки)
CREATE TABLE purchases (
    purchase_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    book_id INT NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quantity INT CHECK (quantity > 0),
    total_price NUMERIC(10, 2), -- Потенциально вычисляемое, но хранится здесь для простоты
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Вставляем примерные данные в Customers
INSERT INTO customers (first_name, last_name, email, registration_date, city) VALUES
('Arthur', 'Dent', 'adent@galaxy.net', '2022-01-15', 'London'),
('Ford', 'Prefect', 'fprefect@guide.com', '2022-01-15', 'Betelgeuse'),
('Zaphod', 'Beeblebrox', 'zbeeblebrox@gov.glx', '2022-03-10', 'Maximegalon'),
('Trillian', 'Astra', 'tricia@earth.exp', '2022-05-20', 'London'),
('Marvin', 'Android', 'marvin@sirius.corp', '2021-12-01', NULL); -- Город NULL

-- Вставляем примерные данные в Books
INSERT INTO books (title, author, genre, price, stock_count) VALUES
('The Hitchhiker''s Guide', 'Douglas Adams', 'Science Fiction', 12.99, 50),
('Dune', 'Frank Herbert', 'Science Fiction', 15.50, 35),
('Pride and Prejudice', 'Jane Austen', 'Romance', 9.99, 60),
('1984', 'George Orwell', 'Dystopian', 11.25, 40),
('Foundation', 'Isaac Asimov', 'Science Fiction', 14.00, 25),
('Moby Dick', 'Herman Melville', 'Adventure', 10.50, NULL); -- Количество на складе NULL

-- Вставляем примерные данные в Purchases
INSERT INTO purchases (customer_id, book_id, quantity, total_price, purchase_date) VALUES
(1, 1, 1, 12.99, '2023-01-20 10:00:00'),
(2, 1, 1, 12.99, '2023-01-21 11:30:00'),
(1, 3, 2, 19.98, '2023-02-10 14:00:00'),
(3, 2, 1, 15.50, '2023-02-15 09:45:00'),
(4, 4, 1, 11.25, '2023-03-01 16:20:00'),
(1, 2, 1, 15.50, '2023-03-05 12:00:00'),
(2, 5, 3, 42.00, '2023-03-10 18:00:00'),
(5, 1, 1, 12.99, '2023-03-12 10:15:00'),
(3, 5, 1, 14.00, '2023-04-01 11:00:00'),
(4, 1, 1, 12.99, '2023-04-05 15:30:00'); -- Покупатель 4 снова купил книгу 1

Задачи:

Подсчитайте общее количество зарегистрированных покупателей.
Вычислите общее количество всех книг на складе (игнорируйте книги с NULL в поле stock_count).
Найдите среднюю цену книг в жанре ‘Science Fiction’.
Определите минимальную и максимальную цену книги, доступную в магазине.
Подсчитайте количество уникальных жанров книг (игнорируйте NULL значения в поле жанра).
Вычислите общее количество книг, купленных каждым покупателем. Отобразите customer_id и total_books_purchased (общее_количество_купленных_книг). Отсортируйте по customer_id.
Найдите среднюю total_price (общую стоимость) покупки для каждой книги. Отобразите book_id и average_purchase_price (средняя_цена_покупки).
Подсчитайте количество книг, написанных каждым автором. Отобразите author и book_count (количество_книг). Отсортируйте по book_count в порядке убывания (descending).
Найдите покупателей, совершивших более 2 покупок. Отобразите customer_id и number_of_purchases (количество_покупок).
Найдите жанры книг, где средняя цена книги превышает $12.00. Отобразите genre и average_genre_price (средняя_цена_жанра).

Вариант 2: Анализ записи студентов на курсы в университете

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS students;

-- Создаем таблицу Students (Студенты)
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    major VARCHAR(50), -- Специальность
    enrollment_year INT NOT NULL, -- Год зачисления
    gpa NUMERIC(3, 2) CHECK (gpa >= 0.0 AND gpa <= 4.0) -- Средний балл
);

-- Создаем таблицу Courses (Курсы)
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_code VARCHAR(10) UNIQUE NOT NULL, -- Код курса
    course_title VARCHAR(100) NOT NULL, -- Название курса
    department VARCHAR(50) NOT NULL, -- Факультет/кафедра
    credits INT CHECK (credits > 0) -- Кредиты (зачетные единицы)
);

-- Создаем таблицу Enrollments (Записи на курсы)
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    semester VARCHAR(20) NOT NULL, -- например, 'Fall 2023' (Осень 2023)
    grade CHAR(2), -- например, 'A+', 'B', 'C-', 'F', NULL если курс не завершен
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- Вставляем примерные данные в Students
INSERT INTO students (student_name, major, enrollment_year, gpa) VALUES
('Alice Wonderland', 'Computer Science', 2021, 3.8),
('Bob The Builder', 'Engineering', 2020, 3.1),
('Charlie Chaplin', 'Film Studies', 2022, 3.5),
('Diana Prince', 'Political Science', 2021, 3.9),
('Evan Almighty', NULL, 2023, 3.0), -- Специальность NULL
('Fiona Shrek', 'Engineering', 2020, 3.3);

-- Вставляем примерные данные в Courses
INSERT INTO courses (course_code, course_title, department, credits) VALUES
('CS101', 'Intro to Programming', 'Computer Science', 4),
('ENG201', 'Thermodynamics', 'Engineering', 3),
('FS101', 'Intro to Film', 'Film Studies', 3),
('PS305', 'International Relations', 'Political Science', 3),
('CS340', 'Databases', 'Computer Science', 4),
('ENG310', 'Fluid Mechanics', 'Engineering', 4);

-- Вставляем примерные данные в Enrollments
INSERT INTO enrollments (student_id, course_id, semester, grade) VALUES
(1, 1, 'Fall 2021', 'A'),
(1, 5, 'Spring 2022', 'A-'),
(2, 2, 'Spring 2021', 'B+'),
(2, 6, 'Fall 2021', 'B'),
(3, 3, 'Fall 2022', 'A'),
(4, 4, 'Spring 2022', 'A+'),
(4, 1, 'Fall 2022', 'B'), -- Диана записалась на CS101
(1, 2, 'Fall 2022', 'B-'), -- Алиса записалась на ENG201
(2, 5, 'Spring 2023', 'C+'), -- Боб записался на Databases (CS340)
(5, 1, 'Fall 2023', NULL), -- Эван записался, оценка ожидается
(6, 2, 'Spring 2021', 'A-'),
(6, 6, 'Fall 2021', 'B+');

Задачи:

Подсчитайте общее количество студентов, обучающихся в университете.
Вычислите общее количество кредитов (зачетных единиц), предлагаемых всеми курсами.
Найдите средний GPA (средний балл) всех студентов (игнорируйте студентов с NULL GPA, хотя таких нет в примере).
Определите самый ранний и самый поздний год зачисления студентов.
Подсчитайте количество уникальных специальностей студентов (игнорируйте NULL значения в поле major).
Вычислите количество курсов, на которые записан или которые уже прошел каждый студент. Отобразите student_id и course_count (количество_курсов).
Найдите количество студентов, записанных на каждый курс в семестре ‘Fall 2022’. Отобразите course_id и student_count (количество_студентов).
Вычислите общее количество кредитов (зачетных единиц), которые студенты проходят/прошли для каждой специальности. Отобразите major и total_credits (всего_кредитов). (Подсказка: Вам может понадобиться суммировать кредиты (credits) на основе записей на курсы (enrollments)).
Найдите курсы, на которые было записано более 2 студентов за все семестры. Отобразите course_id и enrollment_count (количество_записей).
Найдите факультеты/кафедры (department), где среднее количество кредитов (зачетных единиц) на курс больше 3. Отобразите department и average_credits (среднее_количество_кредитов).

Вариант 3: Анализ управления проектами и задачами

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS team_members;
DROP TABLE IF EXISTS projects;

-- Создаем таблицу Projects
CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) UNIQUE NOT NULL,
    client_name VARCHAR(100),
    start_date DATE NOT NULL,
    deadline DATE
);

-- Создаем таблицу Team Members
CREATE TABLE team_members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL, -- например, 'Developer', 'Designer', 'Manager'
    hourly_rate NUMERIC(8, 2) CHECK (hourly_rate >= 0)
);

-- Создаем таблицу Tasks
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INT NOT NULL,
    assigned_member_id INT, -- Может быть NULL, если задача не назначена
    task_description TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'ToDo', -- например, 'ToDo', 'InProgress', 'Done', 'Blocked'
    estimated_hours NUMERIC(5, 2) CHECK (estimated_hours > 0),
    actual_hours NUMERIC(5, 2), -- NULL до завершения или записи часов
    creation_date DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id),
    FOREIGN KEY (assigned_member_id) REFERENCES team_members(member_id)
);

-- Вставляем пример данных в Projects
INSERT INTO projects (project_name, client_name, start_date, deadline) VALUES
('Website Redesign', 'Alpha Corp', '2023-01-15', '2023-06-30'),
('Mobile App Dev', 'Beta LLC', '2023-03-01', '2023-09-30'),
('Data Migration', 'Gamma Inc', '2023-02-01', NULL), -- Без крайнего срока
('Marketing Campaign', 'Alpha Corp', '2023-04-10', '2023-07-31');

-- Вставляем пример данных в Team Members
INSERT INTO team_members (member_name, email, role, hourly_rate) VALUES
('Neo Anderson', 'neo@matrix.dev', 'Developer', 75.00),
('Trinity Moss', 'trinity@matrix.dev', 'Developer', 80.00),
('Morpheus Fishburne', 'morpheus@oracle.net', 'Manager', 90.00),
('Cypher Reagan', 'cypher@neb.ship', 'Designer', 65.00),
('Tank Dozer', 'tank@operator.io', 'Developer', 70.00);

-- Вставляем пример данных в Tasks
INSERT INTO tasks (project_id, assigned_member_id, task_description, status, estimated_hours, actual_hours, creation_date) VALUES
(1, 1, 'Develop homepage layout', 'Done', 20.0, 22.5, '2023-01-20'),
(1, 4, 'Design color palette', 'Done', 8.0, 7.0, '2023-01-18'),
(1, 2, 'Implement user login', 'InProgress', 16.0, NULL, '2023-02-10'),
(2, 1, 'Setup project structure', 'Done', 10.0, 9.5, '2023-03-05'),
(2, 2, 'Develop API endpoints', 'InProgress', 40.0, NULL, '2023-03-15'),
(2, 5, 'Implement push notifications', 'ToDo', 24.0, NULL, '2023-04-01'),
(3, 1, 'Analyze source schema', 'Done', 12.0, 15.0, '2023-02-05'),
(3, 5, 'Develop migration scripts', 'InProgress', 30.0, 10.5, '2023-02-20'), -- Частично учтенные часы
(3, NULL, 'Perform final data validation', 'ToDo', 16.0, NULL, '2023-03-01'), -- Неназначенная задача
(4, 4, 'Create ad visuals', 'ToDo', 25.0, NULL, '2023-04-12'),
(1, 5, 'Fix CSS bugs', 'ToDo', 8.0, NULL, '2023-04-15');

Задачи:

Подсчитайте общее количество активных проектов (предполагается, что все проекты в таблице активны).
Рассчитайте сумму estimated_hours для всех задач по всем проектам.
Найдите среднюю hourly_rate для сотрудников с ролью ‘Developer’.
Определите самую раннюю start_date проекта и самый поздний deadline проекта (игнорируйте NULL значения для deadline).
Подсчитайте количество уникальных ролей членов команды.
Рассчитайте количество задач, назначенных каждому члену команды (игнорируйте неназначенные задачи). Отобразите member_id и task_count. Отсортируйте по task_count по убыванию.
Найдите общее количество actual_hours, зафиксированных для каждого проекта. Отобразите project_id и total_actual_hours. Включайте только те задачи, у которых actual_hours не равно NULL.
Рассчитайте среднее значение estimated_hours для задач, сгруппированных по их status. Отобразите status и average_estimated_hours.
Найдите проекты, которым назначено более 3 задач. Отобразите project_id и number_of_tasks.
Найдите членов команды, которые зафиксировали (actual_hours) более 20 часов в общей сложности по всем назначенным им задачам. Отобразите member_id, member_name и total_logged_hours.

Вариант 4: Анализ отзывов о товарах в электронной коммерции

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

-- Создаем таблицу Users
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE,
    country VARCHAR(50)
);

-- Создаем таблицу Products
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    category VARCHAR(50) NOT NULL,
    brand VARCHAR(50),
    price NUMERIC(10, 2) CHECK (price > 0),
    launch_date DATE
);

-- Создаем таблицу Reviews
CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5), -- Рейтинг (от 1 до 5 звезд)
    review_text TEXT,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Вставляем пример данных в Users
INSERT INTO users (username, email, join_date, country) VALUES
('Reviewer1', 'rev1@email.com', '2022-05-10', 'USA'),
('GadgetFan', 'gf@email.com', '2021-11-20', 'Canada'),
('Bookworm', 'bw@email.com', '2023-01-05', 'UK'),
('StyleGuru', 'sg@email.com', '2022-08-15', 'USA'),
('AnonUser', 'anon@email.com', '2023-03-01', NULL); -- NULL страна

-- Вставляем пример данных в Products
INSERT INTO products (product_name, category, brand, price, launch_date) VALUES
('Super Smartphone X', 'Electronics', 'TechCorp', 799.99, '2022-09-01'),
('Cozy Reading Lamp', 'Home Goods', 'LightUp', 45.50, '2021-05-15'),
('Quantum Computing Explained', 'Books', NULL, 29.95, '2023-02-01'), -- NULL бренд
('Running Shoes Pro', 'Apparel', 'FastFeet', 120.00, '2022-03-10'),
('Organic Coffee Beans', 'Groceries', 'BeanCo', 18.75, NULL); -- NULL дата запуска

-- Вставляем пример данных в Reviews
INSERT INTO reviews (product_id, user_id, rating, review_text, review_date, is_verified_purchase) VALUES
(1, 1, 5, 'Amazing phone, great features!', '2022-10-01 10:00:00', TRUE),
(1, 2, 4, 'Good value for money, battery could be better.', '2022-10-05 15:30:00', TRUE),
(2, 3, 5, 'Perfect lamp for my reading nook.', '2023-01-20 09:00:00', TRUE),
(4, 4, 3, 'Comfortable, but wore out faster than expected.', '2022-07-15 11:00:00', FALSE),
(1, 3, 4, 'Solid device.', '2023-02-10 14:20:00', FALSE), -- Bookworm оставил отзыв на телефон
(3, 3, 5, 'Excellent introduction to a complex topic.', '2023-03-01 18:00:00', TRUE),
(2, 1, 4, NULL, '2023-03-05 12:45:00', TRUE), -- NULL текст отзыва
(5, 2, 5, 'Best coffee beans I ve tried!', '2023-04-01 08:00:00', TRUE),
(4, 1, 4, 'Great for running.', '2023-04-10 16:00:00', TRUE),
(1, 4, 4, 'Nice design, performs well.', '2023-04-15 19:30:00', TRUE);

Задачи:

Подсчитайте общее количество оставленных отзывов.
Подсчитайте количество отзывов, содержащих текст (т.е. review_text не равно NULL).
Рассчитайте сумму всех оценок (rating), поставленных во всех отзывах.
Найдите средний рейтинг для товара с product_id = 1 (‘Super Smartphone X’).
Определите самую низкую и самую высокую оценку (rating), данную любому товару.
Найдите самую раннюю и самую позднюю review_date, зафиксированную в системе.
Рассчитайте количество отзывов, оставленных для каждого товара. Отобразите product_id и review_count. Отсортируйте по product_id.
Определите средний рейтинг для каждого товара. Отобразите product_id, average_rating и number_of_reviews. Отсортируйте по average_rating по убыванию.
Найдите пользователей, которые оставили более 2 отзывов. Отобразите user_id и total_reviews_submitted.
Найдите категории товаров, в которых средняя цена товара превышает $100. Отобразите category и average_price.

Вариант 5: Анализ системы бронирования авиабилетов

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

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

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS flights;
DROP TABLE IF EXISTS airports;
DROP TABLE IF EXISTS airlines;


-- Создаем таблицу Airlines (Авиакомпании)
CREATE TABLE airlines (
    airline_id SERIAL PRIMARY KEY,
    airline_name VARCHAR(100) UNIQUE NOT NULL,
    country VARCHAR(50) NOT NULL
);

-- Создаем таблицу Airports (Аэропорты)
CREATE TABLE airports (
    airport_code CHAR(3) PRIMARY KEY, -- например, 'JFK', 'LHR'
    airport_name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    country VARCHAR(50) NOT NULL
);

-- Создаем таблицу Flights (Рейсы)
CREATE TABLE flights (
    flight_id SERIAL PRIMARY KEY,
    flight_number VARCHAR(10) NOT NULL,
    airline_id INT NOT NULL,
    origin_airport CHAR(3) NOT NULL,
    destination_airport CHAR(3) NOT NULL,
    departure_datetime TIMESTAMP NOT NULL,
    arrival_datetime TIMESTAMP NOT NULL,
    capacity INT CHECK (capacity > 0),
    base_price NUMERIC(10, 2) CHECK (base_price >= 0),
    FOREIGN KEY (airline_id) REFERENCES airlines(airline_id),
    FOREIGN KEY (origin_airport) REFERENCES airports(airport_code),
    FOREIGN KEY (destination_airport) REFERENCES airports(airport_code)
);

-- Создаем таблицу Bookings (Бронирования)
CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    flight_id INT NOT NULL,
    passenger_name VARCHAR(100) NOT NULL,
    booking_date DATE DEFAULT CURRENT_DATE,
    seat_count INT DEFAULT 1 CHECK (seat_count > 0),
    total_fare NUMERIC(10, 2), -- Может отличаться от base_price * seat_count из-за сборов/скидок
    FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
);


-- Вставляем примерные данные в таблицу Airlines
INSERT INTO airlines (airline_name, country) VALUES
('AirConnect', 'USA'),
('EuroFly', 'Germany'),
('AsiaWings', 'Singapore');

-- Вставляем примерные данные в таблицу Airports
INSERT INTO airports (airport_code, airport_name, city, country) VALUES
('JFK', 'John F. Kennedy International', 'New York', 'USA'),
('LAX', 'Los Angeles International', 'Los Angeles', 'USA'),
('LHR', 'London Heathrow', 'London', 'UK'),
('FRA', 'Frankfurt Airport', 'Frankfurt', 'Germany'),
('SIN', 'Singapore Changi', 'Singapore', 'Singapore');

-- Вставляем примерные данные в таблицу Flights
INSERT INTO flights (flight_number, airline_id, origin_airport, destination_airport, departure_datetime, arrival_datetime, capacity, base_price) VALUES
('AC101', 1, 'JFK', 'LAX', '2024-08-01 08:00:00', '2024-08-01 11:30:00', 150, 299.99),
('EF205', 2, 'FRA', 'LHR', '2024-08-01 10:00:00', '2024-08-01 11:30:00', 120, 99.50),
('AW300', 3, 'SIN', 'LHR', '2024-08-02 22:00:00', '2024-08-03 05:00:00', 250, 650.00),
('AC102', 1, 'LAX', 'JFK', '2024-08-05 14:00:00', '2024-08-05 22:30:00', 150, 310.00),
('EF206', 2, 'LHR', 'FRA', '2024-08-06 09:00:00', '2024-08-06 10:30:00', 110, 85.00), -- Меньшая вместимость
('AC550', 1, 'JFK', 'LHR', '2024-08-10 19:00:00', '2024-08-11 07:00:00', 200, 450.00),
('EF410', 2, 'FRA', 'JFK', '2024-08-12 12:00:00', '2024-08-12 15:00:00', 180, NULL); -- NULL базовая цена

-- Вставляем примерные данные в таблицу Bookings
INSERT INTO bookings (flight_id, passenger_name, seat_count, total_fare, booking_date) VALUES
(1, 'Alice Smith', 1, 320.50, '2024-06-15'),
(1, 'Bob Johnson', 2, 641.00, '2024-06-18'), -- забронировано 2 места
(2, 'Charlie Williams', 1, 99.50, '2024-07-01'),
(3, 'Diana Brown', 1, 700.00, '2024-05-20'),
(4, 'Ethan Jones', 1, 310.00, '2024-07-10'),
(1, 'Fiona Garcia', 1, 325.00, '2024-07-12'), -- Еще одно бронирование для рейса 1
(5, 'George Miller', 3, 255.00, '2024-07-20'), -- забронировано 3 места
(6, 'Hannah Davis', 1, 480.00, '2024-07-25'),
(2, 'Ian Rodriguez', 1, 105.00, '2024-07-28'); -- Еще одно бронирование для рейса 2

Задания:

Подсчитайте общее количество запланированных рейсов.
Подсчитайте количество рейсов, у которых указана base_price (не NULL).
Рассчитайте общую вместимость (capacity) всех рейсов, предлагаемых авиакомпанией с airline_id = 1 (‘AirConnect’).
Найдите среднюю base_price для всех рейсов, вылетающих из ‘JFK’ (игнорируйте рейсы с NULL base_price).
Определите минимальную и максимальную зарегистрированную capacity (вместимость) рейсов.
Найдите самое раннее departure_datetime (время отправления) и самое позднее arrival_datetime (время прибытия) рейсов.
Рассчитайте общее количество забронированных мест для каждого рейса. Отобразите flight_id и total_seats_booked. Отсортируйте по flight_id. (Подсказка: Используйте SUM для seat_count).
Найдите количество рейсов, выполняемых каждой авиакомпанией. Отобразите airline_id и num_flights.
Найдите рейсы, где общее количество забронированных мест больше 1. Отобразите flight_id и total_seats_booked. (Подсказка: Используйте результат из задания 7 с HAVING).
Рассчитайте среднюю total_fare (общую стоимость) за место для каждого рейса на основе бронирований. Отобразите flight_id и average_fare_per_seat. Помните о возможном делении на ноль, если у рейса есть бронирования, но 0 мест (хотя это маловероятно из-за ограничений схемы).

Вариант 6: Анализ управления заказами в ресторане

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

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

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS restaurants;

-- Создаем таблицу Restaurants (Рестораны)
CREATE TABLE restaurants (
    restaurant_id SERIAL PRIMARY KEY,
    restaurant_name VARCHAR(100) NOT NULL,
    cuisine VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    opening_year INT
);

-- Создаем таблицу Menu Items (Позиции меню)
CREATE TABLE menu_items (
    item_id SERIAL PRIMARY KEY,
    restaurant_id INT NOT NULL, -- Позиция принадлежит конкретному ресторану сети; предполагаем, что позиции могут существовать в нескольких местах, но являются разными строками, если цены отличаются.
    item_name VARCHAR(100) NOT NULL,
    category VARCHAR(50), -- например, 'Закуска', 'Основное блюдо', 'Десерт', 'Напиток'
    price NUMERIC(8, 2) CHECK (price >= 0),
    is_vegetarian BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
);

-- Создаем таблицу Orders (Заказы)
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    restaurant_id INT NOT NULL,
    customer_name VARCHAR(100), -- Необязательное имя клиента
    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    order_type VARCHAR(20) DEFAULT 'Dine-In', -- например, 'В ресторане', 'Навынос', 'Доставка'
    total_amount NUMERIC(10, 2), -- Может быть вычислено или предварительно рассчитано
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
);

-- Создаем таблицу Order Details (Детали заказа)
CREATE TABLE order_details (
    order_detail_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    item_id INT NOT NULL,
    quantity INT CHECK (quantity > 0),
    price_per_item NUMERIC(8, 2), -- Цена на момент заказа
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (item_id) REFERENCES menu_items(item_id)
);

-- Вставляем примерные данные в таблицу Restaurants
INSERT INTO restaurants (restaurant_name, cuisine, city, opening_year) VALUES
('Pasta Palace', 'Italian', 'New York', 2018),
('Curry Corner', 'Indian', 'London', 2020),
('Burger Barn', 'American', 'New York', 2019),
('Sushi Spot', 'Japanese', 'London', NULL); -- NULL год открытия

-- Вставляем примерные данные в таблицу Menu Items
-- Позиции для Pasta Palace
INSERT INTO menu_items (restaurant_id, item_name, category, price, is_vegetarian) VALUES
(1, 'Spaghetti Carbonara', 'Main Course', 18.50, FALSE),
(1, 'Margherita Pizza', 'Main Course', 15.00, TRUE),
(1, 'Tiramisu', 'Dessert', 8.00, TRUE),
-- Позиции для Curry Corner
(2, 'Chicken Tikka Masala', 'Main Course', 16.00, FALSE),
(2, 'Vegetable Korma', 'Main Course', 14.50, TRUE),
(2, 'Naan Bread', 'Side', 3.50, TRUE),
-- Позиции для Burger Barn
(3, 'Classic Cheeseburger', 'Main Course', 12.00, FALSE),
(3, 'Veggie Burger', 'Main Course', 11.50, TRUE),
(3, 'French Fries', 'Side', 4.00, TRUE),
-- Позиции для Sushi Spot (связаны с рестораном 4)
(4, 'Salmon Nigiri', 'Sushi', 5.00, FALSE),
(4, 'Avocado Roll', 'Sushi', 6.50, TRUE);

-- Вставляем примерные данные в таблицу Orders
INSERT INTO orders (restaurant_id, customer_name, order_time, order_type, total_amount) VALUES
(1, 'Alice', '2023-10-01 19:30:00', 'Dine-In', 42.00), -- Заказ для позиций 1 и 3
(2, 'Bob', '2023-10-02 13:00:00', 'Takeaway', 19.50), -- Заказ для позиций 4 и 6
(3, 'Charlie', '2023-10-02 18:00:00', 'Dine-In', 27.50), -- Заказ для позиций 7 и 9 (2 картофеля фри)
(1, NULL, '2023-10-03 20:00:00', 'Delivery', 15.00), -- Заказ для позиции 2
(2, 'Diana', '2023-10-04 12:30:00', 'Dine-In', 14.50), -- Заказ для позиции 5
(4, 'Ethan', '2023-10-05 14:00:00', 'Takeaway', 11.50); -- Заказ для позиций 10 и 11

-- Вставляем примерные данные в таблицу Order Details
INSERT INTO order_details (order_id, item_id, quantity, price_per_item) VALUES
(1, 1, 1, 18.50), -- Спагетти
(1, 3, 1, 8.00),  -- Тирамису (Итог заказа 1: 26.50 - Ошибка в примере orders.total_amount, игнорировать для задач агрегации)
(2, 4, 1, 16.00), -- Курица Тикка Масала
(2, 6, 1, 3.50),  -- Наан (Итог заказа 2: 19.50)
(3, 7, 1, 12.00), -- Чизбургер
(3, 9, 2, 4.00),  -- 2x Картофель фри (Итог заказа 3: 12 + 2*4 = 20.00 - Ошибка в примере orders.total_amount)
(4, 2, 1, 15.00), -- Пицца Маргарита (Итог заказа 4: 15.00)
(5, 5, 1, 14.50), -- Овощная Корма (Итог заказа 5: 14.50)
(6, 10, 1, 5.00), -- Нигири с лососем
(6, 11, 1, 6.50); -- Ролл с авокадо (Итог заказа 6: 11.50)

Задания:

Подсчитайте общее количество позиций меню, предлагаемых во всех ресторанах.
Подсчитайте количество позиций меню, отмеченных как вегетарианские (is_vegetarian = TRUE).
Рассчитайте сумму quantity для всех проданных позиций по всем заказам (общее количество проданных позиций). (Подсказка: Используйте order_details).
Найдите среднюю цену позиций категории ‘Main Course’ (Основное блюдо) по всем ресторанам.
Определите минимальную и максимальную price (цену) для любой позиции меню.
Найдите самое раннее и самое позднее зарегистрированное order_time (время заказа).
Рассчитайте общее количество позиций в каждом заказе. Отобразите order_id и total_items. (Подсказка: Суммируйте quantity в order_details, сгруппировав по order_id).
Найдите общее количество позиций меню, предлагаемых каждым рестораном. Отобразите restaurant_id и item_count.
Найдите категории позиций меню, где средняя цена за позицию превышает $10. Отобразите category и average_category_price.
Найдите заказы, которые содержат более 2 позиций в сумме (сумма quantity). Отобразите order_id и total_items_in_order. (Подсказка: Используйте логику из задания 7 с HAVING).

Вариант 7: Анализ вовлеченности в публикации в социальных сетях

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS likes;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS users_sm; -- Переименовано, чтобы избежать конфликта, если таблица 'users' уже существует

-- Создаем таблицу пользователей
CREATE TABLE users_sm (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    country_code CHAR(2) -- например, 'US', 'GB', 'DE'
);

-- Создаем таблицу публикаций
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    post_content TEXT, -- Может быть NULL, если это концептуально просто пост с изображением/видео
    post_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    location_tag VARCHAR(100), -- например, 'New York, NY', NULL
    FOREIGN KEY (user_id) REFERENCES users_sm(user_id)
);

-- Создаем таблицу лайков
CREATE TABLE likes (
    like_id SERIAL PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL, -- Пользователь, поставивший лайк публикации
    like_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (user_id) REFERENCES users_sm(user_id),
    UNIQUE(post_id, user_id) -- Пользователь может поставить лайк публикации только один раз
);

-- Вставляем примерные данные в таблицу пользователей
INSERT INTO users_sm (username, registration_date, country_code) VALUES
('SocialButterfly', '2022-01-10', 'US'),
('PhotoMaster', '2021-11-05', 'CA'),
('TravelBug', '2022-03-20', 'GB'),
('CodeNinja', '2020-08-15', 'US'),
('FoodieFan', '2023-02-28', NULL); -- Страна NULL

-- Вставляем примерные данные в таблицу публикаций
INSERT INTO posts (user_id, post_content, post_timestamp, location_tag) VALUES
(1, 'Having a great time at the park! #sunnyday', '2023-05-01 14:30:00', 'Central Park, NY'),
(2, 'Golden hour shot from the mountains.', '2023-05-02 18:00:00', 'Rocky Mountains'),
(1, 'Loving this new cafe downtown.', '2023-05-03 11:00:00', NULL),
(3, 'Exploring the streets of London.', '2023-05-05 09:15:00', 'London, UK'),
(4, NULL, '2023-05-10 16:00:00', NULL), -- Публикация без текстового содержимого
(1, 'My thoughts on the latest tech trends.', '2023-05-12 10:00:00', NULL),
(5, 'Delicious brunch today!', '2023-05-14 12:45:00', 'The Brunch Spot');

-- Вставляем примерные данные в таблицу лайков
INSERT INTO likes (post_id, user_id, like_timestamp) VALUES
(1, 2, '2023-05-01 15:00:00'), -- PhotoMaster поставил лайк публикации SocialButterfly
(1, 3, '2023-05-01 16:30:00'), -- TravelBug поставил лайк публикации SocialButterfly
(2, 1, '2023-05-02 19:00:00'), -- SocialButterfly поставил лайк публикации PhotoMaster
(2, 4, '2023-05-03 08:00:00'), -- CodeNinja поставил лайк публикации PhotoMaster
(4, 1, '2023-05-05 10:00:00'),
(4, 2, '2023-05-05 11:00:00'),
(4, 5, '2023-05-06 14:00:00'), -- FoodieFan поставил лайк публикации TravelBug
(3, 5, '2023-05-14 13:00:00'), -- FoodieFan поставил лайк публикации SocialButterfly о кафе
(6, 4, '2023-05-12 11:00:00'), -- CodeNinja поставил лайк публикации SocialButterfly о технологиях
(7, 1, '2023-05-14 13:30:00'); -- SocialButterfly поставил лайк публикации FoodieFan

Задания:

Подсчитайте общее количество зарегистрированных пользователей.
Подсчитайте общее количество созданных публикаций.
Подсчитайте количество публикаций, имеющих текстовое содержимое (т.е. post_content НЕ РАВНО NULL).
Подсчитайте общее количество лайков, поставленных всем публикациям.
Найдите самую раннюю и самую позднюю метку времени публикации (post_timestamp).
Рассчитайте общее количество публикаций, созданных каждым пользователем. Отобразите user_id и post_count. Отсортируйте результат по post_count в порядке убывания.
Рассчитайте общее количество лайков, полученных каждой публикацией. Отобразите post_id и like_count. Отсортируйте результат по post_id.
Найдите среднее количество лайков на одну публикацию (учитывайте только публикации, получившие хотя бы один лайк).
Найдите пользователей, которые создали более 1 публикации. Отобразите user_id и number_of_posts.
Найдите публикации, которые получили более 2 лайков. Отобразите post_id и number_of_likes.

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

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS user_listens;
DROP TABLE IF EXISTS tracks;
DROP TABLE IF EXISTS albums;
DROP TABLE IF EXISTS artists;


-- Создаем таблицу исполнителей
CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    artist_name VARCHAR(100) UNIQUE NOT NULL,
    genre VARCHAR(50) NOT NULL,
    country VARCHAR(50)
);

-- Создаем таблицу альбомов
CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    artist_id INT NOT NULL,
    album_title VARCHAR(150) NOT NULL,
    release_year INT CHECK (release_year > 1900),
    label VARCHAR(100),
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

-- Создаем таблицу треков
CREATE TABLE tracks (
    track_id SERIAL PRIMARY KEY,
    album_id INT NOT NULL,
    track_title VARCHAR(150) NOT NULL,
    duration_seconds INT CHECK (duration_seconds > 0), -- Продолжительность в секундах
    track_number INT CHECK (track_number > 0), -- Номер трека в альбоме
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

-- Создаем таблицу прослушиваний пользователей
CREATE TABLE user_listens (
    listen_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL, -- Предполагается, что таблица пользователей существует где-то еще, здесь используется только ID
    track_id INT NOT NULL,
    listen_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (track_id) REFERENCES tracks(track_id)
);


-- Вставляем примерные данные в таблицу исполнителей
INSERT INTO artists (artist_name, genre, country) VALUES
('The Rockers', 'Rock', 'USA'),
('SynthWave Masters', 'Electronic', 'UK'),
('Jazz Cats', 'Jazz', 'USA'),
('Global Beats', 'World', NULL); -- Страна NULL

-- Вставляем примерные данные в таблицу альбомов
INSERT INTO albums (artist_id, album_title, release_year, label) VALUES
(1, 'Rock On', 2020, 'Rock Records'),
(2, 'Neon Nights', 2021, 'Synth Sounds'),
(1, 'Acoustic Sessions', 2022, 'Rock Records'),
(3, 'Midnight Jazz', 2019, 'Jazz Label'),
(4, 'World Rhythms', 2023, NULL); -- Лейбл NULL

-- Вставляем примерные данные в таблицу треков
INSERT INTO tracks (album_id, track_title, duration_seconds, track_number) VALUES
(1, 'Highway Song', 245, 1),
(1, 'Rock Anthem', 300, 2),
(2, 'City Lights', 280, 1),
(2, 'Retro Drive', 320, 2),
(3, 'Quiet Fire', 180, 1),
(3, 'Unplugged Melody', 210, 2),
(4, 'Smooth Sax', 350, 1),
(4, 'Late Night Blues', 400, 2),
(5, 'Desert Mirage', 290, 1),
(5, 'Jungle Groove', 260, 2);

-- Вставляем примерные данные в таблицу прослушиваний
INSERT INTO user_listens (user_id, track_id, listen_timestamp) VALUES
(101, 1, '2023-06-01 10:00:00'),
(102, 3, '2023-06-01 11:30:00'),
(101, 2, '2023-06-02 09:00:00'),
(103, 7, '2023-06-03 15:00:00'),
(101, 1, '2023-06-04 10:30:00'), -- Пользователь 101 прослушал трек 1 снова
(102, 4, '2023-06-05 16:00:00'),
(104, 9, '2023-06-06 12:00:00'),
(103, 8, '2023-06-07 18:45:00'),
(101, 5, '2023-06-08 14:00:00'),
(102, 3, '2023-06-09 10:15:00'); -- Пользователь 102 прослушал трек 3 снова

Задания:

Подсчитайте общее количество исполнителей в базе данных.
Подсчитайте общее количество выпущенных альбомов.
Подсчитайте количество треков продолжительностью более 300 секунд (5 минут).
Рассчитайте общее количество прослушиваний всех треков (общее количество строк в user_listens).
Найдите минимальную и максимальную продолжительность трека (duration_seconds).
Найдите самый ранний и самый поздний год выпуска альбома (release_year).
Рассчитайте количество треков в каждом альбоме. Отобразите album_id, album_title и track_count.
Найдите среднюю продолжительность треков (в секундах) для каждого исполнителя. Отобразите artist_id, artist_name и average_duration.
Найдите пользователей (user_id), которые прослушали более 2 треков. Отобразите user_id и listen_count.
Найдите исполнителей, выпустивших альбомы, где средняя продолжительность треков в этих альбомах превышает 250 секунд. Отобразите artist_id, artist_name, album_id и average_album_track_duration. (Подсказка: Группируйте по исполнителю и альбому).

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

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS books_lib; -- Переименовано во избежание конфликта
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS members;


-- Создаем таблицу Authors (Авторы)
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    nationality VARCHAR(50)
);

-- Создаем таблицу Books (Книги)
CREATE TABLE books_lib (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT NOT NULL,
    isbn VARCHAR(20) UNIQUE, -- Международный стандартный книжный номер
    genre VARCHAR(50),
    publication_year INT,
    copies_available INT DEFAULT 1 CHECK (copies_available >= 0), -- Доступные экземпляры
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- Создаем таблицу Members (Читатели)
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100) NOT NULL,
    join_date DATE DEFAULT CURRENT_DATE, -- Дата регистрации
    membership_type VARCHAR(20) DEFAULT 'Standard' -- например, 'Standard', 'Student', 'Premium'
);

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


-- Вставляем пример данных в Authors
INSERT INTO authors (author_name, nationality) VALUES
('Jane Reader', 'British'),
('Leo Writer', 'American'),
('Ken Follett', 'British'), -- Реальный автор для узнаваемости
('Haruki Murakami', 'Japanese'); -- Реальный автор

-- Вставляем пример данных в Books
INSERT INTO books_lib (title, author_id, isbn, genre, publication_year, copies_available) VALUES
('The Secret Garden', 1, '978-0141321066', 'Childrens', 1911, 3),
('War and Peace', 2, '978-0199232765', 'Historical Fiction', 1869, 2),
('The Pillars of the Earth', 3, '978-0451488336', 'Historical Fiction', 1989, 5),
('Norwegian Wood', 4, '978-0375704024', 'Fiction', 1987, 4),
('Another Story', 1, '978-1234567890', 'Fiction', 2010, 2),
('Space Adventures', 2, '978-0987654321', NULL, 2022, 1), -- NULL жанр
('Kafka on the Shore', 4, '978-1400079278', 'Fiction', 2002, 0); -- 0 доступных копий

-- Вставляем пример данных в Members
INSERT INTO members (member_name, join_date, membership_type) VALUES
('Alice Borrower', '2022-01-15', 'Standard'),
('Bob Student', '2023-09-01', 'Student'),
('Charlie Reader', '2021-05-20', 'Premium'),
('Diana Frequent', '2022-11-10', 'Standard');

-- Вставляем пример данных в Loans
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'), -- Возвращена досрочно
(3, 2, '2023-10-05', '2023-11-05', NULL), -- Еще на руках
(4, 1, '2023-10-10', '2023-10-24', NULL), -- Еще на руках
(2, 3, '2023-09-15', '2023-10-15', '2023-10-18'), -- Возвращена с опозданием
(5, 4, '2023-11-01', '2023-11-15', NULL),
(1, 3, '2023-11-02', '2023-11-16', NULL), -- Книга 1 снова выдана
(3, 1, '2023-11-05', '2023-12-05', NULL); -- Книга 3 снова выдана

Задания:

Подсчитайте общее количество книг в каталоге библиотеки.
Подсчитайте количество книг, у которых в настоящее время copies_available равно 0.
Рассчитайте общее количество доступных экземпляров всех книг в библиотеке.
Найдите средний publication_year (год публикации) для книг в жанре ‘Historical Fiction’.
Определите минимальный и максимальный publication_year (год публикации), найденные в коллекции книг.
Найдите самую раннюю и самую позднюю дату join_date (дату регистрации) читателя.
Рассчитайте общее количество книг, написанных каждым автором. Отобразите author_id, author_name и book_count (количество книг).
Найдите, сколько раз каждая книга была выдана. Отобразите book_id, title и loan_count (количество выдач).
Найдите читателей, которые брали более 1 книги (в настоящее время или ранее). Отобразите member_id, member_name и total_loans (общее количество выдач).
Найдите жанры книг, для которых общее количество доступных экземпляров (copies_available) всех книг этого жанра превышает 4. Отобразите genre и total_available_copies (общее количество доступных экземпляров).

Вариант 10: Анализ Посещений Пациентов Медицинской Клиники

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS visits;
DROP TABLE IF EXISTS doctors;
DROP TABLE IF EXISTS patients;

-- Создаем таблицу Patients (Пациенты)
CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender CHAR(1), -- 'M', 'F', 'O' (Другой)
    contact_number VARCHAR(20) UNIQUE
);

-- Создаем таблицу Doctors (Врачи)
CREATE TABLE doctors (
    doctor_id SERIAL PRIMARY KEY,
    doctor_name VARCHAR(100) NOT NULL,
    specialty VARCHAR(100) NOT NULL, -- Специальность
    hire_date DATE, -- Дата найма
    office_number VARCHAR(10) -- Номер кабинета
);

-- Создаем таблицу Visits (Посещения)
CREATE TABLE visits (
    visit_id SERIAL PRIMARY KEY,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    visit_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Дата и время визита
    reason_for_visit TEXT, -- Причина визита
    diagnosis VARCHAR(255), -- Диагноз
    visit_duration_minutes INT CHECK (visit_duration_minutes > 0), -- Продолжительность визита в минутах (оценочная или фактическая)
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

-- Вставляем пример данных в Patients
INSERT INTO patients (first_name, last_name, date_of_birth, gender, contact_number) VALUES
('Peter', 'Pan', '2010-05-10', 'M', '555-1111'),
('Wendy', 'Darling', '2008-11-20', 'F', '555-2222'),
('James', 'Hook', '1975-02-15', 'M', '555-3333'),
('Tinker', 'Bell', '2015-07-01', 'F', NULL), -- NULL контактный номер
('John', 'Darling', '2011-03-25', 'M', '555-4444');

-- Вставляем пример данных в Doctors
INSERT INTO doctors (doctor_name, specialty, hire_date, office_number) VALUES
('Dr. House', 'Diagnostics', '2005-09-16', '221B'),
('Dr. Watson', 'General Practice', '2010-01-20', '101A'),
('Dr. Grey', 'Surgery', '2008-07-01', 'OR-3'),
('Dr. Strange', 'Neurology', '2016-11-04', 'SANCTUM');

-- Вставляем пример данных в Visits
INSERT INTO visits (patient_id, doctor_id, visit_date, reason_for_visit, diagnosis, visit_duration_minutes) VALUES
(1, 2, '2023-01-10 09:00:00', 'Annual Checkup', 'Healthy', 30),
(2, 2, '2023-01-12 10:30:00', 'Sore Throat', 'Strep Throat', 20),
(3, 1, '2023-01-15 11:00:00', 'Leg Pain', 'Gout', 45),
(1, 2, '2023-02-05 14:00:00', 'Follow-up', 'Recovered', 15),
(4, 3, '2023-02-10 08:15:00', 'Consultation', 'Pre-op assessment', 60),
(5, 2, '2023-02-20 09:45:00', 'Fever', 'Influenza', 25),
(3, 4, '2023-03-01 13:00:00', 'Headaches', 'Migraine', 50),
(2, 2, '2023-03-10 10:00:00', 'Annual Checkup', 'Healthy', 30),
(1, 1, '2023-04-01 15:30:00', 'Rash', 'Allergic Reaction', 40),
(3, 1, '2023-04-05 16:00:00', 'Follow-up', 'Condition stable', 20);

Задания:

Подсчитайте общее количество зарегистрированных пациентов.
Рассчитайте общее количество посещений, зарегистрированных для всех пациентов и врачей.
Найдите среднюю visit_duration_minutes (продолжительность визита в минутах) для всех посещений.
Определите дату самого раннего и самого позднего зарегистрированного посещения (visit_date).
Подсчитайте количество различных врачебных специальностей (specialty), доступных в клинике.
Рассчитайте общее количество посещений, обработанных каждым врачом. Отобразите doctor_id, doctor_name (требует соединения (JOIN), но для этого упражнения сосредоточьтесь на агрегации по таблице visits, так что можно отобразить только doctor_id) и visit_count (количество посещений). Отсортируйте по visit_count по убыванию.
Найдите среднюю visit_duration_minutes (продолжительность визита в минутах) для каждой врачебной специальности (specialty). Отобразите specialty (требует соединения (JOIN), но сначала сосредоточьтесь на агрегации - студенты могут сгруппировать по doctor_id, а затем найти специальность, или выполнить соединение) и average_duration (средняя продолжительность).
Рассчитайте минимальную и максимальную visit_duration_minutes для посещений, связанных с ‘Dr. House’ (doctor_id = 1).
Найдите пациентов, которые посещали клинику более двух раз. Отобразите patient_id и number_of_visits (количество посещений).
Найдите врачебные специальности (specialty), где средняя продолжительность посещения (visit_duration_minutes) превышает 35 минут. Отобразите specialty (требует соединения (JOIN)) и average_duration (средняя продолжительность).

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

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS listings;
DROP TABLE IF EXISTS properties;
DROP TABLE IF EXISTS agents;
DROP TABLE IF EXISTS locations;


-- Создаем таблицу Locations (Местоположения)
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    city VARCHAR(50) NOT NULL,
    state CHAR(2) NOT NULL,
    zip_code VARCHAR(10) NOT NULL UNIQUE
);

-- Создаем таблицу Agents (Агенты)
CREATE TABLE agents (
    agent_id SERIAL PRIMARY KEY,
    agent_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    hire_date DATE NOT NULL
);

-- Создаем таблицу Properties (Объекты недвижимости)
CREATE TABLE properties (
    property_id SERIAL PRIMARY KEY,
    location_id INT NOT NULL,
    address VARCHAR(255) NOT NULL,
    property_type VARCHAR(50) NOT NULL, -- например, 'Частный дом', 'Квартира (Кондо)', 'Таунхаус'
    year_built INT,
    bedrooms INT CHECK (bedrooms > 0),
    bathrooms NUMERIC(3,1) CHECK (bathrooms > 0), -- например, 2.5
    square_footage INT CHECK (square_footage > 0),
    FOREIGN KEY (location_id) REFERENCES locations(location_id)
);

-- Создаем таблицу Listings (Объявления)
CREATE TABLE listings (
    listing_id SERIAL PRIMARY KEY,
    property_id INT NOT NULL UNIQUE, -- Предполагая, что у объекта недвижимости может быть только одно активное объявление одновременно
    agent_id INT NOT NULL,
    listing_price NUMERIC(12, 2) CHECK (listing_price > 0),
    listing_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'Active', -- например, 'Активно', 'В ожидании', 'Продано', 'Снято с продажи'
    sold_price NUMERIC(12, 2) CHECK (sold_price > 0), -- Может быть NULL, если не продано
    sold_date DATE, -- Может быть NULL, если не продано
    FOREIGN KEY (property_id) REFERENCES properties(property_id),
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id)
);


-- Вставляем примерные данные в Locations
INSERT INTO locations (city, state, zip_code) VALUES
('Metropolis', 'NY', '10001'),
('Gotham', 'NJ', '07030'),
('Star City', 'CA', '90210'),
('Central City', 'MO', '63005');

-- Вставляем примерные данные в Agents
INSERT INTO agents (agent_name, email, phone, hire_date) VALUES
('Clark Kent', 'ckent@dailyplanet.com', '555-SUPER', '2018-06-01'),
('Bruce Wayne', 'bwayne@wayne.ent', '555-BATS', '2015-04-10'),
('Oliver Queen', 'oqueen@queen.inc', '555-ARROW', '2019-11-15'),
('Barry Allen', 'ballen@ccpd.gov', NULL, '2020-01-25'); -- Телефон NULL

-- Вставляем примерные данные в Properties
INSERT INTO properties (location_id, address, property_type, year_built, bedrooms, bathrooms, square_footage) VALUES
(1, '1 Superman Plaza', 'Condo', 2005, 3, 2.0, 1800),
(2, '1 Wayne Manor', 'Single Family', 1939, 10, 8.5, 15000),
(3, '1 Queen Mansion', 'Single Family', 1960, 8, 6.0, 12000),
(1, '123 Lois Lane Apt 5B', 'Condo', 1998, 2, 1.0, 950),
(4, '456 Flash Street', 'Townhouse', 2010, 3, 2.5, 1600),
(2, '789 Joker Ave', 'Condo', 1985, NULL, 1.0, 800); -- Количество спален NULL

-- Вставляем примерные данные в Listings
INSERT INTO listings (property_id, agent_id, listing_price, listing_date, status, sold_price, sold_date) VALUES
(1, 1, 850000.00, '2023-01-10', 'Active', NULL, NULL),
(2, 2, 15000000.00, '2022-11-01', 'Withdrawn', NULL, NULL), -- Снято с продажи
(3, 3, 12000000.00, '2023-02-15', 'Active', NULL, NULL),
(4, 1, 475000.00, '2023-03-01', 'Sold', 470000.00, '2023-04-20'), -- Продано
(5, 4, 320000.00, '2023-03-20', 'Pending', NULL, NULL), -- В ожидании
(6, 2, 250000.00, '2023-04-05', 'Active', NULL, NULL);

Задачи:

Подсчитайте общее количество выставленных на продажу объектов недвижимости (независимо от статуса).
Рассчитайте общую listing_price (цену выставления) всех ‘Active’ (активных) объявлений.
Найдите среднюю listing_price для объектов недвижимости, расположенных в ‘Metropolis’ (location_id = 1).
Определите минимальную и максимальную listing_price среди всех ‘Active’ (активных) объявлений.
Подсчитайте количество уникальных типов выставленных объектов недвижимости (например, ‘Condo’, ‘Single Family’).
Рассчитайте количество объявлений, которыми управляет каждый агент. Отобразите agent_id и listing_count (количество объявлений). Отсортируйте по listing_count по убыванию.
Найдите среднюю square_footage (площадь) для каждого property_type (типа недвижимости). Отобразите property_type и avg_sqft (средняя площадь). Игнорируйте объекты с NULL square_footage (хотя таких нет в примере).
Рассчитайте общую сумму sold_price (цены продажи), сгенерированную каждым агентом. Отобразите agent_id и total_sales_value (общая сумма продаж). Учитывайте только объявления со статусом ‘Sold’ (Продано).
Найдите местоположения (города), где выставлено на продажу более 1 объекта недвижимости (независимо от статуса). Отобразите location_id и property_count (количество объектов).
Найдите агентов, которые выставили на продажу объекты недвижимости со средней listing_price более $1,000,000. Отобразите agent_id и average_listing_price (средняя цена выставления).

Вариант 12: Анализ системы отслеживания ошибок (баг-трекинга)

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS bugs;
DROP TABLE IF EXISTS projects_tracker; -- Переименовано, чтобы избежать потенциального конфликта с ключевыми словами
DROP TABLE IF EXISTS users_tracker; -- Переименовано, чтобы избежать потенциального конфликта с ключевыми словами


-- Создаем таблицу Projects (Проекты) (используя другое имя)
CREATE TABLE projects_tracker (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) UNIQUE NOT NULL,
    start_date DATE,
    manager_name VARCHAR(100)
);

-- Создаем таблицу Users (Пользователи) (для сообщивших и назначенных исполнителей, используя другое имя)
CREATE TABLE users_tracker (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL -- например, 'Разработчик', 'QA', 'Менеджер'
);

-- Создаем таблицу Bugs (Ошибки)
CREATE TABLE bugs (
    bug_id SERIAL PRIMARY KEY,
    project_id INT NOT NULL,
    reported_by_user_id INT NOT NULL,
    assigned_to_user_id INT, -- NULL, если не назначено
    summary VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'Open', -- например, 'Открыта', 'В работе', 'Решена', 'Закрыта', 'Не будет исправлена'
    severity VARCHAR(20) NOT NULL, -- например, 'Критическая', 'Высокая', 'Средняя', 'Низкая'
    priority INT CHECK (priority BETWEEN 1 AND 5), -- 1 = Наивысший
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    estimated_effort_hours NUMERIC(4,1), -- например, 0.5, 1.0, 4.0, 8.0
    FOREIGN KEY (project_id) REFERENCES projects_tracker(project_id),
    FOREIGN KEY (reported_by_user_id) REFERENCES users_tracker(user_id),
    FOREIGN KEY (assigned_to_user_id) REFERENCES users_tracker(user_id)
);


-- Вставляем примерные данные в projects_tracker
INSERT INTO projects_tracker (project_name, start_date, manager_name) VALUES
('Zeus', '2022-01-10', 'Hera'),
('Apollo', '2022-06-15', 'Artemis'),
('Poseidon', '2023-02-01', 'Amphitrite');

-- Вставляем примерные данные в users_tracker
INSERT INTO users_tracker (user_name, email, role) VALUES
('Hercules', 'herc@olympus.dev', 'Developer'),
('Athena', 'athena@wisdom.qa', 'QA'),
('Ares', 'ares@war.dev', 'Developer'),
('Hermes', 'hermes@messenger.ops', 'Manager'),
('Hades', 'hades@under.dev', 'Developer'); -- Разработчик из подземного мира :)

-- Вставляем примерные данные в Bugs
INSERT INTO bugs (project_id, reported_by_user_id, assigned_to_user_id, summary, status, severity, priority, estimated_effort_hours, created_at) VALUES
(1, 2, 1, 'UI button misaligned on login screen', 'Resolved', 'Low', 4, 1.0, '2023-01-20 10:00:00'),
(1, 2, 1, 'Crash when submitting empty form', 'InProgress', 'Critical', 1, 8.0, '2023-01-25 11:30:00'),
(2, 2, 3, 'Incorrect calculation in reporting module', 'Open', 'High', 2, 12.0, '2023-02-10 14:00:00'),
(1, 4, 1, 'Improve error message clarity', 'Open', 'Low', 5, 2.0, '2023-02-15 09:45:00'),
(3, 2, 5, 'Data corruption on large uploads', 'Open', 'Critical', 1, 20.0, '2023-03-01 16:20:00'),
(2, 3, 3, 'API endpoint returning 500 error', 'InProgress', 'High', 2, 6.0, '2023-03-05 12:00:00'),
(1, 2, NULL, 'Typo in user documentation', 'Open', 'Low', 5, 0.5, '2023-03-10 18:00:00'), -- Не назначено
(3, 2, 5, 'Slow performance on main dashboard', 'Open', 'Medium', 3, 16.0, '2023-04-01 11:00:00'),
(2, 1, 3, 'Add unit tests for payment gateway', 'Open', 'Medium', 3, 24.0, '2023-04-15 15:00:00'); -- Задача, замаскированная под ошибку

Задачи:

Подсчитайте общее количество ошибок, зарегистрированных во всех проектах.
Рассчитайте общую сумму estimated_effort_hours (оценочные трудозатраты в часах) для всех ошибок, находящихся в статусе ‘Open’ (Открыта).
Найдите среднее значение estimated_effort_hours для ошибок с уровнем серьезности ‘Critical’ (Критическая).
Определите временную метку (created_at) самой первой и самой последней зарегистрированной ошибки.
Подсчитайте количество уникальных статусов ошибок, используемых в настоящее время (например, ‘Open’, ‘InProgress’).
Рассчитайте количество ошибок, назначенных каждому пользователю (assigned_to_user_id). Отобразите user_id и assigned_bug_count (количество назначенных ошибок). Игнорируйте назначения NULL. Отсортируйте по количеству по убыванию.
Найдите общее количество ошибок, зарегистрированных для каждого проекта. Отобразите project_id и total_bugs_reported (общее количество ошибок).
Рассчитайте средний приоритет для ошибок, сгруппированных по их уровню серьезности (severity). Отобразите severity и average_priority (средний приоритет).
Найдите проекты, в которых в настоящее время открыто более 3 ошибок. Отобразите project_id и open_bug_count (количество открытых ошибок).
Найдите пользователей (исполнителей), которым назначены ошибки с общей суммой estimated_effort_hours более 20 часов. Отобразите assigned_to_user_id и total_assigned_effort (общие назначенные трудозатраты).

Вариант 13: Анализ системы продажи билетов на мероприятия

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS ticket_sales;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS venues;
DROP TABLE IF EXISTS customers_ticketing; -- Переименовано во избежание конфликтов

-- Создание таблицы площадок (Venues)
CREATE TABLE venues (
    venue_id SERIAL PRIMARY KEY,
    venue_name VARCHAR(100) NOT NULL UNIQUE, -- Название площадки
    city VARCHAR(50) NOT NULL,               -- Город
    capacity INT CHECK (capacity > 0)        -- Вместимость (должна быть > 0)
);

-- Создание таблицы мероприятий (Events)
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(150) NOT NULL,        -- Название мероприятия
    venue_id INT NOT NULL,                   -- ID площадки (внешний ключ)
    event_date DATE NOT NULL,                -- Дата мероприятия
    category VARCHAR(50),                    -- Категория (например, 'Музыка', 'Спорт', 'Театр', 'Конференция')
    base_ticket_price NUMERIC(8, 2) CHECK (base_ticket_price >= 0), -- Базовая цена билета (>= 0)
    FOREIGN KEY (venue_id) REFERENCES venues(venue_id)
);

-- Создание таблицы клиентов (Customers) (для системы продажи билетов)
CREATE TABLE customers_ticketing (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),             -- Имя клиента (упрощено для примера)
    email VARCHAR(100) UNIQUE NOT NULL      -- Email (уникальный)
);

-- Создание таблицы продаж билетов (Ticket Sales)
CREATE TABLE ticket_sales (
    sale_id SERIAL PRIMARY KEY,
    event_id INT NOT NULL,                   -- ID мероприятия (внешний ключ)
    customer_id INT NOT NULL,                -- ID клиента (внешний ключ)
    purchase_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Время покупки (по умолчанию текущее)
    num_tickets INT CHECK (num_tickets > 0), -- Количество билетов (> 0)
    total_amount NUMERIC(10, 2) NOT NULL,    -- Общая сумма (может включать сборы и т.д.)
    ticket_type VARCHAR(30) DEFAULT 'General Admission', -- Тип билета (например, 'VIP', 'Early Bird')
    FOREIGN KEY (event_id) REFERENCES events(event_id),
    FOREIGN KEY (customer_id) REFERENCES customers_ticketing(customer_id)
);

-- Вставка примеров данных в таблицу Venues
INSERT INTO venues (venue_name, city, capacity) VALUES
('Grand Arena', 'New York', 20000),
('Melody Hall', 'Los Angeles', 5000),
('Convention Center', 'Chicago', 50000),
('Open Air Stage', 'Austin', 15000);

-- Вставка примеров данных в таблицу Events
INSERT INTO events (event_name, venue_id, event_date, category, base_ticket_price) VALUES
('Rock Legends Concert', 1, '2024-07-20', 'Music', 85.00),
('Tech Summit 2024', 3, '2024-09-15', 'Conference', 499.00),
('Knights vs Dragons Championship', 1, '2024-08-10', 'Sports', 60.00),
('Shakespeare in the Park', 4, '2024-07-05', 'Theater', 30.00),
('Indie Music Fest', 2, '2024-08-25', 'Music', 55.00),
('AI Symposium', 3, '2024-10-01', 'Conference', 350.00); -- Еще одна конференция

-- Вставка примеров данных в таблицу Customers
INSERT INTO customers_ticketing (customer_name, email) VALUES
('Alice Cooper', 'alice@rock.com'),
('Bob Marley', 'bob@reggae.org'),
('Charlie Parker', 'charlie@jazz.net'),
('Diana Ross', 'diana@motown.com');

-- Вставка примеров данных в таблицу Ticket Sales
INSERT INTO ticket_sales (event_id, customer_id, num_tickets, total_amount, ticket_type, purchase_timestamp) VALUES
(1, 1, 2, 175.50, 'General Admission', '2024-05-10 10:00:00'),
(2, 3, 1, 499.00, 'Early Bird', '2024-05-15 11:30:00'),
(3, 2, 4, 250.00, 'General Admission', '2024-06-01 14:00:00'),
(1, 4, 1, 90.00, 'General Admission', '2024-06-05 09:45:00'), -- То же мероприятие, другой клиент
(5, 1, 2, 115.00, 'General Admission', '2024-06-10 16:20:00'),
(2, 4, 1, 525.00, 'Standard', '2024-06-20 12:00:00'), -- То же мероприятие, другой клиент/цена
(4, 3, 2, 65.00, 'General Admission', '2024-06-25 18:00:00'),
(1, 2, 2, 350.00, 'VIP', '2024-07-01 10:15:00'), -- VIP билеты для мероприятия 1
(6, 1, 1, 350.00, 'Standard', '2024-07-02 11:00:00');

Задачи:

Посчитайте общее количество доступных площадок (venues).
Вычислите общую вместимость (capacity) по всем площадкам.
Найдите среднюю базовую цену билета (base_ticket_price) для мероприятий в категории 'Music'.
Определите минимальную и максимальную базовую цену билета (base_ticket_price) по всем мероприятиям.
Посчитайте количество уникальных категорий мероприятий (category).
Вычислите общее количество проданных билетов (num_tickets) для каждого мероприятия. Отобразите event_id и total_tickets_sold. Отсортируйте результат по total_tickets_sold по убыванию.
Найдите общую сумму продаж (total_amount) для каждой категории мероприятий. Отобразите category (требуется соединение, но сначала агрегируйте) и total_revenue.
Вычислите среднее количество билетов, приобретаемых за одну продажу (AVG(num_tickets)).
Найдите мероприятия, на которые было продано в сумме (по всем продажам) более 3 билетов. Отобразите event_id и total_tickets_sold.
Найдите типы билетов (ticket_type), для которых средняя сумма продажи (total_amount) превышает $100. Отобразите ticket_type и average_sale_amount.

Вариант 14: Анализ производственных запасов

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS inventory_log;
DROP TABLE IF EXISTS components;
DROP TABLE IF EXISTS suppliers;

-- Создание таблицы поставщиков (Suppliers)
CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(100) NOT NULL UNIQUE, -- Название поставщика
    country VARCHAR(50),                         -- Страна
    contact_person VARCHAR(100),                 -- Контактное лицо
    rating INT CHECK (rating BETWEEN 1 AND 5)    -- Рейтинг надежности поставщика (от 1 до 5)
);

-- Создание таблицы компонентов (Components)
CREATE TABLE components (
    component_id SERIAL PRIMARY KEY,
    component_name VARCHAR(100) NOT NULL,    -- Название компонента
    description TEXT,                       -- Описание
    unit_cost NUMERIC(10, 4) CHECK (unit_cost > 0), -- Себестоимость единицы (> 0)
    category VARCHAR(50)                    -- Категория (например, 'Электроника', 'Механика', 'Крепеж')
);

-- Создание таблицы журнала запасов (Inventory Log) (Отслеживает уровни запасов со временем или корректировки)
CREATE TABLE inventory_log (
    log_id SERIAL PRIMARY KEY,
    component_id INT NOT NULL,              -- ID компонента (внешний ключ)
    supplier_id INT,                        -- ID поставщика (NULL, если произведено внутри или источник неизвестен)
    log_date DATE DEFAULT CURRENT_DATE,     -- Дата записи (по умолчанию текущая)
    transaction_type VARCHAR(10) NOT NULL,  -- Тип транзакции ('IN', 'OUT', 'ADJUST' - приход, расход, корректировка)
    quantity INT NOT NULL,                  -- Количество (Положительное для IN/ADJUST+, Отрицательное для OUT/ADJUST-)
    current_stock_level INT,                -- Опционально: Снимок уровня запасов после транзакции
    notes TEXT,                             -- Примечания
    FOREIGN KEY (component_id) REFERENCES components(component_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);

-- Вставка примеров данных в таблицу Suppliers
INSERT INTO suppliers (supplier_name, country, contact_person, rating) VALUES
('Acme Components', 'USA', 'Wile E. Coyote', 3),
('Global Parts Inc.', 'Germany', 'Hans Gruber', 5),
('Fasteners Unlimited', 'USA', 'Penny Pincher', 4),
('Micro Circuits Ltd.', 'Japan', 'Kenji Tanaka', 5);

-- Вставка примеров данных в таблицу Components
INSERT INTO components (component_name, description, unit_cost, category) VALUES
('Resistor 10k Ohm', 'Standard 1/4W resistor', 0.0150, 'Electronics'),
('Capacitor 100uF', 'Electrolytic capacitor', 0.0500, 'Electronics'),
('M3x10mm Screw', 'Stainless steel machine screw', 0.0200, 'Fasteners'),
('Gear Assembly A', 'Small plastic gear assembly', 1.5000, 'Mechanical'),
('Microcontroller X1', '8-bit MCU', 2.7500, 'Electronics');

-- Вставка примеров данных в таблицу Inventory Log (Упрощено: показаны только добавления/начальные запасы)
-- В реальности здесь бы отслеживались IN и OUT непрерывно. Для простоты используем положительное количество.
INSERT INTO inventory_log (component_id, supplier_id, log_date, transaction_type, quantity, current_stock_level) VALUES
(1, 4, '2023-01-15', 'IN', 10000, 10000),
(2, 4, '2023-01-15', 'IN', 5000, 5000),
(3, 3, '2023-01-20', 'IN', 20000, 20000),
(4, 1, '2023-02-01', 'IN', 500, 500),
(5, 4, '2023-02-10', 'IN', 1000, 1000),
(1, 4, '2023-03-01', 'IN', 5000, 15000), -- Получено еще резисторов
(3, 3, '2023-03-05', 'IN', 10000, 30000), -- Получено еще винтов
(2, 4, '2023-04-01', 'OUT', -2000, 3000), -- Использованы конденсаторы
(4, 1, '2023-04-10', 'OUT', -100, 400),  -- Использованы узлы шестерен
(5, 4, '2023-04-15', 'OUT', -300, 700);   -- Использованы микроконтроллеры

-- Фокус задач: Анализ компонентов и, возможно, *чистого* эффекта из журнала для простоты.
-- Добавим простую таблицу Current Stock для упрощения упражнений с агрегацией, отражающую конечное состояние.

DROP TABLE IF EXISTS current_stock;
CREATE TABLE current_stock (
    component_id INT PRIMARY KEY,
    quantity_on_hand INT NOT NULL CHECK (quantity_on_hand >= 0), -- Количество в наличии (>= 0)
    last_updated DATE,                   -- Дата последнего обновления
    FOREIGN KEY (component_id) REFERENCES components(component_id)
);

INSERT INTO current_stock (component_id, quantity_on_hand, last_updated) VALUES
(1, 15000, '2023-03-01'),
(2, 3000, '2023-04-01'),
(3, 30000, '2023-03-05'),
(4, 400, '2023-04-10'),
(5, 700, '2023-04-15');

Задачи (используя в основном components и current_stock):

Посчитайте общее количество различных отслеживаемых компонентов.
Вычислите общее количество в наличии (quantity_on_hand) по всем компонентам в таблице current_stock.
Найдите среднюю себестоимость единицы (unit_cost) для компонентов в категории 'Electronics'.
Определите минимальную и максимальную себестоимость единицы (unit_cost) среди всех компонентов.
Посчитайте количество уникальных категорий компонентов (category).
Вычислите общую стоимость текущих запасов для каждой категории компонентов (Сумма quantity_on_hand * unit_cost). Отобразите category и total_category_value. (Требуется соединение).
Найдите среднее количество в наличии (quantity_on_hand) на один компонент в таблице current_stock.
Посчитайте количество компонентов, поставляемых каждым поставщиком (для простоты, на основе первой записи в inventory_log, или требуется более сложная логика). Упростим: Посчитайте количество компонентов по категориям. Отобразите category и component_count.
Найдите категории компонентов, где общее количество типов компонентов (уникальных видов) больше 1. Отобразите category и component_type_count.
Найдите категории компонентов, где средняя себестоимость единицы (unit_cost) меньше $0.10. Отобразите category и average_unit_cost.

Вариант 15: Анализ абонементов в спортзал и посещаемости занятий

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

Схема:

-- Удаляем таблицы, если они существуют, чтобы начать с чистого листа
DROP TABLE IF EXISTS class_attendance;
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS membership_types;

-- Создаем таблицу типов абонементов
CREATE TABLE membership_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(50) UNIQUE NOT NULL, -- например, 'Базовый', 'Премиум', 'Семейный'
    monthly_fee NUMERIC(7, 2) CHECK (monthly_fee >= 0),
    access_level INT DEFAULT 1 -- Более высокий уровень = больше доступа
);

-- Создаем таблицу членов клуба
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    join_date DATE NOT NULL,
    membership_type_id INT NOT NULL,
    date_of_birth DATE,
    FOREIGN KEY (membership_type_id) REFERENCES membership_types(type_id)
);

-- Создаем таблицу занятий
CREATE TABLE classes (
    class_id SERIAL PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL,
    instructor_name VARCHAR(100),
    day_of_week VARCHAR(10), -- например, 'Понедельник', 'Вторник'
    start_time TIME,
    duration_minutes INT CHECK (duration_minutes > 0),
    max_capacity INT CHECK (max_capacity > 0)
);

-- Создаем таблицу посещаемости занятий
CREATE TABLE class_attendance (
    attendance_id SERIAL PRIMARY KEY,
    class_id INT NOT NULL,
    member_id INT NOT NULL,
    attendance_date DATE NOT NULL,
    check_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES classes(class_id),
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

-- Вставляем пример данных в таблицу типов абонементов
INSERT INTO membership_types (type_name, monthly_fee, access_level) VALUES
('Basic', 29.99, 1),
('Premium', 49.99, 2),
('Platinum', 79.99, 3);

-- Вставляем пример данных в таблицу членов клуба
INSERT INTO members (member_name, email, join_date, membership_type_id, date_of_birth) VALUES
('Rocky Balboa', 'rocky@philly.com', '2022-01-15', 2, '1976-11-21'),
('Sarah Connor', 'sarah@skynet.def', '2021-08-01', 3, '1965-05-13'),
('Luke Skywalker', 'luke@jedi.org', '2023-03-10', 1, '19BBY-05-25'), -- Пример формата даты рождения
('Ellen Ripley', 'ellen@nostromo.corp', '2022-05-20', 2, NULL), -- NULL дата рождения
('Indiana Jones', 'indy@archaeology.edu', '2020-11-01', 3, '1899-07-01');

-- Вставляем пример данных в таблицу занятий
INSERT INTO classes (class_name, instructor_name, day_of_week, start_time, duration_minutes, max_capacity) VALUES
('Yoga Flow', 'Padme', 'Monday', '18:00:00', 60, 20),
('Spin Cycle Fury', 'Max', 'Tuesday', '07:00:00', 45, 25),
('Heavy Lifting', 'Arnold', 'Wednesday', '19:00:00', 75, 15),
('Zumba Party', 'Shakira', 'Thursday', '18:30:00', 60, 30),
('Meditation', 'Yoda', 'Friday', '12:00:00', 30, 20);

-- Вставляем пример данных в таблицу посещаемости занятий
INSERT INTO class_attendance (class_id, member_id, attendance_date, check_in_time) VALUES
(1, 1, '2023-04-03', '2023-04-03 17:55:00'), -- Рокки Йога
(2, 2, '2023-04-04', '2023-04-04 06:58:00'), -- Сара Спиннинг
(3, 1, '2023-04-05', '2023-04-05 18:50:00'), -- Рокки Силовая тренировка
(1, 4, '2023-04-10', '2023-04-10 17:59:00'), -- Рипли Йога
(4, 5, '2023-04-13', '2023-04-13 18:25:00'), -- Инди Зумба
(1, 2, '2023-04-17', '2023-04-17 17:56:00'), -- Сара Йога
(3, 5, '2023-04-19', '2023-04-19 18:55:00'), -- Инди Силовая тренировка
(2, 1, '2023-04-25', '2023-04-25 06:55:00'), -- Рокки Спиннинг
(5, 3, '2023-04-28', '2023-04-28 11:58:00'); -- Люк Медитация

Задания:

Подсчитайте общее количество активных членов клуба.
Рассчитайте общее количество занятий, предлагаемых в неделю (подсчитайте уникальные значения class_id из таблицы classes).
Найдите среднюю ежемесячную плату (monthly_fee) по всем типам абонементов.
Определите минимальную и максимальную вместимость (max_capacity) среди всех занятий.
Подсчитайте количество уникальных инструкторов.
Рассчитайте общее количество зарегистрированных посещений для каждого занятия. Отобразите class_id, class_name (требуется соединение таблиц - join) и total_attendance (общее количество посещений). Отсортируйте результат по total_attendance в порядке убывания.
Найдите количество членов клуба для каждого типа абонемента (membership_type_id). Отобразите type_id, type_name (требуется соединение таблиц - join) и member_count (количество членов).
Рассчитайте среднюю продолжительность занятия в минутах (duration_minutes).
Найдите членов клуба, посетивших более 1 занятия. Отобразите member_id, member_name (требуется соединение таблиц - join) и classes_attended_count (количество посещенных занятий).
Найдите день недели (day_of_week), в который средняя вместимость (max_capacity) занятий является наибольшей. Отобразите day_of_week и average_capacity (средняя вместимость).

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

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