Лаврентев Освоение СQЛ 2009

101

Решение: Первый вариант:

SELECT ROWNUM, id_for_the_same_last_name, First_name, Last_name FROM

(SELECT DISTINCT LEVEL id_for_the_same_last_name, First_name, Last_name FROM emp_selected

CONNECT BY LEVEL<=N ORDER BY last_name, LEVEL)

/

Приведем пояснение этого варианта.

Здесь посмотрим внутренний запрос без distinct:

SELECT LEVEL id_for_the_same_last_name, First_name, Last_name

FROM emp_selected

CONNECT BY LEVEL<=N

ORDER BY last_name, LEVEL;

ID_FOR_THE_SAME_LAST_NAME FIRST_NAME		LAST_NAME
------------------------- -------------------- ----------
1 Ellen	ABEL
2 Ellen	ABEL
2 Ellen	ABEL
3 Ellen	ABEL
3 Ellen	ABEL
3 Ellen	ABEL
3 Ellen	ABEL
1 Matthew	WEISS
2 Matthew	WEISS
2 Matthew	WEISS
3 Matthew	WEISS
3 Matthew	WEISS
3 Matthew	WEISS
3 Matthew	WEISS
4 Matthew	WEISS
4 Matthew	WEISS
4 Matthew	WEISS
4 Matthew	WEISS
4 Matthew	WEISS
4 Matthew	WEISS
4 Matthew	WEISS
4 Matthew	WEISS
5 Matthew	WEISS
5 Matthew	WEISS
5 Matthew	WEISS
5 Matthew	WEISS
5 Matthew	WEISS
5 Matthew	WEISS
5 Matthew	WEISS
5 Matthew	WEISS

30 строк выбрано.

Видна такая закономерность:

Суровнем один встретилось 20=1 записей с ABEL и WEISS,

Суровнем два встретилось 21=2 записей с ABEL и WEISS,

Суровнем три встретилось 22=4 записей с ABEL и WEISS,

Суровнем 4 встретилось 23=8 записей с WEISS,

Суровнем 5 встретилось 23=8 записей с WEISS.

102

Приведем структуру создаваемого запросом дерева, из которого станет понятно, что происходит.

LEVEL 1

ABEL

WEISS

LEVEL 2

ABEL

WEISS

ABEL

WEISS

LEVEL 3

ABEL WEISS

ABEL WEISS

ABEL WEISS ABEL WEISS

LEVEL 4

WEISS

WEISS WEISS WEISS

WEISS WEISS WEISS WEISS

LEVEL 5

WEISS

WEISS WEISS WEISS

WEISS WEISS WEISS WEISS

После третьего уровня «ABEL» уже не может появиться, так как для строки с «ABEL» N=3 и LEVEL<=3. Если в таблицу emp_selected вставить третью строку, например ('Den','TOMPSON', 3), вышеприведенный подзапрос будет строить тернарное дерево на первых трех уровнях.

Второй вариант решения (предложен Д.Байковым):

SELECT ROWNUM, LEVEL row_in_last_name, First_name, Last_name FROM emp_selected

CONNECT BY N=CONNECT_BY_ROOT(N) AND LEVEL<=N

/

Здесь выстраиваемое дерево является одноарным, т.е. от каждой вершины отходит только одна ветвь. Часть запроса «CONNECT_BY_ROOT(N)» строит отдельные деревья для «ABEL» и «WEISS» с наследованием для «ABEL» только «ABEL», для «WEISS» только «WEISS». Нижеследующий запрос демонстрирует такое построение (сначала отформатируем столбец path):

COL path FORMAT A30;

SELECT ROWNUM, LEVEL, First_name, Last_name, SYS_CONNECT_BY_PATH(last_name, '/') "Path"

FROM emp_selected

CONNECT BY N=CONNECT_BY_ROOT(N) AND LEVEL<=N

/

Однако последний вариант решения дает ошибки при одинаковых значениях N в разных строках таблицы emp_selected. Здесь SQL компилятор начинает строить бинарные деревья (для 2-х записей в таблице emp_selected). Поэтому вариант Д.Байкова необходимо уточнить:

SELECT ROWNUM, LEVEL row_in_last_name, First_name, Last_name FROM emp_selected

CONNECT BY First_name||Last_name =CONNECT_BY_ROOT(First_name||Last_name) AND LEVEL<=N ORDER BY last_name,level

/

Задание 18 /5/ Зарплаты новых сотрудников. Текст задания:

Из таблицы EMPLOYEES надо выбрать не более 19-ти различных зарплат (столбец SALARY) среди самых новых сотрудников (столбец HIRE_DATE), анализируя не более 50 сотрудников.

Желательно осуществить выборку одним SQL-запросом. Сложность в баллах:

SQL – 9 PL/SQL – 4.

Решение.

1. Ограничим число анализируемых строк (сотрудников) числом 50

SELECT e.salary,e.hire_date FROM

(SELECT hire_date, salary FROM employees

103

ORDER BY hire_date DESC) e WHERE rownum<=50

/

2.Теперь в полученном результате выделим первые (по убыванию hire_date) 19 строк с

отличающимися зарплатами.

Если столбец hire_date выводить не надо, то эти 19 строк с разной зарплатой и есть ответ, в противном случае в первом SELECT-е, помимо h.salary добавится вывод столбца h.hire_date. Эта выводимая дата будет соответствовать дате принятия на работу сотудника, которому первому назначили такую зарплату. По разному выделены подзапросы, работа с которыми позволяет исключить дубликаты строк, дублирующих зарплату (дубликаты, расположенные в подзапросе «f» ниже первого, не выводятся; первый из дубликатов выводится). Такая относительная сложность вызвана неудачей использования опции distinct, нарушающей порядок вывода строк (такой убывающий порядок важно сохранить по столбцу hire_date).

SELECT h.salary FROM

(SELECT f.salary,f.hire_date FROM

(select( rownum) rn1,e.salary,e.hire_date FROM

(SELECT hire_date, salary FROM employees

order by hire_date desc,salary desc) e WHERE rownum<=50) f

WHERE f.rn1 <= all(SELECT g.rn2 FROM

(SELECT (rownum) rn2, e.salary,e.hire_date FROM

(SELECT hire_date, salary FROM employees

order by hire_date desc, salary desc) e WHERE rownum<=50) g

WHERE f.salary=g.salary)

order by f.hire_date desc, f.salary desc) h

WHERE rownum <=19

/

Задание 19 /5/ Обмен значениями. Текст задания:

Оператор отдела кадров при регистрации сотрудников c номерами 194 и 195 присвоила им данные друг друга. Необходимо исправить эту ошибку. Ситуация осложняется тем, что у вас нет доступа к другим полям таблицы за исключением поля employee_id, являющегося первичным ключом. Необходимо решить задачу одним оператором SQL.

Дополнительные требования к выполнению

Необходимо решить задачу одним оператором SQL Сложность в баллах: 7.

Решение.

UPDATE employees SET employee_id=decode(employee_id,205,204,204,205,employee_id)

WHERE employee_id IN (204, 205);

!!!После UPDATE, так как, возможно, потребуются исходные данные в других задачах, не забыть выполнить

roolback;

Задание 22 /5/ Отчет о выплатах подчиненным. Текст задания:

104

Написать запрос, выдающий отчѐт о суммарных выплатах сотрудникам, непосредственно подчиняющихся руководителю (задаѐтся полное имя), по названиям должностей (поле JOBS.Job_Title).

Отчѐт должен содержать группы строк. Каждая группа относится к данному руководителю и состоит из регулярных строк, отображающих суммарные выплаты и количество сотрудников на данной должности, непосредственно подчиняющихся этому руководителю. Группу должна завершать строка с итоговыми значениями суммарных выплат и количества сотрудников (для сотрудников, непосредственно подчиняющихся данному руководителю).

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

Столбцы отчѐта:

1. Полное имя руководителя

Итоговые строки в этом поле должны содержать полное имя руководителя (First_name, пробел,Last_name из таблицы EMPLOYEES) с отступом (несколько точек), за которым следует текстовая константа ―итоги:‖. Для строки, представляющей общий итог, это поле должно содержать текстовую константу ―О Б Щ И Й‖ c отступом, представленным несколькими точками.

Для остальных (регулярных) строк в этом поле должно быть полное имя руководителя (First_name, пробел,Last_name) без отступа.

2.Название должности

Для итоговых строк этот поле должно содержать название должности руководителя (поле Job_Title из таблицы JOBS) и для строки общего итога – пустое значение, а в поле названия должностей – текстовая константа ―И Т О Г‖.

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

3.Количество сотрудников

Для строки общего итога – общее количество сотрудников, находящихся в непосредственном подчинении у всех руководителей, представленных в отчѐте.

Для итоговых строк – количество сотрудников, находящихся в непосредственном подчинении у данного руководителя.

Для регулярных строк – количество сотрудников на данной должности в непосредственном подчинении данного руководителя.

4.Суммарные выплаты

Для строки общего итога – сумма выплат сотрудникам, находящимся в непосредственном подчинении у всех руководителей, представленных в отчѐте.

Для итоговых строк – суммарные выплаты всем сотрудникам, находящимся в непосредственном подчинении данного руководителя.

Для регулярных строк суммарные месячные выплаты для сотрудников, находящихся на данной должности у данного руководителя.

Пример отчѐта
Руководитель		Должность	Сотрудников	Суммарные выплаты
------------------------------		-------------------------------------	------------------	----------------------------
Steven King		Administration Vice President	2	34000
Steven King		Marketing Manager	1	13000
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Steven King		Stock Manager	5	36400
Steven King	итоги:	President	14	174050
Neena Kochhar		Accounting Manager	1	12000
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Neena Kochhar	итоги:	Administration Vice President	5	44900
Lex De Haan		Programmer	1	9000
Lex De Haan	итоги:	Administration Vice President	1	9000
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Den Raphaely		Purchasing Clerk	5	13900
Den Raphaely итоги:		Purchasing Manager	5	13900
О Б Щ И Й		И Т О Г		34 301250
21 rows selected.

105

Сложность в баллах: 8.

Решение (предложено А. Квашниным):

col " Полное имя руководителя" format a25;

SELECT DECODE(grp_id,0,mgr_name,1,mgr_name||' итоги:',3,' ОБЩИЙ') AS "Полное имя руководителя", DECODE(grp_id,0,emp_job,1,mgr_job,3,' ИТОГ') AS "Название должности",

emp_cnt AS "Количество сотрудников", emp_tot_sal AS "Суммарные выплаты"

FROM (

SELECT e1.first_name||' '||e1.last_name AS mgr_name, j1.job_title AS mgr_job , j2.job_title AS emp_job , count(*) AS emp_cnt,

SUM(e2.salary) AS emp_tot_sal, GROUPING_ID(j1.job_title,j2.job_title) as grp_id

FROM employees e1, employees e2, jobs j1, jobs j2 WHERE e1.employee_id=e2.manager_id

AND e1.job_id=j1.job_id AND e2.job_id=j2.job_id

GROUP BY ROLLUP((e1.first_name||' '||e1.last_name, j1.job_title), j2.job_title)

)

ORDER BY mgr_name, grp_id;

В завершение этого приложения приведем адрес сайта с задачами и решениями SQL олимпиады 2008 г. http://sql.ru/forum/actualsearch.aspx?search=Oracle+%EE%EB%E8%EC%EF%E8%E0%E4 %E0+2008&sin=0&a=&ma=0&bid=3&dt=-1&s=1&so=1

Автор выражает признательность В.Юринскому, Д.Бойкову, А.Квашнину за отмеченные погрешности и предложенные корректировки в первоначальных вариантах решения отдельных задач.