SQL: быстрое погружение

Решение. Мы могли бы ответить на этот вопрос, прокручивая данные в разделе Browse Data (Просмотр данных), но есть более эффективный способ. Используя условие SELECT, мы можем перечислить все данные в порядке возрастания. Но на этот раз нам необходимо перечислить более одного столбца, чтобы мы могли видеть, каким именам клиентов не соответствуют данные почтового индекса. Таким образом, мы выберем поля FirstName, LastName и PostalCode, а затем отсортируем результаты по PostalCode.

SELECT

FirstName,

LastName,

PostalCode

FROM

customers

ORDER BY

PostalCode

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

Рис. 134

Примечание

Если поля перечислить в порядке убывания, придется прокрутить вниз полосу прокрутки, чтобы увидеть нулевые значения.

Глава 5. Контрольные вопросы

Вопрос 1. Создайте запрос для таблицы invoices, включающий оператор CASE, который будет отмечать все продажи из США — страны, откуда выставлен счет, как Domestic Sales (Продажи на внутреннем рынке), а все другие продажи — как Foreign Sales (Продажи за рубежом). После оператора ENDAS создайте новое поле SalesType.

Решение. Чтобы отобразить эту информацию, необходимо применить фильтрацию, используя оператор CASE. Поскольку мы классифицируем наш оператор CASE по стране, где был выставлен счет, необходимо добавить это поле в условие SELECT.

SELECT

InvoiceDate,

BillingAddress,

BillingCity,

BillingCountry,

Total,

CASE

WHEN BillingCountry = 'USA' THEN 'Domestic Sales'

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. Каковы средние годовые продажи клиентам из США согласно имеющимся данным за все годы?