Использование SQL-функций

Цели

После освоения материала данной лекции, Вы должны быть в состоянии выполнить

SQL Функции

Функция

Арг. 1

Арг. 2

Арг. n

Выполняет действие

Результат

Типы SQL Функций

Однострочные

Многострочные

Возвращают один результат
для одной строки

Возвращают один результат
для набора

Однострочные функции (Single-Row Functions)

Особенности однострочных функций:
Обрабатывает каждую строку, возвращаемую запросом
Возвращает по

Однострочные функции (Single-Row Functions)

Преобразования
(Conversion)

Символьные
(Character)

Числовые
(Number)

Даты
(Date)

Общие
(General)

Single-row
functions

Символьные функции (Character Functions)

Функции преобразования регистра символов (Case-Manipulation Functions)

SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';

Пример использования Case-Manipulation Functions

Выбрать

Функции манипулирования символами (Character-manipulation)

SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name,

Числовые функции (Number Functions)

ROUND: Округляет значение до десятичных разрядов
TRUNC: Усекает значение

SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM DUAL;

Пример использования функции ROUND

3

3

1

2

1

2

Пример использования функции TRUNC

SELECT trunc(45.923,2) , trunc(45.923)
, trunc(45.923,-1)
FROM dual;

3

3

1

2

1

2

SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';

Пример использования функции

SELECT last_name, hire_date
FROM employees
WHERE hire_date < '01-FEB-88';

Работа с датами

База данных Oracle

Работа с датами

Функция SYSDATE возвращает:
Текущую дату
Текущее время
Функция сurrent_date возвращает:
Текущую дату
Текущее время

Arithmetic with Dates

Прибавить или вычесть число из даты
Найти разность между

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;

Использование арфиметических операций

Date Functions

Using Date Functions

Using Date Functions

Assume SYSDATE = '25-JUL-03':

Practice 3: Overview of Part 1

This practice covers the following topics:
Writing

Conversion Functions

Implicit data type
conversion

Explicit data type
conversion

Data type
conversion

Implicit Data Type Conversion

For assignments, the Oracle server can automatically convert

Implicit Data Type Conversion

Oracle Server может автоматически неявно преобразовывать типы:

Explicit Data Type Conversion

Explicit Data Type Conversion

Using the TO_CHAR Function with Dates

The format model:
Must be enclosed by

Elements of the Date Format Model

Elements of the Date Format Model

Time elements format the time portion

SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM employees;

Using the TO_CHAR

Using the TO_CHAR Function with Numbers
These are some of the format

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';

Using the TO_CHAR Function

Using the TO_NUMBER and TO_DATE Functions

Convert a character string to

Nesting Functions

Single-row functions can be nested to any level.
Nested functions are

SELECT last_name,
UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM employees
WHERE department_id = 60;

Nesting

General Functions

The following functions work with any data type and pertain

NVL Function

Converts a null value to an actual value:
Data types that

SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

Using

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM employees WHERE

SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM

Using the COALESCE Function

The advantage of the COALESCE function over the

SELECT last_name,
COALESCE(manager_id,commission_pct, -1) comm
FROM employees
ORDER BY commission_pct;

Conditional Expressions

Provide the use of IF-THEN-ELSE logic within a SQL statement
Use

CASE Expression

Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE

SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN

DECODE Function

Facilitates conditional inquiries by doing the work of a CASE

SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,

SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2,

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

Objectives

После завершения этого урока вы должны знать :
Что такое групповые функции

Что такое групповая функция?

Групповые функции работают с наборами строк, чтобы дать

Типы групповых функций

AVG
COUNT
MAX
MIN
SUM
…

SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];

Функции Группа:

SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

Использование функций AVG

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

Использование функций MIN и MAX

Вы можете использовать MAX и MIN для типов
numeric,

COUNT(*) возвращает количество строк в таблице :
COUNT(expr) возвращает количество строк с

SELECT COUNT(DISTINCT department_id)
FROM employees;

Использование DISTINCT

COUNT(DISTINCT expr) возвращает число различных ненулевых значениях

Групповые функции и значения Null

Групповые функции игнорируют столбцы со значением

Создание групп данных

EMPLOYEES

…

Average salary in
EMPLOYEES table for each
department

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

Использование предложения GROUP BY

Все столбцы

Использование предложения GROUP BY

Столбец в GROUP не обязательно должен находиться в

Группировка по нескольким столбцам

EMPLOYEES

Add the
salaries in
the EMPLOYEES
table for each

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

Использование GROUP

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

Любой столбец или выражение в списке SELECT,

Вы не можете использовать групповые функции в WHERE.
Для этой цели используйте

Ограничение результатов группировки

EMPLOYEES

…

The maximum salary per department
when it is
greater than $10,000

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Ограничение результатов

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

Использование предложения HAVING

SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

Nesting Group Functions

Отображение максимальной средней заработной платы:

SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Summary

Вы научились:
Использовать

Количество сотрудников по департаментам.
Оставить только департаменты с средней ЗП >

Employees 1. Часть сотрудников, при выполнении планов получает премию к ЗП.