1. Имеется таблица:

Id	Linked_id	Part
0	-1	Оглавление
1	0	Глава 1
2	1	Часть 1
3	1	Часть 2
4	0	Глава 2
5	4	Часть 1
6	4	Часть 2

Получить результат в виде:

Оглавление

1 Глава 1

1.1 Часть 1

1.2 Часть 2

2 Глава 2

2.1 Часть 1

2.2 Часть 2

Количество глав и частей произвольное.

WITH

b AS (SELECT a.*, ROW_NUMBER() OVER(PARTITION BY Linked_id ORDER BY Part) RW FROM (

SELECT 0 Id, -1 Linked_id, 'Содержание' Part FROM dual

UNION ALL

SELECT 1 Id, 0 Linked_id, 'Глава 1' Part FROM dual

UNION ALL

SELECT 2 Id, 1 Linked_id, 'Часть 1' Part FROM dual

UNION ALL

SELECT 3 Id, 1 Linked_id, 'Часть 1' Part FROM dual

UNION ALL

SELECT 4 Id, 0 Linked_id, 'Глава 2' Part FROM dual

UNION ALL

SELECT 5 Id, 4 Linked_id, 'Часть 1' Part FROM dual

UNION ALL

SELECT 6 Id, 5 Linked_id, 'Часть 2' Part FROM dual) a)

SELECT LTRIM(LTRIM(LTRIM(SYS_CONNECT_BY_PATH(RW, '.'), '.'),'1'),'.') || ' ' || PART "Оглавление"

FROM b

START WITH Id = 0

CONNECT BY PRIOR Id = LINKED_ID;

39. Определить сумму цифр в произвольной символьной строке.

UNDEFINE STR

SELECT '&&STR' AS "STRING",

1 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '1', '')), 0)) +

2 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '2', '')), 0)) +

3 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '3', '')), 0)) +

4 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '4', '')), 0)) +

5 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '5', '')), 0)) +

6 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '6', '')), 0)) +

7 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '7', '')), 0)) +

8 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '8', '')), 0)) +

9 * (LENGTH('&STR') - NVL(LENGTH(REPLACE('&STR', '9', '')), 0)) AS "SUM"

FROM DUAL;

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

SELECT t1.LAST_NAME

FROM EMPLOYEES t1

JOIN (

-- средняя зарплата по каждому отделу

SELECT DEPARTMENT_ID, AVG(SALARY) avsal

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

) t2

USING (DEPARTMENT_ID)

WHERE t1.SALARY > avsal

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

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

? Текст первого ограничения Check;

? Количество столбцов в первом ограничении Check;

? Текст второго ограничения Check;

? Количество столбцов во втором ограничении Check;

? Общее количество ограничений Check.

WITH

-- вспомогательные таблички

C_TYPE AS ( -- пронумеруем полученные строки

SELECT ROWNUM rn, tn, cn, sc

FROM ( -- ограничения типа Check (C) `56 rows

SELECT TABLE_NAME tn, CONSTRAINT_NAME cn, SEARCH_CONDITION sc

FROM USER_CONSTRAINTS -- таблица ограничений, заданных пользователями

WHERE CONSTRAINT_TYPE = 'C'

ORDER BY tn)),

CNT_X AS ( -- 1й (min_rn) и последний индекс (max_rn) ограничений из выборки C_TYPE для каждой таблицы `17 rows

SELECT tn, MIN(rn) min_rn, MAX(rn) max_rn

FROM C_TYPE

GROUP BY tn),

RES_TAB AS ( -- известно, что TABLE_NAME (tn) между индексами min_rn и max_rn те же, что и под этими индексами

-- cp (constraint position) - номер ограничения таблицы

-- cc (count constraints) - число ограничений для каждой таблицы

-- C_TYPE + {cp,cc} - rn `56 rows

SELECT C_TYPE.tn, cn, sc, ROWNUM - min_rn + 1 cp, max_rn - min_rn + 1 cc

-- все строки связаны - null-ов не будет

FROM C_TYPE INNER JOIN CNT_X

ON C_TYPE.tn = CNT_X.tn),

COL_CNT AS ( -- NATURAL JOIN соединяет таблицы по столбцам с одинаковыми именами, а имменно по CONSTRAINT_NAME

-- cnt - число столбцов в каждом ограничении

-- ограничения типа Check (C)`56 rows

SELECT CONSTRAINT_NAME cn, cnt

FROM USER_CONSTRAINTS NATURAL JOIN ( -- подсчет числа столбцов в каждом ограничении `92 rows

SELECT CONSTRAINT_NAME, COUNT(COLUMN_NAME) cnt

FROM USER_CONS_COLUMNS

GROUP BY CONSTRAINT_NAME)

WHERE CONSTRAINT_TYPE ='C')

-- выводимый запрос `17 rows

SELECT t1.tn "Имя таблицы",

t1.sc "Текст 1го ограничения",

t1.cnt "№ столбцов в 1ом ограничении",

t2.sc "Текст 2го ограничения",

t2.cnt "№ столбцов во 2ом ограничении",

t1.cc "Общее количество ограничений"

FROM ( -- {RES_TAB.tn, cn, sc, cp, cc} + cnt `56 rows

SELECT * FROM RES_TAB

-- соединение по cn (CONSTRAINT_NAME)

NATURAL JOIN COL_CNT) t1

-- соединение по tn; вывод всех строк t1 и связанных из t2

LEFT JOIN ( -- {RES_TAB.tn, cn, sc, cp, cc} + cnt `9 rows

-- cp = '2' - с текстом второго ограничения

SELECT * FROM RES_TAB

-- соединение по cn (CONSTRAINT_NAME)

NATURAL JOIN COL_CNT

-- с текстом второго ограничения

WHERE cp = '2') t2

ON t1.tn = t2.tn

WHERE t1.cp = '1' -- с текстом первого ограничения

42. Вывести все даты за 2011 год и соответствующие дни недели без использования иерархических запросов и Model.

-- даты и дни вычисляются в зависимости от номера ячейки, начиная с выбранной даты (01.01.2011)

SELECT TO_DATE('01.01.2011') + level-1 || ', ' ||

TO_CHAR(TO_DATE('01.01.2011') + level-1, 'Day') "Dates and Days"

FROM DUAL

-- создаем столько ячеек, сколько дней в выбранном году (2011)

CONNECT BY level <= ADD_MONTHS('01.01.2011', 12) - TO_DATE('01.01.2011')

43. В произвольной символьной строке убрать все лидирующие и конечные пробелы, а между словами оставить только по одному пробелу.

SELECT REPLACE(REPLACE(REPLACE(TRIM('&your_text'), ' ', ' *'), '* ', ''), '*', '') AS Res

FROM DUAL;

С помощью регулярного выражения:

SELECT REGEXP_REPLACE((TRIM('&your_text')), '( ){2,}', ' ' )

from dual;

44. Создать запрос для определения списка городов, в которых расположены департаменты, суммарная заработная плата в которых выше средней суммарной заработной платы в департаментах этого города. В результат вывести:

- название города;

- название департамента;

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

- суммарная зарплата в департаменте;

2 варианта: 1) считать среднюю зарплату по тем отделам, в которых кто-либо работает или 2) по всем отделам, вне зависимости от наличия в них сотрудников

Без учёта наличия сотрудников:

SELECT town,summdept,summavgsal,dep

FROM

(SELECT town, round(avg(s),0) summavgsal

FROM (

SELECT d.department_name AS dept, l.city AS town, NVL(sum(e.salary),0) s

FROM departments d

FULL OUTER JOIN employees e USING(department_id)

INNER JOIN locations l USING(location_id)

WHERE city IS NOT NULL

GROUP BY d.department_name, l.city

)

GROUP BY town

) s1

JOIN (

SELECT l.city city,d.department_name dep, NVL(SUM(e.salary),0) summdept

from departments d

FULL OUTER JOIN employees e USING(department_id)

INNER JOIN locations l USING(location_id)

where city IS NOT NULL

GROUP BY d.department_name, l.city) s2

ON s1.town=s2.city

WHERE summdept > summavgsal;

С учётом наличия сотрудников в отделе:

SELECT town,summsal ,averagesummsal,dep FROM

(SELECT town,AVG(sal) averagesummsal FROM (

SELECT d.department_name AS depn, l.city AS town, round(SUM(e.salary),0)AS sal

FROM employees e

INNER JOIN (departments d

INNER JOIN locations l

ON (d.location_id=l.location_id) AND (d.manager_id IS NOT NULL) )

ON e.department_id=d.department_id

GROUP BY d.department_name, l.city)

GROUP BY town) s1

JOIN (

SELECT l.city city,d.department_name dep,SUM(e.salary) summsal

FROM employees e

INNER JOIN (departments d

INNER JOIN locations l

ON (d.location_id=l.location_id) AND (d.manager_id IS NOT NULL) )

ON e.department_id=d.department_id

GROUP BY d.department_name,l.city) s2

ON s1.town=s2.city

WHERE summsal>averagesummsal;