В базе данных торговой компании хранится список сотрудников 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).
Описание полей запроса:
Department - название департамента;
TotalPeople - количество сотрудников в департаменте.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
На автостоянке эксплуатируется БД, в которой содержится информация о том, какому автомобилю из 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).
Описание полей запроса:
CarNumber - номер автомобиля;
ParkingNumber - номер парковочного места.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
В БД хранится список специалистов тех. поддержки 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).
Описание полей запроса:
FullName - ФИО специалиста;
IncidentTitle - название инцидента.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
Главврачу необходимо знать актуальную информацию о наличии страховых медицинских полисов
у мед. работников и пациентов.
В БД больницы хранится список врачей 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-запрос.
Руководитель магазина решил наградить 10 самых продуктивных сотрудников. Для этого
ему нужен запрос, который посчитает суммарную выручку каждого сотрудника магазина,
отсортирует по убыванию суммарной выручки и возьмёт первые 10 кортежей. Выводить
следует только продающих сотрудников.
Требуется по фрагменту схемы реляционной БД составить данный запрос на языке SQL.
Описание таблиц на схеме:
Sellers - сотрудники магазина (продавцы);
Products - продаваемые товары;
Sales - продажи;
SalesItems - позиции продаж.
Результатом выполнения запроса должен быть список кортежей (SellerFullName,
TotalRevenue, CountOfSales) отсортированный по убыванию значений поля TotalRevenue
и содержащий максимум 10 записей. Описание полей запроса:
SellerFullName - полное имя продавца (Фамилия и Имя через пробел);
TotalRevenue - суммарная выручка продавца, полученная от продажи товаров;
CountOfSales - совокупное количество продаж.
Решение следует представить в виде текстового файла, содержащего единственный SQL-запрос.
