Main_Table PK User_Constraints 15 страница

mid AS (

SELECT pass, regexp_substr(pass, substr(str, level, 1)|| '.*'|| substr(str, level+1, 1)|| '.*'||substr(str, level+2, 1)) a,

NVL(regexp_count(pass, regexp_substr(pass, substr(str, level, 1)|| '.*'|| substr(str, level+1, 1)|| '.*'||substr(str, level+2, 1))), 0) x,

NVL(regexp_count(pass, regexp_substr(pass, substr(str, level, 1)|| '.*'|| substr(str, level+2, 1)|| '.*'||substr(str, level+1, 1))), 0) y,

NVL(regexp_count(pass, regexp_substr(pass, substr(str, level+1, 1)|| '.*'|| substr(str, level, 1)|| '.*'||substr(str, level+2, 1))), 0) z,

NVL(regexp_count(pass, regexp_substr(pass, substr(str, level+1, 1)|| '.*'|| substr(str, level+2, 1)|| '.*'||substr(str, level, 1))), 0) xx,

NVL(regexp_count(pass, regexp_substr(pass, substr(str, level+2, 1)|| '.*'|| substr(str, level, 1)|| '.*'||substr(str, level+1, 1))), 0) yy,

NVL(regexp_count(pass, regexp_substr(pass, substr(str, level+2, 1)|| '.*'|| substr(str, level+1, 1)|| '.*'||substr(str, level, 1))), 0) zz

FROM t

CONNECT BY LEVEL <= 24)

SELECT pass, CASE WHEN (SUM(x)=0 AND SUM(y)=0 AND SUM(z)=0 AND SUM(xx)=0 AND SUM(yy)=0 AND SUM(zz)=0) THEN 'Пароль надежен' ELSE 'Пароль не надежен' END Пароль

FROM mid

GROUP BY pass;

Результат:

Если на вход подаем строку ‘hnvrevrmNorefe’, то пароль будет признан ненадежным:

PASS	ПАРОЛЬ
hnvrevrmNorefe	Пароль не надежен

А если подать на вход строку ‘e67868n67868v’, то получим следующее:

PASS	ПАРОЛЬ
e67868n67868v	Пароль надежен

А если подать на вход строку ‘c45b545a’, то получим следующее:

PASS	ПАРОЛЬ
c45b545a	Пароль не надежен

Комментарий:

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

26. Имеем таблицу:

Номер	Категория	Продукт
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 номер AS "Номер", last_value(категория ignore nulls) over (order by номер) AS "Категория",

last_value(продукт ignore nulls) over (order by номер) AS "Продукт"

FROM fruits;

Результат:

таблица fruits:

Номер	Категория	Продукт
	Категория 1	Mango
		Banana
	Категория 2	Vanilla
		Strawberry

9 rows selected.

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

Номер	Категория	Продукт
	Категория 1	Mango
	Категория 1	Mango
	Категория 1	Mango
	Категория 1	Banana
	Категория 1	Banana
	Категория 1	Banana
	Категория 2	Vanilla
	Категория 2	Vanilla
	Категория 2	Strawberry

9 rows selected.

Комментарий:

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

27. Построить запрос для плотного ранжирования зарплат по отделам сотрудников с использованием аналитических функций.

SELECT last_name, department_id, salary,

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rank

FROM employees

ORDER BY department_id;

Результат:

LAST_NAME	DEPARTMENT_ID	SALARY	RANK
Pataballa
Lorentz
Baer
Russell
Partners
Errazuriz
Ozer
Abel
Cambrault
Zlotkey

107 rows selected.

Комментарий:

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

28. Построить запрос для неплотного ранжирования зарплат по отделам сотрудников без использования аналитических функций.

SELECT t1.last_name, t1.department_id, t1.salary, COUNT(t2.salary)+1 rank

FROM employees t1 LEFT JOIN employees t2

ON t1.department_id=t2.department_id AND t1.salary<t2.salary

WHERE t1.department_id IS NOT NULL

GROUP BY t1.last_name, t1.department_id, t1.salary

ORDER BY t1.department_id, t1.salary DESC;

Результат:

LAST_NAME	DEPARTMENT_ID	SALARY	RANK
Pataballa
Lorentz
Baer
Russell
Partners
Errazuriz
Ozer
Abel
Cambrault
Zlotkey

107 rows selected.

Комментарий:

Для всех работников считаем, у скольких их коллег по департаменту зарплата больше.

Чем меньше таких людей, тем выше ранг работника.

29. Создать запрос для определения списка сотрудников, которые имеют зарплату, с рангом от 3 до 5 в отделе, в котором они работают.

SELECT *

FROM (SELECT last_name, department_id, salary,

DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) rank

FROM employees

ORDER BY department_id)

WHERE rank BETWEEN 3 AND 5;

Результат:

LAST_NAME	DEPARTMENT_ID	SALARY	RANK
Mourgos
Austin
Pataballa
Lorentz
Errazuriz
Ozer
Cambrault
Abel
Chen

16 rows selected.

Комментарий:

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

30. Создать запрос для определения дат начала проведения Уимблдонского турнира в високосные годы второй половины века, зная, что турнир начинается за шесть недель до последнего понедельника августа. Век вводится как параметр.

SELECT Даты

FROM (

SELECT

TO_CHAR(NEXT_DAY(LAST_DAY('1.08.'||(TO_CHAR(TO_DATE(TO_CHAR('&&Век'-1 || '50', '9999'), 'YYYY'), 'YYYY')+level-1))-7, 'ПОНЕДЕЛЬНИК')-42,'dd.mm.yyyy') Даты,

LAST_DAY('1.02.'||(TO_CHAR(TO_DATE(TO_CHAR('&Век'-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 век, то получим следующее:

ДАТЫ

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.

Комментарий:

Для начала нам нужно выполнить переход от века к определенному году, дабы определить дату начала турнира. Зная, что к ХХ веку относятся года в диапазоне от 1900 до 1999, разумно предположить, что для того, чтобы получить 1950 год (нас интересует вторая половина задаваемого параметром года), достаточно из входного значения 20 вычесть 1 и добавить к концу полученного числа 19 недостающее ‘50’.

Теперь, превратив 1950 в дату, легко получить дату начала турнира. Сделав 50 шагов, повторив описанные выше операции для каждого года, мы будем знать даты начала турнира в каждом году на протяжении полувека.

Узнать, високосный ли год, мы можем, вырезав из поля feb год. Тогда нам удастся вычислить, на какое число приходится последний день февраля, если это 29ое, то год високосный, а значит, данные по этому году должны оставаться в результирующей выборке.

31. Создать внешнюю таблицу для чтения данных из текстового файла init.ora. Таблица должна содержать два столбца: Имя параметра и Значение. Директория Init. Файл для примера находится в папке \\FEANOR\Public2\КИТвП\Курс 3\Семестр 6\Базы данных и СУБД. Создать запрос к внешней таблице, который позволит найти общее количество файлов управления (Control_Files).

CREATE TABLE init_ex

(Имя_параметра VARCHAR2(32),

Значение VARCHAR2(256))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY Init

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

NOBADFILE

NOLOGFILE

FIELDS TERMINATED BY '=' LRTRIM

REJECT ROWS WITH ALL NULL FIELDS (Имя_параметра, Значение))

LOCATION ('init.ora'))

REJECT LIMIT UNLIMITED;

SELECT regexp_count(Значение,'[^,]+') Количество

FROM init_ex

WHERE Имя_параметра = 'control_files';

Результат:

КОЛИЧЕСТВО

Комментарий:

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

Для нахождения количества файлов управления посчитаем число групп символов, разделенных запятой в столбце «Значение» соответствующей строки.

32. Создать запрос для вывода всех дат приема на работу сотрудников, отсутствующих в некотором временном интервале дат.

SELECT hire_date

FROM employees

WHERE TO_DATE(TO_CHAR(hire_date, 'dd.mm.yyyy'))

NOT BETWEEN TO_DATE('&start_date', 'dd.mm.rr') AND TO_DATE('&end_date', 'dd.mm.rr')

ORDER BY hire_date;

Результат:

start_date = 01.01.1989

end_date = 01.04.2000

DATES

17.06.87

17.09.87

21.04.00

21.04.00

Комментарий:

Из таблицы employees выбираем все hire_date, лежащие вне указанного промежутка времени.

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

ID A1 72 123 31

Надо получить

ID Сумма A

1 7+12 192 7+31 383 12+31 43 4 7+12+31 50

Таблица может содержать любое число чисел. Задачу решить без использования Model.

WITH t AS (

SELECT '1' ID, '7' A FROM dual

UNION ALL

SELECT '2' ID, '12' A FROM dual

UNION ALL

SELECT '3' ID, '31' A FROM dual

),

one AS (

SELECT sys_connect_by_path(A,'+') AS c

FROM t

WHERE level>1

CONNECT BY PRIOR ID<ID

),

two AS (

SELECT LTRIM(c,'+') AS Сумма,

(SELECT SUM(A)

FROM t

WHERE c LIKE '%+'||A||'%') AS "A"

FROM one

ORDER BY A)

SELECT rownum AS ID, Сумма, A

FROM two;

Результат:

ID	СУММА	A
	7+12
	7+31
	12+31
	7+12+31

Комментарий:

В первом представлении one с помощью sys_connect_by_path создаем строки, содержащие значения столбца А исходной таблицы, разделенные символом '+'. Формирование строк начинаем не с нулевого, а первого уровня, так как нам необходимо, чтобы минимум два числа находилось в строке.

Во втором представлении two суммируем числа столбца А, проверяя, будет ли соответствующая строка столбца С представления one иметь вид '%+'||A||'%', то есть, содержится ли в ней складываемое число.

34. Создать запрос для получения сведений (Фамилия, Группа, Специальность, Отличник/Хорошист) обо всех отличниках в группе, а если таковых нет, то обо всех хорошистах. (Под отличниками понимаем студентов, сдавших все предусмотренные учебные планом экзамены на отлично с первого раза. Под хорошистами понимаем студентов, сдавших все предусмотренные учебные планом экзамены на отлично или хорошо с первого раза).

WITH fives AS(SELECT номер_студента, 'отличник' rating --получаем список отличниковFROM (SELECT DISTINCT номер_студента /*Выбираем только тех студентов, которые имеют среднюю оценку 5 за все экзамены, которые они сдавали. К сожалению, эти студенты не обязательно являются отличниками, так как они могли вообще не сдавать какие-то экзамены, находящиеся в их учебном плане, ведь в таблице "успеваемость" не содержатся сведения об экзаменах, к которым нет оценки.*/ FROM успеваемость HAVING AVG(оценка)=5 GROUP BY номер_студента MINUS SELECT номер_студента --получили тех, кто за какой-то экзамен так и не имеет оценки FROM (SELECT номер_студента, номер_дисциплины --выбрали всех студентов и все дисциплины по плану, которые должен сдавать каждый студент FROM студенты INNER JOIN группы USING (номер_группы) INNER JOIN учебные_планы USING (код_специальности) MINUS SELECT номер_студента, номер_дисциплины --выбрали тех, кто сдавал экзамены FROM успеваемость ORDER BY номер_студента, номер_дисциплины))), fours AS (SELECT номер_студента, 'хорошист' rating --получаем список хорошистовFROM (SELECT DISTINCT номер_студента FROM успеваемость MINUS SELECT номер_студента --выбрали тех, кто за какие-либо экзамены так и не смогли получмть оценку не ниже 4 FROM (SELECT номер_студента, номер_дисциплины --выбрали всех студентов и все дисциплины по плану, которые должен сдавать каждый студент FROM студенты INNER JOIN группы USING (номер_группы) INNER JOIN учебные_планы USING (код_специальности) MINUS SELECT номер_студента, номер_дисциплины --выбрали тех, кто за какой-либо экзамен с какого-либо раза получили хотя бы 4 FROM успеваемость WHERE оценка>=4 ORDER BY номер_студента, номер_дисциплины) /*В результате вычитания мы получили всех студентов, которые сдали все свои "плановые" экзамены, причем сдали их по крайней мере на 4. Но мы не знаем, с какого раза экзамен был успешно сдан, поэтому нам необходимо проверить, пересдавал ли каждый из полученных студентов экзамены. Косвенным подтверждением факта пересдачи экзамена будет являться запись в таблице "успеваемость" об экзамене, сданном хуже, чем на 4. И вот таких студентов, если они, конечно, имеются, мы и вычтем из текущего представления.*/ MINUS SELECT номер_студента --выбираем тех студентов, которые имеют в таблице "успеваемость" записи об экзаменах, сданных хуже, чем на 4 FROM успеваемость t1 WHERE номер_дисциплины IN (SELECT номер_дисциплины FROM студенты INNER JOIN группы USING (номер_группы) INNER JOIN учебные_планы USING (код_специальности) WHERE номер_студента=t1.номер_студента) AND оценка<4 MINUS --не забудем о том, что в нашу выборку попали и отличники, а они нам здесь не нужны SELECT номер_студента FROM fives)), res_fives AS (--для студентов из списка отличников узнаем их фамилии, номера групп и названия специальностейSELECT студенты.фамилия Фамилия, студенты.номер_группы Группа, sp.название_специальности Специальность, fives.rating "Отличник/Хорошист"FROM fives LEFT JOIN студенты ON (fives.номер_студента=студенты.номер_студента)LEFT JOIN группы gr ON студенты.номер_группы=gr.номер_группыLEFT JOIN специальности sp ON gr.код_специальности=sp.код_специальности), res_fours AS (--для студентов из списка хорошистов узнаем их фамилии, номера групп и названия специальностейSELECT студенты.фамилия Фамилия, студенты.номер_группы Группа, sp.название_специальности Специальность, fours.rating "Отличник/Хорошист"FROM fours LEFT JOIN студенты ON (fours.номер_студента=студенты.номер_студента)LEFT JOIN группы gr ON студенты.номер_группы=gr.номер_группыLEFT JOIN специальности sp ON gr.код_специальности=sp.код_специальности)SELECT Фамилия, Группа, Специальность, "Отличник/Хорошист" --основной запросFROM res_fives --сначала выберем всех отличниковUNION ALL SELECT * --прибавим к отличникам тех хорошистов, чьи группы не совпадают с группами отличниковFROM res_fours WHERE Группа NOT IN (SELECT DISTINCT Группа FROM res_fives) ORDER BY Группа;

Результат:

ФАМИЛИЯ	ГРУППА	СПЕЦИАЛЬНОСТЬ	Отличник/Хорошист
Старова		СИСТЕМЫ АВТОМАТИЧЕСКОГО УПРАВЛЕНИЯ	отличник

Комментарий:

Комментарии сделаны прямо в тексте запроса.

35. Создать запрос для определения сотрудников, которые имеют перерыв в стаже работы в фирме (Таблицы Employees и Job_History).

SELECT e.employee_id, e.last_name

FROM employees e JOIN

(SELECT DISTINCT first_value(start_date) OVER (PARTITION BY employee_id ORDER BY employee_id, start_date) st, employee_id

FROM job_history) t

ON t.employee_id = e.employee_id

WHERE e.hire_date!= t.st

UNION

SELECT j.employee_id, e.last_name

FROM (

SELECT j1.employee_id

FROM job_history j1

HAVING (MAX(end_date)-MIN(start_date)+1)!=(SELECT SUM(end_date-start_date+1)

FROM job_history j2

WHERE j1.employee_id=j2.employee_id)

GROUP BY j1.employee_id) j JOIN employees e ON j.employee_id = e.employee_id;

Результат:

EMPLOYEE_ID	LAST_NAME
	Raphaely
	Kaufling
	Whalen

Комментарий:

Решение задачи разбивается на два этапа.

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

На втором этапе сравниваемые величины должны быть равны у тех, кто работал без перерывов, то есть, должно быть равное количество дней. Если это не так, что такой сотрудник – именно тот, кто нам нужен.

36. Создать запрос для вывода информации о студентах, у которых интервал между датами сдачи экзаменов составляет менее трех дней. Информацию вывести в виде:

Фамилия	Дисциплина 1	Дата сдачи1	Дисциплина 2	Дата сдачи 2
Поляков	Математика	15.04.1998	Физика	17.04.1998

SELECT с.фамилия "Фамилия", д1.название "Дисциплина 1", у1.дата "Дата сдачи 1", д2.название "Дисциплина 2", у2.дата "Дата сдачи 2"

FROM Студенты с INNER JOIN Успеваемость у1 ON у1.номер_студента = с.номер_студента

INNER JOIN Успеваемость у2 ON у1.номер_студента = у2.номер_студента

INNER JOIN Дисциплины д1 ON у1.Номер_дисциплины = д1.Номер_дисциплины

INNER JOIN Дисциплины д2 ON у2.Номер_дисциплины = д2.Номер_дисциплины

WHERE (у2.Дата - у1.Дата) BETWEEN 0 AND 2 AND у1.номер!= у2.номер;

Результат:

Фамилия	Дисциплина 1	Дата сдачи 1	Дисциплина 2	Дата сдачи 2
Старова	Физика	10.06.99	Математика	12.06.99
Поляков	Физика	10.06.99	Математика	12.06.99

Комментарий:

Так как для каждого студента нам необходимо выдать по две даты сдачи экзаменов и по два наименования дисциплин, то произведем соединение с каждой из таблиц по два раза. После этого выберем те дисциплины, между датами сдачи которых прошло от 0 до 2 дней включительно. Но тогда стоит учесть, что нас не интересует вариант, когда под наше условие попадает один и тот же экзамен, отсюда и условие про неравенство номеров экзаменов.

37. Создать запрос для определения списка студентов, сдавших экзамены по дисциплинам, не предусмотренным учебным планом специальности, на которой учатся студенты.

SELECT DISTINCT номер_студента, фамилия || ' ' || Имя Студент

FROM Студенты NATURAL JOIN (SELECT DISTINCT Номер_студента

FROM Успеваемость

WHERE Успеваемость.Оценка > 2 AND Успеваемость.Номер_дисциплины NOT IN

(SELECT Номер_дисциплины

FROM Студенты NATURAL JOIN

Группы NATURAL JOIN Учебные_планы

WHERE Студенты.Номер_студента = Успеваемость.Номер_студента));

Результат:

НОМЕР_СТУДЕНТА	СТУДЕНТ
	Гриценко Владимир

Комментарий:

Задача решается с помощью связанного подзапроса. Мы выбираем тот экзамен студента, который был успешно сдан (больше, чем на 2) и который не присутствует в списке экзаменов, необходимых к сдаче по учебному плану. Этот список как раз и формируется для каждого студента в отдельности во внутреннем запросе.

38. Получить информацию о работе кафедр в виде:

	Кафедра 1	Кафедра 2	Итого
Количество преподавателей
Суммарный оклад
Средний оклад

SELECT 'Количество преподавателей' " ", sum (CASE кафедра WHEN 'Кафедра 1' THEN 1 ELSE 0 END) AS "Кафедра 1",

sum (CASE кафедра WHEN 'Кафедра 2' THEN 1 ELSE 0 END) AS "Кафедра 2", count (номер_преподавателя) AS "Итого"

FROM преподаватели

UNION

SELECT 'Суммарный оклад',

(SELECT SUM(зарплата)

FROM преподаватели

WHERE кафедра='Кафедра 1'),

(SELECT SUM(зарплата)

FROM преподаватели

WHERE кафедра='Кафедра 2'),

(SELECT SUM(зарплата)

FROM преподаватели)

FROM dual

UNION

SELECT 'Средний оклад',

(SELECT AVG(зарплата)

FROM преподаватели

WHERE кафедра='Кафедра 1'),

(SELECT AVG(зарплата)

FROM преподаватели

WHERE кафедра='Кафедра 2'),

(SELECT AVG(зарплата)

Дата добавления: 2015-08-18