Оператор SELECT. Выбор данных из таблицы. Псевдонимы столбцов. Удаление повторяющихся строк, страница 13

title_id, price,

(SELECT ROUND(AVG(price),2) FROM title WHERE type= 'biography' ) AS "Средняя цена"

FROM title

WHERE type= 'biography';

Пример. Список книг и их авторов. (запрос Book-List_Author)

SELECT title_id, (SELECT au_id

FROM title_authors ta

WHERE au_order = 1

AND title_id = t.title_id) AS ["1-й автор"], (SELECT au_id

FROM title_authors ta

WHERE au_order = 2

AND title_id = t.title_id) AS ["2-й автор"], (SELECT au_id

FROM title_authors ta

WHERE au_order = 3

AND title_id = t.title_id) AS ["3-й автор"]

FROM title AS t;

Пример. Список авторов и дат последних публикаций. (запрос Authors-LastPublic-Date)

SELECT au_id,

(SELECT MAX(pubdate)

FROM title t

INNER JOIN title_authors ta

ON ta.title_id = t.title_id

WHERE ta.au_id = a.au_id) AS "Последняя публикация"

FROM authors a;

Пример. Авторы и количество публикаций. (запрос Author-NumBook)

SELECT au_id,

(SELECT COUNT(*)

FROM title_authors ta

WHERE ta.au_id = a.au_id) AS "Кол-во книг"

FROM authors a ;

Подзапросы в предложении FROM

Пример. Наибольшее количество публикаций. (запрос MAX-Book)

SELECT  MAX(ta.count_titles) AS "MAX-Book"

FROM (SELECT COUNT(*) AS count_titles

FROM title_authors

GROUP BY au_id) ta;

Внутренний запрос формирует список вычисляемых значений, как источник данных для внешнего запроса. Результат является таблицей с одним столбцом, для идентификации таблицы и столбца применяются псевдонимы TA и COUNT_TITLES соответственно.

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

Подзапрос используется в предложениях WHERE, HAVING в выражениях с оператором сравнения. Запрос может быть и простым, и сложным. Внутренний запрос содержит один столбец. Запрос должен возвращать одно значение. Это требование наиболее трудно, оно обеспечивается:

- использованием агрегатной функции в негруппированной таблице;

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

Пример. Список книг, продажи которых выше среднего. (запрос Sales-MoreAVG)

SELECT title_id, sales

FROM title

WHERE sales > (SELECT AVG(sales) FROM title);

Агрегатная функция применяется к несгруппированной таблице.

Пример. Список авторов книг, продажи которых выше среднего. (запрос Sales#Authors-MoreAVG)

SELECT ta.au_id, ta.title_id, t.sales

FROM title t

INNER JOIN title_authors ta

ON ta.title_id = t.title_id

WHERE sales > (SELECT AVG(sales) FROM title)

ORDER BY ta.au_id;

Чтобы отобразить коды авторов, кроме кодов книг выполнено объединение двух таблиц.

Внутреннее объединение можно заменить сложным запросом (запрос Sales#Authors-MoreAVG#1)

SELECT ta.au_id, ta.title_id

FROM title_authors ta

WHERE

(SELECT sales FROM title t WHERE ta.title_id = t.title_id)

>

(SELECT AVG(sales) FROM title)

ORDER BY ta.au_id;

Корреляционной переменной сложного запроса является столбец title_id внешней таблицы title_authors. Обратите внимание, что нельзя включить в предложение SELECT столбец sales из внутренней таблицы. Для этой цели следует использовать подзапрос в предложении SELECT.

Пример. Список издательств, средние продажи которых выше общих средних продаж. (запрос Sales#Publish-MoreAVG)

SELECT pub_id, AVG(sales)

FROM title

GROUP BY pub_id

HAVING AVG(sales)  > (SELECT AVG(sales) FROM title);

Подзапрос применяется в выражении HAVING.

Пример. Отобразить тех соавторов, которые имеют долю гонорара, меньшую чем максимальная. (запрос Min-Roaylty)

SELECT ta1.au_id, ta1.title_id, ta1.roaylty_share

FROM title_authors ta1

WHERE ta1.roaylty_share <1 AND (ta1.roaylty_share <

(SELECT MAX(ta2.roaylty_share)

FROM title_authors ta2

WHERE ta1.title_id = ta2.title_id));

Используется сложный подзапрос. Для каждой книги с соавторами (roaylty_share <1) подзапрос рассчитывает максимальную долю гонорара (корреляционная переменная - title_id внешней таблицы). Даные о соавторе помещаются в результат при выполнении ксловия внешнего запроса.