- •Дана символьная строка, в которой отдельные слова разделены любым количеством пробелов.
- •//Условие задачи:
- •Определить список последовательностей подчиненности от преподавателей, не имеющих начальника, до преподавателей, не имеющих подчиненных. Результат представить в виде:
- •Посчитать кол-во 29.02 между двумя заданными датами
- •Показать накопленную сумму сотрудников по их отделам.
- •Вывести все даты за 2011 год и соответствующие дни недели без использования иерархических запросов и Model.
- •В произвольной строке, состоящей из символьных элементов, разделенных запятыми, отсортировать элементы по алфавиту. Например, символьную строку
- •Определить временной интервал между заданной и текущей датами. Результат вывести в виде: ## лет ## мес ## дней, где # обозначает цифру.
- •Вывести фамилии сотрудников, начальники которых работают в другой стране.В результат вывести:
- •Для таблиц схемы, имеющих индексы вывести:
- •Определить дату начала ближайшего к заданной дате Уимблдонского турнира, который начинается за шесть недель до первого понедельника августа
- •Для каждой таблицы схемы вывести:
- •Определить сколько лет, месяцев и дней осталось до президентских выборов в сша, которые проводятся по високосным годам в первый понедельник после первого вторника ноября.
- •Дана таблица из двух столбцов: 1 - строка, 2 – число. Требуется написать запрос, в результате
- •Написать запрос, выдающий отчёт о суммарных выплатах сотрудникам, непосредственно
- •Определить временной интервал между датами. Результат вывести в виде: ## лет ## мес ## дней, где # обозначает цифру.
- •Вывести фамилии всех сотрудников через запятую столько раз сколько символов в их фамилии
- •Для каждой таблицы схемы вывести:
- •Создать запрос для определения сумм зарплат сотрудников от сотрудников, не имеющих менеджеров, до сотрудников, не имеющих подчиненных.
- •Выборы президента сша
- •Плотный ранг
- •Для всех связей между таблицами схемы вывести:
- •Из заданных наборов символов исключить те наборы символов, которые отличаются только порядком. Например, заданы наборы:
- •В названии отдела вывести только второе слово, если название состоит из двух и более слов, иначе вывести первое слово.
- •Создать запрос для определения списка городов, в которых расположены департаменты, суммарная заработная плата в которых выше средней суммарной заработной платы в департаментах этого города.
- •Имеется таблица:
- •Определить сумму цифр в произвольной символьной строке.
- •Для каждой таблицы схемы вывести:
- •Для каждой таблицы схемы вывести:
- •Сформировать отчёт, содержащий номер отдела, название отдела, имена и фамилии сотрудников, а также их зарплату в виде:
- •Условия нет
- •Условия нет
- •Вывести фамилии сотрудников, получающих зарплату выше средней в отделе, в котором они работают.
- •Не помню задание, похоже на костяна, мишаню и лену
- •Для всех таблиц схемы вывести:
- •Задания нет
- •Сумма цифр в строке
- •Создать запрос для вывода фамилий, последних должностей и дат приема на работу сотрудников, информация о работе
- •"Определить ближайший к заданной дате год, когда 29 февраля придется на воскресенье. "
- •В таблицу записана информация об удачных и неудачных попытках подключения к базе данных.
Из заданных наборов символов исключить те наборы символов, которые отличаются только порядком. Например, заданы наборы:
rtyew
trwye
rtgbfdsa
tgrbafsd
Результат:
rtyew
rtgbfdsa
create table table1 (str varchar2(100)); INSERT ALL INTO table1 values('rtyew') INTO table1 values('trwye') INTO table1 values('rtgbfdsa') INTO table1 values('tgrbafsd') SELECT * FROM dual; WITH t as ( SELECT max(str) word, rd FROM(SELECT sys_connect_by_path(ch,' ') str, rd FROM(SELECT ch, rd, row_number() over (partition by rd order by ch) rn FROM(SELECT distinct substr(str,level,1) ch,level, rd FROM(SELECT str, rowid rd FROM table1) CONNECT by level <=length(str) ) ) START WITH rn = 1 CONNECT by prior rn = rn - 1 and prior rd = rd ) GROUP BY rd ) SELECT * FROM table1 WHERE rowid not in (SELECT max(rd) FROM t GROUP BY word);
Для каждой таблицы вывести:
1. Имя таблицы
2. Имя первого столбца первичного ключа
3. Имя второго столбца первичного ключа
4. Общее число столбцов первичного ключа
5. Имя первой подчиненной таблицы
6. Имя второй подчиненной таблицы
7. Общее кол-во подчиненных таблиц у данной рассматриваемой таблицы
8. Имя первого столбца со значениями по умолчанию
9. Первое значение по умолчанию
10. Имя второго столбца со значениями по умолчанию
11. Второе значение по умолчанию
Select T1.*, T2.Dep_T1, T2.Dep_T2, Co.Num As Dep_T_Number
From (Select Pk.*, Dd.Default_Cn1, DD.Dd1, Dd.Default_Cn2, DD.Dd2
From (Select G.Table_Name, G.Min_Cn Pk1, G.Pk2, H.Pk_Columns
From
--ищем первичные ключи
(Select E.*, Nullif(F.Max_Cn, E.Min_Cn) Pk2 -- NULLIF дает NULL если выражения равны
From (Select U_C.Table_Name, Min(U_C_C.Column_Name) Min_Cn
From User_Constraints U_C Inner Join User_Cons_Columns U_C_C
ON u_c.table_name = u_c_c.table_name And U_C.Constraint_Name = U_C_C.Constraint_Name
Where U_C.Constraint_Type ='P' -- = primary_key
Group By U_C.Table_Name Order By U_C.Table_Name) E
Inner Join
(Select U_C.Table_Name, Max(U_C_C.Column_Name) Max_Cn
From User_Constraints U_C Inner Join User_Cons_Columns U_C_C
On U_C.Table_Name = U_C_C.Table_Name And U_C.Constraint_Name = U_C_C.Constraint_Name
Where U_C.Constraint_Type ='P'
Group By U_C.Table_Name Order By U_C.Table_Name) F On E.Table_Name=F.Table_Name ) G
Inner Join
(Select Distinct U_C.Table_Name, Count(U_C.Constraint_Type) As Pk_Columns
From User_Constraints U_C Inner Join User_Cons_Columns U_C_C
On U_C.Table_Name = U_C_C.Table_Name And U_C.Constraint_Name = U_C_C.Constraint_Name
Where U_C.Constraint_Type = 'P'
Group By U_C.Table_Name Order By U_C.Table_Name) H On G.Table_Name=H.Table_Name) Pk
Left Outer Join
--ищем столбцы со значениями по умолчанию
(Select C.Table_Name Table_Name1, C.Max_Cn Default_Cn1, C.Data_Default Dd1, C.Default_Cn2, C.Dd2
From (Select A.*, Nullif(B.Min_Cn, A.Max_Cn) Default_Cn2,
Case When Nullif(B.Min_Cn, A.Max_Cn) Is Null Then Null Else B.Data_Default End as Dd2
From (Select A1.*, A2.Data_Default
From (Select Table_Name, Max(Column_Name) Max_Cn
From User_Tab_Columns
Where Data_Default Is Not Null
Group By Table_Name) A1
Inner Join User_Tab_Columns A2
On A1.Table_Name = A2.Table_Name And A1.Max_Cn = A2.Column_Name) A
Inner Join
(Select B1.*, B2.Data_Default
From (Select Table_Name, Min(Column_Name) Min_Cn
From User_Tab_Columns
Where Data_Default Is Not Null
Group By Table_Name) B1
Inner Join User_Tab_Columns B2
On B1.Table_Name = B2.Table_Name And B1.Min_Cn = B2.Column_Name) B On A.Table_Name = B.Table_Name) C
) Dd On Pk.Table_Name = Dd.Table_Name1) T1
Left Outer Join
-- ищем подчиненные таблицы
(Select D1.Table_Name, D1.Dep As Dep_T1, D2.Dep As Dep_T2
From (Select Table_Name, Dep
From (Select N1.Table_Name, N1.Dep, Count(*) N
From (Select U_C.Table_Name, U_C_С.Table_Name as Dep, Rownum Num
From User_Constraints U_C Left Join User_Constraints U_C_С
On U_C.Constraint_Name = U_C_С.R_Constraint_Name
Where U_C_С.Table_Name Is Not Null
Order By U_C.Table_Name) N1
Inner Join
(Select U_C.Table_Name, U_C_С.Table_Name as Dep, Rownum Num
From User_Constraints U_C Left Join User_Constraints U_C_С
On U_C.Constraint_Name = U_C_С.R_Constraint_Name
Where U_C_С.Table_Name Is Not Null Order By U_C.Table_Name) N2
On N1.Table_Name = N2.Table_Name And N1.Num <= N2.Num
Group By N1.Table_Name, N1.Dep Order By N1.Table_Name, N ) Where N = 1 ) D1
Left Join
(Select Table_Name, Dep
From (Select N1.Table_Name, N1.Dep, Count(*) N
From (Select U_C.Table_Name, U_C_С.Table_Name Dep, Rownum Num
From User_Constraints U_C Left Join User_Constraints U_C_С
On U_C.Constraint_Name = U_C_С.R_Constraint_Name
Where U_C_С.Table_Name Is Not Null
Order By U_C.Table_Name) N1
Inner Join
(Select U_C.Table_Name, U_C_С.Table_Name Dep, Rownum Num
From User_Constraints U_C Left Join User_Constraints U_C_С
On U_C.Constraint_Name = U_C_С.R_Constraint_Name
Where U_C_С.Table_Name Is Not Null
Order By U_C.Table_Name) N2
On N1.Table_Name = N2.Table_Name And N1.Num <= N2.Num
Group By N1.Table_Name, N1.Dep Order By N1.Table_Name, N) Where N = 2 ) D2 On D1.Table_Name = D2.Table_Name
Order By D1.Table_Name) T2 On T1.Table_Name=T2.Table_Name
Left Outer Join
--считаем подчиненные таблицы
(Select Tm, Count(Tm) Num From
(Select U_C.Table_Name As Tm, U_C_С.Table_Name
From User_Constraints U_C Left Join User_Constraints U_C_С
On U_C.Constraint_Name = U_C_С.R_Constraint_Name
Where U_C_С.Table_Name Is Not Null
Order By U_C.Table_Name)
Group By Tm) Co On Co.Tm=T1.Table_Name;
Написать запрос, выдающий отчёт о суммарных выплатах сотрудникам, непосредственно подчиняющихся заданному руководителю по идентификаторам должностей (поле Job_id). Непосредственное подчинение предполагает подчинение на первом уровне. Номер руководителя может встречаться в отчете лишь дважды.
Пример отчёта:
Номер руководителя |
Должность |
Кол-во сотрудников |
Выплаты |
Вид выплаты |
100 |
AD_VP |
2 |
34000 |
Зарплата сотрудников в должности AD_VP |
|
MK_MAN |
1 |
13000 |
Зарплата сотрудников в должности MK_MAN |
|
PU_MAN |
1 |
11000 |
Зарплата сотрудников в должности PU_MAN |
|
SA_MAN |
5 |
79650 |
Зарплата сотрудников в должности SA_MAN |
|
ST_MAN |
5 |
36400 . |
Зарплата сотрудников в должности ST_MAN |
100 |
|
14 |
174050 |
Суммарная зарплата у руководителя 100 |
101 |
AC_MGR |
1 |
12000 |
Зарплата сотрудников в должности AC_MGR |
|
FI_MGR |
1 |
12000 |
Зарплата сотрудников в должности FI_MGR |
|
HR_REP |
1 |
6500 |
Зарплата сотрудников в должности HR_REP |
|
PR_REP |
1 |
10000 |
Зарплата сотрудников в должности PR_REP |
|
AD_ASST |
1 |
4400 |
Зарплата сотрудников в должности AD_ASST |
101 |
|
5 |
44900 |
Суммарная зарплата у руководителя 100 |
|
|
34 |
301250 |
Общий итог |
with t1
as (Select manager_id, job_id, emp_count, summ, m_id, j_id, rownum r
from
(Select manager_id, job_id, count(employee_id) emp_count, sum(salary) summ, grouping(manager_id) m_id, grouping (job_id) j_id
from employees
where manager_id is not null
group by rollup (manager_id, job_id)
order by manager_id)),
t2 as
(select manager_id, min(r) min_r
from t1
group by manager_id)
--Определяем номер строки первого вхождения для каждого руководителя.
select
case j_id when 1 then to_char(manager_id)
else case r when min_r then to_char(manager_id)
else ' ' end end "Номер руководителя",
--Выводим номер менеджера только в первой и в итоговой строчке.
job_id "Должность",
emp_count "Кол-во сотрудников",
summ "Выплаты",
case m_id when 1 then 'Общий итог'
else case j_id when 1 then 'Суммарная зарплата у руководителя ' || manager_id
else 'Зарплата сотружников в должности ' || t1.job_id end end "Вид выплаты"
--Заполняем столбец "Вил выплаты" в зависимости от значения в grouping столбцах.
from t1 left join t2 using(manager_id)
order by manager_id, job_id;