https://pastein.ru/t/3BB
Загрузка данных
Билет №1: Успеваемость студентов ВУЗА
1. Создание таблиц (DDL)
-- Билет №1: Успеваемость студентов ВУЗА
CREATE TABLE факультеты (
id_факультета SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL UNIQUE,
декан VARCHAR(100) NOT NULL,
год_основания INT CHECK (год_основания > 1900 AND год_основания <= EXTRACT(YEAR FROM CURRENT_DATE))
);
CREATE TABLE кафедры (
id_кафедры SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL,
заведующий VARCHAR(100) NOT NULL,
id_факультета INT NOT NULL REFERENCES факультеты(id_факультета) ON DELETE CASCADE
);
CREATE TABLE учебные_группы (
id_группы SERIAL PRIMARY KEY,
название VARCHAR(20) NOT NULL UNIQUE,
курс INT NOT NULL CHECK (курс BETWEEN 1 AND 6),
id_кафедры INT NOT NULL REFERENCES кафедры(id_кафедры) ON DELETE SET NULL
);
CREATE TABLE студенты (
id_студента SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
отчество VARCHAR(50),
дата_рождения DATE NOT NULL,
id_группы INT NOT NULL REFERENCES учебные_группы(id_группы) ON DELETE SET NULL
);
CREATE TABLE ведомости_успеваемости (
id_ведомости SERIAL PRIMARY KEY,
id_студента INT NOT NULL REFERENCES студенты(id_студента) ON DELETE CASCADE,
дисциплина VARCHAR(100) NOT NULL,
оценка INT CHECK (оценка BETWEEN 2 AND 5),
дата_сдачи DATE NOT NULL,
семестр INT NOT NULL CHECK (семестр BETWEEN 1 AND 12)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные
INSERT INTO факультеты (название, декан, год_основания) VALUES
('Факультет информационных технологий', 'Петров А.В.', 1995),
('Факультет экономики', 'Иванова М.С.', 1990),
('Факультет математики', 'Сидоров Н.П.', 1988),
('Факультет физики', 'Кузнецов В.И.', 1992),
('Факультет химии', 'Новикова Е.Д.', 2000);
INSERT INTO кафедры (название, заведующий, id_факультета) VALUES
('Кафедра программирования', 'Алексеев К.Р.', 1),
('Кафедра сетей', 'Белова О.С.', 1),
('Кафедра бухучёта', 'Гаврилов Д.Т.', 2),
('Кафедра высшей математики', 'Демидов Ф.Л.', 3),
('Кафедра теоретической физики', 'Ершов И.Н.', 4);
INSERT INTO учебные_группы (название, курс, id_кафедры) VALUES
('ПИ-11', 1, 1),
('ПИ-21', 2, 1),
('СИ-11', 1, 2),
('БУ-21', 2, 3),
('МА-31', 3, 4);
INSERT INTO студенты (фамилия, имя, отчество, дата_рождения, id_группы) VALUES
('Козлов', 'Алексей', 'Игоревич', '2004-03-15', 1),
('Морозова', 'Дарья', 'Сергеевна', '2003-07-22', 1),
('Волков', 'Никита', 'Андреевич', '2002-11-05', 2),
('Лебедева', 'Анна', 'Михайловна', '2003-04-18', 3),
('Соколов', 'Дмитрий', 'Олегович', '2001-09-30', 4);
INSERT INTO ведомости_успеваемости (id_студента, дисциплина, оценка, дата_сдачи, семестр) VALUES
(1, 'Базы данных', 5, '2024-01-15', 2),
(2, 'Базы данных', 4, '2024-01-16', 2),
(3, 'Алгоритмы', 3, '2024-01-18', 4),
(4, 'Сети', 5, '2024-01-20', 2),
(5, 'Бухучёт', 4, '2024-01-22', 4);
3. SQL-запросы
-- 1. Простой запрос: студенты с оценкой 5
SELECT с.фамилия, с.имя, в.дисциплина, в.оценка
FROM студенты с
JOIN ведомости_успеваемости в ON с.id_студента = в.id_студента
WHERE в.оценка = 5;
-- 2. Запрос с соединением таблиц: студенты с группами и факультетами
SELECT с.фамилия, с.имя, г.название AS группа, ф.название AS факультет
FROM студенты с
JOIN учебные_группы г ON с.id_группы = г.id_группы
JOIN кафедры к ON г.id_кафедры = к.id_кафедры
JOIN факультеты ф ON к.id_факультета = ф.id_факультета;
-- 3. Группировка: средняя оценка по дисциплинам
SELECT дисциплина, ROUND(AVG(оценка), 2) AS средняя_оценка, COUNT(*) AS кол_во_сдач
FROM ведомости_успеваемости
GROUP BY дисциплина
ORDER BY средняя_оценка DESC;
-- 4. Подзапрос: студенты с оценкой выше средней
SELECT фамилия, имя FROM студенты
WHERE id_студента IN (
SELECT id_студента FROM ведомости_успеваемости
WHERE оценка > (SELECT AVG(оценка) FROM ведомости_успеваемости)
);
-- 5. Обновление: повысить оценку 3 до 4 за Базы данных
UPDATE ведомости_успеваемости
SET оценка = 4
WHERE дисциплина = 'Базы данных' AND оценка = 3;
4. Руководство пользователя
## Руководство пользователя: БД «Успеваемость студентов ВУЗА»
### Назначение
База данных предназначена для учёта успеваемости студентов вуза: хранения сведений о факультетах, кафедрах, учебных группах, студентах и их оценках.
### Работа с БД
**Добавление студента:** выполнить INSERT в таблицу студенты, указав id существующей группы.
**Добавление оценки:** выполнить INSERT в ведомости_успеваемости, указав id студента, дисциплину, оценку (2–5), дату и семестр.
**Просмотр успеваемости группы:** использовать запрос с JOIN по таблицам студенты и ведомости_успеваемости с фильтром по id_группы.
**Изменение данных:** использовать UPDATE с условием WHERE для точного выбора записи.
**Удаление студента:** DELETE из таблицы студенты; оценки удалятся автоматически (CASCADE).
### Ограничения
Оценка может быть только от 2 до 5. Курс — от 1 до 6. Семестр — от 1 до 12.
Билет №2: Информационная система супермаркета
1. Создание таблиц (DDL)
-- Билет №2: Информационная система супермаркета
CREATE TABLE должности (
id_должности SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL UNIQUE,
оклад NUMERIC(10,2) NOT NULL CHECK (оклад > 0)
);
CREATE TABLE отделы (
id_отдела SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL UNIQUE,
этаж INT NOT NULL CHECK (этаж >= 1)
);
CREATE TABLE сотрудники (
id_сотрудника SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
id_отдела INT NOT NULL REFERENCES отделы(id_отдела),
id_должности INT NOT NULL REFERENCES должности(id_должности),
дата_найма DATE NOT NULL
);
CREATE TABLE товары (
id_товара SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
цена NUMERIC(10,2) NOT NULL CHECK (цена > 0),
количество_на_складе INT NOT NULL DEFAULT 0 CHECK (количество_на_складе >= 0),
id_отдела INT NOT NULL REFERENCES отделы(id_отдела)
);
CREATE TABLE продажи_товаров (
id_продажи SERIAL PRIMARY KEY,
id_товара INT NOT NULL REFERENCES товары(id_товара),
id_сотрудника INT NOT NULL REFERENCES сотрудники(id_сотрудника),
количество INT NOT NULL CHECK (количество > 0),
дата_продажи TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
сумма NUMERIC(10,2) NOT NULL CHECK (сумма > 0)
);
2. Тестовые данные (DML INSERT)
INSERT INTO должности (название, оклад) VALUES
('Кассир', 35000),
('Продавец-консультант', 38000),
('Менеджер отдела', 55000),
('Грузчик', 32000),
('Охранник', 40000);
INSERT INTO отделы (название, этаж) VALUES
('Бакалея', 1),
('Молочные продукты', 1),
('Электроника', 2),
('Одежда', 2),
('Касса', 1);
INSERT INTO сотрудники (фамилия, имя, id_отдела, id_должности, дата_найма) VALUES
('Иванов', 'Пётр', 1, 2, '2021-03-10'),
('Смирнова', 'Ольга', 2, 2, '2020-06-15'),
('Попов', 'Игорь', 3, 3, '2019-01-20'),
('Фёдорова', 'Наталья', 5, 1, '2022-09-01'),
('Морозов', 'Сергей', 4, 2, '2021-11-05');
INSERT INTO товары (название, цена, количество_на_складе, id_отдела) VALUES
('Хлеб пшеничный', 45.00, 100, 1),
('Молоко 3.2%', 89.00, 50, 2),
('Телевизор Samsung 43"', 35990.00, 5, 3),
('Куртка зимняя мужская', 5490.00, 20, 4),
('Сахар 1кг', 75.00, 200, 1);
INSERT INTO продажи_товаров (id_товара, id_сотрудника, количество, дата_продажи, сумма) VALUES
(1, 4, 2, '2024-01-15 10:30:00', 90.00),
(2, 4, 3, '2024-01-15 11:00:00', 267.00),
(3, 3, 1, '2024-01-16 14:00:00', 35990.00),
(4, 5, 2, '2024-01-17 12:30:00', 10980.00),
(5, 1, 5, '2024-01-18 09:15:00', 375.00);
3. SQL-запросы
-- 1. Простой запрос: товары стоимостью выше 100 рублей
SELECT название, цена, количество_на_складе
FROM товары WHERE цена > 100
ORDER BY цена DESC;
-- 2. Соединение: сотрудники с отделами и должностями
SELECT с.фамилия, с.имя, о.название AS отдел, д.название AS должность, д.оклад
FROM сотрудники с
JOIN отделы о ON с.id_отдела = о.id_отдела
JOIN должности д ON с.id_должности = д.id_должности;
-- 3. Группировка: выручка по отделам
SELECT о.название AS отдел, SUM(п.сумма) AS выручка, COUNT(п.id_продажи) AS кол_во_продаж
FROM продажи_товаров п
JOIN товары т ON п.id_товара = т.id_товара
JOIN отделы о ON т.id_отдела = о.id_отдела
GROUP BY о.название ORDER BY выручка DESC;
-- 4. Подзапрос: товары с остатком ниже среднего
SELECT название, количество_на_складе FROM товары
WHERE количество_на_складе < (SELECT AVG(количество_на_складе) FROM товары);
-- 5. Обновление: поднять цену хлеба на 5%
UPDATE товары SET цена = ROUND(цена * 1.05, 2)
WHERE название LIKE 'Хлеб%';
4. Руководство пользователя
## Руководство пользователя: БД «Информационная система супермаркета»
### Назначение
Система предназначена для учёта сотрудников, товаров и продаж в супермаркете.
### Основные операции
**Добавление товара:** INSERT в таблицу товары с указанием отдела (id_отдела) и цены.
**Регистрация продажи:** INSERT в продажи_товаров; рекомендуется также обновить остаток в товарах.
**Просмотр выручки:** использовать запрос с GROUP BY по отделам или сотрудникам.
**Изменение цены:** UPDATE товары SET цена = ... WHERE id_товара = ...
**Список сотрудников отдела:** SELECT с фильтром по id_отдела.
Билет №3: Информационная система военного округа
1. Создание таблиц (DDL)
-- Билет №3: Информационная система военного округа
CREATE TABLE места_дислокации (
id_места SERIAL PRIMARY KEY,
город VARCHAR(100) NOT NULL,
регион VARCHAR(100) NOT NULL,
адрес VARCHAR(200)
);
CREATE TABLE виды_войск (
id_вида SERIAL PRIMARY KEY,
наименование VARCHAR(100) NOT NULL UNIQUE,
описание TEXT
);
CREATE TABLE части (
id_части SERIAL PRIMARY KEY,
номер_части VARCHAR(50) NOT NULL UNIQUE,
id_вида INT NOT NULL REFERENCES виды_войск(id_вида),
id_места INT NOT NULL REFERENCES места_дислокации(id_места),
командир VARCHAR(100) NOT NULL
);
CREATE TABLE роты (
id_роты SERIAL PRIMARY KEY,
номер_роты INT NOT NULL CHECK (номер_роты > 0),
id_части INT NOT NULL REFERENCES части(id_части) ON DELETE CASCADE,
командир_роты VARCHAR(100) NOT NULL,
UNIQUE(номер_роты, id_части)
);
CREATE TABLE личный_состав (
id_военнослужащего SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
звание VARCHAR(50) NOT NULL,
специальность VARCHAR(100),
id_роты INT NOT NULL REFERENCES роты(id_роты) ON DELETE SET NULL
);
2. Тестовые данные (DML INSERT)
INSERT INTO места_дислокации (город, регион, адрес) VALUES
('Москва', 'Московская область', 'ул. Военная, 1'),
('Санкт-Петербург', 'Ленинградская область', 'пр. Победы, 10'),
('Ростов-на-Дону', 'Ростовская область', 'ул. Гарнизонная, 5'),
('Екатеринбург', 'Свердловская область', 'ул. Армейская, 3'),
('Новосибирск', 'Новосибирская область', 'пр. Суворова, 7');
INSERT INTO виды_войск (наименование, описание) VALUES
('Сухопутные войска', 'Основной вид войск'),
('ВКС', 'Воздушно-космические силы'),
('ВМФ', 'Военно-морской флот'),
('РВСН', 'Ракетные войска стратегического назначения'),
('Войска ПВО', 'Противовоздушная оборона');
INSERT INTO части (номер_части, id_вида, id_места, командир) VALUES
('в/ч 12345', 1, 1, 'полковник Смирнов А.В.'),
('в/ч 23456', 2, 2, 'генерал-майор Попов Д.С.'),
('в/ч 34567', 3, 3, 'капитан 1 ранга Фёдоров Н.И.'),
('в/ч 45678', 1, 4, 'подполковник Козлов В.О.'),
('в/ч 56789', 5, 5, 'полковник Новиков С.Л.');
INSERT INTO роты (номер_роты, id_части, командир_роты) VALUES
(1, 1, 'капитан Волков И.С.'),
(2, 1, 'старший лейтенант Лебедев К.А.'),
(1, 2, 'майор Соколов Р.Е.'),
(1, 4, 'капитан Морозов Т.В.'),
(2, 4, 'лейтенант Алексеев П.Н.');
INSERT INTO личный_состав (фамилия, имя, звание, специальность, id_роты) VALUES
('Иванов', 'Алексей', 'рядовой', 'стрелок', 1),
('Петров', 'Николай', 'сержант', 'механик-водитель', 1),
('Сидоров', 'Дмитрий', 'рядовой', 'связист', 2),
('Кузнецов', 'Виктор', 'ефрейтор', 'сапёр', 3),
('Фомин', 'Олег', 'рядовой', 'стрелок', 4);
3. SQL-запросы
-- 1. Простой запрос: военнослужащие со званием 'рядовой'
SELECT фамилия, имя, специальность FROM личный_состав
WHERE звание = 'рядовой';
-- 2. Соединение: личный состав с ротами и частями
SELECT лс.фамилия, лс.имя, лс.звание, р.номер_роты, ч.номер_части
FROM личный_состав лс
JOIN роты р ON лс.id_роты = р.id_роты
JOIN части ч ON р.id_части = ч.id_части;
-- 3. Группировка: количество личного состава по частям
SELECT ч.номер_части, COUNT(лс.id_военнослужащего) AS численность
FROM части ч
LEFT JOIN роты р ON ч.id_части = р.id_части
LEFT JOIN личный_состав лс ON р.id_роты = лс.id_роты
GROUP BY ч.номер_части ORDER BY численность DESC;
-- 4. Подзапрос: части дислоцированные в Москве
SELECT номер_части, командир FROM части
WHERE id_места = (SELECT id_места FROM места_дислокации WHERE город = 'Москва');
-- 5. Обновление: изменить специальность
UPDATE личный_состав SET специальность = 'снайпер'
WHERE фамилия = 'Иванов' AND имя = 'Алексей';
4. Руководство пользователя
## Руководство пользователя: БД «Информационная система военного округа»
### Назначение
БД предназначена для учёта структуры воинских подразделений: мест дислокации, видов войск, частей, рот и личного состава.
### Основные операции
**Добавление военнослужащего:** INSERT в личный_состав с указанием id роты, звания и специальности.
**Перевод в другую роту:** UPDATE личный_состав SET id_роты = ... WHERE id_военнослужащего = ...
**Просмотр состава роты:** SELECT с фильтром по id_роты.
**Добавление роты:** INSERT в роты с указанием номера роты и id_части.
Билет №4: Информационная система туристического агентства
1. Создание таблиц (DDL)
-- Билет №4: Информационная система туристического агентства
CREATE TABLE виды_жилья (
id_вида SERIAL PRIMARY KEY,
тип VARCHAR(50) NOT NULL UNIQUE,
описание TEXT
);
CREATE TABLE пансионаты (
id_пансионата SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
страна VARCHAR(100) NOT NULL,
город VARCHAR(100) NOT NULL,
звёздность INT CHECK (звёздность BETWEEN 1 AND 5)
);
CREATE TABLE туры (
id_тура SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
id_пансионата INT NOT NULL REFERENCES пансионаты(id_пансионата),
id_вида_жилья INT NOT NULL REFERENCES виды_жилья(id_вида),
цена NUMERIC(12,2) NOT NULL CHECK (цена > 0),
дата_начала DATE NOT NULL,
дата_окончания DATE NOT NULL,
CHECK (дата_окончания > дата_начала)
);
CREATE TABLE клиенты (
id_клиента SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20) UNIQUE,
email VARCHAR(100) UNIQUE
);
CREATE TABLE путевки (
id_путевки SERIAL PRIMARY KEY,
id_клиента INT NOT NULL REFERENCES клиенты(id_клиента),
id_тура INT NOT NULL REFERENCES туры(id_тура),
дата_продажи DATE NOT NULL DEFAULT CURRENT_DATE,
скидка NUMERIC(5,2) DEFAULT 0 CHECK (скидка BETWEEN 0 AND 100),
итоговая_сумма NUMERIC(12,2) NOT NULL
);
2. Тестовые данные (DML INSERT)
INSERT INTO виды_жилья (тип, описание) VALUES
('Стандарт', 'Стандартный номер'),
('Люкс', 'Номер повышенной комфортности'),
('Полулюкс', 'Улучшенный стандартный номер'),
('Апартаменты', 'Номер с кухней'),
('Бунгало', 'Отдельный домик');
INSERT INTO пансионаты (название, страна, город, звёздность) VALUES
('Grand Hotel Sochi', 'Россия', 'Сочи', 4),
('Antalya Resort', 'Турция', 'Анталья', 5),
('Cyprus Beach Club', 'Кипр', 'Лимасол', 4),
('Пансионат Лазурный берег', 'Россия', 'Геленджик', 3),
('Maldives Paradise', 'Мальдивы', 'Мале', 5);
INSERT INTO туры (название, id_пансионата, id_вида_жилья, цена, дата_начала, дата_окончания) VALUES
('Лето в Сочи', 1, 1, 45000, '2024-07-01', '2024-07-14'),
('Турецкий отдых', 2, 2, 120000, '2024-08-01', '2024-08-15'),
('Кипрское лето', 3, 3, 95000, '2024-07-15', '2024-07-29'),
('Выходные в Геленджике', 4, 1, 15000, '2024-06-08', '2024-06-10'),
('Мальдивы мечты', 5, 5, 350000, '2024-12-25', '2025-01-07');
INSERT INTO клиенты (фамилия, имя, телефон, email) VALUES
('Петрова', 'Мария', '+79001112233', 'petrova@mail.ru'),
('Сидоров', 'Антон', '+79112223344', 'sidorov@gmail.com'),
('Козлова', 'Юлия', '+79223334455', 'kozlova@yandex.ru'),
('Волков', 'Игорь', '+79334445566', 'volkov@mail.ru'),
('Новикова', 'Елена', '+79445556677', 'novikova@bk.ru');
INSERT INTO путевки (id_клиента, id_тура, дата_продажи, скидка, итоговая_сумма) VALUES
(1, 1, '2024-05-10', 5, 42750),
(2, 2, '2024-06-01', 0, 120000),
(3, 3, '2024-05-20', 10, 85500),
(4, 4, '2024-05-25', 0, 15000),
(5, 5, '2024-09-15', 3, 339500);
3. SQL-запросы
-- 1. Простой запрос: туры в Турцию
SELECT т.название, п.название AS пансионат, т.цена, т.дата_начала
FROM туры т JOIN пансионаты п ON т.id_пансионата = п.id_пансионата
WHERE п.страна = 'Турция';
-- 2. Соединение: путёвки с клиентами и турами
SELECT к.фамилия, к.имя, т.название AS тур, пу.итоговая_сумма, пу.дата_продажи
FROM путевки пу
JOIN клиенты к ON пу.id_клиента = к.id_клиента
JOIN туры т ON пу.id_тура = т.id_тура;
-- 3. Группировка: выручка по странам
SELECT п.страна, COUNT(пу.id_путевки) AS кол_путёвок, SUM(пу.итоговая_сумма) AS выручка
FROM путевки пу
JOIN туры т ON пу.id_тура = т.id_тура
JOIN пансионаты п ON т.id_пансионата = п.id_пансионата
GROUP BY п.страна ORDER BY выручка DESC;
-- 4. Подзапрос: клиенты купившие тур дороже 100000
SELECT фамилия, имя FROM клиенты
WHERE id_клиента IN (SELECT id_клиента FROM путевки WHERE итоговая_сумма > 100000);
-- 5. Обновление: дать дополнительную скидку 2% клиенту Петровой
UPDATE путевки SET скидка = скидка + 2,
итоговая_сумма = итоговая_сумма * 0.98
WHERE id_клиента = (SELECT id_клиента FROM клиенты WHERE фамилия = 'Петрова');
4. Руководство пользователя
## Руководство пользователя: БД «Туристическое агентство»
### Назначение
БД хранит информацию о пансионатах, турах, клиентах и продажах путёвок.
### Основные операции
**Добавление тура:** INSERT в туры с указанием пансионата, вида жилья, цены и дат.
**Продажа путёвки:** INSERT в путевки с id клиента, id тура и суммой (с учётом скидки).
**Поиск туров:** SELECT с фильтром по стране, цене или датам.
**Просмотр клиентов:** SELECT из клиенты с фильтром по условиям.
Билет №5: Информационная система автопредприятия
1. Создание таблиц (DDL)
-- Билет №5: Информационная система автопредприятия города
CREATE TABLE гаражное_хозяйство (
id_гаража SERIAL PRIMARY KEY,
адрес VARCHAR(200) NOT NULL,
вместимость INT NOT NULL CHECK (вместимость > 0),
телефон VARCHAR(20)
);
CREATE TABLE автотранспорт (
id_автомобиля SERIAL PRIMARY KEY,
марка VARCHAR(100) NOT NULL,
гос_номер VARCHAR(20) NOT NULL UNIQUE,
год_выпуска INT NOT NULL CHECK (год_выпуска > 1990),
тип VARCHAR(50) NOT NULL,
id_гаража INT NOT NULL REFERENCES гаражное_хозяйство(id_гаража)
);
CREATE TABLE маршруты (
id_маршрута SERIAL PRIMARY KEY,
номер VARCHAR(20) NOT NULL UNIQUE,
начальная_точка VARCHAR(100) NOT NULL,
конечная_точка VARCHAR(100) NOT NULL,
протяжённость_км NUMERIC(8,2) CHECK (протяжённость_км > 0)
);
CREATE TABLE водители (
id_водителя SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
категория_прав VARCHAR(10) NOT NULL,
id_автомобиля INT REFERENCES автотранспорт(id_автомобиля),
id_маршрута INT REFERENCES маршруты(id_маршрута)
);
CREATE TABLE обслуживающий_персонал (
id_сотрудника SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
должность VARCHAR(100) NOT NULL,
id_гаража INT NOT NULL REFERENCES гаражное_хозяйство(id_гаража),
телефон VARCHAR(20)
);
2. Тестовые данные (DML INSERT)
INSERT INTO гаражное_хозяйство (адрес, вместимость, телефон) VALUES
('ул. Гаражная, 1', 50, '8(800)111-22-33'),
('пр. Транспортный, 5', 30, '8(800)222-33-44'),
('ул. Механизаторов, 10', 40, '8(800)333-44-55'),
('ул. Базовая, 3', 20, '8(800)444-55-66'),
('пр. Автобусный, 8', 60, '8(800)555-66-77');
INSERT INTO автотранспорт (марка, гос_номер, год_выпуска, тип, id_гаража) VALUES
('ЛиАЗ 5292', 'А123БВ77', 2018, 'автобус', 1),
('МАЗ 203', 'Б456ГД77', 2019, 'автобус', 1),
('ПАЗ 3205', 'В789ЕЖ77', 2017, 'микроавтобус', 2),
('Газель Next', 'Г012ЗИ77', 2021, 'маршрутное такси', 3),
('Volvo 9700', 'Д345КЛ77', 2020, 'автобус', 2);
INSERT INTO маршруты (номер, начальная_точка, конечная_точка, протяжённость_км) VALUES
('М-1', 'Вокзал', 'Аэропорт', 25.5),
('М-2', 'Центр', 'Северный район', 12.3),
('М-3', 'Площадь', 'Промзона', 18.7),
('М-4', 'Рынок', 'Больница', 8.4),
('М-5', 'Депо', 'Парк', 15.0);
INSERT INTO водители (фамилия, имя, категория_прав, id_автомобиля, id_маршрута) VALUES
('Громов', 'Василий', 'D', 1, 1),
('Тихонов', 'Максим', 'D', 2, 2),
('Орлов', 'Андрей', 'C', 3, 3),
('Беляев', 'Руслан', 'B', 4, 4),
('Ефимов', 'Юрий', 'D', 5, 5);
INSERT INTO обслуживающий_персонал (фамилия, имя, должность, id_гаража, телефон) VALUES
('Захаров', 'Михаил', 'Механик', 1, '+79001234567'),
('Лазарев', 'Олег', 'Слесарь', 1, '+79112345678'),
('Макаров', 'Стёпан', 'Диспетчер', 2, '+79223456789'),
('Никитин', 'Борис', 'Механик', 3, '+79334567890'),
('Осипов', 'Денис', 'Мойщик', 2, '+79445678901');
3. SQL-запросы
-- 1. Простой запрос: автобусы выпуска после 2018 года
SELECT марка, гос_номер, год_выпуска FROM автотранспорт
WHERE тип = 'автобус' AND год_выпуска > 2018;
-- 2. Соединение: водители с маршрутами и автомобилями
SELECT в.фамилия, в.имя, а.марка, а.гос_номер, м.номер AS маршрут
FROM водители в
JOIN автотранспорт а ON в.id_автомобиля = а.id_автомобиля
JOIN маршруты м ON в.id_маршрута = м.id_маршрута;
-- 3. Группировка: количество транспорта по гаражам
SELECT г.адрес, COUNT(а.id_автомобиля) AS кол_во_авто
FROM гаражное_хозяйство г
LEFT JOIN автотранспорт а ON г.id_гаража = а.id_гаража
GROUP BY г.адрес ORDER BY кол_во_авто DESC;
-- 4. Подзапрос: водители на маршрутах длиннее средней протяжённости
SELECT фамилия, имя FROM водители
WHERE id_маршрута IN (
SELECT id_маршрута FROM маршруты
WHERE протяжённость_км > (SELECT AVG(протяжённость_км) FROM маршруты)
);
-- 5. Обновление: изменить маршрут водителю Орлову
UPDATE водители SET id_маршрута = 1
WHERE фамилия = 'Орлов';
4. Руководство пользователя
## Руководство пользователя: БД «Автопредприятие»
### Назначение
БД предназначена для учёта транспорта, водителей, маршрутов, гаражей и обслуживающего персонала.
### Основные операции
**Назначить водителя на маршрут:** UPDATE водители SET id_маршрута = ... WHERE id_водителя = ...
**Добавить новый автобус:** INSERT в автотранспорт с id гаража.
**Просмотр транспорта в гараже:** SELECT с фильтром по id_гаража.
**Список водителей на маршруте:** SELECT с JOIN маршруты и фильтром по номеру маршрута.
Билет №6: Информационная система поликлиники
1. Создание таблиц (DDL)
-- Билет №6: Информационная система поликлиники
CREATE TABLE отделения (
id_отделения SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL UNIQUE,
этаж INT NOT NULL CHECK (этаж >= 1),
телефон VARCHAR(20)
);
CREATE TABLE врачи (
id_врача SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
специализация VARCHAR(100) NOT NULL,
id_отделения INT NOT NULL REFERENCES отделения(id_отделения)
);
CREATE TABLE пациенты (
id_пациента SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
дата_рождения DATE NOT NULL,
адрес VARCHAR(200),
телефон VARCHAR(20)
);
CREATE TABLE история_болезней (
id_записи SERIAL PRIMARY KEY,
id_пациента INT NOT NULL REFERENCES пациенты(id_пациента),
id_врача INT NOT NULL REFERENCES врачи(id_врача),
диагноз VARCHAR(200) NOT NULL,
дата_обращения DATE NOT NULL DEFAULT CURRENT_DATE,
лечение TEXT,
закрыта BOOLEAN DEFAULT FALSE
);
CREATE TABLE обслуживающий_персонал (
id_сотрудника SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
должность VARCHAR(100) NOT NULL,
id_отделения INT NOT NULL REFERENCES отделения(id_отделения)
);
2. Тестовые данные (DML INSERT)
INSERT INTO отделения (название, этаж, телефон) VALUES
('Терапевтическое', 1, '8(800)100-11-11'),
('Хирургическое', 2, '8(800)100-22-22'),
('Кардиологическое', 3, '8(800)100-33-33'),
('Неврологическое', 2, '8(800)100-44-44'),
('Педиатрическое', 1, '8(800)100-55-55');
INSERT INTO врачи (фамилия, имя, специализация, id_отделения) VALUES
('Романов', 'Сергей', 'Терапевт', 1),
('Шевцова', 'Ирина', 'Хирург', 2),
('Богданов', 'Павел', 'Кардиолог', 3),
('Дмитриева', 'Светлана', 'Невролог', 4),
('Соловьёв', 'Кирилл', 'Педиатр', 5);
INSERT INTO пациенты (фамилия, имя, дата_рождения, адрес, телефон) VALUES
('Артёмов', 'Вадим', '1985-04-12', 'ул. Ленина, 5-10', '+79001111111'),
('Захарова', 'Людмила', '1990-08-23', 'пр. Мира, 12-5', '+79002222222'),
('Тимофеев', 'Глеб', '1975-11-30', 'ул. Советская, 3-2', '+79003333333'),
('Власова', 'Инна', '2000-01-15', 'ул. Гагарина, 7-1', '+79004444444'),
('Крылов', 'Иван', '1968-06-07', 'пр. Победы, 20-8', '+79005555555');
INSERT INTO история_болезней (id_пациента, id_врача, диагноз, дата_обращения, лечение) VALUES
(1, 1, 'ОРВИ', '2024-01-10', 'Постельный режим, парацетамол'),
(2, 3, 'Гипертония', '2024-01-12', 'Лизиноприл 5мг'),
(3, 2, 'Аппендицит', '2024-01-15', 'Операция'),
(4, 4, 'Мигрень', '2024-01-18', 'Суматриптан'),
(5, 1, 'Бронхит', '2024-01-20', 'Антибиотики 7 дней');
INSERT INTO обслуживающий_персонал (фамилия, имя, должность, id_отделения) VALUES
('Фёдорова', 'Галина', 'Медсестра', 1),
('Гусев', 'Антон', 'Санитар', 2),
('Никонова', 'Тамара', 'Медсестра', 3),
('Ларин', 'Владимир', 'Регистратор', 1),
('Щербакова', 'Нина', 'Медсестра', 5);
3. SQL-запросы
-- 1. Простой запрос: открытые случаи болезни
SELECT п.фамилия, п.имя, h.диагноз, h.дата_обращения
FROM история_болезней h
JOIN пациенты п ON h.id_пациента = п.id_пациента
WHERE h.закрыта = FALSE;
-- 2. Соединение: история болезней с врачами и пациентами
SELECT п.фамилия AS пациент, в.фамилия AS врач, в.специализация, h.диагноз
FROM история_болезней h
JOIN пациенты п ON h.id_пациента = п.id_пациента
JOIN врачи в ON h.id_врача = в.id_врача;
-- 3. Группировка: количество обращений к каждому врачу
SELECT в.фамилия, в.специализация, COUNT(h.id_записи) AS кол_обращений
FROM врачи в
LEFT JOIN история_болезней h ON в.id_врача = h.id_врача
GROUP BY в.фамилия, в.специализация ORDER BY кол_обращений DESC;
-- 4. Подзапрос: пациенты обратившиеся к терапевту
SELECT фамилия, имя FROM пациенты
WHERE id_пациента IN (
SELECT id_пациента FROM история_болезней
WHERE id_врача IN (SELECT id_врача FROM врачи WHERE специализация = 'Терапевт')
);
-- 5. Обновление: закрыть случай ОРВИ
UPDATE история_болезней SET закрыта = TRUE
WHERE диагноз = 'ОРВИ';
4. Руководство пользователя
## Руководство пользователя: БД «Поликлиника»
### Назначение
БД обеспечивает учёт врачей, пациентов, историй болезней и персонала поликлиники.
### Основные операции
**Регистрация пациента:** INSERT в пациенты.
**Запись обращения:** INSERT в история_болезней с id_пациента, id_врача, диагнозом.
**Закрытие случая:** UPDATE история_болезней SET закрыта = TRUE WHERE ...
**Просмотр истории пациента:** SELECT с фильтром по id_пациента.
Билет №7: Информационная система больницы
1. Создание таблиц (DDL)
-- Билет №7: Информационная система больницы
CREATE TABLE врачи (
id_врача SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
специализация VARCHAR(100) NOT NULL,
степень VARCHAR(50)
);
CREATE TABLE пациенты (
id_пациента SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
дата_рождения DATE NOT NULL,
полис VARCHAR(20) UNIQUE,
палата INT CHECK (палата > 0)
);
CREATE TABLE история_болезней (
id_истории SERIAL PRIMARY KEY,
id_пациента INT NOT NULL REFERENCES пациенты(id_пациента),
id_врача INT NOT NULL REFERENCES врачи(id_врача),
диагноз VARCHAR(200) NOT NULL,
дата_поступления DATE NOT NULL,
дата_выписки DATE,
CHECK (дата_выписки IS NULL OR дата_выписки >= дата_поступления)
);
CREATE TABLE операции (
id_операции SERIAL PRIMARY KEY,
id_истории INT NOT NULL REFERENCES история_болезней(id_истории),
id_хирурга INT NOT NULL REFERENCES врачи(id_врача),
название VARCHAR(150) NOT NULL,
дата_операции DATE NOT NULL,
продолжительность_мин INT CHECK (продолжительность_мин > 0),
результат VARCHAR(50) DEFAULT 'Успешно'
);
CREATE TABLE лист_лечения (
id_назначения SERIAL PRIMARY KEY,
id_истории INT NOT NULL REFERENCES история_болезней(id_истории),
препарат VARCHAR(100) NOT NULL,
дозировка VARCHAR(50) NOT NULL,
дата_назначения DATE NOT NULL DEFAULT CURRENT_DATE,
кол_во_дней INT NOT NULL CHECK (кол_во_дней > 0)
);
2. Тестовые данные (DML INSERT)
INSERT INTO врачи (фамилия, имя, специализация, степень) VALUES
('Краснов', 'Андрей', 'Хирург', 'Д.м.н.'),
('Зайцева', 'Марина', 'Терапевт', 'К.м.н.'),
('Белов', 'Николай', 'Анестезиолог', NULL),
('Горбунова', 'Татьяна', 'Кардиолог', 'К.м.н.'),
('Мухин', 'Роман', 'Хирург', NULL);
INSERT INTO пациенты (фамилия, имя, дата_рождения, полис, палата) VALUES
('Грибов', 'Евгений', '1972-05-14', '0011223344', 201),
('Симонова', 'Валентина', '1961-09-28', '1122334455', 305),
('Яковлев', 'Кирилл', '1990-03-07', '2233445566', 201),
('Потапова', 'Зинаида', '1955-12-19', '3344556677', 102),
('Лысов', 'Семён', '1983-07-03', '4455667788', 410);
INSERT INTO история_болезней (id_пациента, id_врача, диагноз, дата_поступления) VALUES
(1, 1, 'Острый аппендицит', '2024-01-10'),
(2, 4, 'Инфаркт миокарда', '2024-01-12'),
(3, 2, 'Пневмония', '2024-01-15'),
(4, 2, 'Диабет 2 типа', '2024-01-18'),
(5, 1, 'Грыжа', '2024-01-20');
INSERT INTO операции (id_истории, id_хирурга, название, дата_операции, продолжительность_мин) VALUES
(1, 1, 'Аппендэктомия', '2024-01-10', 45),
(5, 5, 'Герниопластика', '2024-01-21', 90),
(2, 1, 'Стентирование коронарных артерий', '2024-01-13', 120),
(1, 3, 'Анестезия при аппендэктомии', '2024-01-10', 45),
(5, 3, 'Анестезия при герниопластике', '2024-01-21', 90);
INSERT INTO лист_лечения (id_истории, препарат, дозировка, дата_назначения, кол_во_дней) VALUES
(1, 'Цефтриаксон', '1г в/в', '2024-01-10', 7),
(2, 'Гепарин', '5000 ед п/к', '2024-01-12', 10),
(3, 'Амоксициллин', '500мг 3р/д', '2024-01-15', 10),
(4, 'Метформин', '500мг 2р/д', '2024-01-18', 30),
(5, 'Ибупрофен', '400мг 3р/д', '2024-01-20', 5);
3. SQL-запросы
-- 1. Простой запрос: пациенты в палате 201
SELECT фамилия, имя, полис FROM пациенты WHERE палата = 201;
-- 2. Соединение: операции с хирургами и пациентами
SELECT п.фамилия AS пациент, в.фамилия AS хирург, о.название, о.дата_операции
FROM операции о
JOIN история_болезней h ON о.id_истории = h.id_истории
JOIN пациенты п ON h.id_пациента = п.id_пациента
JOIN врачи в ON о.id_хирурга = в.id_врача;
-- 3. Группировка: среднее время операции по хирургам
SELECT в.фамилия, COUNT(о.id_операции) AS кол_во_операций,
ROUND(AVG(о.продолжительность_мин), 0) AS средн_мин
FROM операции о
JOIN врачи в ON о.id_хирурга = в.id_врача
GROUP BY в.фамилия ORDER BY кол_во_операций DESC;
-- 4. Подзапрос: пациенты у кардиолога
SELECT фамилия, имя FROM пациенты
WHERE id_пациента IN (
SELECT id_пациента FROM история_болезней
WHERE id_врача IN (SELECT id_врача FROM врачи WHERE специализация = 'Кардиолог')
);
-- 5. Обновление: поставить дату выписки
UPDATE история_болезней SET дата_выписки = '2024-01-20'
WHERE id_пациента = 1 AND дата_выписки IS NULL;
4. Руководство пользователя
## Руководство пользователя: БД «Больница»
### Назначение
БД предназначена для учёта госпитализаций, операций и лечения пациентов больницы.
### Основные операции
**Поступление пациента:** INSERT в история_болезней с датой поступления.
**Назначение препарата:** INSERT в лист_лечения с id_истории.
**Регистрация операции:** INSERT в операции с id хирурга.
**Выписка:** UPDATE история_болезней SET дата_выписки = ... WHERE id_истории = ...
Билет №8: Информационная система библиотек города
1. Создание таблиц (DDL)
-- Билет №8: Информационная система библиотек города
CREATE TABLE библиотеки (
id_библиотеки SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
адрес VARCHAR(200) NOT NULL,
телефон VARCHAR(20)
);
CREATE TABLE читальные_залы (
id_зала SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL,
id_библиотеки INT NOT NULL REFERENCES библиотеки(id_библиотеки),
вместимость INT NOT NULL CHECK (вместимость > 0)
);
CREATE TABLE литература (
id_книги SERIAL PRIMARY KEY,
название VARCHAR(200) NOT NULL,
автор VARCHAR(150) NOT NULL,
год_издания INT CHECK (год_издания BETWEEN 1400 AND 2100),
жанр VARCHAR(80),
кол_во_экземпляров INT NOT NULL DEFAULT 1 CHECK (кол_во_экземпляров >= 0),
id_библиотеки INT NOT NULL REFERENCES библиотеки(id_библиотеки)
);
CREATE TABLE читатели (
id_читателя SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
номер_билета VARCHAR(20) NOT NULL UNIQUE,
телефон VARCHAR(20)
);
CREATE TABLE выдача_литературы (
id_выдачи SERIAL PRIMARY KEY,
id_читателя INT NOT NULL REFERENCES читатели(id_читателя),
id_книги INT NOT NULL REFERENCES литература(id_книги),
дата_выдачи DATE NOT NULL DEFAULT CURRENT_DATE,
дата_возврата DATE,
фактический_возврат DATE
);
2. Тестовые данные (DML INSERT)
INSERT INTO библиотеки (название, адрес, телефон) VALUES
('ЦГБ им. Пушкина', 'ул. Центральная, 1', '8(800)200-10-10'),
('Библиотека №2', 'пр. Ленина, 45', '8(800)200-20-20'),
('Детская библиотека', 'ул. Юности, 12', '8(800)200-30-30'),
('Техническая библиотека', 'пр. Труда, 7', '8(800)200-40-40'),
('Библиотека ВУЗа', 'ул. Студенческая, 3', '8(800)200-50-50');
INSERT INTO читальные_залы (название, id_библиотеки, вместимость) VALUES
('Зал классики', 1, 50),
('Зал периодики', 1, 30),
('Зал художественной литературы', 2, 40),
('Зал науки', 4, 25),
('Детский зал', 3, 20);
INSERT INTO литература (название, автор, год_издания, жанр, кол_во_экземпляров, id_библиотеки) VALUES
('Война и мир', 'Толстой Л.Н.', 1869, 'Роман', 5, 1),
('Преступление и наказание', 'Достоевский Ф.М.', 1866, 'Роман', 3, 1),
('Мастер и Маргарита', 'Булгаков М.А.', 1967, 'Роман', 4, 2),
('Python для начинающих', 'Лутц М.', 2019, 'Учебник', 2, 4),
('Гарри Поттер', 'Роулинг Дж.', 1997, 'Фэнтези', 6, 3);
INSERT INTO читатели (фамилия, имя, номер_билета, телефон) VALUES
('Ковалёв', 'Антон', 'Б-0001', '+79009991111'),
('Степанова', 'Лариса', 'Б-0002', '+79009992222'),
('Воронов', 'Денис', 'Б-0003', '+79009993333'),
('Исаева', 'Ксения', 'Б-0004', '+79009994444'),
('Тарасов', 'Олег', 'Б-0005', '+79009995555');
INSERT INTO выдача_литературы (id_читателя, id_книги, дата_выдачи, дата_возврата) VALUES
(1, 1, '2024-01-05', '2024-01-19'),
(2, 3, '2024-01-07', '2024-01-21'),
(3, 4, '2024-01-10', '2024-01-24'),
(4, 5, '2024-01-12', '2024-01-26'),
(5, 2, '2024-01-15', '2024-01-29');
3. SQL-запросы
-- 1. Простой запрос: книги жанра 'Роман'
SELECT название, автор, год_издания, кол_во_экземпляров
FROM литература WHERE жанр = 'Роман' ORDER BY год_издания;
-- 2. Соединение: выдачи с читателями и книгами
SELECT ч.фамилия, ч.имя, л.название AS книга, в.дата_выдачи, в.дата_возврата
FROM выдача_литературы в
JOIN читатели ч ON в.id_читателя = ч.id_читателя
JOIN литература л ON в.id_книги = л.id_книги;
-- 3. Группировка: количество выданных книг по библиотекам
SELECT б.название AS библиотека, COUNT(в.id_выдачи) AS кол_выдач
FROM библиотеки б
LEFT JOIN литература л ON б.id_библиотеки = л.id_библиотеки
LEFT JOIN выдача_литературы в ON л.id_книги = в.id_книги
GROUP BY б.название ORDER BY кол_выдач DESC;
-- 4. Подзапрос: читатели у которых есть просроченные книги
SELECT фамилия, имя FROM читатели
WHERE id_читателя IN (
SELECT id_читателя FROM выдача_литературы
WHERE дата_возврата < CURRENT_DATE AND фактический_возврат IS NULL
);
-- 5. Обновление: зафиксировать фактический возврат книги
UPDATE выдача_литературы SET фактический_возврат = CURRENT_DATE
WHERE id_читателя = 1 AND id_книги = 1;
4. Руководство пользователя
## Руководство пользователя: БД «Библиотеки города»
### Назначение
БД учитывает фонд библиотек, читателей и выдачу книг.
### Основные операции
**Выдача книги:** INSERT в выдача_литературы; уменьшить кол_во_экземпляров в литература.
**Возврат книги:** UPDATE выдача_литературы SET фактический_возврат = ... ; увеличить кол_во_экземпляров.
**Поиск книги:** SELECT с фильтром по жанру, автору или названию.
**Просроченные выдачи:** SELECT WHERE дата_возврата < CURRENT_DATE AND фактический_возврат IS NULL.
Билет №9: Успеваемость студентов кафедры
1. Создание таблиц (DDL)
-- Билет №9: Успеваемость студентов кафедры
CREATE TABLE кафедры (
id_кафедры SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL UNIQUE,
заведующий VARCHAR(100) NOT NULL
);
CREATE TABLE дисциплины (
id_дисциплины SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL,
id_кафедры INT NOT NULL REFERENCES кафедры(id_кафедры),
часов INT NOT NULL CHECK (часов > 0)
);
CREATE TABLE преподаватели (
id_преподавателя SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
степень VARCHAR(50),
id_кафедры INT NOT NULL REFERENCES кафедры(id_кафедры)
);
CREATE TABLE студенты (
id_студента SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
группа VARCHAR(20) NOT NULL,
курс INT NOT NULL CHECK (курс BETWEEN 1 AND 6)
);
CREATE TABLE ведомости_успеваемости (
id_ведомости SERIAL PRIMARY KEY,
id_студента INT NOT NULL REFERENCES студенты(id_студента),
id_дисциплины INT NOT NULL REFERENCES дисциплины(id_дисциплины),
id_преподавателя INT NOT NULL REFERENCES преподаватели(id_преподавателя),
оценка INT CHECK (оценка BETWEEN 2 AND 5),
дата DATE NOT NULL DEFAULT CURRENT_DATE
);
2. Тестовые данные (DML INSERT)
INSERT INTO кафедры (название, заведующий) VALUES
('Информационные технологии', 'Профессор Александров К.С.'),
('Математика', 'Доцент Баранова Е.В.'),
('Физика', 'Профессор Власов Н.А.'),
('Экономика', 'Доцент Герасимова И.П.'),
('Иностранные языки', 'Доцент Демидов Т.О.');
INSERT INTO дисциплины (название, id_кафедры, часов) VALUES
('Базы данных', 1, 72),
('Алгоритмы', 1, 54),
('Высшая математика', 2, 108),
('Физика', 3, 90),
('Английский язык', 5, 64);
INSERT INTO преподаватели (фамилия, имя, степень, id_кафедры) VALUES
('Ефремов', 'Станислав', 'К.т.н.', 1),
('Жукова', 'Анастасия', 'К.ф.-м.н.', 2),
('Зверев', 'Борис', NULL, 3),
('Иванов', 'Пётр', 'К.э.н.', 4),
('Карпова', 'Виктория', NULL, 5);
INSERT INTO студенты (фамилия, имя, группа, курс) VALUES
('Лобанов', 'Евгений', 'ИС-11', 1),
('Малинина', 'Снежана', 'ИС-11', 1),
('Некрасов', 'Тимур', 'ИС-21', 2),
('Осипова', 'Диана', 'ИС-21', 2),
('Павлов', 'Арсений', 'ИС-31', 3);
INSERT INTO ведомости_успеваемости (id_студента, id_дисциплины, id_преподавателя, оценка, дата) VALUES
(1, 1, 1, 5, '2024-01-20'),
(2, 1, 1, 4, '2024-01-20'),
(3, 2, 1, 3, '2024-01-22'),
(4, 3, 2, 4, '2024-01-25'),
(5, 3, 2, 5, '2024-01-25');
3. SQL-запросы
-- 1. Простой запрос: студенты 2-го курса
SELECT фамилия, имя, группа FROM студенты WHERE курс = 2;
-- 2. Соединение: оценки студентов с дисциплинами и преподавателями
SELECT с.фамилия, с.имя, д.название AS дисциплина, п.фамилия AS преподаватель, в.оценка
FROM ведомости_успеваемости в
JOIN студенты с ON в.id_студента = с.id_студента
JOIN дисциплины д ON в.id_дисциплины = д.id_дисциплины
JOIN преподаватели п ON в.id_преподавателя = п.id_преподавателя;
-- 3. Группировка: средняя оценка по каждой дисциплине
SELECT д.название, ROUND(AVG(в.оценка), 2) AS средняя_оценка
FROM ведомости_успеваемости в
JOIN дисциплины д ON в.id_дисциплины = д.id_дисциплины
GROUP BY д.название ORDER BY средняя_оценка DESC;
-- 4. Подзапрос: студенты получившие хотя бы одну двойку
SELECT фамилия, имя FROM студенты
WHERE id_студента IN (
SELECT id_студента FROM ведомости_успеваемости WHERE оценка = 2
);
-- 5. Обновление: пересдача — изменить оценку 3 на 4
UPDATE ведомости_успеваемости SET оценка = 4
WHERE id_студента = 3 AND id_дисциплины = 2 AND оценка = 3;
4. Руководство пользователя
## Руководство пользователя: БД «Успеваемость студентов кафедры»
### Назначение
БД предназначена для учёта успеваемости студентов по дисциплинам кафедр.
### Основные операции
**Добавление оценки:** INSERT в ведомости_успеваемости.
**Просмотр успеваемости студента:** SELECT с JOIN и фильтром по id_студента.
**Средняя оценка по дисциплине:** GROUP BY + AVG.
**Пересдача (изменение оценки):** UPDATE с условием по студенту и дисциплине.
Билет №10: Интернет-магазин электроники
1. Создание таблиц (DDL)
-- Билет №10: Интернет-магазин электроники
CREATE TABLE категории_товаров (
id_категории SERIAL PRIMARY KEY,
название VARCHAR(100) NOT NULL UNIQUE,
описание TEXT
);
CREATE TABLE товары (
id_товара SERIAL PRIMARY KEY,
название VARCHAR(200) NOT NULL,
id_категории INT NOT NULL REFERENCES категории_товаров(id_категории),
цена NUMERIC(12,2) NOT NULL CHECK (цена > 0),
остаток INT NOT NULL DEFAULT 0 CHECK (остаток >= 0),
производитель VARCHAR(100)
);
CREATE TABLE покупатели (
id_покупателя SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
телефон VARCHAR(20),
дата_регистрации DATE NOT NULL DEFAULT CURRENT_DATE
);
CREATE TABLE заказы (
id_заказа SERIAL PRIMARY KEY,
id_покупателя INT NOT NULL REFERENCES покупатели(id_покупателя),
дата_заказа TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
статус VARCHAR(30) NOT NULL DEFAULT 'Новый',
адрес_доставки VARCHAR(200) NOT NULL,
сумма_заказа NUMERIC(12,2) NOT NULL CHECK (сумма_заказа >= 0)
);
CREATE TABLE составы_заказов (
id_строки SERIAL PRIMARY KEY,
id_заказа INT NOT NULL REFERENCES заказы(id_заказа) ON DELETE CASCADE,
id_товара INT NOT NULL REFERENCES товары(id_товара),
количество INT NOT NULL CHECK (количество > 0),
цена_за_ед NUMERIC(12,2) NOT NULL CHECK (цена_за_ед > 0)
);
2. Тестовые данные (DML INSERT)
INSERT INTO категории_товаров (название, описание) VALUES
('Смартфоны', 'Мобильные телефоны'),
('Ноутбуки', 'Портативные компьютеры'),
('Телевизоры', 'Телевизионные приёмники'),
('Наушники', 'Аудио-устройства'),
('Планшеты', 'Планшетные компьютеры');
INSERT INTO товары (название, id_категории, цена, остаток, производитель) VALUES
('iPhone 15 Pro', 1, 119990, 20, 'Apple'),
('Samsung Galaxy S24', 1, 89990, 15, 'Samsung'),
('MacBook Air M2', 2, 149990, 10, 'Apple'),
('Sony WH-1000XM5', 4, 29990, 30, 'Sony'),
('Samsung 4K 55"', 3, 59990, 8, 'Samsung');
INSERT INTO покупатели (фамилия, имя, email, телефон) VALUES
('Рыбаков', 'Игорь', 'rybakov@mail.ru', '+79001234567'),
('Семёнова', 'Виктория', 'semenova@yandex.ru', '+79112345678'),
('Тихомиров', 'Геннадий', 'tikhomirov@gmail.com', '+79223456789'),
('Уварова', 'Полина', 'uvarova@mail.ru', '+79334567890'),
('Федотов', 'Артём', 'fedotov@yandex.ru', '+79445678901');
INSERT INTO заказы (id_покупателя, дата_заказа, статус, адрес_доставки, сумма_заказа) VALUES
(1, '2024-01-10 10:00:00', 'Доставлен', 'ул. Рыбная, 5-3', 119990),
(2, '2024-01-11 14:30:00', 'В пути', 'пр. Семёнова, 12-7', 29990),
(3, '2024-01-12 09:15:00', 'Новый', 'ул. Тихая, 3-1', 149990),
(4, '2024-01-13 17:00:00', 'Доставлен', 'ул. Полевая, 8-4', 89990),
(5, '2024-01-14 12:00:00', 'Отменён', 'пр. Федеральный, 20-2', 59990);
INSERT INTO составы_заказов (id_заказа, id_товара, количество, цена_за_ед) VALUES
(1, 1, 1, 119990),
(2, 4, 1, 29990),
(3, 3, 1, 149990),
(4, 2, 1, 89990),
(5, 5, 1, 59990);
3. SQL-запросы
-- 1. Простой запрос: товары с остатком менее 15 штук
SELECT название, цена, остаток, производитель
FROM товары WHERE остаток < 15 ORDER BY остаток;
-- 2. Соединение: заказы с покупателями и товарами
SELECT п.фамилия, п.имя, т.название AS товар, з.статус, з.сумма_заказа
FROM заказы з
JOIN покупатели п ON з.id_покупателя = п.id_покупателя
JOIN составы_заказов со ON з.id_заказа = со.id_заказа
JOIN товары т ON со.id_товара = т.id_товара;
-- 3. Группировка: выручка по категориям
SELECT к.название AS категория, SUM(со.количество * со.цена_за_ед) AS выручка
FROM составы_заказов со
JOIN товары т ON со.id_товара = т.id_товара
JOIN категории_товаров к ON т.id_категории = к.id_категории
JOIN заказы з ON со.id_заказа = з.id_заказа
WHERE з.статус != 'Отменён'
GROUP BY к.название ORDER BY выручка DESC;
-- 4. Подзапрос: покупатели с суммой заказов выше 100000
SELECT фамилия, имя FROM покупатели
WHERE id_покупателя IN (
SELECT id_покупателя FROM заказы WHERE сумма_заказа > 100000
);
-- 5. Обновление статуса заказа
UPDATE заказы SET статус = 'Доставлен'
WHERE id_заказа = 2;
4. Руководство пользователя
## Руководство пользователя: БД «Интернет-магазин электроники»
### Назначение
БД обеспечивает учёт товаров, покупателей, заказов и составов заказов.
### Основные операции
**Оформление заказа:** INSERT в заказы, затем INSERT в составы_заказов.
**Изменение статуса:** UPDATE заказы SET статус = ... WHERE id_заказа = ...
**Поиск товара:** SELECT с фильтром по категории, производителю или цене.
**История покупателя:** SELECT заказы WHERE id_покупателя = ...
Билет №11: Поликлиника и расписание приёмов
1. Создание таблиц (DDL)
-- Билет №11: Поликлиника и расписание приёмов
-- Таблицы: отделения, врачи, пациенты, талоны на прием, медицинские заключения
-- Таблица 1: отделения
CREATE TABLE IF NOT EXISTS отделения (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: врачи
CREATE TABLE IF NOT EXISTS врачи (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_отделения INT REFERENCES отделения(id),
дополнение VARCHAR(200)
);
-- Таблица 3: пациенты
CREATE TABLE IF NOT EXISTS пациенты (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: талоны на прием
CREATE TABLE IF NOT EXISTS талоны_на_прием (
id SERIAL PRIMARY KEY,
id_пациенты INT NOT NULL REFERENCES пациенты(id),
id_врачи INT NOT NULL REFERENCES врачи(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: медицинские заключения
CREATE TABLE IF NOT EXISTS медицинские_заключения (
id SERIAL PRIMARY KEY,
id_талоны_на_прием INT NOT NULL REFERENCES талоны_на_прием(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №11: Поликлиника и расписание приёмов
INSERT INTO отделения (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO врачи (наименование, id_отделения, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO пациенты (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO талоны_на_прием (id_пациенты, id_врачи, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO талоны_на_прием (id_талоны_на_прием, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №11: Поликлиника и расписание приёмов
-- 1. Простой запрос с условием: активные записи
SELECT * FROM талоны_на_прием WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM талоны_на_прием t4
JOIN пациенты t3 ON t4.id_пациенты = t3.id
JOIN врачи t2 ON t4.id_врачи = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM талоны_на_прием
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM талоны_на_прием
WHERE сумма > (SELECT AVG(сумма) FROM талоны_на_прием);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE талоны_на_прием SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Поликлиника и расписание приёмов»
### Назначение
База данных предназначена для автоматизации учёта в системе «Поликлиника и расписание приёмов». Хранит информацию в 5 взаимосвязанных таблицах: отделения, врачи, пациенты, талоны на прием, медицинские заключения.
### Структура базы данных
Таблица «отделения» — основной справочник. Таблица «врачи» связана с первой по внешнему ключу. Таблица «пациенты» хранит данные об участниках (клиентах/сотрудниках). Таблица «талоны на прием» фиксирует транзакции/события. Таблица «медицинские заключения» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (отделения, врачи), затем добавляйте данные в пациенты и талоны на прием.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE талоны_на_прием SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (медицинские заключения, талоны на прием), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №12: Бронирование номеров в отеле
1. Создание таблиц (DDL)
-- Билет №12: Бронирование номеров в отеле
-- Таблицы: типы номеров, номера, гости, бронирования, дополнительные услуги
-- Таблица 1: типы номеров
CREATE TABLE IF NOT EXISTS типы_номеров (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: номера
CREATE TABLE IF NOT EXISTS номера (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_типы_номеров INT REFERENCES типы_номеров(id),
дополнение VARCHAR(200)
);
-- Таблица 3: гости
CREATE TABLE IF NOT EXISTS гости (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: бронирования
CREATE TABLE IF NOT EXISTS бронирования (
id SERIAL PRIMARY KEY,
id_гости INT NOT NULL REFERENCES гости(id),
id_номера INT NOT NULL REFERENCES номера(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: дополнительные услуги
CREATE TABLE IF NOT EXISTS дополнительные_услуги (
id SERIAL PRIMARY KEY,
id_бронирования INT NOT NULL REFERENCES бронирования(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №12: Бронирование номеров в отеле
INSERT INTO типы_номеров (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO номера (наименование, id_типы_номеров, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO гости (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO бронирования (id_гости, id_номера, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO бронирования (id_бронирования, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №12: Бронирование номеров в отеле
-- 1. Простой запрос с условием: активные записи
SELECT * FROM бронирования WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM бронирования t4
JOIN гости t3 ON t4.id_гости = t3.id
JOIN номера t2 ON t4.id_номера = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM бронирования
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM бронирования
WHERE сумма > (SELECT AVG(сумма) FROM бронирования);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE бронирования SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Бронирование номеров в отеле»
### Назначение
База данных предназначена для автоматизации учёта в системе «Бронирование номеров в отеле». Хранит информацию в 5 взаимосвязанных таблицах: типы номеров, номера, гости, бронирования, дополнительные услуги.
### Структура базы данных
Таблица «типы номеров» — основной справочник. Таблица «номера» связана с первой по внешнему ключу. Таблица «гости» хранит данные об участниках (клиентах/сотрудниках). Таблица «бронирования» фиксирует транзакции/события. Таблица «дополнительные услуги» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (типы номеров, номера), затем добавляйте данные в гости и бронирования.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE бронирования SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (дополнительные услуги, бронирования), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №13: Расписание и продажа билетов в кинотеатр
1. Создание таблиц (DDL)
-- Билет №13: Расписание и продажа билетов в кинотеатр
-- Таблицы: фильмы, кинозалы, сеансы, клиенты, билеты
-- Таблица 1: фильмы
CREATE TABLE IF NOT EXISTS фильмы (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: кинозалы
CREATE TABLE IF NOT EXISTS кинозалы (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_фильмы INT REFERENCES фильмы(id),
дополнение VARCHAR(200)
);
-- Таблица 3: сеансы
CREATE TABLE IF NOT EXISTS сеансы (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: клиенты
CREATE TABLE IF NOT EXISTS клиенты (
id SERIAL PRIMARY KEY,
id_сеансы INT NOT NULL REFERENCES сеансы(id),
id_кинозалы INT NOT NULL REFERENCES кинозалы(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: билеты
CREATE TABLE IF NOT EXISTS билеты (
id SERIAL PRIMARY KEY,
id_клиенты INT NOT NULL REFERENCES клиенты(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №13: Расписание и продажа билетов в кинотеатр
INSERT INTO фильмы (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO кинозалы (наименование, id_фильмы, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO сеансы (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO клиенты (id_сеансы, id_кинозалы, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO клиенты (id_клиенты, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №13: Расписание и продажа билетов в кинотеатр
-- 1. Простой запрос с условием: активные записи
SELECT * FROM клиенты WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM клиенты t4
JOIN сеансы t3 ON t4.id_сеансы = t3.id
JOIN кинозалы t2 ON t4.id_кинозалы = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM клиенты
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM клиенты
WHERE сумма > (SELECT AVG(сумма) FROM клиенты);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE клиенты SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Расписание и продажа билетов в кинотеатр»
### Назначение
База данных предназначена для автоматизации учёта в системе «Расписание и продажа билетов в кинотеатр». Хранит информацию в 5 взаимосвязанных таблицах: фильмы, кинозалы, сеансы, клиенты, билеты.
### Структура базы данных
Таблица «фильмы» — основной справочник. Таблица «кинозалы» связана с первой по внешнему ключу. Таблица «сеансы» хранит данные об участниках (клиентах/сотрудниках). Таблица «клиенты» фиксирует транзакции/события. Таблица «билеты» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (фильмы, кинозалы), затем добавляйте данные в сеансы и клиенты.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE клиенты SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (билеты, клиенты), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №14: Учет сотрудников и проектов в IT-компании
1. Создание таблиц (DDL)
-- Билет №14: Учет сотрудников и проектов в IT-компании
-- Таблицы: отделы, сотрудники, проекты, роли в проектах, навыки сотрудников
-- Таблица 1: отделы
CREATE TABLE IF NOT EXISTS отделы (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: сотрудники
CREATE TABLE IF NOT EXISTS сотрудники (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_отделы INT REFERENCES отделы(id),
дополнение VARCHAR(200)
);
-- Таблица 3: проекты
CREATE TABLE IF NOT EXISTS проекты (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: роли в проектах
CREATE TABLE IF NOT EXISTS роли_в_проектах (
id SERIAL PRIMARY KEY,
id_проекты INT NOT NULL REFERENCES проекты(id),
id_сотрудники INT NOT NULL REFERENCES сотрудники(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: навыки сотрудников
CREATE TABLE IF NOT EXISTS навыки_сотрудников (
id SERIAL PRIMARY KEY,
id_роли_в_проектах INT NOT NULL REFERENCES роли_в_проектах(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №14: Учет сотрудников и проектов в IT-компании
INSERT INTO отделы (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO сотрудники (наименование, id_отделы, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO проекты (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO роли_в_проектах (id_проекты, id_сотрудники, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO роли_в_проектах (id_роли_в_проектах, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №14: Учет сотрудников и проектов в IT-компании
-- 1. Простой запрос с условием: активные записи
SELECT * FROM роли_в_проектах WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM роли_в_проектах t4
JOIN проекты t3 ON t4.id_проекты = t3.id
JOIN сотрудники t2 ON t4.id_сотрудники = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM роли_в_проектах
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM роли_в_проектах
WHERE сумма > (SELECT AVG(сумма) FROM роли_в_проектах);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE роли_в_проектах SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Учет сотрудников и проектов в IT-компании»
### Назначение
База данных предназначена для автоматизации учёта в системе «Учет сотрудников и проектов в IT-компании». Хранит информацию в 5 взаимосвязанных таблицах: отделы, сотрудники, проекты, роли в проектах, навыки сотрудников.
### Структура базы данных
Таблица «отделы» — основной справочник. Таблица «сотрудники» связана с первой по внешнему ключу. Таблица «проекты» хранит данные об участниках (клиентах/сотрудниках). Таблица «роли в проектах» фиксирует транзакции/события. Таблица «навыки сотрудников» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (отделы, сотрудники), затем добавляйте данные в проекты и роли в проектах.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE роли_в_проектах SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (навыки сотрудников, роли в проектах), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №15: Туристическое агентство и продажа туров
1. Создание таблиц (DDL)
-- Билет №15: Туристическое агентство и продажа туров
-- Таблицы: страны, отели, туры, туристы, заявки на бронирование
-- Таблица 1: страны
CREATE TABLE IF NOT EXISTS страны (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: отели
CREATE TABLE IF NOT EXISTS отели (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_страны INT REFERENCES страны(id),
дополнение VARCHAR(200)
);
-- Таблица 3: туры
CREATE TABLE IF NOT EXISTS туры (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: туристы
CREATE TABLE IF NOT EXISTS туристы (
id SERIAL PRIMARY KEY,
id_туры INT NOT NULL REFERENCES туры(id),
id_отели INT NOT NULL REFERENCES отели(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: заявки на бронирование
CREATE TABLE IF NOT EXISTS заявки_на_бронирование (
id SERIAL PRIMARY KEY,
id_туристы INT NOT NULL REFERENCES туристы(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №15: Туристическое агентство и продажа туров
INSERT INTO страны (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO отели (наименование, id_страны, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO туры (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO туристы (id_туры, id_отели, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO туристы (id_туристы, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №15: Туристическое агентство и продажа туров
-- 1. Простой запрос с условием: активные записи
SELECT * FROM туристы WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM туристы t4
JOIN туры t3 ON t4.id_туры = t3.id
JOIN отели t2 ON t4.id_отели = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM туристы
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM туристы
WHERE сумма > (SELECT AVG(сумма) FROM туристы);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE туристы SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Туристическое агентство и продажа туров»
### Назначение
База данных предназначена для автоматизации учёта в системе «Туристическое агентство и продажа туров». Хранит информацию в 5 взаимосвязанных таблицах: страны, отели, туры, туристы, заявки на бронирование.
### Структура базы данных
Таблица «страны» — основной справочник. Таблица «отели» связана с первой по внешнему ключу. Таблица «туры» хранит данные об участниках (клиентах/сотрудниках). Таблица «туристы» фиксирует транзакции/события. Таблица «заявки на бронирование» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (страны, отели), затем добавляйте данные в туры и туристы.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE туристы SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (заявки на бронирование, туристы), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №16: Фитнес-клуб и абонементы
1. Создание таблиц (DDL)
-- Билет №16: Фитнес-клуб и абонементы
-- Таблицы: клиенты, тренеры, направления, абонементы, расписание занятий
-- Таблица 1: клиенты
CREATE TABLE IF NOT EXISTS клиенты (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: тренеры
CREATE TABLE IF NOT EXISTS тренеры (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_клиенты INT REFERENCES клиенты(id),
дополнение VARCHAR(200)
);
-- Таблица 3: направления
CREATE TABLE IF NOT EXISTS направления (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: абонементы
CREATE TABLE IF NOT EXISTS абонементы (
id SERIAL PRIMARY KEY,
id_направления INT NOT NULL REFERENCES направления(id),
id_тренеры INT NOT NULL REFERENCES тренеры(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: расписание занятий
CREATE TABLE IF NOT EXISTS расписание_занятий (
id SERIAL PRIMARY KEY,
id_абонементы INT NOT NULL REFERENCES абонементы(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №16: Фитнес-клуб и абонементы
INSERT INTO клиенты (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO тренеры (наименование, id_клиенты, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO направления (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO абонементы (id_направления, id_тренеры, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO абонементы (id_абонементы, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №16: Фитнес-клуб и абонементы
-- 1. Простой запрос с условием: активные записи
SELECT * FROM абонементы WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM абонементы t4
JOIN направления t3 ON t4.id_направления = t3.id
JOIN тренеры t2 ON t4.id_тренеры = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM абонементы
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM абонементы
WHERE сумма > (SELECT AVG(сумма) FROM абонементы);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE абонементы SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Фитнес-клуб и абонементы»
### Назначение
База данных предназначена для автоматизации учёта в системе «Фитнес-клуб и абонементы». Хранит информацию в 5 взаимосвязанных таблицах: клиенты, тренеры, направления, абонементы, расписание занятий.
### Структура базы данных
Таблица «клиенты» — основной справочник. Таблица «тренеры» связана с первой по внешнему ключу. Таблица «направления» хранит данные об участниках (клиентах/сотрудниках). Таблица «абонементы» фиксирует транзакции/события. Таблица «расписание занятий» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (клиенты, тренеры), затем добавляйте данные в направления и абонементы.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE абонементы SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (расписание занятий, абонементы), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №17: Агентство недвижимости и аренда квартир
1. Создание таблиц (DDL)
-- Билет №17: Агентство недвижимости и аренда квартир
-- Таблицы: собственники, объекты недвижимости, районы, арендаторы, договоры аренды
-- Таблица 1: собственники
CREATE TABLE IF NOT EXISTS собственники (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: объекты недвижимости
CREATE TABLE IF NOT EXISTS объекты_недвижимости (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_собственники INT REFERENCES собственники(id),
дополнение VARCHAR(200)
);
-- Таблица 3: районы
CREATE TABLE IF NOT EXISTS районы (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: арендаторы
CREATE TABLE IF NOT EXISTS арендаторы (
id SERIAL PRIMARY KEY,
id_районы INT NOT NULL REFERENCES районы(id),
id_объекты_недвижимости INT NOT NULL REFERENCES объекты_недвижимости(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: договоры аренды
CREATE TABLE IF NOT EXISTS договоры_аренды (
id SERIAL PRIMARY KEY,
id_арендаторы INT NOT NULL REFERENCES арендаторы(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №17: Агентство недвижимости и аренда квартир
INSERT INTO собственники (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO объекты_недвижимости (наименование, id_собственники, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO районы (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO арендаторы (id_районы, id_объекты_недвижимости, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO арендаторы (id_арендаторы, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №17: Агентство недвижимости и аренда квартир
-- 1. Простой запрос с условием: активные записи
SELECT * FROM арендаторы WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM арендаторы t4
JOIN районы t3 ON t4.id_районы = t3.id
JOIN объекты_недвижимости t2 ON t4.id_объекты_недвижимости = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM арендаторы
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM арендаторы
WHERE сумма > (SELECT AVG(сумма) FROM арендаторы);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE арендаторы SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Агентство недвижимости и аренда квартир»
### Назначение
База данных предназначена для автоматизации учёта в системе «Агентство недвижимости и аренда квартир». Хранит информацию в 5 взаимосвязанных таблицах: собственники, объекты недвижимости, районы, арендаторы, договоры аренды.
### Структура базы данных
Таблица «собственники» — основной справочник. Таблица «объекты недвижимости» связана с первой по внешнему ключу. Таблица «районы» хранит данные об участниках (клиентах/сотрудниках). Таблица «арендаторы» фиксирует транзакции/события. Таблица «договоры аренды» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (собственники, объекты недвижимости), затем добавляйте данные в районы и арендаторы.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE арендаторы SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (договоры аренды, арендаторы), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №18: Служба доставки посылок
1. Создание таблиц (DDL)
-- Билет №18: Служба доставки посылок
-- Таблицы: клиенты, посылки, курьеры, маршруты, доставки
-- Таблица 1: клиенты
CREATE TABLE IF NOT EXISTS клиенты (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: посылки
CREATE TABLE IF NOT EXISTS посылки (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_клиенты INT REFERENCES клиенты(id),
дополнение VARCHAR(200)
);
-- Таблица 3: курьеры
CREATE TABLE IF NOT EXISTS курьеры (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: маршруты
CREATE TABLE IF NOT EXISTS маршруты (
id SERIAL PRIMARY KEY,
id_курьеры INT NOT NULL REFERENCES курьеры(id),
id_посылки INT NOT NULL REFERENCES посылки(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: доставки
CREATE TABLE IF NOT EXISTS доставки (
id SERIAL PRIMARY KEY,
id_маршруты INT NOT NULL REFERENCES маршруты(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №18: Служба доставки посылок
INSERT INTO клиенты (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO посылки (наименование, id_клиенты, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO курьеры (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO маршруты (id_курьеры, id_посылки, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO маршруты (id_маршруты, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №18: Служба доставки посылок
-- 1. Простой запрос с условием: активные записи
SELECT * FROM маршруты WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM маршруты t4
JOIN курьеры t3 ON t4.id_курьеры = t3.id
JOIN посылки t2 ON t4.id_посылки = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM маршруты
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM маршруты
WHERE сумма > (SELECT AVG(сумма) FROM маршруты);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE маршруты SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Служба доставки посылок»
### Назначение
База данных предназначена для автоматизации учёта в системе «Служба доставки посылок». Хранит информацию в 5 взаимосвязанных таблицах: клиенты, посылки, курьеры, маршруты, доставки.
### Структура базы данных
Таблица «клиенты» — основной справочник. Таблица «посылки» связана с первой по внешнему ключу. Таблица «курьеры» хранит данные об участниках (клиентах/сотрудниках). Таблица «маршруты» фиксирует транзакции/события. Таблица «доставки» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (клиенты, посылки), затем добавляйте данные в курьеры и маршруты.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE маршруты SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (доставки, маршруты), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №19: Прокат автомобилей
1. Создание таблиц (DDL)
-- Билет №19: Прокат автомобилей
-- Таблицы: автомобили, филиалы, клиенты, договоры аренды, страховые случаи
-- Таблица 1: автомобили
CREATE TABLE IF NOT EXISTS автомобили (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: филиалы
CREATE TABLE IF NOT EXISTS филиалы (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_автомобили INT REFERENCES автомобили(id),
дополнение VARCHAR(200)
);
-- Таблица 3: клиенты
CREATE TABLE IF NOT EXISTS клиенты (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: договоры аренды
CREATE TABLE IF NOT EXISTS договоры_аренды (
id SERIAL PRIMARY KEY,
id_клиенты INT NOT NULL REFERENCES клиенты(id),
id_филиалы INT NOT NULL REFERENCES филиалы(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: страховые случаи
CREATE TABLE IF NOT EXISTS страховые_случаи (
id SERIAL PRIMARY KEY,
id_договоры_аренды INT NOT NULL REFERENCES договоры_аренды(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №19: Прокат автомобилей
INSERT INTO автомобили (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO филиалы (наименование, id_автомобили, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO клиенты (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO договоры_аренды (id_клиенты, id_филиалы, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO договоры_аренды (id_договоры_аренды, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №19: Прокат автомобилей
-- 1. Простой запрос с условием: активные записи
SELECT * FROM договоры_аренды WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM договоры_аренды t4
JOIN клиенты t3 ON t4.id_клиенты = t3.id
JOIN филиалы t2 ON t4.id_филиалы = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM договоры_аренды
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM договоры_аренды
WHERE сумма > (SELECT AVG(сумма) FROM договоры_аренды);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE договоры_аренды SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Прокат автомобилей»
### Назначение
База данных предназначена для автоматизации учёта в системе «Прокат автомобилей». Хранит информацию в 5 взаимосвязанных таблицах: автомобили, филиалы, клиенты, договоры аренды, страховые случаи.
### Структура базы данных
Таблица «автомобили» — основной справочник. Таблица «филиалы» связана с первой по внешнему ключу. Таблица «клиенты» хранит данные об участниках (клиентах/сотрудниках). Таблица «договоры аренды» фиксирует транзакции/события. Таблица «страховые случаи» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (автомобили, филиалы), затем добавляйте данные в клиенты и договоры аренды.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE договоры_аренды SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (страховые случаи, договоры аренды), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №20: Ветеринарная клиника
1. Создание таблиц (DDL)
-- Билет №20: Ветеринарная клиника
-- Таблицы: владельцы, питомцы, ветеринары, приёмы, вакцинации
-- Таблица 1: владельцы
CREATE TABLE IF NOT EXISTS владельцы (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: питомцы
CREATE TABLE IF NOT EXISTS питомцы (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_владельцы INT REFERENCES владельцы(id),
дополнение VARCHAR(200)
);
-- Таблица 3: ветеринары
CREATE TABLE IF NOT EXISTS ветеринары (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: приёмы
CREATE TABLE IF NOT EXISTS приёмы (
id SERIAL PRIMARY KEY,
id_ветеринары INT NOT NULL REFERENCES ветеринары(id),
id_питомцы INT NOT NULL REFERENCES питомцы(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: вакцинации
CREATE TABLE IF NOT EXISTS вакцинации (
id SERIAL PRIMARY KEY,
id_приёмы INT NOT NULL REFERENCES приёмы(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №20: Ветеринарная клиника
INSERT INTO владельцы (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO питомцы (наименование, id_владельцы, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO ветеринары (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO приёмы (id_ветеринары, id_питомцы, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO приёмы (id_приёмы, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №20: Ветеринарная клиника
-- 1. Простой запрос с условием: активные записи
SELECT * FROM приёмы WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM приёмы t4
JOIN ветеринары t3 ON t4.id_ветеринары = t3.id
JOIN питомцы t2 ON t4.id_питомцы = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM приёмы
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM приёмы
WHERE сумма > (SELECT AVG(сумма) FROM приёмы);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE приёмы SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Ветеринарная клиника»
### Назначение
База данных предназначена для автоматизации учёта в системе «Ветеринарная клиника». Хранит информацию в 5 взаимосвязанных таблицах: владельцы, питомцы, ветеринары, приёмы, вакцинации.
### Структура базы данных
Таблица «владельцы» — основной справочник. Таблица «питомцы» связана с первой по внешнему ключу. Таблица «ветеринары» хранит данные об участниках (клиентах/сотрудниках). Таблица «приёмы» фиксирует транзакции/события. Таблица «вакцинации» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (владельцы, питомцы), затем добавляйте данные в ветеринары и приёмы.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE приёмы SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (вакцинации, приёмы), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №21: Продажа билетов на концерты и фестивали
1. Создание таблиц (DDL)
-- Билет №21: Продажа билетов на концерты и фестивали
-- Таблицы: площадки, мероприятия, артисты, зрители, проданные билеты
-- Таблица 1: площадки
CREATE TABLE IF NOT EXISTS площадки (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: мероприятия
CREATE TABLE IF NOT EXISTS мероприятия (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_площадки INT REFERENCES площадки(id),
дополнение VARCHAR(200)
);
-- Таблица 3: артисты
CREATE TABLE IF NOT EXISTS артисты (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: зрители
CREATE TABLE IF NOT EXISTS зрители (
id SERIAL PRIMARY KEY,
id_артисты INT NOT NULL REFERENCES артисты(id),
id_мероприятия INT NOT NULL REFERENCES мероприятия(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: проданные билеты
CREATE TABLE IF NOT EXISTS проданные_билеты (
id SERIAL PRIMARY KEY,
id_зрители INT NOT NULL REFERENCES зрители(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №21: Продажа билетов на концерты и фестивали
INSERT INTO площадки (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO мероприятия (наименование, id_площадки, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO артисты (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO зрители (id_артисты, id_мероприятия, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO зрители (id_зрители, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №21: Продажа билетов на концерты и фестивали
-- 1. Простой запрос с условием: активные записи
SELECT * FROM зрители WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM зрители t4
JOIN артисты t3 ON t4.id_артисты = t3.id
JOIN мероприятия t2 ON t4.id_мероприятия = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM зрители
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM зрители
WHERE сумма > (SELECT AVG(сумма) FROM зрители);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE зрители SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Продажа билетов на концерты и фестивали»
### Назначение
База данных предназначена для автоматизации учёта в системе «Продажа билетов на концерты и фестивали». Хранит информацию в 5 взаимосвязанных таблицах: площадки, мероприятия, артисты, зрители, проданные билеты.
### Структура базы данных
Таблица «площадки» — основной справочник. Таблица «мероприятия» связана с первой по внешнему ключу. Таблица «артисты» хранит данные об участниках (клиентах/сотрудниках). Таблица «зрители» фиксирует транзакции/события. Таблица «проданные билеты» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (площадки, мероприятия), затем добавляйте данные в артисты и зрители.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE зрители SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (проданные билеты, зрители), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №22: Салон красоты и парикмахерская
1. Создание таблиц (DDL)
-- Билет №22: Салон красоты и парикмахерская
-- Таблицы: мастера, клиенты, услуги, записи на приём, чеки об оплате
-- Таблица 1: мастера
CREATE TABLE IF NOT EXISTS мастера (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: клиенты
CREATE TABLE IF NOT EXISTS клиенты (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_мастера INT REFERENCES мастера(id),
дополнение VARCHAR(200)
);
-- Таблица 3: услуги
CREATE TABLE IF NOT EXISTS услуги (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: записи на приём
CREATE TABLE IF NOT EXISTS записи_на_приём (
id SERIAL PRIMARY KEY,
id_услуги INT NOT NULL REFERENCES услуги(id),
id_клиенты INT NOT NULL REFERENCES клиенты(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: чеки об оплате
CREATE TABLE IF NOT EXISTS чеки_об_оплате (
id SERIAL PRIMARY KEY,
id_записи_на_приём INT NOT NULL REFERENCES записи_на_приём(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №22: Салон красоты и парикмахерская
INSERT INTO мастера (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO клиенты (наименование, id_мастера, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO услуги (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO записи_на_приём (id_услуги, id_клиенты, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO записи_на_приём (id_записи_на_приём, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №22: Салон красоты и парикмахерская
-- 1. Простой запрос с условием: активные записи
SELECT * FROM записи_на_приём WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM записи_на_приём t4
JOIN услуги t3 ON t4.id_услуги = t3.id
JOIN клиенты t2 ON t4.id_клиенты = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM записи_на_приём
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM записи_на_приём
WHERE сумма > (SELECT AVG(сумма) FROM записи_на_приём);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE записи_на_приём SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Салон красоты и парикмахерская»
### Назначение
База данных предназначена для автоматизации учёта в системе «Салон красоты и парикмахерская». Хранит информацию в 5 взаимосвязанных таблицах: мастера, клиенты, услуги, записи на приём, чеки об оплате.
### Структура базы данных
Таблица «мастера» — основной справочник. Таблица «клиенты» связана с первой по внешнему ключу. Таблица «услуги» хранит данные об участниках (клиентах/сотрудниках). Таблица «записи на приём» фиксирует транзакции/события. Таблица «чеки об оплате» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (мастера, клиенты), затем добавляйте данные в услуги и записи на приём.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE записи_на_приём SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (чеки об оплате, записи на приём), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №23: Строительная компания и управление проектами
1. Создание таблиц (DDL)
-- Билет №23: Строительная компания и управление проектами
-- Таблицы: объекты строительства, прорабы, строительные материалы, сметы, этапы работ
-- Таблица 1: объекты строительства
CREATE TABLE IF NOT EXISTS объекты_строительства (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: прорабы
CREATE TABLE IF NOT EXISTS прорабы (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_объекты_строительства INT REFERENCES объекты_строительства(id),
дополнение VARCHAR(200)
);
-- Таблица 3: строительные материалы
CREATE TABLE IF NOT EXISTS строительные_материалы (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: сметы
CREATE TABLE IF NOT EXISTS сметы (
id SERIAL PRIMARY KEY,
id_строительные_материалы INT NOT NULL REFERENCES строительные_материалы(id),
id_прорабы INT NOT NULL REFERENCES прорабы(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: этапы работ
CREATE TABLE IF NOT EXISTS этапы_работ (
id SERIAL PRIMARY KEY,
id_сметы INT NOT NULL REFERENCES сметы(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №23: Строительная компания и управление проектами
INSERT INTO объекты_строительства (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO прорабы (наименование, id_объекты_строительства, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO строительные_материалы (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO сметы (id_строительные_материалы, id_прорабы, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO сметы (id_сметы, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №23: Строительная компания и управление проектами
-- 1. Простой запрос с условием: активные записи
SELECT * FROM сметы WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM сметы t4
JOIN строительные_материалы t3 ON t4.id_строительные_материалы = t3.id
JOIN прорабы t2 ON t4.id_прорабы = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM сметы
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM сметы
WHERE сумма > (SELECT AVG(сумма) FROM сметы);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE сметы SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Строительная компания и управление проектами»
### Назначение
База данных предназначена для автоматизации учёта в системе «Строительная компания и управление проектами». Хранит информацию в 5 взаимосвязанных таблицах: объекты строительства, прорабы, строительные материалы, сметы, этапы работ.
### Структура базы данных
Таблица «объекты строительства» — основной справочник. Таблица «прорабы» связана с первой по внешнему ключу. Таблица «строительные материалы» хранит данные об участниках (клиентах/сотрудниках). Таблица «сметы» фиксирует транзакции/события. Таблица «этапы работ» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (объекты строительства, прорабы), затем добавляйте данные в строительные материалы и сметы.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE сметы SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (этапы работ, сметы), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №24: Успеваемость учеников средней школы
1. Создание таблиц (DDL)
-- Билет №24: Успеваемость учеников средней школы
-- Таблицы: классы, учителя, ученики, школьные предметы, журнал оценок
-- Таблица 1: классы
CREATE TABLE IF NOT EXISTS классы (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: учителя
CREATE TABLE IF NOT EXISTS учителя (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_классы INT REFERENCES классы(id),
дополнение VARCHAR(200)
);
-- Таблица 3: ученики
CREATE TABLE IF NOT EXISTS ученики (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: школьные предметы
CREATE TABLE IF NOT EXISTS школьные_предметы (
id SERIAL PRIMARY KEY,
id_ученики INT NOT NULL REFERENCES ученики(id),
id_учителя INT NOT NULL REFERENCES учителя(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: журнал оценок
CREATE TABLE IF NOT EXISTS журнал_оценок (
id SERIAL PRIMARY KEY,
id_школьные_предметы INT NOT NULL REFERENCES школьные_предметы(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №24: Успеваемость учеников средней школы
INSERT INTO классы (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO учителя (наименование, id_классы, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO ученики (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO школьные_предметы (id_ученики, id_учителя, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO школьные_предметы (id_школьные_предметы, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №24: Успеваемость учеников средней школы
-- 1. Простой запрос с условием: активные записи
SELECT * FROM школьные_предметы WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM школьные_предметы t4
JOIN ученики t3 ON t4.id_ученики = t3.id
JOIN учителя t2 ON t4.id_учителя = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM школьные_предметы
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM школьные_предметы
WHERE сумма > (SELECT AVG(сумма) FROM школьные_предметы);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE школьные_предметы SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Успеваемость учеников средней школы»
### Назначение
База данных предназначена для автоматизации учёта в системе «Успеваемость учеников средней школы». Хранит информацию в 5 взаимосвязанных таблицах: классы, учителя, ученики, школьные предметы, журнал оценок.
### Структура базы данных
Таблица «классы» — основной справочник. Таблица «учителя» связана с первой по внешнему ключу. Таблица «ученики» хранит данные об участниках (клиентах/сотрудниках). Таблица «школьные предметы» фиксирует транзакции/события. Таблица «журнал оценок» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (классы, учителя), затем добавляйте данные в ученики и школьные предметы.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE школьные_предметы SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (журнал оценок, школьные предметы), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №25: Автосалон и продажа автомобилей
1. Создание таблиц (DDL)
-- Билет №25: Автосалон и продажа автомобилей
-- Таблицы: автомобили, поставщики, клиенты, договоры купли-продажи, дополнительное оборудование
-- Таблица 1: автомобили
CREATE TABLE IF NOT EXISTS автомобили (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: поставщики
CREATE TABLE IF NOT EXISTS поставщики (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_автомобили INT REFERENCES автомобили(id),
дополнение VARCHAR(200)
);
-- Таблица 3: клиенты
CREATE TABLE IF NOT EXISTS клиенты (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: договоры купли-продажи
CREATE TABLE IF NOT EXISTS договоры_купли-продажи (
id SERIAL PRIMARY KEY,
id_клиенты INT NOT NULL REFERENCES клиенты(id),
id_поставщики INT NOT NULL REFERENCES поставщики(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: дополнительное оборудование
CREATE TABLE IF NOT EXISTS дополнительное_оборудование (
id SERIAL PRIMARY KEY,
id_договоры_купли-продажи INT NOT NULL REFERENCES договоры_купли-продажи(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №25: Автосалон и продажа автомобилей
INSERT INTO автомобили (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO поставщики (наименование, id_автомобили, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO клиенты (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO договоры_купли-продажи (id_клиенты, id_поставщики, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO договоры_купли-продажи (id_договоры_купли-продажи, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №25: Автосалон и продажа автомобилей
-- 1. Простой запрос с условием: активные записи
SELECT * FROM договоры_купли-продажи WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM договоры_купли-продажи t4
JOIN клиенты t3 ON t4.id_клиенты = t3.id
JOIN поставщики t2 ON t4.id_поставщики = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM договоры_купли-продажи
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM договоры_купли-продажи
WHERE сумма > (SELECT AVG(сумма) FROM договоры_купли-продажи);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE договоры_купли-продажи SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Автосалон и продажа автомобилей»
### Назначение
База данных предназначена для автоматизации учёта в системе «Автосалон и продажа автомобилей». Хранит информацию в 5 взаимосвязанных таблицах: автомобили, поставщики, клиенты, договоры купли-продажи, дополнительное оборудование.
### Структура базы данных
Таблица «автомобили» — основной справочник. Таблица «поставщики» связана с первой по внешнему ключу. Таблица «клиенты» хранит данные об участниках (клиентах/сотрудниках). Таблица «договоры купли-продажи» фиксирует транзакции/события. Таблица «дополнительное оборудование» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (автомобили, поставщики), затем добавляйте данные в клиенты и договоры купли-продажи.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE договоры_купли-продажи SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (дополнительное оборудование, договоры купли-продажи), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №26: Киностудия и производство фильмов
1. Создание таблиц (DDL)
-- Билет №26: Киностудия и производство фильмов
-- Таблицы: фильмы, актёры, режиссёры, съёмочные группы, роли
-- Таблица 1: фильмы
CREATE TABLE IF NOT EXISTS фильмы (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: актёры
CREATE TABLE IF NOT EXISTS актёры (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_фильмы INT REFERENCES фильмы(id),
дополнение VARCHAR(200)
);
-- Таблица 3: режиссёры
CREATE TABLE IF NOT EXISTS режиссёры (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: съёмочные группы
CREATE TABLE IF NOT EXISTS съёмочные_группы (
id SERIAL PRIMARY KEY,
id_режиссёры INT NOT NULL REFERENCES режиссёры(id),
id_актёры INT NOT NULL REFERENCES актёры(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: роли
CREATE TABLE IF NOT EXISTS роли (
id SERIAL PRIMARY KEY,
id_съёмочные_группы INT NOT NULL REFERENCES съёмочные_группы(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №26: Киностудия и производство фильмов
INSERT INTO фильмы (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO актёры (наименование, id_фильмы, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO режиссёры (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO съёмочные_группы (id_режиссёры, id_актёры, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO съёмочные_группы (id_съёмочные_группы, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №26: Киностудия и производство фильмов
-- 1. Простой запрос с условием: активные записи
SELECT * FROM съёмочные_группы WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM съёмочные_группы t4
JOIN режиссёры t3 ON t4.id_режиссёры = t3.id
JOIN актёры t2 ON t4.id_актёры = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM съёмочные_группы
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM съёмочные_группы
WHERE сумма > (SELECT AVG(сумма) FROM съёмочные_группы);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE съёмочные_группы SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Киностудия и производство фильмов»
### Назначение
База данных предназначена для автоматизации учёта в системе «Киностудия и производство фильмов». Хранит информацию в 5 взаимосвязанных таблицах: фильмы, актёры, режиссёры, съёмочные группы, роли.
### Структура базы данных
Таблица «фильмы» — основной справочник. Таблица «актёры» связана с первой по внешнему ключу. Таблица «режиссёры» хранит данные об участниках (клиентах/сотрудниках). Таблица «съёмочные группы» фиксирует транзакции/события. Таблица «роли» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (фильмы, актёры), затем добавляйте данные в режиссёры и съёмочные группы.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE съёмочные_группы SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (роли, съёмочные группы), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №27: Банковские счета и операции
1. Создание таблиц (DDL)
-- Билет №27: Банковские счета и операции
-- Таблицы: клиенты, счета, банковские карты, операции, отделения
-- Таблица 1: клиенты
CREATE TABLE IF NOT EXISTS клиенты (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: счета
CREATE TABLE IF NOT EXISTS счета (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_клиенты INT REFERENCES клиенты(id),
дополнение VARCHAR(200)
);
-- Таблица 3: банковские карты
CREATE TABLE IF NOT EXISTS банковские_карты (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: операции
CREATE TABLE IF NOT EXISTS операции (
id SERIAL PRIMARY KEY,
id_банковские_карты INT NOT NULL REFERENCES банковские_карты(id),
id_счета INT NOT NULL REFERENCES счета(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: отделения
CREATE TABLE IF NOT EXISTS отделения (
id SERIAL PRIMARY KEY,
id_операции INT NOT NULL REFERENCES операции(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №27: Банковские счета и операции
INSERT INTO клиенты (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO счета (наименование, id_клиенты, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO банковские_карты (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO операции (id_банковские_карты, id_счета, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO операции (id_операции, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №27: Банковские счета и операции
-- 1. Простой запрос с условием: активные записи
SELECT * FROM операции WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM операции t4
JOIN банковские_карты t3 ON t4.id_банковские_карты = t3.id
JOIN счета t2 ON t4.id_счета = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM операции
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM операции
WHERE сумма > (SELECT AVG(сумма) FROM операции);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE операции SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Банковские счета и операции»
### Назначение
База данных предназначена для автоматизации учёта в системе «Банковские счета и операции». Хранит информацию в 5 взаимосвязанных таблицах: клиенты, счета, банковские карты, операции, отделения.
### Структура базы данных
Таблица «клиенты» — основной справочник. Таблица «счета» связана с первой по внешнему ключу. Таблица «банковские карты» хранит данные об участниках (клиентах/сотрудниках). Таблица «операции» фиксирует транзакции/события. Таблица «отделения» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (клиенты, счета), затем добавляйте данные в банковские карты и операции.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE операции SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (отделения, операции), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №28: Система технической поддержки и обработки заявок
1. Создание таблиц (DDL)
-- Билет №28: Система технической поддержки и обработки заявок
-- Таблицы: пользователи, инженеры поддержки, заявки, корпоративное оборудование, категории проблем
-- Таблица 1: пользователи
CREATE TABLE IF NOT EXISTS пользователи (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: инженеры поддержки
CREATE TABLE IF NOT EXISTS инженеры_поддержки (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_пользователи INT REFERENCES пользователи(id),
дополнение VARCHAR(200)
);
-- Таблица 3: заявки
CREATE TABLE IF NOT EXISTS заявки (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: корпоративное оборудование
CREATE TABLE IF NOT EXISTS корпоративное_оборудование (
id SERIAL PRIMARY KEY,
id_заявки INT NOT NULL REFERENCES заявки(id),
id_инженеры_поддержки INT NOT NULL REFERENCES инженеры_поддержки(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: категории проблем
CREATE TABLE IF NOT EXISTS категории_проблем (
id SERIAL PRIMARY KEY,
id_корпоративное_оборудование INT NOT NULL REFERENCES корпоративное_оборудование(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №28: Система технической поддержки и обработки заявок
INSERT INTO пользователи (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO инженеры_поддержки (наименование, id_пользователи, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO заявки (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO корпоративное_оборудование (id_заявки, id_инженеры_поддержки, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO корпоративное_оборудование (id_корпоративное_оборудование, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №28: Система технической поддержки и обработки заявок
-- 1. Простой запрос с условием: активные записи
SELECT * FROM корпоративное_оборудование WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM корпоративное_оборудование t4
JOIN заявки t3 ON t4.id_заявки = t3.id
JOIN инженеры_поддержки t2 ON t4.id_инженеры_поддержки = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM корпоративное_оборудование
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM корпоративное_оборудование
WHERE сумма > (SELECT AVG(сумма) FROM корпоративное_оборудование);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE корпоративное_оборудование SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Система технической поддержки и обработки заявок»
### Назначение
База данных предназначена для автоматизации учёта в системе «Система технической поддержки и обработки заявок». Хранит информацию в 5 взаимосвязанных таблицах: пользователи, инженеры поддержки, заявки, корпоративное оборудование, категории проблем.
### Структура базы данных
Таблица «пользователи» — основной справочник. Таблица «инженеры поддержки» связана с первой по внешнему ключу. Таблица «заявки» хранит данные об участниках (клиентах/сотрудниках). Таблица «корпоративное оборудование» фиксирует транзакции/события. Таблица «категории проблем» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (пользователи, инженеры поддержки), затем добавляйте данные в заявки и корпоративное оборудование.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE корпоративное_оборудование SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (категории проблем, корпоративное оборудование), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №29: Учёт в сельскохозяйственном предприятии
1. Создание таблиц (DDL)
-- Билет №29: Учёт в сельскохозяйственном предприятии
-- Таблицы: поля, культуры, сотрудники, сельскохозяйственная техника, сбор урожая
-- Таблица 1: поля
CREATE TABLE IF NOT EXISTS поля (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: культуры
CREATE TABLE IF NOT EXISTS культуры (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_поля INT REFERENCES поля(id),
дополнение VARCHAR(200)
);
-- Таблица 3: сотрудники
CREATE TABLE IF NOT EXISTS сотрудники (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: сельскохозяйственная техника
CREATE TABLE IF NOT EXISTS сельскохозяйственная_техника (
id SERIAL PRIMARY KEY,
id_сотрудники INT NOT NULL REFERENCES сотрудники(id),
id_культуры INT NOT NULL REFERENCES культуры(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: сбор урожая
CREATE TABLE IF NOT EXISTS сбор_урожая (
id SERIAL PRIMARY KEY,
id_сельскохозяйственная_техника INT NOT NULL REFERENCES сельскохозяйственная_техника(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №29: Учёт в сельскохозяйственном предприятии
INSERT INTO поля (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO культуры (наименование, id_поля, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO сотрудники (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO сельскохозяйственная_техника (id_сотрудники, id_культуры, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO сельскохозяйственная_техника (id_сельскохозяйственная_техника, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №29: Учёт в сельскохозяйственном предприятии
-- 1. Простой запрос с условием: активные записи
SELECT * FROM сельскохозяйственная_техника WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM сельскохозяйственная_техника t4
JOIN сотрудники t3 ON t4.id_сотрудники = t3.id
JOIN культуры t2 ON t4.id_культуры = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM сельскохозяйственная_техника
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM сельскохозяйственная_техника
WHERE сумма > (SELECT AVG(сумма) FROM сельскохозяйственная_техника);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE сельскохозяйственная_техника SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Учёт в сельскохозяйственном предприятии»
### Назначение
База данных предназначена для автоматизации учёта в системе «Учёт в сельскохозяйственном предприятии». Хранит информацию в 5 взаимосвязанных таблицах: поля, культуры, сотрудники, сельскохозяйственная техника, сбор урожая.
### Структура базы данных
Таблица «поля» — основной справочник. Таблица «культуры» связана с первой по внешнему ключу. Таблица «сотрудники» хранит данные об участниках (клиентах/сотрудниках). Таблица «сельскохозяйственная техника» фиксирует транзакции/события. Таблица «сбор урожая» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (поля, культуры), затем добавляйте данные в сотрудники и сельскохозяйственная техника.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE сельскохозяйственная_техника SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (сбор урожая, сельскохозяйственная техника), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
Билет №30: Портал поиска работы и подбор персонала
1. Создание таблиц (DDL)
-- Билет №30: Портал поиска работы и подбор персонала
-- Таблицы: соискатели, работодатели, вакансии, резюме, отклики на вакансии
-- Таблица 1: соискатели
CREATE TABLE IF NOT EXISTS соискатели (
id SERIAL PRIMARY KEY,
название VARCHAR(150) NOT NULL,
описание TEXT,
дата_создания DATE DEFAULT CURRENT_DATE
);
-- Таблица 2: работодатели
CREATE TABLE IF NOT EXISTS работодатели (
id SERIAL PRIMARY KEY,
наименование VARCHAR(150) NOT NULL,
id_соискатели INT REFERENCES соискатели(id),
дополнение VARCHAR(200)
);
-- Таблица 3: вакансии
CREATE TABLE IF NOT EXISTS вакансии (
id SERIAL PRIMARY KEY,
фамилия VARCHAR(50) NOT NULL,
имя VARCHAR(50) NOT NULL,
телефон VARCHAR(20),
email VARCHAR(100) UNIQUE
);
-- Таблица 4: резюме
CREATE TABLE IF NOT EXISTS резюме (
id SERIAL PRIMARY KEY,
id_вакансии INT NOT NULL REFERENCES вакансии(id),
id_работодатели INT NOT NULL REFERENCES работодатели(id),
дата DATE NOT NULL DEFAULT CURRENT_DATE,
сумма NUMERIC(12,2),
статус VARCHAR(50) DEFAULT 'Активен'
);
-- Таблица 5: отклики на вакансии
CREATE TABLE IF NOT EXISTS отклики_на_вакансии (
id SERIAL PRIMARY KEY,
id_резюме INT NOT NULL REFERENCES резюме(id),
описание VARCHAR(200) NOT NULL,
дата DATE NOT NULL DEFAULT CURRENT_DATE,
стоимость NUMERIC(10,2)
);
2. Тестовые данные (DML INSERT)
-- Тестовые данные для билета №30: Портал поиска работы и подбор персонала
INSERT INTO соискатели (название, описание) VALUES
('Запись 1-A', 'Описание первой записи'),
('Запись 1-B', 'Описание второй записи'),
('Запись 1-C', 'Описание третьей записи'),
('Запись 1-D', 'Описание четвёртой записи'),
('Запись 1-E', 'Описание пятой записи');
INSERT INTO работодатели (наименование, id_соискатели, дополнение) VALUES
('Элемент 2-A', 1, 'Доп. информация A'),
('Элемент 2-B', 2, 'Доп. информация B'),
('Элемент 2-C', 3, 'Доп. информация C'),
('Элемент 2-D', 4, 'Доп. информация D'),
('Элемент 2-E', 5, 'Доп. информация E');
INSERT INTO вакансии (фамилия, имя, телефон, email) VALUES
('Иванов', 'Алексей', '+79001111111', 'ivanov@mail.ru'),
('Петрова', 'Мария', '+79002222222', 'petrova@mail.ru'),
('Сидоров', 'Дмитрий', '+79003333333', 'sidorov@mail.ru'),
('Козлова', 'Анна', '+79004444444', 'kozlova@mail.ru'),
('Новиков', 'Сергей', '+79005555555', 'novikov@mail.ru');
INSERT INTO резюме (id_вакансии, id_работодатели, дата, сумма, статус) VALUES
(1, 1, '2024-01-05', 1500.00, 'Активен'),
(2, 2, '2024-01-07', 2800.00, 'Завершён'),
(3, 3, '2024-01-10', 3200.00, 'Активен'),
(4, 4, '2024-01-12', 950.00, 'Ожидание'),
(5, 5, '2024-01-15', 4750.00, 'Активен');
INSERT INTO резюме (id_резюме, описание, дата, стоимость) VALUES
(1, 'Доп. услуга A', '2024-01-05', 200),
(2, 'Доп. услуга B', '2024-01-07', 150),
(3, 'Доп. услуга C', '2024-01-10', 300),
(4, 'Доп. услуга D', '2024-01-12', 100),
(5, 'Доп. услуга E', '2024-01-15', 250);
3. SQL-запросы
-- SQL-запросы для билета №30: Портал поиска работы и подбор персонала
-- 1. Простой запрос с условием: активные записи
SELECT * FROM резюме WHERE статус = 'Активен';
-- 2. Запрос с соединением таблиц
SELECT t3.фамилия, t3.имя, t2.наименование, t4.дата, t4.сумма
FROM резюме t4
JOIN вакансии t3 ON t4.id_вакансии = t3.id
JOIN работодатели t2 ON t4.id_работодатели = t2.id;
-- 3. Группировка с агрегатной функцией: сумма и количество по статусу
SELECT статус, COUNT(*) AS кол_во, SUM(сумма) AS итого, ROUND(AVG(сумма),2) AS средняя
FROM резюме
GROUP BY статус ORDER BY итого DESC;
-- 4. Подзапрос: записи с суммой выше средней
SELECT * FROM резюме
WHERE сумма > (SELECT AVG(сумма) FROM резюме);
-- 5. Обновление данных: изменить статус ожидающих записей
UPDATE резюме SET статус = 'Активен' WHERE статус = 'Ожидание';
4. Руководство пользователя
## Руководство пользователя: БД «Портал поиска работы и подбор персонала»
### Назначение
База данных предназначена для автоматизации учёта в системе «Портал поиска работы и подбор персонала». Хранит информацию в 5 взаимосвязанных таблицах: соискатели, работодатели, вакансии, резюме, отклики на вакансии.
### Структура базы данных
Таблица «соискатели» — основной справочник. Таблица «работодатели» связана с первой по внешнему ключу. Таблица «вакансии» хранит данные об участниках (клиентах/сотрудниках). Таблица «резюме» фиксирует транзакции/события. Таблица «отклики на вакансии» содержит детализацию событий.
### Основные операции
**Добавление записи:** начните с INSERT в справочные таблицы (соискатели, работодатели), затем добавляйте данные в вакансии и резюме.
**Просмотр данных:** используйте SELECT с JOIN для получения связанных данных из нескольких таблиц.
**Изменение статуса:** UPDATE резюме SET статус = '...' WHERE id = ...
**Фильтрация:** добавляйте WHERE с нужным условием к любому запросу SELECT.
**Удаление:** выполняйте DELETE сначала из зависимых таблиц (отклики на вакансии, резюме), затем из основных.
### Рекомендации
Всегда указывайте условие WHERE при UPDATE и DELETE. Используйте транзакции (BEGIN / COMMIT) при выполнении нескольких связанных операций.
