SQL: быстрое погружение - [41]
ELSE 'Foreign Sales'
END AS SalesType
FROM
invoices
Рис. 135
Вопрос 2. Отсортируйте эти данные по новому полю SalesType.
Решение. Чтобы отобразить все внутренние продажи в одной группе и все зарубежные продажи в другой группе, необходимо к уже существующему запросу добавить условие ORDERBY (используя новое поле):
SELECT
InvoiceDate,
BillingAddress,
BillingCity,
BillingCountry,
Total,
CASE
WHEN BillingCountry = 'USA' THEN 'Domestic Sales'
ELSE 'Foreign Sales'
END AS SalesType
FROM
invoices
ORDER BY
SalesType
На рис. 136 показаны результаты данного запроса, где сначала отображаются Domestic Sales (Продажи на внутреннем рынке). Если вы запустите этот запрос и прокрутите вниз, вы увидите, что все страны, кроме США, отмечены как Foreign Sales (Продажи за рубежом).
Рис. 136
Вопрос 3. Сколько счетов от продаж на внутреннем рынке превышают сумму $15?
Решение. Чтобы включить числовые и текстовые параметры, в существующий запрос можно добавить условия WHERE и AND.
SELECT
InvoiceDate,
BillingAddress,
BillingCity,
BillingCountry,
Total,
CASE
WHEN BillingCountry = 'USA' THEN 'Domestic Sales'
ELSE 'ForeignSales'
END AS SalesType
FROM
invoices
Where
SalesType = «Domestic Sales» AND Total > 15
Рис. 137
Глава 6. Контрольные вопросы
Вопрос 1. Используя DB Browser и вкладку Browse Data (Просмотр данных) или ER-диаграмму (рис. 65), проанализируйте таблицу tracks. Определите, какие поля в этой таблице будут внешними ключами в другой таблице. На основании определенных вами внешних ключей определите, какие таблицы связаны с таблицей tracks.
Решение. Анализируя таблицу tracks, мы видим три поля с целочисленными значениями, которые могут быть внешними ключами.
Поля AlbumId, MediaTypeId и GenreId соответствуют таблицам albums, media_types и genres соответственно.
Рис. 138
Вопрос 2. Создайте внутреннее соединение между таблицами albums и tracks и отобразите названия альбомов и названия треков в едином наборе результатов.
Решение:
SELECT
t. composer AS "Artist Name",
a. title AS "Album Title",
t. Name AS "Track Name"
FROM
albums a
INNER JOIN
tracks t
ON
a. AlbumId = t.AlbumId
Вопрос 3. Создайте третье внутреннее соединение — с таблицей genres, которую вы нашли, отвечая на вопрос 1. Включите в ваш набор результатов поле Name из этой таблицы.
Решение:
SELECT
g. name AS Genre,
t. composer AS "Artist Name",
a. title AS "Album Title",
t. Name AS "Track Name"
FROM
albums a
INNER JOIN
tracks t
ON
a. AlbumId = t.AlbumId
INNER JOIN
genres g
ON
g. GenreId = t.GenreId
Глава 7. Контрольные вопросы
Вопрос 1. Создайте однострочный список рассылки для всех клиентов из США, включая полные имена, написанные заглавными буквами, и полные адреса с пятизначными почтовыми индексами, в следующем формате:
FRANK HARRIS 1600 Amphitheatre Parkway, Mountain View, CA 94043
Решение. В приведенном выше формате имя и фамилия должны быть написаны в верхнем регистре, поэтому для этих двух полей мы воспользуемся функцией UPPER(). Для объединения остальных полей используем символ ||, добавляя, где необходимо, пробелы и запятые.
SELECT
UPPER(FirstName) || ' ' || UPPER(LastName) || ' '
|| Address || ', ' || City || ', ' || State || ' '
|| SUBSTR(PostalCode,1,5) AS [MailingAddress]
FROM
customers
WHERE
Country = 'USA'
Рис. 139
Вопрос 2. Каковы средние годовые продажи клиентам из США согласно имеющимся данным за все годы?
Решение. Если мы просто ищем агрегатную функцию для одной страны, то можем выбрать страну, где был выставлен счет, и среднее значение от общей суммы, используя условие WHERE, чтобы ограничить наши результаты для одной страны — США.
SELECT
BillingCountry,
AVG(Total)
FROM
invoices
WHERE
BillingCountry = 'USA'
Рис. 140
Напоминание
Чтобы сократить количество возвращаемых знаков после запятой, мы можем использовать функцию ROUND() вне функции AVG().
Вопрос 3. Каков общий объем продаж компании за все время?
Решение. Поскольку в данном запросе задается общая сумма счетов, условие SELECT выглядит довольно просто:
SELECT
SUM(Total)
FROM
invoices
Рис. 141
Вопрос 4. Кто входит в десятку лучших клиентов с точки зрения совершенных ими покупок? Подсказка: чтобы ответить на этот вопрос, необходимо использовать соединение (глава 6).
Решение. Значение общей суммы найдено. Теперь необходимо отобразить первую десятку клиентов, приносящих наибольшую прибыль. Поскольку мы ищем данные из одной таблицы, которые соответствуют данным из другой таблицы во взаимно однозначном отношении, мы используем внутреннее соединение.
SELECT
SUM(Total)AS [Revenue Total],
c. FirstName,
c. LastName
FROM
invoices i
INNER JOIN
customers c
ON
i. CustomerId = c.CustomerId
GROUP BY c.CustomerId
ORDER BY SUM(Total) DESC
Глава 8. Контрольные вопросы
Вопрос 1. Сколько счетов превышает среднюю сумму счетов, выставленных в 2010 году?
Решение. Чтобы ответить на этот вопрос, необходимо решить две задачи. Во-первых, следует найти среднюю сумму счета-фактуры, сгенерированную в 2010 году. Во-вторых, необходимо сравнить это значение с каждым счетом в таблице, чтобы увидеть, сколько из них превышает среднюю стоимость счета-фактуры за 2010 год.
Сначала напишем следующий подзапрос:
select
avg(total)
from
invoices
where
InvoiceDate between '2010-01-01' and '2010-12-31'
