Задача: есть таблица с одним полем из целых положительных чисел. Нужно запросом вывести все числа столько раз, чему равно само число.

SELECT q.n
  FROM (    SELECT DISTINCT t.ROWID rn, 
                            t.n, 
                            LEVEL l
              FROM test_numbers t
           CONNECT BY LEVEL <= t.n) q
 ORDER BY q.rn

Интересный вариант с рекурсивным запросом предложил Дмитрий Грачев. Обратите внимание, что такой пример сработает только начиная с 11-ой версии Oracle и результат, вообще говоря, будет отличаться от того, что выдаст первый пример:

WITH f(a, b) 
  AS (SELECT n a, n b
        FROM test_numbers
       WHERE n > 0
       UNION ALL
      SELECT a - 1, b
        FROM f
       WHERE a > 1)
SELECT b
  FROM f
 ORDER BY b

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

Нужно SELECT-запросом во втором столбце пустоты заполнить нижеследующими непустыми значениями вот так:

SELECT col1,
       NVL(col2, 
           LEAD(col2) OVER (PARTITION BY col1 
                            ORDER BY ROWID)) col2
  FROM t1

Это, пожалуй, один из самых простых примеров, когда использование аналитических функций значительно облегчает разработку запроса. В Учебном Центре РДТЕХ читается двухдневный, очень интенсивный курс по аналитическим функциям в применении к хранилищам данных: Oracle Database 12c: Analytic SQL for Data Warehousing.

Сумму в S рублей нужно разбить монетами по 1, 2 или 5 рублей.

WITH t
     AS (    SELECT LEVEL n
               FROM DUAL
         CONNECT BY LEVEL <= :S)
SELECT t1.n || ' по 1 рублю, ' ||
       t2.n || ' по 2 рубля, ' ||
       t3.n || ' по 5 рублей'
  FROM t t1, t t2, t t3
 WHERE 1 * t1.n + 2 * t2.n + 5 * t3.n = :S
 ORDER BY t1.n DESC, t2.n DESC, t3.n DESC

Знаменитая задачка про поиск простых чисел средствами чистого SQL. Используется обобщенное табличное выражение — иерархический запрос, отбирающий все натуральные числа вплоть до максимального, определяемого параметром S, и полный перебор с дополнительным условием, что возможные делители проверяемого на простоту числа нужно искать среди чисел вплоть до квадратного корня из этого числа.

WITH t
     AS (    SELECT LEVEL n, LEVEL * LEVEL n2
               FROM DUAL
         CONNECT BY LEVEL <= :S)
SELECT t1.n
  FROM t t1
 WHERE NOT EXISTS
          (SELECT 1
             FROM t t2
            WHERE t1.n >= t2.n2 
              AND t2.n != 1 
              AND MOD(t1.n, t2.n) = 0)

P.S. Способ полностью рабочий, но не самый быстрый. Скорее, классический в плане знаний диалекта SQL от Oracle и обобщенных выражений.