Введение в структурированный язык запросов - SQL

Специальные функции

SQL обеспечивает набор специальных функций для преобразований значений колонок. Список таких функций приведен в таблице 8.5.

В таблице EMPLOYEE для каждого служащего можно ввести признак пола - добавить колонку SEX типа CHAR(1) (0 - мужской, 1 - женский). Допустим, что вам нужен список служащих, в котором требуется разделение их по признаку пола с указанием его в числовом формате; тогда можно задать такую команду:

SELECT ENAME, LNAME, AGE, 'Пол:', TO_NUMBER(SEX)
FROM EMPLOYEE
ORDER BY 5;

В качестве примера использования функции DECODE приведем запрос, вычисляющий список служащих с указанием их руководителя. Если руководитель неизвестен, то выводится по умолчанию "не имеет".

SELECT ENAME, DECODE(DEPNO, 10, 'Дрягин', 20,'Жиляева', 30,'
Коротков', 'не имеет')
FROM EMPLOYEE
ORDER BY ENAME;

Предположим, что руководитель организации имеет неопределенное значение колонки DEPNO и, следовательно, для него будет работать умолчание, предусмотренное в DECODE.

Функции для обработки даты

В диалекте SQL СУБД Oracle имеется небольшой набор функций для манипулирования колонками с типом date. Список основных функций обработки даты и времени приведен в таблице 8.6.

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

SELECT ENAME, HIREDATE, HIREDATE + 92  DAYS
FROM EMPLOYEE
WHERE HIREDATE + 92 DAYS > SYSDATE
AND DEPNO=30;

Ключевое слово SYSDATE всегда возвращает текущую дату. В этом примере также показано, как используется арифметический оператор сложения с переменными типа "дата". К переменной типа "дата" можно прибавлять и вычитать из него целое число дней, месяцев, лет, часов, минут, секунд, микросекунд. Для этого используются соответствующие ключевые слова ( DAY, MONTH и т.д.), следующие за целой константой (дробная часть игнорируется, если вы указываете число с десятичной точкой). Имеется ограничение на использование скобок в таких выражениях (так, заключение в скобки выражения 1 DAYS + 1 YEARS приведет к ошибке).

Использование агрегатных функций в запросах

Агрегатные функции в SQL позволяют выбирать обобщающую информацию из группы строк и проводить систематизацию данных. Список агрегатных функций приведен в таблице 8.7. Агрегатные функции почти во всех реализациях SQL носят одинаковые имена. Различие в наименование для Oracle дано через косую черту.

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

SELECT SUM(SAL)
FROM EMPLOYEE;

вы узнаете итоговую сумму зарплаты по организации, а из запроса

SELECT AVG(SAL), STDDEV(SAL)
FROM EMPLOYEE;

- среднюю зарплату по организации и ее разброс (дисперсию).

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

Для того чтобы группировать строки таблицы по какому-либо признаку, в команде SELECT существует специальное предложение GROUP BY, которое задает колонку (или колонки) для проведения группировки. Это предложение группирует строки таблицы по значениям колонок группировки с последующим подавлением дублирующих значений в колонках группировки, т.е. позволяет определять подмножество значений некоторой колонки в терминах другой колонки и применять к полученным подмножествам функции агрегирования.

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

SELECT DEPNO, MIN(SAL), MAX(SAL)
FROM EMPLOYEE
GROUP BY DEPNO;

Предложение GROUP BY должно следовать после предложения WHERE, если последнее присутствует в команде SELECT. Каждая строка результирующей таблицы относится к одной группе строк. Число групп определяется числом различных значений в колонке группировки (в данном случае DEPNO ). Агрегатные функции применяются к каждой группе как к отдельному множеству.

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

SELECT DNAME, JOB, SUM(SAL), COUNT(*), AVG(SAL)
FROM EMPLOYEE, DEPARTAMENT
WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO
GROUP BY DNAME, JOB;

Функции SUM( ), COUNT( ), AVG( ) вычисляют суммы, число строк в группе и среднее значение в группе строк.

В SQL можно задавать условия поиска для группы строк. Для этого в команде SELECT существует предложение HAVING, которое должно следовать за предложением GROUP BY. HAVING задает условие поиска для группы строк.

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

SELECT DNAME, JOB, SUM(SAL), AVG(SAL)
FROM EMPLOYEE, DEPARTAMENT
WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO
GROUP BY DNAME, JOB
HAVING COUNT(*)>=2;

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

Таким образом, вы познакомились с различными вариантами использования команды SQL SELECT.