From накладная

WHERE #10/27/2008# IN

(SELECT ДАТА_ОТГ

FROM ДОГОВОР WHERE ДОГВОР.НОМ_ДОГ=НАКЛАДНАЯ.НОМ_ДОГ);

Подзапросы с использованием HAVING.

7.32. Определить количество товаров, стоимость которых превышает среднюю стоимость монитора – Монитор 17LG.

SELECT Count (НАИМ_ТОВ), ЦЕНА

FROM ТОВАР

GROUP BY ЦЕНА

HAVING ЦЕНА<(SELECT AVG (ЦЕНА)

From ТОВАР WHERE НАИМ_ТОВ='Монитор 17LG');

Связные подзапросы в HAVING

7.34. Отобрать сумму, на которую поставлены товары, сгруппировав поставки по датам поставок и исключить даты, в которые поставлено товаров меньше чем по 2 договорам.

SELECT ДАТА_ОТГ, Sum(СУММА_НАКЛ) AS ВСЕГО

FROM НАКЛАДНАЯ AS A

GROUP BY ДАТА_ОТГ

HAVING 2< (SELECT COUNT(СУММА_НАКЛ)

FROM НАКЛАДНАЯ AS B

WHERE A.ДАТА_ОТГ= B.ДАТА_ОТГ);

Использование оператора EXISTS в подзапросах.

Оператор EXISTS генерирует значение истина в подзапросах, если существует хотя бы одно найденное значение и ложь, если нет значений.

7.36. Отобрать покупателей, которые приобрели товары хотя бы один раз. (Использование EXISTS).

SELECT ПОКУПАТЕЛЬ.КОД_ПОК

FROM ПОКУПАТЕЛЬ

WHERE EXISTS

(SELECT* FROM ДОГОВОР

WHERE ПОКУПАТЕЛЬ.КОД_ПОК=ДОГОВОР.КОД_ПОК);

3.4 Самостоятельная работа

7.3. Выбрать из таблицы товары перечень поставляемых товаров. (Использовать необходимый предикат).

7.4. Выбрать из таблицы Поставки перечень поставщиков, которые поставляют товары. Коды поставщиков повторяться не должны.

7.6. Выбрать из таблицы поставки, 8 поставок которые были сделаны последними.

7.7. Выбрать из таблицы план поставки все поля и провести сортировку по сроку поставки.

7.9 Выбрать все данные о покупателях обслуживающихся в банке Кредит.

7.10 Выбрать все данные о поставках на 22.07.2008. (Константу дата записать как #22/07/2008#).

7.11. Выбрать фамилию и номер телефона для покупателя с кодом П002.

7.12. Отобрать поставки, которые были проведены с 1.07.2008 по 20.07.2008.

7.13. Выбрать покупателей, у которых телефон обслуживается сотовой связью МТС (первые цифры (918) далее три любых цифры тире и четыре любых цифры)

7.14. Отобрать товары, у которых нет фото товара.

7.15. Отобрать ответственных склады в которых ответственными лицами являются Иванов, Петров, Сидоров.

7.19. Выберите покупателей, которые приобретали товары более 2 раз.

7.20. Вычислить общее количество поставляемых товаров по датам поставок.

7.21. Вычислите минимальную партию поставки для каждого договора.

7.22. Вычислите максимальную партию поставки для каждого товара.

7.23. Вычислите общее количество поставок по различным товарам.

7.25. Найти адрес покупателя товар с кодом договора Д111.

7.26. Известно наименование организации таблицы покупатели, но неизвестно значение КОД_ПОК. Найти данные обо всех договорах указанной организации. (Компьютер маркет)

7.27. Известен НОМ_ДОГ но неизвестно, КодТовара. Найти Наименование товара Цену. НОМ_ДОГ = Д222.

7.28. Известен НОМ_ДОГ, но неизвестен, КОД_ПОК Найти Наименование организации, Адрес, номер телефона. НОМ_ДОГ = Д111..

7.30. Выбрать сведения обо всех поставщиках, с которыми заключен договор 5.6.2006.

7.31 Выбрать сведения о том, какого числа и по какому договору поставлен товар с кодом Т003.

7.33 Определить количество товаров, стоимость которых не превышает максимальное значение розничной стоимости товара FDD 3,5.

7.35. Отобрать сумму, на которую поставлены товары, сгруппировав поставки по коду склада, исключив поставки которые были один раз.

7.37. Отобрать товары, количество которых в одну поставку больше 9.

3.5 Вопросы для самопроверки

1. Какая инструкция SQL соответствует запросу на выборку?

2. Изменяет ли инструкция select данные в базе?

3. В каком предложении инструкции SQL указываются таблицы, на которых создается запрос?

4. В каком предложении инструкции SQL задаются условия отбора записей?

5. Можно ли для полей, выбираемых в инструкции SQL для вывода, применять функции?

6. В каком предложении инструкции SQL задаются поля, по которым должна быть выполнена группировка записей запроса?

7.

Лабораторная работа№ 4. Многотабличные запросы

4.1 Многотабличный запрос

Многотабличные запросы позволяет сформировать записи путем объединения взаимосвязанных записей таблиц. Например, при объединении двух нормализованных связанных одно-многозначными отношениями таблиц, для которых обеспечивается связная целостность, результирующая запись образуется на основе записи подчинённой таблицы, в которую добавляются поля из связанной записи в главной таблице. Подобное объединение формирует ненормализованную таблицу, в которой число записей равно числу записей в подчиненной таблице. При этом данные главной таблицы дублируются в различных записях результирующей таблицы.

Рассмотрим технологию конструирования многотабличного запроса.

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

Выберем Создание запроса в режиме конструктора.

Сформируем схему данных запроса: В окне Добавление таблицы (см. Рисунок 8.1) выберем взаимосвязанные таблицы:

- покупатель — для выборки наименования покупателя (поле наим_пок);

- поставка_план — для выборки месяца поставки (поле срок_пост) и количества товара, запланированного к поставке (поле кол_пост);

- товар — для выборки наименования товара (поле наим_тов), представленного кодом код_тов в таблице поставка_план.

- Закроем окно Добавление таблицы.

В окне конструктора запросов будет представлена схема данных запроса, содержащая выбранные таблицы. Между таблицами поставка_план и товар автоматически установлена связь. Таблицы покупатель и поставка_план не имеют полей, по которым их можно было бы связать, поэтому в соответствии с общей схемой данных добавим в схему данных запроса таблицу договор, которая позволит организовать недостающую связь (Рисунок 8.1).

Рисунок 8.1. Окно запроса с созданной схемой данных

Перетащим с помощью мыши поля, включаемые в результат выполнения запроса, в строку бланка запроса Поле: наим_пок — из таблицы покупатель; наим_тов — из таблицы товар; срок_пост и кол_пост — из таблицы поставка_план.

Допустим, необходимо получить информацию о плане поставки конкретных товаров - FDD 3,5. и HDD Maxtor 20GB.

Запишем в разных строках Условие отбора наименования этих товаров, поскольку необходимо выбрать записи со значением в поле наим__тов "fdd 3,5" или "hdd maxtor 20gb". Если марка HDD не известна или безразлична, зададим ее с использованием символа шаблона — звездочка "*". После ввода наименования с символом шаблона система сама вставляет оператор Like, определяющий поиск по образцу. После заполнения бланка запроса он примет вид, представленный на Рисунок 8.2.

Рисунок 8.2. Запрос план поставок товаров FDD 3,5 и HDD

Выполним запрос. Результат выполнения запроса д на рисунке 8.3.

Рисунок 8.3. Результат выполнения запроса о плане поставок FDD 3,5 и HDD

3.9.10 Формирование записей результата при выполнении запроса

Сначала рассмотрим, как формируются записи результата запроса, если не заданы условия отбора. В примере для каждой пары таблиц указан способ объединения только тех записей, в которых связанные поля обеих таблиц совпадают (в соответствии с заданным по умолчанию первым способом объединения). При этом, поскольку рассматриваемые пары таблиц находятся в отношении один-ко-многим и в схеме данных для их связи определены - параметры обеспечения целостности, в результате сформируется столько записей, сколько их имеется в подчиненной таблице низшего уровня поставка_план. Обеспечение целостности не позволяет включить в таблицу поставка_план записи, для которых не существует записей в главных таблицах. По сути, записи таблицы поставка_план дополняются данными из таблиц товар и покупатель. Вместо ключевого поля код__тов в запись о плане поставок будет включено наименование товара, а вместо ном _дог – наименование покупателя.

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

Сохраните запрос под именем " План поставок ".

3.9.11 Параметрические запросы

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

В условие отбора поля наим_тов вместо конкретных значений введем название параметра (Рисунок 8.4). Название параметра вводится как текст, заключенный в квадратные скобки: [Наименование товара]

Рисунок 8.4. Бланк запроса с параметрами для поля НАИМ_ТОВ

3.9.12 Ссылки на имена полей различных таблиц в условии отбора

В условии отбора в качестве операндов могут использоваться значения из разных полей. Выбрать записи из таблицы отгрузка, в которых стоимость товара не соответствует стоимости, указанной для этого товара в таблице товар, вычислить отклонения. Запрос представленный на рисунке 8.5.

Рисунок 8 5. Бланк запроса с параметрами для поля НАИМ_ТОВ

Вычисляемое поле заполняется через кнопку Построить (Рисунок 8.6.)

Рисунок 8 6. Окно построителя для расчета отклонения

Результат представлен на Рисунок 8.7.

Рисунок 7.7. Записи об отгрузки товаров с неверно указанной суммой

4.2 Решение задач на основе нескольких запросов

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

Задача: Необходимо произвести анализ выполнения договоров поставки товаров на конец заданного месяца. При решении этой задачи должно быть подсчитано количество товара, запланированного к поставке, отгруженного покупателям в соответствии с договорами, и получена разность поставок.

Данные о плановых поставках хранятся в таблице поставка_план. На ее основе создадим запрос для подсчета суммарного количества товаров, запланированных к поставке на конец заданного месяца (Рисунок 8.8).

Рисунок 7.8. Подсчет плановой поставки товаров

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

Данные о фактически, отгруженных покупателям товаров хранятся в таблице отгрузка. На ее основе может быть выполнен подсчет суммарного количества фактически поставленных товаров. Для отбора поставок выполненных до конца заданного месяца, добавим в запрос таблицу накладная, в которой хранится дата отгрузки товаров (Рисунок 8.9). Таблицы, на которых построен запрос Факт, находятся в отношении один-ко-многим, для их связи по составному ключу установлены параметры обеспечения целостности. В результате объединения этих таблиц формируется таблица запроса с числом записей, равным числу записей в подчиненной таблице отгрузка.

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

Рисунок 8.9. Подсчет суммарного количества отгруженных товаров

Сравнение количества запланированных к поставке и отгруженных товаров логично произвести в запросе, основанном на двух предыдущих. Однако необходимость учесть, что по некоторым договорам товар мог не отгружаться и в то же время отгружаться покупателям, которые не заключали договоров, требует добавления в запрос таблицы товар, в которой представлена вся номенклатура товаров фирмы. Последнее обстоятельство позволит установить в запросе такие параметры объединения таблиц, которые приведут к формированию в таблице запроса записей не только о товарах, по которым был определен план и факт поставки, но и о запланированных к поставке, но не отгружаемых; отгружаемых, но не планируемых, вообще не планируемых и не отгружаемых товарах (Рисунок 8.10).

В запросе для связей установлен второй способ объединения записей, поэтому в таблицу запроса будут включены и те записи из таблицы товар, для которых нет связанных записей в таблицах план и Факт.

Учитывая, что во всех пустых полях таблицы запроса определено значение Null, для получения разности между запланированным и фактически поставленным количеством товара создадим вычисляемое поле с выражением Nz([Sum-КОЛ_ПОСТ])-Nz ([Sum-КОЛ_ОТГР]). Присвоим вычисляемому полю имя " Отклонение ".

Функция nz (выражение; [значение__если__Null]) возвращает значение, указанное первым аргументом, или для поля со значением Null новое значение, указанное вторым аргументом. Если второй аргумент не указан, по умолчанию для числового поля со значением Null возвращается значение 0, для символьного — пустая строка

Рисунок 8.10. Вычисление разности плановых и фактических поставок

В таблице запроса Анализ выполнения плана (Рисунок 8.11) представлен весь список товаров фирмы. Показано, что не все товары были заказаны в договорах, только по ряду из них выполнялась отгрузка товаров, а некоторые товары отгружались без предварительного оформления договоров. Выполнение запроса Анализ выполнения плана инициирует выполнение запросов План и Факт, поэтому нет необходимости в их предварительном выполнении.

Записав в условие отбора поля кол_пост значение Not Null, можно отобрать только строки, относящиеся к товарам, на которые были заключены договоры. Для того чтобы явно увидеть, в какой последовательности производится объединение таблиц запроса, просмотрите запрос в режиме SQL.

В предложении from указано, что сначала будет производиться левое внешнее объединение таблиц товар и план по полю связи код_тов (товар left join План on товар. код_тов = план. код_тов). Затем будет выполнено левое внешнее объединение таблицы, полученной в результате первого объединения, и таблицы Факт по тому же полю связи.

Объединение таблиц в рассматриваемом запросе дает правильный результат только потому, что в каждой из объединяемых таблиц код_тов является ключом. В таблице товар ключ определен изначально. В таблицах запросов План и Факт поле код_тов также имеет уникальные значения, т. к. они получены в результате группировки по этому полю.

Дата добавления: 2015-10-24