Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 icon

Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1




Скачати 294.62 Kb.
НазваЛабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1
Дата26.10.2012
Розмір294.62 Kb.
ТипЛабораторная работа

Лабораторная работа 5

Функции SQL Oracle. Дополнительные фразы предложения SELECT

Содержание

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

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

2.1. Функции SQL Oracle 1

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

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

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

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

Строковые функции, которые возвращают числовые значения 6

2.1.2.3. Функции даты 7

2.1.2.4. Функции преобразования 8

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

^ 2.2. Фразы GROUP BY и HAVING 10

2.2.1. Синтаксис: 10

2.2.2. Назначение 10

2.3. Фраза ORDER BY 10

2.3.1. Синтаксис: 10

2.3.2. Назначение 10

3. Описание и примеры 11

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

3.2. Фразы GROUP BY и HAVING 12

3.3. Фраза ORDER BY 13

4. Задание 14

5. Контрольные вопросы 15


^

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


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

  • Приобрести практический опыт по использованию фраз 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

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

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

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

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

Функция

Синтаксис

Назначение

Пример

COUNT



Возвращает количество строк в запросе. Если указан expr, то возвращается количество строк, в которых expr не равно null. Можно подсчитать либо все строки (ALL), либо те, в которых значения expr являют различными (DISTINCT).

Если указана звездочка (*), то функция возвращает количество всех строк, включая и дубликаты и те, которые имеют null.

SELECT COUNT(*) AS Total

FROM TEACHER;

SELECT COUNT(post)

FROM TEACHER;

SELECT COUNT(DISTINCT post)

FROM TEACHER;



AVG



Возвращает среднее значение среди всех значений столбца n.

SELECT AVG(Salary)

FROM TEACHER

WHERE Post='professor';

MIN



Возвращает минимальное значе­ние expr

SELECT MIN(Hiredate)

FROM TEACHER;

MAX



Возвращает максимальное значе­ние expr.

SELECT MAX(Hiredate)

FROM TEACHER

WHERE Name LIKE 'А%';

SUM



Возвращает сумму значений столбца n.

SELECT SUM(Salary)

FROM TEACHER

WHERE Post='assistant';

STDEV



Возвращает стандартное откло­не­ние x,. SQL вычис­ля­ет стандартное отклонение как корень квадратный от значения, вычисленного по функции VARIANCE.

SELECT STDEV(Salary)

FROM TEACHER;

VARIANCE



Возвращает variance x,. SQL вычисляет variance х по формуле:



где:

xi - один из элементов x.
n
– количество элементов во множестве x. Если n = 1, то variance равен 0.

SELECT VARIANCE(Salary)

FROM TEACHER;
^

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


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

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

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

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

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

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

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

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


Функ­ция

Синтаксис

Назначение

Пример

ABS



Возвращает абсолютное значение n.  

SELECT ABS(-15) "Absolute"

FROM DUAL;

CEIL



Возвращает наименьшее целое, которое больше или равно n.  

SELECT CEIL(15.7) "Ceil"

FROM DUAL;

FLOOR



Возвращает нибольшее целое, которое меньше или равно n.

SELECT FLOOR(15.7) "Floor"

FROM DUAL;

SIN, COS, TAN



FUN – имя функции.

Возвращаетsin, cos или tan n (угол в радианах).

SELECT SIN(30*3.1415/180)

FROM DUAL;

SINH, COSH, TANH



FUN- имя функции.

озвращает гиперболический sin, cos или tan n.  

SELECT SINH(1) AS

"Hyperbolic sine of 1"

FROM DUAL;

EXP



Возвращает e в степени n, где e = 2.71828183 ...

SELECT EXP(4) AS

"e to the 4th power"

FROM DUAL;

LN



Возвращает натуральный логарифм n, где n больше или равно 0.

SELECT LN(95) AS

"Natural log of 95"

FROM DUAL;

LOG



Возвращает логарифм n по основанию m. Основание m может быть любым положительным числом, отличным от 0 или 1 и n могжет быть любым положительным числом.

SELECT LOG(10,100) AS

"Log base 10 of 100"

FROM DUAL;

MOD



Возвращает остаток от деления m на n. Возвращает m, если n = 0.

SELECT MOD(11,4)

FROM DUAL;

POWER



Вовращает m в степени n. Основание m и показатель степени n могут быть любыми числами, однако, если m отрицательное, то n долдно быть целым числом.

SELECT POWER(3,2)

FROM DUAL;

SIGN



Если n<0, то функция возвращает -1. Если n=0, то функция возвращает 0. Если n>0, то функция возвращает 1.

SELECT SIGN(-15) "Sign"

FROM DUAL;

SQRT



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

SELECT SQRT(26)

FROM DUAL;

ROUND



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

SELECT ROUND(15.193,1)

AS "Round"

FROM DUAL;

TRUNC



Возвращает n, отсеченное до позиции m справа от десятичной точки. Если m опщено, n отсекается до позиции 0. m может быть отрицательным. В этом случае отсечение (устанавливаются в нулевое значение) m цифр слева от десятичной точки.

SELECT TRUNC(15.79,1)

AS "Truncate"

FROM DUAL;
^

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


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

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

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

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

^ Character functions returning character values

Функ­ция

Синтаксис

Назначение

Пример

CHR



Возвращает символ, имеющий бинарный эквивалент значению n либо согласно набору символов базы данных, либо согласно национальному набору символов.  

SELECT CHR(67)

FROM DUAL;

CONCAT



Возвращает строку char1, конкате­ниро­ванную со строкой char2. Эта функция эквивалентна оператору кнокатенации строк (||).

SELECT CONCAT('AB','CD')

FROM DUAL;

INITCAP



Возвращает char, в которой первые буквы каждого слова преобразованы в прописные, все остальные буквы преобразуются в строчные. Словами считаются последовательности симво­лов, ограниченные пробелами или символами, которые не являются буквенно-цифровыми.

SELECT INITCAP('the soap')

FROM DUAL;

LOWER



Возвращает char со всеми буквами, преобразованными в строчные. Воз­вра­ща­емое значение имеет тип аргумента char (CHAR или VARCHAR2).

SELECT LOWER('Mr. Scott')

AS "lower case"

FROM DUAL;

UPPER



Возвращает char со всеми буквами, преобразованными в прописные. Воз­вра­ща­емое значение имеет тип аргумента char (CHAR или VARCHAR2).

SELECT UPPER('Mr. Scott')

AS "UPPER CASE"

FROM DUAL;

LPAD



Возвращает char1, дополненную слева до длины n последовательностью символов из char2; char2 по умолчанию равно единственному пробелу. Если char1 длиннее n, то char1 усекается до n символов.

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

AS "LPAD example"

FROM DUAL;

RPAD



Возвращает char1, дополненную спра­ва до длины n последовательностью символов из char2; char2 по умолчанию равно единственному пробелу. Если char1 длиннее n, то char1 усекается до n символов.

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

AS "RPAD example"

FROM DUAL;

LTRIM



Удаляет в char те символы слева, которые присутствуют в set; set по умолчанию равен единственному пробелу. Если char является строко­вым литералом, то его следует заключить в одиночные кавычки.

SELECT LTRIM('xyxYZ','xy')

AS "LTRIM example"

FROM DUAL;

RTRIM



Удаляет в char те символы справа, которые присутствуют в set; set по умолчанию равен единственному пробелу. Если char является строко­вым литералом, то его следует заключить в одиночные кавычки.

SELECT RTRIM('xYZxy','xy')

AS "RTRIM example"

FROM DUAL;

REPLACE



Возвращает char, в которой все вхождения строки search заменяются на строку replace. Если строка опущена или равна replace null, то все вхождения строки search удаляются. Если строка search равна null, то возвращается char. Эта функция предоставляет дополнительные возможности по сравнению с функцией TRANSLATE. TRANSLATE предоставляет возможность про­из­вести посимвольную замену. REPLACE, в свою очередь, позвроляет заменить одну подстроку на другую, а также произвести удаление подстрок.

SELECT REPLACE('JACK and

JUE','j', 'BL')

AS "REPLACE example"

FROM DUAL;

SUBSTR



Возвращает подстроку строки char, начинающуюся с символа m, и содержащую n символов.

Если аргумент m = 0, то он рассматривается равным 1. Если m положительный, то нахождение началь­ного символа производится справа от char. Если m отрицательный, то Oracle начальный символ отыскивается слева от char. Если n отсутствует, SQL возвращает все символы до конца char. Если n меньше 1, возвращается null.

SELECT SUBSTR('ABCDE',2,2)

AS "SUBSTR example"

FROM DUAL;

SELECT SUBSTR('ABCDE',-4,2)

AS "SUBSTR example"

FROM DUAL;

TRANSLATE



Вовращает char, в которой все вхождения символа из from заменя­ются на соответствующий символ из to. Символы из char, которые отсут­ствуют в from, не заменяются. Аргумент from может содержать больше символов, чем to. В этом случае дополнительные символы в конце from не имеют соответствующих символов в to. Если такие дополнительные символы появляются в char, то они удаляются. Нельзя использовать пустую строку to для удаления всех символов из char, которые содержатся в from.

SELECT TRANSLATE('ABCDE',

'ABCD', '1234')

AS "TRANSLATE example"

FROM DUAL;


^

Строковые функции, которые возвращают числовые значения


Функ­ция

Синтаксис

Назначение

Пример

INSTR



Производит поиск в строке char1, начиная с позиции n, m-го вхождения строки char2 и возвращает позицию символа в char1, в которой такое вхождение первого символа из char2 найдено. Если n отрицательный, Oracle производит поиск с конца строки char1. Значение m должно быть положительным. По умлочанию

SELECT INSTR('CORPORATE
FLOOR','OR','3','2')

FROM DUAL;

значения n и m равны 1; это означает, что Oracle производит поиск, начиная с первого символа char1 и отыскивает первое вхождение строки char2. Возвращаемое значение вычисляется относительно начала строки char1 не зависимо от значения n и выражается в количестве символов. Если поиск оказался безуспешным (то есть char2 не присутствует m раз после n-го символа char1) то возвращается значение 0.

LENGTH



Возвращает длину char в количестве символов. Если char имеет тип CHAR, то в длину включаются все расположенные справа пробелы. Если char равно null, то функция возвращает null.

SELECT INSTR('CORPORATE
FLOOR','OR','3','2')

FROM DUAL;

ASCII



Возвращает десятичное представ­ление первого символа из char в наборе символов базы данных.

SELECT ASCII('Q')

FROM DUAL;
^

2.1.2.3.Функции даты


Функции даты оперируют со значениями типа DATE. Все функции даты возвращают значение типа DATE, за исключением MONTHS_BETWEEN, которая возвращает число.

Функ­ция

Синтаксис

Назначение

Пример

ADD_
MONTHS



Возвращает дату d плюс n месяцев. Аргумент n может быть любым целым числом. Если d последний день месяца или если результирующий месяц содержит меньше дней, чем компонента дня в d, то результат будет содержать последний день результирующего месяца. В противном случае результат будет иметь ту же компоненту дня, что и d.  

SELECT TO_CHAR(
ADD_MONTHS(Hiredate,1),
'DD-MON-YYYY')

FROM TEACHER

WHERE Name = 'John';

LAST_
DAY



Возвращает дату с последним днем месяца из d. Можно использовать эту функцию для определения сколько дней осталось в текущем месяце.

SELECT SYSDATE,

LAST_DAY(SYSDATE),

LAST_DAY(SYSDATE)-SYSDATE

FROM DUAL;

NEXT_
DAY



Возвращает дату того дня недели, который указан в char и который следует за датой d. Аргумент char должен быть названием дня недели, в полном виде или сокращенном, заданным согласно языка, используе­мого в вашем сеансе работы.

SELECT NEXT_DAY(

'15-MAR-98','TUESDAY')

AS "NEXT DAY"

FROM DUAL;

MONTHS_
BETWEEN



Возвращает количество месяцев между датами d1 и d2. Если дата d1 позже, чем дата d2, то результат положителен; если раньше, то отрицателен. Если d1 и d2 содержат либо одну и ту же компоненту дня, либо указанные дни являются последними в месяце, то результат всегда целое число. В противном случае Oracle дробную часть месяцев с на основе 31-дневного месяца и с учетом разницы в компонентах времени дат d1 и d2

SELECT MONTHS_BETWEEN (

TO_DATE('28.10.2002',

'DD.MM.YYYY'),

TO_DATE('28.10.2002',

'DD.MM.YYYY'))

FROM DUAL


NEW_
TIME



Возвращает дату и время во временной зоне z2, когда дата и время в временной зоне z1 равна d. Аргументы z1 и z2 могут быть следующими текстовыми строками:

AST, ADT - Atlantic Standard or Daylight Time

BST, BTD - Bering Standard or Daylight Time

CST, CDT - Central Standard or Daylight Time

EST, EDT - Eastern Standard or Daylight Time

GMT - Greenwich Mean Time

HST, HDT - Alaska-Hawaii Standard Time or Daylight Time

MST, MDT - Mountain Standard or Daylight Time

NST - Newfoundland Standard Time

PST, PDT - Pacific Standard or Daylight Time

YST, YDT - Yukon Standard or Daylight Time

SYSDATE



Возвращает текущую дату и время. Аргументы отсутствуют. Эту функцию нельзя использовать в условии ограничения CHECK.

SELECT TO_CHAR(SYSDATE,

'DD-MM-YYYY HH24:MI:SS)'

FROM DUAL;
^

2.1.2.4.Функции преобразования


Эти функции преобразуют значение из одного типа в другой. Далее приводятся основные функции конвертирования.

Функ­ция

Синтаксис

Назначение

Пример

TO_
CHAR

(преоб­разова­ние даты)



Преобразут d типа DATE в тип VARCHAR2 в формте, определенном форматом даты fmt. Если fmt опущен, то d преобразуется в значение VARCHAR2 согласно формата даты по умолчанию. Наиболее часто используемые элементы формата fmt следующие:

- / , . ; : 'text'- знаки пунктуации и текст в кавычках представляется в результате как есть,

D – День недели (1-7)

DAY – Название дня, дополненное пробелами до 9 символов

DD – День месяца (1-31)

DDD – День года (1-366)

HH – Час дня (1-12)

HH12 – Час дня (1-12)

HH24 – Час дня (0-23)

MI - Минуты (0-59)

MM - Месяц в виде двух цифр (01-12; JAN = 01)

MON - Сокращенное название месяца

MONTH - Название месяца, дополненное до 9 символов

YYYY - Год в виде 4-х цифр

YYY,YY, Y - Последние 3, 2 или 1 цифрі года.

TO_
CHAR

(прео­бразо­вание числа)



Преобразует n типа NUMBER в значение типа VARCHAR2, с использование факультативного формата fmt. Если fmt опущен, то n преобразуется в значение типа VARCHAR2 таким образом, чтобы содержать все значащие цифры. Наиболее часто используемые элементы формата fmt следующие:
Элемент Пример Описание

, 9,999 Возвращает запятуюв указанной позиции. Можно использовать много зяпятых в формате.

. (точка) 99.99 Возвращает десятичную точку в указанной позиции.

$ $9999 Возвращает значение с знаком доллара в начале.

9 9999 Возвращает число с указанным количеством цифр с пробелом в начале, если число положительное, или минусом, если число отрицательное.

0 0999 Возвращает нули в начале.

9990 Возвращает нули в конце.

FM FM90.9 Возвращает число без пробелов к начале и конце

EEEE 9.9EEEE Возвращает значение в научной нотации.

TO_

NUMBER



Преобразует значение char типа CHAR или VARCHAR2, содержащее строковое представление числа в формате fmt, в значение типа NUMBER.

UPDATE TEACHER SET

salary = salary +

TO_NUMBER('100.00','99.9')

WHERE Name = 'John';

TO_

DATE



Преобразует char типа CHAR или VARCHAR2 в значение типа DATE. fmt – это формат даты, определяющий формат представления даты в char. Если fmt опущен, то char должен содержать строковое представление даты ы формате по умолчанию.

UPDATE TEACHER SET

Hiredate = TO_DATE(

'October 30, 2002, 11:00',

'Month dd, YYYY, HH:MI')

WHERE Name = 'John';
^

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


Приведем функции одной строки, котоые не попадают ни под одну из указанных ранее категорий.

Функция

Синтаксис

Назначение

Пример

GREATEST



Возвращает наибольшее из значений в списке exprs. Все exprs после первого неявно преобразуются к типу первого expr до выполнения сравнения. Сравнение сиволов базируется на наборе символов базы данных.

SELECT GREATEST(
'HARRY','HARRIOT','HAROLD')
AS "Greatest"

FROM DUAL;

LEAST



Возвращает наибольшее из значений в списке exprs. Все exprs после первого неявно преобразуются к типу первого expr до выполнения сравнения.

SELECT LEAST(
'HARRY','HARRIOT','HAROLD')
AS "Least"

FROM DUAL;

NVL



Если expr1 равно null, то возвращается expr2; если expr1 не null, возвращается expr1. Аргументы expr1 иexpr2 могут иметь любой тип данных. Если их типы данных разные, то SQL преобразует expr2 в тип данных expr1 прежде, чем их сравнивать.

SELECT Name,

NVL(TO_CHAR(Salary),

'NOT APPLICABLE')

FROM TEACHER;

USER



Возвращает текущего пользователя SQL с типом данных VARCHAR2

SELECT USER

FROM DUAL;
^

2.2.Фразы GROUP BY и HAVING

2.2.1.Синтаксис:



2.2.2.Назначение


Фраза GROUP BY определяет столбец или список столбцов (выражение над столбцом или список выражений над столбцами), которые используются для группирования строк таблицы. Выражения фразы GROUP BY могут содержать любые столбцы из таблиц фразы FROM, не зависимо от того, появляетсяь ли столбец во списке select. Если запрос содержит фразу GROUP BY, то список select может содержать только:

  • константы,

  • выражения, включающие только агрегатные функции,

  • выражения из фразы GROUP BY,

  • выражения, которые включают упомянутые выше выражения.

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

Назначение фразы HAVING – определить условие выбора на группах строк. Она ограничивает группы строк теми, на которых указанное условие равно TRUE..

Указывайте GROUP BY и HAVING фразы после фразы WHERE..

См. также описание синтаксиса для выражений в ПРиложении Лаб.4 и синтаксис описания условий в Приложении Лаб.2 .  
^

2.3.Фраза ORDER BY

2.3.1.Синтаксис:



2.3.2.Назначение


Фраза ORDER BY позволяет упорядочить строки результата запроса. При отсутствии этой фразы нет ни какой гарантии, что будучи выполненным дважды, запрос выдаст строки результата в одном и том же порядке.

  • expr – означает, что строки упорядочиваются согласно значения выражения expr. Выражение может базироваться на столбцах из списка select или на столбцах из таблиц фразы FROM.

  • position – упорядочивает строки на основании значений выражения в указанной позиции списка select; position должно быть целым числом.

  • c_alias – упорядочивает строки на основании значения столбца (выражения), который имеет указанный алиас в списке select.

  • ASC и DESC указывают порядок сортировки: по возрастанию или убыванию. ASC – знчение по умолчанию.

Можно указать множество выражений во фразе ORDER BY. В этом случае производится многоуровневая сортировка. Oracle располагает значения null в конце при сортировке в порядке возрастания и в начале при сортировке в порядке убывания.  

Ограничения:

  • Если вы указали фразу DISTINCT, то фраза ORDER BY должна ссылаться только на столбцы из списка select.

  • Если также задана фраза GROUP BY, то фраза ORDER BY ограничивается следующими выражениями:

- Константы

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

- Выражения, идентичные тем, что используются во фразе group by

- Выражения, включающие приведенные выше выражения, которые вычисляют одно и то же значение для всх строк в группе.
^

3.Описание и примеры

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


Агрегатные функции без фраз WHERE и GROUP BY. Если фразы WHERE и GROUP BY отсутствуют, то областью действия агрегатной функции являются все строки таблицы.

Пример. Сколько строк в таблице TEACHER:

SELECT COUNT(*) AS Total_rows_in_TEACHER_table

FROM TEACHER;

Пример. Чему равна сумма фондов всех факультетов:

SELECT SUM(Fund)

FROM FACULTY;

Пример. Какой средний фонд финансирования кафедр:

SELECT AVG(Fund)

FROM DEPARTMENT;

^ Агрегатные функции с фразой WHERE. При наличии фразы WHERE областью действия агрегатной функции являются те строки, которые удовлетворяют условию фразы WHERE.

Пример. Какова средняя ставка ассистентов, которые не имеют надбавки.

SELECT AVG(Salary)

FROM TEACHER

WHERE UPPER(Post) = 'ASSISTANT' AND Commission IS NULL;

Пример. Какой максимальный фонд среди кафедр факультета информатики?

SELECT MAX(DEPARTMENT.Fund)

FROM DEPARTMENT, FACULTY

WHERE DEPARTMENT.FacNo = FACULTY.FacNo AND UPPER(FACULTY.Name) = 'INFORMATICS';

^ Выражения в агрегатной функции. Аргументы агрегатных функций могут содержать выражения над столбцами таблицы.

Премер. Какова максимальная величина Salary+Commission?

SELECT MAX(Salary + Commission)

FROM TEACHER;

Пример. Какова разница между максимальной и минимальной ставками?

SELECT MAX(Salary) – MIN(Salary)

FROM TEACHER;

^ DISTINCT в агрегатных функциях. Ключевое слово DISTINCT означает, что в расчет следут брать только уникальные значения столбца (выражения) аргумента.

Пример. Сколько номеров телеыонов в таблице TEACHER:

SELECT COUNT(DISTINCT Tel) AS Number_of_telephones

FROM TEACHER;

Пример. Сколько должностей в таблице TEACHER:

SELECT COUNT(DISTINCT Post)

FROM TEACHER;

^ Множество агрегатных функций в списке select. Список Select может содержать множество агрегатных функций.

Example. Сколько профессоров в Вузе и какова их средняя ставка:

SELECT COUNT(*), SUM( Salary )

FROM TEACHER

WHERE UPPER(Post) = 'PROFESSOR';

Агрегатные функции с фразой GROUP BY. См. примеры фразы использования фразы GROUP BY.
^

3.2.Фразы GROUP BY и HAVING


GROUP BY и агрегатные функции в списке select.

Пример. Сколько кафедр в каждом из корпусов:

SELECT Building, COUNT(*)

FROM DEPARTMENT

GROUP BY Building;

Example. Какова сумма зарлпты (Salary+Commission) по всем имеющимся должностям:

SELECT Post, SUM(Salary + Commission)

FROM TEACHER

GROUP BY Post;

^ Группирование и фраза WHERE. Если запрос содержит фразы WHERE и GROUP BY, то фразаWHERE обрабатывается первой, а затем применяется группирование.

Пример. Для каждого корпуса подсчитать количество аудиторий вместимостью более 50:

SELECT Building, COUNT(*)

FROM ROOM

WHERE Seats > 50

GROUP BY Building;

Пример. Для корпусов 5, 7 и 12 подсчитать количество аудиторий вместимостью более 50:

SELECT Building, COUNT(*)

FROM ROOM

WHERE UPPER(Building) IN ('5', '7', '12') AND Seats > 50

GROUP BY Building;

^ Группирование по многим столбцам. Можно группировать по многим столбцам.

Пример. Для каждой недели и дня недели подсчитать количество лекций типа “lab”:

SELECT Day, Week, COUNT(*)

FROM LECTURE

WHERE UPPER(Type) = 'LAB'

GROUP BY Week, Day;

^ Группирование и соединение различных таблиц. Имеется возможность соединить две или более таблиці и зптем произвести группирование по соединенной таблице.

Пример. По каждому факультету выдать количество кафедр:

SELECT f.Name, COUNT(*)

FROM FACULTY f, DEPARTMENT d

WHERE f.FacNo = d.FacNo

GROUP BY f.Name;

Пример. По каждому факультету выдать количество профессоров:

SELECT f.Name, COUNT(*)

FROM FACULTY f, DEPARTMENT d, TEACHER t

WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(t.Post) = 'PROFESSOR'

GROUP BY f.Name;

Пример. Для каждой кафедры каждого факультета подсчитать количество профессоров:

SELECT f.Name, d.Name, COUNT(*)

FROM FACULTY f, DEPARTMENT d, TEACHER t

WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(t.Post) = 'PROFESSOR'

GROUP BY f.Name, d.Name;

^ Использование фразы HAVING. Она задает условие на группу. Фразы HAVING обычно содержат агрегатную функцию.

Example. Вывести корпуса, в которых суммарное количество мест в аудиториях меньше 1000:

SELECT Building

FROM ROOM

GROUP BY Building

HAVING SUM(Seats) < 1000;
^

3.3.Фраза ORDER BY


Она испольуется для упорядочения результатов запроса. Можно упорядочивать

  • по любому столбцу таблицы,

  • по выражению над столбцами,

  • по списку столбцов или выражений.

Упорядочение по столбцу из списка select.

Пример. Выдать назвыание факультета и его декана, упорядочив результат по факультетам:

SELECT Name, Dean

FROM FACULTY

ORDER BY Name;

^ Упорядочение по столбцу таблицы. Можно упорядочивать по столбцу таблицы, даже если он не присутствует в списке select. Эта возможность не поддерживается стандартом ANSI, но допустима в SQL Oracle.

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

SELECT Name, Salary

FROM TEACHER

ORDER BY Post;

^ Упорядочение по списку столбцов. Используя список столбцов можно произвести многоуровневое упорядочение.

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

SELECT Course, Num, Quantity

FROM SGROUP

ORDER BY Course, Num;

^ Упорядочение по выражению над столбцами. Можно упорядочивать по выражению над столбцами.

Example. Вывести имя ставку и надбавку преподавателей. Упорядочить результат по выражению Salary+Commission:

SELECT Name, Salary, Commission

FROM TEACHER

ORDER BY Salary + Commission ASC;

^ Использование порядкового номера столбца в списке select. Можно использовать порядковый номер столбца в списке select для ссылки на него во фразе ORDER BY. Это может оказаться удобным, когда список select содержит выражения.

Пример. Вывести имя преподавателя и его зарплату (Salary+Commission). Упорядочить результат по выражению Salary+Commission в порядке убывания:

SELECT Name, Salary + Commission

FROM TEACHER

ORDER BY 2 DESC;

^ Использование алиаса столбца из списка select. Вы можете воспользоваться алиасом столбца из списка select для ссылки на него во фразе ORDER BY.

Пример. Вывести имена преподавателей и даты их поступления на работу. Если дата не определена, то вывести строку “not defined”. Упорядочить результат по дате в убывающем порядке:

SELECT Name, NVL(TO_CHAR(hiredate,'yyyy-mm-dd'),'not defined') AS Teacher_hiredate

FROM TEACHER

ORDER BY Teacher_hiredate DESC;

^ Соединение таблиц и упорядочение. Если вы соединяете таблицы, то можно упорядочивать по любому столбцу соединенной таблицы.

Пример. Вывести имна преподавателей и их зарплаты факультета инфоматики. Упорядочить результат по зарплате в убывающем порядке.

SELECT t.Name, Salary + Commission

FROM FACULTY f, DEPARTMENT d, TEACHER t

WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(f.Name) = 'INFORMATICS'

ORDER BY 2 DESC;

^ Группирование и упорядочение. Для упорядочение групп необходимо удовлетворить ограничения, описанные в разделе “2.3. Фраза ORDER BY”.

Пример. Вывести количество студентов на каждом курсе. Расположить результат в порядке возрастания курса.

SELECT Course, SUM( Quantity )

FROM SGROUP

GROUP BY Course

ORDER BY Course ASC;

4.Задание


Выразите в SQL следующие запросы:

  1. Сколько студентов на факультете информатики?

  2. Сколько предметов преподается на факультете информатики?

  3. Для каждого факультета вывести его название и разницы между ее фондом и суммарными фондами финансирования всех его кафедр.

  4. Для преподавателей факультета информатики вывести их имена и количество лекций, которые они имеюю на нечетной (1) неделе.

  5. Для преподавателей факультета информатики вывести их имсна и количество предметов, которые они преподают.

  6. Для всех групп факультета информатики вывести их курс, номер и имя куратора. Если куратор отсутствует, то вывести литерал “no curator”. Упорядочить результат по курсу в порядке убывания.

  7. Для каждого предмета, преподаваемого на факультете информатики, вывести единственный столбец, который имеет следующий вид:

Subject название-предмета is taught by имя-преподавателя to group номер-группы in room номер-аудитории building номер-корпуса of week номер-недели.
Упорядочить результат по названию предмета и номеру группу.

  1. Вывести количество студентов на каждой кафедре факультета информатики. (То есть результат содержит два столбца: название кафедры и количество студентов).

  2. Вывести факультеты, фонды финансирования которых превыщает более чем на 2000 суммарный фонд финансирования всех их кафедр. Результат запроса должен содержать: название факультета, фонд факультета, суммарный фонд всех кафедр.

  3. Для всех преподавателей вывести из имена и количество преподаваемых предметов.
^

5.Контрольные вопросы


Дайте ответы на следующие вопросы:

  1. Какие типы фукций SQL вы знаете?

  2. Какая область действия агрегатных функций при наличии и отсутствии фразы GROUP BY?

  3. В каких фразах предложения SELECT могут использоваться агрегатные функции?

  4. Что означают фразы DISTINCT и ALL в агрегатных функциях?

  5. Как агрегатные функции оперируют со значениями null?

  6. Как SQL Oracle оперирует с датами?

  7. Какие выражения может содержать список select, если присутствует фраза GROUP BY?

  8. Каковы цели фраз GROUP BY и HAVING?

  9. Каковы ограничения на выражения столбцов во фразе ORDER BY, если присутствует фраза GROUP BY?

  10. Как Oracle упорядочивает значения null?





Схожі:

Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconЛабораторная работа 4 Предложение select в sql oracle. Основные возможности Содержание Цели лабораторной работы 2 Теоретические основы 2
Приобрести практический опыт по поиску данных в таблицах базы данных с использованием sql*Plus
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconЛабораторная работа 3 Манипулирование данными в sql oracle Содержание Цели лабораторной работы 1 Теоретические основы 1
Изучить возможности sql oracle по вставке, обновлению и удалению строк в таблице
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconЛабораторная работа 2 Ограничения целостности в sql oracle Содержание Цели лабораторной работы 1 Теоретические основы 1 Синтаксис 2
Изучить возможности sql oracle по описанию и поддержанию ограничений целостности
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconЛабораторная работа 6 Вложенные запросы (подзапросы) в sql oracle Содержание Цели лабораторной работы 1 Теоретические основы 1
Приобрести практический опыт по формулировке и обработке подзапросов с использованием sql*Plus
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconЛабораторная робота 1 Создание, изменение и удаление таблиц в sql oracle Содержание
Приобрести практический опыт по созданию, изменению и удалению таблиц в sql*Plus
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconТема лабораторной работы
Лабораторная работа №2 (это пример выполнения лаб работы, индивидуальное задание находится в пункте 4)
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconЛабораторная работа Использование редактора запросов sql borland sql explorer(TM) для создания запросов к данным с использованием языка sql. Требования к программному обеспечению
Лабораторная работа Использование редактора запросов sql borland sql explorer(TM) для создания запросов к данным с использованием...
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconЛабораторная работа Использование редактора запросов sql borland sql explorer(TM) для знакомства с основными объектами баз данных. Требования к программному обеспечению
...
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconМетодические указания для самостоятельной работы студентов при подготовке к лабораторной работе
Снятие спектральной характеристики уха на пороге слышимости. Лабораторная работа №3
Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 iconМетодические указания для самостоятельной работы студентов при подготовке к лабораторной работе
Изучение физических основ и методов импульсной ультразвуковой эхолокации. Лабораторная работа №4
Додайте кнопку на своєму сайті:
Документи


База даних захищена авторським правом ©zavantag.com 2000-2013
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації
Документи