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

UNDEFINE pdate

SELECT

(CASE

WHEN TO_DATE('&&pdate', 'DD.MM.YYYY')<= NEXT_DAY(TO_DATE((CONCAT('31.07.',TO_CHAR(TO_DATE('&pdate', 'DD.MM.YYYY'),'YYYY'))), 'DD.MM.YYYY'),'Понедельник')-42

THEN NEXT_DAY(TO_DATE(CONCAT('31.07',TO_CHAR(TO_DATE('&pdate', 'DD.MM.YYYY'),'YYYY'))),'Понедельник')-42

ELSE NEXT_DAY(TO_DATE(CONCAT('31.07',TO_CHAR(TO_NUMBER(TO_CHAR(TO_DATE('&pdate', 'DD.MM.YYYY'),'YYYY'))+1))),'Понедельник')-42

END) AS "Wimbledon start"

FROM DUAL;

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

SELECT last_name, first_name, salary

FROM(SELECT last_name, first_name, salary,

(SELECT ROWNUM Rnum

FROM (SELECT last_name, salary, first_name, ROWNUM Rnum

FROM (SELECT last_name, salary, first_name

FROM employees

ORDER BY salary ASC)) Elist2

WHERE Elist1.Rnum != Elist2.Rnum AND (Elist1.last_name = Elist2.last_name OR Elist1.first_name = Elist2.first_name

OR Elist1.salary = Elist2.salary)) Quant

FROM (SELECT last_name, salary, first_name, ROWNUM Rnum

FROM (SELECT last_name, salary, first_name

FROM employees

ORDER BY salary ASC)) Elist1

WHERE (SELECT SUM(salary)

FROM (SELECT last_name, salary, first_name, ROWNUM Rnum

FROM (SELECT last_name, salary, first_name

FROM employees

ORDER BY salary ASC)) Elist2

WHERE Elist1.Rnum != Elist2.Rnum AND (Elist1.last_name = Elist2.last_name OR Elist1.first_name = Elist2.first_name

OR Elist1.salary = Elist2.salary)) IS NOT NULL);

17. Для каждой таблицы схемы вывести:

? Имя таблицы;

? Текст первого ограничения Check;

? Количество столбцов в первом ограничении Check;

? Текст второго ограничения Check;

? Количество столбцов во втором ограничении Check;

? Общее количество ограничений Check.

WITH

tab_dates AS (SELECT ROWNUM R_NUM, c_name, t_name, s_con

FROM (SELECT constraint_name AS "C_NAME", table_name AS "T_NAME", search_condition AS "S_CON"

FROM user_constraints

WHERE constraint_type = 'C'

ORDER BY t_name )),

counts AS (SELECT t_name, MIN(R_NUM) AS "MIN_R_NUM", MAX(R_NUM) AS "MAX_R_NUM"

FROM tab_dates

GROUP BY t_name),

res_tab AS (SELECT tab_dates.t_name AS "T_NAME", tab_dates.c_name, tab_dates.s_con, R_NUM - MIN_R_NUM +1 AS "C_POS", MAX_R_NUM - MIN_R_NUM +1 AS "COUNTS"

FROM tab_dates INNER JOIN counts ON tab_dates.t_name = counts.t_name),

col_count AS (SELECT constraint_name AS "C_NAME", all_count

FROM user_constraints NATURAL JOIN

(SELECT constraint_name, COUNT(column_name) AS "ALL_COUNT"

FROM user_cons_columns

GROUP BY constraint_name)

WHERE constraint_type ='C')

SELECT tab1.t_name AS "TABLE_NAME", tab1.s_con AS "First_position", tab1.all_count AS "P1_COLUMN_COUNT",

tab2.s_con AS "Second_position", tab2.all_count AS "P2_COLUMN_COUNT", tab1.counts AS "COUNT_CONST"

FROM (SELECT *

FROM res_tab NATURAL JOIN col_count ) tab1

LEFT JOIN (SELECT *

FROM res_tab NATURAL JOIN col_count

WHERE c_pos = '2') tab2 ON tab1.t_name = tab2.t_name

WHERE tab1.c_pos = '1';

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

Номер отдела	Название отдела	Количество сотрудников	Средний оклад	Фамилия	Оклад	Должность
30	Purchasing	6	4150
				Khoo	3100	PurchasingClerk
				Baida	2900	PurchasingClerk
				Tobias	2800	PurchasingClerk
				Himuro	2600	PurchasingClerk
				Colmenares	2500	PurchasingClerk
40	HumanResources	1	6500
				Mavris	6500	HumanResourcesRepresentative
50	Shipping	45	3475,56
				Weiss	8000	StockManager
				Fripp	8200	StockManager
				Kaufling	7900	StockManager
…	……			……		…….

SELECT

DECODE(ddept_id, NULL, ' ', ddept_id) AS "Номеротдела",

DECODE(dept_name, NULL, ' ', dept_name) AS "Названиеотдела",

DECODE(col, NULL, ' ', col) AS "Количество сотрудников",

DECODE(ROUND(avgsal,2), NULL, ' ', ROUND(avgsal,2)) AS "Среднийоклад",

DECODE(last_name, NULL, ' ', last_name) AS "Фамилия",

DECODE(salary, NULL, ' ', salary) AS "Оклад",

DECODE(jt, NULL, ' ', jt) AS "Должность"

FROM (SELECT ddept_id, dept_name, col, avgsal, last_name, salary, jt, edept_id

FROM (SELECT d.department_id AS ddept_id, d.department_name AS dept_name, COUNT(e.employee_id) OVER (PARTITION BY d.department_id) AS col,

AVG(e.salary) OVER (PARTITION BY d.department_id) AS avgsal, e.last_name AS last_name, e.department_id AS edept_id, e.salary AS salary, j.job_title AS jt

FROM employees e JOIN departments d ON e.department_id = d.department_id

JOIN jobs j ON e.job_id = j.job_id)

GROUP BY GROUPING SETS ((ddept_id, dept_name, col, avgsal),(last_name, salary, jt, edept_id))

ORDER BY ddept_id, edept_id)

ORDER BY

(CASE WHEN ddept_id IS NOT NULL

THEN ddept_id

ELSE edept_id END), ddept_id;

ИЛИ ДРУГОЙ ВАРИАНТ:

SELECT

DECODE(ddept_id, NULL, ' ', ddept_id) AS "Номеротдела",

DECODE(dept_name, NULL, ' ', dept_name) AS "Названиеотдела",

DECODE(col, NULL, ' ', col) AS "Количество сотрудников",

DECODE(ROUND(avgsal,2), NULL, ' ', ROUND(avgsal,2)) AS "Среднийоклад",

DECODE(last_name, NULL, ' ', last_name) AS "Фамилия",

DECODE(salary, NULL, ' ', salary) AS "Оклад",

DECODE(jt, NULL, ' ', jt) AS "Должность"

FROM (SELECT ddept_id, d.department_name dept_name, col, avgsal, e.last_name last_name, d.department_id dep_id, e.salary salary, job_title jt

FROM(SELECT department_id ddept_id, COUNT(*) col, ROUND(AVG(salary), 0) avgsal

FROM employees

GROUP BY department_id) JOIN departments d ON ddept_id = d.department_id

RIGHT JOIN employees e ON e.department_id = d.department_id JOIN jobs USING (job_id))

GROUP BY GROUPING SETS((ddept_id, dept_name, col, avgsal),(last_name, dep_id, salary, jt))

ORDER BY (CASE WHEN ddept_id IS NULL THEN dep_id ELSE ddept_id END), ddept_id, salary DESC nulls FIRST;