Задача A. Добавление городов
Условие
В таблицу Cities нужно добавить города
Vladivostok, Novosibirsk, Lesozavodsk.
Для города Vladivostok нужно указать население: 604901. Структура таблицы:
ID - идентификатор записи с автоинкрементном;Town - название города;Population - численность населения.
Решение следует представить в виде текстового файла, содержащего несколько SQL-запросов.
Запросы следует завершать символом ';'.
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
Задача B. Удаление NULL-значений
Условие
Из таблице Magazines нужно удалить все записи, где поле Edition имеет значение "NULL".
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
Задача C. Создание таблицы с заметками
Условие
В пустой БД требуется создать таблицу Notes со структурой:
ID: INTEGER;
Note: TEXT;
TimeOfCreation: DATETIME;
ProgressMade: REAL;
Status: TEXT.
Дополнительно, нужно прописать параметры полей:
ID - первичный ключ с автоинкрементном;Note - гарантирует уникальность, не NULL;TimeOfCreation - не NULL;ProgressMade - от 0 до 1, по умолчанию ноль;Status - одно из значений: "started",
"accepted", "canceled", по умолчанию "started".
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
Задача D. Создание простой базы контактов
Условие
Требуется по UML-схеме создать базу данных при помощи DDL-скрипта на языке SQL.
При описании полей таблицы следует указывать только тип значений и связи.
Поле id следует сделать первичным ключом.
Схема БД в UML-нотации:
Решение следует представить в виде текстового файла, содержащего несколько SQL-запросов.
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
Задача E. Сотрудники с низкой зарплатой
Условие
Менеджеру по персоналу поручили определить сотрудников, которые могут уволиться из-за низкой заработной платы.
Он считает зарплату низкой, если она строго меньше средней. БД содержит список сотрудников 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.
Описание полей запроса:
EmployeeFullNames - ФИО сотрудника;SumSalary - зарплата сотрудника по всем занимаемым должностям.
Значения поля
SumSalary должны быть округлены до ближайших целых чисел.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Формат входного файла
Пример тестовой БД.
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
Задача F. Прогресс обработки заявок
Условие
В сервисных центрах ИТ-компаний активно используется система управления инцидентами.
Клиенты СЦ создают задачи task, где описывают возникшие проблемы и отправляют их
сотрудникам СЦ. Задача имеет статус, который отражает степень её выполнения.
Только что поступившая задача от клиента помечается как "new", когда задача
полностью выполнена, сотрудники СЦ переводят её в статус "completed".
Задача может находиться в любом статусе несколько раз, количество статусов не ограничено.
Аналитик СЦ хочет оценить эффективность других сотрудников СЦ. Для это необходим
SQL-запрос который возвращает совокупное количество задач в статусах "new" и "completed"
для каждого дня за последние 30 дней (считать от времени создания последней задачи)
в порядке следования дат в календаре. Требуется составить данный запрос.
Результатом выполнения запроса должен быть список кортежей (date,
number_of_new, number_of_completed)
отсортированный по возрастанию значений поля
date
и содержащий количество задач в статусах "new" и "completed", построенных
за каждый день функционирования системы в течении 30 дней с даты самой актуальной
задачи. Если одна и та же задача перешла в два разных статуса
в один момент времени, то учитывается переход с максимальным id записи.
Описание полей запроса:
-
date
- дата формирования сводной статистики в формате YYYY-MM-DD;
-
number_of_new
- количество заявок в статусе "new" на 23:59:59 дня date;
-
number_of_completed
- количество заявок в статусе "completed" на 23:59:59 дня date.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Формат входного файла
Пример тестовой БД.
| 2017-12-29 |
1 |
0 |
| 2018-01-01 |
1 |
0 |
| 2018-01-02 |
2 |
0 |
Ограничения
Полагается, что для работы с базой данных используется SQLite3.
