Использование подзапросов

Методические указания к лабораторной работе №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, вложенный в другую операцию подборки либо обработки Использование подзапросов данных. Подзапрос оформляется в полном согласовании с синтаксисом обыденных запросов, но при всем этом всегда должен возвращать подборку ровно из 1-го столбца. Подзапрос можно использовать в качестве выражения для столбца в секции 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 запрос, связывающий две таблицы средством такового объединения, возвратит полное декартово произведение всех строк первой Использование подзапросов таблицы на все строчки 2-ой таблицы. Таким макаром, число записей в результирующей выборке будет равно произведению числа записей в первой таблице на число записей во 2-ой.

Более общие случаи объединения таблиц формируются при помощи специального синтаксиса JOIN-секций, которые позволяют задавать внутреннее и наружное объединение таблиц. Внутреннее объединение Использование подзапросов значит подборку только связанных записей из обеих таблиц (другими словами запись из одной таблицы имеет соответствие в другой). Такое объединение формируется при помощи секции INNER JOIN. Наружное объединение значит подборку связанных записей из обеих таблиц, но сразу с этим предоставляется возможность избрать записи, не имеющие связанных, только из первой, только из Использование подзапросов 2-ой либо из обеих таблиц. Такое объединение формируется при помощи секций LEFT JOIN (выбираются все записи из первой таблицы), RIGHT JOIN (выбираются все записи из 2-ой таблицы), FULL JOIN (выбираются записи из обеих таблиц, также записи каждой из таблиц, не имеющие связанных).

Синтаксис JOIN-секций может быть обобщенно описан Использование подзапросов последующим образом:

1-ая присоединяемая таблица

INNER|LEFT|RIGHT|FULL JOIN

2-ая присоединяемая таблица

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 содержит детализированную информацию о заказах: одна строчка Использование подзапросов таблицы соответствует одному виду заказываемого покупателем в рамках 1-го заказа. Записи таблицы уникально идентифицируются по значению автоинкрементного поля 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@яху.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 с.


ispolzovanie-oborudovaniya-v-cementnoj-promishlennosti.html
ispolzovanie-ochkov-voli.html
ispolzovanie-operacii-vidavlivanie-k-eskizu-lomanaya-liniya.html