Задача A. Инциденты тех. поддержки
Условие
В БД хранится список специалистов тех. поддержки Specialists,
список инцидентов Incidents и список назначений специалиста тех.
поддержки на инцидент Assignment для дальнейшего устранения последствий инцидента.
За устранение последствий каждого инцидента отвечает единственный
специалист тех. поддержки. Все инциденты обрабатываются специалистами
в порядке регистрации инцидентов в БД. Устраненные инциденты никак не помечаются, вместо
этого у каждого специалиста есть поле Resolved,
которое хранит количество устраненных инцидентов.
Ниже представлено детальное DDL-описание таблиц:
TABLE Specialists ( -- хранит список специалистов тех. поддержки
ID INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
NOT NULL,
FullName TEXT NOT NULL, -- ФИО специалиста
Resolved INTEGER NOT NULL -- количество обработанных инцидентов
CHECK (Resolved >= 0)
DEFAULT (0)
);
TABLE Incidents ( -- список всех инцидентов
ID INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
NOT NULL,
Title TEXT NOT NULL, -- заголовок инцидента
CreatedAt DATETIME NOT NULL,
Description TEXT -- описание инцидента
);
TABLE Assignment ( -- список назначений инцидентов специалистам
SpeсialistID INTEGER NOT NULL -- идентификатор специалиста
REFERENCES Specialists (ID),
IncidentID INTEGER NOT NULL -- идентификатор инцидента
REFERENCES Incidents (ID),
UNIQUE (
SpeсialistID,
IncidentID
)
);
Требуется для каждого специалиста тех. поддержки вывести 5 ближайших к устранению инцидентов.
Результат отсортировать по SpeсialistID, затем по порядку обработки.
Если специалист устранил все инциденты, то его следует игнорировать.
Схема БД в UML-нотации:
Результатом выполнения запроса должен быть список кортежей
(FullName, IncidentTitle).
Описание полей запроса:
FullName - ФИО специалиста;IncidentTitle - название инцидента.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Формат входного файла
Пример тестовой БД.
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
Задача B. Лучшие продавцы товаров
Условие
Руководитель магазина решил наградить 10 самых продуктивных сотрудников. Для этого
ему нужен запрос, который посчитает суммарную выручку каждого сотрудника магазина,
отсортирует по убыванию суммарной выручки и возьмёт первые 10 кортежей. Выводить
следует только продающих сотрудников.
Требуется по фрагменту схемы реляционной БД составить данный запрос на языке SQL.
Описание таблиц на схеме:
Sellers - сотрудники магазина (продавцы);Products - продаваемые товары;Sales - продажи;SalesItems - позиции продаж.
Результатом выполнения запроса должен быть список кортежей (SellerFullName,
TotalRevenue, CountOfSales) отсортированный по убыванию значений поля TotalRevenue
и содержащий максимум 10 записей. Описание полей запроса:
SellerFullName - полное имя продавца (Фамилия и Имя через пробел);TotalRevenue - суммарная выручка продавца, полученная от продажи товаров;CountOfSales - совокупное количество продаж.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Формат входного файла
Пример тестовой БД.
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
Задача C. Остатки товаров на складе
Условие
Товарооборот магазина одежды фиксируется в базе данных этой компании.
БД хранит список товаров Products, список категорий товаров
Categories, список купленных у поставщиков товаров PurchaseItems
и список проданных клиентам товаров SalesItems.
У товара поле CategoryID может быть NULL,
если товар не подходи ни под одну из существующих категорий.
Если товар ни разу не закупался и не продавался, то его следует игнорировать.
Ниже представлено детальное DDL-описание таблиц:
TABLE Categories ( -- хранит список категорий товаров
ID INTEGER NOT NULL -- уникальный идентификатор
PRIMARY KEY AUTOINCREMENT,
Name TEXT UNIQUE -- наименование категории
NOT NULL
);
TABLE Products ( -- хранит список товаров
ID INTEGER NOT NULL -- уникальный идентификатор
PRIMARY KEY AUTOINCREMENT,
Title TEXT UNIQUE -- название товара
NOT NULL,
CategoryID INTEGER, -- идентификатор категории
SellingPrice REAL CHECK (SellingPrice > 0) -- розничная цена продажи товара
NOT NULL,
FOREIGN KEY (
CategoryID
)
REFERENCES Categories (ID) ON DELETE CASCADE
);
TABLE PurchaseItems ( -- хранит список товаров, купленных у поставщиков
ID INTEGER NOT NULL -- уникальный идентификатор
PRIMARY KEY AUTOINCREMENT,
ProductID INTEGER NOT NULL, -- идентификатор товара
QuantityBought INTEGER NOT NULL -- количество купленного товара (штук)
CHECK (QuantityBought > 0)
DEFAULT (1),
FOREIGN KEY (
ProductID
)
REFERENCES Products (ID) ON DELETE CASCADE
);
TABLE SalesItems ( -- хранит список проданных клиентам товаров
ID INTEGER NOT NULL -- уникальный идентификатор
PRIMARY KEY AUTOINCREMENT,
ProductID INTEGER NOT NULL, -- идентификатор товара
QuantitySold INTEGER NOT NULL -- количество проданного товара (штук)
CHECK (QuantitySold > 0)
DEFAULT (1),
FOREIGN KEY (
ProductID
)
REFERENCES Products (ID) ON DELETE CASCADE
);
Требуется вывести список закончившихся товаров.
Схема БД в UML-нотации:
Результатом выполнения запроса должен быть список кортежей
(Title, CategoryName, SellingPrice) для товаров с
нулевым остатком, отсортированный по убыванию розничных цен продажи.
Описание полей запроса:
Title - название товара;CategoryName - наименование категории;SellingPrice - розничная цена продажи товара.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Формат входного файла
Пример тестовой БД.
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
