Базы данных | Задания для практического занятия 10
Создание сложных запросов. Объединение нескольких таблиц и работа с соединениями (Joins).
Вариант 1: База данных интернет-магазина
Сценарий: Вы управляете базой данных интернет-магазина. База данных отслеживает клиентов, товары, категории товаров, заказы и конкретные позиции (товары), включенные в каждый заказ.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;
-- Создаем таблицу категорий (со ссылкой на саму себя для подкатегорий)
CREATE TABLE categories (
category_id SERIAL PRIMARY KEY,
category_name VARCHAR(100) NOT NULL UNIQUE,
parent_category_id INT REFERENCES categories(category_id) NULL -- Для подкатегорий
);
-- Создаем таблицу товаров
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(150) NOT NULL,
price DECIMAL(10, 2) CHECK (price > 0),
category_id INT REFERENCES categories(category_id),
stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0) -- Количество на складе
);
-- Создаем таблицу клиентов
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) 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) CHECK (status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled')) -- Статус заказа
);
-- Создаем таблицу позиций заказа (Связующая таблица)
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
quantity INT CHECK (quantity > 0), -- Количество
price_at_purchase DECIMAL(10, 2) -- Цена на момент оформления заказа
);
-- Вставляем примерные данные
INSERT INTO categories (category_name, parent_category_id) VALUES
('Electronics', NULL), -- Электроника
('Computers', 1), -- Компьютеры (подкатегория Электроники)
('Mobile Phones', 1), -- Мобильные телефоны (подкатегория Электроники)
('Clothing', NULL), -- Одежда
('Books', NULL), -- Книги
('Programming Books', 5); -- Книги по программированию (подкатегория Книг)
INSERT INTO products (product_name, price, category_id, stock_quantity) VALUES
('Laptop Pro 15"', 1299.99, 2, 50), -- Ноутбук Pro 15"
('Smartphone X', 899.00, 3, 120), -- Смартфон X
('Wireless Mouse', 25.50, 2, 200), -- Беспроводная мышь
('T-Shirt (Red)', 19.99, 4, 300), -- Футболка (Красная)
('The SQL Enigma', 45.00, 6, 75), -- Загадка SQL
('Advanced Python', 55.00, 6, 60), -- Продвинутый Python
('Generic USB Cable', 9.99, 1, 500), -- Обычный USB-кабель
('Monitor 27"', 299.00, 2, 30); -- Монитор 27" (Принадлежит к категории 'Computers')
INSERT INTO customers (customer_name, email, registration_date) VALUES
('Alice Wonderland', 'alice@example.com', '2023-01-15'), -- Алиса
('Bob The Builder', 'bob@example.com', '2023-02-20'), -- Боб
('Charlie Chaplin', 'charlie@example.com', '2023-03-10'),-- Чарли
('Diana Prince', 'diana@example.com', '2023-04-05'); -- Диана (Новый клиент, пока нет заказов)
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2023-10-01 10:00:00', 'Shipped'), -- Заказ Алисы, отправлен
(2, '2023-10-05 14:30:00', 'Processing'), -- Заказ Боба, в обработке
(1, '2023-10-10 09:15:00', 'Pending'), -- Еще один заказ Алисы, ожидает
(3, '2023-10-12 11:00:00', 'Shipped'); -- Заказ Чарли, отправлен
INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase) VALUES
(1, 1, 1, 1299.99), -- Алиса, Ноутбук
(1, 3, 1, 25.50), -- Алиса, Мышь
(2, 5, 2, 45.00), -- Боб, Книга по SQL x2
(2, 7, 3, 9.99), -- Боб, USB-кабель x3
(3, 2, 1, 899.00), -- Алиса, Смартфон
(4, 4, 2, 19.99), -- Чарли, Футболка x2
(4, 5, 1, 45.00); -- Чарли, Книга по SQL
-- Примечание: Товары 'Advanced Python' и 'Monitor 27"' еще не заказывались.
-- Примечание: У клиента 'Diana Prince' нет заказов.
-- Примечание: В категории 'Clothing' есть футболка, в 'Mobile Phones' - Smartphone X.
Задания для Варианта 1:
- Вывести имена всех клиентов, которые разместили хотя бы один заказ, вместе с ID заказа и датой заказа. Отсортировать по имени клиента, затем по дате заказа.
- Показать все товары, их цены и названия их категорий. Включить товары, которые могут не принадлежать ни к одной категории (если это возможно согласно схеме - подсказка: проверьте тип JOIN).
- Вывести все категории и названия их родительских категорий. Для категорий верхнего уровня (без родительской категории) имя родительской категории должно отображаться как NULL или быть обработано соответствующим образом. (Требуется self-join / соединение таблицы с самой собой).
- Отобразить имена клиентов и общее количество уникальных заказов, которые они разместили. Включить клиентов, которые не разместили ни одного заказа. Отсортировать по количеству заказов по убыванию.
- Найти все товары (названия товаров), которые были заказаны в количестве более 1 в рамках одной позиции заказа. Вывести название товара, ID заказа и количество.
- Вывести названия товаров, которые принадлежат к категории ‘Electronics’ или любой из ее подкатегорий. (Требуется соединение таблицы категорий с самой собой, а затем с таблицей товаров).
- Показать имена клиентов, которые заказали товар с названием ‘The SQL Enigma’.
- Рассчитать общее количество каждого проданного товара по всем заказам. Вывести название товара и общее количество. Включить товары, которые никогда не продавались (показать количество 0). Отсортировать по названию товара.
- Вывести категории, с которыми связано более 2 товаров. Показать название категории и количество товаров.
- Отобразить детали всех позиций заказа: включить ID заказа, имя клиента, название товара, заказанное количество и цену на момент покупки. Отсортировать по ID заказа, затем по названию товара.
Вариант 2: База данных для управления проектами
Сценарий: Вы управляете базой данных компании, которая отслеживает сотрудников, отделы, проекты, задачи в рамках этих проектов, и какой сотрудник назначен на какую задачу.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS assignments;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
-- Создаем таблицу Отделы (departments)
CREATE TABLE departments (
dept_id SERIAL PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL UNIQUE,
location VARCHAR(100)
);
-- Создаем таблицу Сотрудники (employees)
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
job_title VARCHAR(100),
dept_id INT REFERENCES departments(dept_id),
hire_date DATE
);
-- Создаем таблицу Проекты (projects)
CREATE TABLE projects (
project_id SERIAL PRIMARY KEY,
project_name VARCHAR(150) NOT NULL UNIQUE,
start_date DATE,
deadline DATE,
budget DECIMAL(12, 2)
);
-- Создаем таблицу Задачи (tasks) (с самоссылкой для зависимостей)
CREATE TABLE tasks (
task_id SERIAL PRIMARY KEY,
task_name VARCHAR(200) NOT NULL,
project_id INT REFERENCES projects(project_id),
status VARCHAR(20) CHECK (status IN ('Not Started', 'In Progress', 'Completed', 'Blocked')),
depends_on_task_id INT REFERENCES tasks(task_id) NULL -- Для зависимостей задач
);
-- Создаем таблицу Назначения (assignments) (Связующая таблица)
CREATE TABLE assignments (
assignment_id SERIAL PRIMARY KEY,
employee_id INT REFERENCES employees(employee_id),
task_id INT REFERENCES tasks(task_id),
date_assigned DATE DEFAULT CURRENT_DATE,
UNIQUE(employee_id, task_id) -- Сотрудник может быть назначен на конкретную задачу только один раз
);
-- Вставляем примерные данные
INSERT INTO departments (dept_name, location) VALUES
('Engineering', 'Building West'),
('Marketing', 'Building East'),
('Human Resources', 'Building Central'),
('Research', 'Building West');
INSERT INTO employees (employee_name, job_title, dept_id, hire_date) VALUES
('John Smith', 'Software Engineer', 1, '2022-03-15'),
('Jane Doe', 'Project Manager', 1, '2021-06-01'),
('Peter Jones', 'Marketing Specialist', 2, '2022-09-10'),
('Emily White', 'HR Manager', 3, '2020-11-01'),
('Michael Brown', 'Researcher', 4, '2023-01-20'),
('Sarah Green', 'Software Engineer', 1, '2023-05-01'),
('David Lee', 'UI/UX Designer', 1, NULL); -- Нанят, но дата отсутствует
INSERT INTO projects (project_name, start_date, deadline, budget) VALUES
('Alpha Launch', '2023-01-15', '2023-12-31', 50000.00),
('Beta Marketing Campaign', '2023-06-01', '2023-11-30', 25000.00),
('Gamma Research Initiative', '2023-08-01', '2024-07-31', 75000.00),
('Internal Tools Upgrade', '2023-09-01', NULL, 15000.00); -- Срок сдачи еще не установлен
INSERT INTO tasks (task_name, project_id, status, depends_on_task_id) VALUES
('Design Database Schema', 1, 'Completed', NULL), -- Задача 1
('Develop Backend API', 1, 'In Progress', 1), -- Задача 2 зависит от Задачи 1
('Develop Frontend UI', 1, 'In Progress', 1), -- Задача 3 зависит от Задачи 1
('Create Ad Copy', 2, 'Completed', NULL), -- Задача 4
('Launch Social Media Ads', 2, 'Not Started', 4), -- Задача 5 зависит от Задачи 4
('Initial Research Phase', 3, 'In Progress', NULL), -- Задача 6
('Setup Lab Equipment', 3, 'Not Started', NULL), -- Задача 7
('Update Jenkins Server', 4, 'In Progress', NULL); -- Задача 8
-- Задача без проекта (например, внутренняя административная задача)
INSERT INTO tasks (task_name, project_id, status, depends_on_task_id) VALUES
('Review Annual Performance', NULL, 'Not Started', NULL); -- Задача 9
INSERT INTO assignments (employee_id, task_id, date_assigned) VALUES
(1, 1, '2023-01-20'), -- Джон, Проектирование схемы БД
(1, 2, '2023-03-01'), -- Джон, Backend API
(7, 3, '2023-04-15'), -- Дэвид, Frontend UI
(3, 4, '2023-06-10'), -- Питер, Текст рекламы
(3, 5, '2023-09-01'), -- Питер, Реклама в соцсетях
(5, 6, '2023-08-05'), -- Майкл, Начальное исследование
(2, 1, '2023-01-18'), -- Джейн, Проектирование схемы БД (контроль со стороны РМ)
(6, 2, '2023-05-10'); -- Сара, Backend API
-- Примечание: Эмили Уайт (HR) не назначена на задачи проектов.
-- Примечание: Задачи 'Setup Lab Equipment' (Задача 7) и 'Update Jenkins Server' (Задача 8) еще не назначены.
-- Примечание: Задача 'Review Annual Performance' (Задача 9) не назначена и не относится к какому-либо проекту.
Задания для Варианта 2:
- Вывести список всех сотрудников и название отдела, к которому они принадлежат. Включить сотрудников, которые еще не приписаны к отделу. Отсортировать по названию отдела, затем по имени сотрудника.
- Показать все проекты и задачи, связанные с каждым проектом. Включить проекты, для которых в настоящее время нет определенных задач. Вывести Название проекта (Project Name) и Название задачи (Task Name).
- Для каждой задачи вывести ее название и название задачи, от которой она зависит (если таковая имеется). Если задача не имеет зависимости, показать NULL для названия зависимой задачи. (Требуется self-join / самообъединение таблицы).
- Отобразить имена всех сотрудников, которые назначены хотя бы на одну задачу. Вывести имя сотрудника, название задачи и название проекта, к которому относится задача.
- Найти все задачи, которые в настоящее время находятся в статусе ‘In Progress’, и вывести имена сотрудников, назначенных на эти задачи.
- Вывести имена всех сотрудников отдела ‘Engineering’ вместе с названиями задач, на которые они назначены. Если сотрудник отдела ‘Engineering’ не назначен ни на одну задачу, он все равно должен присутствовать в списке (с NULL вместо названия задачи).
- Показать названия проектов, у которых есть хотя бы одна задача со статусом ‘Not Started’. Вывести только уникальные названия проектов.
- Подсчитать количество задач, назначенных каждому сотруднику. Вывести имя сотрудника и количество задач. Включить сотрудников с нулевым количеством назначенных задач. Отсортировать по количеству задач по убыванию.
- Вывести список отделов, в которых работает более 2 сотрудников. Показать название отдела и количество сотрудников в этом отделе.
- Отобразить полную информацию о задачах, которые зависят от задачи с названием ‘Design Database Schema’. Включить название зависимой задачи, ее статус, название проекта, к которому она относится, и название задачи-предшественника (‘Design Database Schema’).
Вариант 3: База данных библиотечной системы
Сценарий: Вы управляете базой данных публичной библиотеки. Система должна отслеживать книги, авторов, жанры, читателей библиотеки (заемщиков) и записи о выдаче книг.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS book_authors;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
DROP TABLE IF EXISTS genres;
-- Создаем таблицу Genres (Жанры)
CREATE TABLE genres (
genre_id SERIAL PRIMARY KEY,
genre_name VARCHAR(100) NOT NULL UNIQUE,
description TEXT
);
-- Создаем таблицу Authors (Авторы)
CREATE TABLE authors (
author_id SERIAL PRIMARY KEY,
author_name VARCHAR(150) NOT NULL,
birth_year INT
);
-- Создаем таблицу Books (Книги) (с самоссылкой для серий)
CREATE TABLE books (
book_id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
isbn VARCHAR(20) UNIQUE,
publication_year INT,
genre_id INT REFERENCES genres(genre_id),
previous_book_in_series_id INT REFERENCES books(book_id) NULL -- Для книжных серий
);
-- Создаем таблицу BookAuthors (Связующая таблица для отношения Многие-ко-Многим между Книгами и Авторами)
CREATE TABLE book_authors (
book_id INT REFERENCES books(book_id),
author_id INT REFERENCES authors(author_id),
PRIMARY KEY (book_id, author_id) -- Составной первичный ключ
);
-- Создаем таблицу Members (Читатели)
CREATE TABLE members (
member_id SERIAL PRIMARY KEY,
member_name VARCHAR(100) NOT NULL,
join_date DATE DEFAULT CURRENT_DATE,
email VARCHAR(100) UNIQUE
);
-- Создаем таблицу Loans (Выдачи книг)
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,
return_date DATE NULL -- NULL, если книга еще не возвращена
);
-- Вставляем примерные данные
INSERT INTO genres (genre_name, description) VALUES
('Science Fiction', 'Художественная литература, посвященная воображаемым концепциям, таким как футуристическая наука и технологии.'),
('Fantasy', 'Жанр художественной литературы, действие которого происходит в вымышленной вселенной, часто вдохновленной мифами и фольклором реального мира.'),
('Mystery', 'Художественная литература, включающая загадочную смерть или преступление, которое необходимо раскрыть.'),
('Programming', 'Книги, связанные с языками и техниками компьютерного программирования.'),
('History', 'Книги, подробно описывающие прошлые события.');
INSERT INTO authors (author_name, birth_year) VALUES
('Isaac Asimov', 1920),
('J.R.R. Tolkien', 1892),
('Agatha Christie', 1890),
('Charles Petzold', 1953),
('Ada Lovelace', 1815), -- Историческая личность, включена для контекста программирования
('Yuval Noah Harari', 1976);
INSERT INTO books (title, isbn, publication_year, genre_id, previous_book_in_series_id) VALUES
('Foundation', '978-0553293357', 1951, 1, NULL), -- Книга 1
('Foundation and Empire', '978-0553293371', 1952, 1, 1), -- Книга 2 (зависит от Книги 1)
('The Hobbit', '978-0547928227', 1937, 2, NULL), -- Книга 3
('Murder on the Orient Express', '978-0062073501', 1934, 3, NULL), -- Книга 4
('Code: The Hidden Language', '978-0735611313', 1999, 4, NULL), -- Книга 5
('Notes on the Analytical Engine', NULL, 1843, 4, NULL), -- Книга 6 (Без ISBN)
('Sapiens: A Brief History of Humankind', '978-0062316097', 2011, 5, NULL); -- Книга 7
INSERT INTO book_authors (book_id, author_id) VALUES
(1, 1), (2, 1), -- Книги Азимова
(3, 2), -- Книга Толкина
(4, 3), -- Книга Кристи
(5, 4), -- Книга Петцольда
(6, 5), -- Книга Лавлейс (историческая)
(7, 6); -- Книга Харари
INSERT INTO members (member_name, join_date, email) VALUES
('Alice Smith', '2023-01-10', 'alice.s@email.com'),
('Bob Johnson', '2022-11-05', 'bob.j@email.com'),
('Charlie Davis', '2023-05-20', 'charlie.d@email.com'),
('Diana Miller', '2023-09-01', 'diana.m@email.com'); -- Новый читатель, еще не брал книги
INSERT INTO loans (book_id, member_id, loan_date, due_date, return_date) VALUES
(1, 1, '2023-08-01', '2023-08-15', '2023-08-14'), -- 'Foundation', Алиса (Возвращена)
(3, 2, '2023-08-10', '2023-08-24', '2023-08-25'), -- 'The Hobbit', Боб (Возвращена с опозданием)
(4, 1, '2023-09-05', '2023-09-19', NULL), -- 'Orient Express', Алиса (Сейчас на руках)
(5, 3, '2023-09-10', '2023-09-24', NULL), -- 'Code', Чарли (Сейчас на руках)
(1, 3, '2023-09-15', '2023-09-29', NULL); -- 'Foundation', Чарли (Сейчас на руках)
-- Примечание: Книги 'Foundation and Empire', 'Notes on the Analytical Engine', 'Sapiens' еще не выдавались.
-- Примечание: У читателя 'Diana Miller' нет записей о выдаче книг.
-- Примечание: В этом примере не используются книги с несколькими авторами, но схема это поддерживает.
Задания для Варианта 3:
- Выведите список всех книг и их жанров. Включите книги, которым жанр может быть не назначен. Покажите Название книги (Book Title) и Название жанра (Genre Name).
- Покажите всех авторов и названия написанных ими книг. Используйте таблицу
book_authorsдля связи. Включите авторов, у которых может не быть книг в таблицеbooks(если такие есть). - Для каждой книги, являющейся частью серии (т.е. имеющей
previous_book_in_series_id), выведите название книги и название предыдущей книги в серии. (Требуется самосоединение - self-join). - Отобразите имена читателей, у которых в настоящее время есть хотя бы одна книга на руках (т.е.
return_dateравно NULL). Выведите имя читателя и название книги (книг), которые он(и) взял(и). - Найдите все книги, написанные ‘Isaac Asimov’. Выведите названия книг и год их публикации.
- Выведите имена всех читателей и названия книг, которые они брали, включая прошлые выдачи (где
return_dateне NULL). Если читатель никогда не брал книг, он все равно должен появиться в списке (с NULL вместо названия книги). Отсортируйте по имени читателя, затем по дате выдачи (loan_date). - Покажите названия книг, принадлежащих к жанру ‘Science Fiction’ И опубликованных после 1950 года.
- Подсчитайте общее количество раз, которое каждая книга была выдана. Выведите название книги и общее количество выдач. Включите книги, которые никогда не выдавались (покажите количество 0). Отсортируйте по количеству выдач по убыванию.
- Выведите жанры, с которыми связано более 1 книги. Покажите название жанра и количество книг в этом жанре.
- Отобразите полную информацию о книгах, находящихся в настоящее время на руках (
return_dateравно NULL): включите ID выдачи (loan_id), имя читателя, название книги, имя автора (авторов) (обработайте случай с несколькими авторами, если необходимо), дату выдачи (loan_date) и срок возврата (due_date).
Вариант 4: База данных расписания курсов университета
Сценарий: Вы проектируете базу данных для университета с целью управления расписанием курсов, включая кафедры, преподавателей, курсы, аудитории и конкретные временные интервалы проведения занятий. Студенты также записываются на эти запланированные занятия.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS schedule;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS instructors;
DROP TABLE IF EXISTS classrooms;
DROP TABLE IF EXISTS departments;
-- Создаем таблицу Departments (Кафедры)
CREATE TABLE departments (
dept_id SERIAL PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL UNIQUE,
office_location VARCHAR(100)
);
-- Создаем таблицу Instructors (Преподаватели)
CREATE TABLE instructors (
instructor_id SERIAL PRIMARY KEY,
instructor_name VARCHAR(100) NOT NULL,
dept_id INT REFERENCES departments(dept_id)
);
-- Создаем таблицу Classrooms (Аудитории)
CREATE TABLE classrooms (
classroom_id SERIAL PRIMARY KEY,
building VARCHAR(50) NOT NULL,
room_number VARCHAR(10) NOT NULL,
capacity INT CHECK (capacity > 0),
UNIQUE(building, room_number)
);
-- Создаем таблицу Courses (Курсы)
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_code VARCHAR(10) NOT NULL UNIQUE, -- например, CS101
course_title VARCHAR(150) NOT NULL,
credits INT CHECK (credits > 0),
dept_id INT REFERENCES departments(dept_id)
);
-- Создаем таблицу Schedule (Расписание) (Связывает Course, Instructor, Classroom, Time)
CREATE TABLE schedule (
schedule_id SERIAL PRIMARY KEY,
course_id INT REFERENCES courses(course_id),
instructor_id INT REFERENCES instructors(instructor_id),
classroom_id INT REFERENCES classrooms(classroom_id),
semester VARCHAR(20) NOT NULL, -- например, 'Осень 2023'
day_of_week VARCHAR(10) CHECK (day_of_week IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')),
start_time TIME,
end_time TIME,
UNIQUE(classroom_id, semester, day_of_week, start_time) -- Предотвращает бронирование одной и той же аудитории на одно и то же время
);
-- Создаем таблицу Students (Студенты)
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
student_name VARCHAR(100) NOT NULL,
major_dept_id INT REFERENCES departments(dept_id),
enrollment_year INT
);
-- Создаем таблицу Enrollments (Записи на курсы) (Связующая таблица для Students и Schedule)
CREATE TABLE enrollments (
enrollment_id SERIAL PRIMARY KEY,
student_id INT REFERENCES students(student_id),
schedule_id INT REFERENCES schedule(schedule_id),
grade CHAR(1) CHECK (grade IN ('A', 'B', 'C', 'D', 'F', NULL)), -- Разрешает NULL для текущих/незавершенных курсов
UNIQUE(student_id, schedule_id) -- Студент записывается на конкретное занятие в расписании только один раз
);
-- Вставляем примерные данные
INSERT INTO departments (dept_name, office_location) VALUES
('Computer Science', 'Tech Hall'),
('Mathematics', 'Math Tower'),
('Physics', 'Science Wing'),
('Literature', 'Arts Building');
INSERT INTO instructors (instructor_name, dept_id) VALUES
('Dr. Elara Vance', 1), ('Prof. Ben Carter', 1),
('Dr. Anya Sharma', 2), ('Dr. Kenji Tanaka', 3),
('Prof. Olivia Green', 4), ('Dr. Leo Maxwell', NULL); -- В настоящее время не назначен (на кафедру)
INSERT INTO classrooms (building, room_number, capacity) VALUES
('Tech Hall', '101', 50), ('Tech Hall', '205', 30),
('Math Tower', '314', 40), ('Science Wing', '110 Lab', 25),
('Arts Building', 'Auditorium', 150);
INSERT INTO courses (course_code, course_title, credits, dept_id) VALUES
('CS101', 'Intro to Computing', 3, 1), ('CS305', 'Databases', 4, 1),
('MA201', 'Calculus II', 4, 2), ('PH101', 'General Physics I', 4, 3),
('LT220', 'Shakespeare', 3, 4), ('MA101', 'Calculus I', 4, 2);
INSERT INTO schedule (course_id, instructor_id, classroom_id, semester, day_of_week, start_time, end_time) VALUES
(1, 1, 1, 'Fall 2023', 'Monday', '09:00:00', '10:15:00'), -- CS101, Vance, Tech 101
(1, 1, 1, 'Fall 2023', 'Wednesday', '09:00:00', '10:15:00'), -- CS101, Vance, Tech 101
(2, 2, 2, 'Fall 2023', 'Tuesday', '10:30:00', '12:00:00'), -- CS305, Carter, Tech 205
(2, 2, 2, 'Fall 2023', 'Thursday', '10:30:00', '12:00:00'), -- CS305, Carter, Tech 205
(3, 3, 3, 'Fall 2023', 'Monday', '13:00:00', '14:30:00'), -- MA201, Sharma, Math 314
(4, 4, 4, 'Fall 2023', 'Wednesday', '11:00:00', '12:30:00'),-- PH101, Tanaka, Sci 110
(5, 5, 5, 'Fall 2023', 'Friday', '14:00:00', '16:00:00'); -- LT220, Green, Auditorium
INSERT INTO students (student_name, major_dept_id, enrollment_year) VALUES
('Michael Lee', 1, 2022), ('Sophia Chen', 2, 2021),
('Ethan Garcia', 1, 2023), ('Isabella Rossi', 4, 2022),
('Noah Kim', NULL, 2023); -- Не определился (с основной кафедрой)
INSERT INTO enrollments (student_id, schedule_id, grade) VALUES
(1, 1, NULL), -- Michael, CS101 Пн
(1, 2, NULL), -- Michael, CS101 Ср
(1, 3, NULL), -- Michael, CS305 Вт
(2, 5, NULL), -- Sophia, MA201 Пн
(3, 1, NULL), -- Ethan, CS101 Пн
(4, 7, 'A'), -- Isabella, LT220 Пт (Предполагается, что курс завершен досрочно или это промежуточная оценка)
(2, 3, NULL); -- Sophia, CS305 Вт (запись студента с другой специальности)
Задания для Варианта 4:
- Вывести список всех запланированных занятий за семестр ‘Fall 2023’. Покажите название курса, имя преподавателя, здание, номер аудитории, день недели, время начала и время окончания. Отсортируйте по названию курса, затем по дню недели.
- Найти все курсы, предлагаемые кафедрой ‘Computer Science’. Включите код курса, название и количество кредитов.
- Вывести список всех преподавателей и кафедру, к которой они относятся. Включите преподавателей, которые в настоящее время не приписаны к кафедре. Отсортируйте по названию кафедры (NULL значения в конце), затем по имени преподавателя.
- Показать имена студентов, записанных на курс ‘CS305’ (Databases) в семестре ‘Fall 2023’. Подсказка: Вам потребуется объединить (join) таблицы students, enrollments, schedule и courses.
- Вывести список всех аудиторий в здании ‘Tech Hall’ и их вместимость. Отсортируйте по номеру аудитории.
- Найти имена преподавателей, которые ведут более одного различного курса в семестре ‘Fall 2023’. (Учитывайте course_id, а не schedule_id).
- Отобразить расписание для ‘Dr. Elara Vance’ на семестр ‘Fall 2023’. Покажите название курса, день недели, время начала, время окончания, здание и номер аудитории.
- Подсчитать количество курсов, предлагаемых каждой кафедрой. Вывести название кафедры и количество курсов. Включите кафедры, которые не предлагают курсов (если такие существуют). Отсортируйте по количеству курсов по убыванию.
- Найти всех студентов, чья основная специальность (major) - ‘Computer Science’, и вывести названия курсов, на которые они записаны в настоящее время в семестре ‘Fall 2023’ (где оценка равна NULL).
- Вывести список всех аудиторий с вместимостью более 35 человек, вместе с названием курса и именем преподавателя для любых занятий, запланированных в них на семестр ‘Fall 2023’. Включите большие аудитории, даже если в них нет запланированных занятий. Отсортируйте по зданию, затем по номеру аудитории.
Вариант 5: База данных отзывов о товарах интернет-магазина
Сценарий: Вы управляете базой данных интернет-магазина, который позволяет пользователям оставлять отзывы о товарах. Система отслеживает пользователей, товары, категории (с подкатегориями) и отзывы, оставленные пользователями.
Схема:
-- Удаляем таблицы, если они существуют (для чистого старта)
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS users;
-- Создаем таблицу Users (Пользователи)
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) UNIQUE,
registration_date DATE DEFAULT CURRENT_DATE
);
-- Создаем таблицу Categories (Категории) (с самоссылкой для подкатегорий)
CREATE TABLE categories (
category_id SERIAL PRIMARY KEY,
category_name VARCHAR(100) NOT NULL UNIQUE,
parent_category_id INT REFERENCES categories(category_id) NULL -- Для подкатегорий
);
-- Создаем таблицу Products (Товары)
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(150) NOT NULL,
description TEXT,
price DECIMAL(10, 2) CHECK (price > 0),
category_id INT REFERENCES categories(category_id),
added_date DATE DEFAULT CURRENT_DATE
);
-- Создаем таблицу Reviews (Отзывы)
CREATE TABLE reviews (
review_id SERIAL PRIMARY KEY,
product_id INT REFERENCES products(product_id) ON DELETE CASCADE, -- Если товар удален, удаляем отзывы
user_id INT REFERENCES users(user_id) ON DELETE SET NULL, -- Если пользователь удален, сохраняем отзыв, но удаляем связь с пользователем
rating INT CHECK (rating >= 1 AND rating <= 5),
review_text TEXT,
review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Вставляем примерные данные
INSERT INTO users (username, email, registration_date) VALUES
('AliceR', 'alice.r@example.com', '2023-02-10'),
('BobS', 'bob.s@example.com', '2023-03-15'),
('CharlieT', 'charlie.t@example.com', '2023-04-20'),
('DianaP', 'diana.p@example.com', '2023-05-25'); -- Пользователь пока без отзывов
INSERT INTO categories (category_name, parent_category_id) VALUES
('Electronics', NULL), ('Books', NULL),
('Computers & Accessories', 1), ('Fiction Books', 2),
('Keyboards', 3), ('Science Fiction', 4);
INSERT INTO products (product_name, description, price, category_id) VALUES
('Mechanical Keyboard K7', 'RGB Backlit Mechanical Keyboard', 79.99, 5),
('Wireless Ergonomic Mouse', 'Comfortable mouse for long use', 45.50, 3),
('Dune by Frank Herbert', 'Classic Science Fiction Novel', 15.99, 6),
('The Three-Body Problem', 'Hard Sci-Fi by Cixin Liu', 18.00, 6),
('Laptop Stand', 'Adjustable aluminum laptop stand', 29.99, 3),
('Webcam HD 1080p', 'High definition webcam for streaming', 55.00, 1); -- Категория 'Electronics' (Электроника)
INSERT INTO reviews (product_id, user_id, rating, review_text, review_date) VALUES
(1, 1, 5, 'Amazing keyboard, great feel!', '2023-06-01 10:00:00'),
(1, 2, 4, 'Very good, but a bit loud.', '2023-06-05 14:20:00'),
(2, 1, 4, 'Comfortable mouse, tracks well.', '2023-06-02 11:30:00'),
(3, 3, 5, 'A timeless classic!', '2023-07-10 09:00:00'),
(4, 1, 5, 'Mind-bending science fiction!', '2023-07-15 16:45:00'),
(4, 3, 4, 'Great story, complex ideas.', '2023-07-18 12:10:00'),
(5, 2, 3, 'Stand is okay, a bit wobbly.', '2023-08-01 15:00:00');
-- Примечание: У товара 'Webcam HD 1080p' пока нет отзывов.
-- Примечание: Пользователь 'DianaP' не оставил ни одного отзыва.
-- Примечание: Категория 'Fiction Books' не содержит товаров напрямую, только подкатегорию 'Science Fiction'.
Задания для Варианта 5:
- Вывести список всех товаров и названий их категорий. Включить товары, которые могут быть не привязаны к категории. Отсортировать по названию товара.
- Показать все отзывы, включая название товара, имя пользователя (username) рецензента, рейтинг и текст отзыва. Отсортировать по дате отзыва по убыванию.
- Вывести список всех категорий и названий их родительских категорий. Для категорий верхнего уровня имя родительской категории должно быть NULL. (Требуется самосоединение - self-join).
- Найти средний рейтинг для каждого товара, у которого есть хотя бы один отзыв. Вывести название товара и его средний рейтинг. Отсортировать по среднему рейтингу по убыванию.
- Вывести имена пользователей (usernames), которые поставили оценку 5 звезд любому товару. Показать имя пользователя и название товара, которому они поставили 5 звезд.
- Отобразить все товары, принадлежащие категории ‘Computers & Accessories’ или любой из ее подкатегорий (например, ‘Keyboards’). Показать название товара и его цену. (Требуется соединение таблицы категорий с самой собой, а затем с таблицей товаров).
- Найти все отзывы, написанные пользователем ‘AliceR’. Включить ID отзыва, название товара, рейтинг и текст отзыва.
- Подсчитать количество отзывов, оставленных каждым пользователем. Вывести имя пользователя (username) и общее количество отзывов. Включить пользователей, которые не оставили ни одного отзыва (ноль отзывов). Отсортировать по количеству отзывов по убыванию.
- Вывести список категорий, содержащих товары со средним рейтингом выше 4.0. Показать название категории и рассчитанный средний рейтинг для товаров в этой категории. Подсказка: Это может потребовать использования подзапросов или соединения агрегированных результатов.
- Показать названия товаров, которые еще не получили ни одного отзыва.
Вариант 6: База данных учета пациентов больницы
Сценарий: Представьте, что вы управляете упрощенной базой данных для больницы или клиники. Система ведет учет пациентов, врачей, медицинских специальностей, записей на прием и диагнозов, поставленных во время этих приемов.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS diagnoses;
DROP TABLE IF EXISTS appointments;
DROP TABLE IF EXISTS patients;
DROP TABLE IF EXISTS doctors;
DROP TABLE IF EXISTS specialties;
-- Создаем таблицу специальностей (Медицинские отделения)
CREATE TABLE specialties (
specialty_id SERIAL PRIMARY KEY,
specialty_name VARCHAR(100) NOT NULL UNIQUE,
description TEXT
);
-- Создаем таблицу врачей
CREATE TABLE doctors (
doctor_id SERIAL PRIMARY KEY,
doctor_name VARCHAR(100) NOT NULL,
specialty_id INT REFERENCES specialties(specialty_id),
phone_number VARCHAR(20)
);
-- Создаем таблицу пациентов
CREATE TABLE patients (
patient_id SERIAL PRIMARY KEY,
patient_name VARCHAR(100) NOT NULL,
date_of_birth DATE,
gender VARCHAR(10) CHECK (gender IN ('Male', 'Female', 'Other', 'Unknown'))
);
-- Создаем таблицу записей на прием
CREATE TABLE appointments (
appointment_id SERIAL PRIMARY KEY,
patient_id INT REFERENCES patients(patient_id),
doctor_id INT REFERENCES doctors(doctor_id),
appointment_date TIMESTAMP NOT NULL, -- Включает дату и время
room_number VARCHAR(10),
reason_for_visit TEXT
);
-- Создаем таблицу диагнозов (Связывание состояний с приемами)
CREATE TABLE diagnoses (
diagnosis_id SERIAL PRIMARY KEY,
appointment_id INT REFERENCES appointments(appointment_id) UNIQUE, -- Для простоты предполагаем один основной диагноз на прием
condition_name VARCHAR(150) NOT NULL,
diagnosis_notes TEXT
);
-- Вставляем примерные данные
INSERT INTO specialties (specialty_name, description) VALUES
('Cardiology', 'Занимается заболеваниями сердца.'),
('Neurology', 'Занимается расстройствами нервной системы.'),
('Pediatrics', 'Раздел медицины, занимающийся детьми и их болезнями.'),
('General Practice', 'Предоставляет плановую медицинскую помощь.');
INSERT INTO doctors (doctor_name, specialty_id, phone_number) VALUES
('Dr. Evelyn Reed', 1, '555-1111'),
('Dr. Marcus Chen', 2, '555-2222'),
('Dr. Sofia Ramirez', 3, '555-3333'),
('Dr. Ben Carter', 4, '555-4444'),
('Dr. Chloe Jenkins', 1, '555-1112'); -- Еще один кардиолог
INSERT INTO patients (patient_name, date_of_birth, gender) VALUES
('John Doe', '1985-06-15', 'Male'),
('Jane Smith', '1992-11-20', 'Female'),
('Michael Lee', '2018-03-10', 'Male'),
('Emily White', '1970-09-01', 'Female'),
('David Brown', '1995-01-25', 'Male'); -- Пациент пока без записей на прием
INSERT INTO appointments (patient_id, doctor_id, appointment_date, room_number, reason_for_visit) VALUES
(1, 1, '2023-09-01 10:00:00', 'C101', 'Chest pain'),
(2, 4, '2023-09-05 11:30:00', 'G205', 'Annual check-up'),
(3, 3, '2023-09-10 09:15:00', 'P314', 'Fever and cough'),
(1, 2, '2023-09-12 14:00:00', 'N110', 'Headaches'),
(4, 1, '2023-09-15 10:30:00', 'C102', 'Follow-up appointment'),
(2, 4, '2023-10-02 11:00:00', 'G205', 'Flu shot'); -- Предстоящий прием
INSERT INTO diagnoses (appointment_id, condition_name, diagnosis_notes) VALUES
(1, 'Angina Pectoris', 'Назначены лекарства, необходим повторный прием.'),
(2, 'Healthy', 'Плановый осмотр, все в порядке.'),
(3, 'Viral Infection', 'Рекомендован покой и обильное питье.'),
(4, 'Migraine', 'Посоветовали методы снижения стресса.'),
(5, 'Stable Angina', 'Продолжать прием лекарств, контролировать артериальное давление.');
-- Примечание: Для приема 6 (Прививка от гриппа) еще нет записи о диагнозе.
-- Примечание: У пациента David Brown нет записей на прием.
-- Примечание: У доктора Chloe Jenkins нет записей на прием в этих данных.
Задания для Варианта 6:
- Вывести список всех врачей и название их специальности. Включить врачей, которые могут быть не привязаны к специальности (если это возможно согласно схеме). Отсортировать по названию специальности, затем по имени врача.
- Показать все записи на прием, включая имя пациента, имя врача, дату/время приема и причину визита. Отсортировать по дате приема.
- Найти всех пациентов, рожденных после 1 января 1990 года. Вывести их имя и дату рождения.
- Отобразить детали всех записей на прием к врачам специальности ‘Cardiology’ (Кардиология). Включить имя пациента, имя врача, дату приема и причину визита.
- Вывести имена всех пациентов, у которых был прием, где диагностированным состоянием была ‘Migraine’ (Мигрень).
- Показать всех пациентов и дату их самого последнего приема. Включить пациентов, у которых никогда не было приемов (показать NULL для даты). Подсказка: Требуется LEFT JOIN и агрегация (MAX) с группировкой по пациенту.
- Найти все записи на прием, запланированные на ‘2023-09-10’ или позднее. Вывести имя пациента, имя врача и полную дату/время приема.
- Подсчитать количество приемов, проведенных каждым врачом. Вывести имя врача и количество приемов. Включить врачей с нулевым количеством приемов. Отсортировать по количеству приемов по убыванию.
- Вывести список специальностей, к которым приписано более одного врача. Показать название специальности и количество врачей.
- Отобразить полную информацию о приемах, по которым был поставлен диагноз. Включить имя пациента, имя врача, название специальности, дату приема, название диагноза (состояния) и примечания к диагнозу. Отсортировать по дате приема.
Вариант 7: База данных системы бронирования авиабилетов
Сценарий: Вы управляете базой данных для системы бронирования авиабилетов. В базе данных хранится информация об авиакомпаниях, аэропортах, рейсах между аэропортами, пассажирах и их бронированиях на конкретные рейсы.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS flights;
DROP TABLE IF EXISTS passengers;
DROP TABLE IF EXISTS airlines;
DROP TABLE IF EXISTS airports;
-- Создаем таблицу Airports (Аэропорты)
CREATE TABLE airports (
airport_code CHAR(3) PRIMARY KEY, -- например, 'JFK', 'LAX'
airport_name VARCHAR(100) NOT NULL,
city VARCHAR(100),
country VARCHAR(100)
);
-- Создаем таблицу Airlines (Авиакомпании)
CREATE TABLE airlines (
airline_id SERIAL PRIMARY KEY,
airline_name VARCHAR(100) NOT NULL UNIQUE,
headquarters_country VARCHAR(100)
);
-- Создаем таблицу Flights (Рейсы) (ссылается на Airports дважды и на Airlines)
CREATE TABLE flights (
flight_id SERIAL PRIMARY KEY,
flight_number VARCHAR(10) NOT NULL,
airline_id INT REFERENCES airlines(airline_id),
departure_airport CHAR(3) REFERENCES airports(airport_code),
arrival_airport CHAR(3) REFERENCES airports(airport_code),
departure_time TIMESTAMP,
arrival_time TIMESTAMP,
base_price DECIMAL(10, 2) CHECK (base_price > 0)
);
-- Создаем таблицу Passengers (Пассажиры)
CREATE TABLE passengers (
passenger_id SERIAL PRIMARY KEY,
passenger_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
date_of_birth DATE
);
-- Создаем таблицу Bookings (Бронирования) (Связующая таблица)
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(5), -- например, '12A', '3F'
status VARCHAR(20) CHECK (status IN ('Confirmed', 'Cancelled', 'Pending')),
UNIQUE(flight_id, passenger_id) -- Пассажир бронируется только один раз на рейс
);
-- Вставляем примерные данные
INSERT INTO airports (airport_code, airport_name, city, country) VALUES
('JFK', 'John F. Kennedy International Airport', 'New York', 'USA'),
('LAX', 'Los Angeles International Airport', 'Los Angeles', 'USA'),
('LHR', 'London Heathrow Airport', 'London', 'UK'),
('CDG', 'Charles de Gaulle Airport', 'Paris', 'France'),
('HND', 'Tokyo Haneda Airport', 'Tokyo', 'Japan');
INSERT INTO airlines (airline_name, headquarters_country) VALUES
('Global Airways', 'USA'),
('EuroFly', 'France'),
('Pacific Wings', 'Japan'),
('Transatlantic Express', NULL); -- Страна штаб-квартиры не указана
INSERT INTO flights (flight_number, airline_id, departure_airport, arrival_airport, departure_time, arrival_time, base_price) VALUES
('GA101', 1, 'JFK', 'LAX', '2024-01-15 08:00:00', '2024-01-15 11:30:00', 350.00),
('GA102', 1, 'LAX', 'JFK', '2024-01-15 13:00:00', '2024-01-15 21:30:00', 360.00),
('EF201', 2, 'CDG', 'LHR', '2024-01-16 10:00:00', '2024-01-16 10:30:00', 120.00),
('EF202', 2, 'LHR', 'CDG', '2024-01-16 14:00:00', '2024-01-16 16:30:00', 110.00),
('PW301', 3, 'HND', 'LAX', '2024-01-17 22:00:00', '2024-01-17 15:00:00', 850.00), -- Пересекает линию смены дат
('GA105', 1, 'JFK', 'LHR', '2024-01-18 19:00:00', '2024-01-19 07:00:00', 620.00); -- Ночной рейс
-- Рейс без назначенной авиакомпании (возможно, заполнитель для код-шеринга)
INSERT INTO flights (flight_number, airline_id, departure_airport, arrival_airport, departure_time, arrival_time, base_price) VALUES
('XX999', NULL, 'LAX', 'CDG', '2024-01-20 15:00:00', '2024-01-21 11:00:00', 700.00); -- Рейс 7
INSERT INTO passengers (passenger_name, email, date_of_birth) VALUES
('Alice Green', 'alice.g@mail.com', '1990-05-15'),
('Bob White', 'bob.w@mail.com', '1985-11-22'),
('Charlie Black', 'charlie.b@mail.com', '1998-02-10'),
('Diana Blue', 'diana.b@mail.com', '2001-07-30'); -- Новый пассажир, пока без бронирований
INSERT INTO bookings (flight_id, passenger_id, booking_date, seat_number, status) VALUES
(1, 1, '2023-12-01', '10A', 'Confirmed'), -- Элис на GA101
(2, 1, '2023-12-01', '12B', 'Confirmed'), -- Элис на GA102
(3, 2, '2023-12-05', '5C', 'Confirmed'), -- Боб на EF201
(5, 3, '2023-12-10', '22F', 'Confirmed'), -- Чарли на PW301
(1, 2, '2023-12-15', '11A', 'Pending'); -- Боб на GA101 (в ожидании)
-- Примечание: На рейс GA105 (JFK-LHR) пока нет бронирований.
-- Примечание: На рейс XX999 (LAX-CDG) пока нет бронирований.
-- Примечание: У пассажира Дианы Блю нет бронирований.
-- Примечание: У авиакомпании Transatlantic Express пока нет рейсов.
Задания для Варианта 7:
- Вывести все подтвержденные (
Confirmed) бронирования, указав имя пассажира, номер рейса, название авиакомпании, название аэропорта отправления и название аэропорта прибытия. - Показать все рейсы, вылетающие из ‘John F. Kennedy International Airport’ (JFK). Укажите номер рейса, название авиакомпании, название аэропорта назначения и время отправления. Отсортируйте результат по времени отправления.
- Вывести список всех авиакомпаний и количество рейсов, связанных с каждой из них в таблице
flights. Включите в список авиакомпании, у которых может быть ноль рейсов. Отсортируйте результат по количеству рейсов по убыванию. - Отобразить имена пассажиров, которые забронировали рейсы авиакомпании ‘Global Airways’. Имя каждого пассажира должно быть указано только один раз.
- Найти все рейсы, вылетающие из ‘USA’ (США) и прибывающие в ‘UK’ (Великобританию). Вывести номер рейса, город отправления и город прибытия. (Требуется дважды соединить таблицу
flightsс таблицейairports). - Показать всех пассажиров и общее количество их бронирований (независимо от статуса). Включите в список пассажиров, у которых нет бронирований (ноль бронирований). Отсортируйте результат по количеству бронирований по убыванию.
- Вывести номер рейса, название аэропорта отправления и название аэропорта прибытия для всех рейсов. Также укажите название авиакомпании, выполняющей рейс. Включите в результат рейсы, даже если информация об их авиакомпании отсутствует (является NULL).
- Найти аэропорты, из которых вылетает более одного рейса. Выведите название аэропорта, город и количество вылетающих рейсов. Отсортируйте результат по количеству рейсов по убыванию.
- Вывести список всех пассажиров, у которых есть хотя бы одно бронирование со статусом ‘Pending’ (в ожидании). Укажите имя пассажира и номер рейса для соответствующего бронирования в ожидании.
- Отобразить детали рейсов, базовая цена которых (
base_price) превышает $500. Укажите номер рейса, название авиакомпании, название аэропорта отправления, название аэропорта прибытия и базовую цену.
Вариант 8: База данных музыкального стримингового сервиса
Сценарий: Вы разрабатываете базу данных для музыкального стримингового сервиса. Она должна отслеживать исполнителей, альбомы, треки в альбомах, пользователей (слушателей), плейлисты, созданные пользователями, и какие треки находятся в каких плейлистах.
Схема:
-- Удаление таблиц, если они существуют (для чистого старта)
DROP TABLE IF EXISTS playlist_tracks;
DROP TABLE IF EXISTS playlists;
DROP TABLE IF EXISTS users;
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(150) NOT NULL UNIQUE,
country VARCHAR(100)
);
-- Создание таблицы Альбомы
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),
composer VARCHAR(150) NULL -- Разрешено NULL, если композитор неизвестен/не применимо
);
-- Создание таблицы Пользователи
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,
playlist_name VARCHAR(100) NOT NULL,
user_id INT REFERENCES users(user_id), -- Владелец плейлиста
creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_public BOOLEAN DEFAULT TRUE
);
-- Создание таблицы PlaylistTracks (связующая таблица для отношения "многие ко многим" между Плейлистами и Треками)
CREATE TABLE playlist_tracks (
playlist_id INT REFERENCES playlists(playlist_id),
track_id INT REFERENCES tracks(track_id),
date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (playlist_id, track_id) -- Составной первичный ключ
);
-- Вставка тестовых данных
INSERT INTO artists (artist_name, country) VALUES
('The Quantizers', 'USA'),
('Syntax Sisters', 'UK'),
('Data Miners', 'Canada'),
('Algorithm Al', NULL); -- Страна неизвестна
INSERT INTO albums (album_title, artist_id, release_year, genre) VALUES
('Boolean Boogie', 1, 2020, 'Electro-Rock'),
('Abstract Harmony', 2, 2021, 'Synth-Pop'),
('Data Streams', 3, 2022, 'Ambient Techno'),
('Recursive Rhythms', 1, 2023, 'Electro-Rock'),
('Solo Cipher', 4, 2022, 'Experimental'); -- Альбом от Algorithm Al
-- Альбом без связанного исполнителя (возможно, сборник)
INSERT INTO albums (album_title, artist_id, release_year, genre) VALUES
('Various Vibes', NULL, 2023, 'Compilation'); -- Альбом 6
INSERT INTO tracks (track_title, album_id, duration_seconds, composer) VALUES
('Binary Beat', 1, 245, 'The Quantizers'),
('Logic Loop', 1, 180, 'The Quantizers'),
('Null Pointer Pop', 2, 210, 'Syntax Sisters'),
('K-Means Groove', 3, 360, 'Data Miners'),
('Forest Flow', 3, 420, 'Data Miners'),
('Callback Funk', 4, 200, 'The Quantizers'), -- Из альбома Recursive Rhythms
('Cipher Suite Swing', 5, 190, 'Algorithm Al'),
('Compilation Track 1', 6, 180, NULL), -- Трек 8
('Compilation Track 2', 6, 220, NULL); -- Трек 9
-- Трек без альбома
INSERT INTO tracks (track_title, album_id, duration_seconds, composer) VALUES
('Standalone Single', NULL, 150, 'Syntax Sisters'); -- Трек 10
INSERT INTO users (username, email, join_date) VALUES
('coder_chris', 'chris@example.com', '2022-01-15'),
('music_megan', 'megan@example.com', '2022-06-20'),
('listener_liam', 'liam@example.com', '2023-03-01'),
('sampler_sam', 'sam@example.com', '2023-08-10'); -- Новый пользователь, без плейлистов
INSERT INTO playlists (playlist_name, user_id, is_public) VALUES
('Coding Focus', 1, true),
('Synth Waves', 2, true),
('Techno Chill', 1, false),
('Workout Mix', 2, true);
INSERT INTO playlist_tracks (playlist_id, track_id) VALUES
(1, 1), (1, 4), (1, 5), -- Coding Focus: Binary Beat, K-Means Groove, Forest Flow
(2, 3), (2, 7), -- Synth Waves: Null Pointer Pop, Cipher Suite Swing
(3, 4), (3, 5), -- Techno Chill: K-Means Groove, Forest Flow
(4, 1), (4, 6); -- Workout Mix: Binary Beat, Callback Funk
-- Примечание: Трек 'Logic Loop' (Трек 2), 'Compilation Track 1/2' (8,9) и 'Standalone Single' (10) пока не включены в плейлисты.
-- Примечание: Пользователи 'listener_liam' и 'sampler_sam' не имеют плейлистов.
Задания для варианта 8:
- Выведите все треки, включая их название, название альбома, к которому они относятся, и имя исполнителя, связанного с альбомом. Учтите треки, которые могут не быть связаны с альбомом.
- Покажите имена всех пользователей и названия плейлистов, которые они создали. Включите пользователей, которые не создали ни одного плейлиста. Сортируйте по имени пользователя, затем по названию плейлиста.
- Выведите всех исполнителей и названия альбомов, которые они выпустили. Включите исполнителей, у которых может не быть альбомов.
- Отобразите названия треков и их длительность (в секундах) для всех треков, находящихся в плейлисте ‘Coding Focus’.
- Найдите все альбомы, выпущенные исполнителем ‘The Quantizers’. Выведите название альбома и год выпуска.
- Выведите названия всех треков, относящихся к жанру ‘Ambient Techno’. Укажите название трека, название альбома и имя исполнителя.
- Покажите имена пользователей, которые создали плейлисты, содержащие трек ‘K-Means Groove’. Убедитесь, что каждое имя пользователя отображается только один раз.
- Подсчитайте общее количество треков в каждом альбоме. Выведите название альбома и количество треков. Включите альбомы, у которых может быть ноль треков (если это возможно по схеме). Сортируйте по названию альбома.
- Выведите исполнителей, которые выпустили альбомы в более чем одном жанре. Покажите имя исполнителя и количество различных жанров.
- Отобразите детали треков, включенных в плейлисты, созданные пользователем ‘coder_chris’. Покажите название плейлиста, название трека, название альбома и имя исполнителя.
Вариант 9: База данных службы объявлений о недвижимости
Сценарий: Вы управляете базой данных службы объявлений о продаже недвижимости. Система отслеживает объекты на продажу, агентов по недвижимости, клиентов (потенциальных покупателей), типы объектов, просмотры объектов клиентами и предложения, сделанные клиентами на объекты.
Схема:
-- Удаляем таблицы, если они уже существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS offers;
DROP TABLE IF EXISTS viewings;
DROP TABLE IF EXISTS properties;
DROP TABLE IF EXISTS property_types;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS agents;
-- Создание таблицы агентов
CREATE TABLE agents (
agent_id SERIAL PRIMARY KEY,
agent_name VARCHAR(100) NOT NULL,
agency_name VARCHAR(100),
phone VARCHAR(20) UNIQUE
);
-- Создание таблицы клиентов
CREATE TABLE clients (
client_id SERIAL PRIMARY KEY,
client_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
max_budget DECIMAL(12, 2) NULL
);
-- Создание таблицы типов объектов
CREATE TABLE property_types (
type_id SERIAL PRIMARY KEY,
type_name VARCHAR(50) NOT NULL UNIQUE, -- например, 'Apartment', 'House', 'Condo', 'Land'
description TEXT
);
-- Создание таблицы объектов недвижимости
CREATE TABLE properties (
property_id SERIAL PRIMARY KEY,
address VARCHAR(255) NOT NULL,
city VARCHAR(100),
zip_code VARCHAR(10),
listing_price DECIMAL(12, 2) CHECK (listing_price > 0),
bedrooms INT CHECK (bedrooms >= 0),
bathrooms DECIMAL(3, 1) CHECK (bathrooms >= 0), -- например, 2.5 ванных комнаты
square_feet INT CHECK (square_feet > 0),
type_id INT REFERENCES property_types(type_id),
agent_id INT REFERENCES agents(agent_id), -- Агент, разместивший объявление
listing_date DATE DEFAULT CURRENT_DATE,
status VARCHAR(20) CHECK (status IN ('For Sale', 'Pending', 'Sold', 'Withdrawn')) DEFAULT 'For Sale'
);
-- Создание таблицы просмотров (связующая таблица: клиент смотрит объект)
CREATE TABLE viewings (
viewing_id SERIAL PRIMARY KEY,
property_id INT REFERENCES properties(property_id),
client_id INT REFERENCES clients(client_id),
viewing_date TIMESTAMP,
agent_present_id INT REFERENCES agents(agent_id) NULL, -- Агент, присутствовавший при просмотре
feedback TEXT NULL
);
-- Создание таблицы предложений (клиент делает предложение на объект)
CREATE TABLE offers (
offer_id SERIAL PRIMARY KEY,
property_id INT REFERENCES properties(property_id),
client_id INT REFERENCES clients(client_id),
offer_price DECIMAL(12, 2) CHECK (offer_price > 0),
offer_date DATE DEFAULT CURRENT_DATE,
status VARCHAR(20) CHECK (status IN ('Submitted', 'Accepted', 'Rejected', 'Withdrawn')) DEFAULT 'Submitted',
UNIQUE(property_id, client_id, offer_date) -- Предотвращение одинаковых предложений на один и тот же день
);
-- Вставка примеров данных
INSERT INTO agents (agent_name, agency_name, phone) VALUES
('Alice Realty', 'Premier Homes', '555-0101'),
('Bob Estates', 'City Properties', '555-0102'),
('Charlie Sales', 'Premier Homes', '555-0103'),
('Diana Agent', NULL, '555-0104'); -- Независимый агент
INSERT INTO clients (client_name, email, max_budget) VALUES
('John Buyer', 'john.b@mail.net', 500000.00),
('Jane Seeker', 'jane.s@mail.net', 800000.00),
('Peter Looker', 'peter.l@mail.net', 350000.00),
('Mary Interested', 'mary.i@mail.net', NULL); -- Бюджет не указан
INSERT INTO property_types (type_name, description) VALUES
('House', 'Отдельный жилой дом для одной семьи'),
('Apartment', 'Квартира в многоквартирном доме'),
('Condo', 'Индивидуально принадлежащая квартира в жилом комплексе с общими удобствами'),
('Land', 'Неосвоенный земельный участок');
INSERT INTO properties (address, city, zip_code, listing_price, bedrooms, bathrooms, square_feet, type_id, agent_id, listing_date, status) VALUES
('123 Oak St', 'Maplewood', '12345', 450000.00, 3, 2.0, 1800, 1, 1, '2023-08-01', 'For Sale'), -- Дом от Alice
('456 Pine Ave, Unit 10', 'Maplewood', '12345', 320000.00, 2, 1.5, 1100, 3, 2, '2023-08-15', 'Pending'), -- Кондо от Bob
('789 Elm Rd', 'Oakville', '67890', 750000.00, 4, 3.0, 2500, 1, 1, '2023-09-01', 'For Sale'), -- Дом от Alice
('101 Main St, Apt 5B', 'Maplewood', '12346', 280000.00, 1, 1.0, 750, 2, 3, '2023-09-10', 'For Sale'), -- Квартира от Charlie
('Lot 5, River View', 'Oakville', '67891', 150000.00, 0, 0.0, 43560, 4, 2, '2023-09-20', 'For Sale'); -- Земля от Bob
-- Объект без агента (возможно, размещён владельцем напрямую?)
INSERT INTO properties (address, city, zip_code, listing_price, bedrooms, bathrooms, square_feet, type_id, agent_id, listing_date, status) VALUES
('999 Owner Ln', 'Maplewood', '12347', 400000.00, 3, 2.0, 1600, 1, NULL, '2023-10-01', 'For Sale'); -- Объект 6
INSERT INTO viewings (property_id, client_id, viewing_date, agent_present_id, feedback) VALUES
(1, 1, '2023-08-10 14:00:00', 1, 'Хороший двор, кухня нуждается в обновлении.'), -- John смотрит 123 Oak, присутствует Alice
(2, 1, '2023-08-20 11:00:00', 2, 'Хорошее расположение, немного тесно.'), -- John смотрит 456 Pine, присутствует Bob
(1, 2, '2023-08-12 16:00:00', 1, 'Очень понравилась планировка!'), -- Jane смотрит 123 Oak, присутствует Alice
(3, 2, '2023-09-05 10:00:00', 1, 'Просторно, большой потенциал.'), -- Jane смотрит 789 Elm, присутствует Alice
(4, 3, '2023-09-15 15:00:00', 3, NULL); -- Peter смотрит 101 Main, присутствует Charlie
-- Примечание: объекты 5 (земля) и 6 (Owner Ln) пока не просмотрены.
-- Примечание: клиент Mary Interested пока ничего не смотрела.
INSERT INTO offers (property_id, client_id, offer_price, offer_date, status) VALUES
(1, 2, 445000.00, '2023-08-15', 'Submitted'), -- Jane делает предложение на 123 Oak
(2, 1, 315000.00, '2023-08-22', 'Accepted'), -- John делает предложение на 456 Pine (что приводит к статусу Pending)
(1, 1, 450000.00, '2023-08-16', 'Rejected'), -- John делает предложение на 123 Oak (отклонено)
(3, 2, 740000.00, '2023-09-10', 'Submitted'); -- Jane делает предложение на 789 Elm
-- Примечание: на объекты 4, 5, 6 пока предложений нет.
-- Примечание: клиенты Peter Looker и Mary Interested не делали предложений.
Задания для варианта 9:
- Выведите все объекты, которые в данный момент имеют статус ‘For Sale’, показав адрес, город, цену, имя агента и название типа объекта.
- Покажите имена клиентов, которые просматривали объекты, выставленные агентством ‘Alice Realty’. Укажите имя клиента и адрес объекта. Исключите дублирование имён клиентов.
- Выведите всех агентов и количество объектов, которые они сейчас разместили (независимо от статуса). Включите агентов, у которых может быть ноль объектов. Отсортируйте по убыванию количества объектов.
- Покажите данные всех предложений со статусом ‘Submitted’. Укажите адрес объекта, имя клиента, цену предложения и дату.
- Найдите все объекты (адрес, город) типа ‘House’, цена которых ниже $500,000.
- Выведите всех клиентов и общее количество просмотров, в которых они участвовали. Включите клиентов, у которых не было ни одного просмотра. Отсортируйте по количеству просмотров по убыванию.
- Покажите все объекты и имя агента, разместившего объявление, а также агентство. Включите объекты, у которых агент не указан (NULL).
- Найдите агентов из агентства ‘Premier Homes’ и выведите адреса объектов, которые они в данный момент продают (статус ‘For Sale’).
- Покажите типы объектов, для которых в базе данных указано более одного объекта (независимо от статуса). Укажите название типа и количество объектов.
- Выведите историю просмотров для объекта по адресу ‘123 Oak St’. Укажите имя клиента, дату просмотра и оставленный отзыв. Отсортируйте по дате просмотра.
Вариант 10: Система заказов в ресторане
Сценарий: Вы управляете базой данных ресторана. Система отслеживает клиентов, позиции меню, категории позиций (например, Закуски, Основные блюда, Десерты, Напитки), ингредиенты, заказы, размещенные клиентами, и конкретные позиции, включенные в каждый заказ.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_item_ingredients;
DROP TABLE IF EXISTS ingredients;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;
-- Создаем таблицу Categories (с самоссылкой для подкатегорий)
CREATE TABLE categories (
category_id SERIAL PRIMARY KEY,
category_name VARCHAR(100) NOT NULL UNIQUE,
parent_category_id INT REFERENCES categories(category_id) NULL -- например, 'Горячие закуски' в категории 'Закуски'
);
-- Создаем таблицу MenuItems
CREATE TABLE menu_items (
item_id SERIAL PRIMARY KEY,
item_name VARCHAR(150) NOT NULL,
description TEXT,
price DECIMAL(8, 2) CHECK (price >= 0),
category_id INT REFERENCES categories(category_id),
is_vegetarian BOOLEAN DEFAULT FALSE
);
-- Создаем таблицу Ingredients
CREATE TABLE ingredients (
ingredient_id SERIAL PRIMARY KEY,
ingredient_name VARCHAR(100) NOT NULL UNIQUE,
is_allergen BOOLEAN DEFAULT FALSE
);
-- Создаем таблицу MenuItemIngredients (Связующая таблица для отношения Многие-ко-многим)
CREATE TABLE menu_item_ingredients (
item_id INT REFERENCES menu_items(item_id),
ingredient_id INT REFERENCES ingredients(ingredient_id),
PRIMARY KEY (item_id, ingredient_id)
);
-- Создаем таблицу Customers
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
phone_number VARCHAR(20) UNIQUE,
first_visit_date DATE DEFAULT CURRENT_DATE
);
-- Создаем таблицу Orders
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id), -- Может быть NULL для незарегистрированных клиентов/заказов "с улицы"
order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
order_type VARCHAR(10) CHECK (order_type IN ('Dine-in', 'Takeout')),
total_amount DECIMAL(10, 2) -- Вычисляется позже или через триггер/логику приложения
);
-- Создаем таблицу OrderItems (Связующая таблица)
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
item_id INT REFERENCES menu_items(item_id),
quantity INT CHECK (quantity > 0),
price_per_item DECIMAL(8, 2) -- Цена на момент заказа
);
-- Вставляем примерные данные
INSERT INTO categories (category_name, parent_category_id) VALUES
('Закуски', NULL), ('Основные блюда', NULL), ('Десерты', NULL), ('Напитки', NULL),
('Горячие закуски', 1), ('Салаты', 1); -- Подкатегории для 'Закуски'
INSERT INTO menu_items (item_name, description, price, category_id, is_vegetarian) VALUES
('Spring Rolls', 'Хрустящие жареные роллы с овощной начинкой', 6.50, 5, TRUE), -- Горячая закуска
('Caesar Salad', 'Салат Ромэн, гренки, сыр пармезан, соус Цезарь', 8.00, 6, TRUE), -- Салат
('Grilled Salmon', 'Филе лосося на гриле с лимонно-масляным соусом', 18.50, 2, FALSE), -- Основное блюдо
('Spaghetti Carbonara', 'Паста с яйцами, сыром, панчеттой и перцем', 14.00, 2, FALSE), -- Основное блюдо
('Cheesecake', 'Классический чизкейк в нью-йоркском стиле', 7.00, 3, TRUE), -- Десерт
('Mineral Water', 'Бутилированная газированная вода', 2.50, 4, TRUE), -- Напиток
('Chicken Caesar Salad', 'Салат Цезарь с курицей гриль', 11.00, 6, FALSE); -- Салат
INSERT INTO ingredients (ingredient_name, is_allergen) VALUES
('Spring Roll Wrapper', FALSE), ('Cabbage', FALSE), ('Carrot', FALSE), ('Romaine Lettuce', FALSE), ('Croutons', TRUE), -- Глютен
('Parmesan Cheese', TRUE), -- Молочные продукты
('Caesar Dressing', TRUE), -- Яйца, Молочные продукты
('Salmon Fillet', TRUE), -- Рыба
('Lemon', FALSE), ('Butter', TRUE), -- Молочные продукты
('Spaghetti Pasta', TRUE), -- Глютен
('Eggs', TRUE), -- Яйца
('Pancetta', FALSE), ('Black Pepper', FALSE), ('Cream Cheese', TRUE), -- Молочные продукты
('Sugar', FALSE), ('Graham Cracker Crust', TRUE), -- Глютен
('Chicken Breast', FALSE), ('Sparkling Water', FALSE);
INSERT INTO menu_item_ingredients (item_id, ingredient_id) VALUES
(1, 1), (1, 2), (1, 3), -- Спринг-роллы: Обертка, Капуста, Морковь
(2, 4), (2, 5), (2, 6), (2, 7), -- Салат Цезарь: Латук, Сухарики, Пармезан, Соус
(3, 8), (3, 9), (3, 10), -- Лосось на гриле: Лосось, Лимон, Масло
(4, 11), (4, 12), (4, 6), (4, 13), (4, 14), -- Карбонара: Паста, Яйца, Пармезан, Панчетта, Перец
(5, 15), (5, 16), (5, 17), -- Чизкейк: Сливочный сыр, Сахар, Корж
(7, 4), (7, 5), (7, 6), (7, 7), (7, 18); -- Цезарь с курицей: Латук, Сухарики, Пармезан, Соус, Курица
INSERT INTO customers (customer_name, phone_number, first_visit_date) VALUES
('Alice Green', '555-1111', '2023-02-15'),
('Bob White', '555-2222', '2023-03-20'),
('Charlie Black', NULL, '2023-09-01'); -- Номер телефона необязателен
INSERT INTO orders (customer_id, order_time, order_type) VALUES
(1, '2023-10-20 19:30:00', 'Dine-in'),
(2, '2023-10-21 12:15:00', 'Takeout'),
(1, '2023-10-22 20:00:00', 'Dine-in'),
(NULL, '2023-10-22 13:00:00', 'Takeout'); -- Незарегистрированный клиент / Клиент "с улицы"
INSERT INTO order_items (order_id, item_id, quantity, price_per_item) VALUES
(1, 1, 1, 6.50), (1, 3, 1, 18.50), (1, 6, 2, 2.50), -- Алиса: Спринг-роллы, Лосось, 2 Воды
(2, 4, 1, 14.00), (2, 6, 1, 2.50), -- Боб: Карбонара, Вода
(3, 7, 1, 11.00), (3, 5, 1, 7.00), -- Алиса: Цезарь с курицей, Чизкейк
(4, 1, 2, 6.50); -- "С улицы": 2 Спринг-ролла
-- Примечание: Спагетти Карбонара (Позиция 4) было заказано, Салат Цезарь (Позиция 2) - нет.
Задания для Варианта 10:
- Вывести все позиции меню, их цены и названия их категорий. Включить позиции, которые могут не принадлежать ни к какой категории (если это возможно). Отсортировать по названию категории, затем по названию позиции.
- Показать все категории и названия их родительских категорий. Для категорий верхнего уровня имя родительской категории должно быть NULL. (Требуется самосоединение (self-join) таблицы
categories). - Отобразить имена клиентов, которые сделали заказы типа ‘Dine-in’. Вывести имя клиента, ID заказа и время заказа.
- Вывести все вегетарианские позиции меню (название и цену) (
is_vegetarian = TRUE). - Найти все позиции меню (вывести их названия), которые содержат ингредиент ‘Parmesan Cheese’. (Требуется соединение таблиц
menu_items,menu_item_ingredientsиingredients). - Показать названия позиций меню, которые принадлежат категории ‘Закуски’ или любой из её подкатегорий (например, ‘Горячие закуски’). (Требуется соединение таблицы
categoriesс самой собой, а затем с таблицейmenu_items). - Вывести имена клиентов, которые заказали ‘Grilled Salmon’.
- Рассчитать общее количество каждого проданного элемента меню по всем заказам. Вывести название позиции и общее количество. Включить позиции, которые никогда не заказывались (показать количество 0). Отсортировать по названию позиции.
- Вывести категории, содержащие позиции меню, средняя цена которых в этой категории превышает $10. Показать название категории и среднюю цену.
- Отобразить детали всех позиций для
Order ID = 1: включить ID заказа, имя клиента, название позиции, заказанное количество и цену за единицу на момент заказа.
Вариант 11: Система управления блогами/контентом
Сценарий: Вы управляете базой данных для платформы блогов. Система хранит информацию о пользователях (авторах), постах блога, категориях постов, тегах для детализированных тем, комментариях к постам и связях между ними (например, какие теги применяются к каким постам).
Схема:
-- Удаление таблиц, если они существуют (для чистого старта)
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS post_tags;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS tags;
-- Создание таблицы Users (Авторы)
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Создание таблицы Categories
CREATE TABLE categories (
category_id SERIAL PRIMARY KEY,
category_name VARCHAR(100) NOT NULL UNIQUE,
description TEXT
);
-- Создание таблицы Posts
CREATE TABLE posts (
post_id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT,
author_id INT REFERENCES users(user_id),
category_id INT REFERENCES categories(category_id),
publish_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(15) CHECK (status IN ('Draft', 'Published', 'Archived')) DEFAULT 'Draft'
);
-- Создание таблицы Tags
CREATE TABLE tags (
tag_id SERIAL PRIMARY KEY,
tag_name VARCHAR(50) NOT NULL UNIQUE
);
-- Создание таблицы PostTags (Связующая таблица для отношения Многие-ко-Многим между Posts и Tags)
CREATE TABLE post_tags (
post_id INT REFERENCES posts(post_id),
tag_id INT REFERENCES tags(tag_id),
PRIMARY KEY (post_id, tag_id)
);
-- Создание таблицы Comments (с самоссылкой для ответов)
CREATE TABLE comments (
comment_id SERIAL PRIMARY KEY,
post_id INT REFERENCES posts(post_id),
author_id INT REFERENCES users(user_id), -- Автор комментария (может быть NULL для анонимных)
comment_text TEXT NOT NULL,
comment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
reply_to_comment_id INT REFERENCES comments(comment_id) NULL -- Для цепочек комментариев
);
-- Вставка тестовых данных
INSERT INTO users (username, email, registration_date) VALUES
('john_doe', 'john@example.com', '2023-01-10 10:00:00'),
('jane_smith', 'jane@example.com', '2023-02-15 11:00:00'),
('guest_commenter', 'guest@anon.com', '2023-03-20 12:00:00'); -- Пользователь для комментирования
INSERT INTO categories (category_name, description) VALUES
('Технологии', 'Статьи о гаджетах, программном обеспечении и интернете.'),
('Путешествия', 'Истории и советы о путешествиях по миру.'),
('Кулинария', 'Рецепты и кулинарные приключения.');
INSERT INTO posts (title, content, author_id, category_id, publish_date, status) VALUES
('Введение в SQL JOIN', 'Изучение INNER JOIN, LEFT JOIN...', 1, 1, '2023-10-01 09:00:00', 'Published'),
('Мое путешествие по Италии', 'Посещение Рима, Флоренции и Венеции...', 2, 2, '2023-10-05 14:00:00', 'Published'),
('Как приготовить идеальную пасту', 'Пошаговое руководство по приготовлению домашней пасты.', 2, 3, '2023-10-10 11:00:00', 'Published'),
('Продвинутые техники Git', 'За пределами commit, push, pull...', 1, 1, '2023-10-15 16:00:00', 'Draft'),
('Скрытые жемчужины Токио', 'Изучение менее известных мест.', 2, 2, NULL, 'Draft'); -- Еще не опубликовано
INSERT INTO tags (tag_name) VALUES
('SQL'), ('База данных'), ('Советы путешественникам'), ('Европа'), ('Италия'), ('Еда'), ('Рецепт'), ('Паста'), ('Git'), ('Контроль версий'), ('Япония');
INSERT INTO post_tags (post_id, tag_id) VALUES
(1, 1), (1, 2), -- Пост 1: SQL, База данных
(2, 3), (2, 4), (2, 5), -- Пост 2: Советы путешественникам, Европа, Италия
(3, 6), (3, 7), (3, 8), -- Пост 3: Еда, Рецепт, Паста
(4, 9), (4, 10); -- Пост 4: Git, Контроль версий
-- Пост 5 пока без тегов
INSERT INTO comments (post_id, author_id, comment_text, comment_date, reply_to_comment_id) VALUES
(1, 2, 'Отличное объяснение JOIN!', '2023-10-02 10:00:00', NULL), -- Комментарий 1
(1, 3, 'Спасибо, это было полезно.', '2023-10-02 11:30:00', NULL), -- Комментарий 2
(2, 1, 'Италия выглядит потрясающе!', '2023-10-06 09:15:00', NULL), -- Комментарий 3
(1, 1, 'Рад, что это оказалось полезным!', '2023-10-03 08:00:00', 1); -- Комментарий 4 (ответ на Комментарий 1)
-- Примечание: Посты 3, 4, 5 пока без комментариев.
Задания для варианта 11:
- Выведите все опубликованные посты (
status = 'Published') вместе с именем пользователя автора и названием категории поста. Отсортируйте по дате публикации в порядке убывания. - Покажите всех пользователей и заголовки постов, которые они написали. Включите пользователей, которые еще не написали ни одного поста. Отсортируйте по имени пользователя.
- Для каждого комментария выведите его текст, имя пользователя автора комментария и текст комментария, на который он отвечает (если есть). Если это не ответ, укажите NULL для текста ответа. (Требуется самосоединение таблицы
comments). - Выведите заголовки всех постов, у которых есть тег ‘SQL’. (Требуется соединение таблиц
posts,post_tagsиtags). - Найдите все комментарии, написанные пользователем ‘jane_smith’. Выведите текст комментария и заголовок поста, к которому относится комментарий.
- Выведите заголовки всех постов, относящихся к категории ‘Технологии’. Также включите теги, связанные с каждым из этих постов (выведите все теги для каждого поста).
- Покажите заголовки постов, которые получили комментарии от ‘guest_commenter’. Выведите только уникальные заголовки постов.
- Подсчитайте количество постов, написанных каждым автором (пользователем). Выведите имя пользователя и количество постов. Включите пользователей, которые не написали ни одного поста. Отсортируйте по количеству постов в порядке убывания.
- Выведите категории, в которых опубликовано более 1 поста. Покажите название категории и количество опубликованных постов.
- Выведите полные данные о комментариях к посту с заголовком ‘Введение в SQL JOIN’. Включите текст комментария, имя пользователя автора комментария, дату комментария и, если это ответ, укажите ID комментария, на который он отвечает.
Вариант 12: Система учета абонементов фитнес-центра
Сценарий: Вы управляете базой данных фитнес-центра. Система отслеживает членов клуба, типы их абонементов, предлагаемые фитнес-занятия, инструкторов, ведущих занятия, расписание занятий (какое занятие/инструктор/время/место) и записи посещаемости членов клуба.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS attendance;
DROP TABLE IF EXISTS schedule;
DROP TABLE IF EXISTS instructors;
DROP TABLE IF EXISTS fitness_classes;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS membership_types;
-- Создаем таблицу MembershipTypes (Типы абонементов)
CREATE TABLE membership_types (
type_id SERIAL PRIMARY KEY,
type_name VARCHAR(100) NOT NULL UNIQUE,
monthly_fee DECIMAL(8, 2) CHECK (monthly_fee >= 0),
access_level VARCHAR(50) -- например, 'Полный', 'Внепиковый', 'Только занятия'
);
-- Создаем таблицу Members (Члены клуба)
CREATE TABLE members (
member_id SERIAL PRIMARY KEY,
member_name VARCHAR(100) NOT NULL,
join_date DATE DEFAULT CURRENT_DATE,
email VARCHAR(100) UNIQUE,
membership_type_id INT REFERENCES membership_types(type_id)
);
-- Создаем таблицу Instructors (Инструкторы)
CREATE TABLE instructors (
instructor_id SERIAL PRIMARY KEY,
instructor_name VARCHAR(100) NOT NULL,
specialty VARCHAR(100), -- например, 'Йога', 'Сайкл', 'Силовая тренировка'
hire_date DATE
);
-- Создаем таблицу FitnessClasses (Фитнес-занятия) (с самоссылкой для предварительных условий)
CREATE TABLE fitness_classes (
class_id SERIAL PRIMARY KEY,
class_name VARCHAR(100) NOT NULL UNIQUE,
description TEXT,
duration_minutes INT CHECK (duration_minutes > 0),
prerequisite_class_id INT REFERENCES fitness_classes(class_id) NULL -- например, 'Йога для продвинутых' требует 'Йога для начинающих'
);
-- Создаем таблицу Schedule (Расписание) (связывает Занятие, Инструктора, Время/Место)
CREATE TABLE schedule (
schedule_id SERIAL PRIMARY KEY,
class_id INT REFERENCES fitness_classes(class_id),
instructor_id INT REFERENCES instructors(instructor_id),
class_datetime TIMESTAMP NOT NULL, -- Конкретная дата и время
location VARCHAR(50), -- например, 'Студия А', 'Сайкл-зал'
max_capacity INT CHECK (max_capacity > 0)
);
-- Создаем таблицу Attendance (Посещаемость) (Соединительная таблица)
CREATE TABLE attendance (
attendance_id SERIAL PRIMARY KEY,
member_id INT REFERENCES members(member_id),
schedule_id INT REFERENCES schedule(schedule_id),
check_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE (member_id, schedule_id) -- Член клуба может посетить конкретное запланированное занятие только один раз
);
-- Вставляем примерные данные
INSERT INTO membership_types (type_name, monthly_fee, access_level) VALUES
('Gold Full Access', 75.00, 'Full'),
('Silver Off-Peak', 50.00, 'Off-Peak'),
('Bronze Classes Only', 40.00, 'Classes Only');
INSERT INTO members (member_name, join_date, email, membership_type_id) VALUES
('Alice Young', '2023-01-15', 'alice.y@mail.com', 1),
('Bob Oldman', '2022-11-01', 'bob.o@mail.com', 2),
('Charlie Fast', '2023-03-10', 'charlie.f@mail.com', 1),
('Diana Strong', '2023-05-05', 'diana.s@mail.com', 3),
('Ethan Flexible', '2023-08-20', 'ethan.f@mail.com', 1); -- Новый член клуба, посещений пока нет
INSERT INTO instructors (instructor_name, specialty, hire_date) VALUES
('Instructor Ann', 'Yoga', '2022-05-01'),
('Instructor Ben', 'Spin', '2022-08-15'),
('Instructor Chloe', 'Strength Training', '2023-02-01'),
('Instructor Dave', 'Yoga', '2023-06-01'); -- Еще один инструктор по йоге
INSERT INTO fitness_classes (class_name, description, duration_minutes, prerequisite_class_id) VALUES
('Beginner Yoga', 'Introduction to basic yoga poses and breathing.', 60, NULL), -- Занятие 1
('Spin Express', 'High-intensity interval cycling class.', 45, NULL), -- Занятие 2
('Total Body Strength', 'Full body workout using weights and bodyweight.', 60, NULL), -- Занятие 3
('Advanced Yoga', 'Challenging poses and flow sequences.', 75, 1); -- Занятие 4 (Требует Занятие 1)
INSERT INTO schedule (class_id, instructor_id, class_datetime, location, max_capacity) VALUES
(1, 1, '2023-10-23 09:00:00', 'Studio A', 20), -- Йога для начинающих с Ann
(2, 2, '2023-10-23 17:30:00', 'Spin Room', 15), -- Сайкл Экспресс с Ben
(3, 3, '2023-10-24 18:00:00', 'Weight Room', 12), -- Силовая с Chloe
(1, 4, '2023-10-25 10:00:00', 'Studio A', 20), -- Йога для начинающих с Dave
(4, 1, '2023-10-26 09:00:00', 'Studio A', 15); -- Йога для продвинутых с Ann
INSERT INTO attendance (member_id, schedule_id, check_in_time) VALUES
(1, 1, '2023-10-23 08:55:00'), -- Alice посещает Йогу для начинающих (Ann)
(2, 1, '2023-10-23 08:58:00'), -- Bob посещает Йогу для начинающих (Ann)
(3, 2, '2023-10-23 17:25:00'), -- Charlie посещает Сайкл (Ben)
(1, 2, '2023-10-23 17:28:00'), -- Alice посещает Сайкл (Ben)
(4, 3, '2023-10-24 17:55:00'); -- Diana посещает Силовую (Chloe)
-- Примечание: Ethan еще не посещал занятия. У записей расписания с ID 4 (Йога для начинающих с Dave) и 5 (Йога для продвинутых с Ann) пока нет посетителей.
Задания для Варианта 12:
- Вывести список всех членов клуба и название их типа абонемента. Включить членов клуба, которым тип абонемента может быть не назначен (если это возможно). Отсортировать по имени члена клуба.
- Показать все запланированные занятия: включить название занятия, имя инструктора, запланированные дату/время и место проведения. Отсортировать по дате/времени.
- Для каждого фитнес-занятия, у которого есть предварительное требование (prerequisite), вывести название занятия и название требуемого занятия. (Требуется самосоединение (self-join) таблицы
fitness_classes). - Отобразить имена членов клуба, посетивших хотя бы одно занятие. Вывести имя члена клуба, название посещенного занятия и дату/время занятия. Отсортировать по имени члена клуба, затем по дате/времени занятия.
- Найти все занятия, проводимые инструктором ‘Instructor Ann’. Вывести название занятия, запланированные дату/время и место проведения.
- Вывести имена всех членов клуба с типом абонемента ‘Gold Full Access’ вместе с названиями занятий, которые они посетили. Если член клуба с Gold-абонементом не посетил ни одного занятия, он все равно должен присутствовать в списке (с NULL вместо названия занятия).
- Показать названия фитнес-занятий, запланированных к проведению в ‘Studio A’. Вывести только уникальные названия занятий.
- Подсчитать количество занятий, посещенных каждым членом клуба. Вывести имя члена клуба и количество посещений. Включить членов клуба, не посетивших ни одного занятия (с 0 посещений). Отсортировать по количеству посещений по убыванию.
- Вывести список инструкторов, которые должны провести более одного занятия согласно текущему расписанию. Показать имя инструктора и количество запланированных занятий.
- Отобразить полную информацию о посещаемости для занятия ‘Spin Express’, запланированного на ‘2023-10-23 17:30:00’ (Schedule ID = 2). Включить имя члена клуба, время отметки о посещении (check-in time), название занятия и имя инструктора.
Вариант 13: Система управления конференциями
Сценарий: Вы управляете базой данных компании, которая организует технические конференции. Система отслеживает конференции, места их проведения, отдельные сессии в рамках каждой конференции, спикеров, выступающих на сессиях, и участников, которые регистрируются и посещают сессии.
Схема:
-- Удаление таблиц, если они существуют (для начала с чистого листа)
DROP TABLE IF EXISTS attendee_sessions;
DROP TABLE IF EXISTS attendees;
DROP TABLE IF EXISTS session_speakers;
DROP TABLE IF EXISTS speakers;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS conferences;
DROP TABLE IF EXISTS venues;
-- Создание таблицы мест проведения
CREATE TABLE venues (
venue_id SERIAL PRIMARY KEY,
venue_name VARCHAR(150) NOT NULL UNIQUE,
address VARCHAR(255),
capacity INT CHECK (capacity > 0)
);
-- Создание таблицы конференций
CREATE TABLE conferences (
conf_id SERIAL PRIMARY KEY,
conf_name VARCHAR(200) NOT NULL,
start_date DATE,
end_date DATE,
venue_id INT REFERENCES venues(venue_id)
);
-- Создание таблицы сессий (с самоссылкой для предварительных условий)
CREATE TABLE sessions (
session_id SERIAL PRIMARY KEY,
session_title VARCHAR(200) NOT NULL,
session_time TIMESTAMP,
room VARCHAR(50),
conf_id INT REFERENCES conferences(conf_id) NOT NULL,
prerequisite_session_id INT REFERENCES sessions(session_id) NULL -- Для сессий, которые основаны на других
);
-- Создание таблицы спикеров
CREATE TABLE speakers (
speaker_id SERIAL PRIMARY KEY,
speaker_name VARCHAR(100) NOT NULL,
affiliation VARCHAR(150), -- Компания или университет
bio TEXT
);
-- Создание промежуточной таблицы SessionSpeakers (Многие-ко-многим: Сессии <-> Спикеры)
CREATE TABLE session_speakers (
session_id INT REFERENCES sessions(session_id),
speaker_id INT REFERENCES speakers(speaker_id),
PRIMARY KEY (session_id, speaker_id) -- Составной ключ
);
-- Создание таблицы участников
CREATE TABLE attendees (
attendee_id SERIAL PRIMARY KEY,
attendee_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
registration_date DATE DEFAULT CURRENT_DATE
);
-- Создание промежуточной таблицы AttendeeSessions (Многие-ко-многим: Участники <-> Сессии)
CREATE TABLE attendee_sessions (
attendance_id SERIAL PRIMARY KEY,
attendee_id INT REFERENCES attendees(attendee_id),
session_id INT REFERENCES sessions(session_id),
check_in_time TIMESTAMP NULL, -- Отслеживание фактического посещения
UNIQUE (attendee_id, session_id) -- Участник посещает конкретную сессию один раз
);
-- Вставка тестовых данных
INSERT INTO venues (venue_name, address, capacity) VALUES
('Metro Convention Center', '123 Main St', 5000),
('Tech Park Auditorium', '456 Tech Ave', 800),
('University Hall', '789 Campus Dr', 1200);
INSERT INTO conferences (conf_name, start_date, end_date, venue_id) VALUES
('InnovateSphere 2024', '2024-10-15', '2024-10-17', 1),
('DataCon West', '2024-11-05', '2024-11-06', 2),
('FutureTech Summit', '2025-03-10', '2025-03-12', NULL); -- Место проведения пока не определено
INSERT INTO sessions (session_title, session_time, room, conf_id, prerequisite_session_id) VALUES
('Ключевая сессия: Будущее ИИ', '2024-10-15 09:00:00', 'Main Hall', 1, NULL), -- Сессия 1
('Мастер-класс: Продвинутые техники SQL', '2024-10-15 11:00:00', 'Room 101', 1, NULL), -- Сессия 2
('Панель: Архитектуры облачных приложений', '2024-10-15 14:00:00', 'Room 102', 1, NULL), -- Сессия 3
('Глубокое погружение в машинное обучение', '2024-10-16 10:00:00', 'Room 101', 1, 1), -- Сессия 4 (Предварительное условие: Ключевая сессия)
('Овладение конвейерами больших данных', '2024-11-05 10:30:00', 'Auditorium A', 2, NULL), -- Сессия 5
('Защита вашего озера данных', '2024-11-05 14:00:00', 'Auditorium B', 2, 5), -- Сессия 6 (Предварительное условие: Большие данные)
('Этические аспекты ИИ', '2024-11-06 09:00:00', 'Auditorium A', 2, NULL); -- Сессия 7
INSERT INTO speakers (speaker_name, affiliation, bio) VALUES
('Др. Эвелин Рид', 'SynthAI Corp', 'Пионер в исследованиях ИИ.'),
('Марк Джонсон', 'DataWorks Inc.', 'Эксперт по оптимизации баз данных.'),
('Сара Чен', 'CloudNine Solutions', 'Специалист по облачной инфраструктуре.'),
('Дэвид Ли', 'CloudNine Solutions', 'Архитектор безопасности.'), -- Еще один спикер из CloudNine
('Проф. Алан Грант', 'State University', 'Ведущий исследователь в области машинного обучения.'),
('Джейн Матрикса', 'BigData Systems', 'Специалист по инженерии конвейеров данных.');
INSERT INTO session_speakers (session_id, speaker_id) VALUES
(1, 1), -- Ключевая сессия: Рид
(2, 2), -- Мастер-класс по SQL: Джонсон
(3, 3), -- Панель по облаку: Чен
(3, 4), -- Панель по облаку: Ли (Несколько спикеров для сессии 3)
(4, 5), -- Глубокое погружение в машинное обучение: Грант
(5, 6), -- Конвейеры больших данных: Матрикса
(6, 6), -- Защита озера данных: Матрикса (Тот же спикер, другая сессия)
(7, 1); -- Этические аспекты ИИ: Рид
INSERT INTO attendees (attendee_name, email, registration_date) VALUES
('Алиса Кодер', 'alice@coder.com', '2024-08-01'),
('Боб Аналитик', 'bob@analyst.net', '2024-08-15'),
('Чарли Инженер', 'charlie@engineer.org', '2024-09-01'),
('Диана Менеджер', 'diana@manager.co', '2024-09-10'); -- Зарегистрирована, но, возможно, еще не посещает сессии
INSERT INTO attendee_sessions (attendee_id, session_id, check_in_time) VALUES
(1, 1, '2024-10-15 08:55:00'), -- Алиса, Ключевая сессия
(1, 2, '2024-10-15 10:58:00'), -- Алиса, Мастер-класс по SQL
(1, 4, '2024-10-16 09:59:00'), -- Алиса, Глубокое погружение в машинное обучение
(2, 5, '2024-11-05 10:25:00'), -- Боб, Конвейеры больших данных
(2, 6, '2024-11-05 13:55:00'), -- Боб, Защита озера данных
(3, 1, '2024-10-15 08:50:00'), -- Чарли, Ключевая сессия
(3, 3, '2024-10-15 13:58:00'); -- Чарли, Панель по облаку
-- Примечание: Диана еще не посетила ни одной сессии.
-- Примечание: У сессии 7 (Этические аспекты ИИ) пока нет зарегистрированных участников.
Задания для варианта 13:
- Выведите список всех сессий с указанием названия конференции, к которой они относятся, и названия места проведения конференции. Включите конференции, у которых место проведения еще не назначено.
- Покажите имена всех спикеров и названия сессий, которые они проводят. Включите спикеров, которые еще не назначены на сессии. Сортируйте по имени спикера, затем по названию сессии.
- Для каждой сессии, у которой есть предварительное условие, выведите название сессии и название сессии-предварительного условия. (Требуется самосоединение таблицы
sessions). - Выведите имена участников, которые зарегистрировались хотя бы на одну сессию. Укажите имя участника, название сессии и время регистрации. Сортируйте по имени участника, затем по времени регистрации.
- Найдите все сессии, проводимые спикерами, связанными с ‘CloudNine Solutions’. Выведите название сессии, имя спикера и название конференции.
- Выведите имена всех зарегистрированных участников и названия конференций для сессий, которые они посетили. Если участник не посещал ни одной сессии, он все равно должен быть в списке (с NULL для названия конференции). Составьте уникальный список пар участник-конференция.
- Покажите названия сессий, у которых назначено более одного спикера. Выведите только названия сессий.
- Подсчитайте количество сессий, которые проводит каждый спикер. Выведите имя спикера и количество сессий. Включите спикеров с нулевым количеством сессий. Сортируйте по количеству сессий по убыванию.
- Выведите конференции, у которых запланировано более 3 сессий. Покажите название конференции и количество сессий.
- Выведите полные данные об участниках, посетивших сессию под названием ‘Ключевая сессия: Будущее ИИ’. Включите имя участника, электронную почту, дату регистрации и время регистрации на эту конкретную сессию.
Вариант 14: Система отслеживания ошибок (Bug Tracking System)
Сценарий: Вы разрабатываете базу данных для компании-разработчика программного обеспечения для отслеживания задач (ошибок, запросов на новые функции) в различных проектах. Система должна записывать, кто сообщил о задаче, кому она назначена для исправления, текущий статус, а также потенциально связывать связанные задачи.
Схема:
-- Удаляем таблицы, если они существуют (чтобы начать с чистого листа)
DROP TABLE IF EXISTS issues;
DROP TABLE IF EXISTS statuses;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS projects;
-- Создаем таблицу Projects
CREATE TABLE projects (
project_id SERIAL PRIMARY KEY,
project_name VARCHAR(100) NOT NULL UNIQUE,
description TEXT,
start_date DATE
);
-- Создаем таблицу Users
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
user_name VARCHAR(100) NOT NULL UNIQUE,
email VARCHAR(100) UNIQUE,
role VARCHAR(50) CHECK (role IN ('Developer', 'QA', 'Manager', 'Reporter'))
);
-- Создаем таблицу Statuses
CREATE TABLE statuses (
status_id SERIAL PRIMARY KEY,
status_name VARCHAR(50) NOT NULL UNIQUE -- например, Open, In Progress, Resolved, Closed, Reopened
);
-- Создаем таблицу Issues (с самоссылкой для связанных задач)
CREATE TABLE issues (
issue_id SERIAL PRIMARY KEY,
summary VARCHAR(255) NOT NULL,
description TEXT,
project_id INT REFERENCES projects(project_id) NOT NULL,
reporter_id INT REFERENCES users(user_id) NOT NULL,
assignee_id INT REFERENCES users(user_id) NULL, -- Может быть не назначена
status_id INT REFERENCES statuses(status_id) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
priority INT CHECK (priority BETWEEN 1 AND 5), -- 1=Наивысший, 5=Низший
related_issue_id INT REFERENCES issues(issue_id) NULL -- Ссылка на связанную/дублирующую задачу
);
-- Вставляем пример данных
INSERT INTO projects (project_name, description, start_date) VALUES
('Phoenix Framework', 'Next-gen web framework', '2023-01-10'),
('Quantum DB', 'High-performance database engine', '2022-05-20'),
('Orion Analytics', 'Data visualization platform', '2023-08-01');
INSERT INTO users (user_name, email, role) VALUES
('alice_dev', 'alice@dev.com', 'Developer'),
('bob_qa', 'bob@qa.com', 'QA'),
('charlie_mgr', 'charlie@mgr.com', 'Manager'),
('david_dev', 'david@dev.com', 'Developer'),
('eve_reporter', 'eve@reporter.com', 'Reporter'); -- Пользователь, который только сообщает об ошибках
INSERT INTO statuses (status_name) VALUES
('Open'), ('In Progress'), ('Resolved'), ('Closed'), ('Reopened');
INSERT INTO issues (summary, description, project_id, reporter_id, assignee_id, status_id, priority, related_issue_id) VALUES
('UI button misaligned on login screen', 'The login button is 5px too low.', 1, 2, 1, 2, 2, NULL), -- Задача 1, Проект Phoenix, Автор Bob, Назначен Alice, Статус In Progress
('Database connection pool exhausted', 'Under heavy load, connections run out.', 2, 1, 4, 1, 1, NULL), -- Задача 2, Проект Quantum, Автор Alice, Назначен David, Статус Open
('Add export-to-CSV feature', 'Users need to export analytics data.', 3, 3, NULL, 1, 3, NULL), -- Задача 3, Проект Orion, Автор Charlie, Не назначен, Статус Open
('Login fails with special characters in password', 'Passwords with & or % cause errors.', 1, 2, 1, 4, 2, NULL), -- Задача 4, Проект Phoenix, Автор Bob, Назначен Alice, Статус Closed
('Performance degradation on large datasets', 'Queries slow down significantly with >1M rows.', 2, 4, 4, 2, 1, NULL), -- Задача 5, Проект Quantum, Автор David, Назначен David, Статус In Progress
('Documentation unclear for API endpoint X', 'Need better examples for /api/users.', 1, 5, NULL, 1, 4, NULL), -- Задача 6, Проект Phoenix, Автор Eve, Не назначен, Статус Open
('Typo in welcome email', 'Minor typo in the onboarding email template.', 1, 2, 1, 3, 5, NULL), -- Задача 7, Проект Phoenix, Автор Bob, Назначен Alice, Статус Resolved
('Login button issue (duplicate?)', 'Login button positioning seems off.', 1, 5, 1, 1, 2, 1); -- Задача 8, Проект Phoenix, Автор Eve, Назначен Alice, Статус Open, Связана с Задачей 1
-- Симуляция триггера для updated_at (в реальном сценарии это обрабатывалось бы триггером)
UPDATE issues SET updated_at = NOW() WHERE issue_id IN (1, 5);
Задания для Варианта 14:
- Вывести список всех задач с их кратким описанием (summary), названием проекта, к которому они относятся, и названием их текущего статуса.
- Показать имена всех пользователей и краткие описания задач, о которых они сообщили. Включить пользователей, которые еще не сообщали ни о каких задачах. Отсортировать по имени пользователя, затем по краткому описанию задачи.
- Для каждой задачи, у которой есть
related_issue_id, вывести ее краткое описание и краткое описание задачи, с которой она связана. (Требуется самосоединение (self-join) таблицыissues). - Отобразить имена пользователей, которым в данный момент назначена хотя бы одна задача. Вывести имя пользователя, краткое описание задачи и название проекта. Отсортировать по имени пользователя.
- Найти все задачи, назначенные в данный момент пользователю ‘alice_dev’. Вывести краткое описание задачи, название проекта и название текущего статуса.
- Вывести имена всех пользователей с ролью ‘Developer’ и краткие описания задач, которые им назначены в данный момент и находятся в статусе ‘In Progress’. Включить разработчиков, которым не назначено ни одной задачи в статусе ‘In Progress’ (они все равно должны появиться, с NULL вместо краткого описания задачи).
- Показать названия проектов, в которых есть хотя бы одна задача со статусом ‘Open’ и приоритетом 1 (Наивысший). Вывести только уникальные названия проектов.
- Подсчитать количество задач, назначенных каждому пользователю (assignee). Вывести имя пользователя и количество назначенных ему задач. Включить пользователей с нулем назначенных задач. Отсортировать по количеству задач по убыванию.
- Вывести список статусов, с которыми в данный момент связано более 2 задач. Показать название статуса и количество задач.
- Отобразить полную информацию (краткое описание, описание, название проекта, имя сообщившего, имя назначенного исполнителя, название статуса, приоритет) для всех задач, связанных с задачей с ID 1 (использовать столбец
related_issue_id).
Вариант 15: Агентство по прокату автомобилей
Сценарий: Вы управляете базой данных для агентства по прокату автомобилей. Система должна отслеживать пункты проката, типы транспортных средств, конкретные автомобили, информацию о клиентах и договоры аренды (какой автомобиль, какой клиент, даты, местоположение).
Схема:
-- Удаление таблиц, если они существуют (для чистого старта)
DROP TABLE IF EXISTS rentals;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS vehicles;
DROP TABLE IF EXISTS vehicle_types;
DROP TABLE IF EXISTS locations;
-- Создание таблицы Locations
CREATE TABLE locations (
location_id SERIAL PRIMARY KEY,
location_name VARCHAR(100) NOT NULL UNIQUE,
address VARCHAR(255),
phone_number VARCHAR(20)
);
-- Создание таблицы VehicleTypes
CREATE TABLE vehicle_types (
type_id SERIAL PRIMARY KEY,
type_name VARCHAR(50) NOT NULL UNIQUE, -- например, Седан, Внедорожник, Грузовик, Минивэн
daily_rate DECIMAL(8, 2) CHECK (daily_rate > 0)
);
-- Создание таблицы Vehicles
CREATE TABLE vehicles (
vehicle_id SERIAL PRIMARY KEY,
license_plate VARCHAR(15) NOT NULL UNIQUE,
make VARCHAR(50),
model VARCHAR(50),
year INT,
color VARCHAR(30),
type_id INT REFERENCES vehicle_types(type_id) NOT NULL,
current_location_id INT REFERENCES locations(location_id) NOT NULL -- Где автомобиль припаркован в данный момент
);
-- Создание таблицы Customers
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(150) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
driver_license_number VARCHAR(50) UNIQUE
);
-- Создание таблицы Rentals
CREATE TABLE rentals (
rental_id SERIAL PRIMARY KEY,
vehicle_id INT REFERENCES vehicles(vehicle_id) NOT NULL,
customer_id INT REFERENCES customers(customer_id) NOT NULL,
pickup_location_id INT REFERENCES locations(location_id) NOT NULL,
return_location_id INT REFERENCES locations(location_id) NULL, -- NULL, если автомобиль ещё не возвращён, иначе место возврата
pickup_date TIMESTAMP NOT NULL,
expected_return_date DATE,
actual_return_date TIMESTAMP NULL, -- NULL, если автомобиль в аренде
total_cost DECIMAL(10, 2) NULL -- Может быть рассчитано при возврате
);
-- Вставка тестовых данных
INSERT INTO locations (location_name, address, phone_number) VALUES
('Центральный пункт', '100 City Ave', '555-1234'),
('Филиал в аэропорту', '200 Airport Rd', '555-5678'),
('Парковка в западном пригороде', '300 Suburb Ln', '555-9012');
INSERT INTO vehicle_types (type_name, daily_rate) VALUES
('Седан', 55.00),
('Внедорожник', 75.50),
('Грузовик', 85.00),
('Минивэн', 95.00);
INSERT INTO vehicles (license_plate, make, model, year, color, type_id, current_location_id) VALUES
('ABC-123', 'Toyota', 'Camry', 2022, 'Серебристый', 1, 1), -- Седан в центре
('XYZ-789', 'Ford', 'Explorer', 2023, 'Чёрный', 2, 1), -- Внедорожник в центре
('JKL-456', 'Honda', 'CRV', 2021, 'Красный', 2, 2), -- Внедорожник в аэропорту
('MNO-321', 'Ford', 'F-150', 2022, 'Белый', 3, 2), -- Грузовик в аэропорту
('PQR-654', 'Toyota', 'Sienna', 2023, 'Синий', 4, 3), -- Минивэн в западном пригороде
('STU-987', 'Nissan', 'Altima', 2020, 'Серый', 1, 1); -- Седан в центре (доступен)
INSERT INTO customers (customer_name, email, phone, driver_license_number) VALUES
('Артур Дент', 'arthur@hitchhiker.com', '555-1111', 'DL112233'),
('Триллиан Астра', 'trillian@heartofgold.com', '555-2222', 'DL445566'),
('Зафод Библброкс', 'zaphod@prez.galaxy', '555-3333', 'DL778899'),
('Марвин Параноид', 'marvin@sirius.cyber', '555-4444', 'DL990011'); -- Новый клиент, без аренд
INSERT INTO rentals (vehicle_id, customer_id, pickup_location_id, return_location_id, pickup_date, expected_return_date, actual_return_date, total_cost) VALUES
(1, 1, 1, 1, '2024-07-01 10:00:00', '2024-07-05', '2024-07-05 09:30:00', 220.00), -- Артур, Camry, прошлая аренда (возвращена в центр)
(3, 2, 2, 2, '2024-07-10 14:00:00', '2024-07-15', '2024-07-16 11:00:00', 453.00), -- Триллиан, CRV, прошлая аренда (возвращена в аэропорт, с опозданием)
(2, 3, 1, NULL, '2024-07-18 09:00:00', '2024-07-22', NULL, NULL), -- Зафод, Explorer, текущая аренда (взята в центре)
(4, 1, 2, NULL, '2024-07-20 11:30:00', '2024-07-25', NULL, NULL), -- Артур, F-150, текущая аренда (взята в аэропорту)
(5, 2, 3, 3, '2024-06-15 12:00:00', '2024-06-20', '2024-06-20 10:00:00', 475.00); -- Триллиан, Sienna, прошлая аренда (возвращена в западный пригород)
-- Обновление текущего местоположения для автомобилей, возвращённых в другие места (или не обновлённых после последней аренды)
-- Предполагается, что автомобиль 1 (ABC-123) возвращён в центр (Location 1) - уже там
-- Предполагается, что автомобиль 3 (JKL-456) возвращён в аэропорт (Location 2) - уже там
-- Предполагается, что автомобиль 5 (PQR-654) возвращён в западный пригород (Location 3) - уже там
-- Примечание: Автомобили 2 (XYZ-789) и 4 (MNO-321) в данный момент в аренде, их `current_location_id` в таблице vehicles может быть устаревшим, пока они не будут возвращены. Для этого упражнения будем считать, что `vehicles.current_location_id` отражает, где автомобиль *должен* быть, если он не в аренде. Для задач используем таблицу rentals для определения текущего статуса.
Задания для варианта 15:
- Выведите список всех автомобилей (марка, модель, год) вместе с названием типа автомобиля и дневной ставкой аренды.
- Покажите имена всех клиентов и номера лицензий автомобилей, которые они арендовали (включая прошлые и текущие аренды). Включите клиентов, которые никогда не арендовали автомобиль. Упорядочите по имени клиента, затем по дате взятия автомобиля.
- Выведите список всех автомобилей (номер лицензии, марка, модель), которые в данный момент находятся в аренде (т.е.
actual_return_dateравно NULL в таблицеrentals). Также покажите имя клиента, который арендовал автомобиль, и дату взятия. - Покажите имена клиентов, которые арендовали автомобиль типа ‘Внедорожник’. Укажите каждое имя клиента только один раз.
- Найдите все аренды, начавшиеся в пункте ‘Филиал в аэропорту’. Выведите ID аренды, имя клиента, номер лицензии автомобиля и дату взятия.
- Выведите названия всех типов автомобилей и марку/модель автомобилей, относящихся к этому типу, которые в данный момент припаркованы в пункте ‘Центральный пункт’. Включите типы автомобилей, для которых в этом пункте нет автомобилей.
- Покажите названия пунктов, где в данный момент припарковано более одного автомобиля (на основе
vehicles.current_location_id). Укажите название пункта и количество автомобилей. - Подсчитайте общее количество аренд, обработанных каждым пунктом взятия автомобиля. Выведите название пункта и количество аренд. Включите пункты, у которых не было аренд. Упорядочите по количеству аренд по убыванию.
- Выведите клиентов, которые арендовали более одного автомобиля (это может быть один и тот же автомобиль несколько раз или разные автомобили). Покажите имя клиента и количество аренд, которые они совершили.
- Покажите подробности для всех автомобилей типа ‘Седан’. Включите номер лицензии, марку, модель, год, цвет, дневную ставку и название пункта, где автомобиль в данный момент указан (из таблицы
vehicles).
Инструкции по сдаче
- Создайте новый Google Doc. Пример
- Добавьте ссылку на ваш Google Doc здесь: Google Таблица