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

COUNTRIES COUNTRY_ID LOCATIONS

DEPARTMENTS DEPARTMENT_ID, DEPARTMENT_ID EMPLOYEES, JOB_HISTORY

EMPLOYEES EMPLOYEE_ID, EMPLOYEE_ID, EMPLOYEE_ID DEPARTMENTS, EMPLOYEES, JOB_HISTORY

11 rows selected

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

SELECT upper(фамилия), должность

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

WHERE подчиняется IS NULL -- преподаватели, не имеющие начальников, БОЛЬШИМИ буквами

UNION ALL -- соединяем их

SELECT ' '|| фамилия, должность

FROM преподаватели -- остальные преподаватели с отступом в две позиции

WHERE подчиняется IS NOT NULL;

Результат:

Фамилия Должность

КОСТЫРКИН Профессор

АБДУЛОВ Доцент

Викулина Доцент

Соколов Ассистент

8 rows selected

10. Создать представление, которое выводит фамилию студента, оценку, дату сдачи экзамена и название предмета. Оценка должна выводиться в формате: отл, хор, удовл или неудовл. Для студентов, не сдававших экзамен, должно выводиться – Не сдавал – в столбцах Оценка, Дата сдачи экзамена и Название предмета.

SELECT фамилия,

(SELECT название

FROM дисциплины -- выводим название дисциплины вместо номера

WHERE номер_дисциплины = учебные_планы.номер_дисциплины) название,(

CASE -- выводим либо дату, либо «не сдавал», в зависимости от того, есть ли запись о данном студенте в таблице «успеваемость»

WHEN ((номер_студента, учебные_планы.номер_дисциплины) IN

(SELECT номер_студента, номер_дисциплины FROM успеваемость))

THEN (SELECT TO_CHAR(дата)

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

WHERE номер_дисциплины = учебные_планы.номер_дисциплины

AND номер_студента = студенты.номер_студента)

ELSE 'Не сдавал'

END) AS дата,

(CASE -- выводим либо оценку, либо «не сдавал»», в зависимости от того, есть ли запись о данном студенте в таблице «успеваемость»

WHEN ((номер_студента, учебные_планы.номер_дисциплины) IN

(SELECT номер_студента, номер_дисциплины FROM успеваемость))

THEN (SELECT DECODE (оценка, 5, 'Отл', 4, 'Хор', 3, 'Удовл', 2, 'Неудовл') --Переводим оценку в слово

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

WHERE номер_дисциплины = учебные_планы.номер_дисциплины

AND номер_студента = студенты.номер_студента)

ELSE 'Не сдавал' END) AS оценка

FROM студенты NATURAL

JOIN группы

RIGHT JOIN учебные_планы USING (код_специальности)

ORDER BY фамилия;

Результат:

Фамилия Название Дата Оценка

Гриценко Математика Не сдавал Не сдавал

Гриценко Физика 10.06.99 Неудовл

Ежов Экономика Не сдавал Не сдавал

Ежов Менеджмент Не сдавал Не сдавал

Ежов Математика Не сдавал Не сдавал

22 rows selected

11. Создать таблицы Кафедра1 и Кафедра2. Используя возможности многотабличной вставки, обеспечить вставку данных в созданные таблицы преподавателей из таблицы Преподаватели в соответствии с кафедрой, на которой они работают.

CREATE TABLE кафедра1 -- создаем таблицу кафедра1

(

номер_преподавателя NUMBER(5),

фамилия VARCHAR2(20),

имя VARCHAR2(10),

отчество VARCHAR2(15),

дата_контракта DATE,

номер_телефона VARCHAR2(15),

должность VARCHAR2(20),

подчиняется NUMBER(5),

зарплата NUMBER(7,2)

);

CREATE TABLE кафедра2 -- создаем таблицу кафедра2

(

номер_преподавателя NUMBER(5),

фамилия VARCHAR2(20),

имя VARCHAR2(10),

отчество VARCHAR2(15),

дата_контракта DATE,

номер_телефона VARCHAR2(15),

должность VARCHAR2(20),

подчиняется NUMBER(5),

зарплата NUMBER(7,2)

);

INSERT ALL

WHEN кафедра = 'Кафедра 1' THEN

INTO кафедра1 VALUES -- заполняем таблицу кафедра1 данными из таблицы Преподаватели

(

номер_преподавателя,

фамилия,

имя,

отчество,

дата_контракта,

номер_телефона,

должность,

подчиняется,

зарплата

)

WHEN кафедра = 'Кафедра 2' THEN

INTO кафедра2 VALUES -- заполняем таблицу кафедра2 данными из таблицы Преподаватели

(

номер_преподавателя,

фамилия,

имя,

отчество,

дата_контракта,

номер_телефона,

должность,

подчиняется,

зарплата

)

SELECT * FROM преподаватели;

commit;

Результат:

table КАФЕДРА1 created.

table КАФЕДРА2 created.

9 rows inserted.

commited.

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

	Кафедра1	Кафедра2	Итого
Профессор
Доцент
…….
Итого:

SELECT DISTINCT должность,

(SELECT COUNT (фамилия) FROM преподаватели

WHERE кафедра = 'Кафедра 1' -- выбираем в нужную колонку только людей с нужной кафедры

AND pr.должность = должность) AS "Кафедра 1",

(SELECT COUNT (фамилия) FROM преподаватели

WHERE кафедра = 'Кафедра 2' -- выбираем в нужную колонку только людей с нужной кафедры

AND pr.должность = должность) AS "Кафедра 2",

(SELECT COUNT (фамилия) FROM преподаватели

WHERE pr.должность = должность -- считаем всех с одинаковой должностью

) AS "Итого" FROM преподаватели pr

UNION ALL -- Присоединяем последнюю строчку "Итого"

SELECT 'Итого', (SELECT COUNT(фамилия) FROM преподаватели WHERE кафедра = 'Кафедра 1'),

(SELECT COUNT(фамилия) FROM преподаватели WHERE кафедра = 'Кафедра 2'),

(SELECT COUNT(фамилия) FROM преподаватели)

FROM dual;

Результат:

Должность Кафедра 1 Кафедра 2 Итого

Преподаватель 1 2 3

Профессор 1 0 1

Доцент 1 2 3

Ассистент 1 1 2

Итого 4 5 9

13. Создать запрос для определения стажа работы преподавателей. Результат вывести в формате ## лет ## мес #дней.

SELECT фамилия,

TRUNC(months_between(sysdate, дата_контракта) /12) || ' лет ' || -- Кол-во месяцев между датами делим на 12. Получаем кол-во лет.

MOD(trunc(months_between(sysdate, дата_контракта)), 12) || ' месяцев ' || --Берем остаток от деления кол-ва месяцев между датами и 12. Получаем кол-во месяцев.

ROUND(sysdate - add_months(дата_контракта, trunc(months_between(sysdate, дата_контракта)))) || ' дней' "Стаж работы" -- из текущей даты вычитаем дату контракта + кол-во месяцев между ними. Т.к. текущая дата точна до дней, а вторая дата точна до месяцев, то в итоге получаем нужное нам кол-во дней002E

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

ORDER BY фамилия;

Результат:

Фамилия Стаж работы

Абдулов 23 лет 11 месяцев 26 дней

Викулина 14 лет 1 месяцев 26 дней

Загарийчук 34 лет 8 месяцев 26 дней

Казанко 23 лет 8 месяцев 26 дней

9 rows selected

14. Требуется из заданного числа сформировать два новых выражения:

Input: 57896854742
Output 1: 5/789-685-4742
Output 2: [578|968|547|42]

SELECT regexp_replace('&&number','([[:digit:]]{1})([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]*)','\1/\2-\3-\4') AS output1, regexp_replace('&number','([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{1,3})([[:digit:]]*)','[\1|\2|\3|\4]') AS output2 FROM dual;

--С помощью регулярных выражений формируем два новых числа в заданном формате.

Результат:

для 57896854742:

OUTPUT1 OUTPUT2

5/789-685-4742 [578|968|547|42]

15. Увеличить вдвое зарплату преподавателям, которые подчиняются (непосредственно или опосредованно) Костыркину.

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

SET зарплата = зарплата*2 -- увеличиваем зарплату

WHERE фамилия IN

(SELECT фамилия

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

WHERE фамилия!= 'Костыркин' -- ищем фамилии тех, кто подчиняется Костыркину

START WITH фамилия = 'Костыркин'

CONNECT BY prior номер_преподавателя = подчиняется);

Результат:

3 rows updated

16. Создать запрос к таблице с числовым полем, который выводит числа (скажем, от 1 до 50000), которых в таблице нет.

SELECT level FROM dual

CONNECT BY level <=100 -- выводим все числа от 1 до 100

MINUS

SELECT department_id FROM departments; -- вычитаем номера департаментов

-- для примера, будем обращаться к полю department_id в таблице departments. Чуть ниже, результат запишем в строчку для экономии места.

Результат:

Level

1 2 3 4 5 6 7 8 9 11 12 13 14 15 16 17 18 19…

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

Номер	Категория	Продукт
1	Категория 1	Mango
2
3
4		Banana
5
6
7	Категория 2	Vanilla
8
9		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'); --создаем таблицу 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

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

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

SELECT фамилия, стипендия, concat(res*100,' %') AS "% от ст. специальности" -- с помощью фунцкии concat добавляем значок процента

FROM (SELECT фамилия, стипендия, g.код_специальности,

ROUND(стипендия/SUM(стипендия) over (partition BY g.код_специальности),3) AS res

FROM студенты s INNER JOIN группы g -- Во вложенном подзапросе считаем сумму по группе

ON s.номер_группы = g.номер_группы);

Результат:

Фамилия Стипендия % от ст. специальности

Поляков 200 21,1 %

Старова 250 26,3 %

Гриценко 300 31,6 %

9 rows selected

19. Создать запрос, позволяющий выводить информацию о студентах и преподавателях (Фамилия с инициалами, Зарплата/Стипендия, Статус – преподаватель или студент).

SELECT TO_CHAR(фамилия || ' ' || SUBSTR(имя,1,1) || '.' || SUBSTR(отчество,1,1) || '.') AS ФИО,

зарплата AS "Зарплата/стипендия", 'Преподаватель' AS "Статус" FROM преподаватели

UNION -- Объединяем преподавателей и студентов

SELECT TO_CHAR(фамилия || ' ' || SUBSTR(имя,1,1) || '.' || SUBSTR(отчество,1,1) || '.') AS ФИО,

стипендия AS "Зарплата/стипендия", 'Студент' AS "Статус" FROM студенты;

-- С помощью функции SUBSTR извлекаем первые буквы имени и отчества

Результат:

ФИО Зарплата/стипендия Статус

Абдулов С.А. 3000 Преподаватель

Костыркин О.В. 4000 Преподаватель

Тарасова Л.А. 2000 Преподаватель

Студейкин А.А. 2500 Преподаватель

Викулина В.И. 6000 Преподаватель

18 rows selected

20. Имеется таблица:

Id	Linked_id	Part
0	-1	Оглавление
1	0	Глава 1
2	1	Часть 1
3	1	Часть 2
4	0	Глава 2
5	4	Часть 1
6	4	Часть 2

Получить результат в виде:

Оглавление

1 Глава 1

1.1 Часть 1

1.2 Часть 2

2 Глава 2

2.1 Часть 1

2.2 Часть 2

Количество глав и частей произвольное.

-- Создаем таблицу book

create table Book (Id number(2),Linked_id number(2),Part varchar2(10));

insert into Book values(0,-1,'Оглавление');

insert into Book values(1,0,'Глава 1');

insert into Book values(2,1,'Часть 1');

insert into Book values(3,1,'Часть 2');

insert into Book values(4,0,'Глава 2');

insert into Book values(5,4,'Часть 1');

insert into Book values(6,4,'Часть 2');

--Сам запрос:

WITH t1 AS (SELECT * FROM book),

t2 AS (SELECT t1.*, row_number() over(partition BY linked_id order by id) rn FROM t1)

SELECT SUBSTR(smth,4, LENGTH(smth)) as оглавление -- таблица, отсортированная по linked_id вместе со столбцом row_number

FROM (SELECT sys_connect_by_path(rn,'.') ||' ' ||part AS smth

FROM t2 START WITH id = 0 -- составляем оглавление

CONNECT BY prior id = linked_id);

Результат:

Оглавление

Оглавление

1 Глава 1

1.1 Часть 1

1.2 Часть 2

2 Глава 2

2.1 Часть 1

2.2 Часть 2

ВАРИАНТ 4

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

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

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

SELECT DISTINCT ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО

FROM СТУДЕНТЫ ST JOIN УСПЕВАЕМОСТЬ USP ON ST.НОМЕР_СТУДЕНТА = USP.НОМЕР_СТУДЕНТА

WHERE USP.НОМЕР_ДИСЦИПЛИНЫ NOT IN

(SELECT УП.НОМЕР_ДИСЦИПЛИНЫ

FROM (СТУДЕНТЫ СТ2 JOIN ГРУППЫ ГР ON СТ2.НОМЕР_ГРУППЫ = ГР.НОМЕР_ГРУППЫ) JOIN УЧЕБНЫЕ_ПЛАНЫ УП ON ГР.КОД_СПЕЦИАЛЬНОСТИ = УП.КОД_СПЕЦИАЛЬНОСТИ

WHERE НОМЕР_СТУДЕНТА = ST.НОМЕР_СТУДЕНТА);

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

--В таблице JOB_HISTORY выбираем записи для одного сотрудника с перерывом в работе больше 1 дня(чтобы не включать повышения).

select first_name, last_name

from

(select 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 employee_id) j

join employees e on j.employee_id = e.employee_id;

/*Если у сотрудника был перерыв в стаже, то разница между последней датой

окончания и первой датой начала работы не будет совпадать с суммой промежутков

работы.*/

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

--SUMSAL – вспомогательная таблица с суммарными зарплатами по отделам.

WITH SUMSAL AS

(SELECT E.DEPARTMENT_ID, SUM(E.SALARY) AS SUMSAL

FROM DEPARTMENTS D JOIN EMPLOYEES E ON D.DEPARTMENT_ID = E.DEPARTMENT_ID

GROUP BY E.DEPARTMENT_ID),

--S2 – вспомогательная таблица с городами.

S2 AS

(SELECT SUMSAL, LOCATION_ID, D.DEPARTMENT_NAME

FROM SUMSAL SS JOIN DEPARTMENTS D ON SS.DEPARTMENT_ID = D.DEPARTMENT_ID)

--Выбираем города с суммарной з/п, большей средней по городу.

SELECT S2.DEPARTMENT_NAME, L.CITY, SUMSAL AS DEPT_SUM_SALARY, AVERAGE_SUM_SALARY

FROM S2 JOIN LOCATIONS L ON S2.LOCATION_ID = L.LOCATION_ID

JOIN

(SELECT L1.CITY AS CITY, AVG(SUMSAL) AS AVERAGE_SUM_SALARY

FROM S2 JOIN LOCATIONS L1 ON S2.LOCATION_ID = L1.LOCATION_ID

GROUP BY L1.CITY) T

ON L.CITY = T.CITY

WHERE SUMSAL >

(SELECT AVG(SUMSAL)

FROM S2 JOIN LOCATIONS L1 ON S2.LOCATION_ID = L1.LOCATION_ID

WHERE L1.CITY = L.CITY

GROUP BY L1.CITY);

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

Фамилия – Дисциплина 1, Дата сдачи 1; Дисциплина 2, Дата сдачи 2;…..

SELECT Фамилия

|| ' - '

|| wm_concat(д1.название

|| ', '

|| у1.дата

|| '; '

|| д2.название

|| ', '

|| у2.дата

|| '; ') выборка

FROM успеваемость у1

INNER JOIN успеваемость у2

ON (у1.номер_студента = у2.номер_студента

AND у1.номер!= у2.номер

AND у1.дата <= у2.дата)

--Соединяем таблицу успеваемость саму с собой для вычисления интервалов между экзаменами

INNER JOIN студенты

ON (у1.номер_студента = студенты.номер_студента)

--Узнаём фамилию студента.

INNER JOIN дисциплины д1

ON (у1.номер_дисциплины = д1.номер_дисциплины)

INNER JOIN дисциплины д2

ON (у2.номер_дисциплины = д2.номер_дисциплины)

--Узнаём названия дисциплин.

WHERE (у2.дата - у1.дата)<3

GROUP BY фамилия;

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

select department_id, wm_concat(last_name) Фамилии

Для каждого отдела объединяем фамилии сотрудников, работающих в нём в одну строку.

from employees

where department_id is not null

group by department_id;

Другой способ:

select department_id,

substr((max(sys_connect_by_path(last_name,','))),2) as employees

--последние два столбца используются для связи текущего ряда со следующим

from (select department_id,

last_name,

row_number() over (partition by department_id order by first_name) as curr,

row_number() over (partition by department_id order by first_name) -1 as prev

from employees

where department_id is not null)

group by department_id

connect by prev = prior curr and department_id = prior department_id

start with curr = 1;

6. Вывести фамилии сотрудников, зарплаты и неплотные ранги в пределах отделов, в которых работают сотрудники.

select last_name, department_id, salary, rnk

from

(select e1.employee_id, e1.last_name, e1.department_id, e1.salary, 1+count(e2.salary) rnk

from employees e1 left join employees e2 on e1.department_id=e2.department_id and e1.salary<e2.salary

Соединяем таблицу employees саму с собой и для каждого сотрудника считаем количество сотрудников,

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

where e1.department_id is not null

group by e1.employee_id, e1.last_name, e1.department_id, e1.salary

order by e1.department_id, e1.salary desc);

7. Написать запрос, выдающий отчёт о суммарных выплатах сотрудникам, непосредственно подчиняющихся заданному руководителю по идентификаторам должностей (поле Job_id). Непосредственное подчинение предполагает подчинение на первом уровне. Номер руководителя может встречаться в отчете лишь дважды.

Пример отчёта:

Номер руководителя	Должность	Кол-во сотрудников	Выплаты	Вид выплаты
	AD_VP			Зарплата сотрудников в должности AD_VP
	MK_MAN			Зарплата сотрудников в должности MK_MAN
	PU_MAN			Зарплата сотрудников в должности PU_MAN
	SA_MAN			Зарплата сотрудников в должности SA_MAN
	ST_MAN		36400.	Зарплата сотрудников в должности ST_MAN
				Суммарная зарплата у руководителя 100
	AC_MGR			Зарплата сотрудников в должности AC_MGR
	FI_MGR			Зарплата сотрудников в должности FI_MGR
	HR_REP			Зарплата сотрудников в должности HR_REP
	PR_REP			Зарплата сотрудников в должности PR_REP
	AD_ASST			Зарплата сотрудников в должности AD_ASST
				Суммарная зарплата у руководителя 100
				Общий итог

with t1

as (Select manager_id, job_id, emp_count, sumsal, m_id, j_id, rownum r

from

(Select manager_id, job_id, count(*) emp_count, sum(salary) sumsal, grouping(manager_id) m_id, grouping (job_id) j_id

from employees

where manager_id is not null

group by rollup (manager_id, job_id)

order by manager_id)),

Добавляем в представление два grouping столбца для определения итоговых строк.

t2 as

(select manager_id, min(r) min_r

from t1

group by manager_id)

Определяем номер первой строки вхождения для каждого менеджера.

select

case j_id when 1 then to_char(manager_id)

else case r when min_r then to_char(manager_id)

else ' ' end end "Номер руководителя",

Выводим номер менеджера только в первой и в итоговой строчке.

nvl(job_id, ' ') "Должность",

emp_count "Кол-во сотрудников",

sumsal "Выплаты",

case m_id when 1 then 'Общий итог'

else case j_id when 1 then 'Суммарная зарплата у руководителя ' || manager_id

else 'Зарплата сотрудников в должности ' || t1.job_id end end "Вид выплаты"

Заполняем столбец "Вил выплаты" в зависимости от значения в grouping столбцах.

from t1 left join t2 using(manager_id)

order by manager_id, job_id;

8. Создать запрос для получения информации об успеваемости студентов в виде:

ФИО	Дисциплина	Оценка	Дата	Примечания
Петров	Математика		20.1.2008
	Физика		22.1.2008
	Химия		25.1.2008
	Химия		27.1.2008	Пересдача
Усов	Математика		12.06.99
	Экономика		15.06.99
	Менеджмент		17.06.99
	Менеджмент		18.06.99	Пересдача

Задачу решить двумя способами:

а) с использованием аналитических функций;

б) без использования аналитических функций.

а)

with t1 as

(select с.номер_студента, Фамилия, название, оценка, дата,

min(дата) over (partition by у.номер_студента, у.номер_дисциплины) min_d,

/*Определяем первую дату сдачи каждого экзамена для каждого студента.*/

Row_Number() over (order by фамилия, дата) r

from успеваемость у inner join студенты с on у.номер_студента = с.номер_студента

inner join дисциплины д on (у.номер_дисциплины = д.номер_дисциплины)

order by фамилия, дата),

t2 as

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