Язык SQL:использование агрегатных функций и вычисляемых полей.

1.Получить общее количество поставщиков (ключевое слово COUNT):

SELECT COUNT(*) AS N

FROM P;

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

Получить общее, максимальное, минимальное и среднее количества поставляемых деталей (ключевые слова SUM, MAX, MIN, AVG):

SELECT

SUM(PD.VOLUME) AS SM,

MAX(PD.VOLUME) AS MX,

MIN(PD.VOLUME) AS MN,

AVG(PD.VOLUME) AS AV

FROM PD;

3.Для каждой детали получить суммарное поставляемое количество (ключевое слово GROUP BY …):

SELECT

PD.DNUM,

SUM(PD.VOLUME) AS SM

GROUPBYPD.DNUM;

Этот запрос будет выполняться следующим образом. Сначала строки исходной таблицы будут сгруппированы так, чтобы в каждую группу попали строки с одинаковыми значениями DNUM. Потом внутри каждой группы будет просуммировано поле VOLUME. От каждой группы в результатирующую таблицу будет включена одна строка.

4.Получить номера деталей, суммарное поставляемое количество которых превосходит 400 (ключевое слово HAVING …):

Условие, что суммарное поставляемое количество должно быть больше 400 не может быть сформулировано в разделе WHERE, т.к. в этом разделе нельзя использовать агрегатные функции. Условия, использующие агрегатные функции должны быть размещены в специальном разделе HAVING:

SELECT

PD.DNUM,

SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM

HAVING SUM(PD.VOLUME) > 400;

Язык SQL:группировкастрок

Группировка данных в операторе SELECT осуществляется с помощью ключевого слова GROUP BY и ключевого слова HAVING, с помощью которого задаются условия разбиения записей на группы.

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

 

SELECT таб1.поле1, count(таб2.поле2)

FROM таб2,таб1

WHERE таб1.поле=таб2.поле

GROUP BY таб1.поле1;

Kлючевое слово HAVING работает следующим образом: сначала GROUP BY разбивает строки на группы, затем на полученные наборы накладываются условия HAVING.

Включить в результат только те поля, данные которых оканчивается на подстроку "Press":

SELECT таб1.поле, count(таб2.поле2)

FROM таб1,таб2

WHERE таб1.поле1=таб2.поле1

GROUPBY таб1.поле

HAVING таб1.поле LIKE '%Press';

 

 

Язык SQL:соединение таблиц.

Выборка из двух таблиц путем их внутреннего соединения по столбцу КодРегиона

SELECT Поставщик.ИмяПоставщика, Регион.Город, Регион.Факс, Поставщик.КодПоставщика

FROM Регион

INNER JOIN Поставщик ON Регион.КодРегиона =

Поставщик.КодРегиона

ORDERBYПоставщик.ИмяПоставщика

 

Язык SQL:внешнее соединение таблиц.

Строки из таблицы, присоединенной внешним образом (на внешнее

соединение указывает ключевое слово OUTER) будут выбираться не

смотря на то, удовлетворяют они условиям WHERE предложения или

нет. В некоторых случаях это полезно, когда у вас есть главная

таблица и есть вспомогательная, и данные из главной таблицы вам

нужно получить в любом случае. Примервнешнегосоединения:

---------------------------------------------------------

SELECT company, order_num

FROM customer c, OUTER orders o

WHERE c.customer_num=o.customer_num

---------------------------------------------------------

Запрос находит названия компаний и номера ордеров, которые они

послали. Если же компания ордеров не присылала, то ее название

все равно будет выбрано, а номер ордера в этой строке будет ра-

вен NULL. (А если бы мы запустили запрос без параметра OUTER,

то названия этих компаний вообще бы не попали в выборку.)

 

 

Язык SQL:использование подзапросов.

Допустим нам необходимо найти сотрудников, которые получают больше чем сотрудник Smith. Решение данной задачи усложняется ввиду того, что для нахождения подобных сотрудников нам необходимо:

1. Узнать зарплату сотрудника Smith

2. Вытащить всех чья зарплата выше? чем зарплата полученная в предедущем шаге.

На языке SQL это решение будет выглядитьввиде двух запросов, при этом один из запросов будет вложен в другой запрос. Запрос находящийся внутри другого запроса называеться – подзапросом. Подзапрос возвращает значение, которое будет использоваться в основном запросе. Использования подзапросов, есть использования по очереди двух запросов, сперва получаем данные из подзапроса, а затем используем эти данные в качестве данных основного запроса.

Синтаксис и свойства подзапросов

Синтаксис простого подзапроса выглядит так:

SELECT select_list

FROM table

WHERE expr operator

(SELECT select_list

FROM table)

Здесь в качестве оператора может играть роль такие операторы сравнения как >;>=;<;<=;OR;IN при работе с запросами возравщающие ровно одно значение, а также для решения задач запросами возвращающие множество строк используйте в качестве операторов IN; ANY; ALL.

При этом сперва выполняется подзапрос (внутренней запрос), а результат операции будет передан основному запросу. Подзапрос – есть операция SELECT которая передает свое значения другому основному запросу. При этом возможности подзапроса велики, поэтому использования подзапросов открывает перед вами большие возможности. При этом возможно использовать подзапросы в следующих частях оператора SELECT:

• В разделе SELECT
• В качестве таблицы в разделе FROM
• В разделе WHERE
• В условиях HAVING
• В разделе ORDER BY

Рассмотрим список правил, которые необходимо соблюдать во время построение подзапроса:

• Подзапрос должен быть включен внутрь круглых скобок
• Подзапрос необходимо ставить правую часть от оператора сравнения
• Необходимости в упорядочения вывода подзапроса нет необходимости за исключением слючаев когда необходимо получить запроса типа Топ – n
• Используйте однострочные операторы сравнения при работе с запрос возражающий однозначение и наоборот используйте только многострочные операторы сравнения при работе с запросом возвращающим множество строк.

Типы подзапросов

Как уже вы наверное понили из описания подзапросов существует два типа подзапросов:

• Подзапросы возражающие один результат
• Подзапросы возражающие множество строк в качестве результата.

Схема подзапроса возражающей одно значение:

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

Подзапросы возражающие один результат

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

Пример запроса – вывести всех сотрудников, чьи зарплаты больше зарплаты сотрудника SMITH:

 

SELECT *

FROM emp

WHERE sal> (SELECT sal

FROM emp

WHERE ename = 'SMITH')

 

Мы рассмотрели простой пример подзапроса, немного усложним логику запроса и получим:

SELECT *

FROM emp

WHERE job = (SELECT job

FROM emp

WHERE ename = 'SMITH'

)

AND sal> (SELECT MIN (sal)

FROM emp

)

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

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

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

Рассмотрим пример, где без подзапроса в разделе HAVING не обойтись. Допустим нам необходимо вычислить какой из отделов получает самую низкую среднеарифметичускую зарплату среди всех отделов компании.

 

SELECT job, avg(sal)

FROM emp

GROUP BY job

HAVING AVG(sal) = (SELECT MIN(AVG(sal))

FROM emp

GROUP BY job)

 

 

 

Язык SQL:операторы EXISTS, ANY, ALL в командах с подзапросом.