Main_Table PK User_Constraints 7 страница

Читайте также:

SELECT фамилия, имя, зарплата

FROM

(SELECT фамилия, имя, зарплата,

RANK() OVER (ORDER BY зарплата DESC) R,

ROW_NUMBER() OVER (ORDER BY зарплата DESC) row_num,

DENSE_RANK() OVER (ORDER BY зарплата DESC) dense_rank

FROM преподаватели)

WHERE R = row_num AND dense_rank IN (1,2,3) OR dense_rank = 3;

Результат:

Костыркин Олег 4000

Викулина Валентина 3000

Студейкин Андрей 2500

Позднякова Любовь 2500

Комментарий:

Трое самых высокооплачиваемых имеют разный оклад.

С помощью RANK(),ROW_NUMBER(),DENSE_RANK() получаем плотный ранг, номер строки в отсортированном по убыванию зарплаты порядке и неплотный ранг соответственно. Для вывода только одного человека, получающего максимальную зарплату по каждой из зарплат, используем условие WHERE R = row_num AND dense_rank IN (1,2,3), для вывода преподавателей, которые получают такую же зарплату, как и самый низкооплачиваемый из трех высокооплачиваемых, используем условие OR dense_rank = 3.

7. Создать запрос для определения стажа работы преподавателей. Результат вывести в формате ## лет ## мес ## дней.

SELECT фамилия || ' ' || имя || ' ' || отчество AS "ФИО преподавателя", sysdate AS "Текущая дата", дата_контракта,

TRUNC(MONTHS_BETWEEN(sysdate, дата_контракта)/12)|| ' лет '||

TRUNC(MOD(MONTHS_BETWEEN(sysdate, дата_контракта), 12))|| ' мес. '||

CASE

WHEN SUBSTR(sysdate, 0, 2) < SUBSTR(дата_контракта, 0, 2)

THEN SUBSTR(LAST_DAY(дата_контракта), 0, 2) - SUBSTR(дата_контракта, 0, 2) + SUBSTR(sysdate, 0, 2)

ELSE SUBSTR(sysdate, 0, 2) - SUBSTR(дата_контракта, 0, 2)

END || ' дней' Стаж

FROM преподаватели;

Результат:

Костыркин Олег Владимирович 25.05.12 01.09.97 14 лет 8 мес. 24 дней

Викулина Валентина Ивановна 25.05.12 01.04.98 14 лет 1 мес. 24 дней

Соколов Петр Николаевич 25.05.12 01.02.78 34 лет 3 мес. 24 дней

Казанко Виталий Владимрович 25.05.12 01.09.88 23 лет 8 мес. 24 дней

Абдулов Семен Антонович 25.05.12 01.06.88 23 лет 11 мес. 24 дней

Студейкин Андрей Андреевич 25.05.12 01.05.79 33 лет 0 мес. 24 дней

Загарийчук Игорь Дмитриевич 25.05.12 01.09.77 34 лет 8 мес. 24 дней

Позднякова Любовь Алексеевна 25.05.12 10.05.79 33 лет 0 мес. 15 дней

Комментарий:

С помощью TRUNC(MONTHS_BETWEEN(sysdate, дата_контракта)/12)|| ' лет '|| определяем количество полных рабочих лет (находим количество месяцев между текущим и датой найма, делим на 12 и берем целую часть).

С помощью TRUNC(MOD(MONTHS_BETWEEN(sysdate, дата_контракта), 12))|| ' мес. '|| определяем количество полных рабочих месяцев между текущим и датой найма (берем остаток от деления и целую часть).

Если день найма больше текущего дня, то отнимаем дату найма и прибавляем текущий день месяца, получаем количество дней. Если день найма меньше текущего, то находим их разность.

8. Создать представление для вывода фамилии преподавателя, должности и зарплаты. Представление должно гарантировать, что пользователь не сможет ввести отрицательное значение зарплаты и зарплату, большую 50000.

CREATE VIEW info_prep

AS SELECT фамилия, должность, зарплата

FROM преподаватели

WHERE зарплата > 0 AND зарплата < 50000

WITH CHECK OPTION;

SELECT * FROM info_prep;

Результат:

Костыркин Профессор 4000

Викулина Доцент 3000

Соколов Ассистент 1500

Казанко Преподаватель 2000

Абдулов Доцент 3000

Студейкин Доцент 2500

Загарийчук Ассистент 2000

Позднякова Преподаватель 2500

Комментарий:

Пользователь не сможет ввести отрицательное значение зарплаты и зарплату, большую 50000 благодаря ограничению WHERE зарплата > 0 AND зарплата < 50000 и опции WITH CHECK OPTION.

9. Создать представление, которое выводит фамилию студента, оценку, дату сдачи экзамена и название предмета. Оценка должна выводиться в формате: отл, хор, удовл или неудовл. Для студентов, не сдававших экзамен, должно выводиться – Не сдавал – в столбцах Оценка, Дата сдачи экзамена и Название предмета.

CREATE VIEW зкз_студ

AS SELECT ст.фамилия,

DECODE(усп.оценка, 2, 'неудовл.', 3, 'удовл.', 4, 'хор.', 5, 'отл.', 'не сдавал') Оценка,

список_предм.дата, список_предм.название

FROM (((студенты ст INNER JOIN группы гр ON ст.номер_группы = гр.номер_группы)

INNER JOIN учебные_планы уч ON гр.код_специальности = уч.код_специальности)

LEFT JOIN успеваемость усп ON усп.номер_дисциплины = уч.номер_дисциплины

AND ст.номер_студента = усп.номер_студента)

LEFT JOIN (SELECT DISTINCT дата, усп.номер_дисциплины, дисц.название

FROM успеваемость усп INNER JOIN дисциплины дисц ON усп.номер_дисциплины = дисц.номер_дисциплины) список_предм

ON уч.номер_дисциплины = список_предм.номер_дисциплины

ORDER BY 1;

SELECT * FROM зкз_студ;

Результат:

Гриценко неудовл. 10.06.99 Физика

Гриценко не сдавал 12.06.99 Математика

Ежов не сдавал 18.06.99 Менеджмент

Ежов не сдавал 15.06.99 Экономика

Ежов не сдавал 12.06.99 Математика

Котенко не сдавал 10.06.99 Физика

Котенко не сдавал 12.06.99 Математика

Нагорный не сдавал 10.06.99 Физика

Комментарий:

Соединяем таблицы Студенты, Группы, Учебные планы, Успеваемость чтобы узнать, какие экзамены какой студент должен сдавать.

Соединяем таблицы Успеваемость и Дисциплины, чтобы узнать список предметов с датами сдачи.

Используя последний LEFT JOIN (SELECT DISTINCT дата, усп.номер_дисциплины, дисц.название

FROM успеваемость усп INNER JOIN дисциплины дисц ON усп.номер_дисциплины = дисц.номер_дисциплины), получаем студентов и их оценку на экзамене, учитывая то, что студент мог не сдавать экзамен.

Используя второй LEFT JOIN, узнаем студентов и их оценки по конкретным предметам в конкретные дни. Если студент не сдавал экзамен, то выводится, когда он должен был его сдавать.

10. Создать запрос для получения списка дисциплин, которые изучаются на всех специальностях.

SELECT спис_дисц.назв_предм

FROM (SELECT дисц.название назв_предм, COUNT(сп.код_специальности) кол_спец_по_предм

FROM (дисциплины дисц INNER JOIN учебные_планы уч ON дисц.номер_дисциплины = уч.номер_дисциплины)

INNER JOIN специальности сп ON уч.код_специальности = сп.код_специальности

GROUP BY дисц.название

ORDER BY 1) спис_дисц

INNER JOIN (SELECT COUNT(*) кол_спец FROM специальности) спис_спец

ON спис_дисц.кол_спец_по_предм = спис_спец.кол_спец;

Результат:

НАЗВ_ПРЕДМЕТА

Null

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

SELECT фамилия, стипендия,

ROUND(стипендия*100/(SUM(стипендия) OVER (PARTITION BY номер_группы))) || '%' процент

FROM студенты;

Результат:

Поляков 200 27%

Старова 250 33%

Гриценко 300 40%

Котенко 0 0%

Нагорный 200 100%

Устинов 250 50%

Усов 250 50%

Улиткин 225 53%

Комментарий:

С помощью SUM(стипендия) OVER (PARTITION BY номер_группы) выводим суммарную стипендию студентов.

С помощью ROUND(стипендия*100/(SUM(стипендия) OVER (PARTITION BY номер_группы)) находим процент от суммарной стипендии.

12. Создать запрос для получения информации о количестве полученных студентами пятерок, четверок, троек и двоек. Результат запроса должен быть представлен в виде:

WITH подсч_оцен AS

(SELECT фамилия, (SELECT COUNT(оценка) FROM успеваемость WHERE оценка = 2 AND номер_студента = ст.номер_студента) AS "Кол-во 2",

(SELECT COUNT(оценка) FROM успеваемость WHERE оценка = 3 AND номер_студента = ст.номер_студента) AS "Кол-во 3",

(SELECT COUNT(оценка) FROM успеваемость WHERE оценка = 4 AND номер_студента = ст.номер_студента) AS "Кол-во 4",

(SELECT COUNT(оценка) FROM успеваемость WHERE оценка = 5 AND номер_студента = ст.номер_студента) AS "Кол-во 5",

(SELECT COUNT(оценка) FROM успеваемость WHERE номер_студента = ст.номер_студента) AS "Итого:"

FROM студенты ст)

SELECT * FROM подсч_оцен

UNION ALL

SELECT 'Итого: ', SUM("Кол-во 2"), SUM("Кол-во 3"), SUM("Кол-во 4"), SUM("Кол-во 5"), SUM("Итого: ")

FROM подсч_оцен;

Результат:

Поляков 0 0 1 1 2

Старова 0 0 0 2 2

Гриценко 1 1 0 0 2

Котенко 0 0 0 0 0

Нагорный 0 0 0 1 1

Устинов 1 0 0 0 1

Усов 0 1 1 1 3

Улиткин 0 0 0 0 0

Ежов 0 0 0 0 0

Итого: 2 2 2 5 11

Комментарий:

С помощью WITH берем за основу таблицу, в которой осуществляем подсчет 2, 3, 4 и 5 и общее количество оценок каждого студента.

С помощью UNION ALL соединяем эту таблицу со строкой, созданной на основе этой таблицы.

Фамилия	ККол-во 5	ККол-во 4	ККол-во 3	ККол-во 2	ИИтого
ППоляков
ССтарова
ННагорный

Итого:

13. Создать запрос для получения информации о кафедрах в виде:

SELECT NVL(кафедра, 'В целом: ') AS "Кафедра",

COUNT(*) "Кол-во сотрудников",

AVG(зарплата) AS "Средняя зарплата",

MAX(зарплата) AS "Максимальная зарплата"

FROM преподаватели

GROUP BY ROLLUP(кафедра);

Результат:

Кафедра 1 4 2625 4000

Кафедра 2 5 2400 3000

В целом: 9 2500 4000

Комментарий:

С помощью COUNT(*), AVG(зарплата), MAX(зарплата) считаем количество сотрудников, среднюю зарплату и максимальную зарплату.

Используем NVL за счет ROLLUP, т.к. в результате применения GROUP BY ROLLUP(кафедра) появится строка с пустым значением в таблицу Кафедра.

	Кол-во сотрудников	Средняя зарплата	Максимальная зарплата
Кафедра1
Кафедра2
В целом:

14. Создать запрос для получения информации о фамилиях преподавателей, должностях преподавателей, фамилиях их начальников и должностях начальников.

SELECT преп1.фамилия, преп1.должность,

преп2.фамилия AS "Фамилия начальника", преп2.должность AS "Должность начальника"

FROM преподаватели преп1 LEFT JOIN преподаватели преп2

ON преп1.подчиняется = преп2.номер_преподавателя;

Результат:

Викулина Доцент Костыркин Профессор

Соколов Ассистент Викулина Доцент

Казанко Преподаватель Соколов Ассистент

Тарасова Преподаватель Абдулов Доцент

Загарийчук Ассистент Абдулов Доцент

Студейкин Доцент Абдулов Доцент

Позднякова Преподаватель Загарийчук Ассистент

Абдулов Доцент

Костыркин Профессор

Комментарий:

Соединяем таблицу Преподаватели с самой собой.

15. Удалить из таблицы Преподаватели всех преподавателей, которые подчиняются Викулиной (непосредственно и опосредованно).

DELETE FROM преподаватели

WHERE номер_преподавателя IN (SELECT номер_преподавателя

FROM преподаватели

WHERE фамилия!= 'Викулина'

START WITH фамилия = 'Викулина'

CONNECT BY PRIOR номер_преподавателя = подчиняется);

ROLLBACK;

Комментарий:

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

16. Из произвольной символьной строки удалить лишние пробелы между словами, оставив только по одному. Задачу решить при помощи раздела Model без использования регулярных выражений.

WITH str2 AS

(SELECT str1, ROWNUM rn

FROM (SELECT DISTINCT str1

FROM (SELECT '&str1' str1 FROM DUAL)

MODEL

DIMENSION BY (0 d)

MEASURES (str1)

RULES ITERATE (1000) UNTIL (iteration_number = length(str1[0]))(str1[iteration_number+1] = REPLACE(str1[iteration_number],' ',' '))))

SELECT str1 FROM str2

WHERE rn = (SELECT MAX(rn) FROM str2);

Результат:

asd ff gh - > asd ff gh

Комментарий:

На вход подаем произвольную строку, с помощью Model организуем цикл с условием получения строки из предыдущей путем замены сдвоенных пробелов на один. Итераций у цикла 1000. Для получения конечного результата присваиваем всем строкам ROWNUM и извлекаем ту, у которой он наибольший.

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

SELECT dept_id "Номер отдела", dep_name "Название отдела", cnt "Количество сотрудников",

av_sal "Средний оклад", l_n "Фамилия", sal "Оклад", j_t "Должность"

FROM (SELECT d.department_id dept_id, d.department_name dep_name,

COUNT(e.last_name) OVER (PARTITION BY d.department_id) cnt,

AVG(e.salary) OVER (PARTITION BY d.department_id) av_sal,

e.last_name l_n, e.department_id dep_id, e.salary sal, j.job_title j_t

FROM (employees e LEFT JOIN departments d

ON e.department_id = d.department_id) INNER JOIN jobs j

ON e.job_id = j.job_id)

GROUP BY GROUPING SETS((dept_id, dep_name, cnt, av_sal),(l_n, dep_id, sal, j_t))

ORDER BY (CASE WHEN dept_id IS NULL THEN dep_id ELSE dept_id END), dept_id;

Результат:

10 Administration 1 4400

Whalen 4400 Administration Assistant

20 Marketing 2 9500

Fay 6000 Marketing Representative

Hartstein 13000 Marketing Manager

30 Purchasing 6 4150

Raphaely 11000 Purchasing Manager

Tobias 2800 Purchasing Clerk

Комментарий:

Соединяем таблицы employees, departments, jobs.

С помощью функций COUNT и AVG находим количество человек и средний оклад по отделу.

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

Номер отдела	Название отдела	Количество сотрудников	Средний оклад	Фамилия	Оклад	Должность
	Purchasing
				Khoo		Purchasing Clerk
				Baida		Purchasing Clerk
				Tobias		Purchasing Clerk
				Himuro		Purchasing Clerk
				Colmenares		Purchasing Clerk
	Human Resources
				Mavris		Human Resources Representative
	Shipping		3475,56
				Weiss		Stock Manager
				Fripp		Stock Manager
				Kaufling		Stock Manager
…	……			……		…….

18. В произвольной строке, состоящей из символьных элементов, разделенных запятыми, отсортировать элементы по алфавиту. Например, символьную строку

abc,cde,ef,gh,mn,test,ss, df,fw,ewe,wwe

преобразовать к виду:

abc,cde,df,ef,ewe, fw,gh,mn,ss,test,wwe.

SELECT MAX(LTRIM(sys_connect_by_path(str, ','), ',')) RESULT

FROM (SELECT str, ROW_NUMBER() OVER (ORDER BY str) rn

FROM (SELECT regexp_substr('abc,cde,ef,gh,mn,test,ss,df,fw,ewe,wwe','[a-z]+', 1, level) str FROM DUAL

CONNECT BY regexp_substr('abc,cde,ef,gh,mn,test,ss,df,fw,ewe,wwe','[a-z]+', 1, level) IS NOT NULL

ORDER BY 1))

START WITH rn = 1

CONNECT BY PRIOR rn=rn-1;

Результат:

RESULT

abc,cde,df,ef,ewe,fw,gh,mn,ss,test,wwe

Комментарий:

С помощью regexp_substr и цикла проходим по строке и выводим каждую подстроку в новой строке.

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

19. Выделить в HTML разметке содержимое блоков с установленными атрибутами:

· тег с CSS классом content

· тег <li> с CSS классом content

Например, для разметки:

Абзац 1

Абзац 2

<ul>

<li>Элемент 1</li>

<li class="content">Элемент 2</li>

</ul>

Должно быть выделено:

Абзац 2

<li class="content">Элемент 2</li>

WITH text AS

(SELECT ' Абзац 1

Абзац 2

<ul>

<li> Элемент 1 </li>

<li class = "content"> Элемент 2 </li>

</ul>' txt FROM DUAL),

RES AS

(SELECT REGEXP_SUBSTR(txt, '.*.*', 1, level) paragraph,

REGEXP_SUBSTR(txt, '.*<li class = "content">.*', 1, level) elem

FROM text

CONNECT BY REGEXP_SUBSTR(txt, '.*.*', 1, level) IS NOT NULL OR

REGEXP_SUBSTR(txt, '.*<li class = "content">.*', 1, level) IS NOT NULL)

SELECT * FROM RES;

Результат:

PARAGRAPH ELEM

Абзац 2 <li class = "content"> Элемент 2 </li>

Комментарий:

С помощью регулярного выражения организуем поиск в сохраненном тексте REGEXP_SUBSTR(txt, '.*.*', 1, level) и REGEXP_SUBSTR(txt, '.*<li class = "content">.*', 1, level) до тех пор, пока хотя бы одна из этих подстрок не нулевая.

20. Создать запрос для получения списка городов и отделов, расположенных в них. Результаты представить в виде:

Город	Отдел
Seattle	Accounting, Administration,Benefits, Construction,Control And Credit
Toronto	Marketing

Список отделов в каждом городе должен быть отсортирован по алфавиту.

WITH dep_loc AS

(SELECT d.department_name, d.location_id, city,

ROW_NUMBER() OVER (PARTITION BY city ORDER BY department_name) row_num

FROM departments d JOIN locations l ON d.location_id = l.location_id

ORDER BY 2, 3)

SELECT city, MAX(LTRIM(sys_connect_by_path(department_name, ','), ','))

FROM dep_loc

START WITH row_num - 1 = 0

CONNECT BY PRIOR row_num = row_num - 1 AND PRIOR location_id = location_id

GROUP BY city;

Результат:

London Human Resources

Seattle Accounting,Administration,Benefits,Construction,Contracting,Corporate Tax,Executive,Finance,Government Sales,IT Helpdesk,IT Support,Manufacturing,NOC,Operations,Payroll,Purchasing,Recruiting, Retail Sales,Shareholder Services,Treasury,1 Control And Credit

Munich Public Relations

South San Francisco Shipping

Toronto Marketing

Southlake IT

Oxford Sales

Комментарий:

Соединяем таблицы departments и locations для получения таблицы, в которой есть номер региона, название города и названия департаментов. К этой таблице с помощью ROW_NUMBER и LAG добавляем два столбца, в которых порядковый номер отдела по данному городу и опережающее его на 1 число соответственно.

С помощью функции sys_connect_by_path строим дерево зависимостей отдела по каждому городу. Во внешнем SELECT берем только максимальные деревья по каждому городу.

ВАРИАНТ 10

(Базы данных Студент и Human Resources)

1. Получить одной командой SELECT отчет, содержащий нижеследующую информацию, отсортированную по возрастанию по идентификатору подразделения компании:

1. Номер строки отчета по порядку (первая строка имеет номер 1)

2. Название подразделения компании,

3. Фамилия руководителя подразделения компании,

4. Количество сотрудников, приписанных к данному подразделению,

5. Количество разных должностей сотрудников,

6. Минимальный оклад в подразделении,

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

8. Максимальный оклад в подразделении,

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

10. Средний оклад по подразделению (с точностью до сотых долей),

11. Сумма окладов по подразделению,

12. Доля суммы окладов по подразделению от суммы окладов всех подразделений (в процентах, с точностью до сотых долей процента)

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

SELECT DISTINCT

DENSE_RANK() OVER(ORDER BY D.DEPARTMENT_ID) "№",

D.DEPARTMENT_NAME "Название подразделения",

E.LAST_NAME "Фамилия руководителя",

COUNT(E1.EMPLOYEE_ID) OVER (PARTITION BY D.DEPARTMENT_ID) "Количество сотрудников",

COUNT(DISTINCT E1.JOB_ID) OVER (PARTITION BY D.DEPARTMENT_ID) "Количество должностей",

MIN(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) "Минимальный оклад",

WM_CONCAT(J.JOB_TITLE) KEEP (DENSE_RANK FIRST ORDER BY E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) "Название должности",

MAX(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) "Максимальный оклад",

WM_CONCAT(J.JOB_TITLE) KEEP (DENSE_RANK LAST ORDER BY E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) "Jobs with max salary",

ROUND(AVG(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID),2) "Средний оклад по подразделению",

SUM(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) "Сумма окладов по подразделению",

ROUND(SUM(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) / (SELECT SUM(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL) * 100, 2) AS "Доля суммы окладов"

FROM DEPARTMENTS D INNER JOIN EMPLOYEES E ON D.MANAGER_ID = E.EMPLOYEE_ID

JOIN EMPLOYEES E1 ON D.DEPARTMENT_ID = E1.DEPARTMENT_ID INNER JOIN JOBS J ON E1.JOB_ID = J.JOB_ID ORDER BY "№";

Другой способ:

SELECT DISTINCT

DENSE_RANK() OVER(ORDER BY D.DEPARTMENT_ID) "№",

D.DEPARTMENT_NAME "Название подразделения",

E.LAST_NAME "Фамилия руководителя",

COUNT(E1.EMPLOYEE_ID) OVER (PARTITION BY D.DEPARTMENT_ID) "Количество сотрудников",

COUNT(DISTINCT E1.JOB_ID) OVER (PARTITION BY D.DEPARTMENT_ID) "Количество должностей",

(SELECT MAX(LTRIM(SYS_CONNECT_BY_PATH(JOB_TITLE, ', '), ', ')) FROM (

SELECT JOB_TITLE, DEPARTMENT_ID DP, ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY JOB_TITLE DESC) RW

FROM EMPLOYEES EM JOIN JOBS JB ON EM.JOB_ID = JB.JOB_ID WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = EM.DEPARTMENT_ID))

WHERE DP = D.DEPARTMENT_ID

START WITH RW = 1

CONNECT BY PRIOR RW = RW - 1 AND PRIOR TO_CHAR(DP) = TO_CHAR(DP)) "Минимальный оклад",

(SELECT MAX(LTRIM(SYS_CONNECT_BY_PATH(JOB_TITLE, ', '), ', ')) FROM (

SELECT JOB_TITLE, DEPARTMENT_ID DP, ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY JOB_TITLE DESC) RW

FROM EMPLOYEES EM JOIN JOBS JB ON EM.JOB_ID = JB.JOB_ID WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = EM.DEPARTMENT_ID))

WHERE DP = D.DEPARTMENT_ID

START WITH RW = 1

CONNECT BY PRIOR RW = RW - 1 AND PRIOR TO_CHAR(DP) = TO_CHAR(DP)) "Название должности",

MAX(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) "Максимальный оклад",

WM_CONCAT(J.JOB_TITLE) KEEP (DENSE_RANK LAST ORDER BY E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) "Jobs with max salary",

ROUND(AVG(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID),2) "Средний оклад по подразделению",

SUM(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) "Сумма окладов по подразделению",

ROUND(SUM(E1.SALARY) OVER (PARTITION BY D.DEPARTMENT_ID) / (SELECT SUM(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL) * 100, 2) AS "Доля суммы окладов"

FROM DEPARTMENTS D INNER JOIN EMPLOYEES E ON D.MANAGER_ID = E.EMPLOYEE_ID

JOIN EMPLOYEES E1 ON D.DEPARTMENT_ID = E1.DEPARTMENT_ID INNER JOIN JOBS J ON E1.JOB_ID = J.JOB_ID ORDER BY "№";

Комментарий:

Хотел решить проблему с помощью COLECT, там бы пришлось создавать функцию. Пришлось добавлять ещё один select – вообще теперь не знаю как уложить в один на весь запрос.и внем рекурсивно получасть агрегацию строк, далее из них выбирать самую длинную и отрезать из полученного безобразия запятую

Дата добавления: 2015-08-18; просмотров: 234 | Нарушение авторских прав

<== предыдущая страница	\|	следующая страница ==>
Main_Table PK User_Constraints 6 страница	\|	Main_Table PK User_Constraints 8 страница

mybiblioteka.su - 2015-2024 год. (0.055 сек.)