Пример сложного запроса

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

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

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

Вид	Блюдо
Горячее	Помидоры с луком	калорий -	244.6	0.44	руб
Горячее	Бефстроганов	калорий -	321.3	0.53	руб
Горячее	Драчена	калорий -	333.9	0.33	руб
Горячее	Каша рисовая	калорий -	339.2	0.27	руб
Горячее	Омлет с луком	калорий -	354.9	0.36	руб
Десерт	Яблоки печеные	калорий -	170.2	0.30	руб
Десерт	Крем творожный	калорий -	394.3	0.27	руб
Закуска	Салат летний	калорий -	155.5	0.32	руб
Закуска	Салат витаминный	калорий -	217.4	0.37	руб
Закуска	Творог	калорий -	330.0	0.22	руб
Закуска	Мясо с гарниром	калорий -	378.7	0.62	руб
Напиток	Кофе черный	калорий -	7.1	0.05	руб
Напиток	Компот	калорий -	74.4	0.14	руб
Напиток	Кофе на молоке	калорий -	154.8	0.11	руб
Напиток	Молочный напиток	калорий -	264.9	0.34	руб
Суп	Суп молочный	калорий -	396.6	0.22	руб

SELECT Вид, Блюдо, 'калорий -',

(SUM(INT((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000)),

(SUM(Стоимость/К_во*Вес/1000)+MIN(Труд/100)),’руб’

FROM Блюда, Вид_блюд, Состав, Продукты, Наличие

WHERE Блюда.БЛ = Состав.БЛ

AND Состав.ПР = Продукты.ПР

AND Состав.ПР = Наличие.ПР

AND Блюда.В = Вид_блюд.В

AND БЛ NOT IN

(SELECT БЛ

FROM Состав

WHERE ПР IN

(SELECT ПР

FROM Наличие

WHERE К_во = 0))

GROUP BY Вид, Блюдо

HAVING SUM(Стоимость/К_во*Вес/1000+MIN(Труд/100)) < 1.5

AND SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000) < 400

ORDER BY Вид, 4;

Рис. Пример сложного запроса

Такой результат, нестрого говоря, строился следующим образом.

1. FROM. Эта фраза инициирует создание в рабочей памяти таблицы, являющейся декартовым произведением таблиц Блюда, Вид_блюд, Состав, Продукты и Наличие.
2. WHERE. Эта фраза нужна для преобразования полученного декартова произведения в естественное соединение и удаления из последнего строк с кодами блюд, не обеспеченных продуктами. Естественное соединение образуется путем вычеркивания строк, где не совпадают: код блюда из таблицы Блюда с кодом блюда из таблицы Состав, код продукта из таблицы Состав с кодом продукта из таблицы Продукты и т.д. Обеспеченность блюда всеми продуктами проверяется с помощью последовательности подзапросов. Внутренний подзапрос выдает перечень кодов продуктов, которых нет в кладовой пансионата. Следующий подзапрос выдает коды тех блюд, в состав которых должны входить "отсутствующие" продукты. И, наконец, из естественного соединения вычеркиваются строки с кодами полученных блюд (точнее оставляются строки "Где код блюда не принадлежит перечню кодов блюд, полученному в подзапросе".
3. SELECT. Из полученного соединения удаляются столбцы, не используемые в выражениях SELECT или других фразах. Если в списке SELECT есть выражения (константы), то для хранения их значений формируются дополнительные столбцы и инициируются операции по их заполнению. В рассматриваемом примере будут сохранены столбцы Вид, Блюдо, Белки, Углев, Жиры, Вес, Стоимость, К_во и созданы дополнительные столбцы для формирования и хранения значений стоимости и калорийности составляющих каждого блюда, а также для хранения текстовых констант 'калорий -' и 'руб'. Обратите внимание на прием, использованный при суммировании стоимостей продуктов, входящих в состав блюда, и стоимости его приготовления (Труд): можно ли заменить MIN на MAX или AVG?
4. GROUP BY. Отредактированное естественное соединение группируется по видам блюд и их названиям. Создаются группы горячих блюд, десертов и т.д., а внутри каждой группы создаются подгруппы строк со сведениями о продуктах, относящихся к конкретному блюду группы.
5. SELECT. Каждая подгруппа строк, полученная на предыдущем шаге, преобразуется в единственную строку для результата. В нее заносится вид блюда (общий для всех подгрупп группы), название блюда (общее для всех строк подгруппы), две текстовых константы ('калорий -' и 'руб') и две суммы. Последние формируются путем суммирования тех значений дополнительных столбцов, которые принадлежат подгруппе.
6. HAVING. Сформированные строки, не удовлетворяющие условиям фразы HAVING

7. SUM(Стоимость/К_во*Вес/1000+MIN(Труд/100)) < 1.5 и

SUM(((Белки+Углев)*4.1+Жиры*9.3)*Вес/1000) < 400

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

1. ORDER BY. Результат шага 6 упорядочивается в соответствии со списком фразы ORDER BY для получения окончательного результата. Сначала строки группируются по видам блюд (в алфавитном порядке), а затем – по значению элемента данных, указанного на четвертом месте фразы SELECT, т.е. по калорийности.

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

20.Организация клиент–серверных БД. Модели технологий «клиент–сервер».

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

При технологии клиент-сервер клиентское приложение (front-end) формирует запрос к серверу БД (back-end), на котором выполняются все команды. Результаты команд посылаются затем клиенту для использования и просмотра.

Visual FoxPro, Visual Basic и Access обеспечивают средства для создания клиентских частей в приложениях клиент-сервер, которые сочетают мощность, скорость, графический интерфейс, продвинутые средства построения запросов и отчетов.

Дата добавления: 2014-01-11