Задача 1A. Выборка всех полей

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

Условие

Схема реляционной БД содержит таблицу SomeTable структура которой неизвестна. Требуется выбрать все поля всех записей данной таблицы.

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

Ограничения

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


Задача 1B. Выборка двух полей

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

Условие

Схема реляционной БД содержит таблицу Clients, в которой хранится список клиентов с полями:

Требуется вывести только ID и фамилию клиента.

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

Ограничения

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


Задача 1C. Удаление тестовой таблицы

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

Условие

Необходимо удалить таблицу TestTable из реляционной БД.

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

Ограничения

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


Задача 1D. Добавление городов

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

Условие

В таблицу Cities нужно добавить города Vladivostok, Novosibirsk, Lesozavodsk. Для города Vladivostok нужно указать население: 604901. Структура таблицы:

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

Ограничения

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


Задача 2A. Добавление поля в таблицу

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

Условие

В таблицу Customers реляционной БД требуется добавить новое поле Email типа "TEXT", со значение по умолчанию "@mail.ru".

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

Ограничения

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


Задача 2B. Создание таблицы с заметками

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

Условие

В пустой БД требуется создать таблицу Notes со структурой:

ID: INTEGER;
Note: TEXT;
TimeOfCreation: DATETIME;
ProgressMade: REAL;
Status: TEXT.

Дополнительно, нужно прописать параметры полей:

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

Ограничения

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


Задача 2C. Выборка уникальных значений

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

Условие

Требуется из таблицы Books со структурой:

вывести список уникальных инициалов авторов.

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

Ограничения

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


Задача 2D. Классификация целых чисел

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

Условие

База данных представлена одной таблицей Numbers, в которой хранится набор целых чисел Value. Требуется классифицировать числа на положительные "positive", отрицательные "negative" и ноль "zero". Ниже представлено детальное DDL-описание таблицы:


TABLE Numbers ( -- хранит список целых чисел
    Value INTEGER NOT NULL -- целое число
                DEFAULT (0) 
);

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

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

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

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

Ограничения

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


Задача 2E. Лидеры продаж

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

Условие

Для таблицы Sales со структурой:

нужно составить запрос, выводящий 10 наиболее продаваемых наименований товаров.

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

Ограничения

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


Задача 2F. Обновление значений поля

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

Условие

В таблице Animals нужно изменить все значения поля Sex с длинных вариантов: "male", "female" на короткие: "m", "w". Для прочих значений поля Sex, нужно указать "unknown", если они не "NULL".

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

Ограничения

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


Задача 2G. Опытные сотрудники

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

Условие

Требуется из таблицы Employees выбрать ФИО сотрудников мужчин в возрасте от 35 до 70 включительно. Поля таблицы Employees:

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

Ограничения

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


Задача 2H. Поиск клиентов по первой букве

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

Условие

Для таблицы Customers нужно выбрать имена клиентов начинающихся с буквы "G" и отсортировать их по убыванию возраста. Структура таблицы:

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

Ограничения

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


Задача 2I. Суммирование полей

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

Условие

Для таблицы ListOfNumbers, состоящей из двух полей A и B, нужно составить запрос, выводящий абсолютное значение (модуль) суммы значений этих полей.

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

Ограничения

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


Задача 2J. Телефонный код города

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

Условие

Требуется из таблицы TelephoneCodesOfCities со структурой:

выбрать телефонный код города "krasnoyarsk". Города хранятся в БД в нижнем регистре.

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

Ограничения

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


Задача 2K. Удаление NULL-значений

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

Условие

Из таблице Magazines нужно удалить все записи, где поле Edition имеет значение "NULL".

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

Ограничения

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


Задача 3A. Агрегатные функции

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

Условие

База данных представлена одной таблицей Numbers, в которой хранится набор целых чисел Value. Требуется вычислить минимальное и максимальное значения, сумму и количество чисел в таблице, среднее значение среди всех чисел. Ниже представлено детальное DDL-описание таблицы:


TABLE Numbers ( -- хранит список целых чисел
    Value INTEGER NOT NULL -- целое число
                DEFAULT (0)
);

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

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

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

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

Ограничения

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


Задача 3B. Создание простой базы контактов

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

Условие

Требуется по UML-схеме создать базу данных при помощи DDL-скрипта на языке SQL. При описании полей таблицы следует указывать только тип значений и связи. Поле id следует сделать первичным ключом. Схема БД в UML-нотации:

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

Ограничения

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


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

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

Условие

В сервисных центрах ИТ-компаний активно используется система управления инцидентами. Клиенты СЦ создают задачи 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.


Задача 3D. Количество сотрудников в отделах

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

Условие

В базе данных торговой компании хранится список сотрудников Employees и список департаментов Departments. Каждый сотрудник может числиться только в одном департаменте или не числиться ни в одном из них. Также, существуют новые департаменты без сотрудников. Ниже представлено детальное DDL-описание таблиц:


TABLE Employees ( -- хранит список сотрудников
    ID           INTEGER PRIMARY KEY AUTOINCREMENT -- уникальный идентификатор
                         NOT NULL,
    FullName     TEXT    NOT NULL, -- ФИО сотрудника
    Age          INTEGER CHECK (Age >= 0), -- возраст сотрудника
    DepartmentID INTEGER REFERENCES Departments (ID) -- идентификатор департамента
);

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

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

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

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

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

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

Ограничения

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


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

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

Условие

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


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

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


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

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

Условие

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

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

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

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

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

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

Ограничения

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


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

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


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

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


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

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


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

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


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

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

Условие

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

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

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

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

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

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

Ограничения

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


Задача 4A. Номализация базы данных 1

Ограничение времени:1 сек
Ограничение памяти:256 Мб

Условие

Ответьте на следующие вопросы.

Вопрос 1

Перед вами представлена схема БД. Укажите номера которые следует заменить множественностью 1.

1 2 3 4 5 6 7 8

Вопрос 2

Перед вами представлена БД. Какое поле и в какую таблицу необходимо добавить, чтобы структура БД оказалась верной.

Поле BookTypeID в таблицу Author Поле BookID в таблицу BookType Поле BookTypeID в таблицу Book Поле AuthorID в таблицу BookType

Вопрос 3

Перед вами представлены схемы баз данных для социальной сети, в которой существуют пользователи, у каждого пользователя может быть N фотографий профиля. На какой из схем представлена правильно нормализованная и удовлетворяющая требованиям база данных? Введите номер схемы в качестве ответа.

Формат выходных данных

Для решения данного задания необходимо правильно выбрать варианты ответа, либо ввести номер ответа, если этого требует задание.


Задача 4B. Номализация базы данных 2

Ограничение времени:1 сек
Ограничение памяти:256 Мб

Условие

Ответьте на следующие вопросы.

Вопрос 1

Перед вами представлена схема БД. Выберите номер неверно проставленного отношения.

1 2 3 4

Вопрос 2

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

Вопрос 3

Перед вами представлены схемы баз данных для сети автомобильных салонов. Текущая реализация базы данных находится в первой нормальной форме, при этом известно, что скидка на автомобиль, а, следовательно, и его конечная цена, зависит от его Бренда. Что необходимо сделать для того чтобы БД находилась во второй нормальной форме?

Ничего не делать Создать таблицу с полями: Price, Discount, Brand. Создать таблицу с полями: Price, Discount. Создать таблицу с полями: Model, Discount.

Формат выходных данных

Для решения данного задания необходимо правильно выбрать варианты ответа, либо ввести номер ответа, если этого требует задание.


1.230s 0.016s 137