вар 8. ЛЕКЦИЯ 8. СОЗДАНИЕ ЗАПРОСОВ НА ГРУППИРОВКУ И СОРТИРОВКУ ДАННЫХ (. Лекция создание запросов на группировку и сортировку данных. Запросы на изменение. Использование встроенных

• Название: Лекция создание запросов на группировку и сортировку данных. Запросы на изменение. Использование встроенных
• Анкор: вар 8
• Дата: 10.09.2022
• Размер: 380.46 Kb.
• Имя файла: ЛЕКЦИЯ 8. СОЗДАНИЕ ЗАПРОСОВ НА ГРУППИРОВКУ И СОРТИРОВКУ ДАННЫХ (.pdf
• Тип: Лекция; #669933

ЛЕКЦИЯ 8. СОЗДАНИЕ ЗАПРОСОВ НА ГРУППИРОВКУ И СОРТИРОВКУ
ДАННЫХ. ЗАПРОСЫ НА ИЗМЕНЕНИЕ. ИСПОЛЬЗОВАНИЕ ВСТРОЕННЫХ
ФУНКЦИЙ.
Тема 1. Создание запросов с использованием функций
Функции SQL подобны любым другим операторам языка в том смысле, что они производят действия с данными и возвращают результат в качестве своего значения.
Функции имеют тип, который определяется типом возвращаемого значения, поэтому можно говорить о числовых, строковых, временных функциях и т. д. От обычных операторов функции отличаются форматом представления:
имя_функции[(аргумент[, аргумент]...)]
Этот формат допускает, что функции могут иметь ноль, один или более аргументов, причем при отсутствии аргументов круглые скобки не используются.
Имеется два основных класса функций SQL: встроенные и определяемые пользователем.
Встроенными являются функции, предопределенные в SQL. Ко второму классу относятся функции, которые пишутся пользователями на специальном языке, обеспечивающем использование всех возможностей SQL. Каждая СУБД использует для этого свой собственный язык.
SQL Server содержит множество встроенных функций, а также поддерживает создание определяемых пользователем функций.
В SQL определено множество встроенных функций различных категорий. На этом уроке мы рассмотрим:
–агрегатные (или групповые) функции, оперирующие значениями столбцов множества строк и возвращающие одно значение;
–функции одной строки, использующие в качестве аргументов значения столбцов одной строки и возвращающие одно значение.
Встроенные функции (Transact-SQL)
SQL Server содержит множество встроенных функций, а также поддерживает создание определяемых пользователем функций. Категории встроенных функций перечислены на этой странице.
Типы функций
Функция
Описание
Функции, возвращающие наборы строк.
Возвращают объект, который можно использовать так же, как табличные ссылки в SQL-инструкции.
Агрегатные функции
Обрабатывают коллекцию значений и возвращают одно результирующее значение.
Ранжирующие функции
Возвращают ранжирующее значение для каждой строки в секции.
Скалярная функция
(описывается далее)
Обрабатывают и возвращают одиночное значение. Скалярные функции можно применять везде, где выражение допустимо.
Скалярные функции
Категория функции
Описание
Функции конфигурации
Возвращают сведения о текущей конфигурации.
Функции преобразования
Поддержка приведения и преобразования типов данных.
Функции работы с курсорами
Возвращают сведения о курсорах.
Функции и типы данных даты и времени
Выполняют операции над исходными значениями даты и времени, возвращают строковые и числовые значения, а также значения даты и времени.

Логические функции
Выполнение логических операций.
Математические функции
Выполняют вычисления, основанные на числовых значениях, переданных функции в виде аргументов, и возвращают числовые значения.
Функции метаданных
Возвращают сведения о базах данных и объектах баз данных.
Функции безопасности
Возвращают данные о пользователях и ролях.
Строковые функции
Выполняют операции со строковым (char или varchar) исходным значением и возвращают строковое или числовое значение.
Системные функции
Выполняют операции над значениями, объектами и параметрами экземпляра SQL Server и возвращают сведения о них.
Системные статистические функции
Возвращают статистические сведения о системе.
Функции обработки текста и изображений
Выполняют операции над текстовыми или графическими исходными значениями или столбцами и возвращают сведения о
Агрегатные функции
Аргументами агрегатных функций могут быть как столбцы таблиц, так и результаты выражений над ними. Агрегатные функции и сами могут включаться в другие арифметические выражения. В стандарте SQL определены следующие виды агрегатных функций: унарные, бинарные, инверсного распределения, гипотетические функции множеств.
Мы будем рассматривать только определенные в стандарте SQL унарные агрегатные функции. Их перечень представлен в табл. 1.1. Конкретные СУБД расширяют этот список.
AVG - среднее
MIN - минимум
CHECKSUM_AGG - Возвращает контрольную сумму значений в группе. Значения
NULL не учитываются.
SUM - сумма
COUNT - количество
STDEV – среднее квадратическое отклонение
COUNT_BIG - Возвращает количество элементов в группе.
STDEVP - Возвращает статистическое стандартное отклонение всех значений в указанном выражении.
GROUPING - Указывает, является ли указанное выражение столбца в списке GROUP
BY статистическим или нет. В результирующем наборе функция GROUPING возвращает 1
(статистическое выражение) или ноль (нестатистическое выражение).
VAR - дисперсия
GROUPING_ID - Представляет собой функцию, которая вычисляет уровень группирования.
VARP - Возвращает статистическую дисперсию для заполнения всех значений в указанном выражении.
MAX - максимум
Общий формат унарной агрегатной функции следующий:
имя_функции([ALL | DISTINCT] выражение) [FILTER (WHERE условие)] где DISTINCT указывает, что функция должна рассматривать только различные значения аргумента, a ALL — все значения, включая повторяющиеся (этот вариант используется по умолчанию). Фраза FILTER позволяет дополнительно отобрать строки таблицы, столбец которой используется в качестве аргумента функции.
Агрегатные функции применяются во фразах SELECT и HAVING. Здесь мы рассмотрим их использование во фразе SELECT. В этом случае выражение в аргументе функции применяется ко всем строкам входной таблицы фразы SELECT. Кроме того, во

фразе SELECT нельзя использовать и агрегатные функции, и столбцы таблицы (или выражения с ними) при отсутствии фразы GROUP BY, которую мы рассмотрим в теме 2.
Функция COUNT
Функция COUNT имеет два формата. В первом случае возвращается количество строк входной таблицы, во втором случае — количество значений аргумента во входной таблице:
C0UNT(*)
C0UNT([DISTINCT | ALL] выражение)
Простейший способ использования этой функции - подсчет количества строк в таблице (всех или удовлетворяющих указанному условию). Для этого используется первый вариант синтаксиса.
Создайте новый запрос, введите sql-запрос, выполните его, сохраните его.
Создать текстовый отчет, в котором отобразить sql-команды разработанных запросов и скриншоты результатов работы из СУБД SQL Server Management Studio.
Запрос 1. Информация о скольких преподавателях имеется в базе данных?
SELECT COUNT(*) AS "К-во преподавателей"
FROM TEACHER;
Чтобы выполнить sql-команду нажмите на панели редактора кнопку Выполнить. В результате выполнения данного кода будет подсчитано кол-во всех преподавателей.
Например,
Самостоятельно создать запрос 2. Сколько ассистентов не имеют телефонов?
Запросы с агрегатными функциями можно строить и с использованием нескольких таблиц, так как входная таблица и в этом случае будет только одна.
Самостоятельно создать запрос 3. Сколько кафедр на факультете математики и информатики?
Во втором варианте синтаксиса функции COUNT в качестве аргумента может быть использовано имя отдельного столбца. В этом случае подсчитывается количество либо всех значений в этом столбце входной таблицы, либо только неповторяющихся (при использовании ключевого слова DISTINCT).
Запрос 4. На скольких различных должностях работают преподаватели кафедры
«Компьютерные системы и сети»?
SELECT C0UNT(DISTINCT DOLGNOST)
FROM KAFEDRA d, TEACHER t
WHERE d.KOD_KAFEDRU = t.KOD_KAFEDRU AND
LOWER(d.NAME_KAFEDRU) = 'Компьютерные системы и сети';
Функция SUM
Эта агрегатная функция подсчитывает сумму значений аргумента для всех строк входной таблицы. Аргумент должен иметь числовой тип или быть временным

промежутком. В качестве аргумента может выступать имя столбца или выражение над столбцами входной таблицы. В этой функции также допускается использовать ключевые слова DISTINCT и ALL. Приведем примеры.
Запрос 5. Какая суммарная ставка всех ассистентов?
SELECT SUM(Salary)
FROM TEACHER
WHERE LOWER(DOLGNOST) = 'ассистент';
Функция AVG
Агрегатная функция AVG подсчитывает среднее значение аргумента для всех строк входной таблицы. Аргумент должен иметь числовой тип или быть временным промежутком. В качестве аргумента может выступать имя столбца или выражение над столбцами входной таблицы. Допускается использовать ключевые слова DISTINCT и ALL.
Приведем ряд примеров.
Самостоятельно создать запрос 6. Какая средняя ставка среди всех преподавателей?
Самостоятельно создать запрос 7. Какое среднее значение ставки в вузе?
В данном запросе используйте ключевое слово DISTINCT, чтобы применить AVG не ко всем имеющимся в таблице TEACHER ставкам, а только к различным значениям ставки.
Функции MIN и MAX
Эти функции позволяют находить максимальное (МАХ) и минимальное (MIN) значения аргумента для всех строк входной таблицы. Хотя и в этом допускается использование ключевых слов DISTINCT и ALL, они не оказывают влияния на результат.
Аргумент этих функций может быть любого типа, для которого определено упорядочение, то есть числовой, строковый и временной.
Запрос 8. Какова максимальная зарплата преподавателя?
SELECT MAX(Salary + Rise)
FROM TEACHER;
Самостоятельно создать запрос 9. Когда в последний раз (максимальная дата приема на работу) принимали на работу преподавателя на кафедру информатики?
Выражения с использованием агрегатных функций
Агрегатные функции не только могут иметь выражение в своем аргументе, но и сами могут использоваться в выражениях.
Запрос 10. Вывести процентное соотношение суммарной ставки к суммарной зарплате и наоборот.
SELECT SUM(Salary)*100/SUM(Rise) AS "Процент зарплаты к зарплате",
SUM(Rise)*100/SUM(Salary) AS "Процент зарплаты к ставке"
FROM TEACHER;
Однострочные функции
Напомним, что эти функции используют в качестве аргумента одно значение (одного столбца одной строки таблицы) и возвращают в качестве своего результата также единственное значение. Мы рассмотрим эти функции по типам их аргументов.

Строковые функции
Эти функции используют в качестве аргумента строку символов и в качестве результата возвращают также символьную строку. Стандарт SQL предлагает варианты таких функций и для двоичных строк.
Функции UPPER, LOWER
Эти функции мы уже рассматривали и многократно использовали. Они имеют следующий формат:
UPPER(cтрокa)
LOWER(cтрокa)
Приведем для них примеры .
Запрос 11. Вывести фамилии всех преподавателей прописными буквами.
SELECT UPPER(NAME_TEACHER) AS "Все прописные”
FROM TEACHER;
Аналогично можно вывести все фамилии преподавателей строчными буквами.
Числовые функции над числами
Эти функции возвращают числовые значения на основании заданных в аргументе значений того же типа. Числовые функции используются для обработки данных, а также в условиях их поиска. Стандарт SQL предлагает ряд числовых функций с очевидной семантикой. Часть функций перечислены ниже:
ABS абсолютное значение
DEGREES Возвращает для значения угла в радианах соответствующее значение в градусах.
RAND – Возвращает псевдослучайное значение типа float от 0 до 1.
EXP экспонента
ROUND - Возвращает числовое значение, округленное до указанной длины или точности.
FLOOR Возвращает наибольшее целое число, меньшее или равное указанному числовому выражению.
LOG логорифм
SIN - синус
LOG10 десятичный логорифм
SQRT – корень квадратный
PI число 3.14
SQUARE – квадрат числа
POWER Возвращает значение указанного выражения, возведенное в заданную степень.
TAN - тангенс
Особой функцией является WIDTH_BUCKET, с помощью которой можно легко строить гистограммы:
WIDTH_BUCKET(число, миминум, максимум, количество)
Некоторые СУБД расширяют приведенный выше набор функций, включая другие числовые функции, например вычисления обычных и гиперболических тригонометрических функций.
Временные функции
Эти функции используют в качестве аргумента типы даты, времени, временной отметки или временного промежутка. Тип возвращаемого значения не всегда соответствует типу аргумента.

Функции даты и времени в Transact-SQL деляться на
Функции, получающие значения системной даты и времени
Функции, получающие компоненты даты и времени
Функции, получающие значения даты и времени из их компонентов
Функции, получающие разность даты и времени
Функции, изменяющие значения даты и времени
Функции, устанавливающие или получающие формат сеанса
Функции, проверяющие значения даты и времени.
Функции, получающие компоненты даты и времени.
Функция извлекает из операнда указанный компонент и возвращает его в виде числа.
DATENAME ( datepart , date )
Здесь date - это выражение временного типа, а datepart - временная единица, которая может иметь одно из следующих значений: YEAR, MONTH, DAY, HOUR, MINUTE,
SECOND и т.д.
DATEPART ( datepart,date ) - Возвращает целое число, представляющее указанный компонентdatepart указанной даты date.
DAY (date) - Возвращает целое число, представляющее день указанной даты date.
MONTH ( date ) - Возвращает целое число, представляющее месяц указанной даты date.
YEAR (date) - Возвращает целое число, представляющее год указанной даты date.
Рассмотрим пример.
Запрос 12. Вывести фамилии всех преподавателей родившихся в 1979 году.
SELECT Name_teacher, BIRTHDAY
FROM TEACHER
WHERE DATENAME(YEAR, BIRTHDAY)=1979;
Функции, получающие значения системной даты и времени
Функция CURRENT_TIMESTAMP - Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Смещение часового пояса не включается.
Эта функция возвращает текущую дату. Аргументов она не имеет.
Функция GETDATE ( ) Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Смещение часового пояса не включается.
Функция GETUTCDATE ( ) Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемые дата и время отображаются в формате UTC.
Многие СУБД существенно расширяют список функций, оперирующих датой и временем. Далее мы приведем некоторые из важных функций этого типа, которые используются в Oracle. Напоминаем, что тип DATE в Oracle содержит в себе как дату, так и время.
Функции, получающие значения даты и времени из их компонентов
Функция DATEADD (datepart, number , date ) Возвращает новое значение datetime, добавляя интервал к указанной части datepart заданной даты date.
Добавляет к дате, указанной в первом аргументе, количество месяцев второго аргумента.
Dateadd (компонент, кол-во , дата)

Здесь кол-во - это количество прибавляемых лет, месяцев, дней и т.д., а компонент - временная единица, которая может иметь одно из следующих значений: YEAR, MONTH,
DAY, HOUR, MINUTE, SECOND.
Например, DATEADD(month, 1, '2006-08-30')
Запрос 13. Осуществить пересчет даты приема на работу преподавателя на фамилию начинающуюся на букву C в сторону увеличения на 3 месяца.
SELECT NAME_TEACHER, DATA_HIRE AS ' Дата приема ',
DATEADD(month, 3, DATA_HIRE) AS ' Плюс 3 месяца '
FROM TEACHER
WHERE (NAME_TEACHER) LIKE 'С%';
Функция EOMONTH
EOMONTH (start_date [, month_to_add ])
Возвращает дату последнего дня того месяца, который указан в аргументе. Обычно используется для определения, сколько дней осталось до конца месяца.
LAST_DAY(дата)
Функция DATEDIFF
DATEDIFF ( datepart , startdate , enddate )
Возвращает количество пересеченных границ (целое число со знаком), указанных аргументом datepart, за период времени, указанный аргументами startdate и enddate.
Запрос 14. Например, если вы хотите узнать, сколько месяцев уже проработал
Статывка, можно выполнить такой запрос:
SELECT 'Статывка проработал ' ||
R0uND(DATEDIFF(month,GETDATE(), DATA_HIRE),1) ||
' месяцев' AS "Стаж Статывки"
FROM TEACHER
WHERE NAME_TEACHER LIKE 'Статыв%';
Функция NEXT_DAY
Возвращает ближайшую к первому параметру дату, в которой название дня недели совпадает с указанным во втором параметре.
NEХТ_DАУ(дата, день_недели)
Функции преобразования
Стандарт SQL предлагает единственную функцию преобразования данных из одного типа в другой — это функция CAST.
Функция CAST и CONVERT
Производит преобразование выражения, заданного первым аргументом, в тип, заданный вторым аргументом. Преобразование допускается только для определенных пар типов данных.
CAST ( expression AS data_type [ ( length ) ] )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Например
CAST(10.3496847 AS money)
CAST(10.6496 AS int)

Тема 2. Группировка и сортировка
В этом уроке мы рассмотрим еще три фразы предложения SELECT, а именно:
HAVING, GROUP BY и ORDER BY.
Первая из них позволяет группировать строки таблицы и применять к созданным группам агрегатные функции.
Рассмотрим простейшие варианты группировки. Фраза HAVING используется вместе с фразой GROUP BY и позволяет формулировать условия на группах строк для дополнительного отбора.
Наконец, фраза ORDER BY позволяет сортировать строки результирующей таблицы.
Запросы с группировкой строк
Часто при создании отчетов появляется необходимость в формировании промежуточных итоговых значений, то есть относящихся к данным не всей таблицы, а ее частей.
Именно для этого предназначена фраза GROUP BY. Она позволяет все множество строк таблицы разделить на группы по признаку равенства значений одного или нескольких столбцов (и выражений над ними).
Фраза GROUP BY должна располагаться вслед за фразой WHERE (если она отсутствует, то за фразой FROM).
Общий синтаксис фразы GROUP BY следующий:
GROUP BY выражение[, выражение]...
При наличии фразы GROUP BY фраза SELECT применяется к каждой группе, сформированной фразой группировки. В этом случае и действие агрегатных функций, указанных во фразе SELECT, будет распространяться не на всю результирующую таблицу, а только на строки в пределах каждой группы. Каждое выражение в списке фразы SELECT должно принимать единственное значение для группы, то есть оно может быть:
– константой;
– агрегатной функцией, которая оперирует всеми значениями аргумента в пределах группы и агрегирует их в одно значение (например, в сумму);
– выражением, идентичным стоящему во фразе GROUP BY;
– выражением, объединяющим приведенные выше варианты.
Рассмотрим возможности фразы GROUP BY, переходя от простых вариантов ее использования к более сложным.
Группировка по одному столбцу
Группировка по значениям одного столбца является самым простым вариантом использования фразы GROUP BY. Приведем примеры.
Запрос 15. Для каждого корпуса подсчитать количество находящихся в нем кафедр.
SELECT NUM_KORPUSA AS “Корпус”,
СOUNT(*) AS "K-вo кафедр"
FROM KAFEDRA
GROUP BY NUM_KORPUSA ;
Самостоятельно создать запрос 16. Для каждой из должностей указать суммарный фонд заработной платы.
Если в запросе используются фразы и WHERE, и GROUP BY, строки, не удовлетворяющие условию фразы WHERE, исключаются до выполнения группировки.
Вследствие этого группировка производится только по тем строкам, которые удовлетворяют условию.

В случае многотабличных запросов сначала производится соединение таблиц, а затем их группировка. Приведем примеры.
Самостоятельно создать запрос 17. Для каждого факультета, расположенного в корпусе 1, вывести количество групп и общее количество студентов по каждой кафедре.
Группировка по нескольким столбцам
SQL позволяет группировать строки таблицы и по нескольким столбцам. В этом случае имена столбцов перечисляются во фразе GROUP BY через запятую.
Запрос 18. Для каждого факультета, расположенного в корпусе 1, вывести сколько учится студентов по каждой группе.
SELECT f.Name_faculteta,
s."GROUP", count(s."GROUP") AS "Кол-во студентов в группе"
FROM FACULTET f, KAFEDRA d, STUDENT s
WHERE f.KOD_FACULTETA = d.KOD_FACULTETA AND
d.KOD_kafedru = s.KOD_kafedru AND
d.NUM_KORPUSA = '1'
GROUP BY f.Name_faculteta,s."GROUP";
Самостоятельно создать запрос 19. Для каждой кафедры и должности вывести суммарную и среднюю зарплату преподавателей.
Даже при группировке по двум и более столбцам этот вариант фразы GROUP BY обеспечивает только один уровень группировки. Так, приведенный выше запрос обеспечивает только одну итоговую строку для пары значений кафедра-должность.
Использование выражений
Хотя стандарт SQL не допускает группировку по выражениям над столбцами, некоторые СУБД такую возможность предоставляют. В этом случае во фразе SELECT также можно использовать выражение группировки, однако нельзя выводить по отдельности столбцы, участвующие в этом выражении.
Запрос 20. Для каждого значения зарплаты, не превышающего 1500, вывести это значение и количество преподавателей, такую зарплату получающих.
SELECT Salary + Rise, C0UNT(*)
FROM TEACHER
WHERE Salary + Rise <= 1500
GROUP BY Salary + Rise;
Вложение агрегатных функций
Если фраза GROUP BY в запросе отсутствует, то во фразе SELECT нельзя вкладывать агрегатные функции друг в друга. Например, следующий запрос приведет к ошибке:
SELECT AVG(MIN(Salary))
FROM TEACHER;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
00978: вложенная групповая функция без GROUP BY
Однако при наличии фразы GROUP BY такое вложение допускается. Оно интерпретируется следующим образом: сначала для каждой группы выполняется вложенная агрегатная функция, затем к полученной таким образом промежуточной таблице

применяется внешняя агрегатная функция.
Двойное вложение, например
MAX(AVG(MIN(Salary))), недопустимо. Приведем пример.
Запрос 21. Вывести среднее значение среди минимальных и максимальных ставок для каждой группы преподавателей, занимающих одну должность, а также минимальное и максимальное значения среди средних ставок.
SELECT AVG(MIN(Salary)) AS AVG_MIN,
AVG(MAX(Salary)) AS AVG_MAX,
MIN(AVG(Salary)) AS MIN_AVG,
MAX(AVG(Salary)) AS MAX_AVG
FROM TEACHER
GROUP BY Dolgnost ;
Условие отбора групп
Предположим, что нужно вывести номера кафедр, у которых суммарное количество работающих профессоров более 1. Приведенная ниже формулировка запроса является неверной:
SELECT KOD_kafedru
FROM TEACHER
WHERE count(dolgnost) > 1 and dolgnost=’профессор’
GROUP BY KOD_kafedru;
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
WHERE count(dolgnost) > 3 and dolgnost=’профессор’;
*
Ошибка в строке 3;
CRA-00934: групповая функция здесь не разрешена
Дело в том, что фраза WHERE проверяет на соответствие условию строки исходных таблиц, а мы указали в ней агрегатную функцию. Для отбора строк среди полученных групп следует применять фразу HAVING. Она играет такую же роль для групп, что и фраза
WHERE для исходных таблиц, и может использоваться лишь при наличии фразы GROUP
BY. В предложении SELECT фразы WHERE, GROUP BY и HAVING обрабатываются в следующем порядке.
Фразой WHERE отбираются строки, удовлетворяющие указанному в ней условию.
Фраза GROUP BY группирует отобранные строки.
Фразой HAVING отбираются группы, удовлетворяющие указанному в ней условию.
В связи с вышесказанным, предыдущий запрос необходимо записать так.
Перепишем тогда запрос так:
Запрос 22. Вывести номера кафедр, у которых суммарное количество работающих профессоров более 1.
SELECT KOD_kafedru as "Номер кафедры" ,Count(*) as "Кол-во профессоров на
кафедре"
FROM TEACHER
WHERE dolgnost='профессор'
GROUP BY KOD_kafedru
having count(dolgnost) > 1 ;
Использование столбцов группировки во фразе HAVING
Рассмотрим использование во фразе HAVING условий отбора, заданных для группируемых столбцов (или выражений над ними). Для этого усложним предыдущий запрос.

Запрос 23. Вывести названия кафедр факультета математики и информатики, на которых работают один и более профессоров. Указать также количество профессоров и их суммарную зарплату.
SELECT d.Name_kafedru, Count(*), SUM(t.salary + t.Rise)
FROM FACULTET f, KAFEDRA d, TEACHER t
WHERE f.KOD_FACULTETA = d.KOD_FACULTETA AND
d.KOD_kafedru = t.KOD_kafedru AND
LOWER(f.Name_faculteta) = 'математики и информатики' AND
LOWER(t.Dolgnost ) = 'профессор'
GROUP BY d.Name_kafedru
HAVING COUNT(*) > 0;
Фраза HAVING без фразы GROUP BY
Выше мы указали, что фраза HAVING может использоваться лишь при наличии фразы GROUP BY. Из этого правила синтаксис SQL допускает только одно исключение: когда вся таблица интерпретируется как одна группа. В этом случае в списке фразы
SELECT можно использовать только константы, агрегатные функции и выражения над ними. Приведем примеры.
Запрос 24. Если суммарная зарплата всех преподавателей превышает 15 000, вывести их минимальную ставку, максимальную надбавку и суммарную зарплату.
SELECT MIN(Salary), MAX(Rise), SUM(Salary + Rise)
FROM TEACHER
HAVING SUM(Salary + Rise) > 15000;
При наличии фразы WHERE сначала производится отбор строк согласно ее условию, и только после этого применяется условие фразы HAVING.
Запрос 25. Если суммарная зарплата всех ассистентов превышает 2500, вывести их среднюю ставку, среднюю надбавку и суммарную зарплату.
SELECT AVG(Salary), AVG(Rise), SUM(Salary + Rise)
FROM TEACHER
WHERE LOWER(Dolgnost ) = 'ассистент'
HAVING SUM(Salary + Rise) > 2500;
На практике фраза HAVING очень редко используется без фразы GROUP BY, из-за чего такая возможность предоставляется не во всех СУБД.
Сортировка результирующих строк
Как мы уже отмечали, строки в таблицах базы данных неупорядочены. Также неупорядочены и строки результирующей таблицы запроса, однако для их упорядочения в предложении SELECT можно воспользоваться фразой ORDER BY. Она сортирует по значению указанных в ней столбцов (и выражений над столбцами) строки результирующей таблицы запроса. Синтаксис этой фразы следующий:
ORDER BY спецификация_сортировки[. спецификация_сортировки]... где спецификация_сортировки имеет такой синтаксис:
выражение_сортировки [направление_сортировки] [положение_NULL]
Сортировать можно по столбцам (выражениям) тех типов, для которых определены операции сравнения. Это относится, в частности, к символьным строкам, числам и временным значениям. Можно указывать направление сортировки и место расположения строк, имеющих значение NULL для выражений сортировки.

Далее в этом уроке мы рассмотрим общие способы упорядочения результирующих строк.
Сортировка по столбцу или выражению
Сортировать строки результирующей таблицы запроса можно по отдельным столбцам, совокупности столбцов, а также по одному или нескольким выражениям над столбцами. Ниже рассматриваются все эти варианты.
Сортировка по столбцу
Простейший вариант сортировки - это сортировка по одному из столбцов результирующей таблицы.
Запрос 26. Вывести алфавитный список фамилий профессоров и доцентов.
SELECT NAME_TEACHER
FROM TEACHER
wheRE LOwER(Dolgnost ) =’профессор’ OR
LOWER(Dolgnost ) = 'доцент'
ORDER BY NAME_TEACHER;
Сортировка по выражению над столбцами
Упорядочивать строки можно не только по значению столбца, но и по значению выражения над столбцами.
Запрос 27. Вывести фамилии ассистентов и их зарплату по ее возрастанию.
SELECT Name_teacher, Salary + Rise
FROM TEACHER
WHERE LOWER(Dolgnost ) = 'асcистент’
ORDER BY Salary + Rise;
Направление сортировки
Во всех до сих пор приводимых примерах сортировка производилась в порядке возрастания значений. В SQL такой порядок определен по умолчанию. Однако есть возможность и явно указать направление сортировки с помощью ключевых слов ASC (по возрастанию) и DESC (по убыванию), которые следует располагать после имени сортируемого столбца (выражается).
Запрос 28. Вывести фамилии ассистентов и дату их приема на работу по возрастанию даты.
SELECT Name_teacher, Data_hire
FROM TEACHER
WHERE LOWER(Dolgnost) = 'ассистент'
ORDER BY Data_hire ASC;
Самостоятельно создать запрос 29. Вывести фамилии доцентов в обратном алфавитном порядке и их зарплату.
Тема 3. Внесение изменений в базу данных
Добавление новых данных
Новые данные добавляются оператором INSERT. Наименьшей единицей информации, которую можно добавить в реляционную базу данных, является одна строка таблицы.
Немного упрощенный синтаксис оператора INSERT имеет вид:
INSERT INTO Имя_Таблицы [(Колонка [, Колонка ...])]

{VALUES(<величина> [, <величина> ...]) | <оператор SELECT>};
<величина> = {:Переменная | <константа> | <выражение>
| <функция> | udf([<величина> [, <величина> ...]])
| NULL | USER}
<константа> = Число | 'Строка'
<функция> = CAST(<величина> AS <тип данных>)
UPPER(<величина>)
GEN_ID(Имя_Генератора, <величина>)
<выражение> = SQL выражение, возвращающее единичное значение
В этом описании можно выделить два варианта оператора:
1. Вставка одной строки. Для этого после ключевого слова VALUES в круглых скобках указывают вставляемые величины.
2. Вставка в таблицу нескольких строк, выбранных с помощью оператора SELECT * .
В этой практической работе рассматривается только первый вариант оператора
INSERT.
Пример, когда в качестве вставляемых величин применены константы:
INSERT INTO Person(Pr_ID, Pr_LastName, Pr_FirstName)
VALUES(150, 'Иванов', 'Петр');
Удаление существующих данных
Для удаления строк из таблицы используется оператор DELETE. Вот его упрощенный синтаксис:
DELETE FROM Имя_Таблицы
[WHERE <условие поиска>];
<условие поиска> = как в операторе SELECT
Если не использовать предложение WHERE, то будут удалены все строки в таблице.
-- Удаление всех служащих:
DELETE FROM Employee;
-- Удаление всех людей с номерами 150 и больше:
DELETE FROM Person WHERE Pr_ID >= 150;
Отбирать строки для удаления не обязательно только на основании содержимого этих строк. Можно составить условие для удаляемых строк, опираясь на данные из других таблиц. Для составления таких условий необходимо сначала изучить оператор SELECT.
Обновление существующих данных
Оператор UPDATE обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы. Строки для обновления указываются в предложении WHERE. Если пропустить предложение WHERE, то изменятся все строки таблицы.
UPDATE Имя_Таблицы
SET Колонка = <величина>[,
Колонка = <величина>...]
[WHERE <условие поиска>]
<величина> = { Колонка | :Переменная | <константа>
| <выражение> | <функция>
| udf([<величина> [, <величина> ...]]) | NULL | USER}
<выражение> = SQL выражение, возвращающее единичное значение
<условие поиска> = как в операторе SELECT
Примеры:
-- Увеличить зарплату всем служащим на 10%:
UPDATE Employee

SET Salary = 1.1*Salary;
/* Увеличить зарплату всем служащим, которые имеют зарплату меньше 10000 на 15%: */
UPDATE Employee
SET Salary = 1.15*Salary;
WHERE Salary <= 10000;
Отбирать строки для изменения, как и для удаления, можно с использование подчиненного запроса SELECT, который позволит учитывать в условии поиска изменяемых строк данные из других таблиц.
Например, можно выполнить такой запрос: увеличить зарплату на 10% всем служащим, работающим в отделе продаж, которые обслужили за последний месяц клиентов больше чем в полтора раза, чем в среднем по их отделу.
Добавление новых строк
Предложение INSERT вставляет строки в таблицу базы данных. Есть три разновидности этой команды:
INSERT VALUES
INSERT SELECT
INSERT DESALT VALUES 145
Первая из них производит вставку в таблицу явно заданной строки, вторая разновидность – вставку группы строк, выбранных в результате выполнения запроса, а третья — вставку значений по умолчанию.
Вставка отдельных строк
Предложение INSERT... VALUES выполняет вставку в таблицу одной строки. Его удобно использовать для небольших операций, когда в таблицу нужно вставить несколько строк. Синтаксис этого предложения следующий:
INSERT INTO имя_таблицы [<имя_стол6ца[, имя_столбца]...)]
VALUES (значение[, значение]...);
Указание вставляемых столбцов
Этот формат предполагает указания имени таблицы, в которую производится вставка, списка имен столбцов, в которые будут вставляться значения, и списка собственно вставляемых в строку значений. При этом следует придерживаться следующих правил:
–вставляемые данные должны согласовываться с типами данных указанных столбцов;
–размеры данных должны соответствовать размерам столбцов;
–порядок данных во фразе VALUES должен соответствовать порядку перечисления столбцов.
Запрос 30. Выполнить вставку одной строки в таблицу FACULTET

В таблицу вставляется строка со значениями, указанными в списке фразы VALUES, причем расположение значений в списке соответствует расположению соответствующих столбцов в списке столбцов таблицы.
Примечание. Чтобы данные были добавлены, не забудьте нажать на кнопки , а затем для подтверждения внесенных изменений в таблицу. Просмотрите внесенные изменения в таблицу FACULTET.
В этом примере мы перечислили столбцы в том порядке, в каком они были определены при создании таблицы, однако это не обязательно. При желании порядок перечисления имен столбцов в команде INSERT можно изменить.
Список имен столбцов может быть не полным. Можно указывать только те из них, значения которых известны для вставляемой строки, Столбцы, отсутствующие в списке, будут принимать значения NULL для вводимой строки.
Самостоятельно создайте запрос 31. Выполнить вставку одной строки в таблицу
KAFEDRA для столбцов name_kafedru, fio_zavkaf, kod_faculteta с данными 'Психологии',
'Иванова', 5.
В этом примере в водимой строке отсутствуют значения столбцов Nomer_komnatu,
Num_korpusa и Tel_kafedru. В базе данных они примут значение NULL.
Поддержка ограничений целостности
Помните, некоторые из столбцов или наборов столбцов могут иметь ограничения целостности PRIMARY KEY и NOT NULL. Такие столбцы не могут принимать значения
NULL.
Приведенные выше рассуждения относятся ко всем ограничениям целостности, определенным для таблиц. При попытке ввода данных (как, впрочем, и при обновлении и удалении) СУБД проверяет возможное нарушение объявленных ограничений целостности.
И если это так, команда будет отклонена с выдачей соответствующего уведомления.
Использование выражений
В качестве вставляемых значений могут использоваться выражения.
Самостоятельно создайте запрос 32. Ввести в таблицу TEACHER данные (50, 10, '
Капуста Леонид Владимирович',
1271, 1271/3, ‘доцент’, GETDATE()-1)
Здесь мы указали, что надбавка равна третьей части ставки (1271 / 3),а дата приема на работу на один день меньше текущей даты (CURRENT_DATE -1).
Результат запроса в качестве вставляемого значения
Вместо вставляемого значения можно использовать запрос. Это оказывается очень удобным в том случае, когда вставляемое значение присутствует в базе данных.
Запрос 33.Например, в следующем предложении в качестве фамилии заведующего вновь вставляемой кафедры выбирается фамилия декана факультета «Компьютерных наук и технологий» .
INSERT INTO KAFEDRA (name_kafedru, kod_faculteta, fio_zavkaf)
VALUES ( 'Философии', 5, (SELECT fio_decana FROM FACULTET
WHERE LOWER(Name_faculteta) = 'международный'));
Обновление существующих данных
Целью предложения обновления является изменение значений отдельных столбцов всех или удовлетворяющих указанному условию строк таблицы. Упрощенный синтаксис предложения следующий:

UPDATE имя_таблицы [[AS] синоним]
SET имя_столбца = выражение[, имя_столбца = выражение]...
[WHERE условие];
Его элементы означают следующее: имя_таблицы — имя обновляемой таблицы; синоним — синоним обновляемой таблицы для ссылки на нее в подзапросе; имя_столбца - имя обновляемого столбца; выражение - допустимое в SQL выражение соответствующего типа, значение которого присваивается обновляемому столбцу; условие - допустимое в SQL выражение условия, которое используется для отбора обновляемых строк.
По одному предложению UPDATE обновлению подвергаются строки только одной базовой таблицы. 147
Обновление всех строк
Как видно из определения синтаксиса команды UPDATE, фраза WHERE является факультативной. При ее отсутствии все строки таблицы подвергаются обновлению согласно фразе SET.
Во фразе SET можно одновременно изменять значения нескольких столбцов таблицы.
Запрос 34. Например, в следующем примере всем преподавателям увеличивается ставка на 12 % и надбавка на 7 %:
UPDATE TEACHER
SET Salary = Salary + Salary * 0.12, Rise = Rise + Rise * 0.08;
Во фразе SET в правой части оператора присваивания может использоваться любое допустимое в SQL выражение того же типа, что и столбец, имя которого приведено слева от оператора присваивания. Если в этом выражении используется имя столбца целевой таблицы, для вычисления выражения применяется значение этого столбца в текущей строке, которое было перед обновлением.
Примечание. Чтобы данные были добавлены, не забудьте нажать на кнопки , а затем для подтверждения внесенных изменений в таблицу. Просмотрите внесенные изменения в таблицу TEACHER.
Обновление по условию
Данный вариант использует фразу WHERE. В этом случае обновляются столбцы только тех строк таблицы, на которых выполняется условие фразы WHERE. Рассмотрим несколько примеров.
Запрос 35. Увеличить всем ассистентам зарплату и надбавку на 10 %:
UPDATE TEACHER
SET Salary = Salary * 1.1, Rise = Rise * 1.1
WHERE LOWER(Dolgnost ) = 'ассистент';
Самостоятельно создать запрос 36. Установить, что деканат юридического факультета переместился в комнату 232 8 корпуса.
Подзапросы во фразе WHERE
Во фразе WHERE можно использовать подзапросы, как мы это делали в предложении
SELECT. Это дает возможность отбирать строки для обновления на основе информации из других таблиц.

Запрос 37. Например, увеличить ставку всех преподавателей кафедры прикладной математики факультета математики и информатики в полтора раза:
UPDATE TEACHER
SET Salary = Salary * 1.5
WHERE KOD_kafedru = (SELECT KOD_kafedru FROM KAFEDRA
WHERE LOWER(Name_kafedru) = 'прикладная математика');
Подзапросы во фразе SET
До сих пор новые значения представляли собой константы или выражения с использованием значений обновляемой строки. Однако если такие значения присутствуют в других строках обновляемой таблицы или вообще в других таблицах, можно воспользоваться подзапросом. В этом случае допускается использовать две формы фразы
SET:
SET {имя_столбца | (список_инен_столбцов)} = (подзапрос)
В обоих вариантах подзапрос должен возвращать одну строку. В первом случае он также должен возвращать значение одного столбца, а во втором возвращаемая строка должна содержать столько значений, сколько столбцов приведено в списке имен столбцов.
При этом производится присвоение значений строки подзапроса соответствующим столбцам из списка слева.
Запрос 38. Установить всем ассистентам надбавку, равную 70 % текущей средней надбавки по вузу.
UPDATE TEACHER
SET Rise = (SELECT SUM(Rise) * 0.7 / COUNT(*) FROM TEACHER)
WHERE LOWER(Dolgnost ) = 'ассистент';
Удаление существующих строк
Удалять строки из таблицы можно с помощью предложения DELETE. Оно удаляет только строки целиком, а не индивидуальные значения столбцов. Синтаксис команды следующий:
DELETE FROM имя_таблицы [[AS] синонин]
[WHERE условие];
При использовании предложения DELETE вы прежде всего обнаружите, что предупреждающая подсказка, как правило, не выдается. Обычно, когда пользователь удаляет какой-либо объект операционной среды, он получает сообщение типа «Вы уверены
(Д/Н)?». В системах, поддерживающих SQL, строки удаляются без такого сообщения.
Поэтому будьте внимательны.
В зависимости от применения фразы WHERE предложение DELETE позволяет удалить отдельную строку, несколько или все строки таблицы. Строки могут быть и не удалены. При использовании предложения DELETE помните о следующем: нельзя удалить значение отдельного столбца (используйте для этого предложение
UPDATE); как и предложения INSERT и UPDATE, удаление строк может нарушить ограничения целостности; сама таблица не удаляется (используйте для этого предложение DROP TABLE).
Удаление всех строк таблицы
Чтобы удалить все содержимое таблицы, не нужно использовать фразу WHERE.
Помните, что вы удаляете не саму таблицу, а только все ее строки.

Запрос 39. Удалить содержимое таблицы Сотрудники базы данных Educator.
use Educator
DELETE FROM Cотрудники;
Удаление по условию
Обычно нужно удалять только некоторые строки из таблицы. Чтобы определить, какие строки будут удалены, нужно использовать условие во фразе WHERE. Приведем несколько примеров.
Самостоятельно создать запрос 40. Удалить сведения об ассистентах, которые были приняты на работу до 01.01.1986.
Удаление одной строки
Чтобы удалить одну конкретную строку, нужно сформулировать условие таким образом, чтобы оно идентифицировало эту единственную строку. Обычно для этого в условии используются первичный ключ таблицы или уникальный набор столбцов.
Самостоятельно создать запрос 41. Удалить всех преподавателей под фамилией
Швец.