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

Greene 9500 Zlotkey 10500

Greene 9500 Tucker 10000

Greene 9500 King 10000

Greene 9500 Vishney 10500

Greene 9500 Ozer 11500

Greene 9500 Bloom 10000

Greene 9500 Fox 9600

Greene 9500 Abel 11000

Ozer 11500 Russell 14000

Ozer 11500 Partners 13500

Ozer 11500 Errazuriz 12000

Bloom 10000 Russell 14000

Bloom 10000 Partners 13500

Bloom 10000 Errazuriz 12000

Bloom 10000 Cambrault 11000

Bloom 10000 Zlotkey 10500

Bloom 10000 Vishney 10500

Bloom 10000 Ozer 11500

Bloom 10000 Abel 11000

Fox 9600 Russell 14000

Fox 9600 Partners 13500

Fox 9600 Errazuriz 12000

Fox 9600 Cambrault 11000

Fox 9600 Zlotkey 10500

Fox 9600 Tucker 10000

Fox 9600 King 10000

Fox 9600 Vishney 10500

Fox 9600 Ozer 11500

Fox 9600 Bloom 10000

Fox 9600 Abel 11000

Abel 11000 Russell 14000

Abel 11000 Partners 13500

Abel 11000 Errazuriz 12000

Abel 11000 Ozer 11500

128 rows selected

7. Проверить каждый столбец таблицы Employees на уникальность значений и вывести все строки таблицы, в которых хотя бы в одном столбце встречается значение атрибута, которое в этом столбце не уникально (встречается несколько раз).

select * from employees

where last_name = any

(select last_name from

(select last_name, count(last_name) cou from employees –выборка неуникальных значений в lst_name и тд в других столбцах

group by last_name

order by last_name)

where cou>1) or first_name = any (select first_name from

(select first_name, count(first_name) cou from employees

group by first_name

order by first_name)

where cou>1) or employee_id = any (select employee_id from

(select employee_id, count(employee_id) cou from employees

group by employee_id

order by employee_id)

where cou>1) or email = any (select email from

(select email, count(email) cou from employees

group by email

order by email)

where cou>1) or phone_number = any (select phone_number from

(select phone_number, count(phone_number) cou from employees

group by phone_number

order by phone_number)

where cou>1) or hire_date = any (select hire_date from

(select hire_date, count(hire_date) cou from employees

group by hire_date

order by hire_date)

where cou>1) or job_id = any (select job_id from

(select job_id, count(job_id) cou from employees

group by job_id

order by job_id)

where cou>1) or salary = any (select salary from

(select salary, count(salary) cou from employees

group by salary

order by salary)

where cou>1) or COMMISSION_PCT = any (select COMMISSION_PCT from

(select COMMISSION_PCT, count(COMMISSION_PCT) cou from employees

group by COMMISSION_PCT

order by COMMISSION_PCT)

where cou>1) or MANAGER_ID = any (select MANAGER_ID from

(select MANAGER_ID, count(MANAGER_ID) cou from employees

group by MANAGER_ID

order by MANAGER_ID)

where cou>1) or DEPARTMENT_ID = any (select DEPARTMENT_ID from

(select DEPARTMENT_ID, count(DEPARTMENT_ID) cou from employees

group by DEPARTMENT_ID

order by DEPARTMENT_ID)

where cou>1)

8. Создать запрос для построения иерархии объектов "Специальность - Группа - Фамилия" для тех специальностей и групп, в которых есть студенты.

В результате вывести:

1) номер уровня, на котором находится в иерархии данный объект,

2) имя объекта, дополненное слева (LEVEL -1)*3 пробелами.

Объекты одного уровня должны быть отсортированы по именам.

Пример результата:

Уровень Иерархия

1 Системы автоматического управления

2 121

3 Поляков А.Л.

3 Старова Н.Л.

-- строим иерархию с помощью конструкции level, а так же нумеруем уровни

select level "Уровень", lpad(st1,length(st1) + (level-1)*3, ' ') "Иерархия"

-- добавляем еще одну колонку для того чтобы понять подчиненность

from (select st1, id, z

-- соединяем специальности, группы студентов в одну колонку для дальшейшей иерархии, а их id другую колонку

from (select специальности.название_специальности st1, специальности.код_специальности id

from студенты inner join группы

on студенты.номер_группы = группы.номер_группы

inner join специальности

on специальности.код_специальности = группы.код_специальности

union

select студенты.номер_группы, to_number(студенты.номер_группы)

from студенты inner join группы

on студенты.номер_группы = группы.номер_группы

inner join специальности

on специальности.код_специальности = группы.код_специальности

union

select студенты.фамилия, студенты.номер_студента

from студенты inner join группы

on студенты.номер_группы = группы.номер_группы

inner join специальности

on специальности.код_специальности = группы.код_специальности) t1

left join

(select номер_студента, to_number(студенты.номер_группы) z

from студенты

union

select to_number(группы.номер_группы), код_специальности

from группы) t2

on t1.id = t2.номер_студента) t

--указываем с чего начинать иерархию

start with z is null

connect by prior id = z

order siblings by st1

1 СИСТЕМЫ АВТОМАТИЧЕСКОГО УПРАВЛЕНИЯ

2 121

3 Гриценко

3 Поляков

3 Старова

2 122

3 Котенко

3 Нагорный

1 ЭКОНОМИКА ПРЕДПРИЯТИЙ

2 123

3 Усов

3 Устинов

2 124

3 Ежов

3 Улиткин

9. Выбрать сотрудников, имеющих оклад равный минимальному окладу сотрудников отдела, к которому они приписаны. Исключить из выборки сотрудников, которые не приписаны ни к какому отделу.

В результат вывести: Идентификатор сотрудника, Фамилию сотрудника, Оклад, установленный сотруднику, Идентификатор подразделения, к которому приписан сотрудник.

Результат отсортировать по возрастанию по полям: идентификатор подразделения, к которому приписан сотрудник; оклад, установленный сотруднику; фамилия сотрудника.

select employee_id,last_name,salary,department_id

from employees

where (department_id,salary) in

(select department_id,min(salary)—условие выборки что зарплата будет минимальная в отделе

from employees

group by department_id) and department_id is not null

order by department_id,salary,last_name

EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID

----------- ------------------------- -------- -------------

200 Whalen 4400 10

202 Fay 6000 20

119 Colmenares 2500 30

203 Mavris 6500 40

132 Olson 2100 50

107 Lorentz 4200 60

204 Baer 10000 70

173 Kumar 6100 80

102 De Haan 17000 90

101 Kochhar 17000 90

113 Popp 6900 100

206 Gietz 8300 110

12 rows selected

10. Создать запрос для определения количества сотрудников в каждом отделе, которые никем не руководят. В результат вывести:

· столбец 1: идентификатор подразделения компании, к которому приписан сотрудник,

· столбец 2: количество сотрудников подразделения, которые никем не руководят.

В последней дополнительной строке результата

· столбец 1: слово "ВСЕГО",

· столбец 2: общее количество сотрудников компании, которые никем не руководят.

Результат отсортировать по возрастанию по полю идентификатора отдела, к которому приписан сотрудник.

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

Пример вывода (значения получены на другом наборе данных):

DEPT	CNT
...	...
ВСЕГО

select case when (grouping(department_id)=1) then 'ВСЕГО' else to_char(department_id) end dept, count(employee_id) cnt

from employees

where employee_id not in

(select manager_id from employees--условие для не руководящих

where manager_id is not null)

group by rollup (department_id)

order by grouping(department_id), department_id nulls first; -- сперва группа с нулевыми департаментами, группы по департаментам

DEPT CNT

---------------------------------------- ---

10 1

20 1

30 5

40 1

50 40

60 4

70 1

80 29

100 5

110 1

ВСЕГО 89

12 rows selected

11. Выбрать сотрудников, для которых в таблице истории занятия должностей зафиксированы переходы на другую должность и отсутствуют сведения об их первой должности - той, которую они занимали при приеме на работу в компанию.

В результат вывести: Идентификатор сотрудника; Фамилию сотрудника; Дату, когда сотрудник был принят на работу; Идентификатор должности, которую занимает сотрудник в данный момент; Идентификатор подразделения, к которому в данный момент приписан сотрудник.

Результат отсортировать по возрастанию по полям: дата, когда сотрудник был принят на работу; фамилия сотрудника.

SELECT e.employee_id, e.last_name, e.hire_date, e.job_id, e.department_id

FROM employees e

WHERE e.hire_date < (SELECT MIN(j.start_date) –условие для отбора записей, в результате минимальная стартовая дата оказывается больше даты найма на работу-сл-но нет информации о первой должности

FROM job_history j

WHERE e.employee_id = j.employee_id

GROUP BY employee_id)

ORDER BY e.hire_date, e.last_name;

EMPLOYEE_ID LAST_NAME HIRE_DATE JOB_ID DEPARTMENT_ID

----------- ------------------------- --------- ---------- -------------

114 Raphaely 07.12.94 PU_MAN 30

122 Kaufling 01.05.95 ST_MAN 50

12. Создать запрос для вывода ФИО студентов и названий дисциплин, которые им надо изучить по учебному плану. Названия дисциплин должны быть представлены списком с разделителем в виде запятой и отсортированы по алфавиту.

select distinct фамилия, имя, отчество, WM_CONCAT(название) KEEP (DENSE_RANK FIRST ORDER BY фамилия) OVER (PARTITION BY фамилия) предмет --запоминание в массив предметов и отделение их запятой

from (select s.фамилия,s.имя,s.отчество,q.название

from студенты s join группы p on (s.номер_группы=p.номер_группы) join учебные_планы d on (p.код_специальности=d.код_специальности) join

дисциплины q on (d.номер_дисциплины=q.номер_дисциплины)--таблица с фамилиями и соответствующими для них планами

order by s.фамилия, q.название asc)

ФАМИЛИЯ ИМЯ ОТЧЕСТВО ПРЕДМЕТ

Старова Любовь Михайловна Математика,Физика

Устинов Владимир Викторович Математика,Менеджмент,Экономика

Усов Валерий Харитонович Математика,Менеджмент,Экономика

Котенко Анатолий Николаевич Математика,Физика

Гриценко Владимир Николаевич Математика,Физика

Поляков Анатолий Алексеевич Математика,Физика

Нагорный Евгений Васильевич Математика,Физика

Улиткин Андрей Сергеевич Математика,Менеджмент,Экономика

Ежов Алексей Павлович Математика,Менеджмент,Экономика

9 rows selected

13. Из таблицы вида:

ID	DES	T
A	a1
A	a2
A	a3
B	a1
B	a2
C	a3

получить таблицу:

I	a1	a2	a3
C
B
A

select t.id i,nvl(p.t,0) a1,nvl(q.t,0) a2,nvl(s.t,0) a3

from (select distinct id from zadanie order by id asc) t left join (select id,t

from zadanie

where DES='a1' order by id asc) p on (t.id=p.id) left join (select id,t--получение результирующей таблицы путем соединений строк с разными des

from zadanie

where DES='a2' order by id asc) q on (t.id=q.id) left join (select id,t

from zadanie

where DES='a3' order by id asc) s on (t.id=s.id)

order by t.id desc

14. Покажите в одном отчете для всех отделов: его номер, наименование, количество работающих сотрудников, средний оклад вместе со следующими данными по каждому сотруднику – фамилия, оклад и должность.

select dept_id "Номер отдела", dept_name "Название отдела",cnt "Количество сотрудников",

avg_sal "Средний оклад",lname Фамилия,sal Оклад,title Должность

from

-- группируем соответственно отделы отдельно, сотрудников отдельно

(select s.dept_id,s.dept_name,s.cnt,s.avg_sal,s.lname,sal,s.title, s.emp_dept

-- выводим непосресдственно все необходимые данные для каждого отдела, включая сотрудников в этом отделе

from (select d.department_id dept_id, d.department_name dept_name, count(*) over(partition by d.department_id) cnt,

avg(e.salary) over(partition by d.department_id) avg_sal,

e.last_name lname, e.department_id emp_dept, e.salary sal, j.job_title title

from employees e, departments d, jobs j

where e.department_id=d.department_id and e.job_id=j.job_id

group by d.department_id, d.department_name, e.last_name, e.department_id, e.salary, j.job_title) s

group by grouping sets((dept_id,dept_name,cnt,avg_sal),(lname,sal,title, emp_dept)))

-- упорядочиваем сотрудников относительно отделов

order by (case when dept_id is not null then dept_id else emp_dept end), dept_id;

15. Имеется таблица с набором чисел, например:

ID

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

select col1

from (

select col1,

row_number() over(order by abs(col1 - &your_number)) rown--каждой строке раздаем роунамы минимальный будет у меньшего значения разности по модулю числа в строке и введенного числа

from zadanie)

where rown=1;

16. Имеется таблица LCKS с двумя столбцами - Lcker и Lcked, каждая запись в которой связывает 2 объекта - заблокированного (Lcked) и того, кто его заблокировал (Lcker). Необходимо отобразить SQL-запросом иерархию заблокированных объектов, включая те объекты, которые являются причинами всех блокировок. Уровни иерархии выделять отступами от левого края состоящими из пробелов - по одному на каждый новый уровень.

Пример таблицы LCKS:

Lcker Lcked

A B

A C

B D

D E

C F

C J

C H

Надо вывести:

A

B

D

E

C

F

H

J

CREATE TABLE LCKS

(Lcker VARCHAR2(1),

Lcked VARCHAR2(1));

INSERT INTO LCKS VALUES('A', 'B');

INSERT INTO LCKS VALUES('A', 'C');

INSERT INTO LCKS VALUES('B', 'D');

INSERT INTO LCKS VALUES('D', 'E');

INSERT INTO LCKS VALUES('C', 'F');

INSERT INTO LCKS VALUES('C', 'J');

INSERT INTO LCKS VALUES('C', 'H');

WITH

t AS (

SELECT lcker, rownum r FROM (SELECT lcker

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;

A

B

D

E

C

F

H

J

17. Написать запрос к таблице EMPLOYEES, который вернёт зарплату сотрудника с заданным номером (EMPLOYEE_ID), а в случае отсутствия сотрудника с указанным номером - вернёт 0 (ноль).

select case

when salary is null then 0--если нет сотрудника то 0 иначе зарплата

else salary

end x

from (select &x x from dual) t left join employees-- таблица для case

on t.x = employees.employee_id

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 результирующей таблицы, должны присутствовать значения столбца из предыдущей строки.

select dat, nvl(val,lag(val) over (order by dat)) max_val

from d_v

19. Билеты для проезда в городском транспорте имеют шестизначный десятичный номер и буквенно-цифровую серию. Нумерация каждой серии билетов начинается с 000001.

Существует примета, что билет, у которого сумма трех первых цифр равна сумме трёх последних цифр - это "Счастливый билет".

Одной командой SELECT вывести количество "счастливых билетов" в каждой серии (одно число).

with t as (

select to_char(level,'fm000000') ticket from dual connect by level < 1000000 --генерация в верном формате

)

select count(*) lucky_ticket_count

FROM t

WHERE substr(ticket,1,1) + substr(ticket,2,1) + substr(ticket,3,1) = substr(ticket,4,1) + substr(ticket,5,1) + substr(ticket,6)

LUCKY_TICKET_COUNT

------------------

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'.

SELECT col1 FROM zadanie WHERE col1='BY'

UNION ALL

SELECT col2 FROM zadanie WHERE col1='BY'

UNION ALL

SELECT col1 FROM zadanie

CONNECT BY NOCYCLE col1=Prior col2 AND col2<>'BY''BY' --сравнение пока col1 равно такому же значению благодаря prior сol2 из другой строки col1 langcode col2 nextlang

START WITH col1=(SELECT col2 FROM zadanie WHERE col1=(SELECT col2 FROM zadanie WHERE col1='BY'));--старт с rus

COL1

-----------

BY

UA

RUS

ENG

ВАРИАНТ 16

(Базы данных Студент и Human Resources)

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

SELECT фамилия, кафедра, зарплата,(SELECT SUM(зарплата)

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

WHERE e.кафедра = emp.кафедра

AND e.фамилия <= emp.фамилия) сумма

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

ORDER BY кафедра, фамилия;

Результат:

ФАМИЛИЯ	КАФЕДРА	ЗАРПЛАТА	СУММА
Викулина	Кафедра 1
Казанко	Кафедра 1
Костыркин	Кафедра 1
Соколов	Кафедра 1
Абдулов	Кафедра 2
Загарийчук	Кафедра 2
Позднякова	Кафедра 2
Студейкин	Кафедра 2

9 rows selected.

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

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

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

SELECT *

FROM(SELECT department_name, location_id, dept_sum, AVG(dept_sum) OVER (PARTITION BY location_id) avg_city

FROM (SELECT DISTINCT SUM(salary) OVER (PARTITION BY department_id) dept_sum, department_id

FROM employees

ORDER BY department_id) s

JOIN departments d ON s.department_id=d.department_id)

WHERE (dept_sum > avg_city);

Результат:

DEPARTMENT_NAME	LOCATION_ID	DEPT_SUM	AVG_CITY
Finance
Executive

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

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

Затем, на основании полученной выборки, высчитывается среднее значение суммарных зарплат департаментов, расположенных в одном и том же городе.

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

23. В произвольной строке удалить хвостовые и лидирующие пробелы, а также все лишние пробелы между словами (оставить только по одному). Задачу решить при помощи раздела Model.

WITH t AS

(SELECT str, ROWNUM num

FROM(SELECT DISTINCT str

FROM(SELECT '&str' str FROM dual)

MODEL

DIMENSION BY (0 d)

MEASURES (str)

RULES ITERATE (500) UNTIL (ITERATION_NUMBER = LENGTH(str[0]))

(str[ITERATION_NUMBER+1] = REPLACE(str[ITERATION_NUMBER], ' ', ' '))))

SELECT TRIM(BOTH ' ' FROM str) str

FROM t

WHERE num = (SELECT MAX(num) FROM t);

Результат:

На вход подаем строку ‘ dfh hjk f fr ’.

STR

dfh hjk f fr

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

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

24. Создать таблицу следующего вида:

Номер	Оценка

Создать запрос для получения прогноза оценки на пяти шагах. Прогноз оценки на следующем шаге рассчитывается, как сумма оценок на двух предыдущих шагах. Результат представить в виде:

Номер	Прогноз оценки на пятом шаге	Список оценок по шагам
		2,4,6,10,16
		2,5,7,12,19
		1,4,5,9,14
		1,5,6,11,17

WITH t AS

(SELECT '7369' Номер, '2' Оценка FROM dual

UNION ALL

SELECT '7369' Номер, '4' Оценка FROM dual

UNION ALL

SELECT '7499' Номер, '2' Оценка FROM dual

UNION ALL

SELECT '7499' Номер, '5' Оценка FROM dual

UNION ALL

SELECT '7521' Номер, '1' Оценка FROM dual

UNION ALL

SELECT '7521' Номер, '4' Оценка FROM dual

UNION ALL

SELECT '7566' Номер, '1' Оценка FROM dual

UNION ALL

SELECT '7566' Номер, '5' Оценка FROM dual

),

one AS (

SELECT номер, оценка, rownum rn

FROM t),

two AS (

SELECT t1.номер num, t2.оценка o1, t1.оценка o2

FROM one t1 JOIN (SELECT номер, оценка

FROM one

WHERE MOD(rn,2)=1) t2 ON t1.номер = t2.номер

WHERE MOD(rn,2)=0

),

three AS (

SELECT num, o1,

o2,

(o1+o2) o3,

(o1+o2+o2) o4,

(o1+o2+o2+o1+o2) o5

FROM two

)

SELECT num "Номер", o5 "Прогноз оценки на пятом шаге", o1 || ',' || o2 || ',' || o3 || ',' || o4 || ',' || o5 "Список оценок по шагам"

FROM three;

Результат:

Номер	Прогноз оценки на пятом шаге	Список оценок по шагам
		2,4,6,10,16
		2,5,7,12,19
		1,4,5,9,14
		1,5,6,11,17

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

В подзапросе one пронумеруем строки исходной таблицы. В подзапросе two выберем из таблицы, полученной в результате выполнения one оценки, причем те оценки, которые находятся на нечетных строках таблицы, будем помещать в один столбец, а те, которые находятся на четных строках таблицы, - во второй. Последовательно складывая оценки, содержащиеся в этих двух столбцах, получим прогноз оценки на пятом шаге o5, а также все предыдущие прогнозы.

25. Создать регулярное выражение для проверки сложности пароля. Пароль не должен содержать три последовательные буквы латинского алфавита.

WITH t AS (

SELECT lower('&pass') pass, 'abcdefghijklmnopqrstuvwxyz' str FROM dual),

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