Методические указания к лабораторной работе №4 «Базы данных. Язык запросов SQL»
Данные методические указания содержат необходимые теоретические сведения и практические задания по лабораторной работе «Базы данных». Лабораторная работа ориентирована на изучение синтаксиса языка запросов SQL. Некоторые описываемые особенности синтаксиса специфичны для диалекта, используемого Microsoft Office Access.
Введение
Исторически для хранения данных применялся подход, основанный на файлах и использовании средств языков общего назначения. Этот подход порождал большое число проблем, связанных с тем, что между файлами имело место существенное дублирование информации, программы для обработки файлов существенно зависели от формата файлов и даже простые изменения формата файлов требовали существенных переработок программ. Кроме того, при большом числе файлов даже типовые задачи выборки необходимой информации сопряжены с достаточно высоким уровнем сложности.
Решением перечисленных проблем стала унификация подхода к хранению данных и отделению хранимых данных от обрабатывающих их программ. Задачи манипулирования данными, обеспечения надежности доступа и резервного копирования взяли на себя системы управления базами данных (СУБД), а прикладные задачи по выборке, обработке и представлению данных взяли на себя прикладные программы. Существенным шагом на пути к упрощению логики программ, обрабатывающих выборки данных, стало создание унифицированного языка запросов SQL (Structured Query Language). В настоящее время язык SQL стандартизирован международными организациями ANSI и ISO и в той или иной степени поддерживается большим числом различных СУБД, включая Microsoft Office Access, Microsoft SQL Server, Oracle, MySQL.
Выборка данных
Для выборки данных используется оператор SELECT. В простейшей форме оператор требует указания списка выбираемых столбцов в секции SELECT и списка таблиц в секции FROM:
SELECT
поле1 [, поле2,...]
FROM
таблица1 [, таблица2,...];
Если в запросе используется несколько таблиц, то поле можно для уточнения квалифицировать именем таблицы. Если имя поля состоит из нескольких слов, то их заключают в квадратные скобки []. Пример запроса, выбирающего информацию обо всех заказах из таблицы заказов:
SELECT
[Orders].[OrderID],
[Orders].[OrderDate],
[Orders].[CustomerID]
FROM
[Orders];
Имеется возможность выбрать сразу все поля таблицы, не перечисляя их:
SELECT
*
FROM
[Orders];
В секции SELECT можно указывать не только имена полей, но и формировать из них выражения с использованием арифметических операций +, -, *, /, встроенных функций и других операций.
Сортировка данных
При выборке данных с помощью оператора SELECT без явного указания порядка сортировки записи возвращаются в произвольном порядке (в зависимости от СУБД этот порядок может соответствовать или не соответствовать физическому размещению записей). Для явного указания порядка записей в результирующем запросе необходимо использовать секцию ORDER BY:
ORDER BY
поле1 [ ASC | DESC ] [, поле2 [ ASC | DESC ],...]
Следующий пример иллюстрирует базовый синтаксис секции ORDER BY:
SELECT
[Products].[ProductName]
FROM
[Products]
ORDER BY
[Products].[ProductName];
Для явного указания порядка сортировки предназначены ключевые слова ASC и DESC. Ключевое слово ASC означает сортировку по возрастанию и подразумевается по умолчанию. Ключевое слово DESC означает сортировку по убыванию. Следующий пример иллюстрирует сортировку по убыванию:
SELECT
[Products].[ProductName]
FROM
[Products]
ORDER BY
[Products].[ProductName] DESC;
В секции ORDER BY имеется возможность указания нескольких полей, по которым осуществляется сортировка. При этом важным остается порядок указания этих полей: в первую очередь сортировка производится по первому указанному полю, во вторую очередь внутри групп с одинаковым значением первого поля производится сортировка по второму полю и т.д.
Фильтрация данных
По умолчанию запрашиваемые данные получаются неотфильтрованными. Для фильтрации используется секция WHERE запроса. В секции WHERE указывается выражение фильтрации и в результирующую выборку попадают только те записи, для которых выражение фильтрации истинно.
В выражении фильтрации можно ссылаться на все поля всех таблиц, участвующих в запросе и формировать из них выражения. Для формирования выражений могут использоваться арифметические операции +, -, *, /, операции сравнения =, <>, >, <, операции сравнения с NULL-значением IS NULL и IS NOT NULL. В следующем запросе получаются все товары, у которых удвоенная оптовая цена меньше розничной:
SELECT
[Products].[ProductName],
[Products].[ProductWholesalePrice],
[Products].[ProductRetailPrice]
FROM
[Products]
WHERE
2*[Products].[ProductWholesalePrice] < [Products].[ProductRetailPrice]
ORDER BY
[Products].[ProductName];
Для строковых значений поддерживается фильтрация значений с неполным совпадением. Для этого используется операция LIKE с шаблоном поиска, содержащим специальные символы? (один произвольный символ) и * (произвольное количество любых символов). Следующий запрос выбирает всех покупателей, фамилия которых начинается на букву К:
SELECT
*
FROM
[Customers]
WHERE
[Customers].[CustomerSurname] Like 'К*';
Условия секции WHERE можно группировать с помощью логических операций AND, OR и NOT (при вычислениях среди указанных операций наибольший приоритет имеет операция NOT, наименьший – операция OR). Когда стандартные приоритеты операций не устраивают, необходимо использовать скобки. Следующий запрос возвращает все товары, у которых розничная цена равна оптовой, либо оптовая цена больше 10 и поставки прекращены (для установления нужного порядка вычислений используются скобки):
SELECT
*
FROM
[Products]
WHERE
[Products].[ProductWholesalePrice] =
[Products].[ProductRetailPrice]
OR ([Products].[ProductWholesalePrice] > 10
AND [Products].[ProductSupplied]);
Использование функций
При формировании выражений SQL-запросов можно использовать встроенные функции. Имена функций даже для простейших операций сильно зависят от используемой СУБД.
В таблице 1 представлены некоторые полезные функции СУБД Microsoft Access, которые можно использовать в SQL-запросах:
Таблица 1. Встроенные функции
| Имя функции | Назначение |
| LTRIM | Удаляет ведущие пробелы |
| RTRIM | Удаляет ведомые пробелы |
| UCASE | Переводит символы в верхний регистр |
| LCASE | Переводит символы в нижний регистр |
| LEN | Возвращает длину строки |
| NOW | Возвращает сегодняшнюю дату |
| DATEPART | Выбирает нужную часть даты |
| ISNULL | Проверяет, что аргумент равен NULL |
Следующий запрос возвращает год из текущей даты:
SELECT
DATEPART(‘yyyy’, NOW())
Агрегирующие функции
Агрегирующие функции позволяют выбрать из исходной выборки итоговые данные — суммарные и средние значения, количество значений. Если в запросе используется хотя бы одна агрегирующая функция, необходимо использовать агрегирующие значения во всех выражениях секции SELECT (либо поля без агрегирующий функций должны быть группирующими).
Список агрегирующих функций и их назначения описаны в таблице 2.
Таблица 2. Агрегирующие функции
| Имя функции | Назначение |
| SUM | Сумма значений столбца |
| MIN | Минимальное значение столбца |
| MAX | Максимальное значение столбца |
| COUNT | Число строк в выборке |
| AVG | Среднее значение столбца |
Следующий запрос подсчитывает сумму детальных строк всех заказов:
SELECT
SUM(OrderItem.OrderItemPrice)
FROM
OrderItems;
Для того чтобы в одном запросе использовать агрегирующие функции одновременно с обычными выражениями для столбцов, необходимо использовать группировку с помощью секции GROUP BY. Обобщено синтаксис данной секции можно представить следующим образом:
GROUP BY
поле1 [, поле2...]
Следующий запрос подсчитывает сумму детальных строк в разрезе конкретных заказов:
SELECT
OrderItems.OrderID,
SUM(OrderItems.OrderItemPrice)
FROM
OrderItems
GROUP BY
OrderItems.OrderID;
Использование подзапросов
Подзапрос — это оператор SELECT, вложенный в другую операцию выборки или обработки данных. Подзапрос оформляется в полном соответствии с синтаксисом обычных запросов, но при этом всегда должен возвращать выборку ровно из одного столбца. Подзапрос можно использовать в качестве выражения для столбца в секции SELECT запроса, либо в качестве ограничения на выбираемые данные в секции WHERE запроса. Внутри подзапроса допускается обращение к полям из внешнего по отношению к нему запроса. Это означает, что подзапрос должен вычислиться для каждой строки выборки внешнего запроса.
Следующий пример иллюстрирует, как можно выбрать имена всех заказчиков, которые заказывали заданный продукт с помощью вложенных подзапросов:
SELECT *
FROM [Customers]
WHERE [Customers].[CustomerID] IN
(SELECT [Orders].[CustomerID]
FROM [Orders]
WHERE [Orders].[OrderID] IN
(SELECT [OrderItems].[OrderID]
FROM [OrderItems]
WHERE [OrderItems].[ProductID] IN
(SELECT [Products].[ProductID]
FROM [Products]
WHERE [Products].[ProductName] = 'Цейлонский чай')));
Следующий пример иллюстрирует, как можно вычислить для каждого заказчика количество поступивших от него заказов:
SELECT
[Customers].[CustomerSurname],
[Customers].[CustomerName],
(SELECT
COUNT(*)
FROM
[Orders]
WHERE
[Orders].[CustomerID] = [Customers].[CustomerID]) AS [OrderCount]
FROM
[Customers];
При использовании подзапросов необходимо помнить о том, что каждый дополнительный уровень вложенности подзапросов чреват увеличением времени выполнения запроса. Поэтому необходимо стремиться сводить использование подзапросов к минимуму, а для некоторых задач находить более приемлемое по производительности решение с использованием объединения таблиц.
Объединение таблиц
Особенностью оператора SELECT является то, что он оперирует выборкой, а не конкретной таблицей базы данных. Это означает, что выборка столбцов и вычисление условия фильтрации выполняются для каждой строки множества исходных записей разных таблиц, указанных в секции FROM запроса. Простейший случай объединения заключается в перечислении объединяемых таблиц через запятую в секции FROM запроса и наложении связывающего таблицы ограничения в секции WHERE. Например, следующий запрос выбирает имена всех заказчиков и показывает все поступившие от них заказы:
SELECT
[Customers].[CustomerSurname],
[Customers].[CustomerName],
[Orders].[OrderID]
FROM
[Customers],
[Orders]
WHERE
[Customers].[CustomerID] = [Orders].[CustomerID];
Без использования секции WHERE запрос, связывающий две таблицы посредством такого объединения, вернет полное декартово произведение всех строк первой таблицы на все строки второй таблицы. Таким образом, число записей в результирующей выборке будет равно произведению числа записей в первой таблице на число записей во второй.
Более общие случаи объединения таблиц формируются с помощью специального синтаксиса JOIN-секций, которые позволяют задавать внутреннее и внешнее объединение таблиц. Внутреннее объединение означает выборку только связанных записей из обеих таблиц (то есть запись из одной таблицы имеет соответствие в другой). Такое объединение формируется с помощью секции INNER JOIN. Внешнее объединение означает выборку связанных записей из обеих таблиц, но одновременно с этим предоставляется возможность выбрать записи, не имеющие связанных, только из первой, только из второй или из обеих таблиц. Такое объединение формируется с помощью секций LEFT JOIN (выбираются все записи из первой таблицы), RIGHT JOIN (выбираются все записи из второй таблицы), FULL JOIN (выбираются записи из обеих таблиц, а также записи каждой из таблиц, не имеющие связанных).
Синтаксис JOIN-секций может быть обобщенно описан следующим образом:
первая присоединяемая таблица
INNER | LEFT | RIGHT | FULL JOIN
вторая присоединяемая таблица
ON условие объединения
В следующем примере выбирается количество заказов для всех заказчиков, при этом в результирующей выборке присутствуют записи для заказчиков, не имеющих заказов:
SELECT
[Customers].[CustomerSurname],
[Customers].[CustomerName],
COUNT([Orders].[OrderID]) AS [OrderCount]
FROM
[Customers]
LEFT JOIN
[Orders]
ON
[Customers].[CustomerID] = [Orders].[CustomerID]
GROUP BY
[Customers].[CustomerSurname],
[Customers].[CustomerName];
В следующем примере выбираются все покупатели, покупавшие указанный товар (ключевое слово DISTINCT устраняет из выборки дублирующиеся записи):
SELECT DISTINCT
[Customers].*
FROM
[Customers]
INNER JOIN
([Orders]
INNER JOIN
([OrderItems]
INNER JOIN
[Products]
ON
[Products].[ProductID] = [OrderItems].[ProductID])
ON
[OrderItems].[OrderID] = [Orders].[OrderID])
ON
[Orders].[CustomerID] = [Customers].[CustomerID]
WHERE
[Products].[ProductName] = 'Цейлонский чай'
Можно заметить, что многие запросы можно выполнить несколькими способами. Последние приведенные примеры показывают, что нужного результата часто можно добиться с использованием подзапросов, либо с использованием объединений. Предпочтение в зависимости от ситуации следует отдавать либо более производительному, либо более удобочитаемому варианту.
Тестовые данные
Все приведенные примеры запросов тестировались на базе данных Microsoft Office Access 2007 (тестовая база данных предоставляется вместе с данными методическими указаниями). Для того чтобы воспроизвести результат этих запросов, необходимо создать аналогичную по структуре базу данных и наполнить ее тестовыми данными. Тестовая база данных представляет собой модель базы данных, предназначенной для автоматизации взаимодействия организации с покупателями. В базе данных содержатся сведения о покупателях, их заказах и продуктах.
Таблица Customers содержит информацию о покупателях. Записи таблицы уникально идентифицируются по значению числового автоинкрементного поля CustomerID. В остальных полях таблицы содержится информация о покупателе: имя (CustomerName), фамилия (CustomerSurname), домашний адрес (CustomerAdress) и адрес электронной почты (CustomerEMail). Исходное содержимое таблицы Customers представлено в табл. 3.
Таблица Orders содержит заказы покупателей. Один заказ может включать несколько различных продуктов. Записи таблицы заказов уникально идентифицируются по значению автоинкрементного поля OrderID. В остальных полях таблицы содержится номер покупателя (CustomerID) и дата заказа (OrderDate). Исходное содержимое таблицы Orders представлено в табл. 4.
Таблица OrderItems содержит детальную информацию о заказах: одна строка таблицы соответствует одному виду заказываемого покупателем в рамках одного заказа. Записи таблицы уникально идентифицируются по значению автоинкрементного поля OrderItemID. Дополнительно в детальной строке заказа указывается номер заказа (OrderID), к которому относится детальная строка, количество заказанных единиц продукта (OrerItemQuantity) и общая цена строки заказа (OrderItemPrice). Исходное содержимое таблицы OrderItems представлено в табл. 5.
Таблица Products содержит сведения о продуктах. Записи таблицы уникально идентифицируются по значению автоинкрементного поля ProductID. Дополнительно о каждом продукте в таблице записывается его название (ProductName), описание (ProductDescription), оптовая цена (ProductWholesalePrice), розничная цена (ProductRetailPrice) и признак непрекратившихся поставок (ProductSupplied). Исходное содержимое таблицы Products представлено в табл. 6.
Структура таблиц тестовой базы данных и их связи представлены на рис. 1. 
| Рис. 1. Структура таблиц тестовой базы данных |
Таблица 3. Исходное содержимое таблицы Customers
| № | CustomerName | CustomerSurname | CustomerAddress | CustomerEMail |
| Иван | Березин | ул. Ленина, 42-41 | Berezin@gmail.com | |
| Сергей | Борисов | ул. Карла Маркса, 2-15 | Borisov@mail.ru | |
| Филипп | Важин | ул. Стадионная, 11-61 | Vazhin@yahoo.com | |
| Олег | Репин | ул. Октября, 71-25 | Repin@mail.ru | |
| Алексей | Александров | ул. Горького, 17-43 | Alexandrov@gmail.com |
Таблица 4. Исходное содержимое таблицы Orders
| OrderID | CustomerID | OrderDate |
| 21.02.2010 | ||
| 23.02.2010 | ||
| 18.02.2010 |
Таблица 5. Исходное содержимое таблицы OrderItems
| OrderItemID | OrderID | ProductID | OrderItemQuantity | OrderItemPrice |
| 18,00 | ||||
| 18,00 | ||||
| 18,00 | ||||
| 15,00 |
Таблица 6. Исходное содержимое таблицы Products
| ProductID | ProductName | ProductWhole-salePrice | ProductRe-tailPrice | Product-Supplied | ProductDes-cription |
| Цейлонский чай | 13,50 | 18,00 | True | Цейлонский чай | |
| Сироп | 7,50 | 10,00 | True | Сироп | |
| Оливковое масло | 16,00 | 21,35 | True | Оливковое масло | |
| Карри | 30,00 | 40,00 | True | Банка по 400 г. | |
| Мармелад | 60,75 | 81,00 | True | В коробке 10 штук | |
| Пиво | 10,50 | 14,00 | True | В бутылке 12 унций | |
| Кофе | 34,50 | 46,00 | True | Банка по 500 г. | |
| Пельмени | 28,50 | 38,00 | False | Пакет по 250 г. |
Варианты заданий
В вариантах заданий предлагается создать новую базу данных, наполнить ее тестовыми данными и создать запросы с использованием языка запросов SQL. Дополнительно предлагается создать формы для редактирования всех таблиц базы данных. Одна из форм должна включать в себя подчиненную форму с детальной информацией из другой таблицы или другого запроса.
Вариант 1 «Хранилище документов»
База данных информационной системы предназначена для хранения документов организации. Для разграничения доступа к документам пользователям назначаются права доступа. Предусмотрено 4 типа прав доступа на документ: нет доступа, только просмотр, изменение, полные права (удаление документов, назначение прав). Пользователи, обладающие правами на изменение, могут создавать новые документы или новые версии имеющихся документов с сохранением предыдущих версий в базе данных.
Таблица документов Documents содержит название документа DocumentName и вид документа DocumentKind (заявление, акт, служебная записка и т.д.). Таблица версий документов DocumentVersions содержит название документа (поле DocumentName из таблицы Documents), номер версии DocumentVersionNumber (эти два поля являются ключом таблицы), номер автора версии документа AuthorID (выбирается из таблицы пользователей), дату создания версии DocumentVersionCreateDate, дату последнего изменения версии DocumentVersionChangeDate и тело версии DocumentVersionBody. Таблица прав DocumentRights содержит идентификатор строки DocumentRightsID, номер пользователя UserID, тип прав DocumentRightType (просмотр, изменение, полный, нет прав доступа). Таблица пользователей Users содержит номер пользователя UserID и имя пользователя UserName.
Необходимо создать базу данных соответствующей структуры и наполнить ее тестовыми данными. Затем составить и проверить результат выполнения следующих запросов:
1. Выбрать все экземпляры документов, название которых содержит слово «акт».
2. Найти имена всех пользователей, имеющих права не менее, чем на просмотр.
3. Найти все документы, версии которых изменялись в этом году.
4. Найти все документы, для которых пользователь, являющийся автором, не имеет полных прав на документ.
5. Для каждого документа вычислить количество версий, дату последнего изменения и количество разных авторов.
Вариант 2 «Музыкальный архив»
Информационный портал предоставляет пользователям возможность работы с музыкальными файлами разных исполнителей. В базе данных информационного портала хранится информация о композициях, альбомах и исполнителях. Композиции могут относиться к различным жанрам: рок, джаз, классическая музыка и т.д. Для композиции также указываются атрибуты соответствующего музыкального файла: битрейт (128 кбит/c, 192 кбит/c и т.д.), формат (MP3, OGG, FLAC), размер файла.
Таблица композиций Compositions содержит информацию о музыкальных файлах: столбец CompositionID содержит номер композиции, столбец CompositionName содержит название композиции, столбец CompositionLyrics содержит текст песни (если есть), столбец CompositionBody содержит тело музыкального файла в соответствующем формате, столбец CompositionFormat содержит формат музыкального файла, столбец CompositionBitRate содержит битрейт музыкального файла, столбец CompositionSize содержит размер файла, столбец CompositionGenre содержит жанр композиции, столбец AlbumID содержит номер альбома (может отсутствовать, подстановка из таблицы альбомов). Таблица альбомов Albums содержит номер альбома AlbumID, название альбома AlbumName, год выпуска альбома AlbumYear и обложку альбома AlbumCover. Таблица CompositionPerformers содержит соответствие между композициями и исполнителями: столбец CompositionID содержит номер композиции, столбец PerformerID содержит номер исполнителя. Таблица исполнителей Performers содержит номер исполнителя PerformerID, имя исполнителя PerformerName, фамилию исполнителя PerformerSurname, дату рождения PerformerBithdate, место рождения PerformerBirthPlace, фотографию PerformerPhoto.
Необходимо создать базу данных соответствующей структуры и наполнить ее тестовыми данными. Затем составить и проверить результат выполнения следующих запросов:
1. Выбрать все композиции, записанные в формате без потери качества и относящиеся к жанру «джаз».
2. Найти все композиции, вышедшие в этом году.
3. Найти все композиции, исполненные Pat Metheny в 1984–1987 гг.
4. Для каждого исполнителя вывести число его композиций, число его альбомов, а также дату выхода первого и последнего альбомов.
5. Найти все альбомы, в которых нет композиций.
Вариант 3 «Библиотека»
Информационная система предназначена для автоматизации работы библиотеки. Оперативную информацию о книгах, читателях и читаемых книгах в систему заносят библиотекари. Система нацелена на упрощение поиска нужных книг читателями и упрощение работы библиотекарей с карточками читателей.
Таблица Books содержит информацию о книгах: столбец BookID содержит номер книги, столбец BookName содержит заглавие книги, столбец BookCover содержит обложку книги, столбец BookGenre содержит жанр книги, столбец BookPublicYear содержит дату публикации книги, столбец BookPublisher содержит издателя книги, столбец BookISBN содержит ISBN-код книги. Таблица BookInstances соответствует конкретному экземпляру книги в хранилище библиотеки: столбец BookInstanceID содержит номер экземпляра книги, столбец BookInstanceShelve содержит номер полки, на которой находится книга. Таблица читателей Readers содержит следующую информацию: номер читателя ReaderID, имя читателя ReaderName, фамилию читателя ReaderSurname, дату рождения читателя ReaderBithdate. Таблица карточек читателей ReaderItems содержит информацию по каждой книге, которую читатель берет на руки: столбец ReaderItemID содержит номер строки в таблице, столбец ReaderItemBookInstanceID содержит номер экземпляра книги, столбец ReaderItemStartDate содержит дату получения книги на руки, столбец ReaderItemReturnDate содержит дату возвращения книги в библиотеку, столбец ReaderItemDeadlineDate содержит дату окончания срока использования книги читателем.
Необходимо создать базу данных соответствующей структуры и наполнить ее тестовыми данными. Затем составить и проверить результат выполнения следующих запросов:
1. Выбрать все книги, название которых начинается со слова «приключения».
2. Найти все книги, которые читал указанный читатель.
3. Найти все книги, которые находятся на руках у читателей дольше положенного срока.
4. Для каждого читателя вычислить, сколько разных книг он брал на руки, сколько книг у него сейчас на руках и по какому числу книг он превысил срок сдачи.
5. Найти все книги, которые ни разу никем не читались.
Вариант 4 «Почтовый сервер»
Почтовый сервер предназначен для получения и отправки сообщений электронной почты. С момента прихода или создания пользователем все сообщения помещаются в общее хранилище. С точки зрения пользователей сообщения хранятся в специальных папках «Входящие», «Исходящие», «Спам», «Корзина». Сообщение кроме тела и атрибутов со значениями отправителя, получателя и темы может также содержать произвольное количество вложений.
Таблица Users содержит список пользователей почтового сервера: столбец UserID содержит номер пользователя, столбец UserName содержит имя пользователя (используется имя в стиле логин@домен). Таблица UserFolders содержит папки пользователей: столбец UserFolderID содержит номер папки, столбец UserFolderName содержит имя папки, столбец UserID содержит номер пользователя, которому принадлежит папка. Таблица Mails содержит все сообщения всех пользователей: столбец MailID содержит номер сообщения пользователя, столбец MailSubject содержит тему сообщения, столбец MailFrom содержит список отправителей сообщения, столбец MailTo содержит список получателей сообщения, столбец UserFolderID содержит номер папки пользователя (сообщение может находиться не более, чем в одной папке), столбец MailIsImportant содержит признак важности сообщения, столбец MailCreateDate содержит дату создания сообщения, столбец MailIsRead содержит признак прочтенности сообщения, столбец MailSize содержит размер сообщения. Таблица MailAttachments содержит вложения сообщения: столбец MailAttachmentID содержит номер вложения, столбец MailAttachmentBody содержит тело вложения, столбец MailID содержит номер соответствующего сообщения.
Необходимо создать базу данных соответствующей структуры и наполнить ее тестовыми данными. Затем составить и проверить результат выполнения следующих запросов:
1. Выбрать все непрочтенные сообщения, тема которых содержит слово «Microsoft».
2. Выбрать все сообщения конкретного пользователя, находящиеся в его папках «Входящие», «Исходящие», «Спам», «Удаленные».
3. Найти все сообщения, пришедшие конкретному пользователю сегодня.
4. Для каждой папки каждого пользователя вывести число прочтенных и непрочтенных сообщений, а также общий размер папки.
5. Для каждого пользователя посчитать число сообщений с вложениями и без вложений.
Вариант 5 «Страховая компания»
Страховая компания заключает со страховщиками договоры страхования. В рамках договора страховщик обязуется выплатить в течение определенного времени определенную сумму, которая называется страховой премией. Погашение страховой премии может быть разделено на несколько платежей. В свою очередь страховая компания обязуется выплатить страховщику определенную сумму в пределах страховой суммы в случае возникновения страховых случаев (в зависимости от оценки случая страховая компания может выплатить не всю страховую сумму клиенту, а только часть). Необходимо разработать базу данных страховой компании.
Таблица Agents содержит список страховых агентов: столбец AgentID содержит номер страхового агента, столбец AgentName содержит фамилию и инициалы страхового агента, столбец AgentPhoto содержит фотографию страхового агента. Таблица Insurer содержит информацию о страховщиках: столбец InsurerID содержит номер страховщика, столбец InsurerName содержит фамилию, имя и отчество страховщика, столбец InsurerBirthDate содержит дату рождения страховщика, столбец InsurerPassport содержит номер паспорта страховщика. Таблица Contracts содержит информацию о заключеннных договорах страхования: столбец ContractID содержит номер договора страхования, столбец ContractStartDate содержит дату начала действия договора страхования, столбец ContractEndDate содержит дату окончания действия договора страхования, столбец InsuredSum содержит страховую сумму, столбец InsurancePremium содержит страховую премию, столбец AgentID содержит номер страхового агента, отвечающего за договор, столбец InsurerID содержит номер страховщика, заключившего договор. Таблица Payments содержит информацию о взносах по договорам страхования: столбец PaymentID содержит номер взноса, столбец ContractID содержит номер договора страхования, столбец PaymentSum содержит сумму взноса, столбец PaymentDate содержит дату взноса.
Необходимо создать базу данных соответствующей структуры и наполнить ее тестовыми данными. Затем составить и проверить результат выполнения следующих запросов:
1. Выбрать всех страховщиков моложе 30 лет, фамилия которых начинается на букву «А».
2. Выбрать все договоры страхования конкретного страховщика, срок действия которых еще не закончился, но уже начался.
3. Найти все платежи, которые совершил конкретный страховщик (известно его имя, а не номер) за последние три месяца.
4. Для каждого страхового агента вывести количество заключенных им в этом году договоров страхования и общую сумму этих договоров.
5. Для каждого договора страхования вывести страховую премию и сумму страховых взносов.
Вариант 6 «Сервисный центр»
Сервисный центр обеспечивает гарантийное обслуживание бытовой техники и электроники. При продаже товара в магазине с покупателем заключается договор гарантийного обслуживания на определенный срок. Покупатель в течение данного срока может обращаться по поводу неисправностей приобретенной им техники в сервисный центр. Однако сервисный центр может также предоставлять платные услуги за пределами гарантийного срока или для товаров, приобретенных у стороннего поставщика. Необходимо разработать базу данных для автоматизации работы сервисного центра.
Таблица Clients содержит перечень клиентов сервисного центра (заполняется в момент продажи товара в магазине): столбец ClientID содержит номер покупателя, столбец ClientName содержит имя покупателя, столбец ClientPassportNumber содержит номер паспорта покупателя. Таблица Cailms содержит заявки на обслуживание в сервисном центре: столбец ClaimID содержит номер заявки, столбец ClientID содержит номер покупателя, поместившего заявку, столбец ClaimDate содержит дату поступления заявки, столбец SpecialistID содержит номер специалиста, которому назначено исполнение заявки, столбец State содержит текущее состояние заявки (на регистрации, на исполнении, выполнено, отказано). Таблица StorageObjects содержит список объектов, принятых на хранение на время выполнения гарантийного обсуживания: столбец StorageObjectID содержит номер объекта, столбец StorageObjectName содержит название объекта, столбец StorageObjectDescription позволяет занести более подробное текстовое описание объекта, столбец StorageObjectEnterDate содержит дату приемки объекта (если объект еще не приняли на склад, то дата не заполнена), столбец StorageObjectLeaveDate содержит дату выбытия объекта со склада (если объект еще находится на складе, то эту дату оставляют пустой). Таблица Specialists содержит список специалистов, выполняющих услуги сервисного центра по ремонту техники: столбец SpecialistID содержит номер специалиста, стобец SpecialistName содержит фамилию специалиста.
Необходимо создать базу данных соответствующей структуры и наполнить ее тестовыми данными. Затем составить и проверить результат выполнения следующих запросов:
1. Выбрать всех специалистов старше 30 лет, фамилия которых заканчивается на букву «в».
2. Выбрать все заявки, переведенные на конкретного специалиста, которые поступили в течение последнего месяца, но еще не выполнены и не отказаны.
3. Найти на складе все объекты, которые принадлежат конкретному клиенту (для клиента известен только номер его паспорта).
4. Для каждого специалиста вывести число обработанных им в течение месяца заявок и среднее время обработки одной заявки.
5. Для каждого объекта, побывавшего на складе в течение года, выяснить время нахождения его на складе (если объект еще находится на складе, то его не рассматривать).
Список литературы
1. Форта Б. Освой самостоятельно SQL. 10 минут на урок, 3-е издание: Пер. с англ. — М.: Издательский дом «Вильямс», 2005. — 288 с.
2. Дейт К. Введение в системы баз данных, 7-е издание. Пер. с англ. — М.: Издательский дом «Вильямс», 2001. — 1072 с.
3. Дунаев В. Базы данных. Язык SQL. — СПб.: БХВ-Петербург, 2006. — 288 с.