|
Читайте также: |
SELECT SUM(regexp_substr(str,'[0-9]+',1,l)) summa
FROM
(SELECT level l,str FROM st CONNECT BY level<=
(SELECT LENGTH(str) FROM st));
Комментарий:
Находим все числовые комбинации начиная с одного символа и заканчивая, если вся строка из цифр, числом длинной с всю строку, и суммируем их.
7. Создать запрос для получения информации о фамилиях студентов, которые сдали экзамены по всем предметам, предусмотренных для их специальности учебным планом.
SELECT номер_студента, фамилия, имя, отчество, номер_группы
FROM студенты
WHERE номер_студента NOT IN
(SELECT номер_студента FROM (SELECT номер_студента, номер_дисциплины
FROM (студенты NATURAL JOIN группы)
RIGHT JOIN учебные_планы USING (код_специальности)
MINUS
SELECT номер_студента, номер_дисциплины
FROM успеваемость
WHERE оценка > 2));
3412 Поляков Анатолий Алексеевич 121
3413 Старова Любовь Михайловна 121
3418 Усов Валерий Харитонович 123
Комментарий:
В запросе используется оператор NOT IN для исключения тех студентов, которые имеют задолжности по экзаменам.
8. Используя словарь данных, получить информацию о первичных ключах и подчиненных таблицах всех таблиц в схеме HR:
| Имя таблицы | Список столбцов первичного ключа | Список подчиненных таблиц |
В списках имена столбцов и таблиц вывести через запятую.
WITH TABLE_1 AS (
SELECT main_tables.table_name as Main_Table, main_columns.column_name FPK, depend_tables.table_name as Depend_Table,
ROW_NUMBER() OVER (PARTITION BY main_tables.table_name ORDER BY depend_tables.table_name) R,
COUNT(*) OVER (PARTITION BY main_tables.table_name) CNT
FROM user_constraints depend_tables
JOIN user_constraints main_tables
ON depend_tables.r_constraint_name = main_tables.constraint_name
JOIN (SELECT column_name, constraint_name FROM user_cons_columns WHERE position = 1) main_columns
ON depend_tables.r_constraint_name = main_columns.constraint_name)
SELECT Main_Table AS "Главная таблица",
trim(both ',' FROM SYS_CONNECT_BY_PATH(FPK,', ')) "Первичный ключ",
trim(both ',' FROM SYS_CONNECT_BY_PATH(Depend_Table,', ')) "Подчиненные таблицы"
FROM TABLE_1
WHERE R = CNT
START WITH R = 1
CONNECT BY PRIOR Main_Table = Main_Table AND PRIOR R = R-1
ORDER BY Main_Table;
COUNTRIES COUNTRY_ID LOCATIONS
DEPARTMENTS DEPARTMENT_ID, DEPARTMENT_ID EMPLOYEES, JOB_HISTORY
EMPLOYEES EMPLOYEE_ID, EMPLOYEE_ID, EMPLOYEE_ID DEPARTMENTS, EMPLOYEES, JOB_HISTORY
JOBS JOB_ID, JOB_ID EMPLOYEES, JOB_HISTORY
LOCATIONS LOCATION_ID DEPARTMENTS
REGIONS REGION_ID COUNTRIES
ГРУППЫ НОМЕР_ГРУППЫ СТУДЕНТЫ
ДИСЦИПЛИНЫ НОМЕР_ДИСЦИПЛИНЫ, НОМЕР_ДИСЦИПЛИНЫ УСПЕВАЕМОСТЬ, УЧЕБНЫЕ_ПЛАНЫ
ПРЕПОДАВАТЕЛИ НОМЕР_ПРЕПОДАВАТЕЛЯ, НОМЕР_ПРЕПОДАВАТЕЛЯ ДИСЦИПЛИНЫ, ПРЕПОДАВАТЕЛИ
СПЕЦИАЛЬНОСТИ КОД_СПЕЦИАЛЬНОСТИ, КОД_СПЕЦИАЛЬНОСТИ ГРУППЫ, УЧЕБНЫЕ_ПЛАНЫ
СТУДЕНТЫ НОМЕР_СТУДЕНТА УСПЕВАЕМОСТЬ
Комментарий:
В запросе используется предложение WITH, аналитическая функция ROW_NUMBER() и COUNT() c разделом фрагментации и функция SYS_CONNECT_BY_PATH для организации циклов, чтобы выводить значения второго и третьего столбцов в одну строку, через запятую.
9. Создать запрос для получения списка преподавателей и их должностей. В списке сначала должны идти преподаватели, не имеющие начальников. Их фамилии должны быть выведены заглавными буквами. Далее должны идти фамилии остальных сотрудников с отступом от левого края на две позиции.
SELECT
case
when подчиняется is null then UPPER(фамилия)
else LPAD(фамилия, LENGTH(фамилия)+2, ' ')
end Фамилия, Должность
FROM преподаватели п
order by (select фамилия from преподаватели where п.фамилия=фамилия and п.подчиняется is null);
АБДУЛОВ Доцент
КОСТЫРКИН Профессор
Викулина Доцент
Соколов Ассистент
Казанко Преподаватель
Тарасова Преподаватель
Студейкин Доцент
Загарийчук Ассистент
Позднякова Преподаватель
Комментарий:
Главное в этом запросе это использование кореллированного подзапроса в разделе ORDER BY.
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 фамилия;
Поляков Математика 12.06.99 Хор
Поляков Физика 10.06.99 Отл
Старова Математика 12.06.99 Отл
Старова Физика 10.06.99 Отл
Улиткин Менеджмент Не сдавал Не сдавал
Улиткин Математика Не сдавал Не сдавал
Улиткин Экономика Не сдавал Не сдавал
Усов Математика 12.06.99 Отл
Усов Экономика 15.06.99 Удовл
Комментарий:
В запросе используются разные виды соединения, такие как NATURAL JOIN и RIGHT JOIN, также комбинации из DECODE и CASE.
11. Создать таблицы Кафедра1 и Кафедра2. Используя возможности многотабличной вставки, обеспечить вставку данных в созданные таблицы преподавателей из таблицы Преподаватели в соответствии с кафедрой, на которой они работают.
create table Кафедра1 (номер_преподавателя number(5,0), фамилия nvarchar2(20),имя nvarchar2(20),отчество nvarchar2(20), дата_контракта date, номер_телефона nvarchar2(15), должность varchar2(15), подчиняется number(5), зарплата number(10));
create table Кафедра2 (номер_преподавателя number(5,0), фамилия nvarchar2(20),имя nvarchar2(20),отчество nvarchar2(20), дата_контракта date, номер_телефона nvarchar2(15), должность varchar2(15), подчиняется number(5), зарплата number(10));
INSERT ALL
WHEN кафедра = 'Кафедра 1' THEN
INTO кафедра1 VALUES
(номер_преподавателя, фамилия, имя, отчество, дата_контракта, номер_телефона, должность, подчиняется, зарплата)
WHEN кафедра = 'Кафедра 2' THEN
INTO кафедра2 VALUES
(номер_преподавателя, фамилия, имя, отчество, дата_контракта, номер_телефона, должность, подчиняется, зарплата)
SELECT * FROM преподаватели;
4002 Костыркин Олег Владимирович 01.09.97 2-46-89-71 Профессор Кафедра 1
4001 Викулина Валентина Ивановна 01.04.98 2-46-89-70 Доцент Кафедра 1
4006 Соколов Петр Николаевич 01.02.78 2-46-89-75 Ассистент Кафедра 1
4003 Казанко Виталий Владимрович 01.09.88 2-46-89-73 Преподаватель Кафедра 1
4008 Абдулов Семен Антонович 01.06.88 2-46-89-78 Доцент Кафедра 2
4007 Студейкин Андрей Андреевич 01.05.79 2-46-89-78 Доцент Кафедра 2
4005 Загарийчук Игорь Дмитриевич 01.09.77 2-46-89-78 Ассистент Кафедра 2
4004 Позднякова Любовь Алексеевна 10.05.79 2-46-89-72 Преподаватель Кафедра 2
4009 Тарасова Людмила Алексеевна 20.05.81 2-46-89-72 Преподаватель Кафедра 2
Комментарий:
Используется INSERT ALL, если текущее значение поля кафедра имеет значение «Кафедра1», то строка вставляется в таблицу Кафедра1, и наоборот.
12. Создать запрос для получения информации о количестве преподавателей, работающих на кафедрах в разных должностях. Результат запроса должен быть представлен в виде:
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 3
Профессор 1 0 1
Доцент 1 2 3
Ассистент 1 1 2
Итого 4 5 9
| Кафедра1 | Кафедра2 | Итого | |
| Профессор | |||
| Доцент | |||
| ……. | |||
| Итого: |
Комментарий:
Сначала мы считаем count() по столбцам, затем соединяем с count() по строкам, для того чтобы получить структуру указанную в задании.
13. Создать запрос для определения стажа работы преподавателей. Результат вывести в формате ## лет ## мес #дней
select номер_преподавателя, фамилия, имя, отчество,
trunc(months_between(sysdate, дата_контракта) /12) || ' лет ' ||
mod(trunc(months_between(sysdate, дата_контракта)), 12) || ' месяцев ' ||
round(sysdate - add_months(дата_контракта,trunc(months_between(sysdate,
дата_контракта)))) || ' дней' "Стаж работы"
from преподаватели;
4002 Костыркин Олег Владимирович 14 лет 8 месяцев 25 дней
4001 Викулина Валентина Ивановна 14 лет 1 месяцев 25 дней
4006 Соколов Петр Николаевич 34 лет 3 месяцев 25 дней
4003 Казанко Виталий Владимрович 23 лет 8 месяцев 25 дней
4008 Абдулов Семен Антонович 23 лет 11 месяцев 25 дней
4007 Студейкин Андрей Андреевич 33 лет 0 месяцев 25 дней
4005 Загарийчук Игорь Дмитриевич 34 лет 8 месяцев 25 дней
4004 Позднякова Любовь Алексеевна 33 лет 0 месяцев 16 дней
4009 Тарасова Людмила Алексеевна 31 лет 0 месяцев 6 дней
Вариант для проверки (только дата одна для всех):
select номер_преподавателя, фамилия, имя, отчество,
trunc(months_between(sysdate, TO_DATE('&&дата_контракта', 'dd.mm.yyyy')) /12) || ' лет ' ||
mod(trunc(months_between(sysdate, TO_DATE('&&дата_контракта', 'dd.mm.yyyy'))), 12) || ' месяцев ' ||
round(sysdate - add_months(TO_DATE('&&дата_контракта', 'dd.mm.yyyy'),trunc(months_between(sysdate,
TO_DATE('&&дата_контракта', 'dd.mm.yyyy'))))) || ' дней' "Стаж работы"
from преподаватели;
Комментарий:
Использование алгебраических функций для подсчета стажа. Год считается делением на 12 количества месяцев межде текущей датой и датой контракта, округленного в меньшую сторону. Месяц считается как остаток деления на 12 количества месяцев межде текущей датой и датой контракта, округленного в меньшую сторону. День считается по формуле:
round(sysdate — add_months(дата_контракта, trunc(months_between(sysdate, дата_контракта))))
add_months(дата_контракта,trunc(months_between(sysdate, дата_контракта))) =
= текущая дата плюс количество месяцев между текущей датой и датой контракта. Значение 2 может быть любым целым числом. Если в месяце, полученном в результате, число дней меньше, чем в текущем месяце, то возвращается последний день месяца результата. Если, число дней не меньше то день месяца-результата и текущий день месяца совпадают. В итоге вычитанием полученного значения из текущей даты и его округления получаем кол-во дней стажа.
14. Требуется из заданного числа сформировать два новых выражения:
Input:57896854742
Output 1: 5/789-685-4742
Output 2: [578|968|547|42]
SELECT regexp_replace('57896854742,'([[:digit:]]{1})([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]*)','\1/\2-\3-\4') AS output1,
regexp_replace('57896854742','([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{1,3})([[:digit:]]*)','[\1|\2|\3|\4]')
AS output2 FROM dual;
5/789-685-4742 [578|968|547|42]
Комментарий:
Использование регулярных выражений позволяет получить правильный результат.
15. Увеличить вдвое зарплату преподавателям, которые подчиняются (непосредственно или опосредованно) Костыркину.
select фамилия, зарплата, зарплата*2
from преподаватели
where фамилия!='Костыркин'
start with фамилия='Костыркин'
connect by prior номер_преподавателя = подчиняется;
Викулина 3000 6000
Соколов 1500 3000
Казанко 2000 4000
Комментарий:
Запрос выполняется с использованием выборки иерархических данных. Обход дерева организовывается сверху вниз, чтобы получить всех подчиненных Костыркина. Подобная конструкция позволяет найти тех сотрудников, которые подчинены Костыркину опосредовано.
16. Создать запрос к таблице с числовым полем, который выводит числа (скажем, от 1 до 50000), которых в таблице нет.
create table numbers (num_bers number(5,0));
insert into numbers values(25);
insert into numbers values(24);
insert into numbers values(1);
insert into numbers values(10);
insert into numbers values(9);
insert into numbers values(100);
insert into numbers values(99);
select * from numbers;
1, 10, 100, 99, 9, 24, 25
ЗАПРОС:
select level
from dual
connect by level <=50000
MINUS
select num_bers from numbers;
ЧАСТЬ РЕЗУЛЬТАТА:
2, 3, 4, 5, 6, 7, 8, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 26
Комментарий:
Предложением select level from dual connect by level <=50000 получаем числа от 1 до 50000, а затем вычитаем из этих значений значения в нашей таблице.
17. Имеем таблицу:
| Номер | Категория | Продукт |
| 1 | Категория 1 | Mango |
| 2 | ||
| 3 | ||
| 4 | Banana | |
| 5 | ||
| 6 | ||
| 7 | Категория 2 | Vanilla |
| 8 | ||
| 9 | Strawberry |
Создать запрос для отображения ее в виде:
| Номер | Категория | Продукт |
| 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 |
create table продукты (номер number(5,0), категория varchar2(20), продукт varchar2(20));
insert into продукты values (1, 'Категория1', 'Mango');
insert into продукты values (2, '', '');
insert into продукты values (3, '', '');
insert into продукты values (4, '', 'Banana');
insert into продукты values (5, '', '');
insert into продукты values (6, '', '');
insert into продукты values (7, 'Категория2', 'Vanilla');
insert into продукты values (8, '', '');
insert into продукты values (9, '', 'Strawberry');
select * from продукты;
1 Категория1 Mango
4 Banana
7 Категория2 Vanilla
9 Strawberry
SELECT номер,
last_value (категория ignore nulls) over (order by номер) AS Категория,
last_value (продукт ignore nulls) over (order by номер) AS продукт
FROM продукты;
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
Комментарий:
В запросе используем аналитическую функцию last_value() для того, чтобы использовать предыдущее значение при выводе таблицы.
18. Создать запрос, который выводит фамилию студента, стипендию и процент, который составляет его стипендия от суммарной стипендии студентов на специальности, на которой они учатся.
select фамилия, стипендия, round(стипендия/(сумма/100)) процент from (select сс.фамилия, сс.стипендия,сс.номер_группы, гг.код_специальности,
sum(сс.стипендия) over(partition by гг.код_специальности) сумма
from студенты сс JOIN группы гг on (сс.номер_группы=гг.номер_группы));
Поляков 200 21
Старова 250 26
Гриценко 300 32
Котенко 0 0
Нагорный 200 21
Устинов 250 27
Усов 250 27
Улиткин 225 24
Ежов 200 22
Комментарий:
Для создания этого запроса снова используется аналитическая функция – SUM() - с фрагментацией по коду специальности, и псевдоним — СУММА.
19. Создать запрос, позволяющий выводить информацию о студентах и преподавателях (Фамилия с инициалами, Зарплата/Стипендия, Статус – преподаватель или студент).
create table prepods as select * from преподаватели;
TRUNCATE TABLE prepods;
alter table prepods modify(фамилия varchar2(20), имя varchar2(20), отчество varchar2(20));
insert into prepods select * from преподаватели;
select * from
(select фамилия || ' ' || substr(имя, 1,1) || '.' || substr(отчество, 1,1) || '.' "Ф.И.О.", зарплата "Зарплата/Стипендия",
'Преподаватель' "Статус"
from prepods)
UNION
select * from
(select фамилия || ' ' || substr(имя, 1,1) || '.' || substr(отчество, 1,1) || '.' "Ф.И.О.", стипендия "Зарплата/Стипендия",
'Студент' "Статус"
from студенты);
Исправленный вариант:
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 студенты;
Абдулов С.А. 3000 Преподаватель
Викулина В.И. 3000 Преподаватель
Гриценко В.Н. 300 Студент
Ежов А.П. 200 Студент
Загарийчук И.Д. 2000 Преподаватель
Казанко В.В. 2000 Преподаватель
Костыркин О.В. 4000 Преподаватель
Котенко А.Н. 0 Студент
Нагорный Е.В. 200 Студент
Позднякова Л.А. 2500 Преподаватель
Поляков А.А. 200 Студент
Соколов П.Н. 1500 Преподаватель
Старова Л.М. 250 Студент
Студейкин А.А. 2500 Преподаватель
Тарасова Л.А. 2000 Преподаватель
Улиткин А.С. 225 Студент
Усов В.Х. 250 Студент
Устинов В.В. 250 Студент
Комментарий:
Для того, чтобы сделать это задание мне пришлось создать копию таблицы ПРЕПОДАВАТЕЛИ, в которой были изменены типы данных для столбцов фамилия, имя, отчество c nvarchar2 на varchar2, так как при попытки объединения с таблицей СТУДЕНТЫ, oracle выдавал ошибку об несоответствии кодовой таблицы.
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
Количество глав и частей произвольное.
create table оглавление (id number(5,0), linked_id number(5,0), part varchar2(20));
insert into оглавление values (0,-1,'оглавление');
insert into оглавление values (1,0,'Глава 1');
insert into оглавление values (2,1,'Часть 1');
insert into оглавление values (3,1,'Часть 2');
insert into оглавление values (4,0,'Глава 2');
insert into оглавление values (5,4,'Часть 1');
insert into оглавление values (6,4,'Часть 2');
select * from оглавление;
WITH t1 AS (SELECT * FROM оглавление),
t2 AS (SELECT t1.*, row_number() over(partition by linked_id order by id) rn FROM t1)
SELECT SUBSTR(s,4, LENGTH(s)) as оглавление
FROM (SELECT sys_connect_by_path(rn,'.') ||' ' ||part AS s
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
Комментарий:
Для организации этого запроса используется конструкция
START WITH
CONNECT BY, - для организации циклического обхода значений таблицы.
ВАРИАНТ 14
(Базы данных Студент и Human Resources)
1. Создать запрос для определения списка студентов, сдавших экзамены по дисциплинам, не предусмотренным учебным планом специальности, на которой учатся студенты.
Select СТУДЕНТЫ.НОМЕР_СТУДЕНТА, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, НАЗВАНИЕ "Лишний предмет"
From СТУДЕНТЫ JOIN УСПЕВАЕМОСТЬ
ON СТУДЕНТЫ.НОМЕР_СТУДЕНТА=УСПЕВАЕМОСТЬ.НОМЕР_СТУДЕНТА
JOIN "ДИСЦИПЛИНЫ"
ON "ДИСЦИПЛИНЫ"."НОМЕР_ДИСЦИПЛИНЫ" = УСПЕВАЕМОСТЬ.НОМЕР_ДИСЦИПЛИНЫ
MINUS
Select СТУДЕНТЫ.НОМЕР_СТУДЕНТА, ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, ДИСЦИПЛИНЫ.НАЗВАНИЕ
From СТУДЕНТЫ JOIN "ГРУППЫ"
ON СТУДЕНТЫ.НОМЕР_ГРУППЫ="ГРУППЫ"."НОМЕР_ГРУППЫ"
JOIN "СПЕЦИАЛЬНОСТИ"
ON "ГРУППЫ"."КОД_СПЕЦИАЛЬНОСТИ" = "СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ"
JOIN "УЧЕБНЫЕ_ПЛАНЫ"
ON "СПЕЦИАЛЬНОСТИ"."КОД_СПЕЦИАЛЬНОСТИ" = "УЧЕБНЫЕ_ПЛАНЫ"."КОД_СПЕЦИАЛЬНОСТИ"
JOIN "ДИСЦИПЛИНЫ"
ON УЧЕБНЫЕ_ПЛАНЫ.НОМЕР_ДИСЦИПЛИНЫ = ДИСЦИПЛИНЫ.НОМЕР_ДИСЦИПЛИНЫ;
Результат:
НОМЕР_СТУДЕНТА ФАМИЛИЯ ИМЯ ОТЧЕСТВО Лишний предмет
-------------- --------------- --------------- --------------- ------------------------------
3414 Гриценко Владимир Николаевич Экономика
Комментарий:
Делаем выборку по всем сданным студентами экзаменам, а затем исключаем из полученного списка выборку, которая предписана учебным планом. В итоге получаем студентов, которые сдали лишние экзамены.
2. Создать запрос для определения сотрудников, которые имеют перерыв в стаже работы в фирме.
select j1.employee_id
From job_history j1 Join job_history j2
ON j1.employee_id=j2.employee_id AND j2.start_date-j1.end_date>1;
Результат:
EMPLOYEE_ID
-----------
Комментарий:
Соединяем таблицу job_history саму с собой по полю employee_id, и для каждого сотрудника отбираем только те записи, где разница между датой окончания работы и датой начала следующей работы больше одного дня, что и означает перерыв в стаже работы.
3. Определить отделы, в которых суммарная зарплата сотрудников выше средней суммарной зарплаты по отделам, расположенным в том же городе. Вывести информацию о наименовании отдела, суммарной заработной плате отдела и средней заработной плате города, где расположен отдел.
select *
from(select department_name, SUM_DEPT, Avg(SUM_DEPT) Over (Partition By location_id) AVG_CITY
from
(select department_id, sum(salary) SUM_DEPT
from employees
group by department_id) T1
inner join departments d on T1.department_id=d.department_id)
where (SUM_DEPT > AVG_CITY) and (department_name is not null);
Результат:
DEPARTMENT_NAME SUM_DEPT AVG_CITY
------------------------------ -------- --------
Executive 58000 31840
Finance 51600 31840
Комментарий:
С помощью функции Avg(SUM_DEPT) Over (Partition By location_id) считаем среднюю суммарную зарплату по отделам, расположенным в одном городе. Далее считаем суммарную зарплату по всем отделам. Затем делаем объединение с таблицей departments, которое содержит информацию о суммарной заработной плате отдела и о местоположении отдела. При выполнении запроса сотрудники, которые не состоят ни в каком отделе, исключаются благодаря условию department_name is not null.
4. Создать запрос для вывода информации о студентах, у которых интервал между датами сдачи экзаменов составляет менее трех дней. Информацию вывести в виде:
Фамилия – Дисциплина 1, Дата сдачи 1; Дисциплина 2, Дата сдачи 2;…..
Select В.Ведомость
From
(Select Б.Ф, MAX(Б.Ф||' - '||ltrim(sys_connect_by_path(Б.НД, ';'), ';')) Ведомость
FROM
(Select А.СТ, А.Ф, А.Н, А.Д, А.НД,
row_number() over(partition BY А.СТ Order by А.СТ) NUM,
row_number() over(partition BY А.СТ Order by А.СТ)+1 PNUM
From
(Select СТУДЕНТЫ.НОМЕР_СТУДЕНТА СТ, ФАМИЛИЯ Ф, НАЗВАНИЕ Н, ДАТА Д, НАЗВАНИЕ||', '||ДАТА НД
From СТУДЕНТЫ
JOIN УСПЕВАЕМОСТЬ
ON СТУДЕНТЫ.НОМЕР_СТУДЕНТА=УСПЕВАЕМОСТЬ.НОМЕР_СТУДЕНТА
JOIN ДИСЦИПЛИНЫ
ON УСПЕВАЕМОСТЬ.НОМЕР_ДИСЦИПЛИНЫ=ДИСЦИПЛИНЫ.НОМЕР_ДИСЦИПЛИНЫ
ORDER by ФАМИЛИЯ, ДАТА DESC) А) Б
Start with NUM=1
connect by prior PNUM=NUM and prior Б.Ф=Б.Ф
Group by Б.Ф) В
INTERSECT
Select В.Ведомость
From
(Select Б.Ф, MAX(Б.Ф||' - '||ltrim(sys_connect_by_path(Б.НД, ';'), ';')) Ведомость
Дата добавления: 2015-08-18; просмотров: 145 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| Main_Table PK User_Constraints 10 страница | | | Main_Table PK User_Constraints 12 страница |