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

Объединение нескольких таблиц с помощью JOIN в PostgreSQL.

Вариант 1: Товары и категории в электронной коммерции

Сценарий: Упрощенная система электронной коммерции должна категоризировать товары.

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

Таблицы: products (товары), categories (категории)

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    category_id INTEGER REFERENCES categories(category_id)
);

Пример данных:

INSERT INTO categories (category_name, description) VALUES
('Electronics', 'Electronic devices and gadgets'),  -- Электроника, Электронные устройства и гаджеты
('Books', 'Literary works and publications'), -- Книги, Литературные произведения и публикации
('Clothing', 'Apparel and fashion items'), -- Одежда, Предметы одежды и модные товары
('Home Goods', 'Items for household use'); -- Товары для дома, Предметы для домашнего использования

INSERT INTO products (product_name, price, category_id) VALUES
('Laptop', 1200.00, 1), -- Ноутбук
('Smartphone', 800.00, 1), -- Смартфон
('T-Shirt', 25.00, 3), -- Футболка
('Novel - Mystery', 15.00, 2), -- Роман - Детектив
('Cookbook', 20.00, 2), -- Кулинарная книга
('Jeans', 60.00, 3), -- Джинсы
('Coffee Maker', 45.00, NULL), -- Кофеварка
('Tablet', 300.00, 1), -- Планшет
('Pillow', 25.00, NULL); -- Подушка

Задания:

Используя INNER JOIN, выведите названия товаров и соответствующие им названия категорий.
Используя LEFT JOIN, покажите все категории и названия товаров в каждой категории.
Используя RIGHT JOIN, выведите все товары и названия их категорий.
Используя FULL OUTER JOIN, объедините категории и товары.
Используя CROSS JOIN, сгенерируйте все комбинации категорий и товаров.
Используя INNER JOIN с предложением WHERE, найдите названия и цены товаров в категории ‘Electronics’ (Электроника).
Используя LEFT JOIN и предложение WHERE, покажите все категории и названия товаров, но только для товаров, цена которых превышает 50 долларов.
Используя INNER JOIN, найдите названия товаров и названия категорий, упорядочив результат по цене товара в порядке убывания.
Используя LEFT JOIN, подсчитайте количество товаров в каждой категории. Отобразите название категории и количество.
Используя RIGHT JOIN, выведите список всех товаров и названий их категорий, включая товары, которые могут быть не назначены ни одной категории. Если у товара нет категории, укажите название категории как ‘Uncategorized’ (Некатегоризированный).

Вариант 2: Читатели библиотеки и книги

Сценарий: Библиотечная система отслеживает читателей и книги, которые им интересны (список желаний).

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

Таблицы: patrons (читатели), wishlist_books (список желаемых книг)

CREATE TABLE patrons (
    patron_id SERIAL PRIMARY KEY,
    patron_name VARCHAR(100) NOT NULL,
    library_card_number VARCHAR(20) UNIQUE,
    city VARCHAR(50)
);

CREATE TABLE wishlist_books (
    wishlist_id SERIAL PRIMARY KEY,
    patron_id INTEGER REFERENCES patrons(patron_id),
    book_title VARCHAR(150) NOT NULL,
    author VARCHAR(100)
);

Пример данных:

INSERT INTO patrons (patron_name, library_card_number, city) VALUES
('Alice Reader', 'LC123', 'New York'), -- Алиса Ридер
('Bob PageTurner', 'LC456', 'Los Angeles'), -- Боб ПейджТёрнер
('Charlie Bookworm', 'LC789', 'Chicago'), -- Чарли Букворм
('Diana LibraryFan', 'LC101', 'Houston'), -- Диана ЛайбрариФан
('Eve Novelist', 'LC112', 'New York'), -- Ева Новелист
('Frank Fictional', 'LC131', 'Los Angeles'); -- Фрэнк Фикшнал

INSERT INTO wishlist_books (patron_id, book_title, author) VALUES
(1, 'The Secret Garden', 'Frances Hodgson Burnett'), -- Таинственный сад
(NULL, 'Pride and Prejudice', 'Jane Austen'), -- Гордость и предубеждение
(1, 'To Kill a Mockingbird', 'Harper Lee'), -- Убить пересмешника
(3, '1984', 'George Orwell'), -- 1984
(4, 'The Great Gatsby', 'F. Scott Fitzgerald'), -- Великий Гэтсби
(NULL, 'Jane Eyre', 'Charlotte Brontë'), -- Джейн Эйр
(5, 'Moby Dick', 'Herman Melville'), -- Моби Дик
(6, 'Little Women', 'Louisa May Alcott'); -- Маленькие женщины

Задания:

Используя INNER JOIN, получите список имен читателей и названий книг в их списках желаний.
Используя LEFT JOIN, покажите всех читателей и названия книг в их списках желаний.
Используя RIGHT JOIN, выведите все книги из списка желаний и имена читателей, которые их пожелали.
Используя FULL OUTER JOIN, объедините читателей и книги из списка желаний.
Используя CROSS JOIN, сгенерируйте все комбинации читателей и книг из списка желаний.
Используя INNER JOIN с предложением WHERE, найдите имена читателей и названия книг для книг в списке желаний, автором которых является ‘Jane Austen’ (Джейн Остин).
Используя LEFT JOIN и предложение WHERE, покажите всех читателей и названия книг, но только для книг, название которых начинается с ‘The’ (предлог “The”).
Используя INNER JOIN, найдите имена читателей и названия книг в списке желаний, упорядочив результат по имени читателя в алфавитном порядке.
Используя LEFT JOIN, подсчитайте, сколько книг находится в списке желаний каждого читателя. Отобразите имя читателя и количество.
Используя RIGHT JOIN, выведите список всех книг из списка желаний и имена читателей, включая записи списка желаний, которые могут не иметь соответствующего читателя. Если у книги в списке желаний нет читателя, укажите имя читателя как ‘Unknown Patron’ (Неизвестный читатель).

Вариант 3: Пользователи социальных сетей и действия

Сценарий: Упрощенная платформа социальных сетей отслеживает пользователей и их действия (лайки).

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

Таблицы: users (пользователи), activities (действия)

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    city VARCHAR(50)
);

CREATE TABLE activities (
    activity_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    activity_type VARCHAR(50) NOT NULL, -- например, 'post_like', 'comment', 'follow'
    activity_timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Пример данных:

INSERT INTO users (username, city) VALUES
('NetizenNick', 'London'), -- НетизенНик
('SocialSam', 'Paris'), -- СошиалСэм
('OnlineOlivia', 'New York'), -- ОнлайнОливия
('DigitalDan', 'London'), -- ДиджиталДэн
('TechTina', 'San Francisco'), -- ТекТина
('GlobalGreg', 'Tokyo'); -- ГлобалГрег

INSERT INTO activities (user_id, activity_type) VALUES
(1, 'post_like'), -- лайк поста
(NULL, 'comment'), -- комментарий
(1, 'follow'), -- подписка
(3, 'post_like'), -- лайк поста
(4, 'post_like'), -- лайк поста
(NULL, 'follow'), -- подписка
(5, 'comment'), -- комментарий
(6, 'post_like'), -- лайк поста
(3, 'follow'); -- подписка

Задания:

Используя INNER JOIN, получите имена пользователей и типы их действий.
Используя LEFT JOIN, покажите всех пользователей и типы их действий.
Используя RIGHT JOIN, выведите все действия и имена пользователей, которые их совершили.
Используя FULL OUTER JOIN, объедините пользователей и действия.
Используя CROSS JOIN, сгенерируйте все комбинации пользователей и действий.
Используя INNER JOIN с предложением WHERE, найдите имена пользователей и типы действий для действий типа ‘post_like’ (лайк поста).
Используя LEFT JOIN и предложение WHERE, покажите всех пользователей и типы действий, но только для действий, которые произошли за последние 24 часа.
Используя INNER JOIN, найдите имена пользователей и типы действий, упорядочив результат по имени пользователя в алфавитном порядке.
Используя LEFT JOIN, подсчитайте количество действий для каждого пользователя. Отобразите имя пользователя и количество действий.
Используя RIGHT JOIN, выведите список всех действий и имен пользователей, включая действия, которые могут быть связаны с несуществующим пользователем. Если у действия нет пользователя, укажите имя пользователя как ‘Unknown User’ (Неизвестный пользователь).

Вариант 4: Авторы и статьи блог-платформы

Сценарий: Блог-платформе необходимо управлять авторами и их статьями.

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

Таблицы: authors (авторы), articles (статьи)

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    author_id INTEGER REFERENCES authors(author_id),
    article_title VARCHAR(200) NOT NULL,
    publication_date DATE NOT NULL
);

Пример данных:

INSERT INTO authors (author_name, email) VALUES
('Jane Doe', 'jane.doe@example.com'), -- Джейн Доу
('John Smith', 'john.smith@example.com'), -- Джон Смит
('Emily White', 'emily.white@example.com'), -- Эмили Уайт
('David Green', 'david.green@example.com'), -- Дэвид Грин
('Sophia Black', 'sophia.black@example.com'), -- София Блэк
('Oliver Brown', 'oliver.brown@example.com'); -- Оливер Браун

INSERT INTO articles (author_id, article_title, publication_date) VALUES
(1, 'The Future of Technology', '2024-08-01'), -- Будущее технологий
(NULL, 'Cooking at Home', '2024-08-05'), -- Готовим дома
(1, 'Travel in Europe', '2024-08-10'), -- Путешествия по Европе
(3, 'Gardening Tips', '2024-08-15'), -- Советы по садоводству
(4, 'Financial Planning', '2024-08-20'), -- Финансовое планирование
(NULL, 'Baking Desserts', '2024-08-25'), -- Выпечка десертов
(5, 'Photography Basics', '2024-08-30'), -- Основы фотографии
(6, 'Writing a Novel', '2024-09-05'); -- Как написать роман

Задания:

Используя INNER JOIN, получите список имен авторов и названий их статей.
Используя LEFT JOIN, покажите всех авторов и названия их статей.
Используя RIGHT JOIN, выведите все статьи и имена их авторов.
Используя FULL OUTER JOIN, объедините авторов и статьи.
Используя CROSS JOIN, сгенерируйте все комбинации авторов и статей.
Используя INNER JOIN с предложением WHERE, найдите имена авторов и названия статей, опубликованных в августе 2024 года.
Используя LEFT JOIN и предложение WHERE, покажите всех авторов и названия статей, но только для статей, содержащих слово ‘Cooking’ (готовка).
Используя INNER JOIN, найдите имена авторов и названия статей, упорядочив результат по дате публикации в порядке возрастания.
Используя LEFT JOIN, подсчитайте количество статей, написанных каждым автором. Отобразите имя автора и количество статей.
Используя RIGHT JOIN, выведите список всех статей и имен авторов, включая статьи, которые могут быть связаны с несуществующим автором. Если у статьи нет автора, укажите имя автора как ‘Unknown Author’ (Неизвестный автор).

Вариант 5: Музыкальные исполнители и песни

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

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

Таблицы: artists (исполнители), songs (песни)

CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    artist_name VARCHAR(100) NOT NULL UNIQUE,
    genre VARCHAR(50)
);

CREATE TABLE songs (
    song_id SERIAL PRIMARY KEY,
    artist_id INTEGER REFERENCES artists(artist_id),
    song_title VARCHAR(150) NOT NULL,
    duration_seconds INTEGER
);

Пример данных:

INSERT INTO artists (artist_name, genre) VALUES
('Rock Legends', 'Rock'), -- Рок-легенды
('Pop Sensation', 'Pop'), -- Поп-сенсация
('Jazz Virtuosos', 'Jazz'), -- Джазовые виртуозы
('Blues Masters', 'Blues'), -- Блюз-мастера
('Classical Harmony', 'Classical'), -- Классическая гармония
('Indie Vibes', 'Indie'); -- Инди-настроение

INSERT INTO songs (artist_id, song_title, duration_seconds) VALUES
(1, 'Stairway to Heaven', 480), -- Лестница в небеса
(NULL, 'Happy Tune', 240), -- Веселая мелодия
(1, 'Rock Anthem', 300), -- Рок-гимн
(3, 'Smooth Jazz', 360), -- Смус-джаз
(NULL, 'Pop Ballad', 270), -- Поп-баллада
(4, 'Blues Solo', 390), -- Блюзовое соло
(5, 'Symphony No. 5', 600), -- Симфония №5
(6, 'Indie Track', 210); -- Инди-трек

Задания:

Используя INNER JOIN, получите список имен исполнителей и названий их песен.
Используя LEFT JOIN, покажите всех исполнителей и названия их песен.
Используя RIGHT JOIN, выведите все песни и имена исполнителей, которые их исполнили.
Используя FULL OUTER JOIN, объедините исполнителей и песни.
Используя CROSS JOIN, сгенерируйте все комбинации исполнителей и песен.
Используя INNER JOIN с предложением WHERE, найдите имена исполнителей и названия песен, продолжительность которых превышает 300 секунд.
Используя LEFT JOIN и предложение WHERE, покажите всех исполнителей и названия песен, но только для песен в жанре ‘Pop’ (Вам нужно будет так же присоединить и жанр исполнителя, или же подправить схему данных).
Используя INNER JOIN, найдите имена исполнителей и названия песен, упорядочив результат по продолжительности песни в порядке убывания.
Используя LEFT JOIN, подсчитайте количество песен для каждого исполнителя. Отобразите имя исполнителя и количество песен.
Используя RIGHT JOIN, выведите список всех песен и имен исполнителей, включая песни, которые могут быть связаны с несуществующим исполнителем. Если у песни нет исполнителя, укажите имя исполнителя как ‘Unknown Artist’ (Неизвестный исполнитель).

Вариант 6: Университетские курсы и преподаватели

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

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

Таблицы: instructors (преподаватели), courses (курсы)

CREATE TABLE instructors (
    instructor_id SERIAL PRIMARY KEY,
    instructor_name VARCHAR(100) NOT NULL,
    department VARCHAR(100)
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(150) NOT NULL,
    credits INTEGER,
    instructor_id INTEGER REFERENCES instructors(instructor_id)
);

Пример данных:

INSERT INTO instructors (instructor_name, department) VALUES
('Dr. Smith', 'Computer Science'), -- Др. Смит, Информатика
('Prof. Jones', 'Mathematics'), -- Проф. Джонс, Математика
('Dr. Williams', 'Physics'), -- Др. Уильямс, Физика
('Prof. Brown', 'History'), -- Проф. Браун, История
('Dr. Davis', 'English'), -- Др. Дэвис, Английский язык
('Prof. Miller', 'Biology'); -- Проф. Миллер, Биология

INSERT INTO courses (course_name, credits, instructor_id) VALUES
('Intro to CS', 3, 1), -- Введение в информатику
('Calculus II', 4, NULL), -- Математический анализ II
('Quantum Physics', 3, 3), -- Квантовая физика
('American History', 3, 4), -- История Америки
('Shakespearean Lit', 3, 5), -- Литература Шекспира
('Genetics 101', 4, 6), -- Генетика 101
('Data Structures', 4, 1), -- Структуры данных
('Linear Algebra', 3, NULL); -- Линейная алгебра

Задания:

Используя INNER JOIN, получите список имен преподавателей и названия курсов, которые они ведут.
Используя LEFT JOIN, покажите всех преподавателей и названия курсов, которые они ведут.
Используя RIGHT JOIN, выведите все курсы и имена преподавателей, которые их ведут.
Используя FULL OUTER JOIN, объедините преподавателей и курсы.
Используя CROSS JOIN, сгенерируйте все комбинации преподавателей и курсов.
Используя INNER JOIN с предложением WHERE, найдите имена преподавателей и названия курсов для курсов с 4 кредитами.
Используя LEFT JOIN и предложение WHERE, покажите всех преподавателей и названия курсов, но только для курсов, предлагаемых на кафедре ‘Mathematics’ (Математика) (вам может потребоваться присоединить кафедру преподавателя или изменить схему).
Используя INNER JOIN, найдите имена преподавателей и названия курсов, упорядочив результат по количеству кредитов курса в порядке убывания.
Используя LEFT JOIN, подсчитайте количество курсов, которые ведет каждый преподаватель. Отобразите имя преподавателя и количество курсов.
Используя RIGHT JOIN, выведите список всех курсов и имен преподавателей, включая курсы, которые могут быть связаны с несуществующим преподавателем. Если у курса нет преподавателя, укажите имя преподавателя как ‘Unassigned Instructor’ (Неназначенный преподаватель).

Вариант 7: Пациенты больницы и врачи

Сценарий: База данных больницы отслеживает пациентов и назначенных им врачей.

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

Таблицы: doctors (врачи), patients (пациенты)

CREATE TABLE doctors (
    doctor_id SERIAL PRIMARY KEY,
    doctor_name VARCHAR(100) NOT NULL,
    specialization VARCHAR(50)
);

CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    patient_name VARCHAR(100) NOT NULL,
    diagnosis VARCHAR(100),
    doctor_id INTEGER REFERENCES doctors(doctor_id)
);

Пример данных:

INSERT INTO doctors (doctor_name, specialization) VALUES
('Dr. Aisha Khan', 'Cardiology'), -- Доктор Аиша Хан, Кардиология
('Dr. Ben Carter', 'Oncology'), -- Доктор Бен Картер, Онкология
('Dr. Chloe Davis', 'Pediatrics'), -- Доктор Хлоя Дэвис, Педиатрия
('Dr. David Lee', 'Neurology'), -- Доктор Дэвид Ли, Неврология
('Dr. Emily Foster', 'Dermatology'), -- Доктор Эмили Фостер, Дерматология
('Dr. Frank Green', 'Orthopedics'); -- Доктор Фрэнк Грин, Ортопедия

INSERT INTO patients (patient_name, diagnosis, doctor_id) VALUES
('Patient Alpha', 'Hypertension', 1), -- Пациент Альфа, Гипертония
('Patient Beta', 'Leukemia', NULL), -- Пациент Бета, Лейкемия
('Patient Gamma', 'Asthma', 3), -- Пациент Гамма, Астма
('Patient Delta', 'Migraine', 4), -- Пациент Дельта, Мигрень
('Patient Epsilon', 'Eczema', 5), -- Пациент Эпсилон, Экзема
('Patient Zeta', 'Fractured Femur', 6), -- Пациент Зета, Перелом бедренной кости
('Patient Eta', 'Arrhythmia', 1), -- Пациент Эта, Аритмия
('Patient Theta', 'Melanoma', NULL); -- Пациент Тета, Меланома

Задания:

Используя INNER JOIN, выведите имена пациентов и имена назначенных им врачей.
Используя LEFT JOIN, покажите всех врачей и пациентов, которым они назначены.
Используя RIGHT JOIN, выведите всех пациентов и имена их врачей.
Используя FULL OUTER JOIN, объедините врачей и пациентов.
Используя CROSS JOIN, сгенерируйте все комбинации врачей и пациентов.
Используя INNER JOIN с предложением WHERE, найдите имена пациентов и имена врачей для пациентов с диагнозом ‘Cancer’ (Рак) (скорректируйте названия диагнозов в соответствии с примерами данных, например, ‘Leukemia’, ‘Melanoma’).
Используя LEFT JOIN и предложение WHERE, покажите всех врачей и имена пациентов, но только для пациентов с диагнозом ‘Hypertension’ (Гипертония).
Используя INNER JOIN, найдите имена пациентов и имена врачей, упорядочив результат по имени врача в алфавитном порядке.
Используя LEFT JOIN, подсчитайте количество пациентов, назначенных каждому врачу. Отобразите имя врача и количество пациентов.
Используя RIGHT JOIN, выведите список всех пациентов и имен врачей, включая пациентов, которые могут быть связаны с несуществующим врачом. Если у пациента нет врача, укажите имя врача как ‘Unassigned Doctor’ (Неназначенный врач).

Вариант 8: Сообщения и пользователи онлайн-форума

Сценарий: База данных онлайн-форума отслеживает пользователей и созданные ими сообщения.

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

Таблицы: forum_users (пользователи форума), forum_posts (сообщения форума)

CREATE TABLE forum_users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    join_date DATE
);

CREATE TABLE forum_posts (
    post_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES forum_users(user_id),
    post_title VARCHAR(200) NOT NULL,
    post_date DATE
);

Пример данных:

INSERT INTO forum_users (username, join_date) VALUES
('CodeCrusader', '2024-01-15'), -- КодКрусейдер
('DataNinja', '2024-02-20'), -- ДатаНиндзя
('SQLSamurai', '2024-03-10'), -- SQLСамурай
('WebWizard', '2024-04-05'), -- ВебВизард
('CyberPunk', '2024-05-22'), -- КиберПанк
('DebugDude', '2024-06-30'); -- ДебагДюд

INSERT INTO forum_posts (user_id, post_title, post_date) VALUES
(1, 'Introduction to SQL JOINs', '2024-07-01'), -- Введение в SQL JOIN
(NULL, 'Database Normalization Guide', '2024-07-05'), -- Руководство по нормализации баз данных
(1, 'Advanced PostgreSQL Features', '2024-07-10'), -- Расширенные возможности PostgreSQL
(3, 'Query Optimization Techniques', '2024-07-15'), -- Методы оптимизации запросов
(4, 'Frontend vs Backend Databases', '2024-07-20'), -- Фронтенд и бэкенд базы данных
(NULL, 'NoSQL vs SQL Databases', '2024-07-25'), -- NoSQL против SQL баз данных
(5, 'Cybersecurity in Databases', '2024-07-30'), -- Кибербезопасность в базах данных
(6, 'Troubleshooting Common SQL Errors', '2024-08-05'); -- Устранение распространенных ошибок SQL

Задания:

Используя INNER JOIN, получите имена пользователей и названия их сообщений на форуме.
Используя LEFT JOIN, покажите всех пользователей форума и названия их сообщений.
Используя RIGHT JOIN, выведите все сообщения форума и имена пользователей, которые их создали.
Используя FULL OUTER JOIN, объедините пользователей форума и сообщения форума.
Используя CROSS JOIN, сгенерируйте все комбинации пользователей форума и сообщений форума.
Используя INNER JOIN с предложением WHERE, найдите имена пользователей и названия сообщений для сообщений, созданных в июле 2024 года.
Используя LEFT JOIN и предложение WHERE, покажите всех пользователей форума и названия сообщений, но только для сообщений, содержащих слово ‘Database’ (База данных).
Используя INNER JOIN, найдите имена пользователей и названия сообщений, упорядочив результат по дате сообщения в порядке возрастания.
Используя LEFT JOIN, подсчитайте количество сообщений, созданных каждым пользователем форума. Отобразите имя пользователя и количество сообщений.
Используя RIGHT JOIN, выведите список всех сообщений форума и имен пользователей, включая сообщения, которые могут быть связаны с несуществующим пользователем. Если у сообщения нет пользователя, укажите имя пользователя как ‘Unknown User’ (Неизвестный пользователь).

Вариант 9: Сеансы кинотеатра и фильмы

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

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

Таблицы: movies (фильмы), screenings (сеансы)

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    movie_title VARCHAR(100) NOT NULL,
    genre VARCHAR(50)
);

CREATE TABLE screenings (
    screening_id SERIAL PRIMARY KEY,
    movie_id INTEGER REFERENCES movies(movie_id),
    screening_time TIMESTAMP WITHOUT TIME ZONE,
    room_number INTEGER
);

Пример данных:

INSERT INTO movies (movie_title, genre) VALUES
('Action Hero', 'Action'), -- Герой боевиков, Боевик
('Comedy Club', 'Comedy'), -- Комедийный клуб, Комедия
('Drama Queen', 'Drama'), -- Королева драмы, Драма
('Sci-Fi Saga', 'Science Fiction'), -- Научно-фантастическая сага, Научная фантастика
('Thriller Night', 'Thriller'), -- Вечер триллеров, Триллер
('Animated Adventure', 'Animation'); -- Анимационное приключение, Анимация

INSERT INTO screenings (movie_id, screening_time, room_number) VALUES
(1, '2024-09-10 14:00:00', 1), --  14:00
(NULL, '2024-09-10 16:30:00', 2), --  16:30
(1, '2024-09-10 19:00:00', 1), --  19:00
(3, '2024-09-11 15:00:00', 3), --  15:00
(4, '2024-09-11 18:00:00', 4), --  18:00
(NULL, '2024-09-11 21:00:00', 2), --  21:00
(5, '2024-09-12 13:30:00', 5), --  13:30
(6, '2024-09-12 17:00:00', 6); --  17:00

Задания:

Используя INNER JOIN, получите названия фильмов и время их сеансов.
Используя LEFT JOIN, покажите все фильмы и время их сеансов.
Используя RIGHT JOIN, выведите все сеансы и названия фильмов, которые на них показываются.
Используя FULL OUTER JOIN, объедините фильмы и сеансы.
Используя CROSS JOIN, сгенерируйте все комбинации фильмов и сеансов.
Используя INNER JOIN с предложением WHERE, найдите названия фильмов и время сеансов для сеансов в зале номер 1.
Используя LEFT JOIN и предложение WHERE, покажите все фильмы и время сеансов, но только для сеансов ‘2024-09-10’.
Используя INNER JOIN, найдите названия фильмов и время сеансов, упорядочив результат по времени сеанса в порядке возрастания.
Используя LEFT JOIN, подсчитайте количество сеансов для каждого фильма. Отобразите название фильма и количество сеансов.
Используя RIGHT JOIN, выведите список всех сеансов и названий фильмов, включая сеансы, которые могут быть связаны с несуществующим фильмом. Если у сеанса нет фильма, укажите название фильма как ‘Movie Not Found’ (Фильм не найден).

Вариант 10: Автосалон: Автомобили и Модели

Сценарий: База данных автосалона отслеживает автомобили в наличии и информацию об их моделях.

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

Таблицы: car_models (модели автомобилей), cars (автомобили)

CREATE TABLE car_models (
    model_id SERIAL PRIMARY KEY,
    model_name VARCHAR(50) NOT NULL UNIQUE,
    manufacturer VARCHAR(50)
);

CREATE TABLE cars (
    car_id SERIAL PRIMARY KEY,
    model_id INTEGER REFERENCES car_models(model_id),
    vin_number VARCHAR(50) UNIQUE,
    color VARCHAR(20)
);

Пример данных:

INSERT INTO car_models (model_name, manufacturer) VALUES
('Sedan X', 'AutoCorp'), -- Седан X
('SUV Max', 'Family Motors'), -- Внедорожник Max
('Sporty Z', 'Speedy Cars'), -- Спортивный Z
('Truck XL', 'Haul Trucks'), -- Грузовик XL
('Mini M', 'City Auto'), -- Мини M
('Convertible C', 'Open Road'); -- Кабриолет C

INSERT INTO cars (model_id, vin_number, color) VALUES
(1, 'VIN001', 'Red'), -- Красный
(NULL, 'VIN002', 'Blue'), -- Синий
(1, 'VIN003', 'Silver'), -- Серебристый
(3, 'VIN004', 'Black'), -- Черный
(4, 'VIN005', 'White'), -- Белый
(NULL, 'VIN006', 'Green'), -- Зеленый
(5, 'VIN007', 'Yellow'), -- Желтый
(6, 'VIN008', 'Orange'); -- Оранжевый

Задания:

Используя INNER JOIN, получите VIN-номера автомобилей и названия их моделей.
Используя LEFT JOIN, покажите все модели автомобилей и VIN-номера автомобилей каждой модели, имеющихся в наличии.
Используя RIGHT JOIN, выведите все автомобили и названия их моделей.
Используя FULL OUTER JOIN, объедините модели автомобилей и автомобили.
Используя CROSS JOIN, сгенерируйте все комбинации моделей автомобилей и автомобилей.
Используя INNER JOIN с предложением WHERE, найдите VIN-номера и названия моделей автомобилей, которые имеют цвет ‘Red’ (Красный).
Используя LEFT JOIN и предложение WHERE, покажите все модели автомобилей и VIN-номера автомобилей, но только для автомобилей, произведенных компанией ‘AutoCorp’.
Используя INNER JOIN, найдите VIN-номера автомобилей и названия моделей, упорядочив результат по названию модели в алфавитном порядке.
Используя LEFT JOIN, подсчитайте количество автомобилей в наличии для каждой модели. Отобразите название модели и количество автомобилей.
Используя RIGHT JOIN, выведите список всех автомобилей и названий моделей, включая автомобили, которые могут быть связаны с несуществующей моделью автомобиля. Если у автомобиля нет модели, укажите название модели как ‘Unknown Model’ (Неизвестная модель).

Вариант 11: Туристическое агентство: Бронирования и Клиенты

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

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

Таблицы: customers (клиенты), bookings (бронирования)

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(20)
);

CREATE TABLE bookings (
    booking_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    trip_destination VARCHAR(100) NOT NULL,
    booking_date DATE
);

Пример данных:

INSERT INTO customers (customer_name, email, phone_number) VALUES
('Alice Wonderland', 'alice@example.com', '123-456-7890'), -- Алиса Уандерленд
('Bob The Builder', 'bob@example.com', '987-654-3210'), -- Боб Строитель
('Charlie Chaplin', 'charlie@example.com', '111-222-3333'), -- Чарли Чаплин
('Diana Prince', 'diana@example.com', '444-555-6666'), -- Диана Принс
('Eve Harrington', 'eve@example.com', '777-888-9999'), -- Ева Харрингтон
('Frank Sinatra', 'frank@example.com', '101-202-3030'); -- Фрэнк Синатра

INSERT INTO bookings (customer_id, trip_destination, booking_date) VALUES
(1, 'Paris', '2024-09-01'), -- Париж
(NULL, 'Rome', '2024-09-05'), -- Рим
(1, 'Tokyo', '2024-09-10'), -- Токио
(3, 'London', '2024-09-15'), -- Лондон
(4, 'New York', '2024-09-20'), -- Нью-Йорк
(NULL, 'Barcelona', '2024-09-25'), -- Барселона
(5, 'Sydney', '2024-09-30'), -- Сидней
(6, 'Cairo', '2024-10-05'); -- Каир

Задания:

Используя INNER JOIN, получите имена клиентов и пункты назначения их поездок.
Используя LEFT JOIN, покажите всех клиентов и забронированные ими пункты назначения поездок.
Используя RIGHT JOIN, выведите все бронирования и имена клиентов, которые их сделали.
Используя FULL OUTER JOIN, объедините клиентов и бронирования.
Используя CROSS JOIN, сгенерируйте все комбинации клиентов и бронирований.
Используя INNER JOIN с предложением WHERE, найдите имена клиентов и пункты назначения для поездок в ‘Paris’ (Париж).
Используя LEFT JOIN и предложение WHERE, покажите всех клиентов и пункты назначения поездок, но только для бронирований, сделанных в сентябре 2024 года.
Используя INNER JOIN, найдите имена клиентов и пункты назначения поездок, упорядочив результат по дате бронирования в порядке возрастания.
Используя LEFT JOIN, подсчитайте количество бронирований, сделанных каждым клиентом. Отобразите имя клиента и количество бронирований.
Используя RIGHT JOIN, выведите список всех бронирований и имен клиентов, включая бронирования, которые могут быть связаны с несуществующим клиентом. Если у бронирования нет клиента, укажите имя клиента как ‘Unknown Customer’ (Неизвестный клиент).

Вариант 12: Запись на онлайн-курсы и Студенты

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

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

Таблицы: online_students (онлайн-студенты), course_enrollments (записи на курсы)

CREATE TABLE online_students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

CREATE TABLE course_enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES online_students(student_id),
    course_title VARCHAR(150) NOT NULL,
    enrollment_date DATE
);

Пример данных:

INSERT INTO online_students (student_name, email) VALUES
('Anna Learner', 'anna@learn.com'), -- Анна Лернер
('Ben Study', 'ben@study.edu'), -- Бен Стади
('Cathy Online', 'cathy@online.org'), -- Кэти Онлайн
('David Course', 'david@course.net'), -- Дэвид Курс
('Emily Class', 'emily@class.info'), -- Эмили Класс
('Frank Virtual', 'frank@virtual.com'); -- Фрэнк Виртуал

INSERT INTO course_enrollments (student_id, course_title, enrollment_date) VALUES
(1, 'Web Development Basics', '2024-08-15'), -- Основы веб-разработки
(NULL, 'Data Science Fundamentals', '2024-08-20'), -- Основы науки о данных
(1, 'Advanced JavaScript', '2024-08-25'), -- Продвинутый JavaScript
(3, 'Python for Beginners', '2024-08-30'), -- Python для начинающих
(4, 'Machine Learning Intro', '2024-09-01'), -- Введение в машинное обучение
(NULL, 'SQL for Data Analysis', '2024-09-05'), -- SQL для анализа данных
(5, 'Cloud Computing Essentials', '2024-09-10'), -- Основы облачных вычислений
(6, 'Cybersecurity Awareness', '2024-09-15'); -- Осведомленность о кибербезопасности

Задания:

Используя INNER JOIN, получите имена студентов и названия курсов, на которые они записаны.
Используя LEFT JOIN, покажите всех онлайн-студентов и курсы, на которые они записаны.
Используя RIGHT JOIN, выведите все записи на курсы и имена зачисленных студентов.
Используя FULL OUTER JOIN, объедините онлайн-студентов и записи на курсы.
Используя CROSS JOIN, сгенерируйте все комбинации онлайн-студентов и записей на курсы.
Используя INNER JOIN с предложением WHERE, найдите имена студентов и названия курсов для записей на курс ‘Data Science Fundamentals’ (Основы науки о данных).
Используя LEFT JOIN и предложение WHERE, покажите всех онлайн-студентов и названия курсов, но только для курсов, на которые были записаны в августе 2024 года.
Используя INNER JOIN, найдите имена студентов и названия курсов, упорядочив результат по названию курса в алфавитном порядке.
Используя LEFT JOIN, подсчитайте количество курсов, на которые записан каждый онлайн-студент. Отобразите имя студента и количество курсов.
Используя RIGHT JOIN, выведите список всех записей на курсы и имен студентов, включая записи, которые могут быть связаны с несуществующим студентом. Если у записи нет студента, укажите имя студента как ‘Student Not Found’ (Студент не найден).

Вариант 13: Заказы в ресторане и пункты меню

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

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

Таблицы: menu_items (пункты меню), orders (заказы)

CREATE TABLE menu_items (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(5, 2)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    item_id INTEGER REFERENCES menu_items(item_id),
    order_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    table_number INTEGER
);

Пример данных:

INSERT INTO menu_items (item_name, category, price) VALUES
('Margherita Pizza', 'Pizza', 12.99), -- Пицца Маргарита
('Chicken Caesar Salad', 'Salad', 9.50), -- Салат Цезарь с курицей
('Beef Burger', 'Burger', 11.75), -- Говяжий бургер
('Vegetarian Pasta', 'Pasta', 10.25), -- Вегетарианская паста
('Chocolate Cake', 'Dessert', 6.50), -- Шоколадный торт
('Iced Tea', 'Drinks', 2.99); -- Холодный чай

INSERT INTO orders (item_id, table_number) VALUES
(1, 101),
(2, 101),
(NULL, 102),
(1, 103),
(4, 102),
(5, 104),
(6, 103),
(NULL, 104);

Задания:

Используя INNER JOIN, получите названия пунктов меню и идентификаторы заказов, частью которых они являются.
Используя LEFT JOIN, покажите все пункты меню и идентификаторы заказов, в которые они были включены.
Используя RIGHT JOIN, выведите все заказы и названия заказанных пунктов меню.
Используя FULL OUTER JOIN, объедините пункты меню и заказы.
Используя CROSS JOIN, сгенерируйте все комбинации пунктов меню и заказов.
Используя INNER JOIN с предложением WHERE, найдите названия пунктов меню и идентификаторы заказов для заказов за столиком номер 102.
Используя LEFT JOIN и предложение WHERE, покажите все пункты меню и идентификаторы заказов, но только для пунктов из категории ‘Pizza’ (Пицца).
Используя INNER JOIN, найдите названия пунктов меню и идентификаторы заказов, упорядочив результат по цене пункта в порядке убывания.
Используя LEFT JOIN, подсчитайте количество заказов для каждого пункта меню. Отобразите название пункта и количество заказов.
Используя RIGHT JOIN, выведите список всех заказов и названий пунктов меню, включая заказы, которые могут быть связаны с несуществующим пунктом меню. Если у заказа нет пункта меню, укажите название пункта как ‘Item Not Found’ (Пункт не найден).

Вариант 14: Задачи по управлению проектами и сотрудники

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

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

Таблицы: employees (сотрудники), tasks (задачи)

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(employee_id),
    task_description VARCHAR(200) NOT NULL,
    due_date DATE
);

Пример данных:

INSERT INTO employees (employee_name, department) VALUES
('Alice Manager', 'Management'), -- Алиса Менеджер
('Bob Developer', 'Development'), -- Боб Разработчик
('Charlie Designer', 'Design'), -- Чарли Дизайнер
('Diana Tester', 'Testing'), -- Диана Тестировщик
('Eve Analyst', 'Analysis'), -- Ева Аналитик
('Frank Support', 'Support'); -- Фрэнк Поддержка

INSERT INTO tasks (employee_id, task_description, due_date) VALUES
(1, 'Project Planning', '2024-10-20'), -- Планирование проекта
(NULL, 'Develop User Interface', '2024-10-25'), -- Разработка пользовательского интерфейса
(1, 'Team Coordination', '2024-10-30'), -- Координация команды
(3, 'Design Mockups', '2024-11-05'), -- Дизайн макетов
(4, 'Testing Phase 1', '2024-11-10'), -- Тестирование, фаза 1
(NULL, 'Implement Backend Logic', '2024-11-15'), -- Реализация серверной логики
(5, 'Data Analysis Report', '2024-11-20'), -- Отчет по анализу данных
(6, 'Customer Support Training', '2024-11-25'); -- Обучение службы поддержки клиентов

Задания:

Используя INNER JOIN, получите имена сотрудников и описания задач, назначенных им.
Используя LEFT JOIN, покажите всех сотрудников и описания назначенных им задач.
Используя RIGHT JOIN, выведите все задачи и имена сотрудников, назначенных им.
Используя FULL OUTER JOIN, объедините сотрудников и задачи.
Используя CROSS JOIN, сгенерируйте все комбинации сотрудников и задач.
Используя INNER JOIN с предложением WHERE, найдите имена сотрудников и описания задач для задач, срок выполнения которых наступает после ‘2024-11-01’.
Используя LEFT JOIN и предложение WHERE, покажите всех сотрудников и описания задач, но только для задач, связанных с отделом ‘Development’ (Разработка) (вам может потребоваться объединить отдел сотрудника или изменить схему).
Используя INNER JOIN, найдите имена сотрудников и описания задач, упорядочив результат по дате выполнения в порядке возрастания.
Используя LEFT JOIN, подсчитайте количество задач, назначенных каждому сотруднику. Отобразите имя сотрудника и количество задач.
Используя RIGHT JOIN, выведите список всех задач и имен сотрудников, включая задачи, которые могут быть связаны с несуществующим сотрудником. Если у задачи нет сотрудника, укажите имя сотрудника как ‘Unassigned Employee’ (Сотрудник не назначен).

Вариант 15: Билеты на мероприятия и посетители

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

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

Таблицы: events (мероприятия), attendees (посетители)

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name VARCHAR(100) NOT NULL,
    event_date DATE
);

CREATE TABLE attendees (
    attendee_id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES events(event_id),
    attendee_name VARCHAR(100) NOT NULL,
    ticket_type VARCHAR(50)
);

Пример данных:

INSERT INTO events (event_name, event_date) VALUES
('Tech Conference 2024', '2024-12-05'), -- Техническая конференция 2024
('Music Festival Fall', '2024-12-10'), -- Музыкальный фестиваль Осень
('Art Exhibition Gala', '2024-12-15'), -- Гала-выставка искусств
('Food Fair Winter', '2024-12-20'), -- Зимняя ярмарка еды
('Science Symposium', '2024-12-25'), -- Научный симпозиум
('Literature Festival', '2024-12-30'); -- Литературный фестиваль

INSERT INTO attendees (event_id, attendee_name, ticket_type) VALUES
(1, 'Attendee One', 'Regular'), -- Посетитель Один, Обычный
(NULL, 'Attendee Two', 'VIP'), -- Посетитель Два, VIP
(1, 'Attendee Three', 'Regular'), -- Посетитель Три, Обычный
(3, 'Attendee Four', 'Regular'), -- Посетитель Четыре, Обычный
(4, 'Attendee Five', 'VIP'), -- Посетитель Пять, VIP
(NULL, 'Attendee Six', 'Regular'), -- Посетитель Шесть, Обычный
(5, 'Attendee Seven', 'Regular'), -- Посетитель Семь, Обычный
(6, 'Attendee Eight', 'VIP'); -- Посетитель Восемь, VIP

Задания:

Используя INNER JOIN, получите названия мероприятий и имена посетителей, у которых есть на них билеты.
Используя LEFT JOIN, покажите все мероприятия и имена посетителей для каждого мероприятия.
Используя RIGHT JOIN, выведите всех посетителей и названия мероприятий, которые они посещают.
Используя FULL OUTER JOIN, объедините мероприятия и посетителей.
Используя CROSS JOIN, сгенерируйте все комбинации мероприятий и посетителей.
Используя INNER JOIN с предложением WHERE, найдите названия мероприятий и имена посетителей для посетителей с билетами типа ‘VIP’.
Используя LEFT JOIN и предложение WHERE, покажите все мероприятия и имена посетителей, но только для мероприятий, проходящих в декабре 2024 года.
Используя INNER JOIN, найдите названия мероприятий и имена посетителей, упорядочив результат по дате мероприятия в порядке возрастания.
Используя LEFT JOIN, подсчитайте количество посетителей каждого мероприятия. Отобразите название мероприятия и количество посетителей.
Используя RIGHT JOIN, выведите список всех посетителей и названий мероприятий, включая посетителей, которые могут быть связаны с несуществующим мероприятием. Если у посетителя нет мероприятия, укажите название мероприятия как ‘Event Not Found’ (Мероприятие не найдено).

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

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