Главная | Обратная связь
МегаЛекции

Задание к лабораторной работе № 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. Получить контактную информацию клиентов (имя, фамилия, телефон), чьи заказы в настоящий момент находятся в стадии выполнения.

 

 





Рекомендуемые страницы:

Воспользуйтесь поиском по сайту:
©2015- 2020 megalektsii.ru Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав.