Решение примера 3 на запросы с оператором UNION

Пример 3. В базе данных фирмы есть таблица Staff, содержащая данные о сотрудниках фирмы. В ней есть столбцы Name (фамилия), Dept (номер отдела), и Years (длительность трудового стажа).

Name	Dept	Years
Sanders	20	7
Pernal	20	8
Marenghi	38	5
Doctor	20	5
Factor	38	8

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

Решение. Первый запрос возвращает индивидуальные размеры длительности трудового стажа, упорядоченные по номерам отделов:

SELECT Name, Dept, Years FROM STAFF ORDER BY Dept

Второй запрос вернёт средний размер стажа по отделам. Условием соединения является равное число столбцов, совпадение их названий, порядка следования и типов данных. Поэтому включаем в таблицу с итогами также столбец Name с произвольным значением 'Z-AVG':

SELECT 'Z-AVG' AS Name, Dept, AVG(Years) AS Years FROM STAFF GROUP BY Dept

Теперь объединим запросы при помощи оператора UNION и применим оператору ORDER BY к результату объединения. Группировать следует по двум столбцам: номер отдела (Dept) и имя (Name), чтобы строки с итоговыми (средними) значениями, в которых значение имени - NULL, находились выше строк с индивидуальными значениями. Объединение результатов запросов будет следующим:

(SELECT Name, Dept, Years FROM STAFF) UNION (SELECT 'Z-AVG' AS Name, Dept, AVG(Years) AS Years FROM STAFF GROUP BY Dept) ORDER BY Dept, Name

Результатом выполнения запроса с оператором UNION будет следующая таблица, в которой каждая первая строка в каждой группе отделов будет содержать среднюю длительность трудового стажа сотрудников, работающих в этом отделе:

Name	Dept	Years
Doctor	20	5.0000
Pernal	20	8.0000
Sanders	20	7.0000
Z-AVG	20	6.6667
Factor	38	8.0000
Marenghi	38	5.0000
Z-AVG	38	6.5000

Тест Базы данных и язык SQL

Содержание раздела Базы данных и язык SQL