Задача A. Остатки товаров на складе

Автор:О. Константинов   Ограничение времени:1 сек
Входной файл:test.sql   Ограничение памяти:256 Мб
Выходной файл:test.log  

Условие

Товарооборот магазина одежды фиксируется в базе данных этой компании. БД хранит список товаров 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) для товаров с нулевым остатком, отсортированный по убыванию розничных цен продажи. Описание полей запроса:

Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.

Формат входного файла

Пример тестовой БД.

Ограничения

Полагается, что для работы с базой данных используется SQLite3.


Задача B. Инциденты тех. поддержки

Автор:О. Константинов   Ограничение времени:1 сек
Входной файл:test.sql   Ограничение памяти:256 Мб
Выходной файл:test.log  

Условие

В БД хранится список специалистов тех. поддержки 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). Описание полей запроса:

Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.

Формат входного файла

Пример тестовой БД.

Ограничения

Полагается, что для работы с базой данных используется SQLite3.


Задача C. Сотрудники ИТ-отдела

Автор:О. Константинов   Ограничение времени:1 сек
Входной файл:test.sql   Ограничение памяти:256 Мб
Выходной файл:test.log  

Условие

Студент ДВФУ проходил производственную практику в ИТ-отделе предприятия. Должность руководителя отдела называется PC Principal. По окончании практики студенту требуется составить отчет, частью которого является описание организационной структуры ИТ-отдела предприятия. Он решил воспользоваться локальной БД предприятия, которая хранит в одной таблице Employees список всех сотрудников вместе с иерархией подчиненности (для каждого сотрудника указывается его непосредственный начальник). Для учредителя компании поле ChiefID равно NULL. Название занимаемых должностей вынесено в отдельную таблицу Positions. У линейного персонала может быть не указана должность. Ниже представлено детальное DDL-описание таблиц:


TABLE Employees ( -- хранит список сотрудников
    ID         INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                       NOT NULL,
    FullName   TEXT    NOT NULL, -- ФИО сотрудника
    PositionID INTEGER REFERENCES Positions (ID), -- должность сотрудника
    ChiefID    INTEGER REFERENCES Employees (ID) -- непосредственный начальник
);

TABLE Positions ( -- хранит список должностей
    ID    INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                  NOT NULL,
    Title TEXT    UNIQUE -- название должности
                  NOT NULL
);

Требуется создать представление ShowITEmployees, вызов которого вернут список всех сотрудников ИТ-отдела во главе с PC Principal. Схема БД в UML-нотации:

Результатом выполнения запроса должен быть список кортежей (ID, FullName, Position, ChiefID). Описание полей запроса:

Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.

Формат входного файла

Пример тестовой БД.

Ограничения

Полагается, что для работы с базой данных используется SQLite3.


Задача D. Восстановление списка файлов

Автор:О. Константинов   Ограничение времени:1 сек
Входной файл:test.sql   Ограничение памяти:256 Мб
Выходной файл:test.log  

Условие

Российский видеоблогер Илья Мэддисон решил выпустить обзор новой компьютерной игры. Но, при установки её на компьютер, его Windows 10 не выдержала нагрузки и "слетела", так как недавно получила новый накопительный пакет обновлений. Помогите Илье восстановить данные с диска, написав SQL-запрос к таблице файловой системы.

Файловая системы содержит три типа записей: "root" - корень диска, "dir" - директория, "file" - файл; имеет следующую структуру:

Результатом выполнения запроса должна быть список полных имен full_name файлов, директорий и букв диска. Чтобы отличить типы объектов, нужно выводить символ ":" в конце буквы диска, "\" в конце названия директории, значение поля extension через "." в конце имени файла. В качестве разделителя между объектами используется символ "\", список имен должен быть отсортирован по возрастанию id.

Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.

Формат входного файла

Пример тестовой БД.

Ограничения

Полагается, что для работы с базой данных используется SQLite3.


0.244s 0.010s 35