|
Читайте также: |
then 0
else case when b = c or abs(b - c) = 1 or b = d or abs(b - d) = 2 or b = e or abs(b - e) = 3 or b = f or abs(b - f) = 4 or b = g or abs(b - g) = 5 or b = h or abs(b - h) = 6
then 0
else case when c = d or abs(c - d) = 1 or c = e or abs(c - e) = 2 or c = f or abs(c - f) = 3 or c = g or abs(c - g) = 4 or c = h or abs(c - h) = 5
then 0
else case when d = e or abs(d - e) = 1 or d = f or abs(d - f) = 2 or d = g or abs(d - g) = 3 or d = h or abs(d - h) = 4
then 0
else case when e = f or abs(e - f) = 1 or e = g or abs(e - g) = 2 or e = h or abs(e - h) = 3
then 0
else case when f = g or abs(f - g) = 1 or f = h or abs(f - h) = 2
then 0
else case when g = h or abs(g - h) = 1
then 0
else 1
end
end
end
end
end
end
end Bool
from
(select level a from dual connect by level <= 8)
cross join
(select level b from dual connect by level <= 8)
cross join
(select level c from dual connect by level <= 8)
cross join
(select level d from dual connect by level <= 8)
cross join
(select level e from dual connect by level <= 8)
cross join
(select level f from dual connect by level <= 8)
cross join
(select level g from dual connect by level <= 8)
cross join
(select level h from dual connect by level <= 8)
из таблицы, п1.
5) Выбираем столбцы конечного результата из таблицы п4, т.е. строки, где в последнем столбце 1
select 'a' || a A, 'b' || b B, 'c' || c C, 'd' || d D, 'e' || e E, 'f' || f F, 'g' || g G, 'h' || h H
from
(…) where Bool = 1
4. Дана произвольная строка, состоящая из чисел разделённых одинаковыми арифметическими операторами (т.е. только +, только -). Требуется вычислить выражение, записанное таким образом (без использования регулярных выражений).
select str
from(select str, rownum r
from(select str
from(select '&str+-' str from dual)
model
dimension by (0 dim)
measures (str)
rules iterate (1000) UNTIL(LTRIM(str[0], '+-') is null)(
str[1] = to_number(replace(str[0], ltrim(str[0], '0123456789'))),
str[2] = nvl2(str[2], str[2] + str[1], str[1]),
str[3] = nvl2(str[3], str[3] - str[1], str[1]),
str[5] =
case
when substr(str[4], 1, 1) like '+%' then str[2]
when substr(str[4], 1, 1) like '-%' then str[3]
else str[2] end,
str[4] = ltrim(str[0], '0123456789'),
str[0] = ltrim(ltrim(str[0], '0123456789'), '+-'))))
where r = 5;
STR
-------
Комментарий:
С помощью Model на основе входного значения str выводим таблицу, где
str[0] – наша строка (с каждой итерацией спереди строки удаляются символы вплоть до первого арифметического знака)
str[0] = ltrim(ltrim(str[0], '0123456789'), '+-')
str[1] – число из строки до первого знака
str[1] = to_number(replace(str[0], ltrim(str[0], '0123456789'))),
str[2] – хранит сумму чисел на текущей интерации
str[3] – хранит разность чисел на текущей интерации
str[4] – хранит подстроку(текущая строка без цифр до знака)
str[5] – в зависимости от того, с какого знака начинается подстрока str[4], записывается либо накопленная сумма, либо разность. Она впоследствии и будет выведена на экран с помощью условия rownum = 5.
5. Для заданных интервалов дат определить непересекающиеся интервалы. Например, для интервалов
13.01.93 24.07.98
21.09.89 27.10.93
28.10.99 15.03.00
результат должен быть:
20.09.89
25.07.98 27.10.99
16.03.00
select Ds, De
from
(select Ds, De, case when r1 is null then 0 else r1 end as R
from
(select D2 + 1 as Ds, rownum r1
from Edate s1
where not exists (
select *
from Edate s2
where s2.D2 > s1.D2 and s2.D1 <= s1.D2
)
order by Ds) Dss
full join
(select D1 - 1 as De
from Edate s1
where not exists (
select *
from Edate s2
where s2.D1 < s1.D1 and s2.D2 >= s1.D1
)
order by De) Dee
on Dss.Ds < Dee.De
order by R);
DS DE
-------- --------
20.09.89
25.07.98 27.10.99
16.03.00
Комментарий:
1) Создадим таблицу как в примере, назовем ее Edate
2) Подзапрос
(select D2 + 1 as Ds, rownum r1
from Edate s1
where not exists (
select *
from Edate s2
where s2.D2 > s1.D2 and s2.D1 <= s1.D2
) order by Ds)
Выводит список дат, которые являются концами непрерывных множеств + 1, т.е. в нашем случае
Это 26.07.98 и 16.03.00
3) Также и подзапрос
(select D1 - 1 as De, rownum r2
from Edate s1
where not exists (
select *
from Edate s2
where s2.D1 < s1.D1 and s2.D2 >= s1.D1
) order by De)
Только начала непрерывных множеств.
4) Соединив эти две таблицы full join по условию что конец непрерывного множества меньше начала получим результат, остается его отсортировать, что сделаем добавив столбец rownum для первого подзапроса.
6. Определить ближайший к заданной дате год, когда 29 февраля придется на воскресенье.
select Date_Sun
from (select to_date('&date', 'DD-MM-YYYY') as Date_Sun from dual)
model
dimension by (0 dim)
measures(Date_Sun)
rules iterate (100000) until (to_char(Date_Sun[0]) like '29.02%' and to_char(Date_Sun[0], 'D') = '7')(
Date_Sun[0] =
case
when mod(to_number(to_char(Date_Sun[0], 'YYYY')), 4) = 0 or mod(to_number(to_char(Date_Sun[0], 'YYYY')), 400) = 0 then
case
when to_char(Date_Sun[0], 'MM') = '02' then
case
when to_char(Date_Sun[0], 'DD') = '29' then
case
when to_char(Date_Sun[0], 'D') = '7' then Date_Sun[0]
else Date_Sun[0] + 1
end
else Date_Sun[0] + 1 end
else Date_Sun[0] + to_yminterval('00-01') end
else to_date(trunc(add_months(Date_Sun[0], 12), 'YYYY'), 'DD-MM-YYY') end
);
DATE_SUN
--------
29.02.04
Комментарий:
Из таблицы dual выбираем значение даты ближайшего воскресения 29 февраля.
Для этого с помощью model изменяем введенную дату пока она не станет искомой
1) Если год не високосный то переходим на начало следующего года
2) Если год високосный, но не февраль, прибавляем к дате месяц
3) Если год високосный и февраль, но не 29 или же 29, но не воскресенье, то добавляем день
4) Если все условия соблюдены, дата не изменяется, происходит выход из цикла по условию
to_char(Date_Sun[0]) like '29.02%' and to_char(Date_Sun[0], 'D') = '7'
и дата выводится на экран
7. Имеется таблица с колонкой, которая содержит множество значений, разделенных запятыми. Требуется создать запрос, который каждое значение выведет на отдельной строке. Например, дана таблица:
| Номер | Телефон |
| 2-78,2-89 | |
| 2-78,2-83,8-34 |
Результат:
| Номер | Телефон |
| 2-78 | |
| 2-89 | |
| 2-78 | |
| 2-83 | |
| 8-34 |
CREATE TABLE num_tel (номер VARCHAR2(25), телефон VARCHAR2(25));
INSERT INTO num_tel VALUES (952240,'2-78,2-89');
INSERT INTO num_tel VALUES (952423,'2-78,2-83,8-34');
SELECT decode(lvl, 1, Номер) as "Номер", Телефон as "Телефон"
FROM
(SELECT distinct Номер, level lvl, regexp_substr(Телефон,'[^,]+',1,level) Телефон
FROM num_tel
CONNECT BY regexp_count(Телефон, '[^,]+') >= level
ORDER BY Номер, Телефон)
Номер Телефон
------------------------ -------------------------
952240 2-78
2-89
952423 2-78
2-83
8-34
Комментарий:
1) Создаем таблицу num_tel, и записываем в нее данные.
2) Подзапрос возвращает таблицу со столбцами «номер», псевдостолбец level и подстроку до запятой исходя из значения псевдостолбца.
3) Выбираем лишь столбцы «Номер» (если псевдостолбец ненулевой, то подставляется null) и «Телефон».
8. Одной командой увеличить зарплату King в 2 раза, Kohhar в 1.5 раза, а Gietz в 3 раза.
update EMPLOYEES3 e1
set salary = (select rsal from (select Emp_ID, case when Last_name like 'King' then salary*2
when Last_name like 'Kochhar' then salary*1.5
when Last_name like 'Gietz' then salary*3 else salary end as rsal
from EMPLOYEES3) e2
where e1.Emp_ID = e2.Emp_ID);
107 rows updated.
Комментарий:
Связанная команда Update изменяет строки таблицы EMPLOYEES2 на основе строк другой таблицы, которая представляет из себя уже ту же самую таблицу, но с измененными зарплатами King, Kohhar, Gietz. Тем самым достигается желаемый результат. EMPLOYEES2 – таблица, которая содержит данные из таблицы EMPLOYEES
9. Имеется таблица с фамилиями сотрудников и номерами их телефонов:
| Фамилия | Домашний телефон | Рабочий телефон | Мобильный телефон |
| Петров С.С. | 492-89-70 | 195-66-33 | +7-921-984-46-43 |
| Путов Н.Н. | 535-77-12 | 195-67-88 | +7-921-989-49-49 |
| Пеньков А.А. | 273-02-47 | 195-68-55 | +7-921-987-46-83 |
Создать запрос для получения сведений в виде:
| Фамилия | Вид телефона | Телефон |
| Петров С.С. | Домашний телефон | 492-89-70 |
| Рабочий телефон | 195-66-33 | |
| Мобильный телефон | +7-921-984-46-43 | |
| Путов Н.Н. | Домашний телефон | 535-77-12 |
| Рабочий телефон | 195-67-88 | |
| Мобильный телефон | +7-921-989-49-49 | |
| Пеньков А.А. | Домашний телефон | 273-02-47 |
| Рабочий телефон | 195-68-55 | |
| Мобильный телефон | +7-921-987-46-83 |
select "Фамилия", T as "Вид телефона", "Домашний телефон" as "Телефон"
from
(select "Фамилия", 'Домашний телефон' as T, "Домашний телефон", rownum r1, '1'
from EMPTEL
union all
select null, 'Рабочий телефон', "Рабочий телефон", rownum r2, '2'
from EMPTEL
union all
select null, 'Мобильный телефон', "Мобильный телефон", rownum r3, '3'
from EMPTEL
order by 4, 5)
Фамилия Вид телефона Телефон
-------------------- ----------------- ------------------
Петров С.С. Домашний телефон 492-89-70
Рабочий телефон 195-66-33
Мобильный телефон +7-921-984-46-43
Путов Н.Н. Домашний телефон 535-77-12
Рабочий телефон 195-67-88
Мобильный телефон +7-921-989-49-49
Пеньков А.А. Домашний телефон 273-02-47
Рабочий телефон 195-68-55
Мобильный телефон +7-921-987-46-83
Комментарий:
1) Таблица EMPTEL содержит данные исходной таблицы
2) Объединяем три таблицы: первая содержит в столбце «Фамилия» фамилию работника, в столбце «Вид телефона» ‘Домашний телефон’, в столбце «Телефон» номер домашнего телефона, вторая в столбце «Фамилия» значение null и остальные значения по тому же принципу как и в первой таблице, третья аналогично второй.
3) Сортируем по искусственным столбцам rownum.
10. Вывести информацию о суммарной зарплате сотрудников по должностям в 10, 20, 30 и 40 отделах. Задачу решить с использованием и без использования функции PIVOT. Предусмотреть матричное представление.
select (select sum(salary) from employees where department_id = 10) as "Administration",
(select sum(salary) from employees where department_id = 10) as "Marketing",
(select sum(salary) from employees where department_id = 10) as "Purchasing",
(select sum(salary) from employees where department_id = 10) as "Human Resources"
from dual
select * from (
select DEPARTMENT_ID as Department, salary
from employees
)pivot(
sum(salary)
for Department in (10 as "Administration",20 as "Marketing", 30 as "Purchasing", 40 as "Human Resources")
)
order by 1
Administration Marketing Purchasing Human Resources
-------------- --------- ---------- ---------------
4400 4400 4400 4400
Комментарий:
1) Из таблицы dual выбираем значения: каждый подзапрос выводит суммарную зарплату определенного отдел из таблицы employees. select sum(salary) from employees where department_id = 10
Каждый столбец назван по названию департамента "Administration"
2) С пoмощью функции Pivot выводим в матричном виде значения суммарных зарплат по отделу. Функция Pivot позволяет вертикальные данные разносить по таблице как угодно.
11. Вывести все даты за 2011 год и соответствующие дни недели без использования иерархических запросов и Model.
Select To_Date(Rownum, 'DDD') - (Select Add_Months(Trunc(To_date('01.01.2011'), 'Y'), 12) - Trunc(To_date('01.01.2011'),'Y')
From Dual) Days_Of_Year,
To_Char(To_Date(Rownum,'DDD')-(Select Add_Months(Trunc(To_Date('01.01.2011'),'Y'),12) - Trunc(To_Date('01.01.2011'),'Y')
From Dual),'Day') Day_Of_Week
From (
select level a from dual connect by level <= 366
)
Where Rownum Between 0 And (Select Add_Months(Trunc(To_Date('01.01.2011'),'Y'),12) - Trunc(To_Date('01.01.2011'),'Y')
From Dual);
DAYS_OF_YEAR DAY_OF_WEEK
------------ -----------
01.01.11 Суббота
02.01.11 Воскресенье
29.12.11 Четверг
30.12.11 Пятница
31.12.11 Суббота
365 rows selected
Комментарий:
Выбираем строки:
1) День года - как результат запроса To_Date(Rownum, 'DDD') - (Select Add_Months(Trunc(To_date('01.01.2011'), 'Y'), 12) - Trunc(To_date('01.01.2011'),'Y')
From Dual) – приводим обычный численный столбец к типу date и вычитаем год
2) День недели – как результат приведения к типу char дня года с шаблоном ‘Day’.
Rownum берется из таблицы значений select level a from dual connect by level <= 366
С условием, что rownum не больше 365 (результат вычисления)
12. В произвольной символьной строке, например,
‘aagftrhheteiiiiiooraaaapphhhyyfhdfhdfyyy’,
для присутствующих в ней и идущих подряд повторяющихся символов вывести информацию об этих повторяющихся символах в виде строк следующего содержания:
| Позиция в строке | Символ | Количество в подстроке |
| a | ||
| h | ||
| i | ||
| o | ||
| a | ||
| p | ||
| h | ||
| y | ||
| y |
Задачу решить с использованием регулярных выражений без использования аналитических функций
select regexp_instr(str, pattern, 1, level) "Позиция в строке",
substr(regexp_substr(str, pattern, 1, level), 1, 1) "Символ",
length(regexp_substr(str, pattern, 1, level)) "Количество в подстроке"
from (select 'aagftrhheteiiiiiooraaaapphhhyyfhdfhdfyyy' str, '(\w)\1+' pattern from dual)
connect by regexp_instr(str, pattern, 1, level) > 0
Комментарий:
Выводим три столбца: первый – позиция в строке, вычисляется функцией instr
Второй – повторяющийся символ, вычисляется функцией substr
Третий – количество повторяющихся символов, вычисляется функцией length
В качестве шаблона используется '(\w)\1+'
13. Создать регулярное выражение для проверки сложности пароля. Пароль не должен содержать три последовательные буквы латинского алфавита.
WITH
r as (SELECT 'df5dg6dh7sj7' d from dual)
SELECT case when REGEXP_LIKE(d, '([a-z]|[A-Z]){3,}') then 'no' else 'yes' end
from r
yes
Комментарий:
Создадим временную таблицу для проверки работы выражения
WITH
r as (SELECT 'dfjslknpuuudjcn' d from dual)
REGEXP_LIKE(d, '([a-z]|[A-Z]){3,} -- регулярное выражение отбросит те данные, которые содержит 3 подряд идущих символа из множества [a-z] & [A-Z]
14. Для произвольной строки, состоящей из чисел, разделенных указанным разделителем, получить строку, отображающую эти числа в обратном порядке. Например, для исходной строки: 0|0|1|2|1|2|10|22|34|15|0|105|66|73 должна быть получена строка: 73|66|105|0|15|34|22|10|2|1|2|1|0|0. Задачу решить:
- с использованием регулярных выражений;
- с использованием раздела Model.
with tab1 as (select '0|0|1|2|1|2|10|22|34|15|0|105|66|73' as str from dual)
select str as NewString from
(select LTRIM(SYS_CONNECT_BY_PATH(s2, '|'), ',') str, connect_by_isleaf islf
from
(select s1 as s2, rownum r2 from
(select regexp_substr(str, '[^|]+', 1, level) as s1, rownum r1 from tab1
connect by regexp_count(str, '[|]') + 2 > level
order by r1 desc))
start with r2 = 1
connect by prior r2 = r2 - 1)
where islf = 1;
NEWSTRING
-----------------------------------------------------------------------------------------------------
|73|66|105|0|15|34|22|10|2|1|2|1|0|0
Комментарий:
С помощью регулярных выражений и иерархии из строки сделаем столбец, в котором каждая строка – следующее отделенное значение
Строки в столбце отсортируем в обратном порядке
С помощью sys_connect_by_path обратно из столбца сделаем строку, где значения будут отделяться указанным разделителем.
15. Создать запрос для распознавания строк, заключенных в двойные кавычки. Предполагается, что любой символ может быть в строке, в том числе и двойные кавычки, если им предшествует нечетное число обратных слэшей.
select '&&str' as STR, case
when mod((length(s1) - length(replace(s1,'\', ''))),2) = 1
and regexp_like(s1,'[\]"[[:graph:]]')
then regexp_replace(s1, '^["]|["]$', '')
else replace(s1, '"', '') end as pro
from
(select regexp_substr('&&str', '".*"',1,1,'n') s1
from dual)
STR PRO
------------------------ ------
srhdfgjh\\"oracle"\\fgkh oracle
Комментарий:
Запрос выводит только часть фразы, которая была внутри кавычек, не выводя сами кавычки. Выводятся они только в том, случае, если перед их левыми двойными кавычками стоит нечётное число обратных слэшей.
За это отвечает mod((length(s1) - length(replace(s1,'\', ''))),2) = 1
and regexp_like(s1,'[\]"[[:graph:]]')
Здесь проверятся число таких слэшей и проверка на то, стоят ли они перед кавычками.
16. Создать запрос для разделения "задвоенных" данных. Например, из
| CODE_OPERATION | ID_CLIENT |
| 1000 1100 | 841000 841100 |
сделать
| RN | CNT | CODE_OPERATION | ID_CLIENT |
| 1000 1100 | 841000 841100 | ||
create table DoubleDat(CODE_OPERATION varchar(25), ID_CLIENT varchar(25));
insert into DoubleDat values('10001100', '841000 841100');
with
TwithRow as (SELECT CODE_OPERATION cod, ID_CLIENT numCl, rownum rn FROM DoubleDat),
T as (SELECT rn, (rn - 1) as cnt, cod, numCl FROM TwithRow
UNION
SELECT rn, rn, rtrim(rtrim(cod, '0123456789'), ' '), rtrim(rtrim(numCl, '0123456789'), ' ') FROM TwithRow
UNION
SELECT rn, rn + 1, ltrim(ltrim(cod, '0123456789'), ' '), ltrim(ltrim(numCl, '0123456789'), ' ') FROM TwithRow)
SELECT rn, cnt, cod CODE_OPERATION, numCl ID_CLIENT FROM T order by rn,cnt;
RN CNT CODE_OPERATION ID_CLIENT
-- --- ------------------------- -------------------------
1 0 1000 1100 841000 841100
1 1 1000 841000
1 2 1100 841100
Комментарий:
Создаем две временные таблицы:
1) TwithRow as (SELECT CODE_OPERATION cod, ID_CLIENT ncl, rownum rn FROM DoubleDat)
Таблица содержит выборку из данной таблицы вместе с номерами строк
2) T as (SELECT rn, (rn - 1) as cnt, cod, ncl FROM TwithRow
UNION
SELECT rn, rn, substr(cod,0,instr(cod,' ')), substr(ncl,0,instr(ncl,' ')) FROM TwithRow
UNION
SELECT rn, rn + 1, substr(cod,instr(cod,' ') + 1, length(cod)), substr(ncl,instr(ncl,' ')+1, length(ncl)) FROM TwithRow)
Таблицы содержит номер строки из предыдущей таблицы cnt;
номер строки в вычисленной таблице rn, первое число задвоенных данных из первого и второго столбца данной таблицы; второе число задвоенных данных из первого и второго столбца данной таблицы.
3) Выберем все данные из второй временной таблицы
17. Создать запрос для определения правильно и неправильно написанных десятичных чисел, предполагая, что десятичная часть отделяется точкой, а разряды целой части (каждые три цифры) могут разделяться или запятой, или пробелом, или ничем не разделяться. Комбинирование разделителей целой части не допускается.
With Str1 As (Select '&a' numb From Dual)
Select case when numb in (Regexp_Substr (numb, '\d{1,3}([ ]\d{3})*\.(\d{3}[ ])*\d*'), Regexp_Substr (numb, '\d{1,3}([,]\d{3})*\.(\d{3}[,])*\d*'), Regexp_Substr (numb, '[:digit:]*\.[:digit:]*\'))
then 'yes'
else 'no'
end as "result"
From Str1
result
------
yes
Комментарий:
1) Создадим три шаблона, на которые должно быть похоже наше число, т.е. либо тройки чисел через пробелы и десятичная часть отделяется точкой \d{1,3}([ ]\d{3})*\.(\d{3}[ ])*\d*', либо через запятые и десятичная часть отделяется точкой \d{1,3}([,]\d{3})*\.(\d{3}[,])*\d*', либо без разделителей и десятичная часть отделяется точкой [:digit:]*\.[:digit:]*\
2) Далее по этим шаблонам вырезаем из входного числа новые числа, соответствующие шаблонам.
3) Сравниваем входное число с полученными, если удачно, значит входное число соответствует одному из шаблонов и поэтому выводим ‘yes’ иначе ‘no’
18. Из таблицы вида:
| ID | DES | T |
| A | a1 | |
| A | a2 | |
| A | a3 | |
| B | a1 | |
| B | a2 | |
| C | a3 |
получить таблицу:
| I | a1 | a2 | a3 |
| C | |||
| B | |||
| A |
select t1.id "I", case when t2.t is null then 0 else t2.t end "a1",
case when t3.t is null then 0 else t3.t end "a2",
case when t4.t is null then 0 else t4.t end "a3"
from (select distinct id
from TempTable) t1 left join (select TempTable.id, TempTable.des, TempTable.t from TempTable where des = 'a1') t2
on t1.id = t2.id left join (select TempTable.id, TempTable.des, TempTable.t from TempTable where des = 'a2') t3
on t1.id = t3.id left join (select TempTable.id, TempTable.des, TempTable.t from TempTable where des = 'a3') t4
on t1.id = t4.id
order by t1.id desc;
I a1 a2 a3
- -- -- --
C 0 0 45
B 10 23 0
A 12 3 1
Комментарий:
Производим выборку из заданной таблицы TempTable, столбцы новой таблицы I, a1,a2,a3. Эти столбцы получаются путем соединения таблиц: таблицы с Id, таблицы с Id и T, где des = a1, таблицы c Id и T, где des = a2, таблицы c Id и T, где des = a3. Соединение происходит по равным ID.
Сама выборка представляет из себя выборку значений t2.t, t3.t, t4.t, (eсли значение null меняем его на 0) и столбца ID
19. Задана таблица
| A | COL1 |
Требуется сгруппировать значения по группам так, чтобы сумма в группе не превышала заданное число (100).
| A | COL1 | GRP |
select A, COL1,GRP
from test2
MODEL
DIMENSION BY(row_number() OVER(order by A) rn)
Дата добавления: 2015-08-18; просмотров: 67 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| Main_Table PK User_Constraints 22 страница | | | Main_Table PK User_Constraints 24 страница |