Пример. Отобразить список книг, цена которых выше средней на книгу данного типа. (запрос Min-Roaylty)
SELECT type, title_id, price,
(SELECT ROUND(AVG(t2.price),2)
FROM title t2
WHERE t1.type =t2.type) AS AVG_Price
FROM title t1
WHERE price >
(SELECT AVG(t2.price)
FROM title t2
WHERE t1.type =t2.type)
ORDER BY type;
В примере применяется неявная группировка – вместо конструкции GROUP BY применяется условие сложного подзапроса (корреляционная переменная - type внешней таблицы).
Задание. Построить запрос, отображающий книги (по типам), продажи которых ниже продаж бестселлера данного типа.
Оператор IN используется в предложении WHERE (либо HAVING) для сравнения значения с множеством, которое может быть сформировано подзапросом, простым либо сложным. Список столбцов подзапроса состоит из одного элемента. Структура условного выражения:
выражение [NOT] IN (подзапрос)
Пример. Отобразить список издательств, публиковавших биографии. (запрос Publisher-Biography)
SELECT PUB_NAME
FROM publisher
WHERE pub_id IN
(SELECT pub_id
FROM title
WHERE type = 'biography');
Задание. Построить запрос, отображающий список авторов, публиковавшихся (либо нет) в указанном издательстве.
Пример. Отобразить сведения об авторах, публиковавших биографии. (запрос Authors-Biography)
SELECT au_id, au_fname, au_lname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM title_authors
WHERE title_id IN
(SELECT title_id
FROM title
WHERE type = 'biography'));
Запрос содержит два подзапроса. Самый вложенный формирует список книг-биографий, другой подзапрос – спиоск авторов этих кних. Внешний запрос отображает данные по авторам, принадлежащим построенному подзапросами списку.
Задание. Построить запрос, отображающий сведения о соавторах. Постройте запрос, включающий помимо сведений о соавторах информацию о написанных в соавторстве книгах.
Пример. Отобразить авторов, написавших книги без соавторов. (запрос NO_Co-Authors)
SELECT au_id, au_fname, au_lname
FROM authors AS a
WHERE 1.0 IN
(SELECT roaylty_share
FROM title_authors ta
WHERE ta.au_id = a.au_id);
Используется сложный запрос. Для каждого автора из внешней таблицы, внутренний запрос строит список значений гонораров, внешний запрос проверяет присутствие в списке значения 1.0 (нет соавтора).
Контрольное задание. Отобразить авторов, не писавших книги в соавторстве. (В примере проверяется наличие книг без соавтора, в задание – отсутствие книг с соавтором.)
Пример. Типы книг, опубликованные в нескольких издательствах. (запрос Type-Publish)
SELECT DISTINCT t1.type
FROM title t1
WHERE t1.type IN
(SELECT t2.type
FROM title t2
WHERE t1.pub_id <>t2.pub_id);
Внутренний сложный запрос строит список типов книг, внешний проверяет принадлежность типа списку. Список и проверяемое значение относятся к различным издательствам.
Примечание. Оператор IN эквивалентен конструкции ANY, выражение NOT IN – выражению <> ALL.
Примечание. Некоторые СУБД позволяет в операторе IN сравнивать несколько значений, например Oracle, PostgreSQL.
SELECT колонки
FROM таблица1
WHERE (кол1, кол2, …) IN (SELECT колА, колБ, … FROM таблица2);
Запрос сравнивает значения в соответствующих столбцах.
Ключевое слово ALL позволяет определить большее либо меньшее значение, чем все значения, полученные в результате запроса. Если использовать операторы >= или <=, можно рассчитать максимальное или минимальное значение. Запрос в операции сравнения может быть простым или сложным, должен выбирать значения из одного столбца.
Пример. Построить список авторов, которые проживают в городах, где нет издательств. (запрос City-Authors-NO_Publish)
SELECT au_id, au_fname, au_lname, city
FROM authors
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.