SQL: быстрое погружение — страница 22 из 24

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'

В результате выполнения данного запроса мы получим среднее значение $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', '', 'nc@gmail.com', '1');

Примечание

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

SELECT

*

FROM

customers

WHERE

FirstName = 'New'

Примечание

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

Рис. 144

Вопрос 2. Создайте счет для этого клиента.

Решение. Чтобы создать запись счета для нашего нового клиента, нам необходимо обратить особое внимание на поля в таблице invoices, которые соответствуют таблице customers. Например, в наших счетах используется тот же адрес, который отображается в таблице customers.

INSERT INTO

invoices

VALUES ('413', '60', '2019-10-04 00:00:00', '123 Day Street', 'New York', 'NY', 'USA', '10201', '50.00')

Вопрос 3. Удалите этого клиента из базы данных.

Решение. Как мы упоминали в главе 10, рекомендуется просмотреть данные, которые необходимо удалить. В этом случае данные, которые мы удаляем, содержатся в двух таблицах, поэтому для просмотра всех необходимых нам данных мы воспользуемся условием INNERJOIN.

SELECT

c. FirstName,

c. LastName,

i. Total,

i. InvoiceId

FROM

invoices i

INNER JOIN

customers c

ON i.CustomerId = c.CustomerId

WHERE c.CustomerId = 60

Теперь, когда мы подтвердили данные, мы можем использовать оператор DELETE.