Документ: Lab5R-Functions-Group-by-Having-Order-by-NEW, страницы 1-5

		Лабораторная работа 5
		Функции SQL Oracle. Дополнительные фразы предложения SELECT
		Содержание
1.	Цели лабораторной работы .................................................................................................................................		1
2.	Теоретические основы ...........................................................................................................................................		2
	2.1. Функции SQL Oracle......................................................................................................................................		2
	2.1.1. Агрегатные функции ..............................................................................................................................................		2
	2.1.2. Функции одной строки ..........................................................................................................................................		3
	2.2. Фразы GROUP BY и HAVING .....................................................................................................................		9
	2.2.1. Синтаксис:...............................................................................................................................................................		9
	2.2.2. Назначение ..............................................................................................................................................................		9
	2.3. Фраза ORDER BY .........................................................................................................................................		10
	2.3.1. Синтаксис:.............................................................................................................................................................		10
	2.3.2. Назначение ............................................................................................................................................................		10
3.	Описание и примеры.............................................................................................................................................		10
	3.1. Агрегатные функции ...................................................................................................................................		10
	3.2. Фразы GROUP BY и HAVING ...................................................................................................................		11
	3.3. Фраза ORDER BY .........................................................................................................................................		12
4.	Варианты заданий................................................................................................................................................		13
	4.1. Вариант 1........................................................................................................................................................		15
	4.2. Вариант 2........................................................................................................................................................		15
	4.3. Вариант 3........................................................................................................................................................		16
	4.4. Вариант 4........................................................................................................................................................		17
	4.5. Вариант 5........................................................................................................................................................		18
	4.6. Вариант 6........................................................................................................................................................		18
	4.7. Вариант 7........................................................................................................................................................		19
	4.8. Вариант 8........................................................................................................................................................		20
	4.9. Вариант 9........................................................................................................................................................		21
	4.10.	Вариант 10 ...........................................................................................................................................	23
	4.11.	Вариант 11 ...........................................................................................................................................	24
	4.12.	Вариант 12 ...........................................................................................................................................	25
	4.13.	Вариант 13 ...........................................................................................................................................	26
	4.14.	Вариант 14 ...........................................................................................................................................	27
	4.15.	Вариант 15 ...........................................................................................................................................	28
	4.16.	Вариант 16 ...........................................................................................................................................	29
	4.17.	Вариант 17 ...........................................................................................................................................	30
	4.18.	Вариант 18 ...........................................................................................................................................	31
5.	Контрольные вопросы..........................................................................................................................................		32

1.Цели лабораторной работы

Изучить функции SQL Oracle, а также фразы GROUP BY, HAVING, ORDER BY предложения SELECT.

1

Приобрести практический опыт по использованию фраз GROUP BY, HAVING, ORDER BY предложения SELECT с использованием SQL*Plus.

2.Теоретические основы

2.1. Функции SQL Oracle

Функции SQL являются встроенными в Oracle и доступны для использования в соответствующих фразах различных предложений SQL. Не смешивайте функции SQL с пользовательскими функциями, написанными на языке PL/SQL.

Если вы вызываете функцию со значением аргумента null, то она автоматически возвращает значение null. Единственными функциями, которые соответствуют этому правилу, являются CONCAT, DECODE, DUMP, NVL и REPLACE.

Имеется следующие две категории функций SQL:

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

Агрегат- Они возвращают одно значение на основании совокупности строк таблицы. Агрегатные ные функции могут использоваться в списке select и фразе HAVING.

функции

2.1.1.Агрегатные функции

Если вы используете фразу GROUP BY в предложении SELECT, SQL разделяет строки таблицы на группы. В запросе, содержащем фразу GROUP BY, все элементы в списке select должны использоваться либо выражения из фразы GROUP BY, либо выражения, содержащие агрегатный функции, либо константы. SQL применяет агрегатные функции в списке select к каждой группе строк и возвращает единственную результирующую строку в качестве результата для каждой группы.

Если фраза GROUP BY отсутствует, Oracle применяет агрегатную функция в списке select ко всем строкам таблицы запроса. Если агрегатная функция используется во фразе HAVING, то она используется для определения условия удаления групп строк, задаваемых фразой GROUP BY. Фразы GROUP BY и HAVING будут изучены в этой Лаб. позже.

Многие агрегатные функции используют следующие фразы:

DISTINCT	- указывает, что агрегатная функция должна принимать в расчет только различные значения из
	аргумента.
ALL	- указывает, что агрегатная функция должна принимать в расчет все значения из аргумента,
	включая и повторяющиеся. При отсутствии этих фраз по умолчанию подразумевается ALL.

Все агрегатные функции за исключением COUNT(*) игнорируют значения null.

Вы можете использовать функцию NVL в аргументе агрегатной функции для замены значения null.

Если аргумент агрегатной функции не имеет строк или все строки имеют значения null, то агрегатная функция возвращает значение null

Имеются следующие агрегатные функции:

Функция	Синтаксис		Назначение		Пример

		Возвращает количество строк в		SELECT COUNT(*) AS Total
		запросе. Если указано expr, то		SELECT COUNT(*) AS Total
		возвращается количество строк, в		FROM	TEACHER;
		которых expr (в частном случае expr		SELECT COUNT(post)
		– это столбец) не равно null. Можно		FROM	TEACHER;
COUNT		подсчитать либо все строки (ALL),		SELECT COUNT(DISTINCT post)
COUNT
		либо те,	в которых значения expr	FROM	TEACHER;
		являются различными (DISTINCT).
		Если указана звездочка (*), то
		функция	возвращает количество
		всех строк, включая и дубликаты и

2

	те, которые имеют null.

	Возвращает среднее значение среди
	всех значений выражения expr по						SELECT	AVG(Salary)
AVG	всем строкам. Значения NULL не						FROM	TEACHER
	учитываются			при	вычислении		WHERE	Post='professor';
	функции

	Возвращает минимальное значение
MIN	expr	по	всем строкам. Значения				SELECT	MIN(Hiredate)
MIN	NULL		не	учитываются		при	FROM	TEACHER;
	NULL		не	учитываются		при	FROM	TEACHER;
	вычислении функции

	Возвращает максимальное значение						SELECT	MAX(Hiredate)
MAX	expr	по	всем строкам. Значения				SELECT	MAX(Hiredate)
MAX	NULL		не	учитываются		при	FROM	TEACHER
	NULL		не	учитываются		при	FROM	TEACHER
	вычислении функции						WHERE	Name LIKE 'А%';
	вычислении функции

	Возвращает сумму значений expr по						SELECT	SUM(Salary)
SUM	всем строкам. Значения NULL не						SELECT	SUM(Salary)
SUM	учитываются			при	вычислении		FROM	TEACHER
	функции						WHERE	Post='assistant';
	функции

	Возвращает			стандартное отклоне-
	ние x,. SQL вычисляет стандартное						SELECT	STDEV(Salary)
STDEV	отклонение как корень квадратный						SELECT	STDEV(Salary)
	от значения, вычисленного по						FROM	TEACHER;
	функции VARIANCE.

	Возвращает variance x,. SQL
	вычисляет variance х по формуле:
VARIANCE							SELECT	VARIANCE(Salary)
VARIANCE							FROM	TEACHER;
	где:						FROM	TEACHER;
	где:
	xi -	один		из	элементов	x.
	n – количество элементов во
	множестве x. Если n = 1, то variance
	равен 0.

2.1.2.Функции одной строки

Имеются следующие типі функций одной строки:

числовые функции,

символьные функцииc,

функции даты,

функции преобразования,

другие функции одной строки.

Мы обсудим только наиболее часто используемые функции этих типов.

2.1.2.1.Числовые функции

Функ-	Синтаксис		Назначение				Пример
ция	Синтаксис		Назначение				Пример
ция

						SELECT	ABS(-15)
ABS		Возвращает абсолютное значение n.				"Absolute"
						FROM	DUAL;

CEIL		Возвращает	наименьшее	целое,	которое	SELECT	CEIL(15.7) "Ceil"
CEIL		больше или равно n.				FROM	DUAL;
		больше или равно n.				FROM	DUAL;

FLOOR		Возвращает	наибольшее	целое,	которое	SELECT	FLOOR(15.7)

3

		меньше или равно n.						"Floor"
								FROM	DUAL;

SIN,		Возвращает	sin,	cos или	tan n	(угол	в	SELECT SIN(30*3.1415/180)
COS,		Возвращает	sin,	cos или	tan n	(угол	в	SELECT SIN(30*3.1415/180)
COS,		радианах).						FROM	DUAL;
TAN	FUN – имя функции.	радианах).						FROM	DUAL;
TAN	FUN – имя функции.

SINH,		озвращает гиперболический sin, cos или tan						SELECT	SINH(1) AS
COSH,		озвращает гиперболический sin, cos или tan						"Hyperbolic sine of 1"
COSH,		n.						"Hyperbolic sine of 1"
TANH	FUN- имя функции.	n.						FROM	DUAL;
TANH	FUN- имя функции.							FROM	DUAL;

		Возвращает e в степени n, где						SELECT	EXP(4) AS
EXP		Возвращает e в степени n, где						"e to the 4th power"
		e = 2.71828183 ...						FROM	DUAL;
								FROM	DUAL;

LN		Возвращает натуральный логарифм n, где n						SELECT	LN(95) AS
LN		больше или равно 0.						"Natural log of 95"
		больше или равно 0.						FROM	DUAL;
								FROM	DUAL;

		Возвращает логарифм n по основанию m.
		Основание	m	может	быть	любым		SELECT	LOG(10,100) AS
LOG		положительным числом, отличным от 0 или						"Log base 10 of 100"
		1 и n могжет быть любым положительным						FROM	DUAL;
		числом.

MOD		Возвращает	остаток от деления			m на	n.	SELECT	MOD(11,4)
MOD		Возвращает m, если n = 0.						FROM	DUAL;
		Возвращает m, если n = 0.						FROM	DUAL;

		Вовращает m в степени n. Основание m и
POWER		показатель степени n могут быть любыми						SELECT	POWER(3,2)
POWER		числами, однако, если m отрицательное, то						FROM	DUAL;
		числами, однако, если m отрицательное, то						FROM	DUAL;
		n долдно быть целым числом.

		Если n<0, то функция возвращает -1. Если						SELECT	SIGN(-15) "Sign"
SIGN		n=0, то функция возвращает 0. Если n>0, то						SELECT	SIGN(-15) "Sign"
		функция возвращает 1.						FROM	DUAL;
		функция возвращает 1.

		Возвращает корень квадратный от n.
SQRT		Значение n не может быть отрицательным.						SELECT	SQRT(26)
SQRT		SQRT возвращает в качестве результата						FROM	DUAL;
		SQRT возвращает в качестве результата						FROM	DUAL;
		число типа "real".

		Возвращает n округленное до m позиции
		справа от десятичной точки. Если					m	SELECT	ROUND(15.193,1)
ROUND		опущено, n округляется до			позиции 0.		m	AS	"Round"
ROUND		может быть отрицательным для округления						AS	"Round"
		может быть отрицательным для округления						FROM	DUAL;
		до позиции слева от десятичной точки. m						FROM	DUAL;
		должно быть целым числом.

		Возвращает n, отсеченное до позиции m
		справа от десятичной точки. Если m
		опщено, n отсекается до позиции 0. m						SELECT	TRUNC(15.79,1)
TRUNC		может быть отрицательным. В этом случае						AS	"Truncate"
		отсечение (устанавливаются в нулевое						FROM	DUAL;
		значение) m цифр слева от десятичной
		точки.

2.1.2.2. Символьные функции

Имеется два типа символьный функций:

возвращающие символьное значение и

возвращающие числовое значение.

ОНи обсуждаются ниже. Некоторые из них представлены в упрощенном виде.

Character functions returning character values

4

Функ-

Синтаксис

Назначение

Пример

ция

Возвращает символ, имеющий бинарный

эквивалент значению n либо согласно

SELECT

CHR(67)

CHR

набору

символов

базы

данных,

либо

согласно

национальному

набору

FROM

DUAL;

символов.

Возвращает строку char1, конкатениро-

CONCAT

ванную

со строкой char2. Эта функция

SELECT

CONCAT('AB','CD')

эквивалентна

оператору

кнокатенации

FROM

DUAL;

строк (||).

Возвращает char, в которой первые буквы

каждого

слова

преобразованы

в

прописные,

все

остальные

буквы

INITCAP

преобразуются в строчные. Словами

SELECT INITCAP('the soap')

считаются последовательности символов,

FROM

DUAL;

ограниченные пробелами или символами,

которые

не

являются

буквенно-

цифровыми.

Возвращает char со всеми буквами,

SELECT LOWER('Mr. Scott')

LOWER

преобразованными в строчные. Возвраща-

AS "lower case"

емое значение имеет тип аргумента char

FROM

DUAL;

(CHAR или VARCHAR2).

Возвращает char со всеми буквами,

SELECT UPPER('Mr. Scott')

UPPER

преобразованными в прописные. Возвра-

AS "UPPER CASE"

щаемое

значение

имеет

тип

аргумента

FROM

DUAL;

char (CHAR или VARCHAR2).

Возвращает char1, дополненную слева до

длины

n

последовательностью

символов

SELECT LPAD('Page',8,'*.')

LPAD

из

char2; char2 по умолчанию равно

единственному пробелу. Если char1

AS "LPAD example"

FROM

DUAL;

длиннее n, то char1 усекается до n

символов.

Возвращает char1, дополненную справа

до

длины

n

последовательностью

SELECT RPAD('Page',8,'*.')

RPAD

символов из char2; char2 по умолчанию

равно единственному пробелу. Если char1

AS "RPAD example"

FROM

DUAL;

длиннее n, то char1 усекается до n

символов.

Удаляет в char те символы слева, которые

присутствуют в set; set по умолчанию

SELECT LTRIM('xyxYZ','xy')

LTRIM

равен единственному пробелу.

Если char

AS "LTRIM example"

является строковым литералом, то его

FROM

DUAL;

следует заключить в одиночные кавычки.

Удаляет в char те символы справа,

которые присутствуют в set; set по

SELECT RTRIM('xYZxy','xy')

RTRIM

умолчанию

равен

единственному

пробелу. Если char является строковым

AS "RTRIM example"

FROM

DUAL;

литералом, то его следует заключить в

одиночные кавычки.

Возвращает char, в которой все

SELECT REPLACE('JACK and

REPLACE

вхождения строки search заменяются на

JUE','j', 'BL')

строку replace. Если строка опущена или

AS "REPLACE example"

равна

replace

null, то

все вхождения

FROM

DUAL;

5

Материал: Lab5R-Functions-Group-by-Having-Order-by-NEW

Смотрите также: