Примеры запросов и создания таблиц SQL. Часть 2

Задание 9.
Вывести средний и общий объём продаж книг по типу бизнеса.

	SELECT AVG(titles.sales), SUM(titles.sales)
	FROM titles
	WHERE type='Business';

view raw Ex9.sql hosted with ❤ by GitHub

Задание 10. Вывести идентификаторы авторов c количеством написанных книг, в том числе и в соавторстве.

	SELECT title_authors.au_id, COUNT(*)
	FROM title_authors
	GROUP BY au_id;

view raw Ex10.js hosted with ❤ by GitHub

Фильтрация групп с помощью предложения HAVING

Очерёдность с которой СУБД применяет предложения WHERE, GROUP BY и HAVING имеет следующий вид:
1. Предложение WHERE фильтрует строки, которые являются результатом работы операций обозначенных предложениями FROM и JOIN.
2. Предложение GROUP BY группирует выход предложения WHERE.
3. Предложение HAVING фильтрует строки сгруппированного результата. Изменим предыдущий запрос следующим образом: "Найти авторов, которые написали не менее трёх книг". Необходимо добавить: HAVING COUNT(*)>=3
Задание 11. Перечислить для издателей P03 и P04 общий объём продаж, выпущенных книг с разбивкой по типам. Учесть только те книги, где итоговый объём продаж превышает 10000 копий при средней цене одной книги менее 20$.

	SELECT type, SUM(sales), AVG(price)
	FROM titles
	WHERE pub_id in ('P03','P04')
	GROUP BY type
	HAVING (SUM(sales)>10000) AND AVG(price)<20;

view raw Ex11.sql hosted with ❤ by GitHub

Изменение таблиц

Соединение двух и более таблиц можно рассматривать как процесс сравнения данных в указанных столбцах этих таблиц и формирования новой таблицы из строк исходных таблиц, которые дают положительный результат при сравнении. Оператор JOIN сравнивает данные в указанных столбцах каждой таблицы строка за строкой и компонует из строк прошедших сравнение новые строки. Обычно в качестве операции сравнения выступает равенство, т.е. данные сравниваются на полное совпадение, но возможны и другие типы соединения. Имеется несколько разновидностей соединений:
1) соединения с равенством (экви-соединения);
2) естественные соединения (natural);
3) внешние соединения.
Наиболее часто встречающейся разновидностью соединений являются соединения основанные на равенстве.
Пример 1. Найти имена авторов и издателей, живущих в одном и том же городе.

	SELECT authors.au_fname, authors.au_lname, authors.city, publishers.pub_name, publishers.city
	FROM authors, publishers
	WHERE authors.city=publishers.city;

view raw Pr1.sql hosted with ❤ by GitHub

При выполнении соединения, сначала образуется декартово произведение таблиц, т.е. образование всех возможных комбинаций строк этих таблиц друг с другом. Декартово произведение не содержит какой-либо полезной информации, по этой причине соединение должно включать предложение WHERE, которое отбирает связанные между собой строки и указывает как именно они должны быть связанны. Условие соединение таблиц не обязательно является равенством. Можно использовать любую другую операцию сравнения.
Пример 2. Найти авторов, которые публиковались издательством New Age Books и которые живут в штатах названия которых больше, чем название штатов Массачусетс, в алфавитном порядке.

	SELECT pub_name, publishers.state, au_lname, au_fname, authors.state
	FROM publishers, authors
	WHERE authors.state>publishers.state AND pub_name="New Age Books";

view raw Pr2.sql hosted with ❤ by GitHub

Можно соединять между собой столбцы одной и той же таблицы с помощью самосоединения SELF-JOIN.
Пример 3. Найти авторов, живущих в городе Оукленде штата Калифорния в одном и том же почтовом округе.
Т.к. этот запрос включает столбцы одной таблиц автора, то эта таблица может выступить в двух ролях, чтобы различить эти роли, необходимо временно присвоить ей в предложении FROM различные названия, такие как au1, au2. Эти согласующиеся названия будут использоваться для уточнения названий столбцов в следующем запросе.

	SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
	FROM authors au1, authors au2
	WHERE au1.city="Oakland" AND au2.city="Oakland" AND au1.state="CA" AND au2.state="CA" AND au1.zip=au2.zip;

view raw Pr3.sql hosted with ❤ by GitHub

Для уточнения необходимо добавить дополнительное условие.
Пример 4. Найти названия всех книг типа "Современная кулинария". и имена их авторов.

	Select authors.au_fname, authors.au_lname, titles.title_name
	From title, authors, title_authors
	Where titles.type=”mod_cook” and authors.au_id=title_author.au_id and titles.title_id=title_authors.title.id

view raw Pr4.sql hosted with ❤ by GitHub