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

ORDER BY t.type, t.pub_id;

Пример. Отобразить список книг, изданных в соавторстве, с указанием количества авторов. (запрос Title#SUM-Authors)

SELECT

ta.title_id,

COUNT(ta.title_id) AS "Кол-во авторов"

FROM title AS t

INNER JOIN title_authors AS ta

ON t.title_id=ta.title_id

GROUP BY ta.title_id

HAVING COUNT(ta.title_id) > 1

ORDER BY ta.title_id;

Задание. Преобразовать запрос так, чтобы отображались названия книг.

Пример. Отобразить список книг, доход от которых более чем в 10 раз превышает аванс. (запрос Title#SUM-Authors)

SELECT

t.title_name, r.advance,

t.price*t.sales AS ["Доход"],

r.advance/(t.price*t.sales)*100 AS "Доля аванса"

FROM title AS t, roaylties AS r

WHERE t.title_id=r.title_id And t.price*t.sales>r.advance*10

ORDER BY t.price*t.sales DESC;

В синтаксисе JOIN

SELECT

t.title_name, r.advance,

t.price*t.sales AS ["Доход"],

r.advance/(t.price*t.sales)*100 AS "Доля аванса"

FROM title AS t

INNER JOIN roaylties AS r

ON t.title_id=r.title_id And t.price*t.sales>r.advance*10

ORDER BY t.price*t.sales DESC;

В конструкцию ON входит более сложное условие, чем равенство элементов столбцов, по которым выполняется объединение. Оператор неравенства редко встречается при объединении, обычно при самообъединении.

Контрольное 3адание 2. Отобразить список авторов и названия книг, которые написаны в соавторстве.

Пример. Отобразить список авторов, названия книг и издательства. (запрос Author-Book-Publisher)

SELECT

a.au_fname, a.au_lname, t.title_name, p.pub_name

FROM authors AS a INNER JOIN

(title_authors AS ta INNER JOIN

(title AS t INNER JOIN publisher AS p ON p.pub_id = t.pub_id)

ON t.title_id = ta.title_id)

ON a.au_id = ta.au_id ;

Для лучшего понимания можно проанализировать три запроса, которые получены путем последовательного объединения таблиц: Запрос1, Запрос2, Запрос3.

Другой вариант объединения таблиц (объединение по 2 таблицы, потом объединение результатов):

SELECT

a.au_fname, a.au_lname, t.title_name, p.pub_name

FROM (publisher AS p INNER JOIN title AS t ON p.pub_id=t.pub_id)

INNER JOIN (authors AS a INNER JOIN title_authors AS ta ON a.au_id=ta.au_id) ON t.title_id=ta.title_id

ORDER BY t.title_name;

Пример. Вычисление общих выплат: гонорар, аванс, оставшиеся выплаты. (запрос Total_Pay)

SELECT

SUM(t.sales * t.price * r.roaylty_rate) AS ["Общий гонорар"],

SUM(r.advance) AS ["Общий аванс"],

SUM(t.sales * t.price * r.roaylty_rate - r.advance) AS ["Остаток гонорара"]

FROM title AS t INNER JOIN roaylties AS r ON r.title_id = t.title_id

WHERE t.sales IS NOT NULL;

Пример. Выплаты авторам за книгу (с учетом соавторства). (запрос Author-Book-Pay)

SELECT a.au_fname, a.au_lname, t.title_name,

r.roaylty_rate*t.sales*t.price*ta.roaylty_share AS ["Гонорар Ав"],

r.advance*ta.roaylty_share AS ["Аванс Ав"]

FROM authors AS a INNER JOIN

(title_authors AS ta INNER JOIN

(title AS t INNER JOIN roaylties AS r ON r.title_id=t.title_id)

ON t.title_id=ta.title_id)

ON a.au_id=ta.au_id

WHERE t.sales IS NOT NULL

ORDER BY a.au_id;

Пример. Выплаты авторам. (запрос Author-GROUP-Pay)

SELECT a.au_id,

COUNT(t.sales) AS ["Кол-во книг"],

SUM(r.roaylty_rate*t.sales*t.price*ta.roaylty_share) AS ["Гонорар Ав"],

SUM(r.advance*ta.roaylty_share) AS ["Аванс Ав"]

FROM authors AS a INNER JOIN

(title_authors AS ta INNER JOIN

(title AS t INNER JOIN roaylties AS r ON r.title_id=t.title_id)

ON t.title_id=ta.title_id)

ON a.au_id=ta.au_id

WHERE t.sales IS NOT NULL

GROUP BY a.au_id

ORDER BY a.au_id;

Группирование проводилось по идентификатору автора.

Задание. Модифицируйте запрос:

1. Выплаты авторам за некоторый период.