 |
Задание к лабораторной работе № 3
|
|
|
|
Лабораторная работа №3. Запрос, использующий соединение нескольких таблиц
Декартово произведение. Самый общий случай соединения нескольких таблиц. Результирующие строки формируются как все возможные комбинации строк всех указанных таблиц.
Пример 1
SELECT Purchasing.Vendor.*, Production.Product.*, Purchasing.ProductVendor.*
FROM Purchasing.Vendor, Production.Product, Purchasing.ProductVendor
Такие запросы редко имеют практический смысл, так как в результате формируют строки семантически не связанной информации. К декартову произведению можно применить фильтрацию результирующих строк по признаку истинности отношения значений пары столбцов в разных таблицах, например, оставляя в окончательной выборке только те строки, для которых Таблица1.СтолбецА > Таблица2. СтолбецB. Такое соединение таблиц называется тета-соединением. В некоторых источниках отношениями для формирования тета-соединения считаются только отношения (>, <, >=, <=). Другие источники не накладывают ограничений на вид отношений.
В реальности, как правило, используются запросы с соединением таблиц, связанных с помощью ключевых полей. В этом случае из декартова произведения фильтруются только те строки, которые содержат одинаковые значения пар ключевых полей. Такое соединение таблиц называется эквисоединением (соединением по эквивалентности). В качестве механизма фильтрации может выступать традиционный фильтр WHERE.
Пример 2
SELECT Purchasing.Vendor.*, Production.Product.*, Purchasing.ProductVendor.*
FROM Purchasing.Vendor, Production.Product, Purchasing.ProductVendor
WHERE Purchasing.Vendor.VendorID=Purchasing.ProductVendor.VendorID
AND Production.Product.ProductID=Purchasing.ProductVendor.ProductID
Такого же результата можно достичь, и используя другой синаксис предложения SELECT:
Пример 3
SELECT Purchasing.Vendor.*, Production.Product.*, Purchasing.ProductVendor.*
FROM (Purchasing.Vendor
INNER JOIN Purchasing.ProductVendor
ON Purchasing.Vendor.VendorID=Purchasing.ProductVendor.VendorID)
INNER JOIN Production.Product
ON Production.Product.ProductID=Purchasing.ProductVendor.ProductID
|
|
|
В большинстве СУБД использование именно такого синтаксиса является предпочтительным. Такой тип соединения может носить название внутреннего соединения.
На основе внутреннего соединения нескольких таблиц, также как и по одной таблице, можно осуществлять проекцию, указывая в предложении SELECT только необходимый набор столбцов
Пример 4
SELECT Production.Product.Name, Purchasing.Vendor.Name, Purchasing.ProductVendor.LastReceiptCost
FROM (Purchasing.Vendor
INNER JOIN Purchasing.ProductVendor
ON Purchasing.Vendor.VendorID=Purchasing.ProductVendor.VendorID)
INNER JOIN Production.Product
ON Production.Product.ProductID=Purchasing.ProductVendor.ProductID
ORDER BY Production.Product.Name;
Поскольку результатом соединения таблиц или проекции такого соединения является виртуальная таблица, в ней можно использовать агрегирующие функции, как и в случае с единственной таблицей
Пример 5
SELECT Production.Product.Name, min(Purchasing.ProductVendor.LastReceiptCost)
FROM (Purchasing.Vendor
INNER JOIN Purchasing.ProductVendor
ON Purchasing.Vendor.VendorID=Purchasing.ProductVendor.VendorID)
INNER JOIN Production.Product
ON Production.Product.ProductID=Purchasing.ProductVendor.ProductID
GROUP BY Production.Product.Name
ORDER BY Production.Product.Name
Внутреннее соединение позволяет получить только строки из нескольких таблиц, имеющие связь по указанному столбцу (столбцам). Если необходимо включить в результат запроса, кроме этой информации, все остальные строки из одной из объединяемых таблиц – используется внешнее соединение.
Пример 6
SELECT Person.Contact.FirstName, Person.Contact.LastName, HumanResources.Employee.Title
FROM Person.Contact LEFT OUTER JOIN HumanResources.Employee
ON Person.Contact.ContactID=HumanResources.Employee.ContactID
Такой запрос выдаст значения соответствующих столбцов из всех строк первой (левой) таблицы и значения указанного столбца для тех строк из второй таблицы, для которых будет справедлива связь по ключевому полю с первой таблицей. Для тех строк, для которых нет соответствия, во второй таблице будет указано значение NULL. Этот тип соединения таблиц называется левым внешним соединением.
|
|
|
Если нужно вывести, наоборот, все строки из второй (правой) таблицы и только связанные с ними значения из строк первой таблицы – вместо ключевого слова LEFT используется ключевое слово RIGHT. В результате получится правое внешнее соединение таблиц.
Чтобы получить все строки как из левой, так и из правой таблиц, связав те, у которых есть совпадение по ключевым полям, используется конструкция FULL OUTER JOIN.
Для внешних соединений, также как и для внутренних, можно использовать дополнительные условия для выборки и агрегирующие функции.
Задание к лабораторной работе № 3
1) Ознакомиться со структурой таблиц Production.Product, Purchasing.ProductVendor, Purchasing.Vendor, Purchasing.VendorContact, Person.Contact, HumanResources.Employee, Sales.SalesOrderHeader, Sales.SalesOrderDetail и Sales.SpecialOfferProduct
(http://msdn.microsoft.com/ru-ru/library/ms124438.aspx)
2) Ознакомиться со схемой данных БД AdventureWorks и проанализировать связи между указанными выше таблицами. Схему данных в Microsoft Visio можно скачать с microsoft.com по первой же ссылке на поисковый запрос «AdventureWorks OLTP Database Diagram»
3) Выполнить запросы, приведенные в примерах, и дать их содержательную интерпретацию.
4) Составить и выполнить следующие запросы (каждый запрос составить в отдельной вкладке):
4.1. Вывести фамилии, имена, должности и номера телефонов работников, для которых есть сведения о номере телефона.
4.2. Посчитать количество работников, для которых есть сведения об адресе электронной почты
4.3. Вывести список контактных лиц поставщиков запчастей и других товаров (Фамилия, Имя, Адрес электронной почты), отсортированный по названиям компаний-поставщиков.
4.4. Составить запрос, выводящий имена, фамилии и должности тех работников компании, которые также являются представителями компаний-поставщиков деталей и других товаров.
4.5. Получить отсортированный по алфавиту список (фамилия, имя, телефон) представителей поставщиков деталей и других товаров, использующихся в горной линейке велосипедов.
4.6. Получить список названий товаров туристической линейки. Для тех из них, которые закупаются у сторонних поставщиков, указать названия компаний-поставщиков.
4.7. Определить среднюю стандартную стоимость для классов продукции, закупаемой у сторонних поставщиков. Не учитывать товары, для которых класс не указан.
|
|
|
4.8. Получить перечень названий продуктов, вошедших в заказы на продажу, созданные 1 февраля 2004 года.
4.9. Получить общее количество проданных единиц каждого товара по каждому наименованию за всю историю продаж
4.10. Получить контактную информацию клиентов (имя, фамилия, телефон), чьи заказы в настоящий момент находятся в стадии выполнения.
|
|
|
