Итоговые функции: STDEV, STDEVP

Тема: SQL- продолжение (Построение запросов)

Операция JOIN

Во многом широкие возможности SQL базируются на его способности объединять информацию из нескольких таблиц или запросов и представлять результат в виде одного логического набора записей. В большинстве случаев Microsoft Access позволяет обновлять набор записей запроса на объединение, как если бы это была отдельная базовая таблица (при условии, если вы включили в инструкцию ключевое слово DISTINCTROW).

Для задания типа объединения таблиц в логический набор записей, из которого будет выбираться необходимая нам информация, используйте операцию JOIN в предложении FROM. Вы можете попросить Access включить в логический набор записей только соответствующие строки обеих таблиц (эта операция называется INNER JOIN — внутреннее объединение) или включить все строки одной из двух заданных таблиц даже в том случае, когда соответствующая ей строка не найдена во второй таблице (эта операция называется OUTER JOIN — внешнее объединение). Можно создать вложенные объединения, объединив третью таблицу с результатом объединения двух других таблиц.

Синтаксис:

 

({ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [ [AS] псевдоним ] |

< таблица-объединения >

{ INNER | LEFT | RIGHT } JOIN

{ имя-таблицы [ [AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] |

< таблица-объединениям >}

ON < условие-объединения >)

где <таблица-объединения> есть результат другой операции объединения, а <условие-о6ъединешя> — это условие отбора, образованное из предикатов сравнений, которые сравнивают поля первой таблицы с полями второй.

При желании можно употреблять в качестве имен таблиц или запросов альтернативные имена. Такое альтернативное имя можно использовать как псевдоним вместо полного имени таблицы при указании имен столбцов в списке выбора, предложении WHERE или подчиненных предложениях..

Используйте операцию INNER JOIN, если нужно включить все строки из обеих таблиц, удовлетворяющие условию объединения. Используйте LEFT JOIN, если нужно получить все строки из первой таблицы, объединенные с теми строками из второй таблицы, для которых выполняется условие объединения. Если во второй таблице нет строк, для которых условие объединения выполняется, Access в качестве значений столбцов второй таблицы возвращает значения Null. Аналогично, операция RIGHT JOIN возвращает все строки из второй таблицы, объединенные с теми строками из первой таблицы, для которых выполняется условие объединения.

Если в условии объединения вы используете только предикаты сравнения на I равенство, то результат называется объединением по равенству. В бланке QBE Access может представить объединение по равенству, но не может представить объединение по условию «неравенства». Если вам нужно определить объедине­ние таблиц по условию неравенства (<,>, < >, <= или >=), то запрос следует определять в режиме SQL. Если по условию равенства вы выполняется объеди­нение таблицы с самой собой, то результат называется самообъединением.

 

Примеры:

Чтобы выбрать информацию о компонентах и поставщиках, которые поставляют каждый из компонентов, и отсортировать ее по ComponentlD, выполните следующий запрос:

 

SELECT DISTINCTROW Component. *, Suppllier.SuppllierName,

Suppllier. SuppllierAddressl,

Suppllier.Su_PpllierAddress2,

Suppllier. SupplUerCity,

Suppllier.SuppllierState,

Suppllier.SuppllierPostal,

Suppllier. SuppllierPhone,

Suppllier.SuppllierFax

FROM Suppllier

INNER JOIN Component

ON Suppllier.SuppllierlD = Component.SuppllierlD

ORDER BY Component.ComponentlD;

 

Чтобы определить, какие поставщики не поставляют компонент с кодом 25, введите следующую инструкцию:

 

SELECT DISTINCTBOW Component. ComponentlD,

Suppllier.SuppllierID,
Suppllier. SuppllierName,

Suppllier.SuppllierCity,

Suppllier.SuppllierState

FROM Suppllier

INNER JOIN Component

ON Suppllier.SuppllierlD = Component.SuppllierlD

WHERE Component.ComponentID <=> 25;

 

Чтобы определить, какие из клиентов не имеют в данное время заказов, введнте следующую инструкцию:

 

SELECT DISTINCTROW Customer.CompanyName, Customer.City,

Customer. State

FROM Customer

LEFT JOIN [Order]

ON Customer.CustomerlD = Order.CustomerlD

WHERE (((Order.CustomerlD) Is Null));

 

Чтобы получить список изделий и количество изделий, заказанных на настоящий момент, отсортированный по типу изделий, введите следующую инструкцию:

 

SELECT DISTINCTROW Type.TypeDescription,

Catalog.Description, OrderItern.OrderID,

Orderltem.Quantity

FROM Type

INNER JOIN (Catalog

INNER JOIN OrderItem

ON Catalog.CatalogItemID = Orderltem.CatalogItemID)

ON Type.IteraTypeCode = Catalog.ItemTypeCode

ORDER BY Type.TypeDescription;

 

Предложение ORDER BY

Задает последовательность строк, возвращаемых инструкцией SELECT или INSERT.

Синтаксис:

ORDER BY { имя-столбца [ASC | DESC] },...

 

В предложении ORDER BY можно указать несколько столбцов. Список, сортируется сначала по значениям столбца, имя которого указано первым. Если существуют строки, в которых значения этого столбца равны, то они упорядо­чиваются по значениям столбца, имя которого является следующим в списке предложения ORDER BY. Для каждого столбца вы можете задать порядок сортировки: по возрастанию (ASC) или по убыванию (DESC). Если вы не указали порядка сортировки, то по умолчанию принимается значение ASC. Использование предложения ORDER BY в инструкции SELECT — это единст­венное средство для задания последовательности, в которой располагаются возвращаемые записи.

 

Примеры:

Чтобы выбрать клиентов, которые сделали первый заказ не позже 1988 года, и возвратить их список, упорядоченный по возрастанию почтовых индексов, выполните запрос:

 

SELECT DISTINCTROM Customer.CompanyName, Customer.City,

PostalCode

FROM Customer

WHERE (((1988) >=

(SELECT MIN [Year (OrderDate))

FROM [Order]

WHERE

Order.CustomerID = Customer.CustomerID)))

ORDER BY Customer.PostalCode;

 

Чтобы найти всех поставщиков и всех клиентов из штата Вашингтон (Washin­gton) и возвратить их список, упорядоченный по убыванию почтовых индексов, выполните запрос:

 

SELECT DISTINCTROW Customer.CompanyName, Customer.City,

PostalCode

FROM Customer

WHERE Customer.State = "WA"

UNION

SELECT DISTINCTROW Supplier. SupplierName,

Supplier. SupplierCity

Supplier.SupplierPostal

FROM Supplier

WHERE Supplier.SupplierState = "WA"
ORDER BY PostalCode;

 

Описание PARAMETERS

 

Располагается перед инструкцией SQL и предназначено для определения типов данных используемых вами в запросе параметров. Вы можете использовать параметры, чтобы предоставить пользователю возможность во время выполне­ния запроса ввести значения, входящие в условия отбора, или чтобы использо­вать в условии отбора значения, которые содержатся в элементах управления открытой формы.

Синтаксис:

 

PARAMETERS {[ имя-параметра ] тип-данных),...;

Если при выполнении запроса пользователь должен ввести значения, то каждое имя параметра должно описывать значение, которое требуется ввести. Напри­мер, [Распечатать фактуры для заказов на дату:] является более содержатель­ным, чем [Введите дату:]. Если вы хотите сослаться на элемент управления открытой формы, используйте следующий формат:

 

[Forms]! [Myform]![Mycontrol]

 

Чтобы сделать ссылку на элемент управления в подчиненной форме, используйте формат:

[Forms]![MyForm]![Mysubformcontrol].[Form]![CantrolOnSubform]

Допустимые типы данных параметров перечислены ниже:

 

Тип данных SQL Bit Binary Byte Currency DateTime FLOAT LEEEDouble IEEESingle INT[EGER] Long LongBinary LongText REAL Short SMALLINT Text Value VARCHAR

Эквивалентный тип данных Access Да/Нет Двоичный (Binary) Байт Денежный Дата/Время С плавающей точкой (8 байт)_ С плавающей точкой (8 байт) С плавающей точкой (4 байта) Длинное целое Длинное целое Объект OLE Мемо С плавающей точкой (4 байта) Целое Целое Текстовый Значение (Value) Мемо

 

Пример:

 

Чтобы создать запрос с параметрами для подсчета объема продаж и суммарной стоимости всех проданных изделий за данный месяц, введите следующую инструкцию:

 

PARAMETERS [Год, в котором подсчйтывнются итоги":] Short'

[Месяц, в котором подсчитываются итоги:] Short;
SELECT DISTINCTROW Orderltera.CataloglteralD,

SUMfOrderltem.Quantity) AS Quantity,

SUMfOrderltem. [Quantity] *

[QuotedPrice]) AS TotalSales,

SUM (OrderItem.[Quantity] * [OurCost]) AS TotalCost

FROM [Order]

INNER JOIN (Catalog

INNER JOIN Orderltem

ON Catalog.CatalogltemID = Orderltem.CatalogltemID)
ON Order.DrderlD = Orderltem.OrderlD

WHERE (((Year ([OrderDate]}) = [Год, в котором подсчитываются итоги: ])

AND ((Month ([OrderDate])) = [Месяц, в котором подсчитываются итоги:]))

GROUP BY Orderltem.CatalogltemID;

 

Предикат: BETWEEN

Сравнивает значение с заданным диапазоном значений.

 

Синтаксис:

выражение [NOT] BETWEEN выражение AND выражение

Типы данных всех выражений должны быть совместимы. Сравнение буквенно-цифровых литералов (строк) в Microsoft Access производится без учета регистра.

 

Пусть а, Ь и с - выражения. Тогда a BETWEEN Ь AND с эквивалентно следующему выражению:

a >= Ь AND a <= с

a NOT BETWEEN Ь AND с эквивалентно:

а < Ь OR a > c

Результат считается неопределенным, если одно из выражений равно NULL.

 

Пример:

 

Чтобы определить, что среднее значение произведения Quantity (Количество) на QuotedPrice (Договорная цена) не меньше 500 долларов и не больше 10 000 долларов, введите следующее выражение:

 

AVG(Quantity * QuotedPrice) BETWEEN 500 AND 10000

 

Предикат: Сравнение

Сравнивает значения двух выражений или значение некоторого выражения и значение, являющееся результатом подчиненного запроса.

Синтаксис:

 

выражение {= | <> | > | < | >= | <= }

{выражение | подчинениый-запрос}.

Сравнение строк в Microsoft Access производится без учета регистра. Тип данных первого выражения должен быть совместим с типом данных второго выражения или со значением, возвращаемым при выполнении подчиненного запроса. Если подчиненный запрос не возвращает ни одной записи или возвращает более одной записи, регистрируется ошибка, если только список выбора в подчиненном запросе не COUNTC*). В последнем случае несколько возвращаемых записей порождают одно значение. Если результат оценки первого или второго выраже­ния или подчиненного запроса есть Null, результат сравнения неопределен.

 

Примеры:

Чтобы определить, что заказ сделан в 1992 году, введите:

Year(OrderDate) = 1992

Чтобы определить, что OrderlD (код заказа) не равен 50, введите:

OrderlD <> 50

Чтобы определить, что AmountOwed (задолженность) больше, чем 0, введите:

AmountOwed > 0

Чтобы определить, что заказ сделан в первой половине года, введите:

Month(OrderDate) < 7

 

Чтобы определить, что максимальная итоговая стоимость заказа в некоторой группе меньше средней итоговой стоимости заказов, вычисленной по всей таблице Order (Заказ), введите:

 

MAX (SubTotalCost - (SubTotalCost * Discount)) <
(SELECT AVG (SubTotalCost - (SubTotalCost * Discount))

FROM [Order])

 

Предикат: EXISTS

 

Проверяет существование по крайней мере одной строки, которая удовлетворяет условиям отбора в подчиненном запросе.

Синтаксис:

EXISTS {подчиненный-запрос)

Результат всегда определен. Если подчиненный запрос возвращает, по крайней мере, одну строку, результат имеет значение Истина, в противном случае результат равен значению Ложь. Подчиненный запрос может не возвращать никаких значений для этого предиката, поэтому вы можете указывать любой список выбора (включая *).

 

Пример:

Чтобы найти всех поставщиков, которые поставляют, по крайней мере, один вид компонента, введите:

 

SELECT DISTINCTROW Supplier.SupplierName

FROM Supplier

WHERE EXISTS ^:

(SELECT *

FROM Component

WHERE Component.SupplierlD = Supplier.SupplierlD);

 

В приведенном примере внутренний подчи­ненный запрос содержит внешнюю ссылку на таблицу Supplier 1 (Поставщик) в инструкции SELECT, ссылаясь на столбец во внеш­ней таблице (Supplier.SupplierlD в предложении WHERE подчинен­ного запроса). Это приводит к тому, что в инструкции SELECT подчиненный запрос должен выполняться для каждой строки, что может быть не самым эффективным способом достижения ре­зультата.

Предикат: IN

Определяет, равно ли некоторое значение одному из значений, указанных в списке значений или возвращаемых подчиненным запросом.

 

Синтаксис:

выражение [NOT] IN {(подчиненный запрос) | ({литерал},…) | выражение}

Сравнение строк в Microsoft Access производится без учета регистра. Типы данных всех выражений, литералов и столбца, возвращаемого при выполнении подчиненного запроса, должны быть совместимыми. Если результат выражения есть Null или любое из значений, возвращаемых подчиненным запросом, равно Null, то результат сравнения не определен. В терминах других предикатов выражение IN выражени е эквивалентно:

 

выражение = выражение

выражение IN (подчиненный запрос) эквивалентно:

выражение = ANY {подчиненный запрос)

выражение IN (a, Ь, с,...), где a, Ь и с - литералы, эквивалентно:

(выражение = a) OR (выражение =- b) OR (выражение = c)…

выражение NOT IN... эквивалентно следующему:

NOT (выражение IN...)

 

Примеры:

Чтобы определить, что клиент проживает в одном из штатов, расположенных на Западном побережье США, введите следующую строку:

 

State IN ("СА", "OR", "WA")

 

Чтобы определить, что CustomerID (Код клиента) совпадает с одним из значений SupplierID (Код поставщика) для штата Вашингтон, введите следующее:

 

CustomerID IN

(SELECT SupplierID

FROM Supplier
WHERE SupplierState = "WA")

 

Предикат: LIKE

 

Отыскивает строки, сравнивая их с образцом.

 

Синтаксис:

 

имя-столбца [NОТ] LIKE образец

 

Сравнение строк в Microsoft Access производится без учета регистра. Если значение столбца равно Null, то результат сравнения не определен. Сравнение двух пустых строк или пустой строки с символом ' возвращает значение Истина.

Для задания позиций, содержащих одиночный символ или произвольное число символов (включая пулевое число символов), можно использовать несколько специальных символов шаблона, в соответствии со следующей таблицей:

 

Символ шаблона	Назначение
?	Любой одиночный символ
*	Произвольное (включая нулевое) число символов (используется для указания на подстроку символов, для которой в образце нет соответствующих символов)
#	Любая одиночная цифра

 

Вы можете также указать, что некоторая определенная позиция в текстовом или Мемо-поле может содержать только символы из заданного вами списка, заклю­ченного в квадратные скобки. Можно задать диапазон символов, если ввести символ начала диапазона, затем дефис, а потом — конечный символ диапазона, например [A-Z] либо [3-7]. Если вы хотите проверить, что в некоторой позиции стоит символ, не входящий в заданный вами список, начните список с воскли­цательного знака (1). Если вам необходимо проверить наличие в данной позиции символа *,?, # или [, то вам придется заключить такой символ в квадратные скобки.

 

Примеры:

Чтобы определить, что имя клиента содержит по крайней мере 4 буквы и начинается со Smi, введите следующее выражение:

CustomerName LIKE "Smi?*"

Чтобы узнать, что почтовый индекс является допустимым канадским почтовым индексом, введите:

PostalCode LIKE "[A-Z] # [A-Z] #[A-Z]#"

 

Предикат: NULL

 

Определяет, что значение выражения равно Null. Этот предикат всегда имеет значение Истина или Ложь и не может порождать неопределенное значение.

Синтаксис:

выражение IS [NOT] NULL

 

Примеры:

Для выяснения того, что столбец PhoneNumber никогда не заполнялся, введите следующее:

 

PhoneNumber IS NULL

 

Предикаты Some, Any, All

 

Сравнивают значение выражения с некоторым значением или всеми значениями столбца, возвращаемого подчиненным запросом.

 

Синтаксис:

выражение { = | <> | > | < | >= | <= }

[SOME | ANY | ALL] (подчинениый-запрос)

Пояснения:

Сравнение строк в Microsoft Access производится без учета регистра. Тип данных выражения должен быть совместимым с типом данных столбца, возвращаемого подчиненным запросом.

Когда используется ALL, предикат принимает значение Истина, когда все результаты сравнения со значениями, возвращаемыми при выполнении подчи­ненного запроса, истинны. Если выражение или некоторое значение из возвра­щаемых подчиненным запросом равно Null, результат не определен. Если используются SOME (или ANY, которое является синонимом SOME), то предикат принимает значение Истина, когда результат сравнения хотя бы с одним из возвращаемых подчиненным запросов значений истинен. Если выра­жение имеет значение Null, результат не определен. Если в результате выпол­нения подчиненного запроса не возвращается никаких значений, то предикат принимает значение Ложь.

Примеры:

 

Чтобы определить компоненты, стоимость которых больше, чем стоимость любого из компонентов с кодом 008, введите следующее:

 

SELECT DISTINCTROW Component.Description,

Component.OurCost

FROM Component

WHERE (Component.OurCost) > ALL

(SELECT OurCost

FROM Component
WHERE ItemTypeCode = "008");

 

Чтобы найти компоненты, у которых стоимость выше, чем стоимость некоторого из компонентов с кодом 008, введите следующие:

 

SELECT DISTINCTROW Component.Description,

Component.OurCost

FROM Component

WHERE (Component.OurCost) > SOME

(SELECT OurCost

FROM Component

WHERE ItemTypeCode = "008");

Условие отбора

Определяет простой или составной предикат, который может принимать значе­ния Истина, Ложь или быть неопределенным для данной строки или группы строк. Используйте условие отбора в предложении WHERE или в инструкции SELECT, в подчиненном запросе, инструкциях DELETE или UPDATE. Условие отбора может быть также задано внутри предложения HAVING в инструкции SELECT. Условие отбора определяет те строки, которые должны быть отобраны в результирующую логическую таблицу (набор записей) или те строки, которые подлежат обновлению при выполнении операции изменения. Если условие отбора для строки имеет значение Истина, то такая строка включается в результирующий набор.

 

Синтаксис:

[NOT] { предикат | (условие-отбора) }

[{AND | OR | XOR | EQV | IMP)

[NOT] { предикат | { условие-отбора)}]...

 

Пояснения:

В условии отбора Microsoft Access может применять подчиненный запрос к каждой строке таблицы, которая является результатом выполнения предыдущих предложений. Затем Access оценивает результат подчиненного запроса по отношению к каждой строке-кандидату.

Если вы включите предикат сравнения, используя синтаксис выражение опера­тор-сравнения подчипелный-замрос, то в том случае, когда подчиненный запрос не возвращает ни одной строки, возникает ошибка.

Порядок применения логических операторов следующий: NOT, AND, OR, XOR (исключающее OR), EQV (эквивалентность) и IMP (импликация). Для изменения последовательности вычисления логического выражения можно включить дополнительные круглые скобки.

Вы можете использовать логические операторы AND и OR непосредственно в бланке QBE. Если вы хотите использовать ХОН, EQV или IMP, вам следует записать выражение в строке Поле, убрать флажок Вывод на экран, а к строке Условие отбора ввести <>False.

При использовании логического оператора NOT действуют следующие соглаше­ния: NOT (Истина) есть Ложь, NOT (Ложь) есть Истина, a NOT (неопределенное-яначение) есть «неопределенное-значенне». Результат считается неопределенным вовсех случаях, когда предикат ссылается на значение Null. Если условие отбора для строки имеет значение. Ложь или является неопреде­ленным, тег такая строка не включается в результирующий набор.

 

Примеры:

Чтобы найти все изделия, стоимость которых больше 100 долларов и при этом для их комплектации необходимо два дня или их количество и наличии меньше 5 (но последние два условия не выполняются одновременно), введите следую­щую инструкцию:

Таблица истинности для логических операций SQL

 

SELECT DISTINCTROW Catalog. CataloglteralD,
Catalog.Description, Catalog.OurCost,

Catalog.DaysToBuild, Catalog.NumberlnStock

FROM Catalog
WHERE (Catalog.OurCost) > 100

AND ([Catalog.DaysToBuild) = 2

XOR (Catalog.NumberlnStock) < 5);

Инструкция SELECT

 

Выполняет реляционные операции выбора и объединения для создания логичес­кой таблицы (набора записей) на базе других таблиц или запросов. Элементы списка выбора задают вычисляемые выражения или столбцы, выбираемые из исходных таблиц, которые включаются в логическую таблицу. Таблицы, кото­рые должны быть объединены, указываются в предложении FROM, а строки, которые необходимо выбрать, — в предложении WHERE. Используйте GROUP BY для определения, как должны быть сформированы группы для итогового запроса, а предложение HAVING — для отбора тех групп, которые должны быть включены в результат запроса.

Синтаксис:

SELECT [All | DISTINCT | DISTINCTROW | TOP число

[PERCENT]] список-выбора

FROM [{ имя-таблицы [[AS] псевдоним] |
имя-запроса-выборки [[AS] псевдоним]} |
<таблица-объединения>},...
[WHERE условие-отбора]

[GROUP BY имя -столбца,...]

[HAVING условие-отбора]

[UNION [ALL] инструкция-выбора]

[ORDER BY { имя-столбца [ASC | DESC]},... |

IN <”имя-базы-данных-источнмка"> <[строка-подключения- источника-данных]>

[WITH OWNERACCESS OPTION];

 

где список-выбора есть

{* | {выражение [AS имя-столбца-выходной-таблицы] | имя-таблицы. * |

имя-запроса. * | псевдоним. *},...}

 

и где таблица-объединения есть

 

({ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ]} |

< таблица-объединения> }

{INNER | LEFT | RIGHT | JOIN

{ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] |
<та блица–объединения>}

ON < условие-обеединения>)

Пояснения:

Для каждой таблицы и каждого запроса вы можете при желании определить альтернативное имя. Это имя вы можете использовать как псевдоним вместо полного имени таблицы при задании имен столбцов в списке выбора, в предло­жении WHERE или в подчиненных предложениях. Если вы производите объединение таблицы или запроса с самим собой, то вам придется употреблять альтернативное имя, чтобы указать, на какую копию таблицы или запроса вы ссылаетесь при задании списка выбора, условий объединения или условий отбора. Если имя таблицы или имя запроса совпадает с зарезервированным словом языка SQL (например, «Order»), вы должны заключать такое имя в квадратные скобки.

Когда вы перечисляете несколько таблиц или запросов и не указываете условия объединения, в качестве исходной таблицы используется Декартово произведе­ние всех таблиц. Например, FROM TableA, ТаblеВ заставляет Access считать областью поиска все строки из таблицы TableA, присоединенные к каждой из строк таблицы ТаblеВ. В этом случае число логических записей, обрабатывае­мых Access, может быть равно числу строк в таблице ТаblеА, умноженному на число строк в таблице ТаblеВ. В качестве результата Access возвратит те записи, в которых условия отбора, определенные в предложениях WHERE и HAVING, примут значение Истина.

DISTINCT требует, чтобы Access возвратил только строки, отличающиеся ото всех остальных. В запросе, который использует предикат ALL или DISTINCT, вы не сможете изменить содержимое каких-либо столбцов.

DIST1NCTROW (по умолчанию) требует, чтобы Access возвратил только такие строки, в которых конкатенация первичных ключей из всех таблиц, участвую­щих в формировании выходных столбцов, является уникальной. В зависимости оттого, какие именно столбцы вы выберете, в результирующем наборе вы иногда увидите повторяющиеся строки, однако каждая строка в выходной таблице извлечена из уникальной (DISTINCT) комбинации строк (ROWS) базовых таблиц. Если вы хотите обновлять значения полей набора записей запроса на объединение, то нужно использовать предикат DISTINCTROW. Если вы хотите, чтобы результирующий набор содержал только первые п или первые п процентов (PERCENT) записей, используйте предикат ТОР п или TOP n PERCENT. Параметр п должен быть целым числом я должен быть не больше 100, если вы используете ключевое слово PERCENT. Заметьте, что если вы не включите в запрос предложение ORDER BY, то порядок расположения возвращаемых строк неопределен.

Если вы употребляете предложение GROUP BY, то список выбора должен содержать одну или несколько итоговых функций (AVG, COUNT, MAX, MIN, STDEV, STDEVP, SUM, VAR или VARP) либо одно или несколько имей столбцов, указанных в предложении GROUP BY. В предложении GROUP BY можно использовать любой столбец из любой таблицы, указанной в предложе­нии FROM, даже если этот столбец не указан и списке выбора. Если в предложении GROUP BY вы хотите сослаться на вычисляемое выражение, необходимо присвоить этому выражению в списке выбора имя и затем ссылаться на него в предложении GROUP BY. Если перед предложением GROUP BY расположено предложение WHERE, Access формирует группы из строк, ото­бранных после применения предложения WHERE.

Если вы используете предложение HAVING и при этом не включили в запрос предложение GROUP BY, то список выбора должен формироваться с исполь­зованием итоговых функций (AVG, COUNT, MAX, MIN, STDEV, STDEVP, SUM, VAR или VARP). Если вы поставите предложение GROUP BY перед предложением HAVING, условие отбора предложения HAVING будет приме­няться к каждой из групп, образованных на основе совпадения значений заданных столбцов. Если вы не включите в запрос предложение GROUP BY, то условие отбора предложения HAVING будет применяться ко всей логической таблице, определенной инструкцией SELECT.

В предложении ORDER BY можно указать один или несколько столбцов, по значениям которых будут сортироваться возвращаемые записи. Список сортируется сначала по значению столбца, имя которого указано первым. Если существуют строки, в которых значения этого столбца равны, то они упорядо­чиваются по значениям столбца, имя которого и списке предложения ORDER BY является следующим. Для каждого столбца вы можете задать порядок сортировки: по возрастанию (ASC) или по убыванию (DESC). Если вы не указали способ сортировки, то по умолчанию используется сортировка по возрастанию. Использование предложения ORDER BY в инструкции SE­LECT — это единственное средство для задания последовательности, в которой располагаются возвращаемые записи.;

Обычно пользователь, выполняющий запрос, должен иметь право на доступ не только к этому запросу, но и соответствующие права доступа к таблицам, которые используются в запросе (эти права связаны с чтением данных для отбора строк, изменением, вставкой или удалением данных при выполнении запроса). Если с приложением работают несколько пользователей, то для защиты данных в таблицах вы можете сделать так, чтобы никто из пользователей не имел прямого доступа к таблицам, но при этом все пользователи могли бы выполнять созданные вами запросы. Если вы являетесь владельцем и таблиц и запросов, то вы можете запретить доступ к таблицам, но при этом разрешить выполнять запросы. Для обеспечения гарантии правильного выполнения запроса вы должны включить в него предложение WITH OWNERACCESS OPTION, которое наделяет пользователя при доступе к данным при выполнении конкрет­ного запроса такими же правами, что и владельца соответствующей таблицы.

Чтобы выбрать информацию о клиентах и их покупках на сумму более 100 долларов, введите следующую инструкцию:

 

SELECT Cust.CustomerlD, Cust.CompanyName, Ord.OrderDate,

Cat.CatalogltemID, Cat.Description,

Order Item. Quantity, Orderltem.QuotedPrice

FROM [Catalog AS Cat

INNER JOIN ((Customer AS Cust

INNER JOIN [Order] AS Ord

ON Cust.CustoraerlD = Ord.CustomerlD)

INNER JOIN OrderItem

ON Ord.OrderlD = Orderltem.OrderlD)

ON Cat.CatalogltemID = Orderltera.CatalogltemID)

WHERE (Orderltem.Quantity * Orderltem. Price) > 100;

 

Чтобы среди клиентов, имеющих одинаковый почтовый индекс, найти макси­мальный заказ, введите следующий запрос:

 

SELECT DISTIHCTROW Customer.PostalCode,

Max (Otder.SubTotalCost) AS MaxOfSubTotalCost

FROM Customer

INNER JOIN [Order]

ON Customer.CustomerlD = Order.CustomerlD

GROUP BY Customer.PostalCode;

 

Для определения средней и максимальной цен на изделия для каждого типа продукции введите следующее:

 

SELECT DISTINCTROW Туре. TypeDescription,

AVG (Catalog. Price) AS AvgOf Price,

MAX (Catalog.Price) AS MaxOfPrice

FROM Type

INNER JOIN Catalog

ON Type.ItemTypeCode = Catalog.ItemTypeCode

GROUP BY Type.TypeDescription;

 

Чтобы определить наибольшую стоимость заказа по каждой группе клиентов (сформированных по штатам) и выделить такие ситуации, когда наибольшая стоимость заказа в группе оказывается меньше, чем средняя стоимость заказов по всем клиентам, введите следующее:

 

SELECT DISTINCTROW Customer.State,

Max (Order.SubTotalCost) AS MaxOfSubTotalCost

FROM Customer

INNER JOIN [Order]

ON Customer.CustoraerlD = Order.CustomerlD

GROUP BY Customer.State

HAVING (((Max (Order.SubTotalCost)) <

(SELECT AVG (SubTotalCost)

FROM [Order])));

 

Для нахождения средней и максимальной стоимости заказа для клиентов из штата Вашингтон за каждый месяц, в котором максимальная стоимость заказа была меньше 4000 долларов, введите следующую инструкцию:

 

SELECT DISTINCTROW Month ([OrderDate]) AS Month,

AVG (Order.SubTatalCost) AS AvgOfSubTotalCost,

MAX (Order.SubTotalCost) AS MaxOfSubTotalCost

FROM Customer

INNER JOIN [Order]

ON Customer.CustomerlD = Order.CustoraerlD

WHERE (((Customer.State) = "WA"))

GROUP BY Month ([OrderDate])

HAVING (((Max (Orider. SubTotalCost)) < 4000));

 

Для определения числа различающихся цен на изделия, имеющиеся на складе, вам потребуется выполнить два запроса. Запрос DistinctPrice (Различающиеся цены) выглядит следующим образом:

 

SELECT DISTINCT Price

FROM Catalog

WHERE NumberlnStock > 0;

 

Запрос на вычисление количества различающихся цен CountDjstinctPrice (Ко­личество различающихся цен) имеет вид:

 

SELECT COUNT (*)

FROM DistinctPrice;

 

Чтобы выбрать информацию о компонентах и поставщиках, выпускающих каждый из этих компонентов, отсортировав данные по значению столбца ComponentlD, введите следующее:

 

SELECT DISTINCTROW Component.*, Supplier.SupplierName,

Supplier.SupplierAddressl,

Supplier.SuppLierAddress2,

Supplier.SupplierCity,

Supplier.SupplierState,

Supplier.Supplier Postal,

Supplier.Supplier Phone,

Supplier.SupplierFax

FROM Supplier

INNER JOIN Component

ON Supplier.SupplierlD = Component.SuppliefID

ORDER BY Component.ComponentID;

 

Чтобы определить, какие поставщики не вьшускают компонент с номером 25, выполните следующий запрос:

 

SELECT DISTINCTROW Component.ComponentID,

Supplier.SupplierlD, Supplier.SupplierName,

Supplier.SupplierCity, Supplier.SupplierState

FROM Supplier

INNER JOIN Component

ON Supplier.SupplierlD <> Component.SupplierlD

WHERE Component.ComponentID = 25;

 

Чтобы определить, кто из клиентов не имеет в настоящее время заказов, введите следующую инструкцию:

 

SELECT DISTINCTROW Customer.CompanyName, Customer.City,

Customer.State

FROM Customer

LEFT JOIN [Order]

ON Customer.CustomerlD = Order.CustomerlD

WHERE (((Order.CustomerlD) IS NULL));

 

Чтобы увидеть список изделий и их количество по текущим заказам,'отсорти­ровав информацию по типам изделий, введите следующее:

 

SELECT DISTINCTROW Type.TypeDescription,

Catalog.Description,

Orderltem.OrderlD, Orderltem.Quantity

FROM Type

INNER JOIN (Catalog

INNER JOIN Orderltem

ON Catalog.CatalogltemID = Orderltem.CatalogltemID)

ON Type.ItemTypeCode = Catalog.ItemTypeCode

ORDER BY Type.TypeDescription;

 

Чтобы выбрать клиентов, которые сделали первые заказы в 1988 г. или ранее, и вывести их список в порядке возрастания почтовых индексов, введите следующее:

 

SELECT DISTINCTROW Customer.CompanyName, Customer. City,

PostalCode

FROM Customer

WHERE (((1988) >=

(SELECT MIN (Year (OrderDate))

FROM [Order]

WHERE Order.CustomerlD = Customer.CustomerlD)))

ORDER BY Customer.PostalCode;

 

Чтобы найти всех поставщиков и всех клиентов из штата Вашингтон и располо­жить их в порядке убывания почтового индекса, введите следующее:

 

SELECT DISTINCTROW Customer.CompanyName, Customer.City,

PostalCode

FROM Customer

WHERE Customer.State = "WA"

UNION

SELECT DISTINCTROW Supplier.SupplierName,

Supplier.SupplierCity, Supplier.SupplierPostal

FROM Supplier

WHERE Supplier.SupplierState = "WA"

ORDER BY PostalCode;

 

Чтобы найти всех поставщиков, которые поставляют по крайней мере один
компонент, выполните следующий запрос:

 

SELECT DISTINCTROW Supplier.SupplierName

FROM Supplier

WHERE Exists

(SELECT *

FROM Component

WHERE Component.SupplierlD = Supplier.SupplierlD);

 

Чтобы найти компоненты, стоимость которых больше стоимости любого из компонентов с кодом 008, введите следующую инструкцию:

 

SELECT DISTINCTROW Component. Description,

Component.OurCost

FROM Component

WHERE (Component.OurCost) > ALL

(SELECT OurCost

FROM Component

WHERE ItemTypeCode = "008");

 

Чтобы найти все изделия, стоимость которых выше 100 долларов и при этом для
их комплектации необходимо два дня или количество их в наличии меньше 5
(но последние два условия не выполняются одновременно), введите следующую
инструкцию:

 

SELECT DISTINCTROW Catalog.CatalogltemID,

Catalog.Description, Catalog.OurCost,

Catalog.DaysToBuild, Catalog.NumberInStock

FROM Catalog

WHERE (Catalog.OurCost) > 100

AND ((Catalog. DaysToBuild) = 2

XOR (Catalog.NumberlnStock) < 5);

 

Чтобы выбрать из таблицы Customer (Клиент) и поместить во временную таблицу имена всех клиентов из штата Орегон, введите следующее:

 

INSERT INTO TempCust

SELECT *

FROM Customer

WHERE State = "OR";

Подчиненный запрос

Для использования в предикате сравнения выбирает из одного столбца некоторое множество значений либо вообще ни одного.

Синтаксис:

(SELECT [All | DISTINCT список-выбора

FROM [{ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [ [AS] псевдоним ]} |

< таблица-объединения >},...

[WHERE условие-отбора ]

[GROUP BY имя-столбца,...]

[HAVING условие-отбора ]

 

где список-выбора есть

 

{* | { выражение | имя-таблицы. * |

имя-запроса,* | псевдоним. *}}

 

и где < та6лица-о6ъедчнения > есть

 

({ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ]} |

< таблица-объединения >}
{INNER | LEFT | RIGHT | JOIN

{ имя-таблицы [[AS] псевдоним ] |

имя-запроса-выборки [[AS] псевдоним ] |

< таблица-объединения >}

ON <условие-объединения>)

Специальный символ «звездочка» (*) можно употреблять в списке выбора подчиненного запроса только в тех случаях, когда подчиненный запрос исполь­зуется в предикате EXISTS или когда предложение FROM внутри подчиненного запроса ссылается на единственную таблицу или запрос, содержащие только один столбец.

Для каждой таблицы и каждого запроса вы можете при желании определить альтернативное имя. Это имя вы можете использовать как псевдоним вместо полного имени таблицы при задании имен столбцов в списке выбора, в предло­жении WHERE или в подчиненных предложениях. Если вы производите объединение таблицы или запроса с самим собой, то вам придется употреблять альтернативное имя, чтобы указать, на какую копию таблицы или запроса вы ссылаетесь при задании списка выбора, условий объединения или условий отбора. Если имя таблицы или имя запроса совпадает с зарезервированным словом языка SQL (например, «Order»), такое имя нужно заключать в квад­ратные скобки.

Когда вы перечисляете несколько таблиц или запросов и не указываете условий объединения, в качестве исходной таблицы используется Декартово произведе­ние всех таблиц. Например, FROM TableA, TableB заставляет Access считать областью поиска все строки из таблицы TableA, присоединенные к каждой из строк таблицы ТаblеВ. В этом случае число логических записей, обрабатывае­мых Access, может быть равно числу строк в таблице TableA, умноженному на число строк в таблице ТаblеВ. В качестве результата Access возвратит те записи, для которых условия отбора, определенные в предложениях WHERE и HA­VING, примут значение Истина.

В условии отбора в предложении WHERE подчиненного запроса вы можете ссылаться на столбцы любой таблицы или запроса, определенных во внешних запросах. Если ссылка на таблицу или запрос может восприниматься неодноз­начно, нужно полностью задавать имя столбца.

В предложении GROUP BY вы можете ссылаться на любой столбец из любой таблицы, указанной в предложении FROM, даже если этот столбец не входит в список выбора. Если перед предложением GROUP BY расположено предло­жение WHERE, Access формирует группы из строк, отобранных после приме­нения предложения WHERE. Когда вы включаете предложение GROUP BY в инструкцию SELECT, спи

Поделиться:

Воспользуйтесь поиском по сайту: