II.2.2 Коррелированные подзапросы

Q2_8 Получить список сотрудников, зарплата которых превышает среднюю в отделе, где они работают

demo@10g>select employee_id,last_name name,salary,department_id

from EMPLOYEE x

where salary > (select avg(salary) from EMPLOYEE

where department_id = x.department_id)

order by department_id,salary desc;

В запросе используется таблица EMPLOYEE с алиасом «x» и копия этой таблицы. Последовательность действий Oracle при отработке запроса такова: извлекается первая строка из таблицы с алиасом «x», из этой строки извлекается номер отдела, для этого номера отдела в копии таблицы подсчитывается средняя зарплата и если зарплата в извлеченной строке оказывается выше подсчитанной средней, соответсвующие столбцы из извлеченной строки «укладываются» в результат, вслед за тем извлекается вторая строка из т аблицы с алиасом «x» и т.д. – в цикле.

Q2_9 Получить список сотрудников, имеющих хотя бы одного подчиненного

demo@10g> select EMPLOYEE_ID,last_name,job_id,DEPARTMENT_ID

from EMPLOYEE x

where exists (select 1 from EMPLOYEE

where EMPLOYEE.manager_id = x.EMPLOYEE_ID)

order by DEPARTMENT_ID;

Q2_10 Сотрудники, не имеющие подчиненных.

demo@10g> select EMPLOYEE_id,last_name,job_id,Department_id

from EMPLOYEE

where EMPLOYEE_id not in (select manager_id from EMPLOYEE);

строки не выбраны

Запрос отработан неверно, так как одно из значений внутреннего запроса – NULL значение, отчего отработка условия «…EMPLOYEE_id not in (select manager_id from EMPLOYEE)» ничего не извлекает для EMPLOYEE_id. Это касается только сравнения «not in». Для «…EMPLOYEE_id in (select manager_id from EMPLOYEE)» запрос отработал бы верно (смысловое значение запроса в этом случае было бы иное: сотрудники, имеющие подчиненных), несмотря на присутствие NULL значения во внутреннем селекте.

Правильно для «not in» надо сделать так:

demo@10g> select EMPLOYEE_id,last_name,job_id,Department_id

from EMPLOYEE

where EMPLOYEE_id not in (select nvl(manager_id,0) from EMPLOYEE);

Другой вариант отработки этого запроса:

demo@10g> select EMPLOYEE_ID,last_name,job_id,DEPARTMENT_ID

from EMPLOYEE x

where not exists (select manager_id from EMPLOYEE

where manager_id = x.EMPLOYEE_ID)

order by DEPARTMENT_ID;

Q2_11

Коррелированный подзапрос при использовании оператора UPDATE.

Создадим таблицу EMPLOYEE1:

demo@10g> create table EMPLOYEE1 as select * from EMPLOYEE;

Таблица создана.

Добавим столбец к таблице EMPLOYEE1:

demo@10g> alter table EMPLOYEE1 add(dname varchar2(14));

Таблица изменена.

А теперь заполним значениями новую колонку:

demo@10g> update EMPLOYEE1 x

set dname=(select name from department

where department_id=x.department_id);

32 строк обновлено.

demo@10g> drop table EMPLOYEE1;

Таблица удалена.