Задача A. Занятые парковочные места

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

Условие

На автостоянке эксплуатируется БД, в которой содержится информация о том, какому автомобилю из Cars принадлежит то или иное паковочное место из ParkingPlaces. Для реализации связи "многие ко многим", была заведена таблица CarsParkings, где автомобилю сопоставляется конкретное пронумерованное место на стоянке. Каждый автомобиль может иметь несколько парковочных мест. Ниже представлено детальное DDL-описание таблиц:


TABLE Cars ( -- машины
    ID        INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                      NOT NULL,
    CarNumber TEXT    NOT NULL -- номер автомобиля
                      UNIQUE
);

TABLE ParkingPlaces ( -- парковочные места
    ID            INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                          NOT NULL,
    ParkingNumber TEXT    NOT NULL -- номер парковочного места
                          UNIQUE
);

TABLE CarsParkings ( -- принадлежность парковочного места автомобилю
    CarID     INTEGER REFERENCES Cars (ID) -- идентификатор автомобиля
                      NOT NULL,
    ParkingID INTEGER REFERENCES ParkingPlaces (ID) -- идентификатор парковочного места
                      NOT NULL,
    UNIQUE (
        CarID,
        ParkingID
    )
);

Требуется вывести все автомобили, у которых есть парковочные места. Схема БД в UML-нотации:

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

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

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

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

Ограничения

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


Задача B. Прогресс обработки заявок

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

Условие

В сервисных центрах ИТ-компаний активно используется система управления инцидентами. Клиенты СЦ создают задачи task, где описывают возникшие проблемы и отправляют их сотрудникам СЦ. Задача имеет статус, который отражает степень её выполнения. Только что поступившая задача от клиента помечается как "new", когда задача полностью выполнена, сотрудники СЦ переводят её в статус "completed". Задача может находиться в любом статусе несколько раз, количество статусов не ограничено. Аналитик СЦ хочет оценить эффективность других сотрудников СЦ. Для это необходим SQL-запрос который возвращает совокупное количество задач в статусах "new" и "completed" для каждого дня за последние 30 дней (считать от времени создания последней задачи) в порядке следования дат в календаре. Требуется составить данный запрос.

Результатом выполнения запроса должен быть список кортежей (date, number_of_new, number_of_completed) отсортированный по возрастанию значений поля date и содержащий количество задач в статусах "new" и "completed", построенных за каждый день функционирования системы в течении 30 дней с даты самой актуальной задачи. Если одна и та же задача перешла в два разных статуса в один момент времени, то учитывается переход с максимальным id записи. Описание полей запроса:

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

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

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

2017-12-29 1 0
2018-01-01 1 0
2018-01-02 2 0

Ограничения

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


Задача C. Сотрудники и пациенты больницы

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

Условие

Главврачу необходимо знать актуальную информацию о наличии страховых медицинских полисов у мед. работников и пациентов. В БД больницы хранится список врачей Doctors, список пациентов Patients, список личной информации о физ. лицах PersonalData и список полисов ОМС MedicalInsurance. При этом, заболевший врач может быть одновременно и пациентом больницы. В таблице PersonalData хранится информация обо всех работавших в больнице врачах и обо всех обращавшихся за мед. помощью пациентах. В таблицах Doctors и Patients содержится только актуальная информация о врачах и пациентах, но без личных сведений. В таблице MedicalInsurance у актуальных полисов значение поля Status равно "active", в противном случае - "deactivated"; у врачей и пациентов может и вовсе не быть полисов ОМС. Ниже представлено детальное DDL-описание таблиц:


TABLE Doctors ( -- хранит список работающих врачей
    ID             INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                           NOT NULL,
    PersonalDataID INTEGER NOT NULL -- идентификатор персональной информации
                           REFERENCES PersonalData (ID)
);

TABLE Patients ( -- хранит список лечащихся пациентов
    ID             INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                           NOT NULL,
    PersonalDataID INTEGER NOT NULL -- идентификатор персональной информации
                           REFERENCES PersonalData (ID)
);

TABLE PersonalData ( -- хранит персональную информацию по врачам и пациентам
    ID                 INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                               NOT NULL,
    FullName           TEXT    NOT NULL, -- ФИО лица
    MedicalInsuranceID INTEGER REFERENCES MedicalInsurance (ID) -- идентификатор полиса ОМС
                               UNIQUE
);

TABLE MedicalInsurance (
    ID     INTEGER      PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                        NOT NULL,
    Status TEXT         NOT NULL -- статус полиса ОМС
                        DEFAULT ('active')
                        CHECK (Status IN ('active', 'deactivated') ),
    Number VARCHAR (16) NOT NULL -- номер полиса ОМС
                        UNIQUE
);

Требуется подсчитать количество постояльцев (врачей и пациентов) больницы, у которых есть актуальные полисы ОМС. Схема БД в UML-нотации:

Результатом выполнения запроса должен быть один кортеж (TotalPeopleWithMI), где TotalPeopleWithMI - количество постояльцев больницы с актуальными полисами ОМС.

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

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

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

Ограничения

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


Задача D. Сотрудники с низкой зарплатой

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

Условие

Менеджеру по персоналу поручили определить сотрудников, которые могут уволиться из-за низкой заработной платы. Он считает зарплату низкой, если она строго меньше средней. БД содержит список сотрудников Employee, список должностей Positions и список зарплат Salaries. Работник компании может занимать несколько должностей и получать зарплату, состоящую из размера полной ставки Salary умноженную на долю от ставки SalaryPercentage. Ниже представлено детальное DDL-описание таблиц:


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

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

TABLE Salaries ( -- хранит список зарплат
    ID               INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                             NOT NULL,
    EmployeeID       INTEGER NOT NULL -- идентификатор сотрудника
                             REFERENCES Employees (ID),
    PositionID       INTEGER NOT NULL -- идентификатор должности
                             REFERENCES Positions (ID),
    SalaryPercentage REAL    NOT NULL -- получаемая доля от полной ставки з. п.
                             CHECK (SalaryPercentage BETWEEN 0 AND 1)
                             DEFAULT (1),
    UNIQUE (
        EmployeeID,
        PositionID
    )
);

Требуется вывести список сотрудников, получающих низкую (ниже средней по организации) зарплату. Схема БД в UML-нотации:

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

Значения поля SumSalary должны быть округлены до ближайших целых чисел.

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

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

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

Ограничения

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


Задача E. Лучшие продавцы товаров

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

Условие

Руководитель магазина решил наградить 10 самых продуктивных сотрудников. Для этого ему нужен запрос, который посчитает суммарную выручку каждого сотрудника магазина, отсортирует по убыванию суммарной выручки и возьмёт первые 10 кортежей. Выводить следует только продающих сотрудников. Требуется по фрагменту схемы реляционной БД составить данный запрос на языке SQL.

Описание таблиц на схеме:

Результатом выполнения запроса должен быть список кортежей (SellerFullName, TotalRevenue, CountOfSales) отсортированный по убыванию значений поля TotalRevenue и содержащий максимум 10 записей. Описание полей запроса:

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

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

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

Ограничения

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


0.254s 0.009s 41