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

• Имя таблицы;

• Имя первого столбца первого ограничения уникальности;

• Имя второго столбца первого ограничения уникальности;

• Общее число столбцов в первом ограничении уникальности;

• Имя первого столбца второго ограничения уникальности;

• Имя второго столбца второго ограничения уникальности;

• Общее число столбцов во втором ограничении уникальности;

• Общее число ограничений уникальности.

select rez1.table_name as "Table name", rez1.c1 as "1'st column unique constr",

rez1.c2 as "2'nd column unique constr",rez1."sum1" as "Total count 1st unique constr",

rez1.c1_2 as "1'st column 2'nd unique constr", rez1.c2_2 as "2'nd column 2'nd unique constr" ,

rez1.sum2 as "Total count 2'nd unique constr",rez2.Cnt as "Total"

from (select r.table_name, r1.c1,r1.c2,r1."sum1", r1.c1_2, r1.c2_2,r1.sum2

from (select table_name, min(zzz) mi

from (select t1.*, rownum zzz

from (select distinct q.table_name, q.constraint_name

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U') t1

left join

(select distinct q.table_name, q.constraint_name

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U') t2

on t1.table_name = t2.table_name and t1.constraint_name != t2.constraint_name)

group by table_name) r

left join

(select t1.table_name, t1.c1, t1.c2, t1."sum1", t2.table_name table_name2,

t2.c1 c1_2, t2.c2 c2_2, t2."sum1" sum2, rownum zzz

from (select t1.table_name, c1,c2, t."sum1",t.constraint_name

from (select x.table_name,x.column_name c1,y.column_name c2,x.constraint_name

from (select q.table_name, q.constraint_name,w.column_name

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U' and position = 1) x

left join

(select q.constraint_name,w.column_name

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U' and position = 2) y

on x.constraint_name = y.constraint_name) t1

inner join

(select q.constraint_name, max(position) as "sum1"

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U'

group by q.constraint_name) t

on t1.constraint_name = t.constraint_name) t1

left join

(select t1.table_name, c1,c2, t."sum1",t.constraint_name

from (select x.table_name,x.column_name c1,y.column_name c2,x.constraint_name

from (select q.table_name, q.constraint_name,w.column_name

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U' and position = 1) x

left join

(select q.constraint_name,w.column_name

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U' and position = 2) y

on x.constraint_name = y.constraint_name) t1

inner join

(select q.constraint_name, max(position) as "sum1"

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U'

group by q.constraint_name) t

on t1.constraint_name = t.constraint_name) t2

on t1.table_name = t2.table_name and t1.constraint_name != t2.constraint_name) r1

on r.table_name = r1.table_name and r.mi = r1.zzz) rez1

left join

(select table_name, count(*) as Cnt

from (select distinct q.table_name, q.constraint_name

from user_constraints q left join user_cons_columns w

on q.constraint_name = w.constraint_name

where constraint_type = 'U')

group by table_name) rez2

on rez1.table_name = rez2.table_name;

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

Запрос:

SELECT

CASE

WHEN e.salary IS NOT NULL

THEN ' '

ELSE TO_CHAR(d.department_id)

END department_id,

CASE

WHEN e.salary IS NOT NULL

THEN ' '

ELSE d.department_name

END department_name,

CASE

WHEN e.salary IS NOT NULL

THEN ' '

ELSE TO_CHAR(COUNT(ee.employee_id))

END workers,

CASE

WHEN e.salary IS NOT NULL

THEN ' '

ELSE TO_CHAR(ROUND(AVG(ee.salary),2))

END avgsal,

CASE

WHEN e.last_name IS NULL

THEN ' '

ELSE e.last_name

END last_name,

CASE

WHEN e.salary IS NULL

THEN ' '

ELSE TO_CHAR(e.salary)

END salary,

CASE

WHEN j.job_title IS NULL

THEN ' '

ELSE j.job_title

END job_title

FROM departments d

JOIN employees e

ON e.department_id = d.department_id

INNER JOIN employees ee

ON ee.department_id = d.department_id

JOIN jobs j

ON e.job_id = j.job_id

GROUP BY rollup((d.department_id, d.department_name),(e.last_name, e.salary, j.job_title))

ORDER BY d.department_id,

e.salary nulls FIRST;

SELECT d_id "Номер отдела", d_name "Название отдела", cnt "Количество сотрудников", av "Средняя зарплата",

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

FROM (SELECT d.department_id d_id, d.department_name d_name,

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

AVG (e.salary) OVER (PARTITION BY d.department_id) av,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((d_id, d_name, cnt, av),(l_n,dep_id,sal,j_t))

ORDER BY (CASE WHEN d_id IS NULL THEN dep_id

ELSE d_id

END), d_id