7.1. Использование представлений
Представление является еще одним объектом, составляющим логическую структуру любой базы данных. Представление для конечных пользователей выглядит как таблица, однако при этом не содержит данных, а лишь представляет их. Физически же представляемые данные расположены в различных таблицах базы данных.
Представление реализуется в виде сохраненного запроса, на основе которого и производится выборка из различных таблиц базы данных. Представления обладают следующими преимуществами:
• обеспечивают конфиденциальность информации, т.к. позволяют отобразить только необходимую информацию, скрывая определенные поля;
• упрощают визуальное восприятие данных, т.к. пользователь работает с представлением как с единой таблицей, которая создана на основе выборки данных из нескольких таблиц;
• управляют правами доступа к данным, например, вместо того чтобы предоставлять права на выполнение запросов к определенным полям таблиц, проще разрешить выполнение запросов через представление.
SQL Server предоставляет различные способы создания представлений, в частности, с помощью средств Transact-SQL.
7.2. Создание представлений с помощью Transact-SQL
Для создания представления используется команда CREATE VIEW:
CREATE VIEW ИмяПредставления [(поле [,…n])]
[ WITH ENCRYPTION ]
AS
ЗапросНаВыборку
При указании имени представления необходимо придерживаться ранее определенных правил именования объектов, также это имя не должно совпадать с именем уже существующей таблицы в базе данных.
Параметр WITH ENCRYPTION определяет шифрование кода запроса и гарантирует, что пользователи не смогут просмотреть и использовать его.
ЗапросНаВыборку представляет собой оператор SELECT, параметры которого и определяют содержимое представления. Имена полей представления задаются либо с помощью псевдонимов в операторе выборки, либо указываются в параметре поле.
Например, создадим представление, содержащее лишь следующую информацию о сотрудниках: номер, имя и фамилия сотрудника, дата рождения.
CREATE VIEW InfoEmployees ([Номер], [ФИО], [ДатаРождения])
AS
SELECT EmployeeKey,
FirstName+' '+LastName,
CONVERT (char(10), BirthDate, 104)
FROM AdventureWorksDW..DimEmployee
Содержимое представления можно просмотреть так же, как содержимое таблицы: через Обозреватель объектов или сформировав запрос:
SELECT * FROM InfoEmployees.
С помощью данного представления ограничен доступ к некоторым полям исходной таблицы DimEmployees, в этом случае говорят, что на таблицу наложен вертикальный фильтр, т.е. ограничен доступ к части полей таблицы без защиты на уровне столбцов.
Если в коде запроса выборки определено условие отбора записей, то говорят, что на таблицу наложен горизонтальный фильтр.
Например, следующее представление обеспечивает доступ к информации только о тех заказчиках, образование которых High School:
create VIEW InfoCustomersHighSchool
AS
SELECT FirstName, LastName, EnglishEducation
FROM AdventureWorksDW..DimCustomer
WHERE EnglishEducation='High School'
В запросе выборки может быть указана команда SELECT любой сложности, однако при этом не рекомендуется использовать раздел ORDER BY, который в дальнейшем можно применить при выборке данных из созданного представления. Так же рекомендуется при создании представлений на основе нескольких таблиц использовать только внутреннее соединение.
Например, создадим представление, отображающее для каждого заказчика суммарную стоимость его заказов:
CREATE VIEW InfoOrders
AS
SELECT FirstName+' '+LastName as [Заказчик],
convert (money, sum(UnitPrice*OrderQuantity),0) as [Общая стоимость заказов]
FROM (AdventureWorksDW..DimCustomer inner join AdventureWorksDW..FactInternetSales
ON DimCustomer.CustomerKey=FactInternetSales.CustomerKey)
GROUP BY FirstName+' '+LastName
Задание 1. Создайте процедуру, использующую представление InfoOrders, выводящую 5 заказчиков, являющихся лидерами закупок.
7.3. Управление представлениями
Созданное представление может быть изменено выполнением команды ALTER VIEW, имеющей аналогичный синтаксис команде CREATE VIEW. Для удаления представления необходимо выполнить команду:
DROP VIEW ИмяПредставления
Для получения информации о представлении используется хранимая процедура:
sp_help ИмяПредставления
Она отображает список полей представления с описанием их свойств.
Для отображения кода, с помощью которого создано представление, можно воспользоваться хранимой процедурой:
sp_helptext ИмяПредставления
Список объектов, от которых зависит представление, может быть получен выполнением хранимой процедуры:
sp_depends ИмяПредставления
Задание 2. Просмотрите информацию о созданном в предыдущем задании представлении InfoOrders, используя процедуры sp_help, sp_helptext, sp_depends.
Задание 3. Выведите статистику работы компании AdventureWorksDW, отражающую объемы продаж и количество заказов по месяцам.
Указание: Создайте сначала представление, содержащее номер заказа, год и месяц его размещения, итоговую стоимость каждого заказа. Затем на его основе создайте запрос.
Рекомендация: Чтобы получить год и месяц размещения заказа воспользуйтесь функцией преобразования: convert (char(7), FullDateAlternateKey,102) – которая преобразует дату к строке типа yyyy.mm.dd, а т.к. при преобразовании определена строка длиной 7 символов, результатом функции будет строка типа yyyy.mm, т.е. будут выведены только месяцы каждого года.
Задание 4. Определить количество размещенных заказов по временам года.
Указание: Создайте сначала представление, выводящее номера заказов по сезонам (временам года).
Рекомендация: Для выделения сезонов можно использовать следующую конструкцию (после SELECT как имя нового поля):
[Сезон] = Case
When month(FullDateAlternateKey) in (1, 2, 12) Then 'Зима'
When month(FullDateAlternateKey) in (3, 4, 5) Then 'Весна'
When month(FullDateAlternateKey) in (6, 7, 8) Then 'Лето'
When month(FullDateAlternateKey) in (9, 10, 11) Then 'Осень'
End
Сценарий, по которому разыгрался кризис, во многом будет зависеть от того, удастся ли бизнесу и потребителям победить этот кризис в голове. Всемирные бартерные сети
воскресенье, 12 апреля 2009 г.
6.1. Агрегатные функции
Аналитическая выборка данных из базы данных опирается на возможности Transact-SQL создавать запросы с агрегатными функциями:
Avg ([all | distinct] выражение) – среднее арифметическое всех значений.
Count ([all | distinct] выражение | *) – количество значений в списке, отличных от NULL. При использовании символа * подсчитывается количество значений, включая значения NULL или повторяющиеся значения.
Sum ([all | distinct] выражение) – сумма всех значений списка.
Max ([all | distinct] выражение) –максимальное значение.
Min ([all | distinct] выражение) – минимальное значение.
Ключевое слово all предписывает выполнять агрегирование всех записей в результирующем наборе данных, distinct – агрегирование только уникальных записей. По умолчанию используется all.
Например, вычисление средней цены продукции можно осуществить с помощью следующего запроса:
SELECT max(ListPrice) FROM DimProduct
При выполнении агрегатной функции осуществляется объединение значений отдельного поля таблицы или части записей, после чего выполняется указанное агрегирование.
Агрегатная функция возвращается одно единственное значение, поэтому использование других имен полей в списке выборки запрещено.
Задание 1
a) Определить количество сотрудников в БД AdventureWorksDW.
b) Подсчитать максимальную цену заказанной через Интернет продукции.
6.2. Подзапросы
Подзапрос – это оператор SELECT, включенный в другие запросы. Подзапросы применяются для разбивки сложного запроса на серию логических этапов. Их применение эффективно, если запрос использует записи, возвращенные другим запросом.
Существует два вида подзапросов: вложенные и связанные.
Вложенные подзапросы возвращают единственное значение либо список значений. Вложенный запрос выполняется один раз, а затем результирующее значение используется во внешнем запросе.
Например, чтобы определить имена заказчиков, разместивших заказы в первый учетный день, сначала можно воспользоваться следующим запросом:
SELECT CustomerKey
FROM FactInternetSales
WHERE OrderDateKey = (SELECT Min(TimeKey) FROM DimTime)
Выполнение этого запроса осуществляется в два этапа: на первом – осуществляется выполнение подзапроса, как самостоятельного запроса, который возвращает значение, используемое на втором этапе при выполнении внешнего запроса – когда получаем коды заказчиков. Полученные коды заказчиков могут быть использованы далее в подзапросе для получения имен заказчиков:
SELECT FirstName, LastName
FROM DimCustomer
WHERE CustomerKey IN
(SELECT CustomerKey
FROM FactInternetSales
WHERE OrderDateKey=(SELECT Min(TimeKey) FROM DimTime))
Такой запрос может быть оформлен и в виде соединения таблиц. Запросы с соединением таблиц обрабатываются значительно быстрее, поэтому если выполнение запроса не требуется выполнять в несколько этапов, использование подзапросов не обязательно.
Задание 2
a) Вывести список товаров, цена которых выше средней цены.
b) Решить задачу, описанную в примере (имена заказчиков, разместивших заказы в первый учетный день) с использованием соединения таблиц.
Связанные подзапросы используют данные внешнего запроса, причем связанный запрос выполняется один раз для каждой записи внешнего запроса.
Например, для определения списка заказчиков, заказавших в общей сложности более 20 единиц товара, необходимо:
SELECT FirstName, LastName
FROM DimCustomer C
WHERE 20<(SELECT sum(OrderQuantity) FROM FactInternetSales S WHERE C.CustomerKey=S.CustomerKey) В связанных подзапросах для краткости можно применять псевдонимы таблиц. В примере для таблицы DimCustomer определен псевдоним C, а для таблицы FactInternetSales – S. Однако, использование связанных подзапросов неэффективно, лучше преобразовывать их в соединения таблиц, что позволяет оптимизатору запросов выбирать наилучший способ обработки данных. Например, для товаров отобразить сведения о самом последнем их заказе: количество заказанного товара и номер заказа. SELECT EnglishProductName, OrderQuantity, SalesOrderNumber FROM DimProduct P INNER JOIN FactInternetSales S1 ON P.ProductKey=S1.ProductKey WHERE OrderDateKey = (SELECT max(OrderDateKey) FROM FactInternetSales S2 WHERE S2.ProductKey=P.ProductKey) 6.3. Группировка записей Для группировки записей по полям или выражениям применяется раздел GROUP BY оператора SELECT, что позволяет применять для каждой группы функции агрегирования. Синтаксис данной части следующий: [ GROUP BY ВыражениеГруппировки, […n] ] Например, чтобы определить количество товаров в каждом заказе, необходимо сгруппировать записи с одинаковым идентификационным номером заказа SalesOrderNumber и подсчитать количество товаров в каждой группе: SELECT SalesOrderNumber, count(ProductKey) AS Количество FROM FactInternetSales GROUP BY SalesOrderNumber При использовании GROUP BY для каждой определенной группы значений выводится только одна запись в итоговом наборе данных. При группировке записей допускается также использование раздела WHERE, в этом случае группируются записи, удовлетворяющие этому условию. Задание 3. Для каждого товара отобразить сведения о самом крупном (по стоимости) его заказе: наименование товара и стоимость заказа. Раздел WHERE позволяет определить, какие записи должны подвергнуться группировке, а раздел HAVING – какие группы должны быть выведены в итоговый набор данных. Ключевое слово HAVING можно использовать только в разделе GROUP BY. Так с помощью этого раздела можно в итоговый набор данных поместить только те заказы, сумма заказанных товаров в которых превышает 7 единиц: SELECT SalesOrderNumber, sum(OrderQuantity) as [Общая Сумма Товаров] FROM FactInternetSales GROUP BY SalesOrderNumber HAVING sum(OrderQuantity)>=7
Задание 4
a) Вывести список товаров, на которые поступило не менее 1000 заказов.
b) Получить список только тех товаров, чье суммарное заказанное количество превышает 900 штук.
Задание 5 (решения всех задач оформить в виде хранимых процедур)
a) Определить 10 наиболее популярных у заказчиков товаров.
b) Определить общее количество и суммарную стоимость всех заказов для каждого промоутера.
c) Выяснить, товары какого типа (субкатегории) пользуются у заказчиков наибольшим спросом.
http://62.105.135.100/images/default/constructor/logo_294290820.gif
Знакомства, общение, развлечения в Чехии – Seznamka.Ru
Avg ([all | distinct] выражение) – среднее арифметическое всех значений.
Count ([all | distinct] выражение | *) – количество значений в списке, отличных от NULL. При использовании символа * подсчитывается количество значений, включая значения NULL или повторяющиеся значения.
Sum ([all | distinct] выражение) – сумма всех значений списка.
Max ([all | distinct] выражение) –максимальное значение.
Min ([all | distinct] выражение) – минимальное значение.
Ключевое слово all предписывает выполнять агрегирование всех записей в результирующем наборе данных, distinct – агрегирование только уникальных записей. По умолчанию используется all.
Например, вычисление средней цены продукции можно осуществить с помощью следующего запроса:
SELECT max(ListPrice) FROM DimProduct
При выполнении агрегатной функции осуществляется объединение значений отдельного поля таблицы или части записей, после чего выполняется указанное агрегирование.
Агрегатная функция возвращается одно единственное значение, поэтому использование других имен полей в списке выборки запрещено.
Задание 1
a) Определить количество сотрудников в БД AdventureWorksDW.
b) Подсчитать максимальную цену заказанной через Интернет продукции.
6.2. Подзапросы
Подзапрос – это оператор SELECT, включенный в другие запросы. Подзапросы применяются для разбивки сложного запроса на серию логических этапов. Их применение эффективно, если запрос использует записи, возвращенные другим запросом.
Существует два вида подзапросов: вложенные и связанные.
Вложенные подзапросы возвращают единственное значение либо список значений. Вложенный запрос выполняется один раз, а затем результирующее значение используется во внешнем запросе.
Например, чтобы определить имена заказчиков, разместивших заказы в первый учетный день, сначала можно воспользоваться следующим запросом:
SELECT CustomerKey
FROM FactInternetSales
WHERE OrderDateKey = (SELECT Min(TimeKey) FROM DimTime)
Выполнение этого запроса осуществляется в два этапа: на первом – осуществляется выполнение подзапроса, как самостоятельного запроса, который возвращает значение, используемое на втором этапе при выполнении внешнего запроса – когда получаем коды заказчиков. Полученные коды заказчиков могут быть использованы далее в подзапросе для получения имен заказчиков:
SELECT FirstName, LastName
FROM DimCustomer
WHERE CustomerKey IN
(SELECT CustomerKey
FROM FactInternetSales
WHERE OrderDateKey=(SELECT Min(TimeKey) FROM DimTime))
Такой запрос может быть оформлен и в виде соединения таблиц. Запросы с соединением таблиц обрабатываются значительно быстрее, поэтому если выполнение запроса не требуется выполнять в несколько этапов, использование подзапросов не обязательно.
Задание 2
a) Вывести список товаров, цена которых выше средней цены.
b) Решить задачу, описанную в примере (имена заказчиков, разместивших заказы в первый учетный день) с использованием соединения таблиц.
Связанные подзапросы используют данные внешнего запроса, причем связанный запрос выполняется один раз для каждой записи внешнего запроса.
Например, для определения списка заказчиков, заказавших в общей сложности более 20 единиц товара, необходимо:
SELECT FirstName, LastName
FROM DimCustomer C
WHERE 20<(SELECT sum(OrderQuantity) FROM FactInternetSales S WHERE C.CustomerKey=S.CustomerKey) В связанных подзапросах для краткости можно применять псевдонимы таблиц. В примере для таблицы DimCustomer определен псевдоним C, а для таблицы FactInternetSales – S. Однако, использование связанных подзапросов неэффективно, лучше преобразовывать их в соединения таблиц, что позволяет оптимизатору запросов выбирать наилучший способ обработки данных. Например, для товаров отобразить сведения о самом последнем их заказе: количество заказанного товара и номер заказа. SELECT EnglishProductName, OrderQuantity, SalesOrderNumber FROM DimProduct P INNER JOIN FactInternetSales S1 ON P.ProductKey=S1.ProductKey WHERE OrderDateKey = (SELECT max(OrderDateKey) FROM FactInternetSales S2 WHERE S2.ProductKey=P.ProductKey) 6.3. Группировка записей Для группировки записей по полям или выражениям применяется раздел GROUP BY оператора SELECT, что позволяет применять для каждой группы функции агрегирования. Синтаксис данной части следующий: [ GROUP BY ВыражениеГруппировки, […n] ] Например, чтобы определить количество товаров в каждом заказе, необходимо сгруппировать записи с одинаковым идентификационным номером заказа SalesOrderNumber и подсчитать количество товаров в каждой группе: SELECT SalesOrderNumber, count(ProductKey) AS Количество FROM FactInternetSales GROUP BY SalesOrderNumber При использовании GROUP BY для каждой определенной группы значений выводится только одна запись в итоговом наборе данных. При группировке записей допускается также использование раздела WHERE, в этом случае группируются записи, удовлетворяющие этому условию. Задание 3. Для каждого товара отобразить сведения о самом крупном (по стоимости) его заказе: наименование товара и стоимость заказа. Раздел WHERE позволяет определить, какие записи должны подвергнуться группировке, а раздел HAVING – какие группы должны быть выведены в итоговый набор данных. Ключевое слово HAVING можно использовать только в разделе GROUP BY. Так с помощью этого раздела можно в итоговый набор данных поместить только те заказы, сумма заказанных товаров в которых превышает 7 единиц: SELECT SalesOrderNumber, sum(OrderQuantity) as [Общая Сумма Товаров] FROM FactInternetSales GROUP BY SalesOrderNumber HAVING sum(OrderQuantity)>=7
Задание 4
a) Вывести список товаров, на которые поступило не менее 1000 заказов.
b) Получить список только тех товаров, чье суммарное заказанное количество превышает 900 штук.
Задание 5 (решения всех задач оформить в виде хранимых процедур)
a) Определить 10 наиболее популярных у заказчиков товаров.
b) Определить общее количество и суммарную стоимость всех заказов для каждого промоутера.
c) Выяснить, товары какого типа (субкатегории) пользуются у заказчиков наибольшим спросом.
http://62.105.135.100/images/default/constructor/logo_294290820.gif
Знакомства, общение, развлечения в Чехии – Seznamka.Ru
Выборка данных из нескольких таблиц
Такая выборка данных предполагает соединение нескольких таблиц для получения единого набора результатов, включающих записи и поля каждой таблицы. Соединение позволяет собрать данные, разделенные в процессе нормализации.
Существует три вида соединений: внутреннее, внешнее и перекрестное. Для объединения трех и более таблиц можно применять последовательность соединений.
Для соединения таблиц необходимо раздел FROM дополнить ключевыми словами JOIN, которое определяет соединяемые таблицы и метод соединения, и ON, указывающее общие для таблиц поля.
5.1. Внутреннее соединение
При таком виде соединения сравниваются значения общих полей двух таблиц, возвращаются только записи, удовлетворяющие критерию связывания в обеих таблицах. Записи, для которых не имеется пары в связанной таблице, в результат не включаются.
Например, в таблице DimProduct нормализованной базы данных AdventureWorksDW храниться только идентификатор субкатегории товара. Чтобы получить список товаров с указанием их субкатегорий, необходимо соединить таблицы DimProduct и DimProductSubcategory:
SELECT DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName
FROM DimProduct INNER JOIN DimProductSubcategory
ON DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey
При таком соединении товары, для которых не указана их субкатегория, не включаются в набор результатов.
При соединение трех таблиц A, B и C конечный набор результатов получается посредством соединения третьей таблицы С с результатом соединения первых двух таблиц A и B:
FROM (A INNER JOIN B ON …) INNER JOIN C ON …
Задание 1. Вывести имена и фамилии заказчиков, сделавших заказы через Интернет, с указанием даты заказов. Причем вывести только тех, кто сделал заказ не раньше июня 2004 года. Если заказчик размещает несколько заказов в один день, то должна быть выведена только одна запись.
Подсказка: информация о заказах через Интернет – в таблице FactInternetSales, дата заказа содержится в таблице DimTime в поле FullDateAlternateKey.
5.2. Внешнее соединение
Такое соединение также возвращает объединенные записи, соответствующие условию соединения. Однако левые и правые соединения возвращают записи и не соответствующие указанному условию соединения. Такие соединения применяются для получения полного набора записей одной из таблиц.
При левом соединении (ключевое слово LEFT JOIN) в результат будут включены все записи левой таблицы (имя которой расположено слева от LEFT JOIN), независимо от того, есть для них соответствующая запись в правой таблице (имя таблицы расположено справа от LEFT JOIN) или нет.
Например, если в примере из предыдущего пункта вместо INNER JOIN поставить LEFT JOIN, то для товаров, у которых нет субкатегории, в поле EnglishProductSubcategoryName будет содержаться значение NULL.
При правом соединении (ключевое слово RIGHT JOIN) в результат включаются все записи правой таблицы, независимо от того, есть для них соответствующая строка в левой таблице.
Задание 2. Получите список товаров с указанием их субкатегорий (см. пример) с помощью внутреннего, левого и правого соединений. Сравните результаты.
5.3. Перекрестное соединение (произведение)
При таком соединении (ключевое слово CROSS JOIN) выводятся все комбинации записей таблиц, при этом не требуется указание совпадающих значений полей, поэтому условие ON опускается.
В нормализованных базах данных перекрестные соединения чаще всего используются для получения списка всех возможных комбинаций записей двух таблиц, т.е. число записей итогового набора равняется произведения числа записей первой таблицы на число записей второй.
Например, с помощью перекрестного соединения можно перечислить все возможные виды валюты для оплаты всех наименований товаров:
SELECT EnglishProductName, CurrencyName
FROM DimProduct CROSS JOIN DimCurrency
Задание 3. Перечислить все возможные виды валюты для оплаты всех наименований товаров (см. пример), причем не выводить дважды одинаковые записи и отсортировать по алфавиту наименований товаров. Проверьте, равно ли количество строк результата произведению количеств строк таблиц DimProduct и DimCurrency.
5.4. Объединение нескольких наборов результатов
Оператор UNION объединяет результаты двух и более операторов SELECT и применяется в случае, когда данные нельзя получить при помощи одного запроса.
Для получения единого итогового набора данных необходимо написать отдельные операторы SELECT и объединить их при помощи оператора UNION, при этом, в отличие от соединения, записи в итоговый набор добавляются друг за другом:
SELECT СписокПолейПервойТаблицы
FROM ИмяПервойТаблицы
UNION [ALL]
SELECT СписокПолейВторойТаблицы
FROM ИмяВторойТаблицы
[,…]
По умолчанию повторяющиеся записи удаляются, для получения всех записей необходимо указать ключевое слово ALL. Необходимо так же учитывать, что список полей, порядок и все их свойства должны быть одинаковы во всех используемых запросах (совместимость по типу).
Имена полей итогового набора берутся из первого запроса, поэтому создание псевдонимов полей выполняется в нем. Для получения отсортированного набора данных раздел ORDER BY указывается после последнего оператора SELECT.
Задание 4. Получить единый набор данных содержащий фамилии и имена заказчиков и сотрудников, а также их e-mail.
Задание 5. Получить список товаров с указанием номера заказа и наименования промоутера, заказанных в определенный день, например, 1 января 2004 года. Решение оформите в виде хранимой процедуры (дата – входной параметр).
Задание 6. Создайте запрос о заказе каждого товара с указанием его наименования, цены и количества с помощью внутреннего и внешних соединений. Сравните результаты и сделайте вывод о том, все ли товары были заказаны хотя бы один раз.
А отдых в Турции в этом году будет гораздо доступнее из-за снижения цен
Великолепный отдых в коттеджах Норвегии запомнится вам надолго!
Существует три вида соединений: внутреннее, внешнее и перекрестное. Для объединения трех и более таблиц можно применять последовательность соединений.
Для соединения таблиц необходимо раздел FROM дополнить ключевыми словами JOIN, которое определяет соединяемые таблицы и метод соединения, и ON, указывающее общие для таблиц поля.
5.1. Внутреннее соединение
При таком виде соединения сравниваются значения общих полей двух таблиц, возвращаются только записи, удовлетворяющие критерию связывания в обеих таблицах. Записи, для которых не имеется пары в связанной таблице, в результат не включаются.
Например, в таблице DimProduct нормализованной базы данных AdventureWorksDW храниться только идентификатор субкатегории товара. Чтобы получить список товаров с указанием их субкатегорий, необходимо соединить таблицы DimProduct и DimProductSubcategory:
SELECT DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName
FROM DimProduct INNER JOIN DimProductSubcategory
ON DimProduct.ProductSubcategoryKey=DimProductSubcategory.ProductSubcategoryKey
При таком соединении товары, для которых не указана их субкатегория, не включаются в набор результатов.
При соединение трех таблиц A, B и C конечный набор результатов получается посредством соединения третьей таблицы С с результатом соединения первых двух таблиц A и B:
FROM (A INNER JOIN B ON …) INNER JOIN C ON …
Задание 1. Вывести имена и фамилии заказчиков, сделавших заказы через Интернет, с указанием даты заказов. Причем вывести только тех, кто сделал заказ не раньше июня 2004 года. Если заказчик размещает несколько заказов в один день, то должна быть выведена только одна запись.
Подсказка: информация о заказах через Интернет – в таблице FactInternetSales, дата заказа содержится в таблице DimTime в поле FullDateAlternateKey.
5.2. Внешнее соединение
Такое соединение также возвращает объединенные записи, соответствующие условию соединения. Однако левые и правые соединения возвращают записи и не соответствующие указанному условию соединения. Такие соединения применяются для получения полного набора записей одной из таблиц.
При левом соединении (ключевое слово LEFT JOIN) в результат будут включены все записи левой таблицы (имя которой расположено слева от LEFT JOIN), независимо от того, есть для них соответствующая запись в правой таблице (имя таблицы расположено справа от LEFT JOIN) или нет.
Например, если в примере из предыдущего пункта вместо INNER JOIN поставить LEFT JOIN, то для товаров, у которых нет субкатегории, в поле EnglishProductSubcategoryName будет содержаться значение NULL.
При правом соединении (ключевое слово RIGHT JOIN) в результат включаются все записи правой таблицы, независимо от того, есть для них соответствующая строка в левой таблице.
Задание 2. Получите список товаров с указанием их субкатегорий (см. пример) с помощью внутреннего, левого и правого соединений. Сравните результаты.
5.3. Перекрестное соединение (произведение)
При таком соединении (ключевое слово CROSS JOIN) выводятся все комбинации записей таблиц, при этом не требуется указание совпадающих значений полей, поэтому условие ON опускается.
В нормализованных базах данных перекрестные соединения чаще всего используются для получения списка всех возможных комбинаций записей двух таблиц, т.е. число записей итогового набора равняется произведения числа записей первой таблицы на число записей второй.
Например, с помощью перекрестного соединения можно перечислить все возможные виды валюты для оплаты всех наименований товаров:
SELECT EnglishProductName, CurrencyName
FROM DimProduct CROSS JOIN DimCurrency
Задание 3. Перечислить все возможные виды валюты для оплаты всех наименований товаров (см. пример), причем не выводить дважды одинаковые записи и отсортировать по алфавиту наименований товаров. Проверьте, равно ли количество строк результата произведению количеств строк таблиц DimProduct и DimCurrency.
5.4. Объединение нескольких наборов результатов
Оператор UNION объединяет результаты двух и более операторов SELECT и применяется в случае, когда данные нельзя получить при помощи одного запроса.
Для получения единого итогового набора данных необходимо написать отдельные операторы SELECT и объединить их при помощи оператора UNION, при этом, в отличие от соединения, записи в итоговый набор добавляются друг за другом:
SELECT СписокПолейПервойТаблицы
FROM ИмяПервойТаблицы
UNION [ALL]
SELECT СписокПолейВторойТаблицы
FROM ИмяВторойТаблицы
[,…]
По умолчанию повторяющиеся записи удаляются, для получения всех записей необходимо указать ключевое слово ALL. Необходимо так же учитывать, что список полей, порядок и все их свойства должны быть одинаковы во всех используемых запросах (совместимость по типу).
Имена полей итогового набора берутся из первого запроса, поэтому создание псевдонимов полей выполняется в нем. Для получения отсортированного набора данных раздел ORDER BY указывается после последнего оператора SELECT.
Задание 4. Получить единый набор данных содержащий фамилии и имена заказчиков и сотрудников, а также их e-mail.
Задание 5. Получить список товаров с указанием номера заказа и наименования промоутера, заказанных в определенный день, например, 1 января 2004 года. Решение оформите в виде хранимой процедуры (дата – входной параметр).
Задание 6. Создайте запрос о заказе каждого товара с указанием его наименования, цены и количества с помощью внутреннего и внешних соединений. Сравните результаты и сделайте вывод о том, все ли товары были заказаны хотя бы один раз.
А отдых в Турции в этом году будет гораздо доступнее из-за снижения цен
Великолепный отдых в коттеджах Норвегии запомнится вам надолго!
Подписаться на:
Сообщения (Atom)