Задачи - RanMax/BasicOracle GitHub Wiki

Условие

Одной командой SELECT выбрать сотрудников, для которых

• в таблице истории занятия должностей зафиксированы переходы на другую должность
• в таблице истории занятия должностей отсутствуют сведения об их первой должности, которую они занимали при приеме на работу в компанию.

• идентификатор сотрудника,
• фамилию сотрудника,
• дата, когда сотрудник был принят на работу,
• идентификатор должности, которую занимает сотрудник,
• идентификатор подразделения, к которому в данный момент приписан сотрудник.

• дата, когда сотрудник был принят на работу,
• фамилия сотрудника.

• Решение с использованием групповых сравнений и вложенных подзапросов

SELECT e.employee_id, e.last_name, e.hire_date, e.job_id, e.department_id  
FROM hr.employees e
WHERE e.employee_id = ANY(SELECT j.employee_id FROM hr.job_history j)
AND (e.employee_id, e.hire_date) <> ALL(SELECT j.employee_id, j.start_date FROM hr.job_history j)
ORDER BY e.last_name ASC, e.hire_date ASC

• Решение с использованием виртуальной таблицы и соединением с историей занимаемых должностей

WITH emp AS (
  SELECT e.employee_id, e.hire_date, e.last_name, e.job_id, e.department_id 
  FROM employees e, job_history j
  WHERE j.employee_id = e.employee_id
)
SELECT distinct emp.employee_id, emp.last_name, emp.hire_date, emp.job_id, emp.department_id 
FROM emp, job_history j
WHERE emp.employee_id = j.employee_id (+)
AND emp.hire_date = j.start_date (+)
AND j.employee_id IS NULL
ORDER BY emp.last_name ASC, emp.hire_date ASC

• Решение с использованием EXISTS, функций аггрегирования и вложенного подзапроса

SELECT e.employee_id, e.last_name, e.hire_date, e.job_id, e.department_id
FROM employees e
WHERE EXISTS (
  SELECT 1 
  FROM job_history jj 
  WHERE jj.employee_id = e.employee_id 
  GROUP BY jj.employee_id
  HAVING MIN(start_date) <> e.hire_date
)
ORDER BY e.last_name ASC, e.hire_date ASC

• Решение с использованием одностороннего соединения таблиц и аггрегирующих функций

SELECT jh.employee_id, e.last_name, e.hire_date, e.job_id, e.department_id
FROM job_history jh LEFT JOIN employees e ON jh.employee_id = e.employee_id
GROUP BY (jh.employee_id, e.last_name, e.hire_date, e.job_id, e.department_id)
HAVING MIN(start_date) <> hire_date
ORDER BY e.last_name ASC, e.hire_date ASC

Условие

Выбрать сотрудников с минимальным окладом по подразделению,

• сотрудников не приписанных к подразделению выводить не нужно,
• нельзя использовать:
  • подзапросы, в том числе во фразе FROM
  • конструкции CONNECT BY, START WITH, GROUP BY, WITH
  • аналитические функции

• Решение через одностороннее соединение таблиц

SELECT e1.department_id, e1.last_name,e1.salary 
FROM employees e1, employees e2
WHERE e1.salary > e2.salary (+)
  AND e1.department_id = e2.department_id (+) 
  AND e2.employee_id IS NULL 
  AND e1.department_id IS NOT NULL
ORDER BY e1.department_id, e1.last_name, e1.salary, e1.employee_id

Условие

Имеется таблица OLYMP_TRIP, в которой хранятся данные о рейсах, выполняемых транспортом компании.

CREATE TABLE olymp_trip(
   id      NUMBER
 , per_beg DATE NOT NULL
 , per_end DATE NOT NULL
 , CONSTRAINT olymp_trip
      PRIMARY KEY(id)
 , CONSTRAINT olymp_trip#C#per_beg#per_end
      CHECK(per_end >= per_beg)
 , CONSTRAINT olymp_trip#C#per_beg
      CHECK(per_beg = TRUNC(per_beg))
 , CONSTRAINT olymp_trip#C#per_end
      CHECK(per_end = TRUNC(per_end))
   );

Все даты хранятся с точностью до суток (время не хранится).

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

Одной командой SELECT вывести сведения обо всех периодах, когда компания выполняла какие-либо рейсы, то есть смежные и/или пересекающиеся по времени рейсы должны быть объединены в один период.

Каждый период должен встречаться в результате только один раз.

В результат должны быть выведены:

1. Дата начала периода,
2. Дата окончания периода.

INSERT INTO olymp_trip VALUES (1,to_date('01.01.2012', 'DD.MM.YYYY'), to_date('01.01.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (2,to_date('01.01.2012', 'DD.MM.YYYY'), to_date('05.01.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (3,to_date('05.01.2012', 'DD.MM.YYYY'), to_date('06.01.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (4,to_date('10.01.2012', 'DD.MM.YYYY'), to_date('15.01.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (5,to_date('20.01.2012', 'DD.MM.YYYY'), to_date('10.02.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (6,to_date('21.01.2012', 'DD.MM.YYYY'), to_date('22.01.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (7,to_date('25.01.2012', 'DD.MM.YYYY'), to_date('10.02.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (8,to_date('01.03.2012', 'DD.MM.YYYY'), to_date('10.03.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (9,to_date('01.03.2012', 'DD.MM.YYYY'), to_date('10.03.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (10,to_date('01.03.2012', 'DD.MM.YYYY'), to_date('15.03.2012', 'DD.MM.YYYY'));
INSERT INTO olymp_trip VALUES (11,to_date('07.01.2012', 'DD.MM.YYYY'), to_date('08.01.2012', 'DD.MM.YYYY'));

Предлагаемые решения

• Решение через нахождение крайних правых и левых рейсов периода

SELECT per_beg, MIN(per_end) per_end
FROM
(
  SELECT DISTINCT o1.per_beg
  FROM Olymp_Trip o1 LEFT JOIN olymp_trip o2
  ON (o1.per_beg > o2.per_beg AND o1.per_beg <= o2.per_end + 1)
  WHERE o2.id IS NULL
) t1,
(
  SELECT DISTINCT o1.per_end
  FROM Olymp_Trip o1 LEFT JOIN olymp_trip o2
  ON (o1.per_end < o2.per_end AND o1.per_end + 1 >= o2.per_beg)
  WHERE o2.id IS NULL
) t2
WHERE t2.per_end >= t1.per_beg
GROUP BY per_beg
ORDER BY per_beg

• Аналогичное решение только вместо вложенных селектов - виртуальные таблицы

WITH t1 AS (
  SELECT DISTINCT o1.per_beg
  FROM Olymp_Trip o1 LEFT JOIN olymp_trip o2
  ON (o1.per_beg > o2.per_beg AND o1.per_beg <= o2.per_end + 1)
  WHERE o2.id IS NULL
),
t2 AS (
  SELECT DISTINCT o1.per_end
  FROM Olymp_Trip o1 LEFT JOIN olymp_trip o2
  ON (o1.per_end < o2.per_end AND o1.per_end + 1 >= o2.per_beg)
  WHERE o2.id IS NULL
)
SELECT per_beg, MIN(per_end) per_end
FROM t1, t2
WHERE t2.per_end >= t1.per_beg
GROUP BY per_beg
ORDER BY per_beg

• Решение через объединение рейсов внутри одного периода с использованием иерархических запросов и функций аггрегирования

SELECT MIN(per_beg) per_beg, per_end FROM (
   SELECT per_beg, per_end, PRIOR per_beg, PRIOR per_end, CONNECT_BY_ISLEAF flag
   FROM olymp_trip t
   CONNECT BY per_end > PRIOR per_end
   AND per_beg <= PRIOR per_end + 1
)
WHERE flag = 1
GROUP BY per_end
ORDER BY per_end

Условие

Используя таблицы REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS, построить (показать) иерархию объектов "Регион – Страна – Местоположение – Подразделение" для региона name = ' Americas '.

Иерархия должна быть построена (показана) одной командой SELECT.

В результате вывести:

• номер уровня, на котором находится в иерархии данный объект (LEVEL),
• имя объекта, дополненное слева (LEVEL -1)*3 пробелами.

Предлагаемые решения

• Решение с использованием иерархического запроса, операций над множествами и вложенного запроса

SELECT LEVEL, LPAD(' ', (LEVEL-1)*3) || name FROM 
(
  SELECT 'REG_' || region_id id, region_name name, NULL pid FROM regions r WHERE region_name = 'Americas'
  UNION ALL
  SELECT 'COU_' || country_id id, country_name name, 'REG_' || region_id pid FROM countries c
  UNION ALL
  SELECT 'LOC_' || location_id id, city name, 'COU_' || country_id pid FROM locations l
  UNION ALL
  SELECT 'DEP_' || department_id id, department_name name, 'LOC_' || location_id pid FROM departments d
) 
START WITH pid IS NULL
CONNECT BY PRIOR id = pid
ORDER SIBLINGS BY name

• Решение с использованием рекурсивного подзапроса и виртуальной таблицы

WITH obj AS(
  SELECT 'REG_' || region_id id, region_name name, NULL pid FROM regions r WHERE region_name = 'Americas'
  UNION ALL
  SELECT 'COU_' || country_id id, country_name name, 'REG_' || region_id pid FROM countries c
  UNION ALL
  SELECT 'LOC_' || location_id id, city name, 'COU_' || country_id pid FROM locations l
  UNION ALL
  SELECT 'DEP_' || department_id id, department_name name, 'LOC_' || location_id pid FROM departments d
), 
ie (lev, id, name, pid) AS
(
SELECT 1, id, name, pid FROM obj WHERE name = 'Americas'
UNION ALL
SELECT ie.lev + 1, obj.id, obj.name, obj.pid FROM ie, obj WHERE ie.id = obj.pid
) SEARCH DEPTH FIRST BY name ASC SET orderval
CYCLE id SET cyclemark TO '+' DEFAULT '-'
SELECT lev, LPAD(' ', (lev-1)*3) || name name FROM ie
ORDER BY orderval

Условие

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

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

1. Имя таблицы
2. Количество столбцов в таблице

Предлагаемые решения

• Решение с использованием вложенного подзапроса во фразе HAVING

SELECT ut.table_name, count(*) COL_COUNT
FROM user_tab_columns uc, user_tables ut
WHERE uc.TABLE_NAME = ut.TABLE_NAME
GROUP BY ut.table_name
HAVING count(*) = (
  SELECT max(count(*)) 
  FROM user_tab_columns uc, user_tables ut
  WHERE uc.TABLE_NAME = ut.TABLE_NAME
  GROUP BY ut.table_name
)
ORDER BY table_name

• Решение с использованием виртуальной таблицы, аггрегирующих функций и вложенного подзапроса

WITH tab_col AS(
  SELECT ut.table_name, count(*) COL_COUNT
  FROM user_tab_columns uc, user_tables ut
  WHERE uc.TABLE_NAME = ut.TABLE_NAME
  GROUP BY ut.table_name
)
SELECT table_name, col_count 
FROM tab_col 
WHERE col_count = (SELECT MAX(col_count) FROM tab_col)
ORDER BY table_name

• Решение с использованием виртуальной таблицы, аггрегирующих и аналитических функций

WITH tab AS (
  SELECT ut.table_name, count(*) num_col, MAX(COUNT(1)) OVER() max_col
  FROM user_tab_columns uc, user_tables ut
  WHERE uc.TABLE_NAME = ut.TABLE_NAME
  GROUP BY ut.table_name
)
SELECT table_name, num_col
FROM tab

Условие

Для снижения конкуренции за ресурсы при изменении данных необходимо строить индексы по столбцам, входящим в ограничение внешнего ключа (Foreign key).

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

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

1. Название таблицы.
2. Название ссылочного ограничения целостности (внешнего ключа).
3. Название неиндексированного столбца даного ссылочного ограничения целостности.

Для отладки решения рекомендуется создать дополнительную таблицу ORDER_ITEMS_LINE_INFO:

CREATE TABLE order_items_line_info(
   order_id      NUMBER(12)
 , item_line_id  NUMBER(3)
 , info_line_id  NUMBER(3)
 , info_date     DATE NOT NULL
 , info_text     VARCHAR2(100 CHAR) NOT NULL
 , CONSTRAINT order_items_line_info_pk
      PRIMARY KEY(order_id, item_line_id, info_line_id)
 , CONSTRAINT order_items_line_info_fk
      FOREIGN KEY(order_id, item_line_id)
      REFERENCES order_items(order_id, line_item_id)
  ON DELETE CASCADE);

В таблице ORDER_ITEMS_LINE_INFO есть внешний ключ (ORDER_ID, ITEM_LINE_ID), ссылающийся на таблицу ORDER_ITEMS.

В таблице ORDER_ITEMS_LINE_INFO для поддержания ограничения целостности первичного ключа (Primary key) будет построен индекс по столбцам (ORDER_ID, ITEM_LINE_ID, INFO_LINE_ID).

Все столбцы внешнего ключа содержатся в индексе и находятся в нём в тех же позициях, что и во внешнем ключе. Поэтому сведения об ограничении целостности ORDER_ITEMS_LINE_INFO_FK в результат выводить не нужно.

Предлагаемые решения

• Решение через соединение таблиц

SELECT c.table_name, c.constraint_name, cc.column_name 
FROM user_constraints c, user_cons_columns cc, user_ind_columns ic
WHERE c.constraint_type = 'R' 
  AND cc.constraint_name = c.constraint_name
  AND cc.table_name = ic.TABLE_NAME(+)
  AND cc.position = ic.COLUMN_POSITION (+)
  AND cc.COLUMN_NAME = ic.COLUMN_NAME (+)
  AND ic.TABLE_NAME IS NULL
ORDER BY c.table_name, c.constraint_name, cc.column_name

• Решение с использованием виртуальных таблиц

WITH t1 AS (
  SELECT uc.table_name, uc.constraint_name,cc.COLUMN_NAME,cc.POSITION 
  FROM user_constraints uc,user_cons_columns cc 
  WHERE uc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME AND constraint_type = 'R'
), t2 AS (
  SELECT ui.table_name,ic.COLUMN_NAME, ic.COLUMN_POSITION
  FROM user_indexes ui, user_ind_columns ic 
  WHERE ui.INDEX_NAME = ic.INDEX_NAME
)
SELECT t1.table_name, t1.constraint_name,t1.COLUMN_NAME FROM t1, t2 
WHERE t1.table_name = t2.table_name(+) 
  AND t1.column_name = t2.column_name(+) 
  AND t1.position = t2.column_position(+)
  AND t2.table_name IS NULL

Условие

Числа Фибоначчи — элементы числовой последовательности 0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, … в которой каждое последующее число равно сумме двух предыдущих чисел. (Названы по имени средневекового математика Леонардо Пизанского, известного как Фибоначчи).

Более формально, последовательность чисел Фибоначчи {Fn} задается линейным рекуррентным соотношением: F(0) = 0, F(1) = 1, F(n) = F(n-1) + F(n-2), n >=2.

То есть:

F(2) = F(0) + F(1) = 0 + 1 = 1
F(3) = F(1) + F(2) = 1 + 1 = 2
F(4) = F(2) + F(3) = 1 + 2 = 3
F(4) = F(2) + F(3) = 2 + 3 = 5
. . .

Используя только таблицу DUAL вычислить и вывести в результат все числа Фибоначчи в диапазоне от 1 до 1000 (1 <= n_fib <= 1000). Числа в результате не должны повторяться и должны быть отсортированы по возрастанию.

То есть в результате должен получиться столбец из 15 чисел, отсортированных по возрастанию:

N_FIB
-----
    1
    2
    3
    5
    8
   13
   21
   34
   55
   89
  144
  233
  377
  610
  987

Внимание! Решения, подобные приведенному ниже, засчитаны не будут.

-- Решение методом подгонки под ответ

SELECT 1 AS n_fib FROM dual UNION ALL
SELECT 2 FROM dual UNION ALL
SELECT 3 FROM dual UNION ALL
. . .
SELECT 987 FROM dual;

Предлагаемые решения

• Решиние с использованием рекурсивного запроса

WITH fib (f1, f2) AS
(
  SELECT 1 f1, 1 f2 FROM dual
  UNION ALL
  SELECT f1+f2, f1
  FROM fib
  WHERE f1+f2 <= 1000
)
SELECT f1 FROM fib

• Решение с использованием конструкции MODEL

SELECT ch FROM dual
MODEL
  DIMENSION BY(1 n)
  MEASURES (CAST(0 AS NUMBER) ch)
  RULES ITERATE(1000) UNTIL (ch[iteration_number-1] + ch[iteration_number] > 1000)(
    ch[iteration_number] = CASE WHEN iteration_number = 0 THEN 1
                                WHEN iteration_number = 1 THEN 2
                                ELSE ch[iteration_number-2] + ch[iteration_number-1]
                           END
  )
ORDER BY ch

Условие

Билеты для проезда в городском транспорте имеет шестизначный десятичный номер и буквенно-цифровую серию.

Нумерация каждой серии билетов начинается с 000001.

Существует примета, что билет, у которого сумма трех первых цифр равна сумме трёх последних цифр – это "Счастливый билет".

Одной командой SELECT вывести количество "счастливых билетов" в каждой серии (одно число).

Предлагаемые решения

• Решение с использованием рекурсивного запроса и расчленением номера билета при помощи функции получения остатка от деления

WITH tickets (t_num) AS (
  SELECT 1 FROM dual
  UNION ALL
  SELECT t_num + 1 FROM tickets WHERE t_num < 999999
)
SELECT COUNT(t_num) 
FROM tickets 
WHERE MOD(t_num,10) + TRUNC(MOD(t_num,100)/10) + TRUNC(MOD(t_num,1000)/100) = 
  TRUNC(MOD(t_num,10000)/1000) + trunc(MOD(t_num,100000)/10000) + TRUNC(MOD(t_num,1000000)/100000)

• Решиние с использованием иерархического запроса и расчленением номера билета при помощи функции получения остатка от деления

WITH tickets (t_num) AS (
  SELECT LEVEL FROM dual CONNECT BY LEVEL < 1000000
)
SELECT COUNT(t_num) 
FROM tickets 
WHERE MOD(t_num,10) + TRUNC(MOD(t_num,100)/10) + TRUNC(MOD(t_num,1000)/100) = 
  TRUNC(MOD(t_num,10000)/1000) + trunc(MOD(t_num,100000)/10000) + TRUNC(MOD(t_num,1000000)/100000)

• Решиние с использованием иерархического запроса и расчленением номера билета при помощи преобразования номера в строку и выделение подстроки

WITH tickets (t_num) AS (
  SELECT TO_CHAR(LEVEL, 'fm000000') FROM dual CONNECT BY LEVEL < 1000000
)
SELECT COUNT(t_num)
FROM tickets 
WHERE TO_NUMBER(SUBSTR(t_num,1,1)) + TO_NUMBER(SUBSTR(t_num,2,1)) + TO_NUMBER(SUBSTR(t_num,3,1)) =
  TO_NUMBER(SUBSTR(t_num,4,1)) + TO_NUMBER(SUBSTR(t_num,5,1)) + TO_NUMBER(SUBSTR(t_num,6,1))

WITH ch AS (
  SELECT (LEVEL - 1) ch FROM dual CONNECT BY LEVEL <= 10
)
SELECT COUNT(ch1.ch || ch2.ch || ch3.ch || ch4.ch || ch5.ch || ch6.ch)
FROM ch ch1, ch ch2, ch ch3, ch ch4, ch ch5, ch ch6
WHERE ch1.ch + ch2.ch + ch3.ch = ch4.ch + ch5.ch + ch6.ch 
  AND ch1.ch + ch2.ch + ch3.ch + ch4.ch + ch5.ch + ch6.ch <> 0

Условие

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

CREATE TABLE olymp_blanksep_info(
   id NUMBER(9) 
, info VARCHAR2(80 CHAR) NOT NULL
, CONSTRAINT olymp_blanksep_info#P
     PRIMARY KEY(id));

Написать команду SELECT, выводящую следующие данные:

1. Числа из поля ID,

2. Скорректированные данные поля INFO:

• должны быть убраны начальные и хвостовые пробелы,
• между всеми словами должно быть только по одному пробелу
• Возрастающая сортировка должна быть выполнена по скорректированным (п.2) данным поля INFO.

• Использование регулярных выражений (regular expressions) запрещается.
• Функцию REPLACE разрешается использовать не более трех раз.

INSERT INTO olymp_blanksep_info VALUES (1, '   В  ту    пору зимняя погода   ');
INSERT INTO olymp_blanksep_info VALUES (2, 'Стояла долго на дворе');
INSERT INTO olymp_blanksep_info VALUES (3, '  Зимы ждала,  ждала     природа');

Предлагаемые решения

• Решение с помощью регулярных выражений

SELECT id, TRIM(REGEXP_REPLACE(info, '(\s)+', ' ')) info 
FROM olymp_blanksep_info
ORDER BY info

• Решение с использованием рекурсивного запроса и функции LISTAGG

WITH tab (id, sym_pos, sym, info) AS 
(
  SELECT id, 1, SUBSTR(info,1,1), info FROM olymp_blanksep_info o1
  UNION ALL
  SELECT id, sym_pos + 1, SUBSTR(info, sym_pos + 1, 1), info FROM tab WHERE sym_pos + 1 <= LENGTH (info)
), 
sym AS
(
SELECT t1.id, t1.sym_pos, t1.sym
FROM tab t1, tab t2 
WHERE t1.id = t2.id (+) AND t1.sym_pos + 1 = t2.sym_pos(+) AND (t1.sym <> ' ' OR t2.sym <> ' ')
)
SELECT DISTINCT id, TRIM(LISTAGG(sym) WITHIN GROUP (ORDER BY sym_pos) OVER (PARTITION BY id)) info FROM sym
ORDER BY info

• Решение с помощью оператора MODEL

SELECT id, TRIM(res) info FROM olymp_blanksep_info
MODEL
  PARTITION BY (id)
  DIMENSION BY (0 n)
  MEASURES (info, CAST('' AS VARCHAR2(4000)) res)
  RULES ITERATE(100500) UNTIL(iteration_number > LENGTH(info[0]))(
    res[0] = CASE WHEN (SUBSTR(info[0],iteration_number+1,1) = ' ' AND SUBSTR(info[0],iteration_number+2,1) = ' ') THEN res[0]
                  ELSE res[0] || SUBSTR(info[0],iteration_number+1,1)
             END)
ORDER BY info

Условие

Из диапазона натуральных чисел от 1 до 3999 выбрать такие числа, которые, при представлении (строкой) в римском формате, содержат по 5 уникальных символов, при условии, что 3 из этих символов содержатся (в каждой строке) не менее чем по 3 раза. В результат включить сумму всех выбранных чисел, предварённую строкой 'SUM:'

Римская нотация должна содержать заглавные латинские буквы.

Результат должен содержать:

• Столбец 1: Римское представление числа / строку 'SUM:',
• Столбец 2: Десятичное представление числа / сумму всех выбранных чисел
• Отсортировать результат по возрастанию значений во втором столбце.

R                        V
--------------- ----------
CCCLXXXVIII            388
DCCCXXXVIII            838
DCCCLXXXIII            883
MCCCXXXVIII           1338
. . . . . .
MMCMXXXVIII           2938
. . . . . . 
MMMCMLXXXIII          3983
SUM:                161640

• содержит 5 уникальных символов M, C, X, V, I
• три из этих символов - M, X, I - содержатся в данной строке не менее, чем по 3 раза.

• Решение через затирание всех символов, кроме искомых и последующего их подсчета

SELECT NVL(x.rn, 'SUM:') R , SUM(n) V
FROM (
  SELECT TRIM(TO_CHAR(LEVEL, 'RN')) as RN, LEVEL N
  FROM dual
  CONNECT BY LEVEL < 4000
) x
WHERE
  NVL(LENGTH(SUBSTR(regexp_replace(x.rn, '[^I]', ''),0,1)),0)+
  NVL(LENGTH(SUBSTR(regexp_replace(x.rn, '[^V]', ''),0,1)),0)+
  NVL(LENGTH(SUBSTR(regexp_replace(x.rn, '[^X]', ''),0,1)),0)+
  NVL(LENGTH(SUBSTR(regexp_replace(x.rn, '[^L]', ''),0,1)),0)+
  NVL(LENGTH(SUBSTR(regexp_replace(x.rn, '[^C]', ''),0,1)),0)+
  NVL(LENGTH(SUBSTR(regexp_replace(x.rn, '[^D]', ''),0,1)),0)+
  NVL(LENGTH(SUBSTR(regexp_replace(x.rn, '[^M]', ''),0,1)),0) = 5
AND
  DECODE(LENGTH(SUBSTR(regexp_replace(x.rn, '[^I]', ''),0,3)),3,1,0)+
  DECODE(LENGTH(SUBSTR(regexp_replace(x.rn, '[^V]', ''),0,3)),3,1,0)+
  DECODE(LENGTH(SUBSTR(regexp_replace(x.rn, '[^X]', ''),0,3)),3,1,0)+
  DECODE(LENGTH(SUBSTR(regexp_replace(x.rn, '[^L]', ''),0,3)),3,1,0)+
  DECODE(LENGTH(SUBSTR(regexp_replace(x.rn, '[^D]', ''),0,3)),3,1,0)+
  DECODE(LENGTH(SUBSTR(regexp_replace(x.rn, '[^M]', ''),0,3)),3,1,0)+
  DECODE(LENGTH(SUBSTR(regexp_replace(x.rn, '[^C]', ''),0,3)),3,1,0) = 3
GROUP BY ROLLUP(x.rn)
ORDER BY 2;

Условие

with t as
( select case when level = 4 then 'C' when level in (3, 8) then 'B' else 'A' end as x, level as y
    from duaL connect by level <= 9
)

X Y
- -
A 1
A 2
B 3
C 4
A 5
A 6
A 7
B 8
A 9

X Y
- ------------------------------
A 1-2,5-7,9
B 3,8
C 4

P.S. По раздельности задачки избитые, а вместе показались мне интересным полигоном для упражнений в минимизации решения. Как обычно мои критерии минимальности: вложенность/подзапросность/joinутость и версия :) Количество буковок и производительность тоже не забываем.

Предлагаемые решения

• Решение при помощи иерархического запроса и функции LISTAGG

WITH t AS (
  SELECT CASE WHEN LEVEL = 4 THEN 'C' WHEN LEVEL IN (3, 8) THEN 'B' ELSE 'A' END AS x, LEVEL AS y
  FROM dual CONNECT BY LEVEL <= 9
), t2 AS(
  SELECT x, MIN(CONNECT_BY_ROOT y) s_p, y e_p
  FROM t
  WHERE CONNECT_BY_ISLEAF = 1
  CONNECT BY PRIOR y + 1 = y AND PRIOR x = x
  GROUP BY x,y
)
SELECT DISTINCT x, SUBSTR(LISTAGG(CASE WHEN s_p = e_p THEN ',' || s_p ELSE ',' || s_p || '-' || e_p END) 
  WITHIN GROUP (ORDER BY s_p) OVER (PARTITION BY x),2) y
FROM t2
ORDER BY x

Условие

Используются таблицы стандартной демо-схемы HR. Одной командой SELECT собрать информацию по сотрудникам, отделам, местам расположения, странам и регионам в единую таблицу. Сгруппировать сведения:

1. По названию региона,
2. По первым трем цифрам номера телефона сотрудника.

1. Название региона,
2. Первые три цифры телефонного номера сотрудников,
3. Количество сотрудников в данной группе,
4. Средний оклад сотрудников группы (округлить до двух знаков после запятой),
5. Количество мест расположения, где работают сотрудники данной группы,
6. Количество подразделений, к которым приписаны сотрудники данной группы,
7. Список названий городов, в которых работают сотрудники данной группы.

1. Упорядочены по возрастанию
2. Разделены символами ', ' ("запятая" и "пробел")
3. Перед первым названием города не должно быть символов-разделителей
4. После последнего названия города символов-разделителей быть не должно.

1. По названию региона (по возрастанию),
2. По первым трем цифрам номера телефона сотрудника (по возрастанию).

• 1

WITH t1 AS (
  SELECT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  e.employee_id, e.salary, l.location_id, d.department_id, l.city,
  ROW_NUMBER() OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3) ORDER BY r.region_name) in_gr_num,
  DENSE_RANK() OVER (ORDER BY r.region_name, SUBSTR(e.phone_number, 1,3)) gr_num,
  DENSE_RANK() OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3) ORDER BY l.city) city_num 
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id)
--
  SELECT DISTINCT r_name, ph_num,
  (SELECT MAX(in_gr_num) FROM t1 WHERE gr_num = t.gr_num) c1,
  (SELECT round(AVG(salary), 2) FROM t1 WHERE gr_num = t.gr_num) c2,
  (SELECT count(DISTINCT location_id) FROM t1 WHERE gr_num = t.gr_num) c3, 
  (SELECT count(DISTINCT department_id) FROM t1 WHERE gr_num = t.gr_num) c4,
  LISTAGG(city, ', ') WITHIN GROUP (ORDER BY city) OVER (PARTITION BY gr_num) c5
  FROM t1 t

• 2

SELECT r_name, ph_num, ce,ss cl,cd, LISTAGG(city,', ') WITHIN GROUP (ORDER BY city) OVER (PARTITION BY r_name,ph_num) c5 FROM (
  
  
  SELECT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  COUNT(e.employee_id) ce, SUM(e.salary) ss, COUNT(l.location_id) cl, COUNT(d.department_id) cd, 
  LISTAGG(l.city,', ') WITHIN GROUP (ORDER BY l.city) OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3))
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id
  GROUP BY (r.region_name, SUBSTR(e.phone_number, 1,3)), l.city
)

• 3

WITH t2 AS (
  SELECT DISTINCT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  LISTAGG(l.city, ', ') WITHIN GROUP (ORDER BY l.city) OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3)) city
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id
  GROUP BY r.region_name, SUBSTR(e.phone_number, 1,3), l.city),
t1 AS ( SELECT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  COUNT(e.employee_id) ce, SUM(e.salary) ss, COUNT(l.location_id) cl, COUNT(d.department_id) cd 
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id
  GROUP BY r.region_name, SUBSTR(e.phone_number, 1,3))  
--
SELECT tt1.r_name, tt1.ph_num, tt1.ce, tt1.ss, tt1.cl, tt1.cd, tt2.city
FROM t1 tt1, t2 tt2
WHERE tt1.r_name = tt2.r_name AND tt1.ph_num = tt2.ph_num

• 4

WITH t1 AS (
  SELECT r.region_name r_name, SUBSTR(e.phone_number, 1,3) ph_num,
  e.employee_id, e.salary, l.location_id, d.department_id, l.city,
  ROW_NUMBER() OVER (PARTITION BY r.region_name, SUBSTR(e.phone_number, 1,3) ORDER BY r.region_name) in_gr_num,
  DENSE_RANK() OVER (ORDER BY r.region_name, SUBSTR(e.phone_number, 1,3)) gr_num
  FROM regions r, countries c, locations l, departments d, employees e
  WHERE r.region_id = c.region_id
  AND c.country_id = l.country_id
  AND l.location_id = d.location_id
  AND d.department_id = e.department_id),  
t3 AS (SELECT DISTINCT gr_num, city FROM t1),
t4 AS (SELECT DISTINCT gr_num, city, ROW_NUMBER() OVER(PARTITION BY gr_num ORDER BY city) in_gr_num  FROM t3), 
t2 AS (
  SELECT DISTINCT r_name, ph_num,
  (SELECT MAX(in_gr_num) FROM t1 WHERE gr_num = t.gr_num) c1,
  (SELECT round(AVG(salary) ,2) FROM t1 WHERE gr_num = t.gr_num) c2,
  (SELECT count(DISTINCT location_id) FROM t1 WHERE gr_num = t.gr_num) c3, 
  (SELECT count(DISTINCT department_id) FROM t1 WHERE gr_num = t.gr_num) c4
  ,(SELECT SUBSTR(SYS_CONNECT_BY_PATH(city,', '), 3)
        FROM t4 WHERE CONNECT_BY_ISLEAF = 1 AND gr_num = t.gr_num AND ROWNUM = 1
        CONNECT BY  in_gr_num = PRIOR in_gr_num+1
        AND gr_num = PRIOR gr_num
        START WITH in_gr_num = 1) c5
  FROM t1 t)
--
SELECT * FROM t2
ORDER BY r_name, ph_num

Условие

Используются таблицы стандартных демо-схем HR и OE. Используя таблицы REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS, WAREHOUSES, вывести иерархию объектов "Регион – Страна – Город (населенный пункт) – Подразделение или Товарный склад" для региона region_id=2 (region_name = 'Americas '). Иерархия должна быть выведена одной командой SELECT. Внимание! Запрещается использование фраз CONNECT BY, START WITH.

В результат вывести:

1. Номер уровня, на котором находится в иерархии данный объект (LEVEL),
2. Имя объекта, дополненное слева (LEVEL -1)*3 пробелами.

• Названия подразделений компании дополните справа строкой '-d'.
• Названия товарных складов дополните справа строкой '-w'.
• Названия регионов, стран и городов ничем дополнять не нужно.
• Объекты одного уровня, подчиненные одному и тому же узлу, должны быть отсортированы по именам.

Уров. Иерархия
----- -------------------------
1 	Americas
2 		Argentina
2 		Brazil
3 			Sao Paulo
2 		Canada
3 			Toronto
4 			Marketing-d
4 			Toronto-w
3 			Whitehorse
2 		Mexico
. . . . .

Предлагаемые решения

• Решение с использованием одностороннего соединения таблиц

WITH t1 AS (
SELECT r.region_name rn, c.country_name cn, l.city ln, nvl2(w.warehouse_name, w.warehouse_name|| '-w', NULL)  dwn
FROM hr.regions r, hr.countries c, hr.locations l, oe.warehouses w
WHERE r.region_id = c.region_id (+)
AND c.country_id = l.country_id (+)
AND l.location_id = w.location_id (+)
AND r.region_name = 'Americas'
UNION ALL
SELECT r.region_name, c.country_name, l.city, nvl2(d.department_name, d.department_name || '-d', NULL)
FROM hr.regions r, hr.countries c, hr.locations l, hr.departments d
WHERE r.region_id = c.region_id (+)
AND c.country_id = l.country_id (+)
AND l.location_id = d.location_id (+)
AND r.region_name = 'Americas'
),
t2 AS (
SELECT DISTINCT rn c1, NULL c2, NULL c3, NULL c4 FROM t1
UNION ALL
SELECT DISTINCT rn, nvl2(cn,'___'||cn, NULL), NULL, NULL FROM t1
UNION ALL
SELECT DISTINCT rn, nvl2(cn,'___'||cn, NULL), nvl2(ln, '______'||ln, NULL), NULL FROM t1
UNION ALL
SELECT DISTINCT rn, nvl2(cn,'___'||cn, NULL), nvl2(ln,'______'||ln,NULL), nvl2(dwn,'_________'||dwn, NULL) FROM t1
),
t3 AS ( SELECT DISTINCT * FROM t2)
--SELECT * FROM t2
SELECT NVL(c4, NVL(c3, NVL(c2, c1))) hier
FROM t3
ORDER BY c1,c2 NULLS FIRST,c3 NULLS FIRST,c4 NULLS FIRST

• Решение со сваливанием всех сущностей в кучу

WITH obj AS(
  SELECT 'R_' || region_id id, 1 lev, region_name name, NULL pid FROM hr.regions WHERE region_name = 'Americas'
  UNION ALL
  SELECT 'C_' || country_id id, 2 lev, country_name name, 'R_' || region_id pid FROM hr.countries
  UNION ALL
  SELECT 'L_' || location_id id, 3 lev, city name, 'C_' || country_id pid FROM hr.locations
  UNION ALL
  SELECT 'W_' || warehouse_id id, 4 lev, warehouse_name || '-w' name, 'L_' || location_id pid  FROM oe.warehouses
  UNION ALL
  SELECT 'D_' || department_id id, 4 lev, department_name || '-d' name, 'L_' || location_id pid  FROM hr.departments
), hie AS(
SELECT l1.name l1_name, NULL l2_name,  NULL l3_name,  NULL l4_name 
FROM obj l1
WHERE l1.lev = 1
UNION ALL
SELECT l1.name l1_name,  l2.name l2_name,  NULL l3_name,  NULL l4_name
FROM obj l1, obj l2
WHERE l1.id = l2.pid AND l1.lev = 1
UNION ALL
SELECT l1.name l1_name,  l2.name l2_name,  l3.name l3_name,  NULL l4_name
FROM obj l1, obj l2, obj l3
WHERE l1.id = l2.pid AND l2.id = l3.pid AND l1.lev = 1
UNION ALL
SELECT l1.name l1_name,  l2.name l2_name,  l3.name l3_name,  l4.name l4_name
FROM obj l1, obj l2, obj l3, obj l4
WHERE l1.id = l2.pid AND l2.id = l3.pid AND l3.id = l4.pid AND l1.lev = 1
)
SELECT NVL2(l4_name, '         ' || l4_name, 
       NVL2(l3_name, '      ' || l3_name, 
       NVL2(l2_name,'   ' || l2_name, l1_name))) name 
       FROM hie
ORDER BY l1_name NULLS FIRST,l2_name NULLS FIRST,l3_name NULLS FIRST,l4_name NULLS FIRST;

Условие

Используются таблицы стандартной демо-схемы OE.

Одной командой SELECT вывести список покупателей, в информации о которых хранятся два или более телефонных номера.

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

1. Идентификатор покупателя
2. Имя покупателя
3. Фамилию покупателя
4. Количество хранящихся телефонных номеров покупателя
5. Список хранящихся телефонных номеров покупателя. Телефонные номера в списке должны быть:
   1. Упорядочены по алфавиту, как строковые данные (по возрастанию)
   2. Разделены символами ', ' ("запятая" и "пробел")
   3. Перед первым номером телефона не должно быть символов-разделителей
   4. После последнего номера телефона символов-разделителей быть не

• Решение при помощи аналитических функций и функции LISTAGG

WITH cust AS (
  SELECT c.customer_id, c.cust_first_name, c.cust_last_name, COUNT(1) OVER (PARTITION BY c.customer_id) num_cnt, t.column_value tel 
  FROM oe.customers c, 
  TABLE(c.phone_numbers) t
)
SELECT DISTINCT customer_id, cust_first_name, cust_last_name, num_cnt, 
  LISTAGG(tel,', ') WITHIN GROUP (ORDER BY tel) OVER (PARTITION BY customer_id) tel_list
FROM cust WHERE num_cnt >= 2
ORDER BY customer_id ASC

Условие

Используются таблицы стандартной демо-схемы OE.

Проанализировать продажи товаров различного статуса в разрезе семейного положения покупателей. Одной командой SELECT вывести результат в виде таблицы, содержащей пять столбцов:

• Столбец 1: Статус товара,
• Столбец 2: Сумма продаж товаров с данным статусом женатым мужчинам,
• Столбец 3: Сумма продаж товаров с данным статусом неженатым мужчинам,
• Столбец 4: Сумма продаж товаров с данным статусом замужним женщинам,
• Столбец 5: Сумма продаж товаров с данным статусом незамужним женщинам.

• Статус товара определяется по значениям в поле PRODUCT_STATUS таблицы PRODUCT_INFORMATION.
• Семейное положение покупателей определить по полям GENDER и MARITAL_STATUS таблицы CUSTOMERS.
• Результат отсортировать по статусу продукта по возрастанию.

PRODUCT_STATUS женат не женат замужем не замужем
----------------- -------- ---------- -------- ----------
obsolete 12345,6 789101,2 345 6789,1
orderable 7890,1 23456,7 89102,3 45678,9
planned 234567,8 7654
under development 9012 34567,8 9012 3456,7

Предлагаемые решения

• Решение с использованием вложенных селектов

WITH t1 AS (SELECT c.gender g, c.marital_status ms, o.unit_price * o.quantity pr, p.product_status
FROM customers c, orders oo, order_items o, product_information p
WHERE c.customer_id = oo.customer_id
AND o.order_id = oo.order_id
AND o.product_id = p.product_id),
t2 AS (SELECT DISTINCT product_status FROM t1)
--
SELECT product_status,
nvl((SELECT SUM(pr) FROM t1 WHERE g = 'M' AND ms = 'married' AND product_status = tt2.product_status),0) MM,
nvl((SELECT SUM(pr) FROM t1 WHERE g = 'M' AND ms = 'single' AND product_status = tt2.product_status),0) MS,
nvl((SELECT SUM(pr) FROM t1 WHERE g = 'F' AND ms = 'married' AND product_status = tt2.product_status),0) FM,
nvl((SELECT SUM(pr) FROM t1 WHERE g = 'F' AND ms = 'single' AND product_status = tt2.product_status),0) FS
FROM t2 tt2
ORDER BY product_status

• Решение с использованием оператора CASE

WITH t AS (
  SELECT c.customer_id, c.marital_status,c.gender, pi.product_status, oi.unit_price*oi.quantity price
  FROM oe.customers c, oe.orders o, oe.order_items oi, oe.product_information pi
  WHERE c.customer_id = o.customer_id
    AND o.order_id = oi.order_id
    AND oi.product_id (+) = pi.product_id
)
SELECT t.product_status, 
  SUM(CASE WHEN t.gender = 'M' AND t.marital_status = 'married' THEN t.price ELSE 0 END) "женат",
  SUM(CASE WHEN t.gender = 'M' AND t.marital_status = 'single' THEN t.price ELSE 0 END) "не женат",
  SUM(CASE WHEN t.gender = 'F' AND t.marital_status = 'married' THEN t.price ELSE 0 END) "замужем",
  SUM(CASE WHEN t.gender = 'F' AND t.marital_status = 'single' THEN t.price ELSE 0 END) "не замужем"
FROM t
GROUP BY t.product_status
ORDER BY t.product_status;

• Решение с использованием конструкции PIVOT

SELECT * FROM 
(
SELECT pi.product_status,cust.gender,cust.marital_status, SUM(oi.unit_price*oi.quantity) sm
FROM oe.product_information pi, oe.order_items oi, oe.orders o, oe.customers cust
WHERE pi.product_id = oi.product_id
AND oi.order_id = o.order_id
AND cust.customer_id = o.customer_id
GROUP BY pi.product_status, cust.gender, cust.marital_status
)
PIVOT (
SUM(sm) FOR (gender, marital_status) IN (('M', 'married'), ('M', 'single'), ('F', 'married'), ('F', 'single'))
)
ORDER BY product_status;

Условие

Одной командой SELECT посчитать количество сотрудников в каждом подразделении, которые никем не руководят. В результат вывести:

• столбец 1: идентификатор подразделения, к которому приписан сотрудник,
• столбец 2: количество сотрудников подразделения, которые никем не руководят

• столбец 1: слово "ВСЕГО",
• столбец 2: общее количество сотрудников компании, которые никем не руководят.

• идентификатор подразделения, к которому приписан сотрудник.

DEPT CNT
------ ---
10 3
20 5
30 1
40 2
... ...
110 4
ВСЕГО 36

Было дано устное разъяснение, что в выборку не следует включать сотрудников, которые не приписаны ни к какому подразделению (employees.department_id IS NOT NULL). Это упростило задачу, избавив от необходимости использования функции GROUPING.

Предлагаемые решения

• Решение с использованием аггрегирующей функции COUNT и функции ROLLUP

WITH t AS (
  SELECT d.department_id dep, COUNT(1) cnt 
  FROM hr.employees e, hr.departments d
  WHERE e.department_id = d.department_id
  GROUP BY ROLLUP(d.department_id)
)
SELECT NVL(TO_CHAR(dep),'ВСЕГО:') depm, cnt
FROM t
ORDER BY dep NULLS LAST

Условие

Используются таблицы стандартной демо-схемы OE.

Вывести информацию обо всех товарах (всего 32 столбца):

1. Идентификатор товара,

2. Цена товара по каталогу,

3. Название товара на английском языке (US),

4. Название товара на русском языке (RU),

5-32. Название товара на всех остальных языках.

Порядок столбцов определяется значениями идентификаторов национальных языков – они должны быть упорядочены по возрастанию:

'AR', 'CA', 'CS','D','DK', 'E', 'EL','ESA', 'F', 'FRC', 'HU', 'I', 'IW', 'JA', 'KO', 'N', 'NL', 'PL', 'PT', 'PTB', 'RO', 'S', 'SF','SK', 'TH', 'TR', 'ZHS', 'ZHT'.

Результат должен быть отсортирован по идентификатору товара по возрастанию.

Предлагаемые решения

• Решение с использованием конструкции PIVOT

SELECT * FROM
(SELECT pi.product_id pi, pi.min_price mp, pd.language_id li, pd.translated_name tn
FROM oe.product_information pi, oe.product_descriptions pd
WHERE pd.product_id = pi.product_id)
PIVOT
(
MIN(tn) FOR li IN ('US', 'RU', 'AR', 'CA', 'CS','D','DK', 'E', 'EL','ESA', 'F', 'FRC', 'HU',
'I', 'IW', 'JA', 'KO', 'N', 'NL', 'PL', 'PT', 'PTB', 'RO',
'S', 'SF','SK', 'TH', 'TR', 'ZHS', 'ZHT')
)

Условие

Анаграмма (от греч. «снова» и «запись») — литературный приём, состоящий в перестановке букв или звуков определённого слова (или словосочетания), что в результате даёт другое слово или словосочетание. В ряде случаев анаграммами принято также называть иные в функциональном отношении (то есть не являющиеся литературным приёмом) перемешивания буквенного или звукового состава слов.

В данном задании:

- "Анаграммой" будем называть именно "перемешивания буквенного или звукового состава слов". То есть анаграмма исходного слова не должна быть каким-то существующим словом, она должна быть простой комбинацией букв исходного слова.

- В анаграмме должны быть использованы все буквы исходного слова.

- Анаграмма должна иметь такую же длину, как исходное слово.

- В анаграмме каждая буква должна встречаться ровно столько же раз, сколько раз она встречается в исходном слове.

- Буквы разных регистров считаются эквивалентными ('а' эквивалентно 'А', 'с' эквивалентно 'С' и т.д.).

- Слово считается анаграммой самого себя.

Примеры анаграмм трехбуквенных слов:

   - ЖУК  => ЖКУ, ЖУК, КЖУ, КУЖ, УЖК, УКЖ
   - МИР => ИМР, ИРМ, МИР, МРИ, РИМ, РМИ
   - ACC => АСС, САС, ССА
   - МММ => МММ

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

Исходное слово рекомендуется задать с помощью фразы WITH:

WITH w AS (SELECT 'АГАТ' AS word FROM dual)
. . .

При записи ответа используйте слово 'АГАТ'

Предлагаемые решения

• Решение с использованием декартова произведения и фильтрации результата

WITH w AS (
  SELECT 'АГАТ' AS word FROM dual
), sym AS (
  SELECT 1 cod, SUBSTR(word,1,1) s FROM w
  UNION ALL
  SELECT 10 cod, SUBSTR(word,2,1) s FROM w
  UNION ALL
  SELECT 100 cod, SUBSTR(word,3,1) s FROM w
  UNION ALL
  SELECT 1000 cod, SUBSTR(word,4,1) s FROM w
)
SELECT DISTINCT s1.s || s2.s || s3.s || s4.s word
FROM sym s1, sym s2, sym s3, sym s4
WHERE s1.cod + s2.cod + s3.cod + s4.cod = 1111
ORDER BY word

• Решение с использованием соединения таблиц

WITH w AS (
  SELECT 'АГАТ' AS word FROM dual
)
, ww AS (
  SELECT  w.word, SUBSTR(w.word, LEVEL, 1) AS st, LEVEL AS lv
  FROM w
  CONNECT BY LEVEL < LENGTH(w.word) + 1
)
SELECT DISTINCT ww1.st || ww2.st || ww3.st || ww4.st word   
FROM ww ww1, ww ww2, ww ww3, ww ww4, w
WHERE ww1.lv <> ww2.lv
AND ww1.lv <> ww3.lv
AND ww1.lv <> ww4.lv 
AND ww2.lv <> ww3.lv
AND ww2.lv <> ww4.lv
AND ww3.lv <> ww4.lv

Условие

Используются таблицы стандартной демо-схемы HR.

Месячный доход сотрудника складывается из оклада, установленного сотруднику, и установленного для сотрудника размера комиссионных (в процентах от оклада; например, значению 0,2 соответствует сумма комиссионных, равная 20% от оклада).

Если для сотрудника не указан размер комиссионных, то следует считать, что комиссионные данному сотруднику не выплачиваются.

Одной командой SELECT вывести сведения о месячном доходе сотрудников по странам.

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

   1. Название страны, в которой расположено подразделение компании, к которому приписан сотрудник
   2. Сумма месячных доходов сотрудников, приписанных к подразделениям компании, расположенных в данной стране. 

Результат упорядочить по названиям стран по возрастанию.

В предпоследней строке вывести сумму месячных доходов сотрудников, для которых невозможно определить страну. Вместо названия страны в этой строке вывести строку 'N/A' (в верхнем регистре).

Последней строкой вывести общую сумму месячных доходов всех сотрудников. Вместо названия страны в этой строке вывести строку 'TOTAL' (в верхнем регистре).

Все суммы следует округлить до целых значений (ноль знаком после запятой).

Пример результата:

COUNTRY_NAME       MONTH_INCOME
-----------------  ------------
Argentina                 12345
Jamaica                   67890
Russian Federation        93456
United Kingdom           987654
N/A                        8050
TOTAL                   1169395

Предлагаемые решения

• Решение с использованием одностороннего соединения таблиц и функции ROLLUP

SELECT DECODE (GROUPING_ID(c.country_name),0,NVL(c.country_name,'N/A'),'TOTAL') country_name, 
  ROUND(SUM(e.salary + NVL(e.commission_pct,0)*salary),0) sum_salary
FROM hr.employees e, hr.departments d, hr.locations l, hr.countries c
WHERE e.department_id = d.department_id (+) 
  AND d.location_id = l.location_id (+)
  AND l.country_id = c.country_id (+)
GROUP BY ROLLUP(c.country_name)
ORDER BY c.country_name NULLS LAST

Условие

Используются таблицы стандартной демо-схемы HR. Одной командой SELECT вывести два столбца:

1. Фамилии сотрудников-руководителей, выстроив их в соответствии с иерархической структурой предприятия, выделяя каждый следующий уровень подчинения сдвигом фамилии на два пробела вправо (идентификатор непосредственного руководителя сотрудника указан в поле MANAGER_ID таблицы EMPLOYEES).
2. Сумму окладов всех сотрудников, подчиненных данному сотруднику (оклад самого сотрудника при суммировании не учитывать).

Пример результата (данные условные):

TREE           SALARY
-----------    -------
Kingman	    987654
  Carbofoss      64321
  De De Te       23456
    Hunourik     12345
  Errorlevin     43210
  . . .
  Kochegar       99999
    Groomwall    34567
    Vickings      8765
  Marquez        23456
  . . .

Предлагаемые решения

• Решение с использованием иерархического запроса и конструкции EXISTS

SELECT LPAD(' ', LEVEL*2)||e1.last_name TREE, 
       (SELECT sum(e2.salary)
       FROM hr.employees e2
       START WITH e2.manager_id = e1.employee_id
       CONNECT BY e2.manager_id =  prior e2.employee_id
       ) sal
FROM hr.employees e1
WHERE EXISTS (SELECT 1 FROM hr.employees e2 WHERE e2.manager_id = e1.employee_id)
CONNECT BY e1.manager_id =  prior e1.employee_id
START WITH e1.manager_id IS NULL
ORDER SIBLINGS BY e1.last_name      

• Решение с использованием иерархического запроса и псевдостолбца CONNECT_BY_ISLEAF

SELECT LPAD(' ',(LEVEL-1)*2) || last_name last_name, 
  (SELECT SUM(e2.salary) 
  FROM hr.employees e2 
  START WITH e2.employee_id = e1.employee_id 
  CONNECT BY PRIOR e2.employee_id = e2.manager_id
  ) - e1.salary sum_salary
FROM hr.employees e1
WHERE CONNECT_BY_ISLEAF = 0
START WITH e1.manager_id IS NULL
CONNECT BY PRIOR e1.employee_id = e1.manager_id
ORDER SIBLINGS BY e1.last_name ASC

Условие

Для записи чисел римскими цифрами (roman numerals) используются буквы латинского алфавита.

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

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

1. Буква латинского алфавита, используемая для записи римскими цифрами чисел от 1 до 3999.
2. Количество раз использования этой буквы для записи римскими цифрами чисел от 1 до 3999.

Предлагаемые решения

• Решение с использованием рекурсивного запроса и аггрегирующей функции

WITH t(ch,sym,l) AS (
  SELECT TO_CHAR(LEVEL,'fmRN') ch, SUBSTR(TO_CHAR(LEVEL,'fmRN'),1,1) sym, 1 l FROM dual CONNECT BY LEVEL < 4000
  UNION ALL
  SELECT ch, SUBSTR(ch,l+1,1) sym, l+1 l FROM t WHERE l < LENGTH(ch)
)
SELECT sym, COUNT(1) kol 
FROM t GROUP BY sym

Условие

Предлагаемые решения

• Решение через введение псевдосотрудников с именами тега

SELECT CASE ord WHEN 1 THEN LPAD(' ', LEVEL*2)||name ELSE LPAD(' ', (LEVEL-1)*2) || name END  html
  FROM (SELECT employee_id, manager_id, '<li>'||last_name || ', '||first_name||'</li>' name, 1 ord FROM hr.employees
UNION ALL SELECT DISTINCT NULL, manager_id, '<ui>', 0 ord FROM hr.employees
UNION ALL SELECT DISTINCT NULL, manager_id, '</ui>', 2 ord FROM hr.employees)
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY ord, name

• Аналогичное решение с использованием виртуальной таблицы

WITH t AS (
  SELECT employee_id, first_name || ' ' || last_name name, 0 flag, manager_id FROM employees
  UNION ALL
  SELECT DISTINCT NULL employee_id, '<ui>' name,-1 flag, manager_id FROM employees
  UNION ALL
  SELECT DISTINCT NULL employee_id, '</ui>' name,1 flag, manager_id FROM employees
) 
SELECT DECODE(flag,0,LPAD(' ',2*LEVEL) || name, LPAD(' ',2*(LEVEL-1)) || name) FROM t 
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY flag

Условие

Используются таблицы стандартной демо-схемы HR.

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

В результате вывести следующие данные:

1. Название подразделения компании
2. Имя сотрудника
3. Фамилия сотрудника
4. На сколько суток раньше руководителя данный сотрудник был принят на работу

• Решение через соединение таблиц

SELECT d.department_name, e1.first_name, e1.last_name, TO_NUMBER(e2.hire_date - e1.hire_date) days
FROM employees e1, departments d, employees e2
WHERE e1.department_id = d.department_id
  AND d.manager_id = e2.employee_id
  AND TO_NUMBER(e2.hire_date - e1.hire_date) > 0

Условие

Используются таблицы стандартной демо-схемы HR.

Одной командой SELECT вывести наиболее часто встречающтеся имена сотрудников.

Если несколько имен встречаются чаще всего, то вывести все эти имена.

Предлагаемые решения

• Решение с использованием виртуальных таблиц и аналитических функций

WITH t AS (
  SELECT DISTINCT e.first_name, COUNT(1) OVER (PARTITION BY e.first_name) cnt
  FROM employees e
), t2 AS (
  SELECT first_name, cnt, MAX(cnt) OVER () max_cnt
  FROM t 
)
SELECT first_name 
FROM t2 
WHERE cnt = max_cnt

Условие

Используются таблицы стандартной демо-схемы HR.

Вывести в виде таблицы доли зарплат сотрудников в общем фонде зарплаты в виде строк по каждому из сотрудников, предваряемых строкой по отделу в целом.

1. Название отдела
2. Фамилия сотрудника или NULL если строка относится к отделу в целом
3. Доля зарплаты сотрудника или отдела в процентах с точностью 2 знака после запятой

Таблица должна быть упорядочена по названию отдела, фамилии сотрудника

Предлагаемые решения

• Решение с использованием виртуальных таблиц и аналитических функций

WITH dep AS(
  SELECT d.department_id, d.department_name, ROUND(SUM(e.salary)*100/SUM(SUM(e.salary)) OVER(),2) pct,
    LEAD(d.department_id) OVER (ORDER BY d.department_name) next_dep
  FROM employees e, departments d
  WHERE e.department_id = d.department_id
  GROUP BY (d.department_id, d.department_name)
), dep_fix AS(
  SELECT department_id, department_name, NVL2(next_dep, pct, 
    100 - SUM(pct) OVER (ORDER BY department_name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) pct
  FROM dep
), emp AS(
  SELECT d.department_name, e.last_name, e.salary, d.pct dep_pct,
    ROUND(e.salary*100/SUM(e.salary) OVER (),2) pct,
    LEAD(e.last_name) OVER (PARTITION BY d.department_name ORDER BY e.last_name) next_dep_emp
  FROM employees e, dep_fix d
  WHERE e.department_id = d.department_id
), emp_fix AS(
  SELECT department_name, last_name, ROUND(NVL2(next_dep_emp,pct,dep_pct - NVL(SUM(pct) OVER (PARTITION BY department_name 
    ORDER BY last_name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0)),2) pct
  FROM emp
)
SELECT department_name, last_name,pct FROM emp_fix
UNION ALL
SELECT department_name, NULL last_name,pct FROM dep_fix
ORDER BY department_name, last_name NULLS FIRST

Условие

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

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

1. Имя ссылочного ограничения целостности (внешнего ключа)
2. Имя таблицы, которой принадлежит данное ссылочное ограничение целостности (внешний ключ)
3. Имя столбца таблицы, который входит во внешний ключ (foreign key)
4. Имя таблицы, на которую ссылается данный внешний ключ
5. Имя столбца таблицы, на которую ссылается данный внешний ключ, которому соответствует столбец, указанный в п.3

Описание статических представлений словаря данных дается в документе "Oracle® Database. Reference".

Для отладки решения рекомендуется создать дополнительную таблицу ORDER_ITEMS_LINE_INFO:

CREATE TABLE order_items_line_info(
order_id NUMBER(12)
, item_line_id NUMBER(3)
, info_line_id NUMBER(3)
, info_date DATE NOT NULL
, info_text VARCHAR2(100 CHAR) NOT NULL
, CONSTRAINT order_items_line_info_pk
PRIMARY KEY(order_id, item_line_id, info_line_id)
, CONSTRAINT order_items_line_info_fk
FOREIGN KEY(order_id, item_line_id)
REFERENCES order_items(order_id, line_item_id)
ON DELETE CASCADE);

Предлагаемые решения

• Решение через соединение таблиц

SELECT uc1.constraint_name, uc1.table_name, ucc1.column_name, uc2.TABLE_NAME, ucc2.column_name
FROM user_constraints uc1, user_constraints uc2, user_cons_columns ucc1, user_cons_columns ucc2
WHERE uc1.constraint_type = 'R'
  AND uc1.CONSTRAINT_NAME = ucc1.CONSTRAINT_NAME
  AND uc2.CONSTRAINT_NAME = uc1.R_CONSTRAINT_NAME
  AND uc2.CONSTRAINT_NAME = ucc2.CONSTRAINT_NAME
  AND ucc1.position = ucc2.position

Условие

Найти сотрудников, которые руководят одним подчиненным, который в свою очередь никем не руководит.

В результате вывести:

1. Идентификатор сотрудника
2. Имя сотрудника
3. Фамилия сотрудника

1. Идентификатор сотрудника
2. Имя сотрудника

• Решение через иерархический запрос

WITH t(a,b,c,d,e,f,g,h,i,j,k) AS(SELECT * FROM employees)
SELECT a, b, c FROM (
  SELECT CONNECT_BY_ROOT a a, CONNECT_BY_ROOT c b, CONNECT_BY_ROOT b c
  FROM t
  CONNECT BY PRIOR a = j
)
GROUP BY a, b, c
HAVING sum(1) = 2
ORDER BY 1,2,3

• Решение через вложенные подзапросы

SELECT employee_id, first_name, last_name FROM employees e
WHERE (SELECT sum(1) FROM employees f WHERE manager_id = e.employee_id
AND NOT EXISTS (SELECT 1 FROM employees g WHERE manager_id = f.employee_id)) = 1
ORDER BY 1, 2

• Оптимизировано по символам через виртуальную таблицу

WITH t(a,b,c,d,e,f,g,h,i,j,k) AS (SELECT * FROM hr.employees)
SELECT a, c, b FROM t o
WHERE (SELECT sum(1) FROM t p WHERE j = o.a
AND NOT EXISTS (SELECT 1 FROM t WHERE j = p.a)) = 1
ORDER BY 1, 2, 3

• Оптимизировано по символам

with t(i,f,l,e,p,h,j,s,z,m,d) as (select * from hr.employees)
select i,l,f from t where 1=(select sum((select min(2)||1 from t where m=i.i))from t i where i.m=t.i) 
order by 1,2,3

Условие

Часто случается так, что со временем для таблиц создаются индексы, без которых можно было бы обойтись - "лишние" индексы.

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

Одна из разновидностей "лишних" индексов - это такие индексы, которые являются подмножествами какого-либо одного или нескольких других индексов.

Индекс х является подмножеством индекса Z тогда и только тогда, когда

1. индексы X и Z - это индексы одной и той же таблицы.
2. все столбцы индекса X присутствуют в индексе Z.
3. позиции всех столбцов в индексе х совпадают с позициями одноименных столбцов в индексе 2 (то есть порядок столбцов в индексе х совпадает с порядком тех же столбцов в индексе Z).
4. порядок сортировки (ASC / DESC) всех столбцов в индексе х совпадает с порядком сортировки (ASC / DESC) одноименных столбцов в индексе 2.
5. индекс х не используются для поддержания каких-либо декларативных ограничений целостности типа primary key и/или unique.

1. имя таблицы
2. имя "лишнего" индекса
3. имя индекса, подмножеством которого является ‘лишний индекс

Результат отсортировать:

• по имени таблицы (по возрастанию)
• по имени "лишнего" индекса (по возрастанию)
• по имени индекса, подмножеством которого является лсшнии индекс (по возрастанию).

DROP TABLE olymp_index;
create table olymp_index
  (
  a NUMBER,
  b NUMBER,
  c NUMBER,
  d NUMBER,
  e NUMBER
  );

create index olymp_index#I#a on olymp_index(a);
create index olymp_index#I#a_b ON olymp_index (a, b);
create index olymp_index#I#a_b_c ON olymp_index (a, b, c);
create index olymp_index#I#a_b_d on olymp_index (a, b, d);
create index olymp_index#I#a_b_c_d on olymp_index (a, b, c, d);
create index olymp_index#I#b_a_c on olymp_index (b, a, c);
create index olymp_index#I#b_a_d on olymp_index (b, a, d);
create index olymp_index#I#b_a_d_e on olymp_index (b, a, d, e);
create index olymp_index#I#b_e on olymp_index (b, e);
create index olymp_index#I#a_e_d on olymp_index (a, e, d);
create index olymp_index#I#e_a on olymp_index (e, a);
create index olymp_index#I#a#desc ON olymp_index (a DESC);
create index olymp_index#I#a#desc_b on olymp_index (a DESC, b);
create index olymp_index#I#a#desc_b_c on olymp_index (a DESC, b, c);
create index olymp_index#I#a_b#desc_c on olymp_index (a, b DESC, c);

alter table olymp_index
add constraint olymp_index#U#a_e
unique (a, e) USING INDEX olymp_index#I#e_a;

Предлагаемые решения

• Решение через операции над множествами

with tt AS (SELECT index_name, table_name, column_name, column_position, descend
FROM user_ind_columns)
SELECT DISTINCT t1.table_name, t1.index_name "Лишний", t2.index_name "Важный" FROM tt t1, tt t2
WHERE t1.table_name = t2.table_name
AND t1.index_name <> t2.index_name
AND NOT EXISTS (
    SELECT column_name, column_position, descend,
      (SELECT 1
      FROM user_constraints uc
      WHERE uc.index_name = tt1.index_name
      )    
    FROM tt tt1 WHERE table_name = t1.table_name
    AND index_name = t1.index_name
MINUS
    SELECT column_name, column_position, descend, NULL
    FROM tt WHERE table_name = t2.table_name
    AND index_name = t2.index_name        
)

• Решение через соединение таблиц

WITH t AS(
SELECT table_name, index_name, column_name,column_position,descend,
  COUNT(1) OVER (PARTITION BY table_name, index_name) cnt
FROM user_ind_columns ic WHERE NOT EXISTS(SELECT 1 FROM user_constraints c WHERE ic.index_name = c.INDEX_NAME)
) 
SELECT t.table_name, t.index_name, ic.index_name gen_index FROM t, user_ind_columns ic 
WHERE t.table_name = ic.table_name
  AND t.index_name != ic.index_name 
  AND t.column_name = ic.column_name
  AND t.column_position = ic.column_position
  AND t.descend = ic.descend
GROUP BY t.table_name, t.index_name, ic.index_name
HAVING MAX(cnt)=COUNT(1)

Условие

Сгенерировать все даты текущего года

Предлагаемые решения

• Решение через генерацию нужных дат иерархическим запросом

SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1 dt FROM dual
CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'YYYY') + LEVEL - 1, 'YYYY') = TO_CHAR(TRUNC(SYSDATE, 'YYYY'), 'YYYY')

• Решение через генерацию дат иерархическим запросом и фильтрацией результата

WITH t AS (
 SELECT TO_DATE('01.01.2012','DD.MM.YYYY') dat FROM dual
)
SELECT dat + lev - 1 dat2 FROM t, 
 (SELECT LEVEL lev FROM dual CONNECT BY LEVEL < 400) t2
WHERE TO_CHAR(dat,'YYYY') = TO_CHAR(dat+lev -1,'YYYY')

Условие

Используются таблицы стандартной демо-схемы ОЕ.

В таблице locations есть столбец street_address (название улицы, номер дома и другие сведения об адресе местоположения).

Необходимо найти позиции всех пробелов во всех адресах.

Позиции считаются с начала строки, номер первой позиции = 1.

Если в данной строке таблицы поле street_address содержит N пробелов, то эта строка выводится в результат N раз - по одному разу для каждого пробела, одной командой SELECT вывести 3 (три) столбца:

1. идентификатор места расположения (местоположения) подразделения компании (location_id)
2. название улицы, номер дома и другие сведения об адресе местоположения (STREET^address
3. позиция пробела

1. по street_address (по возрастанию)
2. по location_id (по возрастанию)
3. по позиции пробела (по возрастанию)

• Решение через рекурсивный запрос

WITH t(lev,sym,street_address) AS(
  SELECT 1 lev, INSTR(street_address,' ') sym, street_address
  FROM hr.locations l
  WHERE INSTR(street_address,' ') != 0
  UNION ALL
  SELECT t.lev + 1 lev, INSTR(t.street_address,' ',1,t.lev + 1) sym, t.street_address
  FROM t
  WHERE INSTR(t.street_address,' ',1,t.lev + 1) != 0
  
)
SELECT * FROM t

Условие

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

TEAM_ID - идентификатор участника (первичный ключ),

TEAM_NAME - название участника (уникально),

SCORE - количество баллов, набранных участником.

В олимпиаде по СУБД oracle состязались N участников.

Первое место занял участник sking, набравший 240 баллов.

Следом за ним второе и третье место поделили между собой участники nkochhar и ldehaan, набравшие одинаковое количество баллов - 170.

Остальные участники расположились в турнирной таблице аналогичным образом в точном соответствии сбаллами. заняли по итогам соревнований.

Одной командой select выберите из таблицы OLYMP2012_TEAM_SCORE, информацию об участниках, набранных ими баллах и местах, которые в результат вывести три столбца:

1. название участника,
2. количество баллов, набранных командой,
3. места, которые заняли участники (здесь не должно быть ни одного пробела).

1. По количеству баллов, набранных участниками (по убыванию)
2. по имени участника (по возрастанио)

TEAM_NAME    SCORE   PLACE
KskTNG    240     1
IlOEHAAN    170    2-3
NKOCHHAR    170    2-3
DMACLEOD    165    4
IGILLAN    150    5
ADUBLING    130    6-8
Ibbpown    130    6-8
ISVAING    130    6-8
ILBIGGILZN  120    9

Для отладки решения этой задачи создайте представление данных (view):

create or replace view olymp2012_team_score
AS
select --Результаты олимпиады по СУБД Oracle
employee_id as team_id
, email as team_name
, ROUND(salary/100) as score
from employees
where salary is not null;

Предлагаемые решения

• Решение с использованием виртуальных таблиц

with t1 AS (
  SELECT score,
  row_number() over(order by score desc) place
  FROM olymp2012_team_score
),
t2 AS (
  SELECT score sc, MIN(place) p1, MAX(place) p2
  FROM t1
  GROUP BY score
)
select team_name, score, case when p2 = p1 then to_char(p1) else to_char(p1 || '-' ||p2) END pl
from t2, olymp2012_team_score
WHERE score = sc
ORDER BY score DESC

Условие

Используются телицы стандартной демо-схемы HR.

Одной командой select вывести отсортированный список сотрудников компании.

В результат вывести 8 (восемь) столбцов:

1. идентификатор сотрудника
2. Фамилию сотрудника
3. имя сотрудника
4. название подразделения компании, к которому приписан сотрудник
5. Дату найма сотрудника
6. е-mail сотрудника
7. оклад, установленный сотруднику
8. номер телефона

1 по названию подразделения компании (по возрастанию)

2 внутри каждого подразделения сотрудников вывести в порядке:

I) с окладом менее 4000, отсортированных по дате найма (.по возрастанию),

II) с окладом не менее 4000, но менее 8000, отсортированных по e-mail (по убыванию)

III) с окладом не менее 8000, но менее 12000, отсортированных по номеру телефона (по убыванию, NULL-значения в конце)

IV) с окладом не менее 12000 или с неизвестным окладом, отсортированных по величине оклада (по возрастанию, NULL-значения в конце)

3 по идентификатору сотрудника (по возрастанию)

Предлагаемые решения

• Решение с использованием оператора CASE

SELECT employee_id, last_name, first_name, department_name, hire_date, email, salary, phone_number
FROM t1
ORDER BY department_name, 
CASE
  WHEN salary < 4000 THEN sort1
  WHEN salary >= 4000 AND salary < 8000 THEN sort2
  WHEN salary >= 8000 AND salary < 12000 THEN sort3
  WHEN salary >= 12000 THEN sort4          
END

Условие

Предлагаемые решения

Условие

Предлагаемые решения

Условие

Предлагаемые решения

Условие

Предлагаемые решения

Условие

Предлагаемые решения

Условие

Предлагаемые решения

Условие

Предлагаемые решения

Условие

Предлагаемые решения