Пример 2. Использование подзапросов с групповыми функциями

Получите список всех сотрудников в США, имеющих зарплату больше чем средняя, указав, насколько их зарплата превышает среднюю зарплату сотрудников в США.

Поскольку все необходимые данные находятся в таблице employee, обратимся к ней. Групповые функции возвращают одно значение. Следовательно, запросы с их использованием можно использовать как литералы.

Нельзя непосредственно включить в запрос выражение WHERE salary > AVG (salary), поскольку применять группирующие функции в конструкции WHERE нельзя. Для достижения желаемого результата следует создать подзапрос, вычисляющий среднее значение заработной платы, а затем использовать его во внешней инструкции SELECT, предназначенной для выборки сведений о тех сотрудниках компании, чья зарплата превышает найденное среднее значение. Иначе говоря, подзапрос возвращает значение средней зарплаты по стране. Обратите внимание на то, что при построении запроса трижды выполнен отбор по названию страны.

 

SELECT first_name, last_name, salary,  salary - (SELECT AVG(salary) FROM employee WHERE job_country = 'USA') AS salary_diff  FROM employee  WHERE salary > (SELECT AVG(salary) FROM employee WHERE job_country = 'USA')  AND job_country = 'USA'

 

FIRST_NAME   LAST_NAME      SALARY     SALARY_DIFF

==========   ============   ========   ===========

Robert       Nelson         105900      34831,0814

Bruce        Young          97500       26431,0814

Kim          Lambert        102750      31681,0814

Phil         Forest         75060        3991,0814

K. J.        Weston         86292,94    15224,0189

Chris        Papadopoulos   89655       18586,0814

Pete         Fisher         81810,19    10741,2689

Ashok        Ramanathan     80689,5      9620,5814

Walter       Steadman       116100      45031,0814

Mary S.      MacDonald      111262,5    40193,5814

Oliver H.    Bender         212850     141781,0810

Kevin        Cook           111262,5    40193,5814

Пример 3. Вложенные подзапросы с использованием предложения in

Получите номера контрактов продаж и суммы к оплате, в расчетах с которыми используется слово "Franc".

Данные о названиях валют хранятся в таблице country (страна). Самый внутренний запрос предназначен для определения названий стран в названиях валют которых используется слово Franc. Второй, промежуточный, запрос осуществляет выборку номеров клиентов из таблицы customer (заказчик), в расчетах с которыми используется соответствующая валюта. В данном случае может быть выбрано несколько строк. Поэтому во внешнем запросе нельзя использовать операцию сравнения. Вместо него необходимо использовать предложение IN. Внешний запрос осуществляет выборку данных из таблицы sales (продажи) о договорах продаж, данные о которых были получены данные в результате выполнения промежуточного запроса.

SELECT po_number, total_value    FROM sales    WHERE cust_no IN      (SELECT cust_no FROM customer WHERE country IN           (SELECT country FROM country WHERE currency LIKE ‘%Franc%’))

PO_NUMBER   TOTAL_VALUE    =========   ===========    V92F3004    2000           V93F3088    10000          V93F2030    450000,49      V93F2051    999,98         V93S4702    120000         V94S6400    1980,72        V93B1002    100,02