Теоретико-множественные операции

Операция объединения

Объединение двух таблиц содержит те строки, которые есть либо в первой, либо во второй, либо в обеих таблицах.

Пример 1

Предположим, необходимо получить список товаров, по которым в некотором периоде (например, с 01.01.2002 по 30.04.2002) было движение.

Для решения задачи необходимо выбрать все записи из таблицы «Закупки» за определенный период по полю «Код товара». Аналогичную операцию необходимо выполнить и для таблицы «Продажи». Далее необходимо соединить запросы посредством оператора UNION.

SELECT [Код товара]

FROM Закупки

WHERE ([Дата операции]>=#01/01/2002#)

AND ([Дата операции]>=#30/04/2002#)

UNION

SELECT [Код товара]

FROM Продажи

WHERE ([Дата операции]>=#01/01/2002#)

AND ([Дата операции]>=#30/04/2002#)

Примечание: Здесь и далее предполагается, что получение списка кодов товаров тож­дественно получению списка наименований товаров, т.к. список наименований товаров получается простым соединением с данными таблицы «Товары».

Пример 2

Предположим, имеются две таблицы «Закупки» и «Продажи». В таблице «Закупки» содержится информация о приобретаемых у поставщиков товарах. В таблице «Продажи» содержится информация об операциях реализации товаров покупателям.

Обе таблицы имеют поля «КодОперации», «ДатаОперации», «КодКонтрагента», «КодТовара», «Количество», «Сумма». Структура таблиц одинакова, а содержимое – разное.

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

Решение

1. Составить запрос, выбирающий записи из таблицы «Закупки» и приводящий эти записи в соответствии с показанной выше формой.

Запрос 1.

SELECTДатаОперации,
КодКонтрагента,
КодТовара,
Количество ASЗакупкиКоличество,
Сумма ASЗакупкиСумма,
0 ASПродажиКоличество,
0 ASПродажиСумма

FROMЗакупки.

2. Составить запрос, выбирающий записи из таблицы «Продажи» и приводящий эти записи в соответствии с показанной выше формой.

Запрос 2.

SELECTДатаОперации,
КодКонтрагента,
КодТовара,
0 AS ЗакупкиКоличество,
0 ASЗакупкиСумма,
Количество AS ПродажиКоличество,
Сумма AS ПродажиСумма

FROM Продажи.

3. Создать запрос, объединяющий результаты запросов «Запрос1» и «Запрос2» в единую таблицу.

Запрос 3.

SELECT * FROMЗапрос1

UNION

SELECT* FROMЗапрос2

В результате выполнения запроса получим таблицу, объединяющую данные таблиц «Закупки» и «Продажи».

Операция пересечения

Пересечениедвух таблиц содержит только те строки, которые есть и в первой и во второй.

Пример:

Определить список товаров, по которым были и закупки и продажи.

SELECT [Код товара]

FROM Закупки

WHERE [Код товара] IN

(SELECT [Код товара]

FROM Продажи)

где IN – оператор вхождения в другой набор данных; выражение
SELECT [Код товара] FROM Продажипредставляет собой подзапрос. Использование подзапросов будет подробно рассмотрено в дальнейшем.
В настоящий момент достаточно знать, что для решения различных вычислительных задач в операторе SELECT широко используются подзапросы. Подзапрос также представляет собой SQL-оператор, начинающийся со слова SELECT.

Представленный выше запрос читается следующим образом: выбрать коды товаров из таблицы «Закупки», для которых код товара также входит в таблицу, формируемую путем выбора кодов товаров из таблицы «Продажи».

В общем случае операцию пересечения можно представить в виде следующего шаблона:

SELECT <Имя поля>

FROM A

WHERE <Имя поля> IN

(SELECT <Имя поля>

FROM B),

где А и B – имена таблиц или запросов, результат пересечения которых необходимо получить.

Операция разности

Разность двух таблиц содержит только те строки, которые есть в первой, но отсутствуют во второй. Определение данной операции на языке SQL практически полностью совпадает с определением операции пересечения. Отличием является наличие слова NOT перед словом IN.

Пример:

Определить список товаров, которые закупались, но не продавались:

SELECT [Код товара]

FROM Закупки

WHERE [Код товара] NOT IN

(SELECT [Код товара]

FROM Продажи)

Операции группировки и агрегирования

В SQL добавлены дополнительные функции, которые позволяют вычислять обоб­щенные групповые значения. Это так называемые агрегатные функции. Эти функции используются очень часто для получения итоговых (расчетных) значений на основе данных одного или нескольких полей таблицы. Для применения агрегатных функций предполагается предварительная операция группировки. При группировке все множество кортежей отношения разбивается на группы, в которых собираются кортежи, имеющие одинаковые значения атрибутов, заданных в списке группировки. Список группировки – это список полей таблицы, указанных в строке GROUP BY.

В языке SQL определены следующие агрегатные функции:

Агрегатные функции используются подобно именам полей в операторе SELECT, но с одним исключением: они берут имя поля как аргумент. С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT, MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями МАХ и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. В качестве примера определим сколько товаров перечислено в списке товаров:

SELECT Count(*) AS КоличествоТоваров

FROM Товары;

В результате получим таблицу:

Однако чаще всего операции агрегирования применяются совместно с операциями группировки.

Предположим, необходимо выяснить, сколько было продано товаров каждого наименования за период с 01.01.2001 по 01.02.2001. В этом случае запрос будет выглядеть так:

SELECT [Код товара], SUM(Количество) AS КоличествоТовара

FROM Продажи

WHERE [Дата операции] BETWEEN #01/01/2001# AND#02/01/2001#

GROUP BY [Код товара].

В случае, если в условии запроса используется поле, не указанное в строке SELECT, необходимо использование строки WHERE. Если же
необходимо указать условие для группы, желательно использование строки HAVING, т.к. в этом случае запрос выполняется быстрее. Так, если необходимо определить товары с объемами продаж свыше 20, запрос будет
выглядеть так:

SELECT[Код товара], SUM(Количество) AS КоличествоТовара

FROM Продажи

WHERE [Дата операции] BETWEEN #01/01/2001# AND#02/01/2001#

GROUP BY[Код товара]

HAVING SUM(Количество)>20.

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

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

Подзапрос представляет собой такой же SQL-запрос, начинающийся со слова SELECT, который выполняет расчет какого-либо значения, отбор каких-либо значений из других таблиц. Подзапрос всегда заключается в круглые скобки.

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

1. Когда необходимо провести отбор (фильтрацию) одной таблицы на основе рассчитанного или выбранного значения из другой таблицы.

2. Когда запрос формирует таблицу на основе других запросов.

Пример

Составить список товаров, по которым объем продаж за апрель 2002 года был максимальным.

Решение:

Для решения задачи необходимо сначала рассчитать объемы продаж по товарам за апрель 2002 года. Эта задача реализуется следующим SQL-оператором:

SELECT[Код товара], SUM(Количество) AS[Объем продаж]

FROM Продажи

WHERE ([Дата операции] >= #04/01/2002#) AND

([Дата операции] <= #04/30/2002#)

GROUP BY [Код товара].

Дадим этому запросу имя «ОбъемыПродаж».

Теперь необходимо рассчитать максимальный объем продаж из
полученного предыдущим запросом набора данных. Для этого напишем запрос следующего вида:

SELECT MAX([Объем продаж]) as [Максимальный объем продаж]

FROM (…)

Далее вместо многоточия подставим созданный ранее запрос. Получим:

SELECT MAX([Объем продаж]) AS [Максимальный объем продаж]

FROM

(SELECT [Код товара], SUM(Количество) AS [Объем продаж]

FROM Продажи

WHERE([Дата операции] >= #01/04/2002#) AND

([Дата операции] <= #30/04/2002#)

GROUP BY [Код товара]).

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

SELECT MAX([Объем продаж]) AS [Максимальный объем продаж]

FROM ОбъемыПродаж.

Назовем этот запрос «Максимальный объем продаж».

Последней операцией будет отбор записей из запроса «ОбъемыПродаж» для получения списка товаров с максимальным объемом продаж за апрель 2002 года. Переформулируем эту задачу на языке, близком к SQL: выделить (отобрать) все записи из запроса «ОбъемыПродаж», для которых выполняется условие [Объем продаж] = [Максимальный объем продаж].

SELECT T.[Код товара]

FROM ОбъемыПродаж AS T, [Максимальный объем продаж] AS R

WHERE T.[Объем продаж]=R.[Максимальный объем продаж]

1. Изучить теоретический материал, разбирая все представленные в нем примеры решения типовых задач.

2. На основе задания определить структуру результирующего отношения (состав полей таблицы, получаемой в результате выполнения запроса).

3. Определить источники данных для создания результирующего отношения.

4. Определить последовательность действий, необходимых для получения результирующего отношения – последовательность операций по извлечению данных из таблиц-источников и их последующей обработке.

5. Составить запрос (или несколько запросов) на языке SQL, реализующий определенную в п. 3 последовательность действий.

6. Проверить правильность решения задачи на тестовом наборе данных (используя в таблицах-источниках произвольный набор данных).

1. Что такое SQL?

2. Опишите назначение языка SQL. Почему появился язык SQL?

3. Перечислите известные вам стандарты языка SQL.

4. Чем язык SQL отличается от других языков? Можно ли язык SQL отнести к языкам программирования?

5. Опишите структуру языка SQL и назначение каждого из ее элементов.

6. Дайте определение понятию «тип данных». Перечислите основные группы типов данных. Перечислите известные вам типы данных, используемые в языке SQL.

7. Опишите основные операторы языка определения данных DDL. Приведите пример использования одного из них.

8. Опишите основные операторы языка манипулирования данными DML. Приведите пример для каждого из них.

9. Какой оператор языка SQL используется для создания таблиц базы данных? Приведите пример.

10. Какие операторы используются для добавления и изменения данных в таблице базы данных?

11. Как удалить записи из базы данных при помощи языка SQL? Привести пример.

12. Для чего используется оператор SELECT? Опишите синтаксис оператора SELECT и все входящие в него элементы.

13. Как выполнить операцию горизонтального выбора данных из таблицы? Приведите пример. Для чего используется операция горизонтального выбора?

14. Для чего используется операция вертикального выбора? Как осуществить эту операцию при помощи оператора SELECT?

15. Опишите процесс условного соединения наборов данных. Для чего используется условное соединение? Какие виды условного соединения существуют в языке SQL? Приведите пример одного из них.

16. Внутреннее условное соединение наборов данных: для чего используется, опишите синтаксис, приведите пример использования.

17. Внешнее левое условное соединение наборов данных: для чего используется, опишите синтаксис, приведите пример использования.

18. Внешнее правое условное соединение наборов данных: для чего используется, опишите синтаксис, приведите пример использования.

19. Внешнее естественное условное соединение наборов данных: для чего используется, опишите синтаксис, приведите пример использования.

20. Опишите известные вам теоретико-множественные операции. Приведите пример реализации каждой из них.

21. Как произвести соединение однородных по структуре, но разнородных по содержанию данных из многих таблиц в одну? Приведите пример.

22. Опишите реализацию операции пересечения наборов данных на языке SQL. Приведите примеры.

23. Для чего используется операция разности при работе с наборами данных? Приведите пример реализации на языке SQL.

24. Понятие группировки. Опишите известные вам агрегатные функции и приведите примеры их использования.

25. Понятие вложенного запроса. Приведите пример использования вложенного запроса.

1. Робинсон С. Microsoft Access 2000: учебный курс. – СПб.: Питер, 2000.

2. Баркер С.Ф. Профессиональное программирование в Microsoft Access 2002.: Пер. с англ. – М.: «Издательский дом «Вильямс», 2002. – 992 с.

3. Пасько В. Access 97(русифицированная версия) – К.: BHV, 1998.

4. Новалис C. Access 97. Руководство по макроязыку и VBA.

5. Дейт К. Введение в системы баз данных: Пер. с англ. – 6-е изд. – СПб.: Вильямс, 1999.

6. Хансен Г., Хансен Д. Базы данных: разработка и управление: Пер. с англ. – М: ЗАО «Издательство БИНОМ», 1999.

7. Карпова Т.С. Базы данных: модели, разработка, реализация. – СПб.: Питер, 2001.

8. Шкарина Л. Язык SQL: учебный курс. – СПб.: Питер, 2001.

9. Бобровский С. Delphi 5: учебный курс. – СПб.: Питер, 2000.

10. Информационные системы / Петров В.Н. – СПб.: Питер, 2002. – 688 с.

Даны следующие таблицы:

Составить SQL-запросы для решения следующих задач:

1. Определить список товаров, по которым в течение расчетного периода не фиксировалось никакого движения (ни закупок, ни продаж).

Результаты запроса представить в виде таблицы следующего вида:

2. Получить остатки по товарам на складах на определенную дату.

Результаты представить в виде следующей таблицы:

где поля «ОстатокКоличество» и «ОстатокСумма» рассчитываются как результат вычисления выражения [ЗакупкаКоличество] – [ПродажаКоличество] и [ПродажаСумма] – [ЗакупкаСумма] для операций, значение поля «ДатаОперации» которых меньше параметра [ТекущаяДата].

3. Составить оборотную (приход и расход) ведомость по товарам за период в натуральном выражении. Результаты представить в виде следующей таблицы:

где ЗакупкаКоличество = ЗакупкаКоличество(ДатаОперации >=
[НачальнаяДата] И ДатаОперации <= [КонечнаяДата])

ПродажаКоличество = ПродажаКоличество(ДатаОперации >=
[НачальнаяДата] И ДатаОперации <= [КонечнаяДата])

Даны следующие таблицы:

Составить SQL-запросы для решения следующих задач:

1. Определить тройку лидеров по объемам продаж среди товаров за период. Результаты представить в виде следующей таблицы:

где поле «ОбъемПродаж» рассчитывается на основе поля «Количество» таблицы «Продажи» за период, т.е. при ограничении:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

2. Выбрать операции по закупкам и реализации товаров за период. Результаты представить в виде следущей таблицы:

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

3. Определить список товаров, по которым не было реализации за период. Результаты представить в виде следующей таблицы:

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

Имеются следующие таблицы

Составить SQL-запросы для решения следующих задач:

1. Определить общие затраты на закупку товаров за период. Результаты представить в виде следующей таблицы:

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

2. Составить оборотно-сальдовую (остаток на начало, приход, расход, остаток на конец) ведомость по товарам за период в натуральном выражении.

Результаты представить в виде следующей таблицы:

где ОстатокНачКоличество = ЗакупкаКоличество(ДатаОперации < [НачальнаяДата]) - ПродажаКоличество(ДатаОперации < [НачальнаяДата]).

ЗакупкаКоличество = ЗакупкаКоличество(ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]).

ПродажаКоличество = ПродажаКоличество(ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]).

ОстатокКонКоличество = ОстатокНачКоличество + ЗакупкаКоличество – ПродажаКоличество.

3. Рассчитать объемы продаж товаров за период в натуральном и стоимостном выражении (количество проданного товара и его стоимость). Результаты представить в виде следующей таблицы:

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

Имеются следующие таблицы

Составить SQL-запросы для решения следующих задач:

1. Рассчитать объемы закупок товаров за период в натуральном и стоимостном выражении (количество закупленного товара и его стоимость). Результаты представить в виде следующей таблицы:

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

2. Определить общую выручку от реализации товаров за период. Результаты представить в виде таблицы:

где поле «ВыручкаСумма» рассчитывается на основе поля «Сумма» таблицы «Продажа».

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

3. Получить остатки по товарам на определенную дату. Результаты представить в виде следующей таблицы:

где поля «ОстатокКоличество» и «ОстатокСумма» рассчитываются как результат вычисления выражения [ЗакупкаКоличество] – [ПродажаКоличество] и [ПродажаСумма] – [ЗакупкаСумма] для операций, значение поля «ДатаОперации» которых меньше значения параметра [ТекущаяДата].

Имеется база данных, состоящая из таблиц представленных ниже:

Составить SQL-запросы для решения следующих задач:

1. Определить 5 наименований продукции лидирующих по объемам продаж за период и 5 покупателей, имеющих максимальные обороты за период. Результаты оформить в виде следующих таблиц:

Таблица 1

где поле «ОбъемПродажСумма» рассчитывается на основе поля «Сумма» таблицы «РеализацияПродукции».

Таблица 2

где поле «Обороты за период» рассчитывается на основе поля «Сумма» таблицы «РеализацияПродукции».

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

2. Определить объемы расхода материалов каждого наименования в производство за период в натуральном и стоимостном выражении (т.е. количество и стоимость).

где поля «РасходВПроизводствоКоличество» и «РасходВПроизводствоСумма» рассчитываются на основе соответствующих полей «Количество» и «Сумма» таблицы «РасходМатериалов».

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

3. Определить общую выручку от реализации за период. Результаты представить в виде следующей таблицы:

где поле «ВыручкаСумма» рассчитывается на основе поля «Сумма» таблицы «Реализация».

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

Имеется база данных, состоящая из таблиц представленных ниже.

* поле «Норма» в таблице «НормыРасхода» представляет собой значение нормы расхода материала на единицу изготавливаемой продукции.

Составить SQL-запросы для решения следующих задач:

1. Определить прямые материальные затраты (суммарную стоимость израсходованных в производство материалов) на выполнение плана выпуска продукции по каждому наименованию готовой продукции.

Стоимость i-го материала = Норма расхода * СредняяЦена *
Объем производства

Прямые материальные затраты для j-й продукции =

= Стоимость 1-го материала + Стоимость 2-го материала …

При этом нормы расхода материалов на единицу продукции должны быть взяты из таблицы «НормыРасхода».

Средняя стоимость материала должна быть взята из таблицы «ЗакупкаМатериалов» как результат деления общей суммы на приобретение материала на количество приобретенного материала.

Объем производства должен быть взят из таблицы «ПланВыпуска».

Результаты представить в виде следующей таблицы:

2. Определить выручку от реализации продукции по каждому виду продукции за период.

где поле «ВыручкаОтРеализации» рассчитывается на основе поля «Сумма» таблицы «РелизацияПродукции».

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата]

3. Определить нормативный расход материалов на произведенную продукцию (по каждому наименованию готовой продукции) за период на основе таблиц «ПроизводствоПродукции» и «НормыРасхода».

Нормативный расход материала определить по формуле:

Расход i-го материала = Норма * ОбъемПроизводства j-й продукции,

где Норма – норма расхода материала на единицу продукции (из таблицы «НормыРасхода»); объем производства необходимо взять из таблицы «ПроизводствоПродукции».

Результаты представить в виде следующей таблицы:

На отбираемые записи должно накладываться ограничение:

ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата].

Имеется база данных, состоящая из таблиц, представленных ниже:

Составить SQL-запросы для решения следующих задач:

1. Получить количественную оборотно-сальдовую ведомость (остаток на начало, приход, расход, остаток на конец) по каждому виду продукции за период. Результаты представить в виде следующей таблицы:

где графа ОстатокНачКоличество = Производство (ДатаОперации <
[НачальнаяДата]) – Реализации (ДатаОперации <[НачальнаяДата]);

графа Производство рассчитывается на основе поля «Количество» таблицы «ПроизводствоПродукции»;

графа Реализация - на основе поля «Количество» таблицы «РеализацияПродукции»;

графа ОстатокКонКоличество = ОстатокНачКоличество + Производство – Реализация.

2. Определить среднюю цену закупки по каждому материалу на определенную дату. Результаты представить в виде следующей таблицы:

где поле СредняяЦена рассчитывается на основе полей «Сумма» и «Количество» таблицы «ЗакупкаМатериалов»:

Средняя цена = Сумма : Количество

На отбираемые записи должно накладываться ограничение:

ДатаОперации < [ТекущаяДата]

3. Получить количественную оборотно-сальдовую ведомость (остаток на начало, приход, расход, остаток на конец) по материалам за период.

Результаты представить в виде следующей таблицы:

где графа ОстатокНачКоличество = Закупка (ДатаОперации <[НачальнаяДата]) – РасходВПроизводство (ДатаОперации <[НачальнаяДата]);

графа Закупкарассчитывается на основе поля «Количество» таблицы «ПроизводствоПродукции», причем на отбираемые записи должно накладываться ограничение: ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата];

графа РасходВПроизводство - на основе поля «Количество» таблицы «РасходМатериалов», причем на отбираемые записи должно накладываться ограничение: ДатаОперации >= [НачальнаяДата] И ДатаОперации <= [КонечнаяДата];

графа ОстатокКонКоличество = ОстатокНачКоличество + Закупка – РасходВПроизводство.

Имеется база данных, состоящая из таблиц, представленных ниже:

Составить SQL-запросы для решения следующих задач:

Не нашли, что искали? Воспользуйтесь поиском по сайту: