|
Читайте также: |
6. Создать запрос для получения информации о студентах, которые сдали экзамены по всем предметам, предусмотренных для их специальности учебным планом.
SELECT * FROM студенты WHERE номер_студента IN (SELECT DISTINCT номер_студента FROM студентыMINUS (SELECT DISTINCT номер_студента FROM (SELECT * FROM (SELECT студенты.номер_студента, номер_дисциплиныFROM студентыINNER JOIN группы ON студенты.номер_группы = группы.номер_группыINNER JOIN учебные_планы ON группы.код_специальности = учебные_планы.код_специальности)MINUSSELECT студенты.номер_студента, номер_дисциплиныFROM студентыINNER JOIN успеваемость ON успеваемость.номер_студента = студенты.номер_студентаWHERE оценка > 2))) С помощью внутренних запросов находим сначала то, что надо сдать, потом находим сданное, вычитаем (получим студентов, у которых есть долги), потом вычитаем из всех студентов студентов с долгами НОМЕР_СТУДЕНТА ФАМИЛИЯ ИМЯ ОТЧЕСТВО СТИПЕНДИЯ НОМЕР_ГРУППЫ -------------- --------------- --------------- --------------- --------- --------------- 3412 Поляков Анатолий Алексеевич 200 121 3413 Старова Любовь Михайловна 250 121 3418 Усов Валерий Харитонович 250 123
7. Создать запрос, для определения для каждой пары преподавателей их ближайшего общего начальника. Результат представить в виде:
| Имя начальника | Номер начальника | PATH1 | PATH2 |
| JONES | 7566 | FORD-->JONES | ADAMS-->SCOTT-->JONES |
SELECT r.фамилия "Имя начальника", r.номер_преподавателя "Номер начальника", r.path1, p.path2FROM (SELECT фамилия, номер_преподавателя, LTRIM(sys_connect_by_path(фамилия,'->'),'->') path1FROM Преподаватели WHERE level > 1START WITH номер_преподавателя IN(SELECT номер_преподавателя FROM преподаватели)CONNECT BY преподаватели.номер_преподавателя = PRIOR подчиняется) rINNER JOIN (SELECT фамилия, номер_преподавателя, LTRIM(sys_connect_by_path(фамилия,'->'),'->') path2FROM преподаватели WHERE level > 1START WITH номер_преподавателя IN (SELECT номер_преподавателя FROM преподаватели)CONNECT BY номер_преподавателя = PRIOR подчиняется) pON r.номер_преподавателя = p.номер_преподавателя AND r.path1!= p.path2WHERE LENGTH(r.path1) > LENGTH(p.path2) OR (LENGTH(r.path1) = LENGTH(p.path2) AND GREATEST(r.path1, p.path2) = r.path1) В подзапросах r и p находим ряд подчиненности для всех преподавателей, а потом соединяем их (inner join), получая подчиненность в два столбца. Проверки убирают лишних Имя начальника Номер начальника PATH1 PATH2 -------------------- ---------------- ------------------------------------------Костыркин 4002 Соколов->Викулина->Костыркин Викулина->Костыркин Костыркин 4002 Казанко->Соколов->Викулина->Костыркин Викулина->Костыркин Абдулов 4008 Позднякова->Загарийчук->Абдулов Загарийчук->Абдулов Викулина 4001 Казанко->Соколов->Викулина Соколов->Викулина Костыркин 4002 Казанко->Соколов->Викулина->Костыркин Соколов->Викулина->Костыркин Абдулов 4008 Загарийчук->Абдулов Студейкин->Абдулов Абдулов 4008 Позднякова->Загарийчук->Абдулов Студейкин->Абдулов Абдулов 4008 Студейкин->Абдулов Тарасова->Абдулов Абдулов 4008 Загарийчук->Абдулов Тарасова->Абдулов Абдулов 4008 Позднякова->Загарийчук->Абдулов Тарасова->Абдулов 10 rows selected
8. Создать запрос для вывода фамилий трех преподавателей, получающих самые высокие зарплаты. Если самую низкую зарплату у найденных трех преподавателей получают и какие-то другие преподаватели, они тоже должны попасть в список.
SELECT фамилия, преподаватели.зарплатаFROM (SELECT DISTINCT зарплатаFROM (SELECT зарплатаFROM преподавателиORDER BY зарплата DESC)WHERE rownum < 4) зпINNER JOIN преподаватели ON преподаватели.зарплата = зп.зарплата Найдем три наибольшие неуникальные зарплаты, а потом соединим с таблицей преподавателей, найдя, тем самым, всех преподавателей с такими зарплатами, а также дополнительно тех, кто получает наименьшую среди найденных ФАМИЛИЯ ЗАРПЛАТА-------------------- --------Костыркин 4000 Викулина 3000 Абдулов 3000 Либо, если убрать из таблицы, например, Абдулова, то: ФАМИЛИЯ ЗАРПЛАТА-------------------- --------Костыркин 4000 Викулина 3000 Студейкин 2500 Позднякова 2500
9. Создать запрос для распознавания строк, заключенных в двойные кавычки. Предполагается, что любой символ может быть в строке, в том числе и двойные кавычки, если им предшествует нечетное число обратных слэшей.
WITH st AS (SELECT ' 1"\\\"\\\"k+\\"jj\"nvh 2 test1.' str FROM dual) SELECT CASE regexp_instr(str, '[^\\]+(\\\\)+"', 1, 1) WHEN 0 THEN (SELECT regexp_substr(str, '\".*[^\]\"|\".*\"', 1, 1) res FROM st)ELSE (SELECT regexp_substr(str, '\"(\\\\)*\"|\".*?[^\\](\\\\)+"', 1, 1) res FROM st) END pFROM st Если в строке нет места с четным количеством обратных слешей(instr возвращает 0) перед двойными кавычками, то извлекаю из строки все между двойными кавычками. Иначе ориентируюсь по позиции в строке, где располагается четное количество обратных слешей.В данной задаче я использую «ленивую» квантификацию:.*? P --------------"\\\"\\\"k+\\" WITH st AS (SELECT ' 1"h\\"jk\"k+jj\"nvh 2 test1.' str FROM dual) P -----"h\\" WITH st AS (SELECT ' 1"\\\"\\\"k+\"jj\"nvh 2 test1.' str FROM dual) P -----------------"\\\"\\\"k+\"jj\"
10. Создать регулярное выражение для проверки сложности пароля. Пароль не должен содержать три последовательные буквы латинского алфавита.
Первоначальный вариант (случай, когда последовательные идут подряд): WITH alph AS (SELECT 'abcdefghijklmnopqrstuvwxyz' s from dual),str AS (SELECT 'pxyassword' st FROM dual)SELECT DECODE (count(*), 0, 'Последовательных нет', 'Последовательные есть') "Сложность"FROM alph, strWHERE regexp_substr(st, substr(s, level, 3)) IS NOT nullCONNECT BY level < 27 Берутся тройки из алфавита, и ищутся в строке. Сложность ---------------------Последовательных нет str AS (SELECT 'passwordabc' st FROM dual) Сложность ---------------------Последовательные есть Вариант два (наличие трех последовательных букв алфавита вообще в строке): WITH alph AS (SELECT 'abcdefghijklmnopqrstuvwxyz' s from dual),str AS (SELECT 'pxyzassword' st FROM dual)SELECT DECODE (count(*), 0, 'Последовательных нет', 'Последовательные есть') "Сложность"FROM alph, strWHERE regexp_substr(st, substr(s, level, 1)) IS NOT nullAND regexp_substr(st, substr(s, level + 1, 1)) IS NOT nullAND regexp_substr(st, substr(s, level + 2, 1)) IS NOT nullCONNECT BY level < 25 Отдельно берутся три последовательных символа из алфавита, и ищутся совпадения в строке Сложность ---------------------Последовательные есть str AS (SELECT 'pasesbwford' st FROM dual) Сложность ---------------------Последовательные есть str AS (SELECT 'pasesbword' st FROM dual) Сложность ---------------------Последовательных нет
11. Создать запрос для разделения "задвоенных" данных. Например, из
| CODE_OPERATION | ID_CLIENT |
| 1000 1100 | 841000 841100 |
сделать
| RN | CNT | CODE_OPERATION | ID_CLIENT |
| 1000 1100 | 841000 841100 | ||
CREATE TABLE doubledata (code_operation varchar2(50), id_client varchar2(50));INSERT INTO doubledata VALUES ('1000 1100', '841000 841100');INSERT INTO doubledata VALUES ('10110 761100', '451000 21841100');INSERT INTO doubledata VALUES ('1000 116600', '84104 94121');INSERT INTO doubledata VALUES ('11 100', '841404 121'); COMMIT; SELECT rn, cnt, code_operation, id_client FROM (SELECT code_operation, id_client, rownum rn, level - 1 cntFROM doubledataCONNECT BY level = 0UNION ALLSELECT SUBSTR(code_operation, 0, INSTR(code_operation,' ', 1)), SUBSTR(id_client, 0, INSTR(id_client,' ', 1)), rownum rn, level cntFROM doubledataCONNECT BY level = 0UNION ALLSELECT SUBSTR(code_operation, INSTR(code_operation,' ', 1), LENGTH(id_client)),SUBSTR(id_client, INSTR(id_client,' ', 1), LENGTH(id_client)), rownum rn, level + 1 cntFROM doubledataCONNECT BY level = 0)ORDER BY rn, cnt В подзапросе вырезаем разные участки «задвоенных» данных. В итоге получается оригинальная запись, первая и вторая части этой записи, а также нумерация. Все это объединяется (union all) и выводится в нужном нам порядке RN CNT CODE_OPERATION ID_CLIENT -- --- -------------------------------------------------- ---------------------- 1 0 1000 1100 841000 841100 1 1 1000 841000 1 2 1100 841100 2 0 10110 761100 451000 21841100 2 1 10110 451000 2 2 761100 21841100 3 0 1000 116600 84104 94121 3 1 1000 84104 3 2 116600 94121 4 0 11 100 841404 121 4 1 11 841404 4 2 100 121 12 rows selected
12. Создать запрос для получения информации о фамилиях преподавателей, которые не имеют подчиненных.
SELECT фамилия FROM(SELECT номер_преподавателя номер FROM преподавателиMINUSSELECT DISTINCT подчиняется номер FROM преподаватели)INNER JOIN преподаватели ON номер = преподаватели.номер_преподавателя Первым селектом вытащим всех, вторым - мало-мальских начальников. Нехитрой работой со множествами выходит, что если из всех работников вычесть начальников, останутся не начальники. ФАМИЛИЯ --------------------Казанко Позднякова Студейкин Тарасова В случае, если под информацией о фамилиях имелась в виду вся информация о найденных сотрудниках, то меняем в первом select-е “фамилия” на “*”, получаем всю информацию.
13. Определить ограничения, наложенные на таблицы схемы HR, представив их в виде:
| Таблица | Имя ограничения 1 | Тип ограничения 1 | Имя ограничения 2 | Тип ограничения 2 | Общее количество ограничений |
| Employees | Sys_yyy_123 | Первичный ключ | Sys_yyy_17 | Ограничение типа CHECK | |
14. Создать запрос для получения списка преподавателей и их должностей. В списке сначала должны идти преподаватели, не имеющие начальников. Их фамилии должны быть выведены заглавными буквами. Далее должны идти фамилии остальных сотрудников с отступом от левого края на две позиции.
SELECT ф "Фамилия", д "Должность"FROM (SELECT номер_преподавателя, UPPER(фамилия) ф, должность дFROM преподавателиWHERE подчиняется IS null UNION ALL (SELECT номер_преподавателя, ' '||фамилия ф, должность дFROM преподавателиMINUSSELECT номер_преподавателя, ' '||фамилия ф, должность дFROM преподавателиWHERE подчиняется IS null)) Сначала найти тех, у кого нет начальников, потом всех остальных, и провести с ними необходимые нехитрые манипуляции Фамилия Должность ---------------------- --------------------КОСТЫРКИН Профессор АБДУЛОВ Доцент Викулина Доцент Казанко Преподаватель Позднякова Преподаватель Загарийчук Ассистент Соколов Ассистент Студейкин Доцент Тарасова Преподаватель 9 rows selected
15. Написать запрос к таблице EMPLOYEES, который вернёт зарплату сотрудника с указанным номером (EMPLOYEE_ID), а в случае отсутствия сотрудника с указанным номером - вернёт 0 (ноль).
UNDEFINE empid; SELECT salaryFROM (SELECT CASE WHEN '&&empid' IN (SELECT employee_id FROM employees) THEN (SELECT salary FROM employees WHERE employee_id = &&empid) ELSE 0 END salaryFROM employees)WHERE rownum < 2 Проверяется наличие указанного идентификатора работника в таблице(employees). В случае успеха выводим его зарплату. Иначе 0. Тк значения выводятся для каждой записи в таблице, ограничим вывод одной строкой (rownum < 2) empid: 90 SALARY------ 0 empid: 110 SALARY------ 820016. Определить список последовательностей подчиненности от преподавателей, не имеющих начальника, до преподавателей, не имеющих подчиненных. Результат представить в виде:
Костыркин-> Викулина-> Студейкин->Соколов->Казанко (не имеет подчиненных) ……………………………………………………. SELECT "Path" FROM (SELECT * FROM (SELECT LTRIM(SYS_CONNECT_BY_PATH(фамилия, '-> '), '-> ')||'(не имеет подчиненных)' "Path", номер_преподавателя FROM преподаватели START WITH номер_преподавателя IN (SELECT номер_преподавателя FROM преподаватели WHERE подчиняется is null) CONNECT BY PRIOR номер_преподавателя = подчиняется) NATURAL JOIN (SELECT номер_преподавателя FROM преподаватели MINUS SELECT DISTINCT подчиняется номер FROM преподаватели)) Найдем пути подчиненности в подзапросе. Там будут промежуточные, которые уберем, натурально соединив(natural join) с таблицей сотрудников без подчиненных Path --------------------------------------------------------------------------------Костыркин-> Викулина-> Соколов-> Казанко(не имеет подчиненных) Абдулов-> Студейкин(не имеет подчиненных) Абдулов-> Загарийчук-> Позднякова(не имеет подчиненных) Абдулов-> Тарасова(не имеет подчиненных)17. Создать запрос, который выводит фамилию преподавателя, зарплату и процент, который составляет его зарплата от суммарной зарплаты преподавателей кафедры, на которой он работает (с использованием аналитических функций).
SELECT фамилия, зарплата, ROUND(RATIO_TO_REPORT(зарплата) OVER (PARTITION BY кафедра), 2)*100||'%' процентFROM преподаватели Основная сложность - в поиске необходимой функции. В нашем случае очень удачно выполняет свою работу аналитика (ratio_to_report) от оракла. Округления счел нужным делать до целых. ФАМИЛИЯ ЗАРПЛАТА ПРОЦЕНТ -------------------- -------- -----------------------------------------Казанко 2000 19% Соколов 1500 14% Викулина 3000 29% Костыркин 4000 38% Позднякова 2500 21% Загарийчук 2000 17% Студейкин 2500 21% Тарасова 2000 17% Абдулов 3000 25% 9 rows selected
18. Создать запрос для получения информации о количестве полученных студентами пятерок, четверок, троек и двоек по дням недели. Результат запроса должен быть представлен в виде:
| Кол-во 5 | Кол-во 4 | Кол-во 3 | Кол-во 2 | Итого | |
| Понедельник | |||||
| Вторник | |||||
| Среда | |||||
| ……. | |||||
| Итого: |
WITH mark AS (SELECT sk k, SUBSTR(t, 0, INSTR(t,'.', 1) - 1) da,SUBSTR(t, INSTR(t,'.', 1) + 1, LENGTH(t)) оценка FROM (SELECT SUM(co) sk, REPLACE(da,' ') ||'.'|| оценка t FROM (SELECT оценка, COUNT(оценка) co, TO_CHAR(дата, 'DAY') daFROM успеваемостьGROUP BY TO_CHAR(дата, 'DAY'), оценка UNIONSELECT * FROM (SELECT DISTINCT level l, n, dFROM (Select To_char(Sysdate + Level, 'DAY') d, 0 nFrom Dual Connect By Level <=7)Connect By Level <=5) WHERE l!= 1) GROUP BY replace(da,' ') ||'.'|| оценка)) SELECT " ", "Кол-во 5", "Кол-во 4", "Кол-во 3", "Кол-во 2", "Итого" FROM (SELECT DISTINCT 'ПОНЕДЕЛЬНИК' " ", 1 sor, (SELECT k FROM mark WHERE da = 'ПОНЕДЕЛЬНИК' AND оценка = 5) "Кол-во 5",(SELECT k FROM mark WHERE da = 'ПОНЕДЕЛЬНИК' AND оценка = 4) "Кол-во 4",(SELECT k FROM mark WHERE da = 'ПОНЕДЕЛЬНИК' AND оценка = 3) "Кол-во 3",(SELECT k FROM mark WHERE da = 'ПОНЕДЕЛЬНИК' AND оценка = 2) "Кол-во 2",(SELECT SUM(k) FROM mark WHERE da = 'ПОНЕДЕЛЬНИК') "Итого"FROM mark UNION SELECT DISTINCT 'ВТОРНИК', 2, (SELECT k FROM mark WHERE da = 'ВТОРНИК' AND оценка = 5),(SELECT k FROM mark WHERE da = 'ВТОРНИК' AND оценка = 4),(SELECT k FROM mark WHERE da = 'ВТОРНИК' AND оценка = 3),(SELECT k FROM mark WHERE da = 'ВТОРНИК' AND оценка = 2),(SELECT SUM(k) FROM mark WHERE da = 'ВТОРНИК')FROM mark UNIONSELECT DISTINCT 'СРЕДА', 3, (SELECT k FROM mark WHERE da = 'СРЕДА' AND оценка = 5),(SELECT k FROM mark WHERE da = 'СРЕДА' AND оценка = 4),(SELECT k FROM mark WHERE da = 'СРЕДА' AND оценка = 3),(SELECT k FROM mark WHERE da = 'СРЕДА' AND оценка = 2),(SELECT SUM(k) FROM mark WHERE da = 'СРЕДА')FROM mark UNIONSELECT DISTINCT 'ЧЕТВЕРГ', 4, (SELECT k FROM mark WHERE da = 'ЧЕТВЕРГ' AND оценка = 5),(SELECT k FROM mark WHERE da = 'ЧЕТВЕРГ' AND оценка = 4),(SELECT k FROM mark WHERE da = 'ЧЕТВЕРГ' AND оценка = 3),(SELECT k FROM mark WHERE da = 'ЧЕТВЕРГ' AND оценка = 2),(SELECT SUM(k) FROM mark WHERE da = 'ЧЕТВЕРГ')FROM mark UNIONSELECT DISTINCT 'ПЯТНИЦА', 5, (SELECT k FROM mark WHERE da = 'ПЯТНИЦА' AND оценка = 5),(SELECT k FROM mark WHERE da = 'ПЯТНИЦА' AND оценка = 4),(SELECT k FROM mark WHERE da = 'ПЯТНИЦА' AND оценка = 3),(SELECT k FROM mark WHERE da = 'ПЯТНИЦА' AND оценка = 2),(SELECT SUM(k) FROM mark WHERE da = 'ПЯТНИЦА')FROM mark UNIONSELECT DISTINCT 'СУББОТА', 6, (SELECT k FROM mark WHERE da = 'СУББОТА' AND оценка = 5),(SELECT k FROM mark WHERE da = 'СУББОТА' AND оценка = 4),(SELECT k FROM mark WHERE da = 'СУББОТА' AND оценка = 3),(SELECT k FROM mark WHERE da = 'СУББОТА' AND оценка = 2),(SELECT SUM(k) FROM mark WHERE da = 'СУББОТА')FROM mark UNIONSELECT DISTINCT 'ВОСКРЕСЕНЬЕ', 7, (SELECT k FROM mark WHERE da = 'ВОСКРЕСЕНЬЕ' AND оценка = 5),(SELECT k FROM mark WHERE da = 'ВОСКРЕСЕНЬЕ' AND оценка = 4),(SELECT k FROM mark WHERE da = 'ВОСКРЕСЕНЬЕ' AND оценка = 3),(SELECT k FROM mark WHERE da = 'ВОСКРЕСЕНЬЕ' AND оценка = 2),(SELECT SUM(k) FROM mark WHERE da = 'ВОСКРЕСЕНЬЕ')FROM mark UNIONSELECT DISTINCT 'итого:', 8, (SELECT SUM(k) FROM mark WHERE оценка = 5),(SELECT SUM(k) FROM mark WHERE оценка = 4),(SELECT SUM(k) FROM mark WHERE оценка = 3),(SELECT SUM(k) FROM mark WHERE оценка = 2),(SELECT SUM(k) FROM mark)FROM mark) ORDER BY sor В секции WITH создается таблица, в которой находится распределение количества оценок по дням недели (если таковых нет в какие-то дни, то они заменяются нулями). Затем в основной части последовательно извлекаются эти данные, формируясь в необходимом нам виде с помощью объединения строк (union) Кол-во 5 Кол-во 4 Кол-во 3 Кол-во 2 Итого----------- -------- -------- -------- -------- -----ПОНЕДЕЛЬНИК 0 0 0 0 0 ВТОРНИК 0 0 2 0 2 СРЕДА 0 0 0 0 0 ЧЕТВЕРГ 2 0 0 1 3 ПЯТНИЦА 0 1 0 0 1 СУББОТА 3 1 0 1 5 ВОСКРЕСЕНЬЕ 0 0 0 0 0 итого: 5 2 2 2 11 8 rows selected
19. Вывести список сотрудников, зарплату и нарастающую сумму зарплат в пределах отдела. Задачу решить без использования аналитических функций.
Случай, когда вместо отдела – кафедра. SELECT н номер, фам фамилия, зп зарплата, сум накопленнаяFROM (SELECT r, зп, пп.номер_преподавателя н, пп.фамилия фам, сум FROM (SELECT преподаватели.rowid r, преподаватели.зарплата зп, SUM(п.зарплата) сумFROM преподавателиCROSS JOIN преподаватели пWHERE п.rowid <= преподаватели.rowidAND п.кафедра = преподаватели.кафедраGROUP BY преподаватели.rowid, преподаватели.зарплата, преподаватели.кафедра, преподаватели.фамилияORDER BY преподаватели.кафедра, SUM(п.зарплата)) tINNER JOIN преподаватели пп ON пп.rowid = t.r) Соединяем таблицу с собой для получения всех значений (cross join). Сравнивая идентификаторы строки (rowid) и работая со столбцом зарплаты (sum), получим нарастающую зарплату НОМЕР ФАМИЛИЯ ЗАРПЛАТА НАКОПЛЕННАЯ----- -------------------- -------- ----------- 4002 Костыркин 4000 4000 4001 Викулина 3000 7000 4006 Соколов 1500 8500 4003 Казанко 2000 10500 4008 Абдулов 3000 3000 4007 Студейкин 2500 5500 4005 Загарийчук 2000 7500 4004 Позднякова 2500 10000 4009 Тарасова 2000 12000 9 rows selected Вариант для employees с дополнительным выводом отдела (для наглядности): SELECT н номер, фам фамилия, dep, зп зарплата, сум накопленнаяFROM (SELECT r, зп, пп.employee_id н, пп.last_name фам, пп.department_id dep, сум FROM (SELECT employees.rowid r, employees.salary зп, SUM(п.salary) сумFROM employeesCROSS JOIN employees пWHERE п.rowid <= employees.rowidAND (п.department_id = employees.department_id OR(п.department_id IS null ANDemployees.department_id IS null))GROUP BY employees.rowid,employees.salary, employees.department_id, employees.last_nameORDER BY employees.department_id, SUM(п.salary)) tright JOIN employees пп ON пп.rowid = t.r) НОМЕР ФАМИЛИЯ DEP ЗАРПЛАТА НАКОПЛЕННАЯ------ ------------------------- ---- -------- ----------- 200 Whalen 10 4400 4400 201 Hartstein 20 13000 13000 202 Fay 20 6000 19000 114 Raphaely 30 11000 11000 115 Khoo 30 3100 14100 116 Baida 30 2900 17000 117 Tobias 30 2800 19800 118 Himuro 30 2600 22400…
20. Написать команду для удаления одной из двух записей, которые отличаются лишь значениями в двух столбцах. При этом записи должны удовлетворять условиям:
R1.Col1 = R2.Col2 и R2.Col1 = R1.Col2 Решения для общего случая задачи не получилось
ВАРИАНТ 13
(Базы данных Студент и Human Resources)
1. Из таблицы Employees извлечь строки, в которых значения являются уникальными по всем столбцам.
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT * FROM employees WHERE
(first_name = e.first_name OR last_name = e.last_name
OR hire_date = e.hire_date OR job_id = e.job_id
OR salary = e.salary OR commission_pct = e.commission_pct
OR manager_id = e.manager_id OR department_id = e.department_id
OR phone_number = e.phone_number OR email = e.email)
AND employee_id!= e.employee_id);
0 rows selected.
Комментарий:
В итоге ни одна строка не будет выведена.
2. Имеется произвольная символьная строка. Надо найти сумму входящих в нее цифр. Если результат больше 10, то процесс продолжить - снова найти сумму цифр и тд. Пока не получится цифра.
Пример:
Строка t29h8n3m7l88g7999999999999
2+9+8+3+7+8+8+7+9+9+9+9+9+9+9+9+9+9+9+9+9=169
1+6+9=16
1+6=7
with t as (select 't29h8n3m7l88g79999999999999' str from dual)
select str,s
from t
model
partition by (rownum rn)
dimension by (1 d)
measures (str, regexp_replace(str,'\D') s)
rules iterate (1e6) until (length(s[1])=1)
(s[1] = substr(s[1],1,1)+substr(s[1],2));
t29h8n3m7l88g7999999999999 7
Комментарий:
Используя раздел MODEL, организуем цикл в 10^6 итераций, пока строка не станет размером в одно значение, т. е. пока не получим цифру, выделяем из строки только цифры и на каждом шаге складываем по две цифры, итоговая сумма и есть искомая цифра.
3. Создать запрос, который позволит оставить только одно из повторяющихся слов в тексте, идущих друг за другом. Количество повторяющихся слов – произвольное.
SELECT regexp_replace ('I I love oracle very very very very much much much much', '([[:alpha:]]+)[ ]+(\1[ ]+)*\1$|([[:alpha:]]+)[ ]+(\3[ ]+)+', '\1\3 ') AS Res
FROM dual;
I love oracle very much
4. Создать запрос для вывода трех самых высокооплачиваемых преподавателей, на кафедре, на которой они работают. Если какие-то преподаватели кафедры получает самую низкую зарплату из трех высооплачиваемых, они тоже должны попасть в список.
Select Фамилия, Кафедра, Зарплата, R
From (select Фамилия, Кафедра, Зарплата,
Rank() OVER (partition by Кафедра
Order by Зарплата Desc) R
From Преподаватели)
Where R<4;
Костыркин Кафедра 1 4000 1
Викулина Кафедра 1 3000 2
Казанко Кафедра 1 2000 3
Абдулов Кафедра 2 3000 1
Студейкин Кафедра 2 2500 2
Позднякова Кафедра 2 2500 2
Комментарий:
В запросе используется аналитическая функция Rank() с указанием раздела фрагментации.
5. Создать запрос для вывода данных по фамилиям преподавателей, работающих на той же должности, что и Викулина, но на другой кафедре.
select * from Преподаватели p
where p.Кафедра <> (select кафедра from преподаватели r where r.фамилия='Викулина')
and p.Должность = (select должность from преподаватели e where e.фамилия ='Викулина');
4008 Абдулов Семен Антонович 01.06.88 2-46-89-78 Доцент Кафедра 2
4007 Студейкин Андрей Андреевич 01.05.79 2-46-89-78 Доцент Кафедра 2
Комментарий:
Данный запрос является несложным примером использования подзапросов в разделе WHERE.
6. Определить сумму чисел в произвольной символьной строке. Например, для строки fgh123hhh76 результат должен быть 199.
WITH st AS (SELECT '56ren44sam99' str FROM dual)
Дата добавления: 2015-08-18; просмотров: 249 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| Main_Table PK User_Constraints 9 страница | | | Main_Table PK User_Constraints 11 страница |