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

Автор:О. Константинов   Ограничение времени: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.

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

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

Условие

Главврачу необходимо знать актуальную информацию о наличии страховых медицинских полисов у мед. работников и пациентов. В БД больницы хранится список врачей 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.

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

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

Условие

Менеджеру по персоналу поручили определить сотрудников, которые могут уволиться из-за низкой заработной платы. Он считает зарплату низкой, если она строго меньше средней. БД содержит список сотрудников 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.

0.325s 0.021s 29