|
Читайте также: |
FROM lcks
UNION
SELECT lcked
FROM lcks))
SELECT lpad(lcker, length(lcker) + level -1, ' ') rez
FROM
(SELECT distinct t1.lcker, t1.r id, t2.r
FROM t t1 LEFT JOIN lcks
ON t1.lcker = lcks.lcked
LEFT JOIN t t2
ON t2.lcker = lcks.lcker)
START WITH r IS NULL
CONNECT BY PRIOR id = r
ORDER SIBLINGS BY lcker;
REZ
-------
A
B
D
E
C
F
H
J
8 rows selected
Комментарий:
Нумеруем все объекты(и Lcker, и Lcked). В подзапросе 2 раза объединяем таблицу lcks с временным представлением t, чтобы создать подчиненность согласно блокировкам. Затем, с помощью конструкции level организуем иерархию. С помощью ORDER SIBLINGS BY сортируем данные.
17. Написать запрос к таблице EMPLOYEES, который вернёт зарплату сотрудника с заданным номером (EMPLOYEE_ID), а в случае отсутствия сотрудника с указанным номером - вернёт 0 (ноль).
UNDEFINE code;
SELECT CASE WHEN salary IS NULL THEN 0 ELSE salary END salary
FROM employees e RIGHT JOIN (SELECT '&&Code' AS cod FROM dual) d
ON e.employee_id=d.cod;
Результаты:
SALARY
----------------------
1 rows selected
SALARY
----------------------
1 rows selected
Комментарий:
Соединяем введенное число с таблицей employees, чтобы вычислить номер сотрудника. Из нужной строки выбираем зарплату. Если сотрудника с указанным номером нет, то его зарплата будет null. С помощью case заменяем null на 0.
18. Имеется таблица D_V с первым столбцом Dat типа DATE (первичный ключ) и вторым столбцом Val типа NUMBER. Пример (строки упорядочены по первому столбцу):
DAT VAL
01-08-08 232
02-08-08
10-08-08 182
11-08-08
21-08-08 240
22-08-08
Требуется написать запрос для получения на основе таблицы D_V следующей таблицы:
DAT MAX_VAL
01-08-08 232
02-08-08 232
10-08-08 182
11-08-08 182
21-08-08 240
22-08-08 240
Данная результирующая таблица должна быть упорядочена по Dat, но вместо пустых значений, которые присутствовали в столбце VAL отсортированной по DAT исходной таблицы, в столбце MAX_VAL результирующей таблицы, должны присутствовать значения столбца из предыдущей строки.
Всё так, только явным образом отсортируйте.
CREATE TABLE D_V
(DAT DATE,
VAL NUMBER(3),
PRIMARY KEY(DAT));
INSERT INTO D_V VALUES('01.08.08', 232);
INSERT INTO D_V VALUES('02.08.08', null);
INSERT INTO D_V VALUES('10.08.08', 182);
INSERT INTO D_V VALUES('11.08.08', null);
INSERT INTO D_V VALUES('21.08.08', 240);
INSERT INTO D_V VALUES('22.08.08', null);
SELECT dat, NVL(val, lag(val) OVER (ORDER BY dat)) max_val
FROM d_v;
DAT MAX_VAL
------------------------- ----------------------
01.08.08 232
02.08.08 232
10.08.08 182
11.08.08 182
21.08.08 240
22.08.08 240
6 rows selected
Комментарий:
С помощью функции NVL, мы проверяем значение на NULL. Если NULL, то с помощью функции Lag смещаемся на одно значение, чтобы узнать предыдущее значение.
19. Билеты для проезда в городском транспорте имеют шестизначный десятичный номер и буквенно-цифровую серию. Нумерация каждой серии билетов начинается с 000001.
Существует примета, что билет, у которого сумма трех первых цифр равна сумме трёх последних цифр - это "Счастливый билет".
Одной командой SELECT вывести количество "счастливых билетов" в каждой серии (одно число).
SELECT COUNT(*)-1 count
FROM
(SELECT LEVEL-1 x FROM dual CONNECT BY LEVEL <= 10) t1,
(SELECT LEVEL-1 x FROM dual CONNECT BY LEVEL <= 10) t2,
(SELECT LEVEL-1 x FROM dual CONNECT BY LEVEL <= 10) t3,
(SELECT LEVEL-1 x FROM dual CONNECT BY LEVEL <= 10) t4,
(SELECT LEVEL-1 x FROM dual CONNECT BY LEVEL <= 10) t5,
(SELECT LEVEL-1 x FROM dual CONNECT BY LEVEL <= 10) t6
WHERE t1.x +t2.x + t3.x = t4.x + t5.x + t6.x;
COUNT
----------------------
Комментарий:
С помощью псевдостолбца level мы для каждой цифры перебираем значения от 0 до 10. Затем считаем только те строки, для которых сумма первых трех цифр равна суме последних трех цифр. Из результата вычитаем единицу, так как отсчет начинается с 000001 и комбинации 000000 быть не может.
20. В системе существует таблица LGD с перечнем использующихся разговорных языков с первичным ключом Lang_Code. Причём в таблице есть ещё столбец Next_Lang, указывающий на каком языке система должна искать тексты при отсутствии текстов на данном языке (Lang_Code).
Примерное содержимое таблицы LGD:
LANG_CODE NEXT_LANG
RUS ENG
ENG RUS
UA RUS
KZ RUS
BY UA
EST ENG
Надо выдать всю цепочку предпочтений (для поиска текстов) для белорусского языка с кодом 'BY'. Результат состоит из одного столбца. В первой строке должно содержаться значение 'BY'.
CREATE TABLE LGD
(LANG_CODE VARCHAR2(3),
NEXT_LANG VARCHAR2(3)
PRIMARY KEY(LANG_CODE));
INSERT INTO LGD VALUES('RUS', 'ENG');
INSERT INTO LGD VALUES('ENG', 'RUS');
INSERT INTO LGD VALUES('UA', 'RUS');
INSERT INTO LGD VALUES('KZ', 'RUS');
INSERT INTO LGD VALUES('BY', 'UA');
INSERT INTO LGD VALUES('EST', 'ENG');
WITH
t AS
(SELECT lang_code, rownum r
FROM (SELECT lang_code
FROM lgd
UNION
SELECT next_lang
FROM lgd))
SELECT lang
FROM (SELECT t1.lang_code lang, t1.next_lang, t2.lang_code, t4.r id, t3.r
FROM lgd t1 LEFT JOIN lgd t2
ON t1.lang_code = t2.next_lang
LEFT JOIN t t3
ON t2.lang_code = t3.lang_code
LEFT JOIN t t4
ON t1.lang_code = t4.lang_code) tab
START WITH lang = 'BY'
CONNECT BY NOCYCLE PRIOR id = r;
Результат:
LANG
----
BY
UA
RUS
ENG
Комментарий:
Выбираем все языки из таблицы и нумеруем их. Затем соединяем таблицу LGD саму с собой и с пронумерованной выборкой для выявления подчиненности языков. Потом продвигаемся по полученному дереву от ребенка к родителю без циклов, начиная с белорусского языка.
ВАРИАНТ 6
(Базы данных Студент и Human Resources)
1. Вывести фамилии преподавателей, их зарплату и нарастающую сумму зарплат в пределах кафедры. Задачу решить без использования аналитических функций.
WITH
num as
(SELECT каф, зарп, фам, ROWNUM z FROM
(SELECT фамилия фам, кафедра каф, зарплата зарп
FROM преподаватели
ORDER BY кафедра, зарплата)),
numK as
(SELECT num.каф, зарп, фам, z-m_z+1 ran
FROM num
JOIN
(SELECT каф, min(z) m_z
FROM num
GROUP BY каф) t1
on num.каф = t1.каф),
O AS
(SELECT numK.каф, numK.зарп, фам, M_z z, ran
FROM numK
JOIN
(SELECT каф, зарп, min(ran) m_z
FROM numK
GROUP BY каф, зарп) t1
on numK.каф = t1.каф and numK.зарп =t1.зарп)
SELECT DISTINCT каф кафедра, фам фамилия, зарп зарплата,
(SELECT sum(зарп)
FROM O
WHERE ran <= numK.ran AND каф = numK.каф) Нарастающая_зп
FROM O numK
ORDER BY каф, Нарастающая_зп
Результат:
КАФЕДРА ФАМИЛИЯ ЗАРПЛАТА НАРАСТАЮЩАЯ_ЗП
---------- -------------------- -------- --------------
Кафедра 1 Соколов 1500 1500
Кафедра 1 Казанко 2000 3500
Кафедра 1 Викулина 3000 6500
Кафедра 1 Костыркин 4000 10500
Кафедра 2 Тарасова 2000 2000
Кафедра 2 Загарийчук 2000 4000
Кафедра 2 Студейкин 2500 6500
Кафедра 2 Позднякова 2500 9000
Кафедра 2 Абдулов 3000 12000
9 rows selected
Комментарий:
Во временном представлении num выбираем необходимую информацию о преподавателе и нумеруем строки, группируем преподавателей по кафедре и по зарплате по возрастанию. Для того, чтобы пронумеровать преподавателей в пределах кафедры во временном представлении numK, находим минимальное значение нумерации строки по кафедре, а затем, с помощью вычитания минимального значения из номера строки (единицу добавляем, чтобы счёт шёл от 1) даём новую нумерацию строкам. Во временном представлении O определяем преподавателей, у которых равные зарплаты в пределах кафедры. В главном запросе выбираем необходимую нам информацию + находим нарастающую зарплату в пределах кафедры.
2. Создать запрос для определения списка отделов, в которых суммарная зарплата больше средней суммарной зарплаты отделов, расположенных в том же городе, что и отдел Задачу решить с использованием аналитических функций.
with sum_sal AS(
SELECT DISTINCT sum(salary) OVER (PARTITION BY e.department_id) sum_salary, e.department_id, location_id
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
ORDER BY sum_salary DESC
)
SELECT department_id
FROM sum_sal all_sum
WHERE sum_salary >
(SELECT avg(sum_salary)
FROM sum_sal
WHERE location_id = all_sum.location_id)
ORDER BY department_id;
Результат:
DEPARTMENT_ID
-------------
Комментарий:
Во временном представлении sum_sal (при помощи аналитической функции) выбираем суммы зарплат по отделам, номер отдела и номер локации из двух таблиц (employees и departments) по полю “department_id”.
В главном запросе выбираем суммы, которые больше средней по номеру локации отдела (проверяем подзапрососом в главном запросе).
3. В произвольной строке удалить хвостовые и лидирующие пробелы, а также все лишние пробелы между словами (оставить только по одному). Задачу решить при помощи раздела Model.
WITH strTable AS (
SELECT ' my example string ' AS str
FROM dual
)
SELECT TRIM(corrStr) Answer
FROM strTable
MODEL
DIMENSION BY(1 n)
MEASURES (cast('' AS VARCHAR2(1000)) corrStr, TRIM(str) wrongStr)
RULES ITERATE(20)
(corrStr[1]= corrStr[1]||' ' ||SUBSTR(wrongStr[1], 1, instr(wrongStr[1],' ')-1),
wrongStr[1] = TRIM(SUBSTR(wrongStr[1],INSTR(wrongStr[1],' ')))||' '
)
Результат:
Комментарий:
Временное представление используем для проверки правильности исполнения задания.
Главный запрос (SELECT TRIM(corrStr)) выбирает данные из данных, которые получаются в разделе Model (значение corrStr), при выполнение запроса сразу обрезаем лидирующие и конечные пробелы.
В самом разделе model создаем искусственно размерность(DIMENSION) ‘1’ и две величины corrStr и wrongStr для хранения исправленной строки и изначально заданной.
Задаем 20 иттераций, чтобы действие выполнялось 20 раз (т.е. мы можем выбрать 20 слов разделенных любым количество пробелов.)
В строке (corrStr[1]= corrStr[1]||' ' ||SUBSTR(wrongStr[1], 1, instr(wrongStr[1],' ')-1) мы к текущему состоянию исправленной строки, добавляем пробел и слово, взятое из начала старой строки,до первого пробела в ней.
Далее мы отсекаем от строки wrongStr слово, которое добавили в исправленную и удаляем пробелы с помощью TRIM. Повторяем эти два действия столько, сколько иттераций.
4. Создать таблицу следующего вида:
| Номер | Оценка |
Создать запрос для получения прогноза оценки на пяти шагах. Прогноз оценки на следующем шаге рассчитывается, как сумма оценок на двух предыдущих шагах. Результат представить в виде:
| Номер | Прогноз оценки на пятом шаге | Список оценок по шагам |
| 2,4,6,10,16 | ||
| 2,5,7,12,19 | ||
| 1,4,5,9,14 | ||
| 1,5,6,11,17 |
Создаём таблицу и заполняем её:
CREATE TABLE Marks (номер number(4), оценка number(1));
INSERT INTO Marks (номер, оценка) values (7369,2);
INSERT INTO Marks (номер, оценка) values (7369,4);
INSERT INTO Marks (номер, оценка) values (7499,2);
INSERT INTO Marks (номер, оценка) values (7499,5);
INSERT INTO Marks (номер, оценка) values (7521,1);
INSERT INTO Marks (номер, оценка) values (7521,4);
INSERT INTO Marks (номер, оценка) values (7566,1);
INSERT INTO Marks (номер, оценка) values (7566,5);
SELECT *
FROM Marks
НОМЕР ОЦЕНКА
----- ------
7369 2
7369 4
7499 2
7499 5
7521 1
7521 4
7566 1
7566 5
8 rows selected
WITH t1 as
(SELECT о1.номер, о1.оценка rank1, о2.оценка rank2
FROM
WITH
num AS
(SELECT номер, оценка, rownum rn
FROM Marks
Order by номер),
step2 AS
(SELECT t1.номер, t2.оценка rank1, t1.оценка rank2
FROM num t1
JOIN
(SELECT номер, оценка
FROM num
WHERE MOD(rn,2)=1) t2
ON t1.номер = t2.номер
WHERE MOD(rn,2)=0),
stepN AS
(SELECT номер, rank1, rank2, (rank1+rank2) rank3, (rank1+rank2+rank2) rank4, (rank1+rank2+rank2+rank1+rank2) rank5
FROM step2)
SELECT номер "Номер", rank5 "Прогноз оценки на пятом шаге", rank1||','||rank2||','||rank3||','||rank4||','||rank5 "Список оценок по шагам"
FROM stepN
Результат:
Комментарий:
Во временном представлении num нумеруем строки данной таблицы и выбираем из таблицы оценки, что бы все нечетные строки содержали первые оценки, а четные – вторые(вр.представление step2). Далее во временном представлении stepN последовательно складываем оценки, получая следующую иттерацию (шаг), в результат выводим итоговую оценку на пятом шаге и оценки по шагам.
5. Создать регулярное выражение для проверки сложности пароля. Пароль не должен содержать три последовательные буквы латинского алфавита.
SELECT
(CASE fl
WHEN 0 THEN 'correct'
ELSE 'incorrect' END) AS answer
FROM
(SELECT COUNT(*) fl
FROM
(SELECT 'abcdefghijklmnopqrstuvwxyz' al
FROM dual)
WHERE REGEXP_SUBSTR('&enter_password', SUBSTR(al, level, 3)) IS NOT NULL
CONNECT BY level <= 26)
Результат:
Пароль: 111abc111
ANSWER
---------
incorrect
Пароль: 111ab1c11
ANSWER
---------
correct
Комментарий:
Подзапросом выбираем fl - количество вхождений в пароль трёх последовательных букв латинского алфавита. Если значение fl равно нулю, значит, что вхождений нет и пароль проходит проверку на сложность, иначе – пароль не годен.
6. Имеем таблицу:
| Номер | Категория | Продукт |
| 1 | Category 1 | Mango |
| 2 | ||
| 3 | ||
| 4 | Banana | |
| 5 | ||
| 6 | ||
| 7 | Category 2 | Vanilla |
| 8 | ||
| 9 | Strawberry |
Одной командой изменить данные в таблице в соответствии со следующей формой:
| Номер | Категория | Продукт |
| 1 | Category 1 | Mango |
| 2 | Category 1 | Mango |
| 3 | Category 1 | Mango |
| 4 | Category 1 | Banana |
| 5 | Category 1 | Banana |
| 6 | Category 1 | Banana |
| 7 | Category 2 | Vanilla |
| 8 | Category 2 | Vanilla |
| 9 | Category 2 | Strawberry |
Создаём таблицу и заполняем её:
CREATE TABLE fruits (номер NUMBER(3), категория VARCHAR2(25), продукт VARCHAR2(25))
INSERT INTO fruits VALUES (1,'Категория 1','Mango');
INSERT INTO fruits VALUES (2,NULL,NULL);
INSERT INTO fruits VALUES(3,NULL,NULL);
INSERT INTO fruits VALUES (4,NULL,'Banana');
INSERT INTO fruits VALUES (5,NULL,NULL);
INSERT INTO fruits VALUES (6,NULL,NULL);
INSERT INTO fruits VALUES (7,'Категория 2','Vanilla');
INSERT INTO fruits VALUES (8,NULL,NULL);
INSERT INTO fruits VALUES (9,NULL,'Strawberry');
SELECT номер, last_value(категория IGNORE NULLS) OVER (ORDER BY номер) AS Категория, last_value(продукт IGNORE NULLS) OVER (ORDER BY номер) AS продукт
FROM fruits;
Результат:
НОМЕР КАТЕГОРИЯ ПРОДУКТ
----- ------------------------- -------------------------
1 Категория 1 Mango
2 Категория 1 Mango
3 Категория 1 Mango
4 Категория 1 Banana
5 Категория 1 Banana
6 Категория 1 Banana
7 Категория 2 Vanilla
8 Категория 2 Vanilla
9 Категория 2 Strawberry
9 rows selected
Комментарий:
При помощи аналитических функций выбираем последнее ненулевое значение сверху по мере выборки таблицы (игнорируя null) в столбце и подставляем на те позиции в выводе, где значения пропущены. В каждой аналитической функции сортируем по номеру, чтобы сохранялась последовательность обработки данных.
7. Построить запрос для плотного ранжирования зарплат по отделам сотрудников с использованием аналитических функций.
SELECT department_id, last_name, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS RANK
FROM employees
ORDER BY department_id, salary DESC
Результат:
DEPARTMENT_ID LAST_NAME SALARY RANK
------------- ------------------------- -------- ----
10 Whalen 4400 1
20 Hartstein 13000 1
20 Fay 6000 2
30 Raphaely 11000 1
30 Khoo 3100 2
30 Baida 2900 3
107 rows selected
Комментарий:
Выбираем из таблицы employees номер отдела, фамилию, ранг. С помощью аналитической функции dense_rank() делаем плотное ранжирование по зарплатам внутри отделов. Для того, чтобы высший ранг присваивался максимальной зарплате, они сортируются по убыванию.
8. Построить запрос для неплотного ранжирования зарплат по отделам сотрудников без использования аналитических функций.
SELECT *
FROM
(SELECT last_name, salary, department_id,
(SELECT COUNT(*)
FROM employees
WHERE department_id = e.department_id AND salary > e.salary) +1 AS "Rank"
FROM employees e
ORDER BY department_id, salary DESC)
WHERE department_id IS NOT NULL
Результат:
LAST_NAME SALARY DEPARTMENT_ID Rank
------------------------- -------- ------------- ----
Whalen 4400 10 1
Hartstein 13000 20 1
Fay 6000 20 2
Raphaely 11000 30 1
Khoo 3100 30 2
Baida 2900 30 3
106 rows selected
Комментарий:
Выбираем из таблицы employees фамилию, зарплату и номер отдела. Подзапросом выбираем количество сотрудников, для которых зарплаты выше и отдел совпадает с отделом сотрудника из главного запроса. Единицу прибавляем, чтобы счёт ранга шёл от единицы. Исключаем сотрудников, которые не приписаны ни к одному отделу.
9. Создать запрос для определения списка сотрудников, которые имеют зарплату, с рангом от 3 до 5 в отделе, в котором они работают.
WITH ranks AS
(SELECT department_id, last_name, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS RANK
FROM employees
ORDER BY department_id, salary DESC)
SELECT department_id, last_name, rank
FROM ranks
WHERE rank BETWEEN 3 AND 5
Результат:
DEPARTMENT_ID LAST_NAME RANK
------------- ------------------------- ----
30 Baida 3
30 Tobias 4
30 Himuro 5
50 Kaufling 3
50 Vollman 4
50 Mourgos 5
60 Austin 3
60 Pataballa 3
60 Lorentz 4
80 Errazuriz 3
80 Ozer 4
80 Cambrault 5
80 Abel 5
100 Chen 3
100 Urman 4
100 Sciarra 5
16 rows selected
Комментарий:
Делаем выборку из запроса с плотным ранжированием по рангу - ranks. Главным запросом выводим итог (условие значения ранга от 3 до 5 выполняем конструкцией between).
10. Создать запрос для определения дат начала проведения Уимблдонского турнира в високосные годы второй половины века, зная, что турнир начинается за шесть недель до последнего понедельника августа. Век вводится как параметр.
undefine enter_century;
SELECT Даты
FROM (
SELECT
TO_CHAR(NEXT_DAY(LAST_DAY('1.08.'||(TO_CHAR(TO_DATE(TO_CHAR('&&enter_century'-1 || '50', '9999'), 'YYYY'), 'YYYY')+level-1))-7, 'ПОНЕДЕЛЬНИК')-42,'dd.mm.yyyy') Даты,
LAST_DAY('1.02.'||(TO_CHAR(TO_DATE(TO_CHAR('&enter_century'-1 || '50', '9999'), 'YYYY'), 'YYYY')+level-1)) feb
FROM DUAL
CONNECT BY level<=50)
WHERE TO_CHAR(feb, 'dd.mm.yyyy') = '29.02.' || EXTRACT (YEAR FROM feb);
Результат: век=20
ANSWER
--------
14.07.1952
16.07. 1956
18.07. 1960
20.07. 1964
15.07. 1968
17.07. 1972
19.07. 1976
14.07. 1980
16.07. 1984
18.07. 1988
20.07. 1992
15.07. 1996
12 rows selected
Комментарий:
Т.к. мы знаем, что к веку относятся года в диапазоне от 00 до 99, то для того, чтобы получить 50 год (т.к. нужна вторая половина), надо из заданного значения вычесть 1 и добавить к концу полученного числа ‘50’.
Переведя **50(50й год заданного века) в дату, мы можем получить дату начала турнира. Для каждого года второй половины века повторяем ту же операцию и находим даты начала. Чтобы определить високосные года, берём из last_d год. Вычисляем, какое число - последний день февраля. Если это 29е, то год является високосным, следовательно этот год должен быть в результате.
11. Создать внешнюю таблицу для чтения данных из текстового файла init.ora. Таблица должна содержать два столбца: Имя параметра и Значение. Директория Init. Файл для примера находится в папке \\FEANOR\Public2\КИТвП\Курс 3\Семестр 6\Базы данных и СУБД. Создать запрос к внешней таблице, который позволит найти общее количество файлов управления (Control_Files).
CREATE TABLE init_table (param varchar2(32), val varchar2(256))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY init
ACCESS PARAMETERS
(RECORDS DELIMITED BY newline
NOLOGFILE
FIELDS TERMINATED BY '=' LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(param, val))
LOCATION ('init.ora'))
REJECT LIMIT UNLIMITED;
DESC init_table
Name Null Type
----- ---- -------------
PARAM VARCHAR2(32)
VAL VARCHAR2(256)
SELECT FLOOR(LENGTH(NVL(regexp_replace(val, '[^"]', ''), 0)) /2) AS Answer
FROM init_table
WHERE param = 'control_files';
Результат:
ANSWER
------
Комментарий:
Создаём таблицу: строки разделяем по новой строке, поля по знаку =. Строки с пустыми полями отклоняем. Количество строк для выборки не ограничено.
Создаём запрос: регулярным выражением все, что не кавычки – замещаем на пустоту. Т.к. каждый файл в кавычках, то если мы поделим количество кавычек на два - получим количество файлов. Делаем выборку из созданной нами таблицы.
12. Создать запрос для вывода всех дат приема на работу сотрудников, отсутствующих в некотором временном интервале дат.
WITH t1 AS
(SELECT d1-1+level temp
FROM
(SELECT to_date('&enter_date1', 'dd.mm.yyyy') d1, to_date('&enter_date2', 'dd.mm.yyyy') d2
FROM dual) CONNECT BY level <= d2 - d1 +1)
SELECT hire_date
FROM employees
WHERE hire_date NOT IN
(SELECT * FROM t1)
ORDER BY hire_date
Результат:
HIRE_DATE
---------
17.06.87
17.09.87
21.09.89
04.01.00
13.01.00
24.01.00
29.01.00
03.02.00
06.02.00
23.02.00
08.03.00
24.03.00
21.04.00
21.04.00
14 rows selected
Комментарий:
Задаём интервал параметрически: 1ая дата – начало интервала, 2ая дата – конец интервала. Во временном представлении находим все даты из заданного интервала и выводим в результат даты приёма на работу сотрудников, не попадающих в этот интервал.
13. Создать запрос, который позволит получать все комбинации сумм для чисел, содержащихся в числовом столбце таблицы. Например, имеется таблица:
ID A1 72 123 31
Надо получить
ID Сумма A
1 7+12 192 7+31 383 12+31 43 4 7+12+31 50Таблица может содержать любое число чисел. Задачу решить без использования раздела Model.
Создаём таблицу и заполняем её:
CREATE TABLE test_table (ID NUMBER(8,0), A NUMBER(8,0));
INSERT INTO test_table VALUES (1,7);
INSERT INTO test_table VALUES (2,12);
INSERT INTO test_table VALUES (3,31);
DESC test_table
Name Null Type
---- ---- ---------
ID NUMBER(8)
A NUMBER(8)
WITH connected AS (SELECT sys_connect_by_path(A,'+') AS con_by_path
FROM test_table
WHERE LEVEL>1
CONNECT BY PRIOR ID<ID),
res AS
(SELECT ltrim(con_by_path,'+') AS Сумма,
(SELECT SUM(A)
FROM test_table
WHERE con_by_path LIKE '%+'||A||'%') AS "A"
FROM connected
ORDER BY 2)
SELECT rownum AS ID, Сумма, A
FROM res
Результат:
Дата добавления: 2015-08-18; просмотров: 198 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| Main_Table PK User_Constraints 3 страница | | | Main_Table PK User_Constraints 5 страница |