SQL: быстрое погружение - [42]
В результате выполнения данного запроса мы получим среднее значение $5,80. Теперь необходимо написать внешний запрос для выбора счетов, превышающих средний показатель за 2010 год.
SELECT
InvoiceDate,
Total
FROM
invoices
WHERE
Total >
(SELECT
avg(total)
from
invoices
where
InvoiceDate between '2010-01-01' and '2010-12-31')
ORDER BY
Total DESC
Рис. 142
В результате выполнения данного запроса получено 179 строк.
Примечание
Если бы требовалось получить только фактическое количество возвращенных счетов-фактур, во внешнем запросе можно было бы изменить поле Total, указав COUNT(Total).
Вопрос 2. Какие клиенты получили эти счета?
Решение. Чтобы связать данные о клиентах из таблицы customers с таблицей invoices, необходимо использовать повторное объединение. Сам вопрос подразумевает однозначную связь между таблицей customers и таблицей invoices. Мы уже выбрали интересующие нас счета, поэтому теперь нам необходимо получить информацию о клиентах, которым были выставлены эти счета. При решении данного вопроса воспользуемся внутренним соединением. Это решение очень похоже на решение вопроса 1. Все, что мы добавили, — это раздел внутреннего соединения, поэтому у нас также имеется доступ к именам клиентов.
SELECT
i. InvoiceDate,
i. Total,
c. FirstName,
c. LastName
FROM
invoices i
INNER JOIN
customers c
ON
i. CustomerId = c.CustomerId
WHERE
Total >
(SELECT
avg(total)
from
invoices
where
InvoiceDate between '2010-01-01' and '2010-12-31')
ORDER BY
Total DESC
Вопрос 3. Сколько клиентов живут в США?
Решение. Мы можем изменить решение вопроса 2, включив оператор AND в конец условия WHERE внешнего запроса.
SELECT
InvoiceDate,
Total,
BillingCountry
FROM
invoices
WHERE
Total >
(SELECT
avg(total)
from
invoices
where
InvoiceDate between '2010-01-01' and '2010-12-31')
AND BillingCountry = 'USA'
ORDER BY
Total DESC
Рис. 143
В результате выполнения данного запроса получено 40 строк.
Примечание
При необходимости получения точного количества результатов можно использовать функцию SUM().
Глава 9. Контрольные вопросы
В данном разделе необходимо преобразовать запрос, в котором сравнивается средняя сумма счетов по городу со средним глобальным показателем, в представление.
SELECT
BillingCity,
AVG(Total) AS [City Average],
(select
avg(total)
from
invoices) AS [Global Average]
FROM
invoices
GROUP BY
BillingCity
ORDER BY
BillingCity
Вопрос 1. Из запроса SELECT возьмите внутренний запрос и создайте из него представление. Сохраните его с именем V_GlobalAverage.
Если вы выполняли примеры из главы, возможно, вы уже сохранили функцию, рассчитывающую среднее значение, в качестве представления. Убедитесь, что новому представлению присвоено новое имя.
Решение. Во внутреннем запросе в первую строку добавим синтаксис представления.
CREATE VIEW V_GlobalAverage AS
select
avg(total)
from
invoices AS [Global Average]
Вопрос 2. Удалите подзапрос из приведенного выше кода и замените его вновь созданным представлением V_GlobalAverage.
Решение. При использовании представления в условии SELECT мы используем символ *.
SELECT
BillingCity,
AVG(Total) AS [City Average],
(select
*
from
V_GlobalAverage) AS [Global Average]
FROM
invoices
GROUP BY
BillingCity
ORDER BY
BillingCity
Вопрос 3. Сохраните этот новый запрос как представление с именем V_CityAvgVsGlobalAvg.
Решение. Копируем код из вопроса 2 и в верхнюю часть запроса добавляем оператор CREATEVIEW.
CREATE VIEW V_CityAvgVsGlobalAvg AS
SELECT
BillingCity,
AVG(Total) AS [City Average],
(select
*
from
V_GlobalAverage) AS [Global Average]
FROM
invoices
GROUP BY
BillingCity
ORDER BY
BillingCity
Вопрос 4. Удалите представление V_GlobalAverage. Как будет работать V_CityAvgVsGlobalAvg?
Решение. Чтобы удалить наше представление, воспользуемся условием DROPVIEW. Кроме того, на вкладке Database Structure (Структура базы данных) в DB Browser мы можем щелкнуть правой кнопкой мыши по представлению и удалить его.
DROP VIEW V_GlobalAverage
Теперь, чтобы увидеть, как это повлияет на наши предыдущие операторы, необходимо написать условие SELECT для выбора виртуальной таблицы.
V_CityAvgVsGlobalAvg
SELECT
*
FROM
V_CityAvgVsGlobalAvg
В результате мы получим следующее сообщение об ошибке:
no such table: main.V_GlobalAverage:
(таблица не существует: main.V_GlobalAverage:)
Глава 10. Контрольные вопросы
Вопрос 1. В таблицу customers добавьте новую запись.
Решение. Сначала необходимо добавить новую запись в таблицу customers. Клиент может существовать сам по себе, не будучи упомянутым в какой-либо другой таблице (если он еще не совершил покупку). Для начала вставьте запись в таблицу customers.
INSERT INTO
customers
VALUES ('60', 'New', 'Customer', '', '123 Day Street', 'New York', 'NY', 'USA', '11201', '(347) 525-8688', '', '[email protected]', '1');
Примечание
Некоторые поля оставим пустыми, поставив рядом две одинарные кавычки. Запустим запрос на выполнение и проанализируем результаты.
SELECT
*
FROM
customers
WHERE
FirstName = 'New'
Примечание
Если для нового клиента вы использовали другое имя, измените это значение в запросе соответствующим образом.
Рис. 144
Вопрос 2. Создайте счет для этого клиента.
Решение. Чтобы создать запись счета для нашего нового клиента, нам необходимо обратить особое внимание на поля в таблице invoices, которые соответствуют таблице customers. Например, в наших счетах используется тот же адрес, который отображается в таблице customers.
