Часть 4. Многотабличные запросы (выборка из двух таблиц, выборка из трех таблиц с использованием JOIN)
Задание: Вывести список названий компаний-поставщиков (поле Name_company) и названия книг (поле Title_book), которые они поставили в период с 01.01.2002 по 31.12.2003 (условие по полю Date_order).
SELECT books.title_book, deliveries.name_company
FROM(books INNERJOIN purchases
ON books.code_book=purchases.code_book)INNERJOIN deliveries
ON purchases.code_delivery=deliveries.code_delivery
WHERE purchases.date_order BETWEEN'01.01.2010'AND'31.12.2011'
Самостоятельно:
· Вывести список авторов (поле Name_author), книги которых были выпущены в издательстве ‘Лань’ (условие по полю Publish).
· Вывести список поставщиков (поле Name_company), которые поставляют книги издательства ‘Питер’ (условие по полю Publish).
· Вывести список авторов (поле Name_author) и названия книг (поле Title_book), которые были поставлены поставщиком ‘ОАО Книготорг’ (условие по полю Name_company).
Часть 5. Вычисления
Рассмотрим вычисления с использованием агрегатных функций
Агрегатные функции – это функции столбца, предназначенные для того, чтобы вычислять некоторое значение для заданного множества строк. Таким множеством строк может быть группа строк, если агрегатная функция применяется к сгруппированной таблице, или вся таблица. Если запросом определено условие WHERE, то для вычисления используются только значения выбранных таким образом строк (то есть тех записей, которые удовлетворяют условию WHERE).
Рассмотрим следующие агрегатные функции:
· SUM – возвращает сумму значений столбца группы записей;
· COUNT – возвращает количество записей группы;
· AVG – возвращает среднее значение столбца группы записей;
· MIN – возвращает минимальное значение столбца группы записей;
· MAX – возвращает максимальное значение столбца группы записей;
Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением: они берут имя поля как аргумента. Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые, и символьные поля.
Задание: Определим количество поставщиков книг
SELECTCOUNT(code_delivery)
FROM Deliveries
Задание: Определить количество книг, имеющих 300 страниц
SELECTCOUNT(code_book)
FROM Books
WHERE Pages >=300
Предложение GROUP BY используется для определения группы выходных строк, к которым могут применяться агрегатные функции.
Задание: Определить количество книг, соответствующих определенным авторам
SELECT code_author,COUNT(code_book)
FROM Books
WHERE Pages >=300
GROUPBY code_author
Предложение HAVING определяет критерии отбора для групп записей, является аналогом предложения WHERE, которое определяет критерии отбора для индивидуальных строк. В запросе SQL предложение HAVING следует за предложением GROUP BY. Пример. Вывести коды авторов, написавших более одной книги
SELECT code_author,COUNT(code_book)
FROM Books
WHERE Pages >=300
GROUPBY code_author
HAVINGCOUNT(code_book)>1
Самостоятельно:
· Вывести суммарную стоимость партии одноименных книг (использовать поля Amount и Cost) и название книги (поле Title_book) в каждой поставке.
· Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages) и названия соответствующих книг (поле Title_book).
· Вывести количество лет с момента рождения авторов (использовать поле Birthday) и имена соответствующих авторов (поле Name_author).
Вычисление итоговых значений с использованием агрегатных функций
Задание: Вывести общую сумму поставок книг (использовать поле Cost),выполненных `Спектр` (условие по полю Name_company).
USE DB_book
SELECTSUM(purchases.cost*purchases.amount)ASVALUE
FROM deliveries INNERJOIN purchases
ON deliveries.code_delivery = purchases.code_delivery
WHERE deliveries.name_company ='Спектр'
Самостоятельно:
· Вывести общее количество всех поставок (использовать любое поле из таблицы Purchases), выполненных в период с 01.01.2010 по 01.02.2013 (условие по полю Date_order).
· Вывести среднюю стоимость (использовать поле Cost) и среднее количество экземпляров книг (использовать поле Amount) в одной поставке, где автором книги является `Акунин` (условие по полю Name_author).
· Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с минимальной общей стоимостью (использовать поля Cost и Amount).
· Вывести все сведения о поставке (все поля таблицы Purchases), а также название книги (поле Title_book) с максимальной общей стоимостью (использовать поля Cost и Amount).
Изменение наименований полей
Задание: Вывести название книги (поле Title_book), суммарную стоимость партии одноименных книг (использовать поля Amount и Cost), поместив в результат в поле с названием Itogo, в поставках за период с 01.01.2010 по 01.06.2012 (условие по полю Date_order).
USE DB_book
SELECT books.title_book, purchases.cost*purchases.amount AS Itogo
FROM books INNERJOIN purchases
ON books.code_book = purchases.code_book
WHERE purchases.date_order BETWEEN'01.01.2010'AND'01.06.2012'
Самостоятельно:
· Вывести стоимость одной печатной страницы каждой книги (использовать поля Cost и Pages), поместив результат в поле с названием One_page, и названия соответствующих книг (поле Title_book).
· Вывести общую сумму поставок книг (использовать поле Cost) и поместить результат в поле с названием Sum_cost, выполненных ‘ОАО Луч’ (условие по полю Name_company).