13 Операторы комбинирования результирующих таблиц в многотабличных запросах (привести примеры).

UNION, INTERSECT, EXSEPT(MINUC)

При использовании этих операторов на таблицы накладываются ограничения по соединению, т.е. д/б одинаковое количество столбцов, а в столбцах данные д/б одного типа и длины.

Пример1:

(select Area

from branch

where area is not null)

union

(select Area

from properti

where area is not null)

пример 2:

(select city

from branch)

INTERSECT

(select city

from properti)

14 Предложение 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)

fromstaff

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

GROUP BY

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

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

select position, avg(salary)

fromstaff

group byposition;

15 Условия отбора групп (предложение having)

Точно так же, как предложение WHEREиспользуется для отбора отдельных строк, участвующих в запросе, предложениеHAVINGможно применить для отбора групп строк. Его формат соответствует формату предложенияWHERE. ПредложениеHAVINGсостоит из ключевого словаHAVING, за которым следует условие отбора. Таким образом, данное предложение определяет условие отбора для групп строк. Следующий пример иллюстрирует роль предложенияHAVING:

Подсчитать количество сотрудников работающих в каждом из офисов, исключив офисы, в которых работает менее 2 человек.

select bno, count(sno)

fromstaff

group by bno;

having count(sno)>2;

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

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

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

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

17 Подзапросы: типы, примеры, правила и ограничения, ключевые слова any, all, exist, not exist

Подчиненнымназывается запрос, содержащийся в предложении WHERE или HAVING другой инструкции SQL.

Вывести список сотрудников, работающих в Минских отделениях предприятия

selectfname, lname, position

from staff

where bno in (select bno

from branch

wherecity= ‘Минск’);

правила

• таблица результатов подчиненного запроса должна состоять только из одного столбца.

• в подчиненный запрос не может входить предложение ORDER BY. Результаты подчиненного запроса используются только внутри главного запроса и для пользователя остаются невидимыми

• имена столбцов в подчиненном запросе могут являться ссылками (носят название внешних ссылок) на столбцы таблиц главного запроса.

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

EXIST/not exist

В результате проверки на существование (предикат EXISTS) можно выяснить, содержится ли в таблице результатов подчиненного запроса хотя бы одна строка. Аналогичной простой проверки не существует. Проверка на существование допустима только в подчиненных запросах.

Если подчиненный запрос не возвращает ни одной строки, проверка EXISTS возвращает значение FALSE. Эта проверка не может возвращать NULL. Можно изменить логику проверки EXISTS и использовать форму NOT EXISTS.

Any

В проверке ANY, для того чтобы сравнить проверяемое значение со столбцом данных, отобранных подчиненным запросом, используется один из шести операторов сравнения (=, !=, <, <=, >, >=). Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если любое из этих сравнений дает результат TRUE, то проверка ANY возвращает значение TRUE.

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

selectfname, lname, tel_no

fromrenter

where max_rent >= any (select rent

from property_for_rent

where type = ‘f’ and rooms=2);

ALL

В проверке ALL, как и в проверке ANY, используется один из шести операторов сравнения проверяемого значения со столбцом данных, отобранных подчиненным запросом. Проверяемое значение поочередно сравнивается с каждым элементом, содержащимся в столбце. Если все сравнениядают результат TRUE, то проверка ALL возвращает значение TRUE. Вот пример запроса с предикатом ALL:

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

selectfname, lname, tel_no

fromrenter

where max_rent >= all (select rent

from property_for_rent

where type = ‘f’ and rooms=2);