-- 1. Найти имена сотрудников, получивших за годы начисления зарплаты
-- минимальную зарплату.
select distinct(emp.empname) from emp natural join salary where salary.salvalue = (select min(salvalue) from salary);
-- 2. Найти имена сотрудников, работавших или работающих в тех же отделах, в
-- которых работал или работает сотрудник с именем RICHARD MARTIN.
select emp.empname from emp natural join career where career.deptno in (select career.deptno from career natural join emp where emp.empname = 'RICHARD MARTIN') and emp.empname != 'RICHARD MARTIN';
-- 3. Найти имена сотрудников, работавших или работающих в тех же отделах и
-- должностях, что и сотрудник RICHARD MARTIN
select emp.empname from emp natural join career where (career.deptno, career.jobno) in (select career.deptno, career.jobno from career natural join emp natural join job where emp.empname = 'RICHARD MARTIN') and emp.empname != 'RICHARD MARTIN';
-- 4. Найти сведения о номерах сотрудников, получивших за какой-либо месяц зарплату
-- большую, чем средняя зарплата за 2007 г. или большую чем средняя зарплата за 2008г.
select distinct(empno) from salary where salvalue > any(select avg(salvalue) from salary where year in ('2007', '2008') group by year);
-- 5. Найти сведения о номерах сотрудников, получивших зарплату за какой-либо месяц
-- большую, чем средние зарплаты за все годы начислений.
select distinct(empno) from salary where salvalue > all(select avg(salvalue) from salary group by year);
-- 6. Определить годы, в которые начисленная средняя зарплата была больше средней
-- зарплаты за все годы начислений.
select year from salary group by year having avg(salvalue) > (select avg(salvalue) from salary);
-- 7. Определить номера отделов, в которых работали или работают сотрудники,
-- имеющие начисления зарплаты.
select distinct(deptno) from career c where empno in (select empno from salary where empno = c.empno);
-- 8. Определить номера отделов, в которых работали или работают сотрудники,
-- имеющие начисления зарплаты.
select distinct(deptno) from emp e inner join career c on e.empno = c.empno  where exists(select empno from salary s where s.empno = e.empno);
-- 9. Определить номера отделов, для сотрудников которых не начислялась зарплата.
select distinct(deptno) from emp e inner join career c on e.empno = c.empno  where not exists(select empno from salary s where s.empno = e.empno);
-- 10. Вывести сведения о карьере сотрудников с указанием названий и адресов отделов
-- вместо номеров отделов.
select jobno, empno, (select deptname||' '|| deptaddr from dept where deptno = c.deptno) as deptname, startdate, enddate from career c;
-- 11. Определить целую часть средних зарплат, по годам начисления.
select year, cast(avg(salvalue) as integer) as avg_sal from salary group by year;
-- 12. Разделите сотрудников на возрастные группы: A) возраст 20-30 лет; B) 31-40 лет;
-- C) 41-50; D) 51-60 или возраст не определён.
select emp.*,
case
when EXTRACT(YEAR FROM (SYSDATE - birthdate) YEAR TO MONTH) between 20 and 30 then 'A'
when EXTRACT(YEAR FROM (SYSDATE - birthdate) YEAR TO MONTH) between 30 and 40 then 'B'
when EXTRACT(YEAR FROM (SYSDATE - birthdate) YEAR TO MONTH) between 40 and 50 then 'C'
when EXTRACT(YEAR FROM (SYSDATE - birthdate) YEAR TO MONTH) between 50 and 60 then 'D'
else 'D'
end as category
from emp;
-- 13. Перекодируйте номера отделов, добавив перед номером отдела буквы BI для
-- номеров <=20, буквы LN для номеров >=30.
select dept.*,
case
when deptno <= 20 then concat('BI',to_char(deptno))
when deptno >= 30 then concat('LN',to_char(deptno))
end coded_name
from dept;
-- 14. Выдать информацию о сотрудниках из таблицы EMP, заменив отсутствие данного
-- о дате рождения датой 01-01-1000.
select empno, empname,
coalesce(birthdate, to_date('01/01/1000', 'DD/MM/YYYY')) as birthdate
from emp;