Рисунок 3.24 - Пример структуры подчиненности
| Каталог | Индекс раздела |
| Назад | Оглавление | Вперед |
В этом разделе мы приводим материалы для практической работы - задачи на выборку данных из базы данных "Корпорация Кинга". Несколько общих замечаний к приводимым задачам:
/* и */, в DB2 комментарием является строка, начинающаяся с символов --.
3.6.1.1 Для каждого сотрудника вывести его имя, должность, название отдела и город, в котором он работает.
Информация, которая должна быть выбрана в запросе, рассредоточена по таблицам: employee, job, department и location. Для сведения этой информации вместе нужно установить соединение этих таблиц. Как видно из концептуальной схемы базы данных (см. Приложение 1), указанные таблицы связаны через первичные-внешние ключи.
SELECT last_name, function, name, regional_group
FROM employee, job, department, location
WHERE employee.job_id = job.job_id
AND employee.department_id = department.department_id
AND department.location_id = location.location_id
DB2 допускает и другую реализацию соединения:
SELECT last_name, function, name, regional_group
FROM employee
JOIN job ON employee.job_id = job.job_id
JOIN department ON employee.department_id = department.department_id
JOIN location ON department.location_id = location.location_id
В дальнейшем мы, впрочем, не будем приводить решения с операцией JOIN как альтернативные, так как они ничего не добавляют к логике построения запроса.
3.6.1.2 Выбрать названия тех товаров, для которых в 1989 г. допускались скидки больше 20%.
Сначала приведем общие соображения по поводу того, как сформулировать условия ограничения выборки.
Для каждого товара имеется заявленная цена и минимально допустимая цена. Условие скидки больше 20% может быть сформулировано, например, как: разность между заявленной и минимальной ценой составляет 0.2 от заявленной цены - (list_price-min_price)/list_price>0.2 или: минимальная цена составляет 0.8 от заявленной цены - min_price<list_price*0.8.
Следует внимательно отнестись к ограничению по времени. Для цен имеется также дата начала действия цены и дата окончания ее действия. Под определение "в 1989 г." попадают не только те цены, которые были установлены в течение 1989 г., но также и те, которые были установлены ранее и не были отменены до начала 1989 г. Ограничение по времени может быть сформулировано как: начало действия цены - до 31 декабря 1989 г., окончание действия цены - после 1 января 1989 г. Однако следует иметь в виду то, что цена может действовать и в настоящее время, поэтому точная формулировка условия для окончания действия цены - после 1 января 1989 г. или не определена.
Первый вариант решения базируется на "реляционном" стиле мышления. Названия товаров находятся в таблице product, цены - в таблице price. Между таблицами имеется непосредственная связь по ключу product_id. Значит, следует выполнить естественное соединение этих двух таблиц, а затем - ограничение по рассмотренным выше условиям. Реализацию этого решения мы приводим, используя формат представления даты Oracle:
SELECT description,list_price,min_price
FROM product,price
WHERE product.product_id = price.product_id
AND min_price<list_price*0.8
AND start_date<'31-DEC-1989'
AND (end_date >'01-JAN-1989' OR end_date IS NULL);
Второй вариант базируется на "процедурном" стиле мышления. Сформулированные выше ограничения относятся к данным, находящимся в таблице price. Поэтому произведем сначала выборку из таблицы price с ограничением по строкам, удовлетворяющим нашим условиям, и с проекцией по столбцам, которые понадобятся для дальнейшей обработки (product_id, list_price, min_price). Эта выборка составит временную таблицу (назовем ее x), в которой фактически будет содержаться почти полный ответ на запрос. "Почти" - потому что вместо названия товара в таблице x будет содержаться его код. Название содержится в таблице product. Для того, чтобы выбрать название, нужно соединить строки таблицы x с теми строками таблицы product, которые имеют такое же значение в столбце product_id, и выбирать из получившейся в результате соединения таблицы description вместо product_id. Для разнообразия в реализации второго решения мы используем формат представления даты DB2:
SELECT description,list_price,min_price
FROM product,
(SELECT product_id, list_price, min_price
FROM price
WHERE min_price<list_price*0.8
AND start_date<'31/12/1989'
AND (end_date >'01/01/1989' OR end_date IS NULL) ) x
WHERE product.product_id = t1.product_id
3.6.1.3 Выбрать фамилии тех сотрудников, которые обслуживают покупателей, не являясь продавцами (название должности - не 'SALESPERSON').
Первый вариант решения. Фамилии сотрудников находятся в таблице employee, названия должностей - в таблице job, коды сотрудников, обслуживающих покупателей, - в таблице customer. Между таблицами employee и job возможно естественное соединение по столбцу job_id, между таблицами employee и customer возможно соединение по столбцам salesperson_id (внешний ключ в таблице customer) - employee_id (первичный ключ в таблице employee). Устанавливаем это соединение и вводим дополнительное ограничение (название должности - не 'SALESPERSON').
SELECT last_name
FROM employee, job, customer
WHERE salesperson_id=employee_id
AND employee.job_id=job.job_id
AND function<>'SALESPERSON'
Второй вариант решения. Выбирается только информация из таблицы employee - имена сотрудников, поэтому в основном запросе выборка идет только из таблицы employee. В условиях ограничения выборки устанавливаем, что код должности в выбираемой строке не должен совпадать с кодом должности 'SALESPERSON', выбираемым из таблицы job (вложенный запрос), и код сотрудника должен входить во множество кодов сотрудников, обслуживающих покупателей, выбираемое из таблицы customer (вложенный запрос).
SELECT last_name
FROM employee
WHERE job_id <>
(SELECT job_id
FROM job
WHERE function='SALESPERSON')
AND employee_id IN
(SELECT salesperson_id
FROM customer);
3.6.1.4 Выбрать названия тех товаров, которые не продавались до 1989 г.
Запрос сформулирован не очень точно. Речь может идти либо о товарах, которые не выставлялись на продажу до 1989 г., либо о товарах, которые, может быть, и были выставлены на продажу, но их никто не покупал. Приведем решения для обеих интерпретаций.
В первой интерпретации (выбрать товары, которые не выставлялись на продажу) информация о времени может быть выбрана из таблицы price. Приведем насколько решений для этой интерпретации.
1). Выбираются те товары, коды которых не входят во множество кодов товаров, для которых время установки цены - до 1989 г.
SELECT description
FROM product
WHERE product_id NOT IN
(SELECT product_id
FROM price
WHERE start_date <='01-JAN-1989');
2). Выбираются те товары, для которых не существует цены, установленной до 1989 г.
SELECT description
FROM product
WHERE NOT EXISTS (SELECT *
FROM price
WHERE price.product_id= product.product_id
AND start_date <='01-JAN-1989');
3). Выбираются множество тех товаров, для которых существует цена, установленная до 1989 г. и вычитается из множества всех товаров.
SELECT description
FROM product
WHERE product_id IN
(SELECT product_id
FROM price
MINUS
SELECT product_id
FROM price
WHERE start_date <='01-JAN-1989');
Информация о времени для второй интерпретации (выбрать товары, которые покупались) может быть получена из таблицы sales_order.
1). Выбираются те товары, коды которых не входят во множество кодов товаров, для которых время покупки - до 1989 г.
SELECT description
FROM product
WHERE product_id NOT IN
(SELECT product_id
FROM sales_order,item
WHERE sales_order.order_id=item.order_id
AND order_date<'01-JAN-1989')
Сформировать решения для этой интерпретации, соответствующее другим методам, мы предоставляем читателю.
3.6.1.5 Выбрать всех сотрудников с одинаковыми именами.
Для этого запроса существует много решений, приведем некоторые из них.
Решение первое: перебрать список сотрудников и для каждого определить, существуют ли в фирме другие сотрудники с таким же именем. Выборка других (с другим значением employee_id) сотрудников с таким же именем выполняется в коррелированном вложенном запросе.
SELECT first_name, last_name
FROM employee x
WHERE EXISTS
(SELECT first_name
FROM employee
WHERE employee.first_name=x.first_name
AND x.employee_id<>employee.employee_id)
Решение второе мало отличается от первого: перебрать список сотрудников, для каждого сотрудника выбрать (во вложенном запросе) имена всех остальных сотрудников и определить, не совпадает ли имя рассматриваемого сотрудника с каким-нибудь именем из вложенного запроса.
SELECT first_name, last_name
FROM employee x
WHERE first_name = ANY
(SELECT first_name
FROM employee
WHERE x.employee_id<>employee.employee_id)
Решение третье: определить, какие имена встречаются в списке сотрудников более одного раза, и выбрать тех сотрудников, чьи имена входят в список повторяющихся имен. Выборка повторяющихся имен выполняется во вложенном запросе. При этом весь список сотрудников группируется по именам, и отбираются только те группы, в которые входит более одной строки.
SELECT first_name, last_name
FROM employee
WHERE first_name IN
(SELECT first_name
FROM employee
GROUP BY first_name
HAVING COUNT(*)>1)
Решение четвертое: выполнить соединение таблицы employee самой с собой по условию равенства имен и неравенства идентификационных кодов сотрудников. Поскольку для имен, которые встречаются в таблице более двух раз, в результирующей выборке будут повторяющиеся строки (объясните, почему), необходимо применить DISTINCT.
SELECT DISTINCT x.first_name, x.last_name
FROM employee x, employee y
WHERE x.first_name=y.first_name
AND x.employee_id<>y.employee_id
3.6.1.6 Определить, какой товар являлся самым дорогим в каждый момент времени.
Естественно, мы не можем выбрать "все моменты времени", но в этом и нет необходимости. Моменты, в которые самый дорогой товар может сменяться, это моменты установления новой цены на какой-либо товар. Следовательно, нужно перебрать все строки таблицы price, и для каждой строки установить, нет ли в таблице товара, срок действия которого включает в себя дату установления цены, имеющуюся в выбранной строке, а цена превышает имеющуюся в выбранной строке. Если такого товара нет, то товар, к которому относится выбранная строка, становится "чемпионом" с момента установления данной цены. Решение, таким образом, реализуется в коррелированном запросе со сложным условием выборки.
SELECT description, list_price, start_date
FROM product, price x
WHERE list_price >= ALL (
SELECT list_price
FROM price
WHERE price.product_id<>x.product_id
AND price.start_date<=x.start_date
AND (price.end_date>x.end_date OR price.end_date IS NULL))
AND x.product_id = product.product_id
ORDER BY 3;
3.6.1.7 Выбрать имена и зарплаты 10 самых высокооплачиваемых сотрудников фирмы (без учета комиссионных).
Это чрезвычайно "неприятный" запрос. Конечно, можно просто вывести список сотрудников, упорядоченный по убыванию зарплаты, а затем оставить в нем только первые 10 строк. Но поскольку реляционная модель предполагает отсутствие какой-либо упорядоченности в строках таблицы, операция исключения строк после первых 10 не может быть проделана средствами SQL.
Логика приблизительного решения средствами исключительно SQL состоит в следующем. Для каждого сотрудника следует определить, сколько человек в фирме получают большую зарплату, чем он. Если это число меньше 10, то рассматриваемый сотрудник входит в десятку.
SELECT last_name, salary
FROM employee temp
WHERE 10>
(SELECT COUNT(*)
FROM employee
WHERE salary > temp.salary)
ORDER BY 2 DESC;
Для того чтобы запрос выполнялся как в DB2, так и в Oracle, мы применили вложенный запрос слева от операции сравнения (ограничение, накладываемое Oracle).
Это одна из немногих задач, для которых коррелированный запрос является единственно возможным решением.
Выполните, однако, аналогичный запрос, для выборки не 10, а 8 сотрудников. Если вы не изменили содержимое базы данных, то результат выборки будет содержать не 8, а 9 строк. Попробуйте объяснить результат.
3.6.1.8 Выбрать те отделы, в которых точно та же номенклатура должностей, что и в отделе 23.
Подобный запрос у нас уже был (в пп.3.3.4.2), но там допускалось, чтобы в выбранном отделе были и такие должности, которых нет в отделе 23. Логика решения, примененного для той задачи, была такова, что при переборе строк таблицы department для каждого выбранного отдела проверялось, нет ли в таблице employee такой должности в отделе 23 (первый вложенный запрос), которой бы не было в выбранном отделе (второй вложенный запрос). Для выполнения условия новой задачи расширим проверку. Убедимся также, что в выбранном отделе нет такой должности, которой бы не было в отделе 23.
SELECT department_id
FROM department
WHERE NOT EXISTS
(SELECT *
FROM employee
WHERE department_id=23
AND job_id NOT IN
(SELECT job_id
FROM employee
WHERE employee.department_id = department.department_id
)
)
/* новое условие */
AND NOT EXISTS
(SELECT *
FROM employee
WHERE employee.department_id = department.department_id
AND job_id NOT IN
(SELECT job_id
FROM employee
WHERE department_id=23
)
)
AND department_id<>23
Возможна и другая логика построения этого запроса. В задаче п.3.3.4.2 мы убедились в том, что в выбранном отделе есть все должности, имеющиеся в отделе 23. Если же количество должностей в выбранном отделе совпадает с количеством должностей отдела 23, то в выбранном отделе нет никаких других должностей.
SELECT department_id
FROM department,
/* количество должностей в отделе 23 */
(SELECT COUNT(DISTINCT job_id) AS c23
FROM employee
WHERE department_id=23) y
WHERE NOT EXISTS
(SELECT * FROM employee
WHERE department_id=23
AND job_id NOT IN
(SELECT job_id FROM employee
WHERE employee.department_id = department.department_id
)
)
AND c23=
/* количество должностей в выбранном отделе */
(SELECT COUNT(DISTINCT job_id) AS cx
FROM employee
WHERE employee.department_id = department.department_id)
AND department_id<>23
3.6.1.9 Выбрать должность, которая есть во всех городах.
Для того чтобы выбрать код должности, которая есть во всех городах, создадим временную таблицу (назовем ее jl), в которой будут 2 столбца: код города и код должности, имеющейся в этом городе. Таблица jl создается как естественное соединение таблиц employee и department. Перебираем строки таблицы jl с применением следующего ограничения: в множестве всех кодов должностей не существует такого кода должности, который не входил бы во множество кодов должностей, имеющихся в том городе, код которого записан в рассматриваемой строке. Для получения названия должности нужно выполнить естественное соединение результата этой выборки с таблицей job и проекцию по столбцу function.
Решение приведем для DB2, так как оно более компактное. Временную таблицу jl создаем явным образом как временное представление, и решение будет выглядеть так:
WITH jl AS
--- создание временной виртуальной таблицы
(SELECT DISTINCT job_id, location_id
FROM employee, department
WHERE employee.department_id = department.department_id)
SELECT DISTINCT function
FROM job, jl x
-- естественное соединение с job
WHERE x.job_id=job.job_id
--- не существует такого кода должности...
AND NOT EXISTS
--- ...вo множестве всех кодов должностей ...
(SELECT location_id
FROM location
-- ...который не входил бы...
WHERE location_id NOT IN
--- ...во множество кодов должностей...
(SELECT location_id
FROM jl y
--- ...имеющихся в том городе, код которого
--- записан в рассматриваемой строке
WHERE y.job_id=x.job_id))
Возможно, для кого-то более удобной будет другая формулировка ограничения, применяемого при переборе строк таблицы jl: реляционная разность между множеством всех кодов должностей и множеством кодов должностей, имеющихся в том городе, код которого записан в рассматриваемой строке, должна быть пустым множеством.
WITH jl AS
(SELECT DISTINCT job_id, location_id
FROM employee, department
WHERE employee.department_id = department.department_id)
SELECT DISTINCT function
FROM job, jl x
WHERE x.job_id=job.job_id
--- должна быть пустым множеством
AND NOT EXISTS
--- множество всех кодов должностей
(SELECT location_id FROM location
-- реляционная разность
EXCEPT
--- множество кодов должностей
SELECT location_id
FROM jl x
--- имеющихся в том городе, код которого
--- записан в рассматриваемой строке
WHERE y.job_id=x.job_id )
Следуя той же логике можно построить и аналогичные решения для Oracle (которые также будет работать и для DB2). В Oracle нет конструкции WITH, так что решения для Oracle будут отличаться от приведенных выше тем, что везде вместо имени временной таблицы jl придется полностью повторять вложенный запрос, который определяет эту таблицу.
3.6.1.10 Определить, на какую сумму подорожал товар 'DUNK HOOP' с того момента, когда он был выставлен на продажу.
Очевидно, что для получения ответа нужно определить самую первую цену товара (цену, для которой дата установления цены минимальная), последнюю его цену (цену, для которой дата установления цены максимальная) и вычесть первую цену из второй.
В Oracle для того чтобы свести в одном запросе эти две цифры, мы создаем в фразе FROM две временные таблицы - t1 и t2, состоящие из одного столбца и одной строки каждая. В одну таблицу выбирается цена с минимальной датой, в другую - с максимальной. Таблицы соединяются (декартово произведение) и определяется разность столбцов. Решение для Oracle:
SELECT p2-p1
FROM
(SELECT list_price AS p2
FROM price, product
WHERE price.product_id = product.product_id
AND description='DUNK HOOP'
AND start_date =
(SELECT MAX(start_date)
FROM price, product
WHERE price.product_id = product.product_id
AND description='DUNK HOOP')) t1,
(SELECT list_price AS p1
FROM price, product
WHERE price.product_id = product.product_id
AND description='DUNK HOOP'
AND start_date =
(SELECT MIN(start_date)
FROM price, product
WHERE price.product_id = product.product_id
AND description='DUNK HOOP')) t2;
Это решение будет работать и для DB2. Однако некоторые дополнительные возможности диалекта SQL DB2 позволяют сделать этот запрос более компактным. Во-первых, мы можем определить временное представление (tt), в которое мы выберем информацию о ценах товара 'DUNK HOOP', и выбирать цены с минимальной и максимальной датой из этого представления. Во-вторых, DB2 позволяет использовать скалярный вложенный запрос в фразе FROM. Таким образом, мы получаем результат как арифметическую разность двух вложенных запросов. Поскольку такая операция будет производиться для каждой строки временного представления tt, нужно ввести ключевое слово DISTINCT. Решение для DB2:
WITH tt AS
(SELECT list_price, start_date AS sd
FROM price, product
WHERE price.product_id = product.product_id
AND description='DUNK HOOP')
SELECT DISTINCT
(SELECT list_price FROM tt
WHERE sd=(SELECT MAX(sd) FROM tt))
-
(SELECT list_price FROM tt
WHERE sd=(SELECT MIN(sd) FROM tt))
FROM tt
3.6.1.11 Определить названия товаров, для которых объем продаж (в денежном выражении) в 1990 г. снизился по сравнению с предыдущим годом.
Очевидно, что для ответа на этот вопрос следует выбрать объем продаж по товарам в 1990 г. и в 1989 г. (такая выборка может быть сделана из таблиц sales_order и item) и сравнить их для каждого товара. Объем продаж по каждому году составляет отдельную выборку - промежуточную таблицу. Две промежуточные таблицы следует соединить по коду товара (естественное соединение), а для получения названия товара - выполнить естественное соединение также с таблицей product.
В приводимом ниже решении используется формат представления даты Oracle. (Обратите внимание: используется полное имя столбца item.total, так как столбец item есть также и в таблице sales_order.)
SELECT description, s1, s2
FROM product,
(SELECT SUM(item.total) AS s1, product_id AS p1
FROM item, sales_order
WHERE item.order_id = sales_order.order_id
AND order_date BETWEEN '01-JAN-1989' AND '31-DEC-1989'
GROUP BY product_id) t1,
(SELECT SUM(item.total) AS s2, product_id AS p2
FROM item, sales_order
WHERE item.order_id = sales_order.order_id
AND order_date BETWEEN '01-JAN-1990' AND '31-DEC-1990'
GROUP BY product_id) t1
WHERE p1=p2
AND product_id=p1
AND s1>s2;
3.6.1.12 Определить количество проданных за лето 1990 г. экземпляров для каждого товара, в названии которого есть слово 'SOFTBALL'.
На первый взгляд запрос довольно прост и решается естественным соединением таблиц product, item и sales_order с последующим ограничением по названию и дате продаже и группированием по названию. Однако следует учесть, что среди товаров SOFTBALL есть, возможно, и такие, которые летом 1990 г. не продавались вообще, и в такую выборку эти товары не попадут. Чтобы отразить в результате запроса и эти товары, нужно сначала получить естественное соединение таблиц item и sales_order с дополнительным ограничением по дате продажи (назовем результат этого соединения t1). Затем следует выполнить левое внешнее соединение таблицы product с таблицей t1. В результат внешнего соединения войдут все строки таблицы product, включая и те, которым нет естественного соответствия в таблице t1. Результат внешнего соединения далее ограничивается по названию товара, группируется по названию, и для каждой группы вычисляется сумма количеств проданных экземпляров.
Решение для Oracle:
SELECT description, SUM(quantity)
FROM product,
(SELECT product_id, order_date, quantity
FROM item, sales_order
WHERE sales_order.order_id=item.order_id
AND order_date BETWEEN '01-JUN-1990' AND '31-AUG-1990') t2
WHERE description like '%SOFTBALL%'
AND product.product_id = t2.product_id (+)
GROUP BY description
ORDER BY 2 DESC;
Решение для DB2:
SELECT description, SUM(quantity)
FROM sales_order
JOIN item ON sales_order.order_id=item.order_id
AND order_date BETWEEN '01/06/1990' AND '31/08/1990'
RIGHT OUTER JOIN product ON product.product_id = item.product_id
WHERE description like '%SOFTBALL%'
GROUP BY description
ORDER BY 2 DESC;
3.6.1.13 Выбрать случаи, когда один и тот же товар заказывался одним и тем же покупателем с интервалом не более недели.
Для выполнения этой задачи нам прежде всего потребуется знать, кто, что и когда покупал. Такую информацию можно легко получить, сделав выборку, представляющую собой естественное соединение таблиц item и sales_order с проекцией по столбцам: customer_id (кто), product_id (что), order_date (когда) и sales_order (уникальный идентификатор заказа). Затем следует выполнить автосоединение этой выборки. Соединяться должны строки, имеющие одинаковые значения в столбцах customer_id и product_id, что соответствует условиям "один и тот же покупатель" и "один и тот же товар". Но значения в столбцах order_id у соединяемых строк должны быть разными, чтобы исключить соединение строки со своей копией. Дополнительное условие соединения: разность дат в соединяемых строках должна составлять от 0 до 7 дней (интервал не более недели).
Для того, чтобы выбрать название покупателя и название товара, а не их идентификаторы, следует выполнить естественное соединение описанной выше выборки с таблицами customer и product.
В приводимом ниже решении выборка первого шага реализована во вложенном запросе, который приходится повторять дважды, а автосоединение и естественное соединение с таблицами customer и product выполняются во вложенных запросах.
SELECT name, description, y.od, x.od
FROM
(SELECT item.product_id AS pid,
sales_order.order_date AS od,
customer_id AS cid,
sales_order.order_id AS oid
FROM item, sales_order
WHERE item.order_id = sales_order.order_id) x,
(SELECT item.product_id AS pid,
sales_order.order_date AS od,
customer_id AS cid,
sales_order.order_id AS oid
FROM item, sales_order
WHERE item.order_id = sales_order.order_id) y,
product, customer
WHERE x.pid=y.pid
AND x.cid=y.cid
AND x.oid<>y.oid
AND x.od - y.od BETWEEN 0 AND 7
AND product_id = x.pid
AND customer_id = x.cid
Хотя формат представления даты разный в DB2 и в Oracle, разность дат в любом случае даст число, таким образом, решение одинаково применимо для обеих СУБД. Для DB2 запись можно сократить, вынеся повторяющийся вложенный запрос во временное представление.
3.6.1.14 Для каждого отдела определить процентное отношение средней заработной платы рядовых сотрудников к зарплате менеджера этого отдела.
Очевидное пошаговое решение этой задачи:
В приводимом ниже решении два первых шага реализованы во вложенных запросах фразы FROM.
SELECT t1.department_id, se/sm*100
FROM
(SELECT AVG(salary) AS se, department_id
FROM employee,job
WHERE employee.job_id=job.job_id
AND function<>'MANAGER'
GROUP BY department_id
) t1,
(SELECT last_name, salary AS sm, department_id
FROM employee, job
WHERE employee.job_id=job.job_id
AND function='MANAGER') t2
WHERE t1.department_id = t2.department_id
Приведенное решение работает в обеих СУБД. Однако DB2 позволяет сформулировать решение несколько иначе:
SELECT department_id,
(SELECT AVG(salary)
FROM employee JOIN job ON employee.job_id = job.job_id
WHERE employee.department_id = department.department_id
AND function <> 'MANAGER')
/
(SELECT salary
FROM employee JOIN job ON employee.job_id = job.job_id
WHERE employee.department_id = department.department_id
AND function = 'MANAGER')
*100
FROM department
В этом решении перебираются строки таблицы department, для каждой строки выбирается средняя зарплата рядовых сотрудников данного отдела и средняя зарплата менеджера и первое делится на второе. DB2 позволяет использовать вложенные скалярные запросы в списке выборки фразы SELECT. В данном случае список выборки содержит выражение с двумя такими запросами, причем оба этих запроса являются коррелированными, то есть, выполняются для каждой строки выборки.
При том, что оба варианта решают поставленную задачу, в некоторых граничных ситуациях эти запросы поведут себя по-разному. Такие граничные ситуации сводятся к следующим:
В первом решения строка отдела, в котором имеет место первая или вторая граничная ситуация не попадает в первую или вторую выборку соответственно, а, следовательно, не попадает и в результат соединения. Во втором решении для такого отдела будет выбрано неопределенное значение в числителе или в знаменателе, следовательно, отдел попадет в результирующую выборку, но со значением NULL во втором столбце.
Третья граничная ситуация приведет к тому, что в первом решении для отдела будет выбрано несколько строк (по одной строке для каждого менеджера) на втором шаге, следовательно, и в результирующей выборке отдел будет представлен несколькими строками - отношение средней зарплаты рядовых сотрудников к зарплате каждого менеджера.
Во втором решении третья граничная ситуация приведет к ошибке во время его выполнения. При этой ситуации вложенный запрос, стоящий в знаменателе, окажется не скалярным, а в списке выборки допускаются только скалярные запросы. Избежать ошибки можно, например, выбирая в этом вложенном запросе не salary, а AVG(salary). Тогда для отдела будет выведена одна строка - отношение средней зарплаты рядовых сотрудников к средней зарплате менеджеров отдела.
3.6.1.15 Выбрать цепочку начальников-подчиненных, связывающих сотрудников DOUGLAS и ROBERTS.
Поясним эту задачу. Предположим, что иерархия подчиненности в фирме имеет вид, показанный на рис. 3.24.
Если DOUGLAS должен, например, передать "по инстанциям" какие-то материалы для ROBERTS, то он передает их своему непосредственному начальнику SMITH, тот - своему начальнику и т.д. Материалы поднимаются по иерархическому дереву до тех пор, пока не дойдут до такого менеджера, который является начальником (пусть и не непосредственным) и DOUGLAS, и ROBERTS. (На рис.3.24 таким "наименьшим общим начальником" является JONES). Этот начальник спускает материалы по той ветви дерева, которая ведет к ROBERTS. Для приведенной на рисунке структуры, таким образом, решением является последовательность имен: DOUGLAS, SMITH, BROWN, JONES, ADAMS, ROBERTS.
Рисунок 3.24 - Пример структуры подчиненности
Логику построения запроса будем разрабатывать поэтапно, предположив, что в нашем распоряжении есть оператор присваивания, позволяющий сохранить результат выполнения одного шага.
1). Мы можем выбрать всех прямых начальников DOUGLAS. Введем в выборку столбец y, начальное значение которого - 0, и оно будет возрастать на 1 на каждом следующем уровне рекурсии. При использовании диалекта SQL Oracle решение на этом шаге будет выглядеть так:
tmp1 =
SELECT level AS y,last_name,employee_id,manager_id
FROM employee
CONNECT BY PRIOR manager_id=employee_id
START WITH last_name= 'DOUGLAS';
Результатом этого шага должно быть множество строк с такими значениями фамилий и y: DOUGLAS 1, SMITH 2, BROWN 3, JONES 4, GREEN 5, KING 6.
2). Аналогичным образом выбираются все прямые начальники ROBERTS, но в этой выборке начальное значение y - 99, и оно убывает на 1 на каждом следующем уровне рекурсии. Решение этого шага:
tmp2 =
SELECT 100-level AS y,last_name,employee_id,manager_id
FROM employee
CONNECT BY PRIOR manager_id=employee_id
START WITH last_name= 'ROBERTS';
Результатом этого шага должно быть множество строк с такими значениями фамилий и y: ROBERTS 99, ADAMS 98, JONES 97, GREEN 96, KING 95.
3). Выделим из результата 1-го шага те фамилии, которые отсутствуют в результате 2-го шага, то есть, "собственных" начальников DOUGLAS.
tmp1a =
SELECT * FROM tmp1
WHERE employee_id NOT IN
(SELECT employee_id FROM tmp2);
Результ шага: DOUGLAS 1, SMITH 2, BROWN 3.
4). Точно так же мы можем выделить "собственных" начальников ROBERTS. Но вместе с ними мы выделим и "наименьшего общего начальника". Общие начальники составляют ту часть множества tmp2, которая пересекается с множеством tmp1. "Наименьший общий начальник" имеет в этом пересечении наибольшее значение y.
tmp2a =
SELECT * FROM tmp2
WHERE employee_id NOT IN
(SELECT employee_id FROM tmp1)
OR l2=(SELECT MAX(l2)
FROM tmp1
WHERE employee_id IN (
SELECT employee_id
FROM tmp2));
Результ шага: ROBERTS 99, ADAMS 98, JONES 97.
5). Теперь объединим множества tmp1a и tmp2a. При объединении упорядочим результат по возрастанию значения в столбце y.
SELECT last_name, y
FROM (
SELECT * FROM tmp1a
UNION
SELECT * FROM tmp2a
)
ORDER BY 2;
Конечный результат: DOUGLAS 1, SMITH 2, BROWN 3, JONES 97, ADAMS 98, ROBERTS 99.
Поскольку оператора присваивания в языке SQL нет, для получения работающего решения нам придется подставить в конечный шаг формулировки предыдущих шагов. Для Oracle запрос после выполнения подстановок будет выглядеть так:
SELECT last_name, y
FROM (
SELECT * FROM (
SELECT *
FROM (
SELECT level AS y, last_name, employee_id, manager_id
FROM employee
CONNECT BY PRIOR manager_id=employee_id
START WITH last_name= 'DOUGLAS'
) tmp1
WHERE employee_id NOT IN (
SELECT employee_id
FROM (
SELECT 100-level AS y, last_name, employee_id, manager_id
FROM employee
CONNECT BY PRIOR manager_id=employee_id
START WITH last_name= 'ROBERTS'
) tmp2
)
) tmp1a
UNION
SELECT *
FROM (
SELECT *
FROM (
SELECT 100-level AS y, last_name, employee_id, manager_id
FROM employee
CONNECT BY PRIOR manager_id=employee_id
START WITH last_name= 'ROBERTS'
) tmp1
WHERE employee_id NOT IN (
SELECT employee_id
FROM (
SELECT level AS y, last_name, employee_id, manager_id
FROM employee
CONNECT BY PRIOR manager_id=employee_id
START WITH last_name= 'DOUGLAS'
) tmp2
)
OR y=(
SELECT MAX(y)
FROM (
SELECT 100-level AS y, last_name, employee_id, manager_id
FROM employee
CONNECT BY PRIOR manager_id=employee_id
START WITH last_name= 'ROBERTS'
) tmp1
WHERE employee_id IN (
SELECT employee_id
FROM (
SELECT level AS y, last_name, employee_id, manager_id
FROM employee
CONNECT BY PRIOR manager_id=employee_id
START WITH last_name= 'DOUGLAS'
) tmp2
)
)
) tmp2a
) tmp
ORDER BY 2;
Работающий запрос для DB2 мы приводим без пояснений, во-первых, потому что он компактнее, чем запрос для Oracle, а во-вторых, потому что его структура и создаваемые в нем временные представления полностью соответствует приведенной нами логике построения запроса.
WITH
tmp1 (y, name, e_id, m_id) AS
(SELECT 1, last_name, employee_id, manager_id
FROM employee
WHERE last_name= 'DOUGLAS'
UNION ALL
SELECT y+1,last_name, employee_id, manager_id
FROM employee, tmp1
WHERE employee_id= m_id ),
tmp2 (y, name, e_id, m_id) AS
(SELECT 99, last_name, employee_id, manager_id
FROM employee
WHERE last_name= 'ROBERTS'
UNION ALL
SELECT y-1, last_name, employee_id, manager_id
FROM employee, tmp2
WHERE employee_id= m_id ),
tmp1a AS
(SELECT * FROM tmp1
WHERE e_id NOT IN (SELECT e_id FROM tmp2) ),
tmp2a AS
(SELECT * FROM tmp2
WHERE e_id NOT IN (SELECT e_id FROM tmp1)
OR y=(
SELECT MAX(y) FROM tmp2
WHERE e_id IN (SELECT e_id FROM tmp1 )
) )
SELECT name, y
FROM (
SELECT * FROM tmp1a
UNION
SELECT * FROM tmp2a
) tmp
ORDER BY 2;
| Назад | Оглавление | Вперед |
| Каталог | Индекс раздела |