https://pastein.ru/t/vBB
Загрузка данных
Билет №1
Предметная область: Успеваемость студентов ВУЗА
Таблицы: факультеты, кафедры, учебные группы, студенты, ведомости успеваемости.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Успеваемость студентов ВУЗА
-- Задание 1. Создание таблиц и отношений
CREATE TABLE faculties ( -- факультеты
faculty_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
dean VARCHAR(100) NOT NULL
);
CREATE TABLE chairs ( -- кафедры
chair_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
faculty_id INT NOT NULL,
FOREIGN KEY (faculty_id) REFERENCES faculties(faculty_id)
);
CREATE TABLE groups_study ( -- учебные группы
group_id INT PRIMARY KEY,
code VARCHAR(20) NOT NULL UNIQUE,
course INT NOT NULL CHECK (course BETWEEN 1 AND 6),
chair_id INT NOT NULL,
FOREIGN KEY (chair_id) REFERENCES chairs(chair_id)
);
CREATE TABLE students ( -- студенты
student_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
birth_date DATE NOT NULL,
group_id INT NOT NULL,
FOREIGN KEY (group_id) REFERENCES groups_study(group_id)
);
CREATE TABLE records_progress ( -- ведомости успеваемости
record_id INT PRIMARY KEY,
student_id INT NOT NULL,
subject VARCHAR(80) NOT NULL,
grade INT NOT NULL CHECK (grade BETWEEN 2 AND 5),
exam_date DATE NOT NULL,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
-- Задание 2. Тестовые данные
INSERT INTO faculties VALUES
(1,'Информационных технологий','Смирнов А.В.'),
(2,'Экономический','Кузнецова Е.П.'),
(3,'Юридический','Попов Д.С.'),
(4,'Инженерный','Васильев И.Н.'),
(5,'Гуманитарный','Морозова О.А.');
INSERT INTO chairs VALUES
(1,'Программной инженерии',1),
(2,'Сетевых технологий',1),
(3,'Бухгалтерского учета',2),
(4,'Гражданского права',3),
(5,'Машиностроения',4);
INSERT INTO groups_study VALUES
(1,'ПИ-21',2,1),
(2,'ПИ-31',3,1),
(3,'СТ-21',2,2),
(4,'БУ-11',1,3),
(5,'ГП-41',4,4);
INSERT INTO students VALUES
(1,'Иванов Иван Иванович','2004-03-12',1),
(2,'Петров Петр Петрович','2003-07-25',1),
(3,'Сидорова Анна Олеговна','2004-01-30',2),
(4,'Кузьмин Олег Игоревич','2002-11-05',3),
(5,'Новикова Мария Сергеевна','2005-09-18',4);
INSERT INTO records_progress VALUES
(1,1,'Базы данных',5,'2025-01-15'),
(2,1,'Математика',4,'2025-01-20'),
(3,2,'Базы данных',3,'2025-01-15'),
(4,3,'Программирование',5,'2025-01-18'),
(5,4,'Бухучет',4,'2025-01-22');
-- Задание 3. Запросы
-- 1) Простой запрос на выборку с условием: студенты, родившиеся после 2004 года
SELECT full_name, birth_date FROM students WHERE birth_date > '2004-01-01';
-- 2) Соединение таблиц: студент и его группа с факультетом
SELECT s.full_name, g.code, f.name AS faculty
FROM students s
JOIN groups_study g ON s.group_id = g.group_id
JOIN chairs c ON g.chair_id = c.chair_id
JOIN faculties f ON c.faculty_id = f.faculty_id;
-- 3) Группировка и агрегатная функция: средний балл по каждому студенту
SELECT s.full_name, AVG(r.grade) AS avg_grade
FROM students s JOIN records_progress r ON s.student_id = r.student_id
GROUP BY s.full_name;
-- 4) Подзапрос: студенты с баллом выше среднего по всем оценкам
SELECT DISTINCT s.full_name FROM students s
JOIN records_progress r ON s.student_id = r.student_id
WHERE r.grade > (SELECT AVG(grade) FROM records_progress);
-- 5) Обновление данных: исправить оценку 3 на 4 по предмету 'Базы данных'
UPDATE records_progress SET grade = 4 WHERE subject = 'Базы данных' AND grade = 3;
Задание 4. Краткое руководство пользователя
Руководство пользователя. База данных предназначена для учета успеваемости студентов ВУЗА. Сначала заполняются справочники: факультеты, затем кафедры (с указанием факультета), учебные группы (с указанием кафедры). После этого вносятся студенты с привязкой к группе и оценки в ведомости успеваемости. Для получения среднего балла используйте запрос с группировкой по студенту. Связи между таблицами защищены внешними ключами: нельзя добавить студента в несуществующую группу.
Билет №2
Предметная область: Информационная система супермаркета
Таблицы: отделы, сотрудники, товары, продажа товаров, должности.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Информационная система супермаркета
CREATE TABLE departments ( -- отделы
dept_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE,
floor INT NOT NULL
);
CREATE TABLE positions ( -- должности
position_id INT PRIMARY KEY,
title VARCHAR(80) NOT NULL UNIQUE,
salary DECIMAL(10,2) NOT NULL CHECK (salary > 0)
);
CREATE TABLE employees ( -- сотрудники
emp_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
dept_id INT NOT NULL,
position_id INT NOT NULL,
hire_date DATE NOT NULL,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
FOREIGN KEY (position_id) REFERENCES positions(position_id)
);
CREATE TABLE goods ( -- товары
goods_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
dept_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
CREATE TABLE sales ( -- продажа товаров
sale_id INT PRIMARY KEY,
goods_id INT NOT NULL,
emp_id INT NOT NULL,
qty INT NOT NULL CHECK (qty > 0),
sale_date DATE NOT NULL,
FOREIGN KEY (goods_id) REFERENCES goods(goods_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
INSERT INTO departments VALUES
(1,'Продукты',1),(2,'Бытовая химия',1),(3,'Электроника',2),
(4,'Одежда',2),(5,'Канцтовары',1);
INSERT INTO positions VALUES
(1,'Кассир',35000),(2,'Продавец',32000),(3,'Менеджер',55000),
(4,'Грузчик',30000),(5,'Администратор',60000);
INSERT INTO employees VALUES
(1,'Орлов Сергей','2022-05-10',1,1,'2022-05-10'),
(2,'Белова Ирина','2021-03-01',1,2,'2021-03-01'),
(3,'Гусев Антон','2020-09-15',3,3,'2020-09-15'),
(4,'Зайцев Роман','2023-01-20',2,4,'2023-01-20'),
(5,'Лебедева Ольга','2019-11-11',4,5,'2019-11-11');
INSERT INTO goods VALUES
(1,'Молоко 1л',1,75.50),(2,'Стиральный порошок',2,320.00),
(3,'Наушники',3,1990.00),(4,'Футболка',4,890.00),(5,'Тетрадь 48л',5,45.00);
INSERT INTO sales VALUES
(1,1,1,10,'2025-02-01'),(2,3,3,2,'2025-02-01'),
(3,2,2,5,'2025-02-02'),(4,1,1,7,'2025-02-03'),(5,4,2,3,'2025-02-03');
-- Запросы
-- 1) Выборка с условием: товары дороже 500 руб.
SELECT name, price FROM goods WHERE price > 500;
-- 2) Соединение: какой сотрудник продал какой товар
SELECT e.full_name, g.name, s.qty
FROM sales s JOIN employees e ON s.emp_id = e.emp_id
JOIN goods g ON s.goods_id = g.goods_id;
-- 3) Группировка: суммарная выручка по каждому товару
SELECT g.name, SUM(g.price * s.qty) AS revenue
FROM goods g JOIN sales s ON g.goods_id = s.goods_id
GROUP BY g.name;
-- 4) Подзапрос: сотрудники на должностях с зарплатой выше средней
SELECT full_name FROM employees WHERE position_id IN
(SELECT position_id FROM positions WHERE salary > (SELECT AVG(salary) FROM positions));
-- 5) Обновление: повысить цену товаров отдела 'Электроника' на 10%
UPDATE goods SET price = price * 1.10
WHERE dept_id = (SELECT dept_id FROM departments WHERE name = 'Электроника');
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система автоматизирует учет товаров, сотрудников и продаж супермаркета. Заполните справочники отделов и должностей, затем сотрудников и товары (с привязкой к отделу). Продажи фиксируются с указанием товара, кассира и количества. Отчет о выручке формируется запросом с группировкой по товару. Внешние ключи гарантируют целостность данных.
Билет №3
Предметная область: Информационная система военного округа
Таблицы: места дислокации, вид войск, части, роты, личный состав.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Информационная система военного округа
CREATE TABLE locations ( -- места дислокации
location_id INT PRIMARY KEY,
city VARCHAR(80) NOT NULL,
region VARCHAR(80) NOT NULL
);
CREATE TABLE troop_types ( -- вид войск
type_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
);
CREATE TABLE units ( -- части
unit_id INT PRIMARY KEY,
number VARCHAR(20) NOT NULL UNIQUE,
type_id INT NOT NULL,
location_id INT NOT NULL,
FOREIGN KEY (type_id) REFERENCES troop_types(type_id),
FOREIGN KEY (location_id) REFERENCES locations(location_id)
);
CREATE TABLE companies_mil ( -- роты
company_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
unit_id INT NOT NULL,
FOREIGN KEY (unit_id) REFERENCES units(unit_id)
);
CREATE TABLE personnel ( -- личный состав
person_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
rank VARCHAR(40) NOT NULL,
company_id INT NOT NULL,
FOREIGN KEY (company_id) REFERENCES companies_mil(company_id)
);
INSERT INTO locations VALUES
(1,'Вологда','Вологодская обл.'),(2,'Череповец','Вологодская обл.'),
(3,'Псков','Псковская обл.'),(4,'Тверь','Тверская обл.'),(5,'Курск','Курская обл.');
INSERT INTO troop_types VALUES
(1,'Мотострелковые'),(2,'Артиллерия'),(3,'Связь'),(4,'Инженерные'),(5,'ВДВ');
INSERT INTO units VALUES
(1,'в/ч 12345',1,1),(2,'в/ч 23456',2,2),(3,'в/ч 34567',3,3),
(4,'в/ч 45678',5,4),(5,'в/ч 56789',4,5);
INSERT INTO companies_mil VALUES
(1,'1-я рота',1),(2,'2-я рота',1),(3,'1-я рота',2),
(4,'1-я рота',4),(5,'3-я рота',3);
INSERT INTO personnel VALUES
(1,'Громов Алексей','капитан',1),
(2,'Соколов Дмитрий','сержант',1),
(3,'Федоров Илья','лейтенант',3),
(4,'Михайлов Юрий','рядовой',4),
(5,'Тихонов Глеб','майор',5);
-- Запросы
-- 1) Выборка с условием: личный состав в звании 'майор'
SELECT full_name, rank FROM personnel WHERE rank = 'майор';
-- 2) Соединение: часть, вид войск и место дислокации
SELECT u.number, t.name AS troop_type, l.city
FROM units u JOIN troop_types t ON u.type_id = t.type_id
JOIN locations l ON u.location_id = l.location_id;
-- 3) Группировка: число военнослужащих в каждой роте
SELECT c.name, COUNT(p.person_id) AS soldiers
FROM companies_mil c LEFT JOIN personnel p ON c.company_id = p.company_id
GROUP BY c.name;
-- 4) Подзапрос: части, дислоцированные в Вологодской области
SELECT number FROM units WHERE location_id IN
(SELECT location_id FROM locations WHERE region = 'Вологодская обл.');
-- 5) Обновление: присвоить рядовому person_id=4 звание 'ефрейтор'
UPDATE personnel SET rank = 'ефрейтор' WHERE person_id = 4;
Задание 4. Краткое руководство пользователя
Руководство пользователя. БД учитывает структуру военного округа: места дислокации, виды войск, части, роты и личный состав. Сначала вносятся места дислокации и виды войск, затем части (привязка к виду войск и месту), роты и личный состав. Запрос с группировкой показывает численность каждой роты. Целостность обеспечена внешними ключами.
Билет №4
Предметная область: Информационная система туристического агентства
Таблицы: пансионаты, туры, клиенты, путевки, вид жилья.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Информационная система туристического агентства
CREATE TABLE pensions ( -- пансионаты
pension_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
country VARCHAR(60) NOT NULL,
stars INT CHECK (stars BETWEEN 1 AND 5)
);
CREATE TABLE housing_types ( -- вид жилья
housing_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE tours ( -- туры
tour_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
pension_id INT NOT NULL,
days INT NOT NULL CHECK (days > 0),
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (pension_id) REFERENCES pensions(pension_id)
);
CREATE TABLE clients ( -- клиенты
client_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE vouchers ( -- путевки
voucher_id INT PRIMARY KEY,
tour_id INT NOT NULL,
client_id INT NOT NULL,
housing_id INT NOT NULL,
buy_date DATE NOT NULL,
FOREIGN KEY (tour_id) REFERENCES tours(tour_id),
FOREIGN KEY (client_id) REFERENCES clients(client_id),
FOREIGN KEY (housing_id) REFERENCES housing_types(housing_id)
);
INSERT INTO pensions VALUES
(1,'Солнечный берег','Турция',5),(2,'Морская звезда','Египет',4),
(3,'Альпийский','Австрия',5),(4,'Лазурный','Греция',3),(5,'Прибой','Россия',4);
INSERT INTO housing_types VALUES
(1,'Стандарт'),(2,'Люкс'),(3,'Полулюкс'),(4,'Апартаменты'),(5,'Семейный номер');
INSERT INTO tours VALUES
(1,'Турция все включено',1,10,65000),(2,'Дайвинг в Египте',2,7,55000),
(3,'Горные лыжи',3,12,120000),(4,'Острова Греции',4,9,70000),
(5,'Черноморье',5,14,40000);
INSERT INTO clients VALUES
(1,'Антонова Вера','+7900-111-22-33'),(2,'Борисов Глеб','+7900-222-33-44'),
(3,'Власова Нина','+7900-333-44-55'),(4,'Гордеев Иван','+7900-444-55-66'),
(5,'Дроздова Алла','+7900-555-66-77');
INSERT INTO vouchers VALUES
(1,1,1,2,'2025-03-01'),(2,3,2,1,'2025-03-02'),
(3,1,3,5,'2025-03-05'),(4,5,4,1,'2025-03-06'),(5,4,5,3,'2025-03-07');
-- Запросы
-- 1) Выборка с условием: туры дороже 60000 руб.
SELECT name, price FROM tours WHERE price > 60000;
-- 2) Соединение: клиент, его тур и вид жилья
SELECT cl.full_name, t.name AS tour, h.name AS housing
FROM vouchers v JOIN clients cl ON v.client_id = cl.client_id
JOIN tours t ON v.tour_id = t.tour_id
JOIN housing_types h ON v.housing_id = h.housing_id;
-- 3) Группировка: число проданных путевок по каждому туру
SELECT t.name, COUNT(v.voucher_id) AS sold
FROM tours t LEFT JOIN vouchers v ON t.tour_id = v.tour_id
GROUP BY t.name;
-- 4) Подзапрос: туры в пансионатах 5 звезд
SELECT name FROM tours WHERE pension_id IN
(SELECT pension_id FROM pensions WHERE stars = 5);
-- 5) Обновление: скидка 5% на тур 'Черноморье'
UPDATE tours SET price = price * 0.95 WHERE name = 'Черноморье';
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система предназначена для работы турагентства. Заполняются справочники пансионатов и видов жилья, формируются туры, регистрируются клиенты. Продажа оформляется как путевка (тур + клиент + вид жилья). Запрос с группировкой показывает популярность туров по количеству проданных путевок.
Билет №5
Предметная область: Информационная система автопредприятия города
Таблицы: автотранспорт, водители, маршруты, обслуживающий персонал, гаражное хозяйство.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Информационная система автопредприятия города
CREATE TABLE garages ( -- гаражное хозяйство
garage_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
address VARCHAR(120) NOT NULL,
capacity INT NOT NULL CHECK (capacity > 0)
);
CREATE TABLE vehicles ( -- автотранспорт
vehicle_id INT PRIMARY KEY,
plate VARCHAR(15) NOT NULL UNIQUE,
model VARCHAR(60) NOT NULL,
garage_id INT NOT NULL,
FOREIGN KEY (garage_id) REFERENCES garages(garage_id)
);
CREATE TABLE drivers ( -- водители
driver_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
category VARCHAR(10) NOT NULL,
experience INT NOT NULL CHECK (experience >= 0)
);
CREATE TABLE routes ( -- маршруты
route_id INT PRIMARY KEY,
number VARCHAR(10) NOT NULL,
vehicle_id INT NOT NULL,
driver_id INT NOT NULL,
length_km DECIMAL(6,1) NOT NULL,
FOREIGN KEY (vehicle_id) REFERENCES vehicles(vehicle_id),
FOREIGN KEY (driver_id) REFERENCES drivers(driver_id)
);
CREATE TABLE service_staff ( -- обслуживающий персонал
staff_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
job VARCHAR(60) NOT NULL,
garage_id INT NOT NULL,
FOREIGN KEY (garage_id) REFERENCES garages(garage_id)
);
INSERT INTO garages VALUES
(1,'Гараж №1','ул. Заводская, 5',50),(2,'Гараж №2','ул. Северная, 12',30),
(3,'Гараж №3','пр. Победы, 40',40),(4,'Гараж №4','ул. Мира, 8',25),
(5,'Гараж №5','ул. Гагарина, 3',60);
INSERT INTO vehicles VALUES
(1,'А123ВС35','ЛиАЗ-5292',1),(2,'В234СК35','ПАЗ-3204',1),
(3,'Е345МН35','МАЗ-203',2),(4,'К456ОР35','НефАЗ-5299',3),(5,'М567ТУ35','ЛиАЗ-4292',5);
INSERT INTO drivers VALUES
(1,'Карпов Олег','D',12),(2,'Лосев Игорь','D',8),(3,'Мухин Артем','D',20),
(4,'Носов Вадим','D',3),(5,'Орехов Семен','D',15);
INSERT INTO routes VALUES
(1,'5',1,1,18.5),(2,'12',2,2,22.0),(3,'7',3,3,15.0),
(4,'3',4,4,30.5),(5,'9',5,5,12.0);
INSERT INTO service_staff VALUES
(1,'Панов Кирилл','механик',1),(2,'Репин Денис','слесарь',1),
(3,'Седов Павел','электрик',2),(4,'Титов Марк','мойщик',3),
(5,'Уваров Лев','механик',5);
-- Запросы
-- 1) Выборка с условием: водители со стажем более 10 лет
SELECT full_name, experience FROM drivers WHERE experience > 10;
-- 2) Соединение: маршрут, автобус и водитель
SELECT r.number, v.model, d.full_name
FROM routes r JOIN vehicles v ON r.vehicle_id = v.vehicle_id
JOIN drivers d ON r.driver_id = d.driver_id;
-- 3) Группировка: количество машин в каждом гараже
SELECT g.name, COUNT(v.vehicle_id) AS cars
FROM garages g LEFT JOIN vehicles v ON g.garage_id = v.garage_id
GROUP BY g.name;
-- 4) Подзапрос: водители, обслуживающие маршруты длиннее средней длины
SELECT full_name FROM drivers WHERE driver_id IN
(SELECT driver_id FROM routes WHERE length_km > (SELECT AVG(length_km) FROM routes));
-- 5) Обновление: увеличить стаж всех водителей на 1 год
UPDATE drivers SET experience = experience + 1;
Задание 4. Краткое руководство пользователя
Руководство пользователя. БД автопредприятия учитывает гаражи, транспорт, водителей, маршруты и обслуживающий персонал. Сначала заводятся гаражи, затем транспорт и персонал (с привязкой к гаражу), водители и маршруты. Запрос с группировкой показывает загрузку гаражей. Внешние ключи не позволяют закрепить транспорт за несуществующим гаражом.
Билет №6
Предметная область: Информационная система поликлиники
Таблицы: врачи, пациенты, история болезней, отделения, обслуживающий персонал.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Информационная система поликлиники
CREATE TABLE divisions ( -- отделения
division_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE,
floor INT NOT NULL
);
CREATE TABLE doctors ( -- врачи
doctor_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
specialty VARCHAR(80) NOT NULL,
division_id INT NOT NULL,
FOREIGN KEY (division_id) REFERENCES divisions(division_id)
);
CREATE TABLE patients ( -- пациенты
patient_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
birth_date DATE NOT NULL,
policy VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE service_staff ( -- обслуживающий персонал
staff_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
job VARCHAR(60) NOT NULL,
division_id INT NOT NULL,
FOREIGN KEY (division_id) REFERENCES divisions(division_id)
);
CREATE TABLE case_history ( -- история болезней
case_id INT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
diagnosis VARCHAR(120) NOT NULL,
visit_date DATE NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);
INSERT INTO divisions VALUES
(1,'Терапия',1),(2,'Хирургия',2),(3,'Кардиология',3),
(4,'Неврология',2),(5,'Офтальмология',1);
INSERT INTO doctors VALUES
(1,'Авдеев Роман','терапевт',1),(2,'Беляев Олег','хирург',2),
(3,'Власов Игорь','кардиолог',3),(4,'Громова Анна','невролог',4),
(5,'Дубова Елена','офтальмолог',5);
INSERT INTO patients VALUES
(1,'Зимин Артур','1990-04-10','POL0000001'),
(2,'Иванова Лидия','1985-12-22','POL0000002'),
(3,'Котов Денис','2000-06-15','POL0000003'),
(4,'Леонова Вера','1978-09-30','POL0000004'),
(5,'Минин Сергей','1995-01-05','POL0000005');
INSERT INTO service_staff VALUES
(1,'Орлова Тамара','медсестра',1),(2,'Попова Раиса','медсестра',2),
(3,'Рыков Глеб','санитар',3),(4,'Седова Инна','регистратор',1),
(5,'Тихая Юлия','медсестра',5);
INSERT INTO case_history VALUES
(1,1,1,'ОРВИ','2025-02-10'),(2,2,3,'Гипертония','2025-02-11'),
(3,3,2,'Аппендицит','2025-02-12'),(4,4,4,'Мигрень','2025-02-13'),
(5,1,1,'Бронхит','2025-03-01');
-- Запросы
-- 1) Выборка с условием: пациенты, родившиеся раньше 1990 года
SELECT full_name, birth_date FROM patients WHERE birth_date < '1990-01-01';
-- 2) Соединение: пациент, врач и диагноз
SELECT p.full_name AS patient, d.full_name AS doctor, ch.diagnosis
FROM case_history ch JOIN patients p ON ch.patient_id = p.patient_id
JOIN doctors d ON ch.doctor_id = d.doctor_id;
-- 3) Группировка: количество приемов у каждого врача
SELECT d.full_name, COUNT(ch.case_id) AS visits
FROM doctors d LEFT JOIN case_history ch ON d.doctor_id = ch.doctor_id
GROUP BY d.full_name;
-- 4) Подзапрос: врачи отделения 'Терапия'
SELECT full_name FROM doctors WHERE division_id =
(SELECT division_id FROM divisions WHERE name = 'Терапия');
-- 5) Обновление: перевести врача doctor_id=1 в отделение 'Кардиология'
UPDATE doctors SET division_id =
(SELECT division_id FROM divisions WHERE name = 'Кардиология') WHERE doctor_id = 1;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система поликлиники ведет учет отделений, врачей, пациентов, персонала и истории болезней. Заполните отделения, затем врачей и персонал (с привязкой к отделению) и пациентов. Каждый прием регистрируется в истории болезней. Запрос с группировкой показывает нагрузку на врачей.
Билет №7
Предметная область: Информационная система больницы
Таблицы: врачи, пациенты, история болезней, операции, лист лечения.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Информационная система больницы
CREATE TABLE doctors ( -- врачи
doctor_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
specialty VARCHAR(80) NOT NULL
);
CREATE TABLE patients ( -- пациенты
patient_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
ward INT NOT NULL
);
CREATE TABLE case_history ( -- история болезней
case_id INT PRIMARY KEY,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
diagnosis VARCHAR(120) NOT NULL,
admit_date DATE NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);
CREATE TABLE operations ( -- операции
operation_id INT PRIMARY KEY,
case_id INT NOT NULL,
name VARCHAR(120) NOT NULL,
op_date DATE NOT NULL,
FOREIGN KEY (case_id) REFERENCES case_history(case_id)
);
CREATE TABLE treatment_list ( -- лист лечения
item_id INT PRIMARY KEY,
case_id INT NOT NULL,
medicine VARCHAR(100) NOT NULL,
dosage VARCHAR(40) NOT NULL,
FOREIGN KEY (case_id) REFERENCES case_history(case_id)
);
INSERT INTO doctors VALUES
(1,'Аникин Борис','хирург'),(2,'Власова Дина','терапевт'),
(3,'Гущин Олег','кардиолог'),(4,'Денисов Юрий','травматолог'),
(5,'Егорова Нина','анестезиолог');
INSERT INTO patients VALUES
(1,'Жуков Тимур',101),(2,'Зотова Алла',102),(3,'Карин Влад',103),
(4,'Лукин Петр',101),(5,'Мокеева Вера',104);
INSERT INTO case_history VALUES
(1,1,1,'Перелом руки','2025-02-01'),(2,2,3,'Стенокардия','2025-02-03'),
(3,3,1,'Аппендицит','2025-02-05'),(4,4,4,'Травма колена','2025-02-07'),
(5,5,2,'Пневмония','2025-02-09');
INSERT INTO operations VALUES
(1,1,'Остеосинтез','2025-02-02'),(2,3,'Аппендэктомия','2025-02-06'),
(3,4,'Артроскопия','2025-02-08'),(4,3,'Перевязка','2025-02-09'),
(5,1,'Снятие гипса','2025-03-01');
INSERT INTO treatment_list VALUES
(1,2,'Аспирин','100 мг/сут'),(2,5,'Амоксициллин','500 мг x3'),
(3,1,'Кеторол','30 мг'),(4,3,'Цефтриаксон','1 г x2'),(5,4,'Найз','100 мг');
-- Запросы
-- 1) Выборка с условием: пациенты палаты 101
SELECT full_name, ward FROM patients WHERE ward = 101;
-- 2) Соединение: пациент, диагноз и оперирующий врач
SELECT p.full_name, ch.diagnosis, d.full_name AS doctor
FROM case_history ch JOIN patients p ON ch.patient_id = p.patient_id
JOIN doctors d ON ch.doctor_id = d.doctor_id;
-- 3) Группировка: число операций по каждой истории болезни
SELECT ch.diagnosis, COUNT(o.operation_id) AS ops
FROM case_history ch LEFT JOIN operations o ON ch.case_id = o.case_id
GROUP BY ch.diagnosis;
-- 4) Подзапрос: пациенты, которым проводились операции
SELECT full_name FROM patients WHERE patient_id IN
(SELECT patient_id FROM case_history WHERE case_id IN (SELECT case_id FROM operations));
-- 5) Обновление: перевести пациента patient_id=2 в палату 105
UPDATE patients SET ward = 105 WHERE patient_id = 2;
Задание 4. Краткое руководство пользователя
Руководство пользователя. БД больницы хранит врачей, пациентов, истории болезней, операции и листы лечения. Сначала вносятся врачи и пациенты, затем заводится история болезни, к которой привязываются операции и назначения. Запрос с группировкой подсчитывает число операций по каждому случаю.
Билет №8
Предметная область: Информационная система библиотек города
Таблицы: библиотеки, читальные залы, литература, читатели, выдача литературы.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Информационная система библиотек города
CREATE TABLE libraries ( -- библиотеки
library_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
address VARCHAR(120) NOT NULL
);
CREATE TABLE reading_halls ( -- читальные залы
hall_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
library_id INT NOT NULL,
seats INT NOT NULL CHECK (seats > 0),
FOREIGN KEY (library_id) REFERENCES libraries(library_id)
);
CREATE TABLE books ( -- литература
book_id INT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
author VARCHAR(100) NOT NULL,
library_id INT NOT NULL,
year_pub INT,
FOREIGN KEY (library_id) REFERENCES libraries(library_id)
);
CREATE TABLE readers ( -- читатели
reader_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
card_no VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE loans ( -- выдача литературы
loan_id INT PRIMARY KEY,
book_id INT NOT NULL,
reader_id INT NOT NULL,
loan_date DATE NOT NULL,
return_date DATE,
FOREIGN KEY (book_id) REFERENCES books(book_id),
FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
);
INSERT INTO libraries VALUES
(1,'Центральная','ул. Ленина, 1'),(2,'Юношеская','ул. Мира, 14'),
(3,'Техническая','пр. Победы, 7'),(4,'Детская','ул. Садовая, 22'),
(5,'Научная','ул. Пушкина, 9');
INSERT INTO reading_halls VALUES
(1,'Общий зал',1,50),(2,'Периодика',1,20),(3,'Зал техники',3,30),
(4,'Детский зал',4,25),(5,'Научный зал',5,40);
INSERT INTO books VALUES
(1,'Война и мир','Толстой Л.Н.',1,1869),(2,'Базы данных','Дейт К.',3,2005),
(3,'Сказки','Пушкин А.С.',4,1990),(4,'Алгоритмы','Кормен Т.',5,2013),
(5,'Преступление и наказание','Достоевский Ф.М.',1,1866);
INSERT INTO readers VALUES
(1,'Абрамов Иван','RD0001'),(2,'Бортова Ольга','RD0002'),
(3,'Веселов Глеб','RD0003'),(4,'Громова Яна','RD0004'),(5,'Дятлов Роман','RD0005');
INSERT INTO loans VALUES
(1,1,1,'2025-02-01','2025-02-15'),(2,2,2,'2025-02-03',NULL),
(3,4,3,'2025-02-05','2025-02-20'),(4,1,4,'2025-02-10',NULL),
(5,5,1,'2025-02-12',NULL);
-- Запросы
-- 1) Выборка с условием: книги, изданные после 2000 года
SELECT title, year_pub FROM books WHERE year_pub > 2000;
-- 2) Соединение: читатель и взятые им книги
SELECT r.full_name, b.title, l.loan_date
FROM loans l JOIN readers r ON l.reader_id = r.reader_id
JOIN books b ON l.book_id = b.book_id;
-- 3) Группировка: количество книг в каждой библиотеке
SELECT lib.name, COUNT(b.book_id) AS books_cnt
FROM libraries lib LEFT JOIN books b ON lib.library_id = b.library_id
GROUP BY lib.name;
-- 4) Подзапрос: читатели, не вернувшие книги
SELECT full_name FROM readers WHERE reader_id IN
(SELECT reader_id FROM loans WHERE return_date IS NULL);
-- 5) Обновление: отметить возврат книги по выдаче loan_id=2
UPDATE loans SET return_date = '2025-03-01' WHERE loan_id = 2;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система объединяет библиотеки города. Заполняются библиотеки, их читальные залы и фонд литературы, регистрируются читатели. Выдача книги фиксируется записью в таблице выдачи; при возврате проставляется дата возврата. Запрос с подзапросом выводит должников (книги без даты возврата).
Билет №9
Предметная область: Успеваемость студентов кафедры
Таблицы: кафедры, дисциплины, преподаватели, студенты, ведомости успеваемости.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Успеваемость студентов кафедры
CREATE TABLE chairs ( -- кафедры
chair_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
head VARCHAR(100) NOT NULL
);
CREATE TABLE disciplines ( -- дисциплины
discipline_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
hours INT NOT NULL CHECK (hours > 0),
chair_id INT NOT NULL,
FOREIGN KEY (chair_id) REFERENCES chairs(chair_id)
);
CREATE TABLE teachers ( -- преподаватели
teacher_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
degree VARCHAR(60),
chair_id INT NOT NULL,
FOREIGN KEY (chair_id) REFERENCES chairs(chair_id)
);
CREATE TABLE students ( -- студенты
student_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
group_code VARCHAR(20) NOT NULL
);
CREATE TABLE records_progress ( -- ведомости успеваемости
record_id INT PRIMARY KEY,
student_id INT NOT NULL,
discipline_id INT NOT NULL,
teacher_id INT NOT NULL,
grade INT NOT NULL CHECK (grade BETWEEN 2 AND 5),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (discipline_id) REFERENCES disciplines(discipline_id),
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
INSERT INTO chairs VALUES
(1,'Информатики','Смирнов А.В.'),(2,'Математики','Кузнецов Б.П.'),
(3,'Физики','Попова Е.С.'),(4,'Экономики','Орлов Д.И.'),(5,'Лингвистики','Белова Н.К.');
INSERT INTO disciplines VALUES
(1,'Базы данных',144,1),(2,'Программирование',180,1),
(3,'Высшая математика',160,2),(4,'Физика',120,3),(5,'Экономика',90,4);
INSERT INTO teachers VALUES
(1,'Авдеева Мария','к.т.н.',1),(2,'Борисов Олег','д.ф-м.н.',2),
(3,'Власов Иван','к.ф-м.н.',3),(4,'Гусева Раиса','доцент',1),
(5,'Дронов Петр','к.э.н.',4);
INSERT INTO students VALUES
(1,'Зуев Артем','ПИ-21'),(2,'Ильина Оля','ПИ-21'),(3,'Котов Влад','ПИ-31'),
(4,'Лосева Яна','ИВТ-21'),(5,'Мельник Дан','ИВТ-21');
INSERT INTO records_progress VALUES
(1,1,1,1,5),(2,1,3,2,4),(3,2,1,1,3),(4,3,2,4,5),(5,4,4,3,4);
-- Запросы
-- 1) Выборка с условием: дисциплины объемом более 140 часов
SELECT name, hours FROM disciplines WHERE hours > 140;
-- 2) Соединение: студент, дисциплина и преподаватель
SELECT s.full_name, d.name AS discipline, t.full_name AS teacher, r.grade
FROM records_progress r JOIN students s ON r.student_id = s.student_id
JOIN disciplines d ON r.discipline_id = d.discipline_id
JOIN teachers t ON r.teacher_id = t.teacher_id;
-- 3) Группировка: средний балл по каждой дисциплине
SELECT d.name, AVG(r.grade) AS avg_grade
FROM disciplines d JOIN records_progress r ON d.discipline_id = r.discipline_id
GROUP BY d.name;
-- 4) Подзапрос: преподаватели кафедры 'Информатики'
SELECT full_name FROM teachers WHERE chair_id =
(SELECT chair_id FROM chairs WHERE name = 'Информатики');
-- 5) Обновление: пересдача — поднять оценку 3 до 4 по дисциплине 'Базы данных'
UPDATE records_progress SET grade = 4
WHERE grade = 3 AND discipline_id = (SELECT discipline_id FROM disciplines WHERE name='Базы данных');
Задание 4. Краткое руководство пользователя
Руководство пользователя. БД учитывает успеваемость студентов кафедры. Заполняются кафедры, дисциплины и преподаватели (привязка к кафедре), студенты. Оценки заносятся в ведомость с указанием студента, дисциплины и преподавателя. Запрос с группировкой считает средний балл по дисциплинам.
Билет №10
Предметная область: Интернет-магазин электроники
Таблицы: категории товаров, товары, покупатели, заказы, составы заказов.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Интернет-магазин электроники
CREATE TABLE categories ( -- категории товаров
category_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
);
CREATE TABLE goods ( -- товары
goods_id INT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price > 0),
stock INT NOT NULL DEFAULT 0,
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
CREATE TABLE customers ( -- покупатели
customer_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE orders ( -- заказы
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(30) NOT NULL DEFAULT 'новый',
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items ( -- составы заказов
item_id INT PRIMARY KEY,
order_id INT NOT NULL,
goods_id INT NOT NULL,
qty INT NOT NULL CHECK (qty > 0),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);
INSERT INTO categories VALUES
(1,'Смартфоны'),(2,'Ноутбуки'),(3,'Наушники'),(4,'Телевизоры'),(5,'Аксессуары');
INSERT INTO goods VALUES
(1,'Смартфон Galaxy A55',1,32990,15),(2,'Ноутбук Aspire 5',2,54990,8),
(3,'Наушники AirBuds',3,4990,40),(4,'Телевизор LED 50"',4,39990,5),
(5,'Чехол силиконовый',5,590,100);
INSERT INTO customers VALUES
(1,'Антонов Илья','anton@mail.ru'),(2,'Белкина Юля','belka@mail.ru'),
(3,'Власов Рома','vlasov@mail.ru'),(4,'Гордеева Аня','goran@mail.ru'),
(5,'Дёмин Олег','demin@mail.ru');
INSERT INTO orders VALUES
(1,1,'2025-03-01','доставлен'),(2,2,'2025-03-02','новый'),
(3,1,'2025-03-05','в пути'),(4,3,'2025-03-06','новый'),(5,4,'2025-03-07','доставлен');
INSERT INTO order_items VALUES
(1,1,1,1),(2,1,5,2),(3,2,3,1),(4,3,2,1),(5,5,4,1);
-- Запросы
-- 1) Выборка с условием: товары дешевле 10000 руб.
SELECT name, price FROM goods WHERE price < 10000;
-- 2) Соединение: заказ, покупатель и товары
SELECT o.order_id, c.full_name, g.name, oi.qty
FROM order_items oi JOIN orders o ON oi.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
JOIN goods g ON oi.goods_id = g.goods_id;
-- 3) Группировка: сумма каждого заказа
SELECT o.order_id, SUM(g.price * oi.qty) AS total
FROM orders o JOIN order_items oi ON o.order_id = oi.order_id
JOIN goods g ON oi.goods_id = g.goods_id
GROUP BY o.order_id;
-- 4) Подзапрос: товары категории 'Смартфоны'
SELECT name FROM goods WHERE category_id =
(SELECT category_id FROM categories WHERE name = 'Смартфоны');
-- 5) Обновление: пометить заказ order_id=2 как 'отменен'
UPDATE orders SET status = 'отменен' WHERE order_id = 2;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Интернет-магазин электроники ведет каталог по категориям, учет покупателей и заказов. Заполните категории и товары, зарегистрируйте покупателей. Заказ состоит из позиций (товар + количество). Сумма заказа вычисляется запросом с группировкой по заказу. Статус заказа изменяется операцией UPDATE.
Билет №11
Предметная область: Поликлиника и расписание приемов
Таблицы: отделения, врачи, пациенты, талоны на прием, медицинские заключения.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Поликлиника и расписание приемов
CREATE TABLE divisions ( -- отделения
division_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
);
CREATE TABLE doctors ( -- врачи
doctor_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
specialty VARCHAR(80) NOT NULL,
division_id INT NOT NULL,
FOREIGN KEY (division_id) REFERENCES divisions(division_id)
);
CREATE TABLE patients ( -- пациенты
patient_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE tickets ( -- талоны на прием
ticket_id INT PRIMARY KEY,
doctor_id INT NOT NULL,
patient_id INT NOT NULL,
visit_time DATETIME NOT NULL,
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);
CREATE TABLE conclusions ( -- медицинские заключения
conclusion_id INT PRIMARY KEY,
ticket_id INT NOT NULL,
diagnosis VARCHAR(150) NOT NULL,
recommendation VARCHAR(200),
FOREIGN KEY (ticket_id) REFERENCES tickets(ticket_id)
);
INSERT INTO divisions VALUES
(1,'Терапия'),(2,'Хирургия'),(3,'Лор'),(4,'Кардиология'),(5,'Неврология');
INSERT INTO doctors VALUES
(1,'Архипов Лев','терапевт',1),(2,'Быков Олег','хирург',2),
(3,'Власова Ева','лор',3),(4,'Гущин Иван','кардиолог',4),
(5,'Дёмина Зоя','невролог',5);
INSERT INTO patients VALUES
(1,'Емельянов Тимур','+7901-001'),(2,'Жигалова Инна','+7901-002'),
(3,'Зорин Глеб','+7901-003'),(4,'Исаева Лена','+7901-004'),(5,'Климов Ян','+7901-005');
INSERT INTO tickets VALUES
(1,1,1,'2025-03-10 09:00'),(2,1,2,'2025-03-10 09:30'),
(3,4,3,'2025-03-10 10:00'),(4,2,4,'2025-03-11 11:00'),(5,5,5,'2025-03-11 12:00');
INSERT INTO conclusions VALUES
(1,1,'ОРВI','Постельный режим'),(2,3,'Аритмия','ЭКГ-контроль'),
(3,4,'Грыжа','Плановая операция'),(4,5,'Невралгия','Физиотерапия'),
(5,2,'Здоров','Без рекомендаций');
-- Запросы
-- 1) Выборка с условием: талоны на 10 марта 2025
SELECT ticket_id, visit_time FROM tickets WHERE visit_time >= '2025-03-10 00:00' AND visit_time < '2025-03-11 00:00';
-- 2) Соединение: пациент, врач и время приема
SELECT p.full_name AS patient, d.full_name AS doctor, t.visit_time
FROM tickets t JOIN patients p ON t.patient_id = p.patient_id
JOIN doctors d ON t.doctor_id = d.doctor_id;
-- 3) Группировка: число талонов у каждого врача
SELECT d.full_name, COUNT(t.ticket_id) AS tickets_cnt
FROM doctors d LEFT JOIN tickets t ON d.doctor_id = t.doctor_id
GROUP BY d.full_name;
-- 4) Подзапрос: пациенты, у которых есть медицинское заключение
SELECT full_name FROM patients WHERE patient_id IN
(SELECT patient_id FROM tickets WHERE ticket_id IN (SELECT ticket_id FROM conclusions));
-- 5) Обновление: перенести талон ticket_id=2 на другое время
UPDATE tickets SET visit_time = '2025-03-12 14:00' WHERE ticket_id = 2;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система ведет расписание приемов поликлиники. Заполняются отделения и врачи, регистрируются пациенты. На прием выдается талон (врач + пациент + время). По итогу приема оформляется медицинское заключение. Запрос с группировкой показывает загруженность врачей.
Билет №12
Предметная область: Бронирование номеров в отеле
Таблицы: типы номеров, номера, гости, бронирования, дополнительные услуги.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Бронирование номеров в отеле
CREATE TABLE room_types ( -- типы номеров
type_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE,
price DECIMAL(10,2) NOT NULL CHECK (price > 0)
);
CREATE TABLE rooms ( -- номера
room_id INT PRIMARY KEY,
number VARCHAR(10) NOT NULL UNIQUE,
type_id INT NOT NULL,
floor INT NOT NULL,
FOREIGN KEY (type_id) REFERENCES room_types(type_id)
);
CREATE TABLE guests ( -- гости
guest_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
passport VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE bookings ( -- бронирования
booking_id INT PRIMARY KEY,
room_id INT NOT NULL,
guest_id INT NOT NULL,
check_in DATE NOT NULL,
check_out DATE NOT NULL,
FOREIGN KEY (room_id) REFERENCES rooms(room_id),
FOREIGN KEY (guest_id) REFERENCES guests(guest_id)
);
CREATE TABLE extra_services ( -- дополнительные услуги
service_id INT PRIMARY KEY,
booking_id INT NOT NULL,
name VARCHAR(80) NOT NULL,
cost DECIMAL(10,2) NOT NULL,
FOREIGN KEY (booking_id) REFERENCES bookings(booking_id)
);
INSERT INTO room_types VALUES
(1,'Стандарт',3500),(2,'Полулюкс',5500),(3,'Люкс',9000),
(4,'Семейный',7000),(5,'Одноместный',2800);
INSERT INTO rooms VALUES
(1,'101',1,1),(2,'102',1,1),(3,'201',3,2),(4,'202',2,2),(5,'301',4,3);
INSERT INTO guests VALUES
(1,'Аксенов Петр','4011 111111'),(2,'Богданова Ия','4011 222222'),
(3,'Воронов Лев','4011 333333'),(4,'Гладких Ульяна','4011 444444'),
(5,'Дроздов Семён','4011 555555');
INSERT INTO bookings VALUES
(1,1,1,'2025-04-01','2025-04-05'),(2,3,2,'2025-04-02','2025-04-04'),
(3,5,3,'2025-04-03','2025-04-10'),(4,4,4,'2025-04-05','2025-04-07'),
(5,1,5,'2025-04-10','2025-04-12');
INSERT INTO extra_services VALUES
(1,1,'Завтрак',600),(2,2,'Трансфер',1500),(3,3,'СПА',2500),
(4,1,'Парковка',400),(5,4,'Завтрак',600);
-- Запросы
-- 1) Выборка с условием: типы номеров дороже 5000 руб.
SELECT name, price FROM room_types WHERE price > 5000;
-- 2) Соединение: бронь, гость и номер
SELECT b.booking_id, g.full_name, r.number, b.check_in, b.check_out
FROM bookings b JOIN guests g ON b.guest_id = g.guest_id
JOIN rooms r ON b.room_id = r.room_id;
-- 3) Группировка: сумма доп. услуг по каждому бронированию
SELECT booking_id, SUM(cost) AS extra_total
FROM extra_services GROUP BY booking_id;
-- 4) Подзапрос: номера типа 'Люкс'
SELECT number FROM rooms WHERE type_id =
(SELECT type_id FROM room_types WHERE name = 'Люкс');
-- 5) Обновление: продлить бронь booking_id=2 до 06.04.2025
UPDATE bookings SET check_out = '2025-04-06' WHERE booking_id = 2;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система бронирования отеля учитывает типы номеров, сами номера, гостей и брони. Заполните типы номеров и номера, зарегистрируйте гостей. Бронирование связывает номер, гостя и даты заезда/выезда. Дополнительные услуги привязываются к брони; их сумма считается запросом с группировкой.
Билет №13
Предметная область: Расписание и продажа билетов в кинотеатр
Таблицы: фильмы, кинозалы, сеансы, клиенты, билеты.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Расписание и продажа билетов в кинотеатр
CREATE TABLE films ( -- фильмы
film_id INT PRIMARY KEY,
title VARCHAR(120) NOT NULL,
genre VARCHAR(50) NOT NULL,
duration INT NOT NULL CHECK (duration > 0)
);
CREATE TABLE halls ( -- кинозалы
hall_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
seats INT NOT NULL CHECK (seats > 0)
);
CREATE TABLE sessions ( -- сеансы
session_id INT PRIMARY KEY,
film_id INT NOT NULL,
hall_id INT NOT NULL,
start_time DATETIME NOT NULL,
price DECIMAL(8,2) NOT NULL,
FOREIGN KEY (film_id) REFERENCES films(film_id),
FOREIGN KEY (hall_id) REFERENCES halls(hall_id)
);
CREATE TABLE clients ( -- клиенты
client_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20)
);
CREATE TABLE film_tickets ( -- билеты
ticket_id INT PRIMARY KEY,
session_id INT NOT NULL,
client_id INT NOT NULL,
seat_no INT NOT NULL,
FOREIGN KEY (session_id) REFERENCES sessions(session_id),
FOREIGN KEY (client_id) REFERENCES clients(client_id)
);
INSERT INTO films VALUES
(1,'Звездный путь','фантастика',130),(2,'Любовь и город','мелодрама',105),
(3,'Гонка','боевик',118),(4,'Тайна леса','триллер',95),(5,'Веселые зверята','мультфильм',88);
INSERT INTO halls VALUES
(1,'Красный',120),(2,'Синий',80),(3,'Зеленый',60),(4,'VIP',30),(5,'Детский',50);
INSERT INTO sessions VALUES
(1,1,1,'2025-04-01 18:00',400),(2,2,2,'2025-04-01 20:00',350),
(3,3,1,'2025-04-01 21:30',400),(4,5,5,'2025-04-02 11:00',250),
(5,4,3,'2025-04-02 22:00',300);
INSERT INTO clients VALUES
(1,'Алёшин Кир','+7902-001'),(2,'Власова Ника','+7902-002'),
(3,'Гнатюк Лев','+7902-003'),(4,'Дёмина Роза','+7902-004'),(5,'Ефимов Тим','+7902-005');
INSERT INTO film_tickets VALUES
(1,1,1,15),(2,1,2,16),(3,2,3,5),(4,4,4,10),(5,1,5,17);
-- Запросы
-- 1) Выборка с условием: фильмы длиннее 100 минут
SELECT title, duration FROM films WHERE duration > 100;
-- 2) Соединение: сеанс, фильм и зал
SELECT s.session_id, f.title, h.name AS hall, s.start_time
FROM sessions s JOIN films f ON s.film_id = f.film_id
JOIN halls h ON s.hall_id = h.hall_id;
-- 3) Группировка: количество проданных билетов на каждый сеанс
SELECT s.session_id, COUNT(t.ticket_id) AS sold
FROM sessions s LEFT JOIN film_tickets t ON s.session_id = t.session_id
GROUP BY s.session_id;
-- 4) Подзапрос: фильмы, на которые проданы билеты
SELECT title FROM films WHERE film_id IN
(SELECT film_id FROM sessions WHERE session_id IN (SELECT session_id FROM film_tickets));
-- 5) Обновление: поднять цену вечерних сеансов на 50 руб.
UPDATE sessions SET price = price + 50 WHERE start_time >= '2025-04-01 18:00';
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система автоматизирует кинотеатр: фильмы, залы, сеансы, клиентов и продажу билетов. Заполните фильмы и залы, сформируйте расписание сеансов. Билет связывает сеанс, клиента и место. Запрос с группировкой показывает заполняемость сеансов по числу проданных билетов.
Билет №14
Предметная область: Учет сотрудников и проектов в IT-компании
Таблицы: отделы, сотрудники, проекты, роли в проектах, навыки сотрудников.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Учет сотрудников и проектов в IT-компании
CREATE TABLE departments ( -- отделы
dept_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
);
CREATE TABLE employees ( -- сотрудники
emp_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
dept_id INT NOT NULL,
salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
CREATE TABLE projects ( -- проекты
project_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
start_date DATE NOT NULL,
budget DECIMAL(12,2) NOT NULL
);
CREATE TABLE project_roles ( -- роли в проектах
role_id INT PRIMARY KEY,
project_id INT NOT NULL,
emp_id INT NOT NULL,
role_name VARCHAR(60) NOT NULL,
FOREIGN KEY (project_id) REFERENCES projects(project_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
CREATE TABLE emp_skills ( -- навыки сотрудников
skill_id INT PRIMARY KEY,
emp_id INT NOT NULL,
skill VARCHAR(60) NOT NULL,
level VARCHAR(20) NOT NULL,
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
INSERT INTO departments VALUES
(1,'Разработка'),(2,'Тестирование'),(3,'Аналитика'),(4,'DevOps'),(5,'Дизайн');
INSERT INTO employees VALUES
(1,'Антонов Глеб',1,120000),(2,'Белова Мира',2,90000),
(3,'Власов Тим',1,110000),(4,'Громов Ян',4,130000),(5,'Дёмина Ева',5,95000);
INSERT INTO projects VALUES
(1,'CRM-система','2025-01-10',2000000),(2,'Мобильное приложение','2025-02-01',1500000),
(3,'Сайт-портал','2025-03-15',800000),(4,'Аналитика BI','2025-03-20',1200000),
(5,'Чат-бот','2025-04-01',500000);
INSERT INTO project_roles VALUES
(1,1,1,'Тимлид'),(2,1,3,'Разработчик'),(3,2,2,'Тестировщик'),
(4,2,4,'DevOps-инженер'),(5,3,5,'Дизайнер');
INSERT INTO emp_skills VALUES
(1,1,'Java','эксперт'),(2,1,'SQL','продвинутый'),(3,3,'Python','эксперт'),
(4,4,'Docker','эксперт'),(5,5,'Figma','продвинутый');
-- Запросы
-- 1) Выборка с условием: сотрудники с зарплатой выше 100000
SELECT full_name, salary FROM employees WHERE salary > 100000;
-- 2) Соединение: сотрудник, проект и его роль
SELECT e.full_name, p.name AS project, pr.role_name
FROM project_roles pr JOIN employees e ON pr.emp_id = e.emp_id
JOIN projects p ON pr.project_id = p.project_id;
-- 3) Группировка: число сотрудников в каждом отделе
SELECT d.name, COUNT(e.emp_id) AS staff
FROM departments d LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.name;
-- 4) Подзапрос: сотрудники, участвующие в проектах
SELECT full_name FROM employees WHERE emp_id IN (SELECT emp_id FROM project_roles);
-- 5) Обновление: повысить зарплату отдела 'Разработка' на 10%
UPDATE employees SET salary = salary * 1.10
WHERE dept_id = (SELECT dept_id FROM departments WHERE name = 'Разработка');
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система учитывает сотрудников и проекты IT-компании. Заполните отделы и сотрудников, создайте проекты. Участие сотрудника в проекте оформляется ролью, навыки фиксируются отдельно. Запрос с группировкой показывает численность отделов.
Билет №15
Предметная область: Туристическое агентство и продажа туров
Таблицы: страны, отели, туры, туристы, заявки на бронирование.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Туристическое агентство и продажа туров
CREATE TABLE countries ( -- страны
country_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE hotels ( -- отели
hotel_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
country_id INT NOT NULL,
stars INT CHECK (stars BETWEEN 1 AND 5),
FOREIGN KEY (country_id) REFERENCES countries(country_id)
);
CREATE TABLE tours ( -- туры
tour_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
hotel_id INT NOT NULL,
days INT NOT NULL CHECK (days > 0),
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (hotel_id) REFERENCES hotels(hotel_id)
);
CREATE TABLE tourists ( -- туристы
tourist_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
passport VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE requests ( -- заявки на бронирование
request_id INT PRIMARY KEY,
tour_id INT NOT NULL,
tourist_id INT NOT NULL,
req_date DATE NOT NULL,
persons INT NOT NULL CHECK (persons > 0),
FOREIGN KEY (tour_id) REFERENCES tours(tour_id),
FOREIGN KEY (tourist_id) REFERENCES tourists(tourist_id)
);
INSERT INTO countries VALUES
(1,'Турция'),(2,'Египет'),(3,'Греция'),(4,'Таиланд'),(5,'Италия');
INSERT INTO hotels VALUES
(1,'Rixos Premium',1,5),(2,'Sunrise Resort',2,4),(3,'Olympic Palace',3,4),
(4,'Phuket Paradise',4,5),(5,'Roma Bella',5,3);
INSERT INTO tours VALUES
(1,'Анталия 10 дней',1,10,72000),(2,'Хургада 7 дней',2,7,58000),
(3,'Родос 9 дней',3,9,69000),(4,'Пхукет 12 дней',4,12,130000),
(5,'Рим экскурсии',5,6,85000);
INSERT INTO tourists VALUES
(1,'Агеев Лев','70 111111'),(2,'Бойко Яна','70 222222'),
(3,'Власюк Ким','70 333333'),(4,'Гущина Рита','70 444444'),(5,'Дроздов Ян','70 555555');
INSERT INTO requests VALUES
(1,1,1,'2025-03-01',2),(2,4,2,'2025-03-02',1),(3,1,3,'2025-03-03',3),
(4,3,4,'2025-03-05',2),(5,2,5,'2025-03-06',4);
-- Запросы
-- 1) Выборка с условием: туры дешевле 70000 руб.
SELECT name, price FROM tours WHERE price < 70000;
-- 2) Соединение: тур, отель и страна
SELECT t.name AS tour, h.name AS hotel, c.name AS country
FROM tours t JOIN hotels h ON t.hotel_id = h.hotel_id
JOIN countries c ON h.country_id = c.country_id;
-- 3) Группировка: число заявок и всего человек по каждому туру
SELECT t.name, COUNT(r.request_id) AS reqs, SUM(r.persons) AS persons_total
FROM tours t LEFT JOIN requests r ON t.tour_id = r.tour_id
GROUP BY t.name;
-- 4) Подзапрос: отели в стране 'Турция'
SELECT name FROM hotels WHERE country_id =
(SELECT country_id FROM countries WHERE name = 'Турция');
-- 5) Обновление: скидка 7% на туры в отели 5 звезд
UPDATE tours SET price = price * 0.93
WHERE hotel_id IN (SELECT hotel_id FROM hotels WHERE stars = 5);
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система продажи туров. Заполните страны и отели (привязка к стране), сформируйте туры. Туристы оставляют заявки на бронирование с указанием тура и числа человек. Запрос с группировкой показывает спрос на туры по числу заявок и туристов.
Билет №16
Предметная область: Фитнес-клуб и абонементы
Таблицы: клиенты, тренеры, направления, абонементы, расписание занятий.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Фитнес-клуб и абонементы
CREATE TABLE directions ( -- направления
direction_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE trainers ( -- тренеры
trainer_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
direction_id INT NOT NULL,
FOREIGN KEY (direction_id) REFERENCES directions(direction_id)
);
CREATE TABLE clients ( -- клиенты
client_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE memberships ( -- абонементы
membership_id INT PRIMARY KEY,
client_id INT NOT NULL,
start_date DATE NOT NULL,
months INT NOT NULL CHECK (months > 0),
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (client_id) REFERENCES clients(client_id)
);
CREATE TABLE schedule ( -- расписание занятий
lesson_id INT PRIMARY KEY,
direction_id INT NOT NULL,
trainer_id INT NOT NULL,
lesson_time DATETIME NOT NULL,
FOREIGN KEY (direction_id) REFERENCES directions(direction_id),
FOREIGN KEY (trainer_id) REFERENCES trainers(trainer_id)
);
INSERT INTO directions VALUES
(1,'Йога'),(2,'Кроссфит'),(3,'Бокс'),(4,'Пилатес'),(5,'Плавание');
INSERT INTO trainers VALUES
(1,'Антонов Глеб',2),(2,'Белова Майя',1),(3,'Власов Тим',3),
(4,'Громова Зоя',4),(5,'Дёмин Лев',5);
INSERT INTO clients VALUES
(1,'Егоров Ким','+7903-001'),(2,'Жукова Ия','+7903-002'),
(3,'Зорин Влад','+7903-003'),(4,'Исаев Ян','+7903-004'),(5,'Котова Аня','+7903-005');
INSERT INTO memberships VALUES
(1,1,'2025-03-01',6,12000),(2,2,'2025-03-05',3,7000),
(3,3,'2025-03-10',12,20000),(4,4,'2025-03-12',1,3000),(5,5,'2025-03-15',6,12000);
INSERT INTO schedule VALUES
(1,1,2,'2025-03-20 10:00'),(2,2,1,'2025-03-20 12:00'),
(3,3,3,'2025-03-20 18:00'),(4,4,4,'2025-03-21 09:00'),(5,5,5,'2025-03-21 19:00');
-- Запросы
-- 1) Выборка с условием: абонементы дороже 10000 руб.
SELECT membership_id, price FROM memberships WHERE price > 10000;
-- 2) Соединение: занятие, направление и тренер
SELECT s.lesson_time, dr.name AS direction, t.full_name AS trainer
FROM schedule s JOIN directions dr ON s.direction_id = dr.direction_id
JOIN trainers t ON s.trainer_id = t.trainer_id;
-- 3) Группировка: сумма проданных абонементов по каждому клиенту
SELECT c.full_name, SUM(m.price) AS total
FROM clients c JOIN memberships m ON c.client_id = m.client_id
GROUP BY c.full_name;
-- 4) Подзапрос: тренеры направления 'Бокс'
SELECT full_name FROM trainers WHERE direction_id =
(SELECT direction_id FROM directions WHERE name = 'Бокс');
-- 5) Обновление: продлить абонемент membership_id=4 на 2 месяца
UPDATE memberships SET months = months + 2 WHERE membership_id = 4;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система фитнес-клуба учитывает направления, тренеров, клиентов, абонементы и расписание. Заполните направления и тренеров, зарегистрируйте клиентов и продайте абонементы. Расписание связывает направление, тренера и время. Запрос с группировкой считает выручку по клиентам.
Билет №17
Предметная область: Агентство недвижимости и аренда квартир
Таблицы: собственники, объекты недвижимости, районы, арендаторы, договоры аренды.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Агентство недвижимости и аренда квартир
CREATE TABLE districts ( -- районы
district_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE owners ( -- собственники
owner_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE properties ( -- объекты недвижимости
property_id INT PRIMARY KEY,
address VARCHAR(150) NOT NULL,
district_id INT NOT NULL,
owner_id INT NOT NULL,
rooms INT NOT NULL CHECK (rooms > 0),
rent_price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (district_id) REFERENCES districts(district_id),
FOREIGN KEY (owner_id) REFERENCES owners(owner_id)
);
CREATE TABLE tenants ( -- арендаторы
tenant_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
passport VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE rent_contracts ( -- договоры аренды
contract_id INT PRIMARY KEY,
property_id INT NOT NULL,
tenant_id INT NOT NULL,
start_date DATE NOT NULL,
months INT NOT NULL CHECK (months > 0),
FOREIGN KEY (property_id) REFERENCES properties(property_id),
FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id)
);
INSERT INTO districts VALUES
(1,'Центральный'),(2,'Заречье'),(3,'Северный'),(4,'Южный'),(5,'Зеленый');
INSERT INTO owners VALUES
(1,'Аксёнов Лев','+7904-001'),(2,'Белая Рита','+7904-002'),
(3,'Власов Ким','+7904-003'),(4,'Гордеев Ян','+7904-004'),(5,'Дёмина Оля','+7904-005');
INSERT INTO properties VALUES
(1,'ул. Ленина, 5-12',1,1,2,35000),(2,'ул. Мира, 8-3',2,2,1,25000),
(3,'пр. Победы, 40-77',1,3,3,50000),(4,'ул. Садовая, 2-9',4,4,1,22000),
(5,'ул. Парковая, 11-5',5,5,2,38000);
INSERT INTO tenants VALUES
(1,'Емельянов Ян','40 111111'),(2,'Жукова Ия','40 222222'),
(3,'Зорин Лев','40 333333'),(4,'Исаева Аня','40 444444'),(5,'Котов Влад','40 555555');
INSERT INTO rent_contracts VALUES
(1,1,1,'2025-03-01',12),(2,2,2,'2025-03-05',6),(3,3,3,'2025-03-10',24),
(4,4,4,'2025-03-12',12),(5,1,5,'2025-04-01',6);
-- Запросы
-- 1) Выборка с условием: объекты с арендой дороже 30000 руб.
SELECT address, rent_price FROM properties WHERE rent_price > 30000;
-- 2) Соединение: договор, объект и арендатор
SELECT rc.contract_id, p.address, t.full_name AS tenant, rc.months
FROM rent_contracts rc JOIN properties p ON rc.property_id = p.property_id
JOIN tenants t ON rc.tenant_id = t.tenant_id;
-- 3) Группировка: число объектов в каждом районе
SELECT d.name, COUNT(p.property_id) AS objects
FROM districts d LEFT JOIN properties p ON d.district_id = p.district_id
GROUP BY d.name;
-- 4) Подзапрос: объекты, которые сдаются в аренду (есть договор)
SELECT address FROM properties WHERE property_id IN (SELECT property_id FROM rent_contracts);
-- 5) Обновление: поднять аренду в 'Центральном' районе на 5%
UPDATE properties SET rent_price = rent_price * 1.05
WHERE district_id = (SELECT district_id FROM districts WHERE name = 'Центральный');
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система агентства недвижимости ведет районы, собственников, объекты, арендаторов и договоры. Заполните районы и собственников, внесите объекты. Договор аренды связывает объект и арендатора. Запрос с группировкой показывает распределение объектов по районам.
Билет №18
Предметная область: Служба доставки посылок
Таблицы: клиенты, посылки, курьеры, маршруты, доставки.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Служба доставки посылок
CREATE TABLE clients ( -- клиенты
client_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE couriers ( -- курьеры
courier_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
transport VARCHAR(40) NOT NULL
);
CREATE TABLE routes ( -- маршруты
route_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
distance DECIMAL(6,1) NOT NULL
);
CREATE TABLE parcels ( -- посылки
parcel_id INT PRIMARY KEY,
client_id INT NOT NULL,
weight DECIMAL(6,2) NOT NULL CHECK (weight > 0),
address_to VARCHAR(150) NOT NULL,
FOREIGN KEY (client_id) REFERENCES clients(client_id)
);
CREATE TABLE deliveries ( -- доставки
delivery_id INT PRIMARY KEY,
parcel_id INT NOT NULL,
courier_id INT NOT NULL,
route_id INT NOT NULL,
status VARCHAR(30) NOT NULL DEFAULT 'в пути',
FOREIGN KEY (parcel_id) REFERENCES parcels(parcel_id),
FOREIGN KEY (courier_id) REFERENCES couriers(courier_id),
FOREIGN KEY (route_id) REFERENCES routes(route_id)
);
INSERT INTO clients VALUES
(1,'Антонов Ян','+7905-001'),(2,'Белова Ия','+7905-002'),
(3,'Власов Лев','+7905-003'),(4,'Гущин Ким','+7905-004'),(5,'Дёмина Аня','+7905-005');
INSERT INTO couriers VALUES
(1,'Егоров Глеб','велосипед'),(2,'Жуков Тим','автомобиль'),
(3,'Зорин Влад','пешком'),(4,'Исаев Лев','скутер'),(5,'Котов Ян','автомобиль');
INSERT INTO routes VALUES
(1,'Центр-Север',8.5),(2,'Центр-Юг',12.0),(3,'Запад',6.0),
(4,'Восток',15.0),(5,'Кольцо',20.0);
INSERT INTO parcels VALUES
(1,1,2.5,'ул. Ленина, 5'),(2,2,0.8,'ул. Мира, 8'),(3,3,5.0,'пр. Победы, 40'),
(4,4,1.2,'ул. Садовая, 2'),(5,5,3.3,'ул. Парковая, 11');
INSERT INTO deliveries VALUES
(1,1,1,1,'доставлено'),(2,2,2,2,'в пути'),(3,3,5,4,'в пути'),
(4,4,4,3,'доставлено'),(5,5,2,5,'ожидает');
-- Запросы
-- 1) Выборка с условием: посылки тяжелее 2 кг
SELECT parcel_id, weight FROM parcels WHERE weight > 2;
-- 2) Соединение: доставка, посылка и курьер
SELECT d.delivery_id, p.address_to, c.full_name AS courier, d.status
FROM deliveries d JOIN parcels p ON d.parcel_id = p.parcel_id
JOIN couriers c ON d.courier_id = c.courier_id;
-- 3) Группировка: число доставок у каждого курьера
SELECT c.full_name, COUNT(d.delivery_id) AS deliveries_cnt
FROM couriers c LEFT JOIN deliveries d ON c.courier_id = d.courier_id
GROUP BY c.full_name;
-- 4) Подзапрос: клиенты, чьи посылки еще не доставлены
SELECT full_name FROM clients WHERE client_id IN
(SELECT client_id FROM parcels WHERE parcel_id IN
(SELECT parcel_id FROM deliveries WHERE status <> 'доставлено'));
-- 5) Обновление: отметить доставку delivery_id=2 как доставленную
UPDATE deliveries SET status = 'доставлено' WHERE delivery_id = 2;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система службы доставки учитывает клиентов, курьеров, маршруты, посылки и доставки. Зарегистрируйте клиентов и курьеров, заведите маршруты. Посылка принадлежит клиенту; доставка связывает посылку, курьера и маршрут. Запрос с группировкой показывает нагрузку курьеров.
Билет №19
Предметная область: Прокат автомобилей
Таблицы: автомобили, филиалы, клиенты, договоры аренды, страховые случаи.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Прокат автомобилей
CREATE TABLE branches ( -- филиалы
branch_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
city VARCHAR(60) NOT NULL
);
CREATE TABLE cars ( -- автомобили
car_id INT PRIMARY KEY,
model VARCHAR(80) NOT NULL,
plate VARCHAR(15) NOT NULL UNIQUE,
branch_id INT NOT NULL,
day_price DECIMAL(8,2) NOT NULL CHECK (day_price > 0),
FOREIGN KEY (branch_id) REFERENCES branches(branch_id)
);
CREATE TABLE clients ( -- клиенты
client_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
license_no VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE rent_contracts ( -- договоры аренды
contract_id INT PRIMARY KEY,
car_id INT NOT NULL,
client_id INT NOT NULL,
start_date DATE NOT NULL,
days INT NOT NULL CHECK (days > 0),
FOREIGN KEY (car_id) REFERENCES cars(car_id),
FOREIGN KEY (client_id) REFERENCES clients(client_id)
);
CREATE TABLE insurance_cases ( -- страховые случаи
case_id INT PRIMARY KEY,
contract_id INT NOT NULL,
description VARCHAR(200) NOT NULL,
damage DECIMAL(10,2) NOT NULL,
FOREIGN KEY (contract_id) REFERENCES rent_contracts(contract_id)
);
INSERT INTO branches VALUES
(1,'Центральный','Вологда'),(2,'Северный','Вологда'),(3,'Привокзальный','Череповец'),
(4,'Аэропорт','Москва'),(5,'Южный','Ярославль');
INSERT INTO cars VALUES
(1,'Kia Rio','А001АА35',1,2500),
(2,'Lada Vesta','В002ВВ35',1,2000),
(3,'Hyundai Solaris','С003СС35',2,2400),
(4,'Toyota Camry','Т004ТТ35',4,5000),
(5,'Renault Logan','Р005РР35',3,2100);
INSERT INTO clients VALUES
(1,'Антонов Лев','77 111111'),(2,'Белова Ия','77 222222'),
(3,'Власов Ким','77 333333'),(4,'Гущин Ян','77 444444'),(5,'Дёмина Аня','77 555555');
INSERT INTO rent_contracts VALUES
(1,1,1,'2025-03-01',5),(2,4,2,'2025-03-03',3),(3,2,3,'2025-03-05',7),
(4,3,4,'2025-03-08',2),(5,5,5,'2025-03-10',4);
INSERT INTO insurance_cases VALUES
(1,2,'Скол лобового стекла',8000),(2,3,'Царапина на двери',5000),
(3,1,'Прокол колеса',2000);
-- Запросы
-- 1) Выборка с условием: автомобили дороже 2300 руб./сутки
SELECT model, day_price FROM cars WHERE day_price > 2300;
-- 2) Соединение: договор, автомобиль и клиент
SELECT rc.contract_id, c.model, cl.full_name, rc.days
FROM rent_contracts rc JOIN cars c ON rc.car_id = c.car_id
JOIN clients cl ON rc.client_id = cl.client_id;
-- 3) Группировка: количество машин в каждом филиале
SELECT b.name, COUNT(c.car_id) AS cars_cnt
FROM branches b LEFT JOIN cars c ON b.branch_id = c.branch_id
GROUP BY b.name;
-- 4) Подзапрос: договоры, по которым были страховые случаи
SELECT contract_id, days FROM rent_contracts
WHERE contract_id IN (SELECT contract_id FROM insurance_cases);
-- 5) Обновление: поднять суточную цену в филиале 'Аэропорт' на 10%
UPDATE cars SET day_price = day_price * 1.10
WHERE branch_id = (SELECT branch_id FROM branches WHERE name = 'Аэропорт');
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система проката автомобилей учитывает филиалы, машины, клиентов, договоры и страховые случаи. Заполните филиалы и автомобили, зарегистрируйте клиентов. Договор фиксирует машину, клиента и срок аренды; при ущербе оформляется страховой случай. Запрос с группировкой показывает парк по филиалам.
Билет №20
Предметная область: Ветеринарная клиника
Таблицы: владельцы, питомцы, ветеринары, приемы, вакцинации.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Ветеринарная клиника
CREATE TABLE owners ( -- владельцы
owner_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE vets ( -- ветеринары
vet_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
specialty VARCHAR(80) NOT NULL
);
CREATE TABLE pets ( -- питомцы
pet_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
species VARCHAR(40) NOT NULL,
owner_id INT NOT NULL,
birth_year INT,
FOREIGN KEY (owner_id) REFERENCES owners(owner_id)
);
CREATE TABLE appointments ( -- приемы
appt_id INT PRIMARY KEY,
pet_id INT NOT NULL,
vet_id INT NOT NULL,
appt_date DATE NOT NULL,
reason VARCHAR(150),
FOREIGN KEY (pet_id) REFERENCES pets(pet_id),
FOREIGN KEY (vet_id) REFERENCES vets(vet_id)
);
CREATE TABLE vaccinations ( -- вакцинации
vacc_id INT PRIMARY KEY,
pet_id INT NOT NULL,
vaccine VARCHAR(80) NOT NULL,
vacc_date DATE NOT NULL,
FOREIGN KEY (pet_id) REFERENCES pets(pet_id)
);
INSERT INTO owners VALUES
(1,'Антонова Лена','+7906-001'),(2,'Белов Ким','+7906-002'),
(3,'Власова Ия','+7906-003'),(4,'Гущин Ян','+7906-004'),(5,'Дёмин Лев','+7906-005');
INSERT INTO vets VALUES
(1,'Егорова Зоя','терапевт'),(2,'Жуков Тим','хирург'),
(3,'Зорина Аня','дерматолог'),(4,'Исаев Влад','стоматолог'),(5,'Котова Майя','офтальмолог');
INSERT INTO pets VALUES
(1,'Барсик','кот',1,2020),(2,'Рекс','собака',2,2019),(3,'Кеша','попугай',3,2022),
(4,'Мухтар','собака',4,2018),(5,'Мурка','кошка',5,2021);
INSERT INTO appointments VALUES
(1,1,1,'2025-03-10','Прививка'),(2,2,2,'2025-03-11','Осмотр лапы'),
(3,3,3,'2025-03-12','Перья'),(4,4,1,'2025-03-13','Профосмотр'),
(5,1,4,'2025-03-15','Зубы');
INSERT INTO vaccinations VALUES
(1,1,'Бешенство','2025-03-10'),(2,2,'Комплексная','2025-02-01'),
(3,4,'Бешенство','2025-01-15'),(4,5,'Комплексная','2025-03-01'),
(5,1,'Лептоспироз','2025-03-20');
-- Запросы
-- 1) Выборка с условием: питомцы-собаки
SELECT name, species FROM pets WHERE species = 'собака';
-- 2) Соединение: прием, питомец и ветеринар
SELECT a.appt_date, p.name AS pet, v.full_name AS vet, a.reason
FROM appointments a JOIN pets p ON a.pet_id = p.pet_id
JOIN vets v ON a.vet_id = v.vet_id;
-- 3) Группировка: число приемов по каждому ветеринару
SELECT v.full_name, COUNT(a.appt_id) AS appts
FROM vets v LEFT JOIN appointments a ON v.vet_id = a.vet_id
GROUP BY v.full_name;
-- 4) Подзапрос: питомцы, которым делали вакцинацию
SELECT name FROM pets WHERE pet_id IN (SELECT pet_id FROM vaccinations);
-- 5) Обновление: изменить причину приема appt_id=3
UPDATE appointments SET reason = 'Выпадение перьев' WHERE appt_id = 3;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система ветклиники учитывает владельцев, ветеринаров, питомцев, приемы и вакцинации. Зарегистрируйте владельцев и ветеринаров, заведите питомцев. Прием связывает питомца и врача, вакцинации фиксируются отдельно. Запрос с группировкой показывает нагрузку ветеринаров.
Билет №21
Предметная область: Продажа билетов на концерты и фестивали
Таблицы: площадки, мероприятия, артисты, зрители, проданные билеты.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Продажа билетов на концерты и фестивали
CREATE TABLE venues ( -- площадки
venue_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(60) NOT NULL,
capacity INT NOT NULL CHECK (capacity > 0)
);
CREATE TABLE artists ( -- артисты
artist_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
genre VARCHAR(50) NOT NULL
);
CREATE TABLE events ( -- мероприятия
event_id INT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
venue_id INT NOT NULL,
artist_id INT NOT NULL,
event_date DATE NOT NULL,
FOREIGN KEY (venue_id) REFERENCES venues(venue_id),
FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);
CREATE TABLE spectators ( -- зрители
spectator_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(100)
);
CREATE TABLE sold_tickets ( -- проданные билеты
ticket_id INT PRIMARY KEY,
event_id INT NOT NULL,
spectator_id INT NOT NULL,
price DECIMAL(8,2) NOT NULL,
FOREIGN KEY (event_id) REFERENCES events(event_id),
FOREIGN KEY (spectator_id) REFERENCES spectators(spectator_id)
);
INSERT INTO venues VALUES
(1,'Дворец спорта','Вологда',5000),(2,'Парк культуры','Череповец',3000),
(3,'Концертный зал','Москва',1500),(4,'Стадион','Ярославль',20000),(5,'Клуб Drive','Вологда',800);
INSERT INTO artists VALUES
(1,'Группа Север','рок'),(2,'DJ Pulse','электроника'),(3,'Хор Лира','классика'),
(4,'Рэп-проект М','хип-хоп'),(5,'Джаз-бэнд','джаз');
INSERT INTO events VALUES
(1,'Рок-фест 2025',1,1,'2025-05-10'),(2,'Электро-ночь',5,2,'2025-05-12'),
(3,'Вечер классики',3,3,'2025-05-15'),(4,'Хип-хоп шоу',4,4,'2025-05-20'),
(5,'Джаз в парке',2,5,'2025-05-22');
INSERT INTO spectators VALUES
(1,'Антонов Ян','an@mail.ru'),(2,'Белова Ия','be@mail.ru'),
(3,'Власов Лев','vl@mail.ru'),(4,'Гущина Аня','gu@mail.ru'),(5,'Дёмин Ким','de@mail.ru');
INSERT INTO sold_tickets VALUES
(1,1,1,1500),(2,1,2,1500),(3,2,3,1200),(4,4,4,2000),(5,1,5,1800);
-- Запросы
-- 1) Выборка с условием: площадки вместимостью более 3000
SELECT name, capacity FROM venues WHERE capacity > 3000;
-- 2) Соединение: мероприятие, площадка и артист
SELECT e.name AS event, v.name AS venue, a.name AS artist, e.event_date
FROM events e JOIN venues v ON e.venue_id = v.venue_id
JOIN artists a ON e.artist_id = a.artist_id;
-- 3) Группировка: выручка по каждому мероприятию
SELECT e.name, SUM(st.price) AS revenue
FROM events e LEFT JOIN sold_tickets st ON e.event_id = st.event_id
GROUP BY e.name;
-- 4) Подзапрос: артисты, у которых есть мероприятия с проданными билетами
SELECT name FROM artists WHERE artist_id IN
(SELECT artist_id FROM events WHERE event_id IN (SELECT event_id FROM sold_tickets));
-- 5) Обновление: поднять цену билетов на 'Рок-фест 2025' на 200 руб.
UPDATE sold_tickets SET price = price + 200
WHERE event_id = (SELECT event_id FROM events WHERE name = 'Рок-фест 2025');
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система продажи билетов на концерты и фестивали. Заполните площадки и артистов, создайте мероприятия. Зрители покупают билеты на мероприятия. Запрос с группировкой считает выручку по каждому мероприятию.
Билет №22
Предметная область: Салон красоты и парикмахерская
Таблицы: мастера, клиенты, услуги, записи на прием, чеки об оплате.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Салон красоты и парикмахерская
CREATE TABLE masters ( -- мастера
master_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
position VARCHAR(60) NOT NULL
);
CREATE TABLE services ( -- услуги
service_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
price DECIMAL(8,2) NOT NULL CHECK (price > 0),
duration INT NOT NULL
);
CREATE TABLE clients ( -- клиенты
client_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE appointments ( -- записи на прием
appt_id INT PRIMARY KEY,
client_id INT NOT NULL,
master_id INT NOT NULL,
service_id INT NOT NULL,
appt_time DATETIME NOT NULL,
FOREIGN KEY (client_id) REFERENCES clients(client_id),
FOREIGN KEY (master_id) REFERENCES masters(master_id),
FOREIGN KEY (service_id) REFERENCES services(service_id)
);
CREATE TABLE receipts ( -- чеки об оплате
receipt_id INT PRIMARY KEY,
appt_id INT NOT NULL,
amount DECIMAL(8,2) NOT NULL,
pay_date DATE NOT NULL,
FOREIGN KEY (appt_id) REFERENCES appointments(appt_id)
);
INSERT INTO masters VALUES
(1,'Антонова Лена','парикмахер'),(2,'Белова Ия','маникюрист'),
(3,'Власова Зоя','косметолог'),(4,'Гущина Аня','визажист'),(5,'Дёмина Майя','парикмахер');
INSERT INTO services VALUES
(1,'Стрижка женская',1200,60),(2,'Маникюр',900,90),(3,'Окрашивание',3000,120),
(4,'Чистка лица',2000,80),(5,'Макияж',1500,60);
INSERT INTO clients VALUES
(1,'Егорова Ника','+7907-001'),(2,'Жукова Роза','+7907-002'),
(3,'Зорина Оля','+7907-003'),(4,'Исаева Вера','+7907-004'),(5,'Котова Инна','+7907-005');
INSERT INTO appointments VALUES
(1,1,1,1,'2025-04-01 10:00'),(2,2,2,2,'2025-04-01 11:00'),
(3,3,3,4,'2025-04-02 12:00'),(4,4,5,3,'2025-04-02 14:00'),(5,1,4,5,'2025-04-03 16:00');
INSERT INTO receipts VALUES
(1,1,1200,'2025-04-01'),(2,2,900,'2025-04-01'),(3,3,2000,'2025-04-02'),
(4,4,3000,'2025-04-02'),(5,5,1500,'2025-04-03');
-- Запросы
-- 1) Выборка с условием: услуги дороже 1500 руб.
SELECT name, price FROM services WHERE price > 1500;
-- 2) Соединение: запись, клиент, мастер и услуга
SELECT a.appt_time, cl.full_name AS client, m.full_name AS master, s.name AS service
FROM appointments a JOIN clients cl ON a.client_id = cl.client_id
JOIN masters m ON a.master_id = m.master_id
JOIN services s ON a.service_id = s.service_id;
-- 3) Группировка: выручка по каждому мастеру
SELECT m.full_name, SUM(r.amount) AS revenue
FROM masters m JOIN appointments a ON m.master_id = a.master_id
JOIN receipts r ON a.appt_id = r.appt_id
GROUP BY m.full_name;
-- 4) Подзапрос: услуги, которые хотя бы раз заказывали
SELECT name FROM services WHERE service_id IN (SELECT service_id FROM appointments);
-- 5) Обновление: поднять цену услуги 'Окрашивание' на 10%
UPDATE services SET price = price * 1.10 WHERE name = 'Окрашивание';
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система салона красоты учитывает мастеров, услуги, клиентов, записи и чеки. Заполните мастеров и услуги, зарегистрируйте клиентов. Запись связывает клиента, мастера и услугу; оплата фиксируется чеком. Запрос с группировкой считает выручку по мастерам.
Билет №23
Предметная область: Строительная компания и управление проектами
Таблицы: объекты строительства, прорабы, строительные материалы, сметы, этапы работ.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Строительная компания и управление проектами
CREATE TABLE objects_build ( -- объекты строительства
object_id INT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
address VARCHAR(150) NOT NULL,
deadline DATE NOT NULL
);
CREATE TABLE foremen ( -- прорабы
foreman_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE materials ( -- строительные материалы
material_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
unit VARCHAR(20) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
CREATE TABLE estimates ( -- сметы
estimate_id INT PRIMARY KEY,
object_id INT NOT NULL,
material_id INT NOT NULL,
qty DECIMAL(10,2) NOT NULL CHECK (qty > 0),
FOREIGN KEY (object_id) REFERENCES objects_build(object_id),
FOREIGN KEY (material_id) REFERENCES materials(material_id)
);
CREATE TABLE work_stages ( -- этапы работ
stage_id INT PRIMARY KEY,
object_id INT NOT NULL,
foreman_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
status VARCHAR(30) NOT NULL DEFAULT 'план',
FOREIGN KEY (object_id) REFERENCES objects_build(object_id),
FOREIGN KEY (foreman_id) REFERENCES foremen(foreman_id)
);
INSERT INTO objects_build VALUES
(1,'ЖК Радуга','ул. Ленина, 100','2026-06-01'),
(2,'ТЦ Меридиан','пр. Победы, 50','2025-12-15'),
(3,'Школа №5','ул. Школьная, 7','2026-08-30'),
(4,'Мост','наб. Реки','2027-01-20'),
(5,'Склад','промзона, 3','2025-09-10');
INSERT INTO foremen VALUES
(1,'Антонов Лев','+7908-001'),(2,'Белов Ким','+7908-002'),
(3,'Власов Ян','+7908-003'),(4,'Гущин Тим','+7908-004'),(5,'Дёмин Влад','+7908-005');
INSERT INTO materials VALUES
(1,'Цемент М500','мешок',420),(2,'Кирпич','шт',18),(3,'Арматура 12мм','тонна',65000),
(4,'Песок','тонна',900),(5,'Бетон М300','м3',4500);
INSERT INTO estimates VALUES
(1,1,1,500),(2,1,3,12),(3,2,2,50000),(4,3,5,200),(5,4,3,40);
INSERT INTO work_stages VALUES
(1,1,1,'Фундамент','выполнен'),(2,1,1,'Стены','в работе'),
(3,2,2,'Каркас','план'),(4,3,3,'Кровля','план'),(5,4,4,'Опоры','в работе');
-- Запросы
-- 1) Выборка с условием: материалы дороже 1000 руб.
SELECT name, price FROM materials WHERE price > 1000;
-- 2) Соединение: смета — объект и материал с количеством
SELECT o.name AS object, m.name AS material, e.qty, m.unit
FROM estimates e JOIN objects_build o ON e.object_id = o.object_id
JOIN materials m ON e.material_id = m.material_id;
-- 3) Группировка: стоимость материалов по каждому объекту
SELECT o.name, SUM(m.price * e.qty) AS total_cost
FROM estimates e JOIN objects_build o ON e.object_id = o.object_id
JOIN materials m ON e.material_id = m.material_id
GROUP BY o.name;
-- 4) Подзапрос: объекты, по которым есть этапы в работе
SELECT name FROM objects_build WHERE object_id IN
(SELECT object_id FROM work_stages WHERE status = 'в работе');
-- 5) Обновление: отметить этап stage_id=3 как 'в работе'
UPDATE work_stages SET status = 'в работе' WHERE stage_id = 3;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система строительной компании ведет объекты, прорабов, материалы, сметы и этапы работ. Заведите объекты и прорабов, справочник материалов. Смета связывает объект и материалы с количеством, этапы работ закрепляются за прорабом. Запрос с группировкой считает стоимость материалов по объектам.
Билет №24
Предметная область: Успеваемость учеников средней школы
Таблицы: классы, учителя, ученики, школьные предметы, журнал оценок.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Успеваемость учеников средней школы
CREATE TABLE classes ( -- классы
class_id INT PRIMARY KEY,
name VARCHAR(10) NOT NULL UNIQUE,
year_in INT NOT NULL
);
CREATE TABLE teachers ( -- учителя
teacher_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
subject VARCHAR(60) NOT NULL
);
CREATE TABLE subjects ( -- школьные предметы
subject_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);
CREATE TABLE pupils ( -- ученики
pupil_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
class_id INT NOT NULL,
FOREIGN KEY (class_id) REFERENCES classes(class_id)
);
CREATE TABLE grade_journal ( -- журнал оценок
record_id INT PRIMARY KEY,
pupil_id INT NOT NULL,
subject_id INT NOT NULL,
grade INT NOT NULL CHECK (grade BETWEEN 2 AND 5),
grade_date DATE NOT NULL,
FOREIGN KEY (pupil_id) REFERENCES pupils(pupil_id),
FOREIGN KEY (subject_id) REFERENCES subjects(subject_id)
);
INSERT INTO classes VALUES
(1,'9А',2016),(2,'9Б',2016),(3,'10А',2015),(4,'11А',2014),(5,'8В',2017);
INSERT INTO teachers VALUES
(1,'Антонова Лена','Математика'),(2,'Белов Ким','Физика'),
(3,'Власова Зоя','Русский язык'),(4,'Гущин Ян','История'),(5,'Дёмина Аня','Информатика');
INSERT INTO subjects VALUES
(1,'Математика',1),(2,'Физика',2),(3,'Русский язык',3),
(4,'История',4),(5,'Информатика',5);
INSERT INTO pupils VALUES
(1,'Егоров Тим',1),(2,'Жукова Ия',1),(3,'Зорин Лев',2),
(4,'Исаева Аня',3),(5,'Котов Влад',4);
INSERT INTO grade_journal VALUES
(1,1,1,5,'2025-03-01'),(2,1,2,4,'2025-03-02'),(3,2,1,3,'2025-03-01'),
(4,3,5,5,'2025-03-03'),(5,4,3,4,'2025-03-04');
-- Запросы
-- 1) Выборка с условием: отличные оценки (5)
SELECT pupil_id, grade, grade_date FROM grade_journal WHERE grade = 5;
-- 2) Соединение: ученик, предмет и оценка
SELECT p.full_name, s.name AS subject, gj.grade
FROM grade_journal gj JOIN pupils p ON gj.pupil_id = p.pupil_id
JOIN subjects s ON gj.subject_id = s.subject_id;
-- 3) Группировка: средний балл по каждому ученику
SELECT p.full_name, AVG(gj.grade) AS avg_grade
FROM pupils p JOIN grade_journal gj ON p.pupil_id = gj.pupil_id
GROUP BY p.full_name;
-- 4) Подзапрос: ученики класса '9А'
SELECT full_name FROM pupils WHERE class_id =
(SELECT class_id FROM classes WHERE name = '9А');
-- 5) Обновление: исправить оценку record_id=3 на 4
UPDATE grade_journal SET grade = 4 WHERE record_id = 3;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система учитывает успеваемость учеников школы. Заполните классы и учителей, закрепите предметы за учителями, внесите учеников по классам. Оценки заносятся в журнал. Запрос с группировкой считает средний балл по ученикам.
Билет №25
Предметная область: Автосалон и продажа автомобилей
Таблицы: автомобили, поставщики, клиенты, договоры купли-продажи, дополнительное оборудование.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Автосалон и продажа автомобилей
CREATE TABLE suppliers ( -- поставщики
supplier_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
country VARCHAR(60) NOT NULL
);
CREATE TABLE cars ( -- автомобили
car_id INT PRIMARY KEY,
model VARCHAR(80) NOT NULL,
supplier_id INT NOT NULL,
price DECIMAL(12,2) NOT NULL CHECK (price > 0),
year_make INT NOT NULL,
FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
);
CREATE TABLE clients ( -- клиенты
client_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
phone VARCHAR(20) NOT NULL
);
CREATE TABLE contracts ( -- договоры купли-продажи
contract_id INT PRIMARY KEY,
car_id INT NOT NULL,
client_id INT NOT NULL,
deal_date DATE NOT NULL,
deal_price DECIMAL(12,2) NOT NULL,
FOREIGN KEY (car_id) REFERENCES cars(car_id),
FOREIGN KEY (client_id) REFERENCES clients(client_id)
);
CREATE TABLE extra_equipment ( -- дополнительное оборудование
equip_id INT PRIMARY KEY,
contract_id INT NOT NULL,
name VARCHAR(80) NOT NULL,
cost DECIMAL(10,2) NOT NULL,
FOREIGN KEY (contract_id) REFERENCES contracts(contract_id)
);
INSERT INTO suppliers VALUES
(1,'AutoImport','Германия'),(2,'KoreaCars','Корея'),(3,'JapanMotors','Япония'),
(4,'LadaTrade','Россия'),(5,'EuroCar','Чехия');
INSERT INTO cars VALUES
(1,'BMW X5',1,6500000,2024),(2,'Kia Sportage',2,3200000,2024),
(3,'Toyota RAV4',3,3800000,2023),(4,'Lada Vesta',4,1600000,2024),
(5,'Skoda Octavia',5,2900000,2023);
INSERT INTO clients VALUES
(1,'Антонов Лев','+7909-001'),(2,'Белова Ия','+7909-002'),
(3,'Власов Ким','+7909-003'),(4,'Гущин Ян','+7909-004'),(5,'Дёмина Аня','+7909-005');
INSERT INTO contracts VALUES
(1,1,1,'2025-03-01',6500000),(2,4,2,'2025-03-05',1600000),
(3,2,3,'2025-03-08',3150000),(4,3,4,'2025-03-10',3800000),(5,5,5,'2025-03-12',2850000);
INSERT INTO extra_equipment VALUES
(1,1,'Зимняя резина',60000),(2,1,'Сигнализация',25000),(3,3,'Коврики',8000),
(4,4,'Защита картера',12000),(5,2,'Тонировка',9000);
-- Запросы
-- 1) Выборка с условием: автомобили дороже 3000000 руб.
SELECT model, price FROM cars WHERE price > 3000000;
-- 2) Соединение: договор, автомобиль и клиент
SELECT ct.contract_id, c.model, cl.full_name, ct.deal_price
FROM contracts ct JOIN cars c ON ct.car_id = c.car_id
JOIN clients cl ON ct.client_id = cl.client_id;
-- 3) Группировка: число проданных машин по каждому поставщику
SELECT s.name, COUNT(ct.contract_id) AS sold
FROM suppliers s JOIN cars c ON s.supplier_id = c.supplier_id
JOIN contracts ct ON c.car_id = ct.car_id
GROUP BY s.name;
-- 4) Подзапрос: автомобили поставщиков из России
SELECT model FROM cars WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE country = 'Россия');
-- 5) Обновление: сделать скидку 3% по договору contract_id=3
UPDATE contracts SET deal_price = deal_price * 0.97 WHERE contract_id = 3;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система автосалона учитывает поставщиков, автомобили, клиентов, договоры и доп. оборудование. Заполните поставщиков и автомобили, зарегистрируйте клиентов. Договор фиксирует продажу машины клиенту; доп. оборудование привязывается к договору. Запрос с группировкой показывает продажи по поставщикам.
Билет №26
Предметная область: Киностудия и производство фильмов
Таблицы: фильмы, актеры, режиссеры, съемочные группы, роли.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Киностудия и производство фильмов
CREATE TABLE directors ( -- режиссеры
director_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
country VARCHAR(60) NOT NULL
);
CREATE TABLE films ( -- фильмы
film_id INT PRIMARY KEY,
title VARCHAR(120) NOT NULL,
director_id INT NOT NULL,
year_make INT NOT NULL,
budget DECIMAL(14,2) NOT NULL,
FOREIGN KEY (director_id) REFERENCES directors(director_id)
);
CREATE TABLE actors ( -- актеры
actor_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
birth_year INT
);
CREATE TABLE crews ( -- съемочные группы
crew_id INT PRIMARY KEY,
film_id INT NOT NULL,
name VARCHAR(80) NOT NULL,
members INT NOT NULL CHECK (members > 0),
FOREIGN KEY (film_id) REFERENCES films(film_id)
);
CREATE TABLE roles ( -- роли
role_id INT PRIMARY KEY,
film_id INT NOT NULL,
actor_id INT NOT NULL,
character_name VARCHAR(100) NOT NULL,
FOREIGN KEY (film_id) REFERENCES films(film_id),
FOREIGN KEY (actor_id) REFERENCES actors(actor_id)
);
INSERT INTO directors VALUES
(1,'Антонов Лев','Россия'),(2,'Белов Ким','США'),(3,'Власов Ян','Франция'),
(4,'Гущин Тим','Россия'),(5,'Дёмин Влад','Италия');
INSERT INTO films VALUES
(1,'Северный ветер',1,2024,50000000),(2,'Город огней',2,2023,120000000),
(3,'Тайна реки',3,2024,30000000),(4,'Грань',4,2025,45000000),(5,'Рассвет',5,2023,60000000);
INSERT INTO actors VALUES
(1,'Егоров Тим',1985),(2,'Жукова Ия',1990),(3,'Зорин Лев',1978),
(4,'Исаева Аня',1995),(5,'Котов Влад',1982);
INSERT INTO crews VALUES
(1,1,'Операторская',8),(2,1,'Гримеры',4),(3,2,'Каскадеры',12),
(4,3,'Звукорежиссеры',5),(5,4,'Декораторы',6);
INSERT INTO roles VALUES
(1,1,1,'Капитан'),(2,1,2,'Журналистка'),(3,2,3,'Детектив'),
(4,3,4,'Учительница'),(5,4,5,'Инженер');
-- Запросы
-- 1) Выборка с условием: фильмы с бюджетом более 50 млн
SELECT title, budget FROM films WHERE budget > 50000000;
-- 2) Соединение: фильм, режиссер и актер с ролью
SELECT f.title, d.full_name AS director, a.full_name AS actor, r.character_name
FROM roles r JOIN films f ON r.film_id = f.film_id
JOIN directors d ON f.director_id = d.director_id
JOIN actors a ON r.actor_id = a.actor_id;
-- 3) Группировка: число съемочных групп по каждому фильму
SELECT f.title, COUNT(c.crew_id) AS crews_cnt
FROM films f LEFT JOIN crews c ON f.film_id = c.film_id
GROUP BY f.title;
-- 4) Подзапрос: фильмы режиссеров из России
SELECT title FROM films WHERE director_id IN
(SELECT director_id FROM directors WHERE country = 'Россия');
-- 5) Обновление: увеличить бюджет фильма 'Грань' на 5 млн
UPDATE films SET budget = budget + 5000000 WHERE title = 'Грань';
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система киностудии учитывает режиссеров, фильмы, актеров, съемочные группы и роли. Заполните режиссеров и фильмы, внесите актеров. Роли связывают актера и фильм, съемочные группы закрепляются за фильмом. Запрос с группировкой считает число групп по фильмам.
Билет №27
Предметная область: Банковские счета и операции
Таблицы: клиенты, счета, банковские карты, операции, отделения.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Банковские счета и операции
CREATE TABLE branches ( -- отделения
branch_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
city VARCHAR(60) NOT NULL
);
CREATE TABLE clients ( -- клиенты
client_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
passport VARCHAR(20) NOT NULL UNIQUE
);
CREATE TABLE accounts ( -- счета
account_id INT PRIMARY KEY,
client_id INT NOT NULL,
branch_id INT NOT NULL,
number VARCHAR(20) NOT NULL UNIQUE,
balance DECIMAL(14,2) NOT NULL DEFAULT 0,
FOREIGN KEY (client_id) REFERENCES clients(client_id),
FOREIGN KEY (branch_id) REFERENCES branches(branch_id)
);
CREATE TABLE cards ( -- банковские карты
card_id INT PRIMARY KEY,
account_id INT NOT NULL,
number VARCHAR(20) NOT NULL UNIQUE,
expiry DATE NOT NULL,
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
CREATE TABLE operations ( -- операции
operation_id INT PRIMARY KEY,
account_id INT NOT NULL,
op_type VARCHAR(20) NOT NULL,
amount DECIMAL(14,2) NOT NULL,
op_date DATE NOT NULL,
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
INSERT INTO branches VALUES
(1,'Отделение №1','Вологда'),(2,'Отделение №2','Череповец'),
(3,'Отделение №3','Москва'),(4,'Отделение №4','Ярославль'),(5,'Отделение №5','Тверь');
INSERT INTO clients VALUES
(1,'Антонов Лев','40 111111'),(2,'Белова Ия','40 222222'),
(3,'Власов Ким','40 333333'),(4,'Гущин Ян','40 444444'),(5,'Дёмина Аня','40 555555');
INSERT INTO accounts VALUES
(1,1,1,'40817810001',150000),(2,2,1,'40817810002',50000),
(3,3,2,'40817810003',320000),(4,4,3,'40817810004',8000),(5,5,1,'40817810005',95000);
INSERT INTO cards VALUES
(1,1,'2200000000000001','2027-05-31'),(2,2,'2200000000000002','2026-08-31'),
(3,3,'2200000000000003','2028-01-31'),(4,4,'2200000000000004','2026-12-31'),
(5,5,'2200000000000005','2027-03-31');
INSERT INTO operations VALUES
(1,1,'пополнение',50000,'2025-03-01'),(2,1,'списание',12000,'2025-03-02'),
(3,3,'списание',20000,'2025-03-03'),(4,2,'пополнение',30000,'2025-03-04'),
(5,5,'списание',5000,'2025-03-05');
-- Запросы
-- 1) Выборка с условием: счета с балансом более 100000 руб.
SELECT number, balance FROM accounts WHERE balance > 100000;
-- 2) Соединение: счет, клиент и отделение
SELECT a.number, cl.full_name, b.name AS branch, a.balance
FROM accounts a JOIN clients cl ON a.client_id = cl.client_id
JOIN branches b ON a.branch_id = b.branch_id;
-- 3) Группировка: сумма операций по типу
SELECT op_type, SUM(amount) AS total FROM operations GROUP BY op_type;
-- 4) Подзапрос: клиенты, имеющие счета в отделении 'Отделение №1'
SELECT full_name FROM clients WHERE client_id IN
(SELECT client_id FROM accounts WHERE branch_id =
(SELECT branch_id FROM branches WHERE name = 'Отделение №1'));
-- 5) Обновление: начислить 5% на счета с балансом свыше 100000
UPDATE accounts SET balance = balance * 1.05 WHERE balance > 100000;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Банковская система учитывает отделения, клиентов, счета, карты и операции. Заполните отделения и клиентов, откройте счета (привязка к клиенту и отделению), выпустите карты. Операции по счету меняют баланс. Запрос с группировкой суммирует операции по типу.
Билет №28
Предметная область: Система технической поддержки и обработки заявок
Таблицы: пользователи, инженеры поддержки, заявки, корпоративное оборудование, категории проблем.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Система технической поддержки и обработки заявок
CREATE TABLE problem_categories ( -- категории проблем
category_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL UNIQUE
);
CREATE TABLE users_sys ( -- пользователи
user_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE engineers ( -- инженеры поддержки
engineer_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
grade VARCHAR(30) NOT NULL
);
CREATE TABLE equipment ( -- корпоративное оборудование
equip_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
inv_no VARCHAR(20) NOT NULL UNIQUE,
user_id INT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users_sys(user_id)
);
CREATE TABLE tickets ( -- заявки
ticket_id INT PRIMARY KEY,
user_id INT NOT NULL,
engineer_id INT NOT NULL,
category_id INT NOT NULL,
created DATE NOT NULL,
status VARCHAR(30) NOT NULL DEFAULT 'открыта',
FOREIGN KEY (user_id) REFERENCES users_sys(user_id),
FOREIGN KEY (engineer_id) REFERENCES engineers(engineer_id),
FOREIGN KEY (category_id) REFERENCES problem_categories(category_id)
);
INSERT INTO problem_categories VALUES
(1,'Сеть'),(2,'ПО'),(3,'Оборудование'),(4,'Доступ'),(5,'Печать');
INSERT INTO users_sys VALUES
(1,'Антонов Лев','an@corp.ru'),(2,'Белова Ия','be@corp.ru'),
(3,'Власов Ким','vl@corp.ru'),(4,'Гущин Ян','gu@corp.ru'),(5,'Дёмина Аня','de@corp.ru');
INSERT INTO engineers VALUES
(1,'Егоров Тим','senior'),(2,'Жуков Лев','junior'),(3,'Зорин Влад','middle'),
(4,'Исаев Ян','senior'),(5,'Котова Майя','middle');
INSERT INTO equipment VALUES
(1,'Ноутбук Dell','INV001',1),(2,'Принтер HP','INV002',2),
(3,'Монитор LG','INV003',3),(4,'ПК офисный','INV004',4),(5,'МФУ Canon','INV005',5);
INSERT INTO tickets VALUES
(1,1,1,2,'2025-03-01','закрыта'),(2,2,2,5,'2025-03-02','открыта'),
(3,3,3,1,'2025-03-03','в работе'),(4,4,1,4,'2025-03-04','открыта'),
(5,1,4,3,'2025-03-05','закрыта');
-- Запросы
-- 1) Выборка с условием: открытые заявки
SELECT ticket_id, created FROM tickets WHERE status = 'открыта';
-- 2) Соединение: заявка, пользователь и инженер
SELECT t.ticket_id, u.full_name AS user, e.full_name AS engineer, t.status
FROM tickets t JOIN users_sys u ON t.user_id = u.user_id
JOIN engineers e ON t.engineer_id = e.engineer_id;
-- 3) Группировка: число заявок по каждой категории
SELECT pc.name, COUNT(t.ticket_id) AS tickets_cnt
FROM problem_categories pc LEFT JOIN tickets t ON pc.category_id = t.category_id
GROUP BY pc.name;
-- 4) Подзапрос: инженеры, на которых назначены заявки
SELECT full_name FROM engineers WHERE engineer_id IN (SELECT engineer_id FROM tickets);
-- 5) Обновление: закрыть заявку ticket_id=3
UPDATE tickets SET status = 'закрыта' WHERE ticket_id = 3;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система техподдержки учитывает пользователей, инженеров, оборудование, категории проблем и заявки. Заполните категории, пользователей и инженеров, закрепите оборудование за пользователями. Заявка связывает пользователя, инженера и категорию. Запрос с группировкой показывает структуру обращений.
Билет №29
Предметная область: Учет в сельскохозяйственном предприятии
Таблицы: поля, культуры, сотрудники, сельскохозяйственная техника, сбор урожая.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Учет в сельскохозяйственном предприятии
CREATE TABLE fields ( -- поля
field_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
area DECIMAL(8,2) NOT NULL CHECK (area > 0)
);
CREATE TABLE crops ( -- культуры
crop_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE,
season VARCHAR(20) NOT NULL
);
CREATE TABLE employees ( -- сотрудники
emp_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
position VARCHAR(60) NOT NULL
);
CREATE TABLE machinery ( -- сельскохозяйственная техника
machine_id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
type VARCHAR(40) NOT NULL
);
CREATE TABLE harvest ( -- сбор урожая
harvest_id INT PRIMARY KEY,
field_id INT NOT NULL,
crop_id INT NOT NULL,
machine_id INT NOT NULL,
amount_t DECIMAL(10,2) NOT NULL CHECK (amount_t > 0),
harvest_date DATE NOT NULL,
FOREIGN KEY (field_id) REFERENCES fields(field_id),
FOREIGN KEY (crop_id) REFERENCES crops(crop_id),
FOREIGN KEY (machine_id) REFERENCES machinery(machine_id)
);
INSERT INTO fields VALUES
(1,'Северное',120.5),(2,'Южное',98.0),(3,'Заречное',150.0),
(4,'Луговое',75.5),(5,'Дальнее',200.0);
INSERT INTO crops VALUES
(1,'Пшеница','лето'),(2,'Картофель','осень'),(3,'Кукуруза','лето'),
(4,'Ячмень','лето'),(5,'Подсолнечник','осень');
INSERT INTO employees VALUES
(1,'Антонов Лев','агроном'),(2,'Белов Ким','комбайнер'),
(3,'Власов Ян','тракторист'),(4,'Гущин Тим','механик'),(5,'Дёмин Влад','бригадир');
INSERT INTO machinery VALUES
(1,'Комбайн Acros','комбайн'),(2,'Трактор МТЗ','трактор'),(3,'Сеялка СЗ','сеялка'),
(4,'Комбайн Дон','комбайн'),(5,'Опрыскиватель','опрыскиватель');
INSERT INTO harvest VALUES
(1,1,1,1,360.0,'2025-08-10'),(2,2,2,2,250.0,'2025-09-15'),
(3,3,3,4,420.0,'2025-08-20'),(4,4,4,1,180.0,'2025-08-25'),(5,5,5,4,300.0,'2025-09-30');
-- Запросы
-- 1) Выборка с условием: поля площадью более 100 га
SELECT name, area FROM fields WHERE area > 100;
-- 2) Соединение: сбор — поле, культура и техника
SELECT f.name AS field, cr.name AS crop, m.name AS machine, h.amount_t
FROM harvest h JOIN fields f ON h.field_id = f.field_id
JOIN crops cr ON h.crop_id = cr.crop_id
JOIN machinery m ON h.machine_id = m.machine_id;
-- 3) Группировка: суммарный сбор по каждой культуре
SELECT cr.name, SUM(h.amount_t) AS total_t
FROM crops cr JOIN harvest h ON cr.crop_id = h.crop_id
GROUP BY cr.name;
-- 4) Подзапрос: культуры, собранные комбайнами
SELECT name FROM crops WHERE crop_id IN
(SELECT crop_id FROM harvest WHERE machine_id IN
(SELECT machine_id FROM machinery WHERE type = 'комбайн'));
-- 5) Обновление: скорректировать сбор harvest_id=4 до 200 т
UPDATE harvest SET amount_t = 200.0 WHERE harvest_id = 4;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Система учета сельхозпредприятия ведет поля, культуры, сотрудников, технику и сбор урожая. Заполните справочники полей, культур, техники и сотрудников. Сбор урожая связывает поле, культуру и технику. Запрос с группировкой считает валовой сбор по культурам.
Билет №30
Предметная область: Портал поиска работы и подбор персонала
Таблицы: соискатели, работодатели, вакансии, резюме, отклики на вакансии.
Решение (SQL-скрипт: создание таблиц, тестовые данные, запросы)
-- Предметная область: Портал поиска работы и подбор персонала
CREATE TABLE employers ( -- работодатели
employer_id INT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
industry VARCHAR(60) NOT NULL
);
CREATE TABLE applicants ( -- соискатели
applicant_id INT PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE vacancies ( -- вакансии
vacancy_id INT PRIMARY KEY,
title VARCHAR(120) NOT NULL,
employer_id INT NOT NULL,
salary DECIMAL(10,2) NOT NULL CHECK (salary > 0),
FOREIGN KEY (employer_id) REFERENCES employers(employer_id)
);
CREATE TABLE resumes ( -- резюме
resume_id INT PRIMARY KEY,
applicant_id INT NOT NULL,
position VARCHAR(120) NOT NULL,
experience INT NOT NULL CHECK (experience >= 0),
FOREIGN KEY (applicant_id) REFERENCES applicants(applicant_id)
);
CREATE TABLE responses ( -- отклики на вакансии
response_id INT PRIMARY KEY,
vacancy_id INT NOT NULL,
resume_id INT NOT NULL,
resp_date DATE NOT NULL,
status VARCHAR(30) NOT NULL DEFAULT 'отправлен',
FOREIGN KEY (vacancy_id) REFERENCES vacancies(vacancy_id),
FOREIGN KEY (resume_id) REFERENCES resumes(resume_id)
);
INSERT INTO employers VALUES
(1,'ТехноСофт','IT'),(2,'СтройГрад','строительство'),(3,'МедЦентр','медицина'),
(4,'ФинБанк','финансы'),(5,'ЭкоФерма','сельское хозяйство');
INSERT INTO applicants VALUES
(1,'Антонов Лев','an@job.ru'),(2,'Белова Ия','be@job.ru'),
(3,'Власов Ким','vl@job.ru'),(4,'Гущин Ян','gu@job.ru'),(5,'Дёмина Аня','de@job.ru');
INSERT INTO vacancies VALUES
(1,'Программист',1,120000),(2,'Прораб',2,90000),(3,'Медсестра',3,55000),
(4,'Аналитик',4,110000),(5,'Агроном',5,70000);
INSERT INTO resumes VALUES
(1,1,'Программист',5),(2,2,'Бухгалтер',8),(3,3,'Медсестра',3),
(4,4,'Аналитик',2),(5,5,'Программист',1);
INSERT INTO responses VALUES
(1,1,1,'2025-03-01','приглашен'),(2,3,3,'2025-03-02','отправлен'),
(3,4,4,'2025-03-03','отказ'),(4,1,5,'2025-03-04','отправлен'),(5,2,2,'2025-03-05','приглашен');
-- Запросы
-- 1) Выборка с условием: вакансии с зарплатой выше 100000 руб.
SELECT title, salary FROM vacancies WHERE salary > 100000;
-- 2) Соединение: отклик — вакансия и соискатель
SELECT r.response_id, v.title AS vacancy, a.full_name AS applicant, r.status
FROM responses r JOIN vacancies v ON r.vacancy_id = v.vacancy_id
JOIN resumes rs ON r.resume_id = rs.resume_id
JOIN applicants a ON rs.applicant_id = a.applicant_id;
-- 3) Группировка: число откликов на каждую вакансию
SELECT v.title, COUNT(r.response_id) AS responses_cnt
FROM vacancies v LEFT JOIN responses r ON v.vacancy_id = r.vacancy_id
GROUP BY v.title;
-- 4) Подзапрос: вакансии работодателей отрасли 'IT'
SELECT title FROM vacancies WHERE employer_id IN
(SELECT employer_id FROM employers WHERE industry = 'IT');
-- 5) Обновление: пригласить соискателя по отклику response_id=2
UPDATE responses SET status = 'приглашен' WHERE response_id = 2;
Задание 4. Краткое руководство пользователя
Руководство пользователя. Портал поиска работы учитывает работодателей, соискателей, вакансии, резюме и отклики. Заполните работодателей и вакансии, зарегистрируйте соискателей и их резюме. Отклик связывает вакансию и резюме. Запрос с группировкой показывает популярность вакансий по числу откликов.
