Сформировать отчёт, содержащий номер отдела, название отдела, имена и фамилии сотрудников, а также их зарплату в виде:
…
Сотрудников, не приписанных к конкретному отделу не показывать.
Решение: (ВНИМАНИЕ! В решении 2 варианта. Сабинину может подойти и первый. Андреевой нужен второй (где кейс по группингу))
SELECT
CASE WHEN Фамилия IS NULL THEN 'Отдел №'
ELSE Фамилия END last_name,
CASE WHEN имя IS NULL THEN "Номер отдела"
ELSE имя END first_name,
CASE WHEN оклад IS NULL THEN "Название отдела"
ELSE оклад END salary
FROM (
SELECT to_char(dept_id) "Номер отдела", dept_name "Название отдела",l_name Фамилия,f_name Имя,to_char(sal) Оклад
FROM
(SELECT s.dept_id,s.dept_name,s.l_name,sal,s.f_name, s.emp_dept
FROM
(SELECT d.department_id dept_id, d.department_name dept_name,
e.last_name l_name,e.first_name f_name, e.department_id emp_dept, e.salary sal
FROM employees e, departments d, jobs j
WHERE e.department_id=d.department_id AND e.job_id=j.job_id
) s
GROUP BY GROUPING SETS((dept_id,dept_name),(l_name,f_name,sal, emp_dept)))
ORDER BY (CASE WHEN dept_id IS NOT NULL THEN dept_id ELSE emp_dept END), dept_id);
Вариант 2:
SELECT
CASE WHEN gr = 0 THEN 'Отдел №'
ELSE Фамилия END last_name,
CASE WHEN gr = 0 THEN "Номер отдела"
ELSE имя END first_name,
CASE WHEN gr = 0 THEN "Название отдела"
ELSE оклад END salary
FROM (
SELECT to_char(dept_id) "Номер отдела", dept_name "Название отдела",l_name Фамилия,f_name Имя,to_char(sal) Оклад, gr
FROM
(SELECT s.dept_id,s.dept_name,s.l_name,sal,s.f_name, s.emp_dept, grouping(dept_id) gr
FROM
(SELECT d.department_id dept_id, d.department_name dept_name,
e.last_name l_name,e.first_name f_name, e.department_id emp_dept, e.salary sal
FROM employees e, departments d, jobs j
WHERE e.department_id=d.department_id AND e.job_id=j.job_id
) s
GROUP BY GROUPING SETS((dept_id,dept_name),(l_name,f_name,sal, emp_dept)))
ORDER BY (CASE WHEN dept_id IS NOT NULL THEN dept_id ELSE emp_dept END), dept_id);
Условия нет
delete from my_table
where R1 =(
select distinct m1.R1
from my_table m1
JOIN my_table m2
on m1.R1=m2.R2 and m1.R2=m2.R1
where rownum=1);
Условия нет
Проверка на уникальность:
#SET CONSTRAINT All Deferred;
Alter table employees
modify first_name constraint first_name_unique UNIQUE;
Alter table employees
modify last_name constraint last_name_unique UNIQUE;
Alter table employees
modify salary constraint salary_unique UNIQUE;
Выборка по каждому столбцу отдельно:
select distinct t1.employee_id, t1.first_name
from employees t1
join employees t2
on (t1.employee_id!=t2.employee_id)and(t1.first_name=t2.first_name)
order by t1.first_name;
select distinct t1.employee_id, t1.last_name
from employees t1
join employees t2
on (t1.employee_id!=t2.employee_id)and(t1.last_name=t2.last_name)
order by t1.last_name;
select distinct t1.employee_id, t1.salary
from employees t1
join employees t2
on (t1.employee_id!=t2.employee_id)and(t1.salary=t2.salary)
order by t1.salary;
ИТОГ:
select distinct t1.employee_id, t1.first_name,t1.last_name, t1.salary
from employees t1
join employees t2
on (t1.employee_id!=t2.employee_id)and((t1.first_name=t2.first_name)or(t1.last_name=t2.last_name)or(t1.salary=t2.salary))
order by first_name;
Написать запрос, выдающий отчёт о суммарных выплатах сотрудникам, непосредственно подчиняющихся заданному руководителю по идентификаторам должностей (поле Job_id). Непосредственное подчинение предполагает подчинение на первом уровне. Номер руководителя может встречаться в отчете лишь дважды.
Пример отчёта:
Номер руководителя |
Должность |
Кол-во сотрудников |
Выплаты |
Вид выплаты |
100 |
AD_VP |
2 |
34000 |
Зарплата сотрудников в должностиAD_VP |
|
MK_MAN |
1 |
13000 |
Зарплата сотрудников в должностиMK_MAN |
|
PU_MAN |
1 |
11000 |
Зарплата сотрудников в должностиPU_MAN |
|
SA_MAN |
5 |
79650 |
Зарплата сотрудников в должностиSA_MAN |
|
ST_MAN |
5 |
36400. |
Зарплата сотрудников в должностиST_MAN |
100 |
|
14 |
174050 |
Суммарная зарплата у руководителя 100 |
101 |
AC_MGR |
1 |
12000 |
Зарплата сотрудников в должностиAC_MGR |
|
FI_MGR |
1 |
12000 |
Зарплата сотрудников в должностиFI_MGR |
|
HR_REP |
1 |
6500 |
Зарплата сотрудников в должностиHR_REP |
|
PR_REP |
1 |
10000 |
Зарплата сотрудников в должностиPR_REP |
|
AD_ASST |
1 |
4400 |
Зарплата сотрудников в должностиAD_ASST |
101 |
|
5 |
44900 |
Суммарная зарплата у руководителя 100 |
|
|
34 |
301250 |
Общий итог |
WITH table_1 AS
(SELECT manager_id, job_id, cn, sumsal, Mng_Gr, Job_Gr, ROWNUM r
FROM
(SELECT manager_id, job_id, count(*) cn, Sum(salary) sumsal, grouping(manager_id) Mng_Gr, grouping(job_id) Job_Gr
FROM employees
GROUP BY ROLLUP(manager_id, job_id)
ORDER BY manager_id)),
table_2 AS (SELECT manager_id, MIN(r) minr FROM table_1 GROUP BY manager_id)
SELECT NVL(DECODE(Job_Gr, 1, manager_id, DECODE(r, minr, manager_id, '')), '') AS "Номер руководителя", NVL(job_id, '') AS "Должность", cn AS "Количество сотрудников",
sumsal AS "Выплаты", DECODE(Mng_Gr, 1, 'Общий итог', DECODE(Job_Gr, 1,
'Суммарная зарплата у руководителя '
|| manager_id, 'Зарплата сотрудников в должности '
|| DECODE(Job_Gr, 1, ' ', job_id))) AS "Вид выплаты"
FROM table_1 LEFT JOIN table_2 USING(manager_id)
ORDER BY manager_id, job_id;
Создать запрос для определения сумм зарплат сотрудников между двумя заданными сотрудниками, находящимися в явном или неявном подчинении. Если заданы два пользователя, не подчиняющиеся друг другу, то выводить фамилии и зарплаты каждого из них.
with data as (
select emp_last_name, emp_id, s, mng_last_name, mng_id from
(select &&employee_id, e.last_name emp_last_name, e.employee_id emp_id, e.salary s, &&manager_id, m.last_name mng_last_name,
m.employee_id mng_id
from employees e JOIN employees m on (e.manager_id=m.employee_id)
start with m.manager_id=&&manager_id --а не m.employee_id ли тут?
connect by NOCYCLE prior &&employee_id = m.manager_id)),
data2 as ((select employee_id, last_name ln2,salary ss from employees
where employee_id=&&employee_id)
UNION (select employee_id, last_name ln2,salary ss from employees --10
where employee_id=&&manager_id)),
data3 as (
select emp_last_name, emp_id, s, mng_last_name, mng_id
from data
union
select ln2, employee_id, ss, TO_CHAR(null), TO_NUMBER(null)
from data2)
select distinct
(case
when EXISTS (select 'X' from data) then 'Сумма затрат: '
when count(data3.emp_last_name)<=2 then emp_last_name
end) " ",
(case
when EXISTS (select 'X' from data) then sum(s) over (order by s ROWS between unbounded preceding and unbounded following)--18
when (count(data3.emp_last_name))<=2 then s
end) " "
from data3
group by data3.emp_last_name, data3.s;