Current_year mon tue wed thu fri sat sun

ВАРИАНТ 1

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

1. Для двух заданных сотрудников найти их ближайшего общего начальника.

UNDEFINE first_emp last_emp;

WITH tbl_same_managers AS

(SELECT employee_id, RANK() OVER (ORDER BY (MIN(LEVEL))) rank

FROM employees

WHERE employee_id NOT IN (&&first_emp, &&last_emp)

START WITH employee_id IN (&first_emp, &last_emp)

CONNECT BY PRIOR manager_id = employee_id

GROUP BY employee_id

HAVING COUNT(*) = 2/*общие начальники => встречаются два раза*/)

SELECT employee_id chief_id, last_name chief_name

FROM tbl_same_managers JOIN employees USING (employee_id)

WHERE rank = 1 /*ближайшего общего начальника*/;

Результат:

Kогда emp1=174, emp2=178 =>

EMPLOYEE_ID LAST_NAME

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

149 Zlotkey

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

Сначала создается вспомогательная таблица tbl_same_managers: Выводит все общие начальники для двух заданных сотрудников. В tbl_same_managers строится иерархия снизу - вверх: START WITH employee_id IN (&first_emp, &last_emp), CONNECT BY PRIOR manager_id = employee_id. При этом сами сотрудники выключится в предложении WHERE. Далее, группирует по employee_id и выбирает только сотрудники, которые два раза встречаются – это список всех общих начальников для двух заданных сотрудников.

После выбирать ближайший общего начальника для двух заданных сотрудников с наименьшим ранком по минимальному значению LEVEL(rank=1).

Структура натурального дерева:

King(100)

Kochhar De Haan Mourgos Zlotkey(149) Hartstein

Abel(174) Taylor Grant(178)

2. Для заданного интервала лет вывести количество каждого дня недели в году.

UNDEFINE start_year end_year;

With

dates_table AS

(Select &&start_year + Level - 1 Current_year

,Decode(To_char(Last_day(To_date('01.02.'|| To_char(&&start_year + Level - 1), 'DD.MM.YYYY','NLS_DATE_LANGUAGE = ENGLISH')),'dd'),29,0,1) Is_leap_year

,TO_CHAR(TO_DATE('01.01.'||TO_CHAR(&start_year + LEVEL - 1),'DD.MM.YYYY','NLS_DATE_LANGUAGE = ENGLISH'), 'Dy','NLS_DATE_LANGUAGE = ENGLISH') name_day

FROM dual

CONNECT BY LEVEL <= &end_year - &start_year + 1)

SELECT current_year,

51 + DECODE(Is_leap_year, 1, DECODE(name_day, 'Mon', 2, 1), DECODE(name_day, 'Mon', 2, 'Sun', 2, 1)) mon,

51 + DECODE(Is_leap_year, 1, DECODE(name_day, 'Tue', 2, 1), DECODE(name_day, 'Tue', 2, 'Mon', 2, 1)) tue,

51 + DECODE(Is_leap_year, 1, DECODE(name_day, 'Wed', 2, 1), DECODE(name_day, 'Wed', 2, 'Tue', 2, 1)) wed,

51 + DECODE(Is_leap_year, 1, DECODE(name_day, 'Thu', 2, 1), DECODE(name_day, 'Thu', 2, 'Wed', 2, 1)) thu,

51 + DECODE(Is_leap_year, 1, DECODE(name_day, 'Fri', 2, 1), DECODE(name_day, 'Fri', 2, 'Thu',2, 1)) fri,

51 + DECODE(Is_leap_year, 1, DECODE(name_day, 'Sat', 2, 1), DECODE(name_day, 'Sat', 2, 'Fri',2, 1)) sat,

51 + DECODE(Is_leap_year, 1, DECODE(name_day, 'Sun', 2, 1), DECODE(name_day, 'Sun', 2,'Sat',2, 1)) sun

From Dates_table;

Результат:

start_year = 1900; end_year = 1905;

CURRENT_YEAR MON TUE WED THU FRI SAT SUN

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

1900 53 52 52 52 52 52 52

1901 52 53 52 52 52 52 52

1902 52 52 53 52 52 52 52

1903 52 52 52 53 52 52 52

1904 52 52 52 52 53 53 52

1905 52 52 52 52 52 52 53

6 rows selected

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

Сначала создается вспомогательная таблица dates_table(с помощью иерархия):

Вывод список лет, которые находятся в заданном интервале лет(current_year).

Определит високосние (Is_leap_year) с помощью формулы:

DECODE(to_char(last_day(to_date('01.02'|| TO_CHAR(&&start_year + LEVEL - 1), 'DD.MM.YYYY')),'dd'),29,0,1)

Вычиление последного дня для феврали, если он является 29-ой, то високосный год (Is_leap_year=0), иначе Is_leap_year=1

Название первого дня недели в каждом годе (01-Январь)

Далее, в каждом годе, не зависит от какого, есть по 51 каждого дня недели, т. е. 51 - понедельник, 51 - вторник....

Рассмотрим обычный год. Например, если первый день недели является Monday, то конец года

(31 – декабрь) также Monday. Так как 365=52*7+1=51*7+6*1+2(для Monday). То есть, если год не високосный и первый день недели - это понедельник, тогда к 51 понедельнику нужно добавить 2, остальные 1 => DECODE(name_day, 'Mon', 2, 1)

Рассмотрим високосный год. Например, если первый день недели является Monday, то конец года (31 – декабрь) – это Tueday. Так как 366=52*7+2=51*7+5*(1)+2(для Monday)+ 2(для Tueday)). То есть, если год високосный и первый день недели - это понедельник, тогда к 51 понедельнику, вторнику нужно добавить 2, остальные 1 => DECODE(name_day, 'Mon', 2, 'Sun', 2, 1)) за строка количества понедельники “mon” и DECODE(name_day, 'Tue', 2, 'Mon', 2, 1)) за строка количества вторника “tue”.

3. Имеется таблица с числовым столбцом. Требуется вывести те числа из заданного диапазона (скажем, от 1 до 5000), которых в таблице нет.

CREATE TABLE given_table_number AS (SELECT employee_id FROM employees);

--Решение №1:

SELECT num_row

FROM (SELECT ROWNUM num_row

FROM all_objects

WHERE ROWNUM <= &right_interval)

WHERE num_row >= &left_interval

MINUS

SELECT * FROM given_table_number;

--Решение №2:

SELECT num_row

FROM (SELECT LEVEL num_row

FROM dual

CONNECT BY LEVEL <= &right_interval)

WHERE num_row >= &left_interval

MINUS

SELECT * FROM given_table_number

Результат:

Kогда right_interval=5000, left_interval = 0

4893 rows selected

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

С помощью фукнции (Rownum) или иерархического подзапроса(LEVEL) генерируем последовательность натуральных чисел, начиная с 1 и до right_interval

Затем из данной последовательности выбираются числа, большие нижней границы (left_interval) и Minus множества числовых столбцов заданной таблицы.

4. Имеется таблица с двумя столбцами: id NUMBER(9) PRIMARY KEY и info VARCHAR2(80) NOT NULL

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

Написать команду SELECT, выводящую числа из поля id и скорректированные данные поля INFO. При этом должны быть убраны начальные и хвостовые пробелы, а между всеми словами должно быть только по одному пробелу

Возрастающая сортировка должна быть выполнена по скорректированным данным поля INFO. Ограничения способа решения: Решение найти с использованием раздела MODEL.

CREATE TABLE tbl_info

(id NUMBER(9) PRIMARY KEY, info VARCHAR2(80) NOT NULL);

INSERT INTO tbl_info VALUES (1, ' Hello, the world');

INSERT INTO tbl_info VALUES (2, 'Model clause in oracle ');

INSERT INTO tbl_info VALUES (3, 'Z23456789 123456789 123456789 123456789 80 symbols');

INSERT INTO tbl_info VALUES (4, 'A 70spaces ');

SELECT id, TRIM(str2) format_info

FROM tbl_info

MODEL

PARTITION BY (id)

DIMENSION BY(1 n)

MEASURES (cast('' AS VARCHAR2(4000)) str2, TRIM(info) str1)

RULES ITERATE(80)

(

str2[1]= str2[1]||' ' ||SUBSTR(str1[1], 1, INSTR(str1[1],' ')-1),

str1[1] = TRIM(SUBSTR(str1[1],INSTR(str1[1],' ')))||' '

)

order by 2;

Результат:

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