Структуризированный язык запросов (SQL)
       

с возможностями предложения SELECT показало,


Краткое знакомство с возможностями предложения SELECT показало, что с его помощью можно реализовать все реляционные операции. Кроме того, в предложении SELECT выполняются разнообразные вычисления, агрегирование данных, их упорядочение и ряд других операций, позволяющих описать в одном предложении ту работу, для выполнения которой потребовалось бы написать несколько страниц программы на алгоритмических языках Си, Паскаль или на внутренних языках ряда распространенных СУБД.
Например, пусть требуется получить калорийность и стоимость тех блюд, для которых:

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

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

    ВидБлюдо ГорячееПомидоры с лукомкалорий -244.60.44руб ГорячееБефстрогановкалорий -321.30.53руб ГорячееДраченакалорий -333.90.33руб ГорячееКаша рисоваякалорий -339.20.27руб ГорячееОмлет с лукомкалорий -354.90.36руб ДесертЯблоки печеныекалорий -170.20.30руб

    ДесертКрем творожныйкалорий -394.30.27руб ЗакускаСалат летнийкалорий -155.50.32руб ЗакускаСалат витаминныйкалорий -217.40.37руб ЗакускаТворогкалорий -330.00.22руб ЗакускаМясо с гарниромкалорий -378.70.62руб НапитокКофе черныйкалорий -7.10.05руб НапитокКомпоткалорий -74.40.14руб НапитокКофе на молокекалорий -154.80.11руб НапитокМолочный напитоккалорий -264.90.34руб СупСуп молочныйкалорий -396.60.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;


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

    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
      исключаются из результата предыдущего шага.
    7. ORDER BY. Результат шага 6 упорядочивается в соответствии со списком фразы ORDER BY для получения окончательного результата. Сначала строки группируются по видам блюд (в алфавитном порядке), а затем – по значению элемента данных, указанного на четвертом месте фразы SELECT, т.е. по калорийности.

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

      Содержание раздела