12. Предложение group by. Статистические (агрегативные) функции в sql (примеры).

СТАТИСТИЧЕСКИЕ (АГРЕГАТНЫЕ) ФУНКЦИИ

В SQL имеется пять статистических функций, которые позволяют получать различные виды итоговой информации:

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

- AVG() – для вычисления среднего значения столбца

- MIN() – определяет минимальное значение столбца

- MAX() – определяет максимальное значение столбца

- COUNT() – подсчитывает число всех определенных значений столбца

- COUNT(*) – подсчитывает число строк таблицы.

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

select avg(salary) as per_month, avg(12*salary) as per_year

from staff;

select count (sno)

from staff

where position = ‘менеджер’;

GROUP BY

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

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

select position, avg(salary)

from staff

group by position;

13. Сортировка результатов запроса (предложение order by).

Предложение order by сортирует результаты запроса на основании данных, содержащихся в одном или нескольких столбцах. Если это предложение не указано, результаты запроса не будут отсортированы в подчиненный запрос не может входить предложение ORDER BY. Результаты подчиненного запроса используются только внутри главного запроса и для пользователя остаются невидимыми, поэтому нет смысла их сортировать.

ASC- по возрастанию (по умолчанию)

DESC-по убыванию

14. Добавление данных (инструкции insert, merge).

INSERT

Существует 2 формы:

1. Вставка единой строки в таблицу (Insert into имя таблицы (имя столбца) values (константы)

2. Копирование одной строки в другую (Insert into имя таблицы (имя столбца) select …)

insert into staff (sno, fname, lname, address, tel_no, position, sex, salary, DOB, bno)

values (‘s122’, ‘Иван’, ‘Иванов’, ‘…’, ‘…’, null, ‘m’, null, ‘3-Mar-82’, 2);

insert into staff

values (‘s122’, ‘Иван’, ‘Иванов’, ‘…’, ‘…’, null, ‘m’, null, ‘3-Mar-82’, 2);

MERGE (INSERT+ UPDATE)

MERGE имя таблицы

Using { имя таблицы или имя представления }

ON (условия)

WHEN MATCHED THEN инструкция обновления (для UPDATE)

WHEN NOT MATCHED THEN инструкция вставки (для INSERT)

15. Модификация и удаление данных (инструкции update, merge и delete).

UPDATE

Инструкция UPDATE обновляет значения одного или нескольких столбцов в выбранных строках одной таблицы.

Увеличить заработную плату сотрудника до 300

update staff set salary = 300 where lname = ‘…’;

Перевести всех служащих из отделения № 2 в отделение № 1

update staff

set bno = 1 where bno = 2;.

update staff set salary = 1.05 * salary //// без запроса

Всем служащим отделения Минского отделения увеличить зарплату на 10%

update staff ///// с подчиненным запросом

set salary = 1.1 * salary

where bno in (select bno from branch

where city = ‘Минск’);

DELETE

Инструкция DELETE, синтаксическая диаграмма которой изображена на рисунке 12, удаляет выбранные строки из одной таблицы

delete from staff

where lname = ‘Иванов’;.

delete from staff///////удаление всех строк

Хотя в результате выполнения приведенной инструкции таблица STAFF становится пустой, из базы данных она не удаляется. Определение таблицы и ее столбцов остается в базе данных. Таблица по-прежнему существует, и в нее по-прежнему можно добавлять новые строки

MERGE (INSERT+ UPDATE)

MERGE имя таблицы

Using { имя таблицы или имя представления }

ON (условия)

WHEN MATCHED THEN инструкция обновления (для UPDATE)

WHEN NOT MATCHED THEN инструкция вставки (для INSERT)