1. Запрос по одной таблице, вычисляющий агрегатную функцию с использованием операторов where, having, order by.

2. Запрос соединения нескольких таблиц с использованием оператора where, одна таблица должна использоваться в запросе несколько раз под псевдонимами.

3. Запрос по правому, левому или полному соединению.

4. Запрос с двумя вложенными запросами в конструкциях where и having

5. Запрос с вложенным запросом, используя all, any или exists.

6. Вложенный запрос с объединением двух таблиц (одна из разновидностей join), опосредованно связанных третьей.

7. Объединение однотипных запросов.

8. Рекурсивный запрос.

Вопросы для самопроверки:

1. Чем отличается использование операторов group by, group by rollup, group by rollup cube, grouping, orger by, partition?

2. Чем отличается использование операторов where и having?

3. Какому запросу join … on соответствует оператор where?

4. Какой операции реляционной алгебры соответствует оператор where?

5. Какой операции реляционной алгебры соответствует оператор join?

Запросы для самостоятельного программирования

В домашнем задании было предложено придумать запросы с использованием различных операторов для своего примера. Чтобы убедиться в активном владении SQL, нужно уметь писать запросы по заданию для произвольной схемы данных, понимая, какие операторы нужны в запросе. Кроме того, нужно уметь совместно использовать различные принципы построения SQL-запросов. Ниже приводится список заданий, сгруппированных по нескольким уровням сложности.

Задания сформулированы в свободной форме для БД «продажи», приведенной на Рис. 15. При этом фраза «вывести продавцов» означает то, что результат запроса должен содержать имена продавцов, аналогично – «вывести клиентов» - «вывести имена клиентов» и «вывести товары» - «вывести названия товаров», «вывести сделки» - вывести всю информацию о сделке

Уровень 1.

Where

1. Вывести сделки с количеством проданного товара > 10.

2. Вывести продавцов с зарплатой > 20000.

3. Вывести продавцов из отдела розничных продаж.

4. Вывести продавцов из Екатеринбурга.

5. Вывести товары с количеством на складе < 5.

Агрегатные функции

1. Вывести среднюю стоимость всех сделок.

2. Вывести максимальную стоимость всех сделок.

3. Вывести минимальную стоимость всех сделок.

4. Вывести суммарную стоимость всех сделок.

5. Вывести количество всех сделок.

Уровень 2

Group by

1. Вывести среднюю стоимость всех сделок для каждой даты.

2. Вывести максимальную стоимость всех сделок для каждой даты.

3. Вывести минимальную стоимость всех сделок для каждой даты.

4. Вывести суммарную стоимость всех сделок для каждой даты.

5. Вывести количество всех сделок для каждой даты.

Having

1. Вывести номера продавцов со средней стоимостью сделок > 100.

2. Вывести номера продавцов с максимальной стоимостью сделок > 100.

3. Вывести номера продавцов с минимальной стоимостью сделок > 100.

4. Вывести номера продавцов с суммарной стоимостью сделок > 100.

5. Вывести номера продавцов, заключивших более 100 сделок.

Соединение where

1. Вывести все сделки отдела розничных продаж.

2. Вывести все сделки с мылом.

3. Вывести все сделки продавцов из Екатеринбурга.

4. Вывести сделки, заключенные с покупателями из Екатеринбурга.

5. Вывести список продавцов, продававших покупателям из одного с ними города.

Join

1. Вывести всех без исключения продавцов и сделки, ими заключенные.

2. Вывести всех без исключения покупателей и сделки, ими заключенные.

3. Вывести все без исключения товары и соответствующие им сделки.

Уровень 3

group by

1. Рассчитать среднюю стоимость сделок для каждого отдела.

2. Рассчитать минимальную стоимость сделок для каждого отдела.

3. Рассчитать максимальную стоимость сделок для каждого отдела.

4. Рассчитать суммарную стоимость сделок для каждого отдела.

5. Рассчитать количество сделок для каждого отдела.

Where + агрегатная функция

1. Рассчитать среднюю стоимость сделок с количеством проданного товара > 10.

2. Рассчитать минимальную стоимость сделок с количеством проданного товара > 10.

3. Рассчитать максимальную стоимость сделок с количеством проданного товара > 10.

4. Рассчитать суммарную стоимость сделок с количеством проданного товара > 10.

Having + агрегатная функция

1. Рассчитать среднюю стоимость сделок для продавцов, заключивших более 100 сделок.

2. Рассчитать максимальную стоимость сделок для покупателей, заключивших более 100 сделок.

3. Рассчитать суммарную стоимость сделок для тех товаров, на которые было заключено более 100 сделок.

4. Рассчитать суммарную стоимость сделок для продавцов, заключивших более 100 сделок.

5. Рассчитать максимальную стоимость сделок для тех товаров, на которые было заключено более 100 сделок.

Where + having

1. Вывести тех продавцов, суммарная стоимость сделок которых с количеством товара в каждой сделке > 100 шт. была больше 1000 руб.

2. Вывести тех продавцов, средняя стоимость сделок которых с количеством товара в каждой сделке > 100 шт. была больше 1000 руб.

3. Вывести тех покупателей, минимальная стоимость сделок которых с количеством товара в каждой сделке > 100 шт. была больше 1000 руб.

4. Вывести тех покупателей, максимальная стоимость сделок которых с количеством товара в каждой сделке > 100 шт. была больше 1000 руб.

5. Вывести те товары, максимальная стоимость сделок на которые с количеством товара > 100 шт. в каждой сделке была больше 1000 руб.

Where + join

1. Вывести всех без исключения продавцов и номера их сделок, если стоимость сделки была больше 1000.

2. Вывести всех без исключения покупателей и номера их сделок, если стоимость сделки была больше 1000.

3. Вывести всех без исключения товары и номера их сделок, если стоимость сделки была больше 1000.

Объединение однотипных запросов

1. Вывести товары с меткой популярные и непопулярные. Популярные – с количеством сделок >= 500, непопулярные - < 500.

2. Вывести товары с меткой дорогие и дешевые. Дорогие – с суммарной стоимостью >= 500, дешевые - < 500.

3. Вывести покупателей с меткой постоянные и нерегулярные. Постоянные – с количеством сделок >= 500, нерегулярные - < 500.

4. Вывести продавцов с меткой сильные и слабые. Сильные – с количеством сделок >= 500, слабые - < 500.

Уровень 4

Псевдонимы, рекурсивная связь к 1 таблице

1. Вывести всех подчиненных Иванова.

2. Для каждого продавца найти продавцов из одного с ним отдела.

3. Для каждого продавца найти продавцов из одного с ним города.

4. Для каждого покупателя найти покупателей из одного с ним города.

Псевдонимы, связь одной таблицы с собой через вторую

1. Вывести всех продавцов, продававших покупателям Иванова.

2. Вывести всех покупателей продавцов, продававших товары Павлову.

3. Вывести товары, которые покупают покупатели, купившие мыло.

4. Вывести товары, которые продают продавцы, продающие мыло.

Псевдонимы, пары записей

1. Вывести пары товаров, если у них был один продавец.

2. Вывести пары товаров, если их покупал один покупатель.

3. Вывести пары продавцов, если они продавали один и тот же товар.

4. Вывести пары продавцов, если они заключили сделку с одним и тем же покупателем.

5. Вывести пары покупателей, если у них был общий продавец.

Псевдонимы, циклическая связь между тремя таблицами

1. Для каждого продавца вывести продавцов из одного города с его покупателями.

2. Для каждого покупателя вывести покупателей из одного города с его продавцами.

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

1. Вывести тех продавцов, у которых средняя стоимость сделок больше, чем средняя стоимость сделок по их отделу.

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

3. Вывести тех продавцов, у которых максимальная стоимость сделок больше, чем средняя стоимость сделок по их отделу.

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

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

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

1. Вывести разницу между средней стоимостью сделок данного продавца и средней стоимостью сделки его отдела.

2. Вывести разницу между максимальной стоимостью сделок данного продавца и средней стоимостью сделок по его городу.

3. Вывести разницу между минимальной стоимостью сделок данного продавца и средней стоимостью сделок по его городу.

4. Вывести разницу между средней стоимостью сделок данного покупателя и средней стоимостью сделок по его городу.

5. Вывести разницу между максимальной стоимостью сделок данного покупателя и средней стоимостью сделок по его городу.

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

1. Для каждого продавца вывести максимальную из минимальных стоимостей сделок по каждой дате.

2. Для каждого покупателя вывести среднюю из минимальных стоимостей сделок по каждой дате.

3. Для каждого товара вывести минимальную из средних стоимостей сделок по каждой дате.

4. Для каждого продавца вывести среднюю из максимальных стоимостей сделок по каждой дате.

Вложенные запросы с кванторами

1. Вывести продавцов, которые продавали только мыло.

2. Вывести продавцов, которые заключили только одну сделку и продали мыло.

3. Вывести покупателей, которые покупали только мыло.

4. Вывести покупателей, которые заключили только одну сделку и купили мыло.

Вложенные запросы с объединением join

1. Вывести всех без исключения имена продавцов и названия товаров, которые они продавали.

2. Вывести всех без исключения имена покупателей и названия товаров, которые они покупали.

3. Вывести все без исключения названия товаров, и имена продавцов, которые их продавали.

4. Вывести все без исключения названия товаров, и имена покупателей, которые их покупали.

Уровень 5

Вложенные запросы с объединением join + where

1. Вывести всех без исключения продавцов и покупателей (если они были), с которыми они заключали сделки, если стоимость хотя бы 1 сделки была больше 1000.

Вложенные запросы с объединением join + having

2. Вывести всех без исключения продавцов и покупателей (если они были), с которыми они заключали сделки, если суммарная стоимость этих сделок > 1000.

Вложенные запросы с кванторами + where

3. Вывести продавцов, в сделках которых со стоимостью > 1000 продавалось только мыло.

Вложенные запросы с кванторами + having

4. Вывести продавцов, в сделках которых с суммарной стоимостью > 1000, сгруппированным по товарам, продавалось только мыло.

Псевдонимы, рекурсивная связь к 1 табл + where

5. Вывести всех подчиненных Иванова, если они заключили хотя бы 1 сделку со стоимостью > 1000.

Псевдонимы, рекурсивная связь к 1 табл + having

6. Вывести тех подчиненных Иванова, если суммарная стоимость сделок у подчиненного > 1000.

Псевдонимы, связь одной таблицы с собой через вторую + where

7. Вывести всех продавцов, продававших покупателям Иванова, если они заключили хотя бы 1 сделку со стоимостью > 1000.

Псевдонимы, связь одной таблицы с собой через вторую + having

8. Вывести всех продавцов, продававших покупателям Иванова, если суммарная стоимость сделок у продавца > 1000.

Вложенный запрос в having + where

9. Вывести тех продавцов, у которых средняя стоимость сделок больше, чем средняя стоимость сделок c количеством товара > 10 по их отделу.

Вложенный запрос в select + where

10. Вывести разницу между средней стоимостью сделок данного продавца и средней стоимостью сделок c количеством товара > 10 его отдела.

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

11. Вывести всех продавцов, продававших покупателям Иванова, у которых средняя стоимость сделок больше, чем средняя стоимость сделок по их отделу.

12. Вывести всех подчиненных Иванова, у которых средняя стоимость сделок больше, чем средняя стоимость сделок по их отделу.

Псевдонимы + вложенные запросы с кванторами

13. Вывести всех подчиненных Иванова, которые продавали только мыло.

14. Вывести всех продавцов, продававших покупателям Иванова, которые продавали только мыло.

Уровень 6

Псевдонимы + вложенные запросы во from + union

1. Для каждого продавца и его подчиненных вывести максимальную из минимальных стоимостей сделок по каждой дате.

2. Для каждого продавца и его подчиненных вывести максимальную из средних стоимостей сделок по каждой дате.

3. Для каждого продавца и его подчиненных вывести среднюю из минимальных стоимостей сделок по каждой дате.

4. Для каждого продавца и его подчиненных вывести среднюю из максимальных стоимостей сделок по каждой дате.