Студент ДВФУ проходил производственную практику в ИТ-отделе предприятия.
Должность руководителя отдела называется 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).
Описание полей запроса:
ID - идентификатор сотрудника;
FullName - ФИО сотрудника;
Position - должность сотрудника;
ChiefID - идентификатор руководителя сотрудника.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Менеджеру по персоналу поручили определить сотрудников, которые могут уволиться из-за низкой заработной платы.
Он считает зарплату низкой, если она строго меньше средней. БД содержит список сотрудников 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-запрос.
В сервисных центрах ИТ-компаний активно используется система управления инцидентами.
Клиенты СЦ создают задачи 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-запрос.
Товарооборот магазина одежды фиксируется в базе данных этой компании.
БД хранит список товаров 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-запрос.
Российский видеоблогер Илья Мэддисон решил выпустить обзор новой компьютерной игры.
Но, при установки её на компьютер, его Windows 10 не выдержала нагрузки и "слетела",
так как недавно получила новый накопительный пакет обновлений.
Помогите Илье восстановить данные с диска, написав
SQL-запрос к таблице файловой системы.
Файловая системы содержит три типа записей:
"root" - корень диска, "dir" - директория, "file" - файл; имеет следующую структуру:
id - уникальный идентификатор записи;
object_name - название объекта (буквы диска, директории, файла);
object_type - указывает какого типа объект хранится в ФС: "root", "dir", "file";
parent_id - указывает вложенность файла или директории в корень диска или директорию;
extension - если присутствует, то указывает расширение файла.
Результатом выполнения запроса должна быть список полных имен full_name файлов, директорий и
букв диска. Чтобы отличить типы объектов, нужно выводить символ ":" в конце буквы диска,
"\" в конце названия директории, значение поля extension через "." в конце имени файла.
В качестве разделителя между объектами используется символ "\", список имен должен быть отсортирован
по возрастанию id.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Одной спортивной организации понадобилось сформировать отчет обо всех когда-либо проведенных ею соревнованиях.
Все данные о прошедших соревнованиях и их результатах хранятся в базе данных, состоящей из следующих таблиц:
CONTESTS (соревнования)
ROWI INTEGER PRIMARY KEY AUTOINCREMENT, -- ID соревнования
...
TEAMS (команды)
ROWI INTEGER PRIMARY KEY AUTOINCREMENT, -- ID команды
KIND INTEGER NOT NULL, -- ID дисциплины
...
POINTS (оценки)
CONTEST INTEGER REFERENCES CONTESTS(ROWI),
TEAM INTEGER REFERENCES TEAMS(ROWI),
MARK INTEGER NOT NULL,
...
Однако уже в процессе оказалось, что основная таблица, содержащая всю сводную информацию, была утеряна.
В связи с этим возникла необходимость восстановить ее на основе существующих в БД записей.
В качестве ответа необходимо вывести таблицу, состоящую из следующих столбцов:
ID соревнования,
ID дисциплины,
номер места среди участников текущей дисциплины (начиная с единицы),
ID команды.
Сортировка производится по всем имеющимся столбцам в порядке их следования.
При этом у команд, набравших равное число баллов, места должны совпадать.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Молодой программист Вася прибыл на международную IT-конференцию и почти сразу же озаботился следующей проблемой.
График мероприятий конференции хранится в БД.
Основу такой БД составляет таблица EVENTS, имеющая следующий вид:
ROWI INTEGER PRIMARY KEY AUTOINCREMENT,
HEAD DATE NOT NULL CHECK (HEAD < TAIL), -- начало мероприятия
TAIL DATE NOT NULL -- конец мероприятия
и таблица KWORDS, в которой каждому мероприятию ставится в соответствие набор ключевых слов.
EVENT INTEGER REFERENCES EVENTS(ROWI),
WORD INTEGER NOT NULL,
PRIMARY KEY (EVENT, WORD)
Вася хочет посетить как можно большее число мероприятий, которые не перекрываются по времени.
При этом его интересуют только те мероприятия, в описании которых встречаются ключевые слова из заданного списка.
Напишите SQL-запрос с параметром, выполняющий выборку таких мероприятий.
В качестве ответа нужно вывести таблицу, состоящую из одного столбца,
который будет содержать номера выбранных мероприятий, отсортированных по времени их начала.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Вместо списка ключевых слов в тексте запроса должен присутствовать символ (:words).
