[GROUP BY выражение1 [, выражение2... ] [HAVING условие] ]
[{UNION | UNION ALL | INTERSECT | MINUS} SELECT оператор ]
[ORDER BY выражение1 [ASC | DESC]
[, выражение2 [ASC | DESC]] ...];
Вся извлеченная информация выводится в окно интерактивного редактора SQL*PLUS.
Построение списка выбора.В операторе SELECT при формировании списка выбора, состоящего из имен столбцов и выражений, для построения выражений могут использоваться имена столбцов таблиц и представлений, литералы, функции, соединяемые знаками арифметических действий. Если необходимо указать имена столбцов, которые имеют одинаковые идентификаторы в двух разных таблицах, то к этим именам через точку необходимо приписать имя таблицы. При необходимости сложному выражению можно присвоить псевдоним, который будет использован в дальнейшем тексте оператора. Все имена таблиц, имена столбцов которых использовались для построения выражений в списке выбора, обязательно должны быть перечислены в тексте оператора после ключевого слова FROM.
Упорядочение строк. Строки, возвращаемые SELECT-запросом, могут быть упорядочены по возрастанию или убыванию значений определенных выражений. В качестве выражения может использоваться имя столбца. Для реализации этой процедуры используется конструкция ORDER BY, в которой указывается перечень, состоящий из одного или нескольких выражений, разделенных запятыми, по значениям которых и осуществляется упорядочение. По умолчанию извлекаемые строки упорядочиваются по возрастанию значений указанных в перечне выражений. Для задания другого варианта упорядочения строк после каждого выражения или группы выражений в перечне указывается либо ASC (по возрастанию) либо DESC (по убыванию значений), а перечисление этих групп осуществляется через запятую.
Условие выбора строк.В конструкции WHERE при построении условия, которому должны удовлетворять выбираемые строки, можно использовать весь имеющийся в языке SQL набор операций сравнения и логических операций. Для задания конкретного значения в условии можно воспользоваться переменной подстановки, которая позволяет ввести необходимое значение в момент выполнения запроса. Переменная подстановки определяется наличием символа & в начале ее имени.
Подзапросы или вложенные запросы применяются для возврата группы строк или множества значений, которые будут использованы родительским запросом. В зависимости от формы построения он может выполняться либо один раз для родительского запроса, либо один раз для каждой строки, извлеченной родительским запросом. В последнем случае такой подзапрос называется коррелированным подзапросом. Характерным признаком коррелированного подзапроса является наличие в его фразе WHERE ссылок на столбцы родительского запроса.
Объединение строк.В многокомпонентном запросе можно определенным образом объединить в единое целое группы строк, извлекаемые отдельно выполняемыми запросами. Для задания порядка объединения используется одно из ключевых слов конструкции UNION | UNION ALL | INTERSECT | MINUS.
Примеры.
1. Выбрать из таблицы KNIGA всю информацию о книгах:
SELECT * FROM KNIGA;
2. Выбрать из таблицы KNIGA информацию о первых трех книгах:
SELECT * FROM KNIGA WHERE ROWNUM < 4;
3. Выбрать из таблицы KNIGA информацию о книгах с указанием фамилии автора, названия и цены и упорядочить ее по возрастанию значений столбцов АВТОР, НАЗВАНИЕ и убыванию значений столбца ЦЕНА; фамилии авторов и названия вывести заглавными буквами:
SELECT UPPER(АВТОР), UPPER(НАЗВАНИЕ), ЦЕНА FROM KNIGA ORDER BY АВТОР, НАЗВАНИЕ ASC, ЦЕНА DESC;
4. Выбрать из таблицы KNIGA информацию (фамилия автора, название) о книгах в жанре “Романн”;
SELECT АВТОР, НАЗВАНИЕ FROM KNIGA WHERE ЖАНР = 'Роман';
5. Выбрать из таблицы KNIGA информацию (фамилия автора, название, жанр) о книгах в жанре не “Романн”;
а) SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA WHERE ЖАНР != 'Роман';
б) SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA WHERE ЖАНР <> 'Роман';
в) SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA
MINUS
SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA
WHERE ЖАНР = 'Роман';
6. Выбрать из таблицы KNIGA информацию (фамилия автора, название, цена) о книгах стоимостью больше 260 и меньше 1000;
SELECT АВТОР, НАЗВАНИЕ, ЦЕНА FROM KNIGA WHERE ЦЕНА BETWEEN 260 AND 1000;
7. Выбрать из таблицы KNIGA информацию (фамилия автора, название, жанр) о книгах в жанрах “Роман” и “Детектив”:
а) SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA
WHERE ЖАНР = 'Роман' OR ЖАНР = 'Детектив';
б) SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA
WHERE ЖАНР IN ('Роман', 'Детектив');
в) SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA
WHERE ЖАНР = 'Роман'
UNION
SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA
WHERE ЖАНР = 'Детектив';
8. Выбрать из таблицы KNIGA информацию (фамилия автора, название, жанр) о книгах жанра “Роман”, “Детектив” издательства “Аст”:
а) SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA WHERE (ЖАНР = 'Роман' OR ЖАНР = 'Детектив') AND ИЗДАТЕЛЬСТВО = 'Аст';
б) SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA WHERE ЖАНР IN ('Роман', 'Детектив') AND ИЗДАТЕЛЬСТВО = 'Аст';
9. Выбрать из таблицы KNIGA информацию (фамилия автора, название) о книгах, название которых начинается со слова “Гибель”:
а) SELECT АВТОР, НАЗВАНИЕ FROM KNIGA WHERE НАЗВАНИЕ LIKE 'Гибель%';
б) SELECT АВТОР, НАЗВАНИЕ FROM KNIGA WHERE SUBSTR(НАЗВАНИЕ, 1, 6) = 'Гибель';
10. Выбрать из таблицы KNIGA информацию (фамилия автора, название, жанр) о книгах, относящихся к указанному жанру. Необходимое значение задать, используя переменную подстановки:
SELECT АВТОР, НАЗВАНИЕ, ЖАНР FROM KNIGA
WHERE ЖАНР = '&GANR' ORDER BY АВТОР;
В ответ на запрос, выдаваемый системой, набрать одно из значений столбца ЖАНР (Роман, Фантастика, Детектив).
11. Выбрать из таблицы KNIGA информацию о книгах, имеющихся в других издательствах, того же жанра, что и в издательстве “Аст”:
а) SELECT * FROM KNIGA WHERE ЖАНР IN
(SELECT DISTINCT ЖАНР FROM KNIGA WHERE
ИЗДАТЕЛЬСТВО= 'Аст') AND ИЗДАТЕЛЬСТВО <> 'Аст';
б) SELECT * FROM KNIGA WHERE ЖАНР = ANY
SELECT DISTINCT ЖАНР FROM KNIGA WHERE
ИЗДАТЕЛЬСТВО = 'Аст') AND ИЗДАТЕЛЬСТВО <> 'Аст';
12.Выбрать из таблицы KNIGA список фамилий авторов, чьи книги имеются в каждом из издательств:
SELECT АВТОР FROM KNIGA WHERE ИЗДАТЕЛЬСТВО = 'Аст'
INTERSECT
SELECT АВТОР FROM KNIGA WHERE ИЗДАТЕЛЬСТВО = 'Нова';
Группирование строк. Строки, возвращаемые SELECT-запросом, могут быть объединены в группы на основе значений определенного выражения для каждой строки. Примером такого группирования может служить объединение в группы книг одного жанра, информация о которых имеется в таблице KNIGA. Так как в таблице присутствуют книги только трех жанров, то будут сформированы только три группы строк. Применив к каждой группе функцию SUM для столбца, содержащего значение цены, можно получить суммарную величину стоимости книг по каждому жанру. Для осуществления группирования используется конструкция GROUP BY оператора SELECT, в которой указывается перечень, состоящий из одного или нескольких выражений, разделенных запятыми, по значениям которых и осуществляется группирование. Если оператор SELECT содержит пункт GROUP BY, то список извлекаемых значений ограничен. Он может содержать константы, групповые функции, функцию SYSDATE и выражения, идентичные выражениям, указанным в пункте GROUP BY. На формирование результирующих строк могут быть наложены определенные условия. Для задания такого условия используется ключевое слово HAVING.
Примеры.
1. Выбрать из таблицы KNIGA информацию о количестве различных жанров:
SELECT COUNT(DISTINCT ЖАНР) FROM KNIGA;
2. Выбрать из таблицы KNIGA информацию о количестве, суммарной стоимости и максимальной стоимости имеющихся книг:
SELECT COUNT(КОД_КНИГИ), SUM(ЦЕНА), MAX(ЦЕНА) FROM KNIGA;
3. Выбрать из таблицы KNIGA по каждому издательству информацию о количестве и суммарной стоимости изданных им книг, сгруппировав ее по жанрам:
SELECT ИЗДАТЕЛЬСТВО, ЖАНР, COUNT(НАЗВАНИЕ), SUM(ЦЕНА) FROM KNIGA GROUP BY ИЗДАТЕЛЬСТВО, ЖАНР;
4. Выбрать из таблицы KNIGA информацию о количестве и средней стоимости (округлив значение средней стоимости до двух знаков после запятой) книг в тех жанрах, где количество различных названий книг не менее 2:
SELECT ЖАНР, COUNT(DISTINCT НАЗВАНИЕ), ROUND(AVG(ЦЕНА), 2) FROM KNIGA GROUP BY ЖАНР
HAVING COUNT(DISTINCT НАЗВАНИЕ) >= 2;
5. Выбрать из таблицы KNIGA информацию о минимальной стоимости книг в жанре “Роман”:
SELECT MIN(ЦЕНА) FROM KNIGA WHERE ЖАНР = 'Роман';
6. Выбрать из таблицы KNIGA информацию о самой дешевой книге в жанре “Роман”:
SELECT АВТОР, НАЗВАНИЕ, ЦЕНА FROM KNIGA
WHERE ЦЕНА = (SELECT MIN(ЦЕНА) FROM KNIGA WHERE ЖАНР = 'Роман') AND ЖАНР = 'Роман';
7. Выбрать из таблицы KNIGA информацию (фамилия автора, название жанр, цена) о книгах, имеющих максимальную стоимость в своем жанре:
а) SELECT АВТОР, НАЗВАНИЕ, ЖАНР, ЦЕНА FROM KNIGA WHERE (ЦЕНА, ЖАНР) IN (SELECT MAX(ЦЕНА), ЖАНР FROM KNIGA GROUP BY ЖАНР);
б) SELECT АВТОР, НАЗВАНИЕ, KNIGA.ЖАНР, ЦЕНА FROM KNIGA, (SELECT ЖАНР, MAX(ЦЕНА) МАКС FROM KNIGA GROUP BY ЖАНР) P1 WHERE ЦЕНА = МАКС AND KNIGA.ЖАНР = P1.ЖАНР;
в) SELECT АВТОР, НАЗВАНИЕ, ЖАНР, ЦЕНА FROM KNIGA P1 WHERE ЦЕНА = (SELECT MAX(ЦЕНА) FROM KNIGA WHERE KNIGA.ЖАНР = P1.ЖАНР);
Третий запрос содержит коррелированный подзапрос. Поскольку в своем условии подзапрос содержит ссылку на столбец родительского запроса, поэтому он будет выполняться один раз для каждой строки, извлекаемой родительским запросом. В первом и во втором вариантах подзапрос не является коррелированным, он выполняется только один раз для родительского запроса.
8. Выбрать из таблицы KNIGA информацию о книгах, стоимостью больше средней стоимости книг:
SELECT АВТОР, НАЗВАНИЕ, ЦЕНА FROM KNIGA WHERE ЦЕНА > (SELECT AVG(ЦЕНА) FROM KNIGA);
9.Выдать из таблицы KNIGA список жанров, по которым имеется наибольшее количество различных книг, с указанием количества книг:
SELECT ЖАНР, COUNT(DISTINCT НАЗВАНИЕ) FROM KNIGA
GROUP BY ЖАНР HAVING COUNT(DISTINCT НАЗВАНИЕ)=
(SELECT MAX(COUNT(DISTINCT НАЗВАНИЕ)) FROM KNIGA
GROUP BY ЖАНР);
10.Выбрать из таблицы KNIGA информацию о книгах (фамилия автора, название), относящихся к жанрам, по которым имеется наибольшее количество различных книг:
SELECT АВТОР, НАЗВАНИЕ FROM KNIGA WHERE ЖАНР IN
(SELECT ЖАНР FROM KNIGA GROUP BY ЖАНР HAVING
COUNT(DISTINCT НАЗВАНИЕ)=
(SELECT MAX(COUNT(DISTINCT НАЗВАНИЕ)) FROM KNIGA
GROUP BY ЖАНР));
Выбор информации из нескольких таблиц (соединение). Соединение– это SELECT-запрос, который выбирает строки из двух или более таблиц. При этом запрос может извлекать любые столбцы из любой таблицы. Если хотя бы две из этих таблиц имеют одинаково названные столбцы, то имена таких столбцов должны уточняться именами таблиц, записываемых перед именами столбцов через точку. Большинство SELECT-запросов с соединениями содержат условия, в которых сравниваются значения столбцов из разных таблиц. Такие условия называются условиями соединения.
Эквисоединение.Это соединение с использованием в условии соединения операции равенства. Таким образом, эквисоединение извлекает строки с эквивалентными значениями в указанных столбцах.
Декартово произведение.Если в запросе не указано условие соединения, то строится декартово произведение таблиц, т. е. к каждой строке первой таблицы приписывается каждая строка второй таблицы.
Самосоединение.Соединяет таблицу саму с собой. При этом таблица появляется в списке FROM дважды и должна иметь дополнительное имя (псевдоним), чтобы можно было однозначно идентифицировать столбцы в условии соединения.
Внешнее соединение.Выдает все строки, которые удовлетворяют условию соединения, а также строки одной из таблиц, которые не удовлетворяют условию соединения. Чтобы записать запрос, который выполняет внешнее соединение таблиц A и B и выдает все строки из таблицы A, применим операцию внешнего соединения (+) ко всем столбцам из таблицы B в условиях соединения. Тогда для всех строк из таблицы A, для которых нет соответствующих строк в таблице B, ORACLE предоставит строку, содержащую NULL во всех выражениях в списке столбцов, которые содержат столбцы из таблицы B.
Примеры.
1. Выбрать из таблиц KNIGA и KNIGA_POSTAVKA информацию о книгах, поставленных продавцам за период с 24.01.2006 по 12.02.2006, указав для выводимого значения даты специальный формат вывода:
KNIGA.КОД_КНИГИ = KNIGA_POSTAVKA.КОД_КНИГИ AND ДАТА_ПОСТУПЛЕНИЯ BETWEEN '24-JAN-06' AND '12-FEB-06'
ORDER BY ПРОДАВЕЦ, АВТОР;
2. Выбрать из таблиц KNIGA и KNIGA_POSTAVKA по указанному продавцу – перечень издательств и жанров имеющихся у него книг без повторения; для задания фамилии продавца использовать переменную подстановки:
SELECT DISTINCT ПРОДАВЕЦ, ИЗДАТЕЛЬСТВО, ЖАНР
FROM KNIGA, KNIGA_POSTAVKA
WHERE KNIGA_POSTAVKA.КОД_КНИГИ = KNIGA.КОД_КНИГИ AND ПРОДАВЕЦ = '&PRODAVES'
ORDER BY ИЗДАТЕЛЬСТВО, ЖАНР;
3. Выбрать из таблиц KNIGA и KNIGA_POSTAVKA список продавцов, у которых в наличии более 10 книг, указав данные об общем количестве и суммарной стоимости имеющихся у них книг:
SELECT ПРОДАВЕЦ, SUM(КОЛ_ЕДИНИЦ), SUM(ЦЕНА*КОЛ_ЕДИНИЦ) FROM KNIGA, KNIGA_POSTAVKA
WHERE KNIGA_POSTAVKA.КОД_КНИГИ = KNIGA.КОД_КНИГИ
GROUP BY ПРОДАВЕЦ HAVING SUM(КОЛ_ЕДИНИЦ) >= 10;
4. Выбрать из таблиц KNIGA и KNIGA_POSTAVKA по каждому издательству информацию об общем количестве и суммарной стоимости поставленных ими книг каждому продавцу:
SELECT ИЗДАТЕЛЬСТВО, ПРОДАВЕЦ, COUNT(КОЛ_ЕДИНИЦ), SUM(ЦЕНА*КОЛ_ЕДИНИЦ) FROM KNIGA, KNIGA_POSTAVKA
WHERE KNIGA.КОД_КНИГИ = KNIGA_POSTAVKA.КОД_КНИГИ
GROUP BY ИЗДАТЕЛЬСТВО, ПРОДАВЕЦ;
5.Выбрать из таблиц KNIGA и KNIGA_POSTAVKA по каждой книге, сведения о которой имеются в таблице KNIGA, информацию о количестве продавцов, которым она была поставлена:
SELECT KNIGA.КОД_КНИГИ, COUNT(ПРОДАВЕЦ) FROM KNIGA, KNIGA_POSTAVKA WHERE KNIGA_POSTAVKA.КОД_КНИГИ
(+)= KNIGA.КОД_КНИГИ GROUP BY KNIGA.КОД_КНИГИ;
6.Выбрать из таблиц KNIGA и KNIGA_POSTAVKA по каждому продавцу информацию об отсутствующих у них книгах, общий перечень которых находится в таблице KNIGA:
SELECT ПРОДАВЕЦ, НАЗВАНИЕ, ЦЕНА FROM KNIGA, KNIGA_POSTAVKA
MINUS
SELECT ПРОДАВЕЦ, НАЗВАНИЕ, ЦЕНА FROM KNIGA, KNIGA_POSTAVKА WHERE KNIGA_POSTAVKA.КОД_КНИГИ = KNIGA.КОД_КНИГИ;
7.Выбрать из таблицы KNIGA информацию (название, цена) о трех самых дешевых книгах; предполагается, что в перечне книг не более трех различных книг с минимальной ценой:
SELECT А.НАЗВАНИЕ, А.ЦЕНА FROM KNIGA А, KNIGA В WHERE А.ЦЕНА >= В.ЦЕНА
GROUP BY А.НАЗВАНИЕ, А.ЦЕНА HAVING COUNT(В.НАЗВАНИЕ) <= 3 ORDER BY А.НАЗВАНИЕ;
8.Выбрать из таблиц KNIGA и KNIGA_POSTAVKA информацию (название и фамилию автора) о книгах, которые не были поставлены в магазин для продажи:
SELECT НАЗВАНИЕ, АВТОР FROM KNIGA WHERE
NOT EXISTS
(SELECT * FROM KNIGA_POSTAVKA WHERE KNIGA_POSTAVKA.КОД_КНИГИ = KNIGA.КОД_КНИГИ);
