5. Определить список последовательностей подчиненности от преподавателей, не имеющих начальника, до преподавателей, не имеющих подчиненных. Результат представить в виде:

Костыркин-> Викулина-> Студейкин->Соколов->Казанко (не имеет подчиненных)

SELECT LTRIM(SYS_CONNECT_BY_PATH(фамилия,'->'),'->') || ' (не имеет подчиненных)' as res

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

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

(SELECT distinct подчиняется

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

WHERE подчиняется IS NOT NULL)

START WITH подчиняется IS NULL

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

6. Посчитать кол-во 29.02 между двумя заданными датами

Select &&date1 As Date1, &&date2 As Date2, Trunc(((Months_Between(&date2, &date1))/48-(1- (((Months_Between(&date1, To_Date('29.02.0004')))/48)-Trunc(((Months_Between(&date1, To_Date('29.02.0004')))/48),0)))+1),0) As "SUM 29.02 :"

From Dual;

7. Показать накопленную сумму сотрудников по их отделам.

Select Last_Name, Salary, Department_Id,

(Select Sum(Salary) From (Select Last_Name, Salary, Department_Id, Rownum Rnum From Employees where Department_Id is not Null) Elist2

Where Elist1.Rnum >= Elist2.Rnum And (Elist1.Department_Id = Elist2.Department_Id)) "Cumulative Sum"

From (Select Last_Name, Salary, Department_Id, Rownum Rnum From Employees where Department_Id is not Null ) Elist1

Order By Department_Id;

8. Для каждой таблицы вывести:

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;