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

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

FROM dual;

Результат:

	Кафедра 1	Кафедра 2	Итого
Количество преподавателей
Суммарный оклад
Средний оклад

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

Задача решена в три этапа.

На первом мы с помощью CASE определяем количество преподавателей на каждой из кафедр. Если преподаватель работает на кафедре 1, то прибавляем единичку к сумме во втором столбце, если на кафедре 2 – к сумме в третьем.

На втором и третьем этапах с помощью функций SUM и AVG считаем суммарную и среднюю зарплаты на каждой из кафедр, а также суммарную и среднюю заплаты на всех кафедрах.

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

39. Имеется таблица с колонкой, которая содержит множество значений, разделенных запятыми. Требуется создать запрос, который каждое значение выведет на отдельной строке. Например, дана таблица:

Номер	Телефон
	2-78,2-89
	2-78,2-83,8-34

Результат:

Номер	Телефон
	2-78
	2-89
	2-78
	2-83
	8-34

WITH t AS(

SELECT '952240' Номер,'2-78,2-89' Телефон FROM dual

UNION ALL

SELECT '952423' Номер,'2-78,2-83,8-34' Телефон FROM dual

)

SELECT decode(lvl, 1, Номер) Номер, Телефон

FROM (

SELECT distinct Номер, level lvl, regexp_substr(Телефон,'[^,]+',1,level) Телефон

FROM t

CONNECT BY regexp_count(Телефон, '[^,]+') >= level

ORDER BY Номер, Телефон);

Результат:

НОМЕР	ТЕЛЕФОН
	2-78
	2-89
	2-78
	2-83
	8-34

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

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

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

40. Задана таблица

A	COL1

Требуется сгруппировать значения по группам так, чтобы сумма в группе не превышала заданное число (100).

A	COL1	GRP

WITH t AS (

SELECT 1 A, 10 COL1 FROM dual

UNION ALL

SELECT 2 A, 90 COL1 FROM dual

UNION ALL

SELECT 3 A, 1 COL1 FROM dual

UNION ALL

SELECT 4 A, 5 COL1 FROM dual

UNION ALL

SELECT 5 A, 5 COL1 FROM dual

UNION ALL

SELECT 6 A, 50 COL1 FROM dual

UNION ALL

SELECT 7 A, 99 COL1 FROM dual

UNION ALL

SELECT 8 A, 2 COL1 FROM dual

UNION ALL

SELECT 9 A, 0 COL1 FROM dual

UNION ALL

SELECT 10 A, 50 COL1 FROM dual

)

SELECT A, col1, GRP

FROM t

MODEL

DIMENSION BY(ROWNUM rn)

MEASURES(A, col1, col1 summ, 1 GRP)

RULES(summ[rn > 1] = CASE WHEN summ[cv() - 1] + COL1[cv()] <= 100 THEN summ[cv() - 1] + COL1[cv()] ELSE COL1[cv()] END,

GRP[rn > 1] = CASE WHEN summ[cv() - 1] + COL1[cv()] <= 100 THEN GRP[cv()-1] ELSE GRP[cv()-1] +1 END)

ORDER BY rn;

Результат:

A	COL1	GRP

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

Задача решена с помощью раздела Model.

В столбце summ мы считаем сумму всех строк, которые в таблице находятся выше текущей строки. Для каждой строки высчитывается сумма значений столбца summ предыдущей строки и столбца col1 текущей. Если эта сумма оказывается больше 100, то в текущей строке в столбце summ записывается значение col1, а если нет, то summ[cv()-1]+col1[cv()]. Функция cv() определяет индекс текущей строки.

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

ВАРИАНТ 17

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

1. Создать запрос для вывода 3 дисциплин, по которым на сессии было получено максимальное количество пятерок. Если по каким-то дисциплинам получено такое же количество пятерок, как и по дисциплине с наименьшим количеством пятерок из первой тройки, то она тоже должна попасть в список. Задачу решить с использованием аналитических функций.

select оценка, название

--плотное ранжирование дисциплин по числу пятерок

from (select оценка, название, dense_rank() over(order by cnt desc) rnk

-- подсчет числа пятерок по предмету

from (select оценка, название, count(название) cnt

from успеваемость a inner join предмет b on a.номер_предмета = b.номер_предмета

where оценка = 5

group by оценка, название)

)

where rnk <= 3;

Результат:

5 Математика

5 Физика

2. Есть таблица, в которой значения в двух столбцах Obj1 и Obj2 определяют номера объектов, между которыми имеется связь. Один объект может быть связан с несколькими, те в свою очередь могут быть связаны друг с другом. В результате, значения образуют связанные множества. На входе имеем номер некоторого объекта, нужно получить все элементы множества, к которому оно относится.

with t0 as (

select 1 value1, 2 value2 from dual

union all

select 2, 3 from dual

union all

select 2, 4 from dual

union all

select 8, 6 from dual

union all

select 5, 6 from dual

),

t as (

select value1, value2 from t0

union

select value2, value1 from t0

)

select distinct value1

from t

connect by nocycle

prior value1 = value2

start with value1 = &s;

Результат:

4 rows selected

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

t0 имитирует исходную таблицу, создаем иерархический запрос, который проходит по связанным элементам и строит из них дерево, и по определении конца множества (попадание в бесконечный цикл) завершаем работу запроса. NOCYCLE – позволяет в запросе с предложением CONNECT BY распознать, что встретился бесконечный цикл и прекратить выполнение запроса без выдачи ошибки (вместо возврата ошибки зацикливания при выполнении предложения CONNECT BY)

3. Имеется таблица с тремя столбцами: именем, фамилией и коэффициентом, показывающим сколько раз должна повторяться запись. Требуется написать запрос, выводящий таблицу, содержащую строки с именами и фамилиями сотрудников. Число строк для каждого сотрудника должно определяться значением коэффициента. То есть, если Иван Петров имел коэффициент 3, а Сергей Сидоров – коэффициент 5, то в результатах запроса должны быть 3 строки для сотрудника Петрова и 5 строк для Сидорова.

Строки должны быть объединены в группы и отсортированы по фамилии и имени. Кроме того, должны быть пронумерованы элементы внутри группы и должна присутствовать сквозная нумерация. Этот запрос должен работать для произвольного количества строк в исходной таблице.

Пример выходного отчёта:

Сквозной №	№ в группе	Имя	Фамилия
		Иван	Петров
		Иван	Петров
		Иван	Петров
		Сергей	Сидоров
		Сергей	Сидоров
		Сергей	Сидоров
		Сергей	Сидоров
		Сергей	Сидоров

--создание новой таблицы

create table new_tbl (Имя varchar2(25), Фамилия varchar2(25), Коэффициент number(2));

--заполнение новой таблицы

insert into new_tbl values ('Иван', 'Петров', 3);

insert into new_tbl values ('Сергей', 'Сидоров', 5);

select * from new_tbl;

--выво данных из таблицы

select rownum as "Сквозной номер", номер, Имя, Фамилия

--цикл для нумерации повторений, а так же обеспечивающий

--их необходимое число указанное в поле коэффициент

from(select distinct level номер, имя, фамилия

from new_tbl

connect by level <= коэффициент

order by 2, 3, 1);

Результат:

1 1 Иван Петров

2 2 Иван Петров

3 3 Иван Петров

4 1 Сергей Сидоров

5 2 Сергей Сидоров

6 3 Сергей Сидоров

7 4 Сергей Сидоров

8 5 Сергей Сидоров

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

undefine d1

undefine d2

with d as

(select dates

from(select TRUNC(s + x) dates

from (select level x, to_date('&&d1','DD-MM-YYYY')-1 s from dual connect by level <= to_date('&&d2','DD-MM-YYYY') + 1 - to_date('&d1','DD-MM-YYYY')))

order by dates)

select * from d

minus

select distinct dates

from d inner join преподаватели п

on d.dates = п.дата_контракта and п.дата_контракта < to_date('&d2','DD-MM-YYYY')

and п.дата_контракта > to_date('&d1','DD-MM-YYYY');

Результат:

25.04.79

26.04.79

27.04.79

28.04.79

29.04.79

30.04.79

01.05.79

02.05.79

03.05.79

04.05.79

05.05.79

06.05.79

07.05.79

08.05.79

09.05.79

10.05.79

11.05.79

12.05.79

13.05.79

14.05.79

15.05.79

21 rows selected

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

Создаем табличку состоящая и диапазона дат, границы которого мы задаем, затем вычитаем из всего кол-ва дат, те даты, которые являются датами найма сотрудников и попадают в заданный диапазон

5. Создать запрос, позволяющий выводить информацию о студентах и преподавателях (Фамилия с инициалами, Зарплата/Стипендия, Статус – преподаватель или студент).

--вывод студентов

SELECT to_char(фамилия||' '||SUBSTR(имя,1,1)||'.'||SUBSTR(отчество,1,1)||'.') AS ФИО,

стипендия AS "Зарплата/стипендия",

'Студент' AS "Статус"

FROM студенты

UNION

--вывод преподавателей

SELECT to_char(фамилия||' '||SUBSTR(имя,1,1)||'.'||SUBSTR(отчество,1,1)||'.') AS ФИО,

зарплата AS "Зарплата/стипендия",

'Преподаватель' AS "Статус"

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

ORDER BY 1;

Результат:

Абдулов С.А. 3000 Преподаватель

Викулина В.И. 3000 Преподаватель

Гриценко В.Н. 300 Студент

Загарийчук И.Д. 2000 Преподаватель

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

Костыркин О.В. 4000 Преподаватель

Котенко А.Н. 0 Студент

Нагорный Е.В. 250 Студент

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

Поляков А.А. 200 Студент

Соколов П.Н. 1500 Преподаватель

Старова Л.М. 250 Студент

Студейкин А.А. 2500 Преподаватель

6. Получить информацию о подчиненности таблиц в схеме в виде:

ИмяТаблицы1(ИмяFK1 ссылается на ИмяТаблицы2/ИмяPK2)

ИмяТаблицы2(ИмяFK2 ссылается на ИмяТаблицы3/ИмяPK3) ……

--вспомогательная таблица, содержащая иноформацию о подчиненности таблиц

select distinct uc.table_name || '(' || uc.constraint_name || ' cсылается на ' || a.table_name || '/' || uc.r_constraint_name || ')' as "Table info"

from user_constraints uc join user_cons_columns ucc

on uc.constraint_name = ucc.constraint_name

join user_constraints a

on a.constraint_name = uc.r_constraint_name

join user_cons_columns b on a.constraint_name = b.constraint_name

where uc.constraint_type like 'R' and ucc.position = b.position;

Результат:

JOB_HISTORY(JHIST_DEPT_FK cсылается на DEPARTMENTS/DEPT_ID_PK)

JOB_HISTORY(JHIST_EMP_FK cсылается на EMPLOYEES/EMP_EMP_ID_PK)

ПРЕПОДАВАТЕЛИ(SYS_C00180083 cсылается на ПРЕПОДАВАТЕЛИ/SYS_C00180082)

EMPLOYEES(EMP_MANAGER_FK cсылается на EMPLOYEES/EMP_EMP_ID_PK)

COUNTRIES(COUNTR_REG_FK cсылается на REGIONS/REG_ID_PK)

УСПЕВАЕМОСТЬ(SYS_C00180098 cсылается на СТУДЕНТЫ/SYS_C00180072)

ПРЕДМЕТ(SYS_C00180090 cсылается на ПРЕПОДАВАТЕЛИ/SYS_C00180082)

LOCATIONS(LOC_C_ID_FK cсылается на COUNTRIES/COUNTRY_C_ID_PK)

JOB_HISTORY(JHIST_JOB_FK cсылается на JOBS/JOB_ID_PK)

EMPLOYEES(EMP_DEPT_FK cсылается на DEPARTMENTS/DEPT_ID_PK)

EMPLOYEES(EMP_JOB_FK cсылается на JOBS/JOB_ID_PK)

DEPARTMENTS(DEPT_LOC_FK cсылается на LOCATIONS/LOC_ID_PK)

УСПЕВАЕМОСТЬ(SYS_C00180099 cсылается на ПРЕДМЕТ/SYS_C00180089)

DEPARTMENTS(DEPT_MGR_FK cсылается на EMPLOYEES/EMP_EMP_ID_PK)

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

Узнаем для каждой таблицы поля в которых содержится внешний ключ и его имя, далее находим на какой первичный ключ ссылается наш внешний и находим какое поле содержит этот ключ и таблицу, в котором находится это поле

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

--создание представления

Create view Оценки AS (

select фамилия,

--изменение формата вывода оценки

decode(Оценка, 5, 'Отл.', 4, 'Хор.', 3, 'Удовл.', 2, 'Неудовл.', 'Не сдавал.') AS Оценка,

-- изменение формата вывода даты сдачи

decode(Оценка, null, ' - ', to_char(у.дата)) AS Дата,

--изменение формата вывода предмета

decode(Оценка, null, ' - ', д.название) AS Предмет

from студенты с left outer join успеваемость у

on с.номер_студента = у.номер_студента

left outer join дисциплины д

on д.номер_дисциплины = у.номер_дисциплины);

select * from Оценки;

Результат:

Поляков Отл. 10.06.99 Физика

Нагорный Отл. 12.06.99 Математика

Поляков Хор. 12.06.99 Математика

Старова Хор. 10.06.99 Математика

Гриценко Удовл. 11.06.99 Экономика

Котенко Не сдавал. - -

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

Объединяем таблицы «студенты», «успеваемость» и «дисциплины» для того, чтобы узнать какие дисциплины, и на какую оценку сдавали студенты. Если студенты не сдавали экзамены то, с помощью функции decode мы заменяем пустые поля на «Не сдавал».

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

SELECT DISTINCT НАЗВАНИЕ

FROM (SELECT Д.НАЗВАНИЕ, COUNT(У.КОД_СПЕЦИАЛЬНОСТИ) СПЕЦ, С.КОЛВО

FROM УЧЕБНЫЕ_ПЛАНЫ У

JOIN ДИСЦИПЛИНЫ Д

ON У.НОМЕР_ДИСЦИПЛИНЫ = Д.НОМЕР_ДИСЦИПЛИНЫ

CROSS JOIN (SELECT COUNT(КОД_СПЕЦИАЛЬНОСТИ) КОЛВО FROM

СПЕЦИАЛЬНОСТИ) С

GROUP BY Д.НАЗВАНИЕ, С.КОЛВО)

WHERE СПЕЦ = КОЛВО

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

С помощью объединения таблицы «учебные_планы» С таблицей «дисциплины» узнаем какие дисциплины изучаются на разных специальностях и далее объединяем с таблицей «дисциплины», чтобы узнать название этих дисциплин.

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

select фамилия, стипендия, round(стипендия/sum(стипендия) over(partition by номер_группы), 3) as " % "

from студенты

order by номер_группы;

Результат:

Поляков 200 0,267

Старова 250 0,333

Гриценко 300 0,4

Котенко 0 0

Нагорный 200 1

Устинов 250 0,5

Усов 250 0,5

Улиткин 225 0,529

Ежов 200 0,471

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

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

--вспомогательная таблица

with

aux_tbl as(select фамилия,

--подсчет "5"

(select count(оценка) from успеваемость where оценка = 5 and номер_студента = с.номер_студента) as "Количество 5",

--подсчет "4"

(select count(оценка) from успеваемость where оценка = 4 and номер_студента = с.номер_студента) as "Количество 4",

--подсчет "3"

(select count(оценка) from успеваемость where оценка = 3 and номер_студента = с.номер_студента) as "Количество 3",

--подсчет "2"

(select count(оценка) from успеваемость where оценка = 2 and номер_студента = с.номер_студента) as "Количество 2",

--Итог

(select count(оценка) from успеваемость where номер_студента = с.номер_студента) as "Всего:"

from студенты с)

select *

from aux_tbl

union all

--всего 5, 4, 3, 2, а так же общее количество оценок

select 'Итого:', sum("Количество 5"), sum("Количество 4"), sum("Количество 3"), sum("Количество 2"), sum("Всего:")

from aux_tbl;

Результат:

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

Старова 0 1 0 0 1

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

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

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

Итого: 2 2 1 0 5

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

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

select distinct кафедра as " ",

count(*)OVER(PARTITION BY кафедра) as "Кол-во сотрудников",

avg(зарплата) OVER(PARTITION BY кафедра) as "Средняя з.п.",

min(зарплата)OVER(PARTITION BY кафедра) as "Минимальная з.п."

from ПРЕПОДАВАТЕЛИ

UNION all

SELECT 'В целом:', COUNT(*), AVG(зарплата), Min(зарплата)

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

Результат:

Кафедра 1 4 2625 1500

Кафедра 2 5 2400 2000

В целом: 9 2500 1500

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

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

12. Определить отделы, в которых суммарная зарплата сотрудников выше средней суммарной зарплаты по отделам, расположенным в том же городе. Вывести информацию о наименовании отдела, суммарной заработной плате отдела и средней заработной плате города, где расположен отдел.

--вспомогательная таблица для определения ID департамента, его названия--суммарных зарплат по нему и его расположения

with

aux_tbl as(select DISTINCT e.department_id, department_name,

sum(salary) over (partition by e.department_id) dep_salary, location_id

from employees e join departments d on e.department_id = d.department_id)

--средняя зарплата по департаментам в городе

select department_name, dep_salary, (select avg(dep_salary) from aux_tbl

where location_id = a.location_id) avg_city

from aux_tbl a

where dep_salary > (select avg(dep_salary) from aux_tbl

where location_id = a.location_id)

order by department_id;

Результат:

Executive 58000 31840

Finance 51600 31840

13. Создать запрос для вывода информации о студентах, у которых интервал между датами сдачи экзаменов составляет менее трех дней. Информацию вывести в виде:

Фамилия	Дисциплина 1	Дата сдачи1	Дисциплина 2	Дата сдачи 2

select с.фамилия "Фамилия", д1.название "Дисциплина1", у1.дата "Дата сдачи 1",

д2.название "Дисциплина2", у2.дата "Дата сдачи 2"

from студенты с join успеваемость у1

on с.номер_студента = у1.номер_студента

join успеваемость у2 on у1.номер_студента=у2.номер_студента

join дисциплины д1 on у1.номер_дисциплины = д1.номер_дисциплины

join дисциплины д2 on у2.номер_дисциплины = д2.номер_дисциплины

where (у2.дата - у1.дата)>0 and (у2.дата - у1.дата)<3;

where (у2.дата - у1.дата)>= 0 and (у2.дата - у1.дата)<3 and у2.дата!= у1.дата

Результат:

Старова Физика 10.06.99 Математика 12.06.99

Поляков Физика 10.06.99 Математика 12.06.99

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

Объединяя таблицы «дисциплины» и «успеваемость» сами с собой по номерам дисциплины и по номерам студентов соответственно и задавая условие на разность дат, получаем те дисциплины и даты, которые соответствуют нашему условию.

14. Вывести сведения о сотрудниках, которые подчиняются тем же непосредственным руководителям, что и сотрудники Rajs или Abel, работают в тех же подразделениях компании, что и указанные сотрудники.

Требуется вывести: Фамилию сотрудника, Название должности сотрудника, Фамилию непосредственного руководителя сотрудника, Название подразделения компании, где работает сотрудник. Сведения о сотрудниках Rajs и Abel выводить не нужно

Сведения должны быть отсортированы по возрастанию: по названию подразделения компании, где работает сотрудник; по фамилии сотрудника.

SELECT t.last_name, jobs.job_title, e.last_name manager, d.department_name

FROM

(select last_name, job_id, manager_id, department_id

from employees

where (manager_id, department_id) in

(

select manager_id, department_id

from employees

where last_name in ('Rajs', 'Abel')

)

and last_name not in ('Rajs', 'Abel'))t

INNER JOIN employees e

ON t.manager_id = e.employee_id

INNER JOIN jobs

ON jobs.job_id = t.job_id

INNER JOIN departments d

ON d.department_id = t.department_id

ORDER BY d.department_name, t.last_name;

Результат:

Hutton Sales Representative Zlotkey Sales

Johnson Sales Representative Zlotkey Sales

Livingston Sales Representative Zlotkey Sales

Taylor Sales Representative Zlotkey Sales

Davies Stock Clerk Mourgos Shipping

Feeney Shipping Clerk Mourgos Shipping

Grant Shipping Clerk Mourgos Shipping

Matos Stock Clerk Mourgos Shipping

OConnell Shipping Clerk Mourgos Shipping

Vargas Stock Clerk Mourgos Shipping

Walsh Shipping Clerk Mourgos Shipping

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

В подзапросах находим сотрудников:

- у которых начальники такие же как у Rajs или Abel;

- которые работают в тех же отделах, что и Rajs или Abel и указываем, что записи о Rajs и Abel нам в таблице не нужны. Далее получившуюся таблицу объединяем с таблицами «employees», «jobs» и «departments» для нахождения фамилий, должностей и названий подразделений соответственно.

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

WITH y as

(SELECT x, rownum rn

FROM (select distinct x

from (select '&x' x from dual)

model

dimension by (0 d)

measures (x)

rules iterate (1000) UNTIL (iteration_number=length(x[0])) (x[iteration_number+1] = replace(x[iteration_number], ' ', ' '))))

SELECT x

FROM y

WHERE rn = (SELECT max(rn) FROM y);

Результат:

&x

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

Дата добавления: 2015-08-18