4.2.1 Текущие цены

Довольно часто в запросах к "Корпорации Кинга" нам необходимо выбирать цены на товары, действующие в настоящее время. Для упрощения работы можно создать представление, выбирающее только текущие цены:

    CREATE VIEW current_price 
      (product_id, list_price, min_price, start_date)
     AS SELECT product_id, list_price, min_price,start_date
         FROM price
         WHERE end_date IS NULL

4.2.2 Размещение отделов

В "Корпорации Кинга" полная внешняя идентификация отдела может быть обеспечена только указанием названия отдела и города, в котором находится отдел, так как в одном городе может быть несколько отделов, а названия отделов в разных городах могут повторяться. Поскольку запросы часто требуют полной идентификации отдела, в таких запросах приходится выполнять естественное соединение таблиц location и department. Такое соединение можно оформить в виде представления:

    CREATE VIEW dep_loc AS 
       SELECT department_id,department.location_id, name,regional_group
          FROM department, location
          WHERE location.location_id = department.location_id

4.2.3 Информация о сотрудниках

Полную информацию о сотрудниках можно представить следующим образом:

    CREATE VIEW employee_plus 
     (employee_id, last_name, first_name, middle_initial, function, 
      manager_id, hire_date, salary, commission, dep_name, loc_name)
     AS SELECT employee_id, last_name, first_name, middle_initial,
               function, manager_id, hire_date, salary, commission, name, regional_group
         FROM employee, job, dep_loc
         WHERE employee.job_id = job.job_id
         AND employee.department_id = deploc.department_id

4.2.4 Расширенная информация об отделах

Вот представление, содержащее расширенную информацию об отделах: название и место расположения отдела, число сотрудников отдела, минимальную, максимальную и среднюю зарплату в отделе, фонд зарплаты отдела и идентификатор менеджера отдела. В это представление включаются также и отделы без менеджеров, и отделы, вообще не содержащие сотрудников. В данным примере использованы средства Oracle для внешнего соединения.

    CREATE VIEW department_plus
         (department_id, dep_name, loc_name, emp_cnt, 
          min_salary, max_salary, avg_salary, sum_salary, mng_id)
     AS 
      SELECT dep_loc.department_id, name, regional_group, emp_cnt, 
             min_salary, max_salary, avg_salary, sum_salary, employee_id
      FROM (
       SELECT t1.department_id, emp_cnt, min_salary, 
              max_salary, avg_salary, sum_salary, employee_id
        FROM
         (SELECT department_id, COUNT(*) AS emp_cnt, 
                 MIN(salary) AS min_salary, MAX(salary) AS max_salary, 
                 AVG(salary) AS avg_salary, SUM(salary) AS sum_salary
          FROM employee
          GROUP BY department_id) t1,
         (SELECT department_id, employee_id
           FROM employee, job
           WHERE employee.job_id = job.job_id
           AND function='MANAGER' ) t2
        WHERE t2.department_id (+) = t1.department_id
       UNION 
       SELECT department_id, TO_NUMBER(NULL) AS emp_cnt, TO_NUMBER(NULL) AS min_salary, 
     TO_NUMBER(NULL) AS max_salary, TO_NUMBER(NULL) AS avg_salary, 
     TO_NUMBER(NULL) AS sum_salary, TO_NUMBER(NULL) AS employee_id
       FROM department
       WHERE department_id NOT IN
         (SELECT DISTINCT department_id 
           FROM employee) )t3,
       dep_loc
     WHERE dep_loc.department_id = t3.department_id

4.2.5 Запросы к представлениям

Ниже мы приводим решения некоторых задач из разделов 3.3 и 3.6, выполненные с учетом представлений, созданных в предыдущих пунктах

4.2.5.1 Для каждого сотрудника вывести его имя, должность, название отдела и город, в котором он работает.

    SELECT last_name, function, dep_name, loc_name
       FROM employee_plus

4.2.5.2 Выбрать фамилии тех сотрудников, которые обслуживают покупателей, не являясь продавцами (название должности - не 'SALESPERSON').

    SELECT last_name
      FROM employee_plus, customer
      WHERE salesperson_id=employee_id
      AND function><'SALESPERSON'

4.2.5.3 Вывести список штатов, в которых находятся покупатели, обслуживаемые отделами, расположенными в городе NEW YORK.

    SELECT DISTINCT state
       FROM customer, employee_plus
       WHERE salesperson_id = employee_id
       AND loc_name = 'NEW YORK'

Или:

    SELECT DISTINCT state
      FROM customer
      WHERE salesperson_id IN
        (SELECT employee_id 
          FROM employee_plus
          WHERE loc_name = 'NEW YORK' )

4.2.5.4 Определить те товары, которых сотрудник JONES может купить на свою зарплату больше 1000 штук.

    SELECT description, FLOOR(salary/min_price)
      FROM current_price, product, employee
      WHERE current_price.product_id=product.product_id
      AND last_name='JONES'
      AND min_price*1001>=salary
      ORDER BY 2 DESC

4.2.5.5 Выбрать в каждом отделе самого высокооплачиваемого сотрудника

    SELECT dep_name, loc_name, last_name, salary
      FROM employee, department_plus
      WHERE employee.department_id = department_plus.department_id
      AND salary=max_salary

4.2.5.6 Для каждого отдела определить процентное отношение средней заработной платы рядовых сотрудников к зарплате менеджера этого отдела.

    SELECT department_plus.department_id,
       (sum_salary-salary)/(emp_cnt-1)/salary*100
     FROM employee, department_plus
     WHERE employee_id = mng_id
     AND emp_cnt<1

Последнее решение требует некоторых пояснений. В представлении department_plus имеются данные о суммарной зарплате по отделу, о количестве сотрудников в отделе и идентификатор менеджера отдела. Мы выполняем естественное соединение таблицы employee с представлением department_plus для того, чтобы включить в выборку зарплату менеджера отдела. Алгебраическое выражение в списке выборки вычисляет среднюю зарплату без учета зарплаты менеджера. Дополнительное условие в фразе WHERE исключает отделы, состоящие из одного сотрудника, так как для этих отделов запрос не имеет смысла, а выполнение алгебраического выражения приведет к ошибке "деление на 0".