Вложенные запросы

Запросы к нескольким таблицам

Запросы к одной таблице

Пример реляционной базы данных

Изучение оператора SQL будем на примере реляционной БД, схема которой представлена на Рис. 15, состоящей из четырех таблиц, содержание которых приведено в Табл. 10 - Табл. 13.

Рис. 15. Схема базы данных «Продажи»

Табл. 10. Таблица «Продавцы»

Табл. 11. Таблица «Покупатели»

Табл. 12. Таблица «Товары»

Табл. 13. Таблица «Сделки»

Вывести все данные из таблицы продавцы (запрос приведен наЛист. 2, результат - Табл. 14):

Лист. 2. Запрос с операторами Select и From

SELECT *

FROM Продавцы;

Табл. 14. Результат запроса с операторами Select и From.

Вывести все данные из таблицы продавцы, отсортировав записи по отделам (запрос приведен наЛист. 3, результат -Табл. 15):

Лист. 3. Запрос с оператором Order By

SELECT *

FROM Продавцы

ORDER BY Отдел;

Табл. 15. Результат запроса с оператором Order By

Вывести все данные из таблицы продавцы, отсортировав записи по отделам и городам (запрос приведен на Лист. 4, результат - Табл. 16.):

Лист. 4. Запрос №2 с оператором Order By

SELECT *

FROM Продавцы

ORDER BY Отдел, Город;

Табл. 16. Результат запроса №2 с оператором Order By

Вывести все отделы (запрос приведен на Лист. 5, результат - Табл. 17):

Лист. 5. Запрос с выводом атрибута

SELECT Отдел

FROM Продавцы;

Табл. 17. Результат запроса с выводом атрибута

Вывести все отделы, исключив повторяющиеся записи (запрос приведен на Лист. 6, результат - Табл. 18):

Лист. 6. Запрос с оператором Distinct

SELECT DISTINCT Отдел

FROM Продавцы;

Табл. 18. Результат запроса с оператором Distinct

Вывести число наименований товаров, которые когда-либо были проданы (запрос приведен на Лист. 7):

Лист. 7. Запрос с операторами Distinct и Count

SELECT count (DISTINCT N_Товара)

FROM Сделки;

Вывести среднее количество проданных товаров по всем сделкам (запрос приведен на Лист. 8, результат - Табл. 19):

Лист. 8. Запрос с оператором avg

SELECT avg(Количество) AS Среднее_количество

FROM Сделки;

Табл. 19. Результат запроса с оператором avg

Вывести минимальное количество проданных товаров по всем сделкам (запрос приведен на Лист. 9, результат - Табл. 20):

Лист. 9. Запрос с оператором min

SELECT Min(Количество) AS Минимальное_количество

FROM Сделки;

Табл. 20. Результат запроса с оператором min

Вывести максимальное количество проданных товаров по всем сделкам (запрос приведен на Лист. 10, результат - Табл. 21):

Лист. 10. Запрос с оператором max

SELECT max(Количество) AS Максимальное_количество

FROM Сделки;

Табл. 21. Результат запроса с оператором max

Вывести суммарное количество проданных товаров по всем сделкам (запрос приведен на Лист. 11, результат - Табл. 22):

Лист. 11. Запрос с оператором sum

SELECT sum(Количество) AS Суммарное_количество

FROM Сделки;

Табл. 22. Результат запроса с оператором sum

Ошибка №1. Часто в заданиях встречаются слова «число» или «количество». Нужно догадаться по смыслу, речь идет о количестве строк в некоторой группе записей, или о названии колонки в какой-либо таблице. В данном случае используется агрегирующий оператор count. Если бы речь шла о названии колонки, то нужно было бы догадаться о какой таблице идет речь. Так, например, в нашей базе данных «Количество» есть в таблице «Товары» (имеется в виду количество на складе) и в таблице «Сделки» (количество товара, проданного в сделке). Сравнение запросов см. в Табл. 23.

Табл. 23. Сравнение запросов на количество

Вывести цену единицы товара (запрос приведен на Лист. 12, результат - Табл. 24):

Лист. 12. Запрос с операциями над атрибутами

SELECT N, N_Продавца, N_Покупателя, N_Товара, Стоимость/Количество AS Цена

FROM Сделки

ORDER BY Сделки.N_Товара;

Табл. 24. Результат запроса с операциями над атрибутами

Вывести информацию о сделках, стоимость которых больше 400 (запрос приведен на Лист. 13, результат - Табл. 25):

Лист. 13. Запрос с оператором Where

SELECT N, N_Продавца, N_Покупателя, N_Товара, Количество, Стоимость

FROM Сделки

WHERE Стоимость>400;

Табл. 25. Результат запроса с оператором Where

Вывести информацию о сделках, стоимость которых больше 400 и количеством проданного товара в сделке больше 30 (запрос приведен на Лист. 14, результат - Табл. 26):

Лист. 14. Запрос с операторами Where и And

SELECT N, N_Продавца, N_Покупателя, N_Товара, Количество, Стоимость

FROM Сделки

WHERE Стоимость>400

AND Количество>30;

Табл. 26. Результат запроса с операторами Where и And

Вывести номера продавцов и число сделок, ими заключенных (запрос приведен на Лист. 15, результат - Табл. 27):

Лист. 15. Запрос с операторами Group By и Count

SELECT N_продавца, count(*) as Количество_сделок

FROM Сделки

GROUP BY N_продавца;

Табл. 27. Результат запроса с операторами Group By и Count

Вывести номера продавцов и максимальную стоимость их сделок (запрос приведен на Лист. 16, результат - Табл. 28):

Лист. 16. Запрос с оператором Group By и агрегирующей функцией

SELECT N_Продавца, min(Стоимость) As Минимальная_стоимость, avg(Стоимость) As Средняя_стоимость, max(Стоимость) As Максимальная_стоимость

FROM Сделки

GROUP BY N_Продавца;

Табл. 28. Результат запроса с оператором Group By и агрегирующей функцией

Ошибка №2. Не нужно путать операторы Group By и Order By. Order By только сортирует записи, а Group By объединяет их в группы. Причем для каждой группы на экран выводится только одна строка – результат обработки всех строк в группе.

Ошибка №3. При группировке поля, участвующие в группировке, перечисляются в select без агрегирующей операции, все остальные поля – с агрегирующей операцией.

Вывести номера продавцов, даты и максимальную стоимость их сделок на каждый день (запрос приведен на Лист. 17, результат - Табл. 29):

Лист. 17. Запрос №2 с оператором Group By

SELECT N_Продавца, Дата, max(Стоимость)

FROM Сделки

GROUP BY N_Продавца, Дата;

Табл. 29. Результат запроса №2 с оператором Group By

Вывести номера продавцов и максимальную стоимость их сделок в случае, если средняя стоимость их сделок больше 500 (запрос приведен на Лист. 18, результат - Табл. 30):

Лист. 18. Запрос с оператором Having и агрегирующей функцией

SELECT N_Продавца, max(Стоимость) as Максимальная_стоимость

FROM Сделки

GROUP BY N_Продавца

HAVING avg(Стоимость)>500;

Табл. 30. Результат запроса с оператором Having и агрегирующей функцией

Ошибка №4. Не нужно путать применение where и having. Having используется при группировке, когда условие накладывается на поле с агрегирующей функцией. В данном примере - средняя стоимость их сделок больше 500. Если бы слово «средняя» отсутствовало, это было бы признаком использования where

Вывести номера продавцов и максимальную стоимость их сделок в случае, если средняя стоимость их сделок больше 500, отсортировать по максимальной стоимости (запрос приведен на Лист. 19, результат - Табл. 31):

Лист. 19. Запрос с операторами Having и Order By

SELECT N_Продавца, max(Стоимость) as Максимальная_стоимость

FROM Сделки

GROUP BY N_Продавца

HAVING avg(Стоимость)>500

ORDER BY avg(Стоимость);

Табл. 31. Результат запроса с операторами Having и Order By

Вывести номера продавцов и максимальную стоимость их сделок в случае, если средняя стоимость их сделок больше 500 и максимальная стоимость сделок < 800 (запрос приведен на Лист. 20, результат - Табл. 32):

Лист. 20. Запрос с операторами Having и And

SELECT N_Продавца, max(Стоимость) as Максимальная_стоимость

FROM Сделки

GROUP BY N_Продавца

HAVING (avg(Стоимость)>500 And max(Стоимость)<800);

Табл. 32. Результат запроса с операторами Having и And

Вывести номера продавцов и минимальную стоимость их сделок в случае, если номер продавца > 1 (запрос приведен на Лист. 21, результат - Табл. 33):

Лист. 21. Запрос с оператором Having без агрегирующей функции

SELECT N_Продавца, min(Стоимость) As Минимальная_стоимость

FROM Сделки

GROUP BY N_Продавца

HAVING N_Продавца>1;

Табл. 33. Результат запроса с оператором Having без агрегирующей функции

Ошибка №5. Аналогично полям Select при группировке, те поля, которые перечисляются в Group By, могут использоваться в Having без агрегирующей операции; те, которых нет в Group By, используются в Having c агрегирующей операцией.

Вывести номера продавцов и максимальную стоимость тех сделок, кол-во товара которых больше 15, в случае, если максимальная стоимость этих сделок меньше 800 (запрос приведен на Лист. 22, результат - Табл. 34):

Лист. 22. Запрос с операторами Where, Group by и Having

SELECT N_Продавца, max(Стоимость) as Максимальная_стоимость

FROM Сделки

WHERE Количество>15

GROUP BY N_Продавца

HAVING max(Стоимость)<800;

Табл. 34. Результат запроса с операторами Where, Group by и Having

Ошибка №6. Не нужно путать порядок следования операторов: where, group by, having, order by

Ошибка №7. При совместном использовании операторов where, group by, having в одном запросе сперва осуществляется селекция по условию в where, затем происходит группировка и вычисление агрегирующих функций. Таким образом, при подсчете средних значений не учитываются записи, которые были отброшены. Так, в нашем примере, средняя стоимость по продавцу №1 меньше 400. Поэтому нужно смотреть по заданию, считать ли агрегирующие функции по отфильтрованным строкам или надо строить более сложный запрос.

Запрос, в задании которого приведены всевозможные стоимости, так что легко сделать несколько вышеперечисленных ошибок: Вывести номера продавцов с суммарной стоимостью тех сделок, в которых стоимость была больше 400, если эта суммарная стоимость больше 500, отсортировать по суммарной стоимости, приведен на Лист. 23, его результат – в Табл. 35. Обратите внимание, что продавец №1 отсеялся, несмотря на то, что суммарная стоимость его сделок больше 500. Почти все его сделки были со стоимостью меньше 400. Запрос сперва выполняет селекцию в where, а суммарный итог подводит по оставшимся записям.

Лист. 23. Запрос с операторами Where, Group by и Having, Order by

SELECT N_Продавца, sum(Стоимость) AS Суммарная_стоимость

FROM Сделки

WHERE Стоимость>400

GROUP BY N_Продавца

HAVING sum(Стоимость)>500

ORDER BY sum(Стоимость);

Табл. 35. Результат запроса с операторами Where, Group by и Having, Order by

Вывести номера и имена продавцов и соответствующих им заказчиков, если они живут в одном городе (запрос приведен на Лист. 24, результат - Табл. 36):

Лист. 24. Запрос на соединение трех таблиц

SELECT DISTINCT Сделки.N_Продавца, Продавцы.Имя, Сделки.N_Покупателя, Покупатели.Имя

FROM Сделки, Продавцы, Покупатели

WHERE Сделки.N_Продавца = Продавцы.N

AND Сделки.N_Покупателя = Покупатели.N

AND Продавцы.Город = Покупатели.Город;

Табл. 36. Результат запроса на соединение трех таблиц

Вывести номера и имена продавцов и имена соответствующих им начальников (запрос приведен на Лист. 25, результат -Табл. 37):

Лист. 25. Запрос с псевдонимами к таблице с рекурсивной связью

SELECT А.N, A.Имя, B.Имя

FROM Продавцы А, Продавцы B

WHERE A.N_Начальника = B.N;

Табл. 37. Результат запроса с псевдонимами к таблице с рекурсивной связью

Ошибка №8. Необходимость в псевдонимах к одной таблице для начинающих программистов не всегда ясна. Нужно смотреть по тексту задания, в каких смыслах упоминается одна и та же таблица. Так, в этом запросе в явном виде написана таблица продавцы и упоминаются начальники (которые фактически тоже хранятся в таблице продавцы). Таким образом, таблица «Продавцы» должна два раза встретиться в запросе под двумя псевдонимами. Мы фактически создаем две копии одной таблицы и работаем с ними, как будто это разные таблицы.

Вывести имена тех продавцов, которые продавали клиентам Иванова (запрос приведен на Лист. 26, результат - Табл. 38):

Лист. 26. Запрос с псевдонимами к одной таблице

SELECT DISTINCT B.Имя

FROM Продавцы AS А, Продавцы AS B, Сделки AS C, Сделки AS D

WHERE А.Имя='Иванов' And B.Имя<>'Иванов' And А.N=C.N_Продавца And B.N=D.N_Продавца And C.N_Покупателя=D.N_Покупателя;

Табл. 38. Результат запроса с псевдонимами к одной таблице

Вывести пары продавцов, у которых были общие клиенты (запрос приведен на Лист. 27, результат - Табл. 39):

Лист. 27. Запрос №2 с псевдонимами к одной таблице

SELECT DISTINCT А.Имя, B.Имя

FROM Продавцы AS А, Продавцы AS B, Сделки AS C, Сделки AS D

WHERE А.Имя<>B.Имя And А.N=C.N_Продавца And B.N=D.N_Продавца And C.N_Покупателя=D.N_Покупателя;

Табл. 39. Результат запроса №2 с псевдонимами к одной таблице

Вывести все товары и даты, в которые эти товары продавались; в случае, если товар не продавался ни разу, вывести пустое место (запрос приведен на Лист. 28, результат - Табл. 40).

Лист. 28. Запрос с оператором Left Join

SELECT Товары. Название, Сделки.Дата

FROM Товары LEFT JOIN Сделки

ON Товары.N = Сделки.N_Товара

Табл. 40. Результат запроса с оператором Left Join

Вывести все названия товаров с количеством товара на складе больше 15 и их суммарное проданное количество, отсортировав по суммарному проданному количеству место (запрос приведен на Лист. 29, результат - Табл. 1). В этом запросе таблицы соединяются с помощью левого соединения, затем идет фильтрация с помощью where. Дополнительную трудность в понимании создают атрибуты из разных таблиц с одинаковым названием – «количество».

Лист. 29. Запрос с операторами Left Join, Where, Group By, Order By

SELECT Товары.Название, sum(Сделки.Количество) AS суммарное_количество

FROM Товары LEFT JOIN Сделки ON Товары.N=Сделки.N_Товара

GROUP BY Товары.Название

ORDER BY sum(Сделки.Количество);

Табл. 41 Результат запроса с операторами Left Join, Where, Group By, Order By

Из запроса виден порядок операторов в языке SQL:

1. select
2. from
3. join
4. where
5. group by
6. having
7. order by

Аналогично работают другие виды соединений (см. Табл. 42).

Табл. 42. Описание различных видов соединений.

Ошибка №9. Тяжело опознать случаи, когда надо выводить все записи из одной таблицы, если остальные условия накладываются на другую таблицу (при этом записям в первой таблице может не быть соответствий во второй). Такие случаи можно увидеть, если в задании встречается слово «все».

Вывести номера продавцов, заключивших более одной сделки (запрос приведен на Лист. 30, результат - Табл. 43):

Лист. 30. Вложенный подзапрос в Where

SELECT distinct А.N_Продавца

FROM Сделки AS А

WHERE 1<(

SELECT count(*)

FROM Сделки B

WHERE B.N_Продавца=А.N_Продавца);

Табл. 43. Результат вложенного подзапроса в Where

Вывести номера продавцов и максимальную из средних стоимостей их сделок по дням. (запрос приведен на Лист. 31, результат – в Табл. 44):

Лист. 31. Вложенный подзапрос в From

SELECT N_Продавца, max(Средняя_стоимость)

FROM (SELECT N_Продавца, Дата, avg(Стоимость) AS Средняя_стоимость

FROM Сделки GROUP BY N_Продавца, Дата)

GROUP BY N_Продавца;

Табл. 44. Результат запроса с вложенным подзапросом в From

Ошибка №10. Вложенные подзапросы помогают поэтапно конструировать запрос в сложных случаях, но злоупотреблять ими не стоит. Так, например, этот запрос легко делается с использованием Having без вложенного подзапроса. См. сравнение одинаковых по результату запросов в Табл. 45.

Табл. 45. Одинаковые по результату запросы с подзапросами и без

Вывести номера продавцов и разницу между средней стоимостью их сделок и средней стоимостью всех сделок (запрос приведен на Лист. 32, результат - Табл. 46):

Лист. 32. Вложенный подзапрос в Select

SELECT N_Продавца, avg(Стоимость) – (SELECT avg(стоимость) FROM Сделки)

FROM Сделки

GROUP BY N_Продавца;

Табл. 46. Результат запроса с вложенным подзапросом в Select

Вывести номера продавцов и среднюю стоимость их сделок, если средняя стоимость сделок этих продавцов больше средней стоимости по их отделу (запрос приведен на Лист. 33, результат - Табл. 47):

Лист. 33. Вложенный подзапрос в Having

SELECT B.Имя, avg(А.Стоимость)

FROM Сделки AS А, Продавцы AS B

WHERE А.N_Продавца=B.N

GROUP BY B.Отдел, А.N_Продавца, B.Имя

HAVING avg(А.Стоимость) > ( SELECT avg(C.Стоимость) FROM Сделки C, Продавцы D WHERE C.N_Продавца = D.N AND D.Отдел = B.Отдел);

Табл. 47 Результат запроса с вложенным подзапросом в Having

Ошибка №11. Группу во внешнем запросе однозначно идентифицирует А.N_Продавца, но мы в операторе GROUP BY также привели поля из таблицы B. «B.Имя» необходимо вывести на экран, без его указания в GROUP BY его пришлось бы выводить с агрегирующей функцией. «B.Отдел» используется во вложенном подзапросе, поэтому без указания в GROUP BY его тоже пришлось бы приводить в подзапросе с агрегирующей функцией.