Информационная система Деканата

    Ниже  приведены правила и примеры использования в пердикате специальных операторов сравнения: IN, BETWEEN, LIKE, IS NULL.                

    Оператор  IN полностью определяет множество, которому данное значение может принадлежать или не принадлежать.

    Оператор  BETWEEN задает интервал принадлежности значения столбца заданным границам.

    Оператор  LIKE используется для поиска подстрок поэтому он применим только к столбцам с символьными данными (типа CHAR или VARCHAR). С этим оператором могут использоваться шаблоны. Оператор LIKE часто используется при поиске имени или названия, полное написание которого неизвестно.

    Оператор  IS NULL используется для поиска в таблице записей со значениями в заданном столбце NULL.

    Функции агрегирования  группируют значения столбца таблицы и возвращают значение вычисленной функции. Ниже приведен список таких функций.

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

    Функция имеет следующий синтаксис:

    COUNT ([DISTINCT | ALL] < выражение >)

    При выполнении подсчета различных значений в столбце необходимо использовать слово DISTINCT.

    Особый  вариант использования функции  COUNT (*) возвращает число строк в таблице, включая дубликаты и атрибуты с неопределенными значениями.

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

    Функция имеет следующий синтаксис:

    SUM ([DISTINCT | ALL] < выражение >)

2. AVG - функция вычисления среднего значения в выбранном столбце таблицы (для числовых выражений).

    Функция имеет следующий синтаксис:

    AVG ([DISTINCT | ALL] <выражение>)

3. MAX - функция выбора наибольшего значения из всех значений выбранного столбца таблицы;

    Функция имеет следующий синтаксис:

    MAX ([DISTINCT | ALL] < выражение >)

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

4. MIN - функция выбора наименьшего значения из всех значений выбранного столбца таблицы;

    Функция имеет следующий синтаксис:

    MIN ([DISTINCT | ALL] < выражение >)

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

    Организация группировки данных для их совместной обработки выполняется  с помощью ключевых слов GROUP BY. Совместная обработка данных обычно сводится к вычислению некоторой функции: суммы, среднего значения, числа элементов множества отобранных значений и т. п. Использование ключевых слов GROUP BY приводит к тому, что оператор SELECT выдает по одной строке для каждой группы записей, формируемых на основе одинаковых значений или выражений в столбцах.

    Синтаксис конструкции группировки строк:

    GROUP BY < выражение >, [< выражение >]  ...[HAVING <условие>]

    Ключевое  слово НАVING используется для уточнения, какие группы из GROUP BY будут включаться в окончательный результат. Предложения, содержащие ключевые слова GROUP BY и НАVING, обрабатываются следующим образом:

    Условие, заданное ключевым словом HAVING, относится к сформированной условием GROUP BY группе, а не к конкретным значениям атрибута.

    Для выполнения сортировки результатов  запроса по возрастанию или убыванию используются ключевые слова ORDER ВY.

    Сортировка  задается следующей синтаксической конструкцией:

    ORDER BY < выражение > | < положение > [ASC | DESC] [,...]

    Параметр < выражение > базируется на одном  или нескольких столбцах, перечисленных  после ключевого слова SELECT через запятую. Строки с одинаковыми значениями по первому выражению упорядочиваются по второму выражению (если оно определено) и так далее.

    Параметр < положение > задает число, идентифицирующее позицию столбца в перечислении после ключевого слова SELECT, то есть вместо указания имен столбцов, можно указать номер позиции столбца в описке SELECT.

                UNION – формальное объединение результатов всех исходных запросов в виде отношения (то есть с подавлением дублированных строк );

          UNION ALL – формальное объединение результатов всех исходных запросов с сохранением дублированных строк.

          Соединение (JOIN) является одной из наиболее часто используемых и наиболее мощных операций реляционной алгебры и является противоположностью другой часто используемой операции – проецирования (PROJECT). С помощью операции JOIN устанавливаются связи между отношениями.

          Формат команды  выборки данных из нескольких связанных  таблиц имеет вид:

          SELECT <имя таблицы. имя столбца>,…,<имя таблицы. имя столбца> FROM <список имен таблиц> [WHERE <предикат>]

          После слова SELECT указывают имена столбцов таблиц с указанием префиксов имен таблиц, имена самих таблиц перечисляются после слова FROM, имена столбцов таблиц определяются друг от друга запятыми.   Операция соединения, с операндами представленными одной таблицей.

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

          SQL позволяет выполнять запросы с вложенными подзапросами. Количество вложений одного запроса в другой практически не ограничено. Обычно внутренний запрос генерирует значения, которые проверяются на предмет истинности предиката (это относится ко всем запросам за исключением коррелированных запросов). Выполнение запроса осуществляется в следующей последовательности: выполняется внутренний запрос, его результат запоминается, затем выполняется основной запрос.

          В подзапросе нельзя выполнять поиск по нескольким столбцам. Вариант “SELECT *” в подзапросе возможен только в случае применения оператора EXIST.

          Подзапросы могут  основываться на операторах отношений (=, <>, <, >, <=, >=) или операторе  IN(или IS IN).

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

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

          Любая коммерческая СУБД включает оптимизатор запросов. Оптимизатор преобразует версию запроса с соединением в версию с подзапросом. Поэтому при написании  подзапросов следует использовать заведомо более эффективный вариант.

          EXIST – оператор существования/отсутствия использует подзапрос в качестве аргумента и принимает значение “истина”, если подзапрос генерирует выходные данные, в противном случае генерирует “ложь”. Его можно применять в предикате отдельно или комбинировать с булевыми операторами.

          Оператор NOT EXISTS противоположен по назначению оператору EXISTS и принимает значение “истина”, если результирующее множество пусто.

          Операторы ANY, SOME, ALL также как и оператор EXISTS используют в качестве аргументов подзапросы.

          Операторы ANY и SOME взаимозаменяемы – выполняются одинаково.

          Оператор ALL возвращает значение “истина”, если каждое значение, выбранное при выполнении подзапроса, удовлетворяет условию внешнего запроса. 

    3.5 Создание запросов к базе данных «Деканат». 

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

    ЗадолжПредм: выводит список должников по предмету, который задается с клавиатуры.

    SELECT Предмет.название, Студент.ФИО, Студент.специальность,  Студент.год_поступления, Студент.номер_группы, Препод.ФИО

    FROM Препод INNER JOIN (Предмет INNER JOIN (Студент  INNER JOIN СдачаЭкз ON Студент.№зачетки = СдачаЭкз.№зачетки) ON Предмет.№предм = СдачаЭкз.№предм) ON Препод.№препод = Предмет.№препод

    WHERE (((Предмет.название)=[Введите название предмета]) AND ((СдачаЭкз.оценка)<3));

      

      

    ЗадолжГр: выводит список должников в группе, которая задается с клавиатуры.

    SELECT Студент.специальность, Студент.год_поступления,  Студент.номер_группы, Студент.ФИО,  Предмет.название, Препод.ФИО

    FROM Препод INNER JOIN (Предмет INNER JOIN (Студент  INNER JOIN СдачаЭкз ON Студент.№зачетки = СдачаЭкз.№зачетки) ON Предмет.№предм = СдачаЭкз.№предм) ON Препод.№препод = Предмет.№препод

    WHERE (((Студент.специальность)=[Введите cпециальность]) AND ((Студент.год_поступления)=[Введите год поступл]) AND ((Студент.номер_группы)=[Введите №группы]) AND ((СдачаЭкз.оценка)<3));

      

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

    SELECT Препод.должн, Препод.ФИО, Предмет.название, Студент.ФИО, Студент.специальность, Студент.год_поступления, Студент.номер_группы

    FROM Студент INNER JOIN (Препод INNER JOIN (Предмет  INNER JOIN СдачаЭкз ON Предмет.№предм = СдачаЭкз.№предм) ON Препод.№препод = Предмет.№препод) ON Студент.№зачетки = СдачаЭкз.№зачетки

    WHERE (((Препод.ФИО)=[Введите ФИО препод]) AND ((СдачаЭкз.оценка)<3));

      

    МедОсмотр: мед.осмотр.

    SELECT Студент.ФИО, Студент.специальность,  Студент.год_поступления, Студент.номер_группы

    FROM Студент

    WHERE (((Студент.мед_осмотр)=No));

      

    Флюрография: выводит список студентов не сдавших  флюрографию.

    SELECT Студент.ФИО, Студент.специальность,  Студент.год_поступления, Студент.номер_группы

    FROM Студент

    WHERE (((Студент.флюрогр)=No));

      

    СрБалГр: выводит средний балл указанной  группы.

    SELECT Студент.специальность, Студент.год_поступления, Студент.номер_группы, Avg(СдачаЭкз.оценка) AS [Avg-оценка]

    FROM Студент INNER JOIN СдачаЭкз ON Студент.№зачетки = СдачаЭкз.№зачетки

    GROUP BY Студент.специальность, Студент.год_поступления,  Студент.номер_группы

    HAVING (((Студент.специальность)=[Введите специальность]) AND ((Студент.год_поступления)=[Введите  год поступления]) AND ((Студент.номер_группы)=[номер  группы]));

      

    СрБалСтуд: выводит средний балл указанного студента.

    SELECT [Студент].[ФИО], [Студент].[специальность], [Студент].[год_поступления], [Студент].[номер_группы], Avg([СдачаЭкз].[оценка]) AS [Avg-оценка]

    FROM Студент INNER JOIN СдачаЭкз ON [Студент].[№зачетки]=[СдачаЭкз].[№зачетки]

    GROUP BY [Студент].[ФИО], [Студент].[специальность], [Студент].[год_поступления], [Студент].[номер_группы]

    HAVING (((Студент.ФИО)=[Введите ФИО студента]));