51. Вывести фамилии сотрудников, получающих зарплату выше средней в отделе, в котором они работают.

select * from employees e1

where salary > (

select AVG(salary) from employees e2

where e2.department_id = e1.department_id)

52. Не помню задание, похоже на костяна, мишаню и лену

WITH COUNTS AS

(SELECT COUNT(CONSTRAINT_NAME) AS COUNTS,

TABLE_NAME

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'C'

GROUP BY TABLE_NAME

)

SELECT UC.TABLE_NAME,

UC.CONSTRAINT_NAME AS FIRST_CONSTRAINT,

COUNTS AS COUNT_OF_CONSTRAINTS,

UTC.NULLABLE,

UCC.COLUMN_NAME

FROM USER_CONSTRAINTS UC

JOIN USER_CONS_COLUMNS UCC

ON UC.TABLE_NAME = UCC.TABLE_NAME

AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME

JOIN USER_TAB_COLUMNS UTC

ON UCC.COLUMN_NAME = UTC.COLUMN_NAME

AND UTC.TABLE_NAME = UC.TABLE_NAME

JOIN COUNTS ON UC.TABLE_NAME = COUNTS.TABLE_NAME

WHERE UC.CONSTRAINT_TYPE = 'C'

AND UC.CONSTRAINT_NAME IN

(SELECT CONSTRAINT_NAME

FROM

( SELECT DISTINCT UC2.TABLE_NAME,

UC2.CONSTRAINT_NAME

FROM USER_CONSTRAINTS UC2

JOIN USER_CONS_COLUMNS UCC

ON UCC.CONSTRAINT_NAME = UC2.CONSTRAINT_NAME

AND UCC.TABLE_NAME = UC2.TABLE_NAME

WHERE UC2.CONSTRAINT_TYPE = 'C'

ORDER BY UC2.TABLE_NAME,

UC2.CONSTRAINT_NAME

) T

WHERE T.TABLE_NAME = UC.TABLE_NAME

AND ROWNUM = 1

)

ORDER BY UC.TABLE_NAME;

53. Создать внешнюю таблицу для чтения данных из текстового файла init.ora. Таблица должна содержать два столбца: Имя параметра и Значение. Директория Init. Файл для примера - во вложении. Создать запрос к внешней таблице, который позволит найти общее количество файлов управления (Control_Files).

/*CREATE TABLE init_par

(Имя_параметра VARCHAR2(32),

Значение VARCHAR2(256))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY Init

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

NOBADFILE

NOLOGFILE

FIELDS TERMINATED BY '=' LRTRIM

REJECT ROWS WITH ALL NULL FIELDS (Имя_параметра, Значение))

LOCATION ('init.ora'))

REJECT LIMIT UNLIMITED; */

SELECT length(Значение) - length(replace(Значение, ',')) + 1 Количество

FROM init_par

WHERE Имя_параметра = 'control_files';

54. Для всех таблиц схемы вывести:

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

with DATA as (select u.table_name, min(u.constraint_name) min_cn, u.constraint_type, a.cc "Общее кол-во check на табл."

from user_constraints u

JOIN (select table_name, count(table_name) cc from user_constraints where constraint_type='C'

group by table_name) a on (u.table_name=a.table_name)

where constraint_type='C'

group by u.table_name, u.constraint_type, a.cc

order by u.table_name)

select d.table_name, d.min_cn, ucc.column_name , co.nulls,d."Общее кол-во check на табл." from data d

JOIN user_cons_columns ucc on(d.min_cn=ucc.constraint_name)

JOIN col co on (ucc.column_name=co.cname)and (d.table_name=co.tname);

-----------------------------------------------------------------

select table_name, count(table_name) from all_constraints where owner='RENSAM' and constraint_type='C'

group by table_name;

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

В результат вывести пять столбцов:

1. Имя ссылочного ограничения целостности (внешнего ключа)

2. Имя таблицы, которой принадлежит данное ссылочное ограничение целостности (внешний ключ)

3. Имя столбца таблицы, который входит во внешний ключ (foreignkey)

4. Имя таблицы, на которую ссылается данный внешний ключ

5. Имя столбца таблицы, на которую ссылается данный внешний ключ, которому соответствует столбец, указанный в п.3

Результат отсортировать по возрастанию по столбцам 1, 2 и 3, перечисленным выше.

select distinct uc.table_name, uc.constraint_name, uc.r_constraint_name,

ucc.column_name,

a.table_name as "Dest_Table", b.column_name as "Dest_col"

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

order by uc.table_name, uc.constraint_name, ucc.column_name;